Research on my.cnf priority of multiple Parameter option Files in mysql
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)
