MySQL - Using Option Files with CLI Client Tools

• Summary
• File Structure
• Option Groups
• Reading Option Files
• Applicable Options
• .mylogin.cnf
  • Obfuscation / Encryption
  • File Permissions
• Further Reading

Summary

The MySQL CLI client tools such as mysql and mysqldump have the ability to read options from a file. This serves as an alternative to supplying each required option as a command line argument. This is particularly useful when automating MySQL tasks, it saves having to devise your own config file solution.

File Structure

An option file is divided into sections which in MySQL terminology are referred to as option groups, beneath which the associated options reside, EG:

[client]
host=127.0.0.1
port=3309

[mysql]
xml

[mysqldump]
result-file=/home/thecliguy/inventory-backup.sql
verbose

(I’m not advocating the above settings, they are simply for illustrative purposes.)

Lines prefixed with # or ; are treated as comments.

Since the file content is stored in cleartext, you should take measures to ensure that the file is protected with adequate permissions if it contains a password, consult the official MySQL documentation for details. If you are using MySQL 5.6+ you could place credentials in an obfuscated .mylogin.cnf file, this subject is covered in detail later in the post but it is important to state that the appropriate use of file permissions is still of vital importance.

Option Groups

The [client] option group is read by all client tools. If an option group name is the same as the calling program name (EG mysqldump) then it is read.

The order in which option groups are listed is of significance, options specified later override options specified earlier. So for example, putting the option groups in the order [client], [mysqldump] enables options specific to mysqldump to override [client] options.

Reading Option Files

The MySQL CLI client tools implicitly attempt to read option files by checking for the presence of specifically named files in a number of different locations. The file names, locations and the order in which the search is performed is dependent on the OS, consult the official MySQL documentation for details.

If multiple option files are read then the end result is the combination of each applicable option. If the same option is encountered more than once then the last instance takes precedence with the exception of user in the mysqld option group where the first instance takes precedence.

To prevent any option files from being read, use the --no-defaults parameter or to read only a specific file use --defaults-file=<filename>.

NB: Even with --no-defaults or --defaults-file, client tools will continue to read .mylogin.cnf if present. I’ll cover .mylogin.cnf later in this post.

On *nix only, if a client tool encounters a world-writable option file then it is ignored and a warning is emitted to stderr, eg:

mysqldump: [Warning] World-writable config file '/home/thecliguy/.my.cnf' is ignored.

Applicable Options

In order to determine which options will be applied when a client tool is executed, use the --print-defaults parameter.

The following example was run on Ubuntu, where .my.cnf (containing the values shown in the example under File Structure) and .mylogin (the purpose of this file is explained later in the post) were present in the user’s home directory (a directory in which client tools implicitly check for the presence of option files on *nix):

mysqldump would have been started with the following arguments:
--quick --quote-names --max_allowed_packet=16M --host=127.0.0.1 --port=3309 
--result-file=/home/thecliguy/inventory-backup.sql --verbose --user=testuser 
--password=*****

NB: On Windows 10 I noticed that --print-defaults doesn’t detect the presence of a .mylogin file.

.mylogin.cnf

In MySQL 5.6, a new type of option file was introduced called .mylogin.cnf, the purpose of which is to store authentication credentials. The file content is obfuscated but can be read by MySQL client tools. Creating and editing .mylogin.cnf is done with mysql_config_editor.

The unobfuscated format of .mylogin.cnf follows the same structure as that of a regular cleartext text option file, however each section is referred to as a login path opposed to an option group and the only permitted options are host, user, password and port.

To my knowledge you cannot specify where mysql_config_editor places the resultant .mylogin.cnf file it produces, on Windows the file location is the %APPDATA%\MySQL and on *nix systems it’s the user’s home directory. Likewise, I do not believe it is possible to instruct CLI client tools to load .mylogin.cnf from a specified location, they will implicitly search the aforementioned locations.

A .mylogin.cnf file can contain multiple login paths, by default mysql_config_editor writes to [client] unless supplied with a specific name. CLI client tools follow the same logic for reading login paths in .mylogin.cnf as they do for reading option groups in regular option files (see Option Groups). It is possible to instruct a CLI client tool to read only a specific login path using --login-path, eg mysqldump --login-path=foo.

Obfuscation / Encryption

Throughout this post I’ve referred to .mylogin.cnf as being obfuscated opposed to encrypted. This is because although the data is encrypted, the key used to perform the encryption is stored alongside it in cleartext. Therefore the file is not encrypted as such, it just contains an encrypted element.

Encryption is achieved using AES, mysql_config_editor generates a random key formed of 20 non-printable ASCII characters.

Since the key and the data are stored together, anyone with access to the file can decrypt the data. To demonstrate the point, this PHP script will decrypt the content of a .mylogin.cnf file. It does so by reading the file content, extracting the key, extracting the data and then performing the decryption using the OpenSSL library. There are no dependencies on MySQL components, I have successfully created a .mylogin.cnf on one machine using mysql_config_editor and used the script to decrypted it on another where no MySQL binaries or libaries were present.

Therefore, even though the data is encrypted it is still vital to secure this file with appropriate permissions, the next section of this post elaborates further.

File Permissions

The MySQL 8.0 online docs for mysql_config_editor state that: “The login path file must be readable and writable to the current user, and inaccessible to other users. Otherwise, mysql_config_editor ignores it, and client programs do not use it, either.”. Having performed some tests, I found this to be true on Ubuntu (16.04 LTS) but not on Windows 10 Pro (Version 1803 Build 10.0.17134.523).

On Ubuntu, mysql_config_editor creates .mylogin.cnf with restrictive permissions (600), where the owner is granted read and write access to the file, and all others have no permissions. Changing the file permissions to make it accessible to other users causes client tools such as mysqldump to complain:

mysqldump: [Warning] /home/thecliguy/.mylogin.cnf should be readable/writable only by current user.
mysqldump: Got error: 1045: Access denied for user 'thecliguy'@'localhost' (using password: NO) when trying to connect

On Windows 10, mysql_config_editor appears not to apply any explicit permissions to .mylogin.cnf on creation. Examining the file reveals only the default NTFS permissions which are applied through inheritance from C:\Users\<USERNAME>. The client tools read .mylogin.cnf with no complaint even though technically other users do have access to the file because in addition to the respective user the default inherited permissions also grant the Administrators group and SYSTEM account full control.

As mentioned, technically other users do have access to .mylogin.cnf however the Administrators group and SYSTEM account are special because they both possess the ability to take ownership of files. So although you could remove the Administrators group and SYSTEM account from the NTFS permissions of a file it would be somewhat superficial since they can always regain access by taking ownership.

This lead me to wonder if perhaps the client tools tolerated the default permissions on Windows since they are restrictive anyway and whether they’d complain if any deviation is made. To test this theory I granted All Users Full control of C:\Users\<USER>, subfolders and files, I then proceeded to run mysqldump. Despite granting extremely lax permissions on .mylogin.cnf, mysqldump read it without complaint.

So contrary to the documentation, the client tools do not decline to use .mylogin.cnf if it is accessible to other users on Windows. I have filed an issue on the MySQL Bug System suggesting that either Windows should adhere to the same standards as *nix or the documentation should be revised stating that it is applicable only to *nix, see Login Path File (mylogin.cnf) Permissions Not Assigned or Assessed on Windows.

Further Reading

• MySQL Github Repository
  • NB: To learn about how mysql_config_editor handles encryption I studied the source code. Search the repo for mysql_config_editor.cc.
• Official MySQL Documentation