Safer mysqldump with mysql_config_editor
MySQL 5.6 now issues messages whenever you use passwords on your command line. It’s definitely a good security improvement. However, if you have cronjobs setup to backup your database you might get a lot of emails or logs with those warnings.
The best and safest way to stop getting those messages is using the new utility mysql_config_editor which has also been implemented in the 5.6 version. As the documentation says:
The mysql_config_editor utility (available as of MySQL 5.6.6) enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf. The file location is the %APPDATA%\MySQL directory on Windows and the current user’s home directory on non-Windows systems. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.
Below you have a case of how to get your bash script for my daily mysqldump database backups to work more securely:
1) 1st I use mysql_config_editor, which comes within MySQL 5.6+, to set up the encrypted password file. Suppose your username is “my_db_user”.
$ mysql_config_editor set --login-path=local --host=localhost --user=my_db_user --password
It will prompt for the password. Once you enter it, the user/pass are saved encrypted in your /home/[user_who_ran_mysql_config_editor]/.mylogin.cnf
2) Now on, for any of your bash scripts you can write something like this:
$ mysqldump -u my_db_user -pinsecure_password my_database | gzip > my_database_backup.tar.gz
$ mysqldump --login-path=local my_database | gzip > my_database_backup.tar.gz
This way avoids exposed passwords in your server or applications as I did in “Automated backups with MySQL using cronjobs“. =p