MySQL programs are known to read startup options from option files, otherwise identified as configuration files. Option files are able to provide a way to specify commonly used options, which assists the user in not having to enter the options on the command line every time a program is ran.

To figure out whether or not a program reads option files, use the — help option (for MySQLD use verbose and – – help). If the program reads option files, then the help message will indicate which files it searches for and which option groups are recognized.

For example, a MySQL program started with the –no-defaults option will read no option files other than .mylogin.cnf. Likewise, a server starting with the persisted_globals_load system variable deactivated does not read mysqld-auto.cnf.

More on Options Files

Option files are usually plain text files and are made by using any text editor. However there are a few exceptions to this. They are as follows:

  1. The .mylogin.cnf file that contains login path options. This is an encrypted file made by the mysql_config_editor utility. For reference, a “login path” is an option group that allows only certain options: host, user, password, port and socket. There are programs that specify which login path to read from .mylogin.cnf using the –login-path option.
  2. To specify an another login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable. This variable is used by the mysql-test-run.pl testing utility, but can also be viewed as
  3. The mysqld-auto.cnf file in the data directory is also an exception. This JSON-format file contains ongoing system variable settings. The server creates this upon execution of SET PERSIST or SET PERSIST_ONLY statements. Administration of mysqld-auto.cnf should be left to the server and should never be performed manually.

MySQL searches for option files in the order described in the above paragraphs and reads any that exist. If you want to use an option file that does not exist, make it using the appropriate method mentioned above.

Below is a table for reference:

Table 4.2 Option Files Read on Unix and Unix-Like Systems

File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options (server only)
defaults-extra-file The file specified with –defaults-extra-file, if any
~/.my.cnf User-specific options
~/.mylogin.cnf User-specific login path options (clients only)
DATADIR/mysqld-auto.cnf System variables persisted with SET PERSIST or SE PERSIST_ONLY (server only)

 

When faced with using options files, do not use one that is an easy target for security breaches. An example of this is .my.cnf – mysql user & password. Avoid this one at all costs!

Instead, use the following method:

  • Create file ~/.my.cnf and add following lines in it and replace mysqluser & mysqlpass values.

[client]

user=mysqluser

password=mysqlpass

  • For safety, make this file understandable to you only by running chmod 0600 ~/.my.cnf

Purpose

Whenever you need to run mysql commands mysql, mysqlcheck, mysqdump, etc; the commands will choose your username & password from this file if one is not provided to them as argument (-u and -p). This will save you time. However, if a username and password are specified by you as part of the commands arguments, then that information will be used.