Shulou(Shulou.com)06/01 Report--

My.cnf is the file name of the default configuration file for mysql servers on the unix platform.

Enter my_print_defaults to get the order of my.cnf read when mysql server starts: (generally these four, depending on installation method, OS distribution, mysql version)

Or

$mysql-- help | grep my.cnf

/ etc/my.cnf / etc/mysql/my.cnf / usr/local/mysql/my.cnf (some versions are written / usr/local/mysql/etc/my.cnf) ~ / .my.cnf

That is, read / etc/my.cnf first, then read / etc/mysql/my.cnf, the third read / usr/local/mysql/my.cnf, and the third is basedir, the mysql installation directory.

The fourth is ~ / .my.cnf, which is / home/$USERNAME, and $USERNAME is the server startup user.

The order given in the manual is (read from top to bottom)

File Name Purpose/etc/my.cnf Global options/etc/mysql/my.cnf Global optionsSYSCONFDIR/my.cnf Global options$MYSQL_HOME/my.cnf Server-specific optionsdefaults-extra-file The file specified with-- defaults-extra-file=path, if any~/.my.cnf User-specific options~/.mylogin.cnf Login path options

However, the general reading order is as follows:

/ etc/my.cnf / etc/mysql/my.cnf ~ / .my.cnf

Omit part of the path.

Where / etc/my.cnf and / etc/mysql/my.cnf are global option files

And ~ / .my.cnf is the user option file.

Scenario 1:

Global options and Global options exist at the same time.

That is, / etc/my.cnf and / etc/mysql/my.cnf exist at the same time

Question: if / etc/my.cnf exists, will you still look for / etc/mysql/my.cnf?

If so, use the / etc/mysql/my.cnf file directly

Or should I use / etc/my.cnf first and then use / etc/mysql/my.cnf? If the parameters are the same, does the latter override the former?

Experiment:

/ etc/my.cnf

[mysqld]

Long_query_time = 15

Slow_query_log = on

Autocommit = off

/ etc/mysql/my.cnf

[mysqld]

Long_query_time = 12

Slow_query_log = on

Query:

Mysql > show variables like 'autocommit'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like 'long_query_time'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 12.000000 | |

+-+ +

1 row in set (0.01 sec)

The autocommit=off in / etc/my.cnf is used first at this time.

Although slow_query_log is set, the parameters are the same, / etc/mysql/my.cnf has a higher priority, so it is 12s.

Continue the experiment:

Delete / etc/my.cnf

$sudo mv / etc/my.cnf / etc/my.cnf.bk

Restart the server and query:

Mysql > show variables like 'autocommit'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like 'long_query_time'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 12.000000 | |

+-+ +

1 row in set (0.01 sec)

Only / etc/mysql/my.cnf is used at this time.

Scenario 2:

Global options and User-specific options co-exist

That is, / etc/my.cnf and ~ / .my.cnf exist at the same time.

Experiment:

Similarly, delete the other configuration files first and make sure that only the following two locations are left:

/ etc/my.cnf

[mysqld]

Long_query_time = 15

Slow_query_log = on

Autocommit = off

~ / .my.cnf

[mysqld]

Long_query_time = 12

Slow_query_log = on

Save and exit after editing

Query:

Mysql > show variables like 'autocommit'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like 'long_query_time'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 12.000000 | |

+-+ +

1 row in set (0.01 sec)

Autocommit defaults to on

Long_query_time defaults to 10

Note / etc/my.cnf has come into effect

Autocommit = off

Although there are settings

Long_query_time = 15

But

~ / .my.cnf

There are settings in

Long_query_time = 12

Therefore, ~ / .my.cnf is also valid, and when the same option exists, the priority is higher than / etc/my.cnf of Global options.

Conclusion:

When more than one my.cnf exists:

When Global options and User-specific options exist at the same time, User-specific options takes precedence over Global options and both will be read. If the option is the same, the higher priority overrides the former.

Although / etc/my.cnf and / etc/mysql/my.cnf are both Global options, the rules are also the same as Global options and User-specific options.

Author's official account on Wechat (continuously updated)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.