mysql -u user -p asks us to provide the password, but the shell sometimes needs to be non-interactive.
Imagine a case where you need to take a complete backup of your database every night, and transfer it to S3; then how would you provide the MySQL password?
You can do something like this:
mysqldump -u user -p password my_database > mydatabase.dump
Typing the password as an argument is insecure because it leads to exposing the password in the
less /root/.bash_history mysqldump -u user -p password my_database > mydatabase.dump
We can fix this issue by creating a local file named
.my.cnf in the current user’s directory.
To be safe, I usually create a backup user
bkp and protect the
.my.cnf so only the
bkp can read/write to it:
cd /home/bkp touch .my.cnf chmod u=rw,go-rwx .my.cnf
Lastly, open up the
.my.cnf file and type the password as follows:
[client] user=mysql_user password="password" default-character-set=utf8mb4
Now you can access
mysqldump without password:
bkp@myserver:~# mysql mysql> show databases; ...
That’s it. 🥳