MySQL user options file

The normal way to connect to a mysql server is to use the command line client 'mysql' as follows

[safeer@LinuxBox1 ~]$mysql -u safeer -h server01 -p app1
Enter password:

This will connect to the database 'app1' in server 'server01' using the username safeer, and a password.

There are different mysql clients used for different purposes, like mysqldump for backing up databases, mysqladmin for administrative tasks etc. For each of these clients you should specify the username, password,host and many other options.

If you are using the same values for these options frequently, you can get rid of typing the long statement by using mysql user options file. This file will be having the name .my.cnf and will be located in the users home directory.

Actually there are three Global option files for mysql viz,
/etc/my.cnf --- Global Options

$MYSQL_HOME/my.cnf --- Server Specific Options
$MYSQL_HOME will usually be /var/lib/mysql/. my.cnf will not be present here always.

Defaults-Extra-File - File specified with the option --defaults-extra-file=path
This need not have to be present at all.

User specific options will be present in the home directory of the user
~/.my.cnf

In addition, options can be specified at command line when using the clients.

If the same option appears in more than one place in the above mentioned list, the last one will get precedence over others.

Following contents are permitted in option files

Comments : Comments can be inserted with "#" and ";" at the begining

[group] : Program or program group that interact with mysql server. This can be the command line tools like mysql,mysqldump,mysqladmin,mysql,mysqld_safe etc....To represent all the command line client together (except mysqld) you can use [client]

Syntax for option file is the same as the command line options, except that the leading double dashes are removed

option : same as --option on command line

option=value : same as --option=value

For example, to connect to a database "app2" in the machine "server01" using the username "nebu" and password "my_pass123" using command line, also the communication between server and client should be compressed (if both end supports compression).

[safeer@LinuxBox1 ~]$mysql --compress --user="nebu" --password="my_pass123" --database="app2" --host="server01"

instead we can set this in user option file
/home/safeer/.my.cnf

[mysql]
compress
user="nebu"
password="my_pass123"
database="app2"
host="server01"

Now you can simply use 'mysql' without all those long options.
[safeer@LinuxBox1 ~]$mysql
This will achieve the same task as the previous command.

you can override any of the above options from command line. Suppose that you want to connect to another database "db_app3" on the same server, use:
[safeer@LinuxBox1 ~]$mysql --database="db_app3"

If you are using the same credentials for all clients you can put it under the [client] group. We can override those setting or add addition settings by including more [group] options

/home/safeer/.my.cnf

[client]
user="nebu"
password="my_pass123"

[mysqldump]
compact

You should make sure that only you have the permision to read and write to the options file

This setting will allow all clients to use the username "nebu" and password "my_pass123" (unless overridden at command line). In addition mysqldump will use the compact option.

I found the option file most useful when I had to use mysql clients with scripts, especially to take backups of databases. since there is no way for user interaction, I created database users with read permission to the databases to be backed up, put its username and password in the options file, and run mysqldump from a cronjob.