Access MySQL Without Password

Running mysql -u user -p prompts for a password interactively. That works fine at a terminal, but it breaks automated workflows.

If you need to back up a database every night and push it to S3, you cannot type a password into a cron job. The naive solution is passing the password as a command-line argument:

mysqldump -u user -p password my_database > mydatabase.dump

This is insecure. The password is now recorded in .bash_history:

less /root/.bash_history
mysqldump -u user -p password my_database > mydatabase.dump

The proper fix is a .my.cnf file in the user's home directory.

Create a dedicated backup user and lock down the file permissions so only that user can read it:

cd /home/bkp
touch .my.cnf
chmod u=rw,go-rwx .my.cnf

Add the credentials to .my.cnf:

[client]
user=mysql_user
password="password"
default-character-set=utf8mb4

Now mysql and mysqldump authenticate without a password argument:

bkp@myserver:~# mysql
mysql> show databases;
...

Summary

Never pass MySQL passwords as command-line arguments -- they end up in .bash_history and are visible in process listings.
Use a .my.cnf file in the user's home directory to store credentials for non-interactive access.
Restrict file permissions with chmod u=rw,go-rwx so only the owning user can read the credentials file.
Create a dedicated backup user rather than storing root credentials in a config file.

#Summary

Summary