0. The global option is located in /etc/mysql/my.cnf
$ ls -l /etc/mysql/my.cnf rw-r--r- 1 root root 3505 Sep 7 16:23 /etc/mysql/my.cnf
1. Additional settings to override the settings in global options must follow two rules. First, the file names must be end with ".cnf" extension. Second, these files can be found in /etc/mysql/conf.d or ~/.my.cnf.
2. To prevent yourself from repeatedly typing the same parameters (yes I know, you can use bash's alias), put these option in your user-specific option file, ~/.my.cnf.
# read by all clients. e.g mysql, mysqldump, or mysqladmin [client] user=root pass=password # read only by the targeted client [mysql] database=mysql pager=less
3. What are the available options for each client ? Display the help documentation and look for all those item with double dashes, --option=value
$ mysql --help | less $ mysqladmin --help | less
4. World-writable option file is ignored. Important to understand this if somehow you mistakenly changed the config file permissions.
$ ls -l ~/.my.cnf rw-rw-r- 1 kianmeng kianmeng 82 Feb 8 07:43 /home/kianmeng/.my.cnf $ chmod o+w ~/.my.cnf $ ls -l ~/.my.cnf rw-rw-rw 1 kianmeng kianmeng 82 Feb 8 07:43 /home/kianmeng/.my.cnf
When you try to run any of the mysql client program, all the options set in the ~/.my.cnf will be ignored. How to troubleshoot this ? Imagine your officemate put a prank on you and you're really stuck ?
$ mysql --print-defaults Warning: World-writable config file '/home/kianmeng/.my.cnf' is ignored mysql would have been started with the following arguments: --port=3306 --socket=/var/run/mysqld/mysqld.sock
Revert back our changes.
$ chmod o+w ~/.my.cnf $ ls -l ~/.my.cnf rw-rw-r- 1 kianmeng kianmeng 82 Feb 8 07:43 /home/kianmeng/.my.cnf
Double check our config file is loaded properly.
$ mysql --print-defaults mysql would have been started with the following arguments: --port=3306 --socket=/var/run/mysqld/mysqld.sock --user=root --pass=password --database=mysql --pager=less