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