How to use .my.cnf files for MySQL

Introduction

The .my.cnf file tells the mysql command-line utility how to connect to a MySQL database. It is typically stored in the home directory of the current user. Here's a sample file:

[mysql]
# Set a larger maximum packet size for the client
max_allowed_packet=512M
user=root
password=my_secret!p@sswordIsVerylong#

Notice the plaintext password is in the file. It's a good idea to set read-write privileges only for the user (ie chmod 600). Now the user should be able to simply type mysql at a command-line and the MySQL monitor should automatically connect to the database.

If it does not work

The following command may not work: sudo mysql

Why? Well, try this: sudo strace mysql 2>&1|grep open

And you will probably get something like:

open("/etc/ld.so.cache", O_RDONLY)      = 3
open("/usr/lib64/libncursesw.so.5", O_RDONLY) = 3
open("/lib64/libpthread.so.0", O_RDONLY) = 3
open("/lib64/libcrypt.so.1", O_RDONLY)  = 3
open("/lib64/libnsl.so.1", O_RDONLY)    = 3
open("/lib64/libm.so.6", O_RDONLY)      = 3
open("/lib64/libc.so.6", O_RDONLY)      = 3
open("/lib64/libdl.so.2", O_RDONLY)     = 3
open("/home/user/.my.cnf", O_RDONLY)  = -1 EACCES (Permission denied)
open("/etc/nsswitch.conf", O_RDONLY)    = 3
open("/etc/ld.so.cache", O_RDONLY)      = 3
open("/lib64/libnss_files.so.2", O_RDONLY) = 3
open("/etc/services", O_RDONLY)         = 3
open("/usr/share/mysql/charsets/Index.xml", O_RDONLY) = 4

Notice the EACCES line? That means that user's .my.cnf file could not be read by root. This can happen if you secured the system properly. So, how do we get sudo to look at /root/.my.cnf?

sudo -H strace mysql 2>&1|grep open

Instead of an EACCES line, it should have something like:

open("/root/.my.cnf", O_RDONLY) = 3

Of course, the grep hides some important details and strace gives us details we no longer need, so just type this:

sudo -H mysql

Your rating: None Average: 4 (1 vote)