Technology sharing | The order in which the mysql client reads the configuration file

Author: Yu Zhenxing

A member of the DBA team of Akson, keen on technology sharing and writing technical documents.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

We all know that when using the mysql client to access the MySQL database, the configuration files in different locations will be read in a certain order, but during a test, we found that in addition to reading the default configuration file path in order, mysql also has The additional behavior of reading configuration files, the following is an interesting little knowledge point encountered when testing in my local test environment

A scene phenomenon

When I did the test locally, I found a strange phenomenon. When I used socket to log in to the database, I found that when I did not specify a user, the default was not to use the root user to log in, but was modified to the zhenxing user.

 [root@10-186-61-162 ~]# mysql -S /data/mysql/3306/data/mysqld.sock -p
Enter password:
ERROR 1045 (28000): Access denied for user 'zhenxing'@'127.0.0.1' (using password: NO)

By observing the default parameter behavior of the current mysql client, we can see that it is consistent with the error report, and the default user has indeed changed to zhenxing

 [root@10-186-61-162 ~]# mysql --help|egrep "user|host|port"
  -h, --host=name     Connect to host.
  -P, --port=#        Port number to use for connection or 0 for default to, in
  -u, --user=name     User for login if not current user.
host                              127.0.0.1
port                              3306
user                              zhenxing

As a DBA here, our first reaction is to check whether the default user is configured in the /etc/my.cnf file, so check the client configuration parameters of the configuration file, as follows

 [client]
host            = 127.0.0.1
user            = root
port            = 3306

[mysql]
host            = 127.0.0.1
user            = root
port            = 3306
prompt          = '\U[\d]> '

It is found that the configuration value in the configuration file is the root user, and the zhenxing user has not been configured. It seems that this configuration file is not read. Is it reading other configuration files? Continue to check other configuration files

Second, the investigation ideas

1. Get the configuration file reading order

We first print out all the configuration files that may be read and the order in which they are read to check one by one

 ## 查看mysql客户端读取配置文件的顺序
[root@10-186-61-162 ~]# mysql --verbose --help|grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /data/mysql/3306/base/my.cnf ~/.my.cnf

2. Check /etc/my.cnf

/etc/my.cnf It has been confirmed that no relevant configuration has been made before, and it is skipped here.

3. Check /etc/mysql/my.cnf

Check the /etc/mysql/my.cnf configuration and find that there is no relevant configuration, exclude

 [root@10-186-61-162 ~]# cat /etc/mysql/my.cnf
cat: /etc/mysql/my.cnf: 没有那个文件或目录

4. Check /usr/local/mysql/etc/my.cnf

Check /usr/local/mysql/etc/my.cnf configuration, and find that there is no related configuration, exclude

 [root@10-186-61-162 ~]# cat /usr/local/mysql/etc/my.cnf
cat: /usr/local/mysql/etc/my.cnf: 没有那个文件或目录

5. Check /data/mysql/3306/base/my.cnf

Check /data/mysql/3306/base/my.cnf configuration, found that there is no relevant configuration, exclude

 [root@10-186-61-162 ~]# cat /data/mysql/3306/base/my.cnf
cat: /data/mysql/3306/base/my.cnf: 没有那个文件或目录

6. Check ~/.my.cnf

View ~/.my.cnf there is still no related configuration, exclude

 [root@10-186-61-162 ~]# cat ~/.my.cnf
cat: /root/.my.cnf: 没有那个文件或目录

So far, the configuration file read paths obtained according to mysql --verbose --help|grep my.cnf have been excluded, and the user zhenxing has not been configured.

7. Use no-defaults to eliminate configuration file interference

• Try to use --no-defaults to not read any configuration files to eliminate the interference of configuration files and see if it will return to normal

 [root@10-186-61-162 ~]# mysql --help|grep no-defaults
--no-defaults           Don't read default options from any option file

## 查看不读取配置文件时,客户端的默认值
[root@10-186-61-162 ~]# mysql --no-defaults --help|egrep "user|host|port"
  -h, --host=name     Connect to host.
  -P, --port=#        Port number to use for connection or 0 for default to, in
  -u, --user=name     User for login if not current user.
host                              127.0.0.1
port                              3306
user                              zhenxing

## 查看读取的所有客户端配置文件参数设置
[root@10-186-61-162 ~]# mysql --print-defaults
mysql would have been started with the following arguments:
--host=127.0.0.1 --user=root --port=3306 --host=127.0.0.1 --user=root --port=3306 --prompt=\U[\d]>  --user=zhenxing --password=***** --host=127.0.0.1 --port=3306

From the above output results, we can get the following 2 basic phenomena:

• Even if you specify --no-defaults without reading any configuration files, the default value of this user is still the zhenxing user
• When the output --print-defaults gets the actual running value, you can see that the [client] under /etc/my.cnf and the attribute configuration under the [mysql] tag are correctly obtained from top to bottom
• In addition to /etc/my.cnf, at the end there are --user=zhenxing --password=***** --host=127.0.0.1 --port=3306 these 4 parameters are additionally added to the end of the command

ps: The principle of mysql client and server reading configuration is 文件读取从上到下，后面相同参数配置覆盖前面的参数

After a series of exclusions, the source of the modified default value is still not found.

8. Print the system call of the mysql client

• Use strace to directly observe which configuration is called by the mysql client during execution. The following is a snippet of the configuration related to calling my.cnf (the result is a simplified output)

 1. stat("/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=195, ...}) = 0      = 3
2. stat("/etc/mysql/my.cnf", 0x7ffd56813180) = -1 ENOENT (No such file or directory)
3. stat("/usr/local/mysql/etc/my.cnf", 0x7ffd56813180) = -1 ENOENT (No such file or directory)
4. stat("/data/mysql/3306/base/my.cnf", 0x7ffd56813180) = -1 ENOENT (No such file or directory)
5. stat("/root/.my.cnf", 0x7ffd56813180)   = -1 ENOENT (No such file or directory)
6. stat("/root/.mylogin.cnf", {st_mode=S_IFREG|0600, st_size=336, ...}) = 0

The following conclusions can be drawn from the above calling sequence:

• The calling sequence of lines 1-5 is basically the same as the logic we verified
• Lines 2-5 displayed as No such file or directory are consistent with our verification results
• The 6th line of output adds a read operation to /root/.mylogin.cnf, and you can know that the current file does exist

9. Check /root/.mylogin.cnf

When we see this file, we generally know that this is the file generated by the mysql_config_editor tool to configure the login-path. We can view the current configuration information in the following ways

 [root@10-186-61-162 ~]# mysql_config_editor print --all
[client]
user = "zhenxing"
password = *****
host = "127.0.0.1"
port = 3306

Here you can see that there is a connection parameter configuration of the client label in the configuration. The content of the configuration is exactly the abnormal default value displayed at the beginning of our article. At this point, we have basically located the cause of this strange phenomenon. The reason for the failure scenario is also that this is the test environment that has done some tests on the usage of mysql_config_editor , which led to the occurrence of this phenomenon.

3. Scenario Summary

1. In addition to reading the configuration in the normal order output by the command mysql --verbose --help|grep my.cnf , the mysql client will additionally read the configuration in the file .mylogin.cnf at the end

2. Even if --no-defaults is specified, it will still read the configuration values of the [client] and [mysql] tags in .mylogin.cnf

The official documentation also gives clear instructions in the following link (the following is the key description fragment)

• https://dev.mysql.com/doc/refman/8.0/en/option-file-options.html

 The mysql client reads [client] and [mysql] from other option files, and [client], [mysql], and [mypath] from the login path file.

Client programs read the login path file even when the --no-defaults option is used. This permits passwords to be specified in a safer way than on the command line even if --no-defaults is present.

• Personally, I guess the purpose of this design was to consider that when the backup script needs to connect to the database, in order to prevent the interference of unnecessary parameter files, after specifying the --no-defaults parameter, it can still be used .mylogin.cnf password configured in the file to improve security