MySQL Option File : Configuration and Troubleshooting

Examples discussed here are in Ubuntu 12.10.

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

No comments:

Post a Comment