How to configure mysqldump

Submitted by olaf on 2014-11-10

I use rsnapshot for my daily backup. Part of the backup is making a dump of MySQL databases.

Invoking mysqldump is pretty straightforward. mysqldump — A Database Backup Program lists all available options. Depending on your use case, you have two ways to do a backup, either all

mysqldump --all-databases >mysql-backup.sql

or only some databases

mysqldump --databases db1 db2 db3 >mysql-backup.sql

The tricky part - at least for me - was to provide the necessary credentials and privileges, so the backup could run unattended. As so often, it is pretty trivial, once you figure everything out.

The first step is to grant the privileges, so mysqldump can do its job. Fortunately, right at the beginning, the minimum privileges are listed

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

I have chosen a separate user responsible for database backups. To take action, issue

grant select, show view, trigger, lock tables on *.* to 'mysqldump'@'localhost' identified by 'SecreTpasswd';
flush privileges;

If you will dump only a few databases or just one, you can restrict this to specific databases of course

grant ... on db1.* to 'mysqldump'@'localhost' identified by ...;
grant ... on db2.* to 'mysqldump'@'localhost' identified by ...;

Next, you must provide mysqldump with the necessary credentials. This can be done with command line options. But doing that, exposes the password to anyone currently logged on to the machine. So, the right way is using an option file. In our case, this is just a few lines. I have put this into /etc/mysql/mysqldump.cnf

[mysqldump]
host     = localhost
user     = mysqldump
password = 'SecreTpasswd'

and changed the access mode to

chmod 600 /etc/mysql/mysqldump.cnf

Adding an appropriate option, so mysqldump knows where to look for credentials

mysqldump --defaults-file=/etc/mysql/mysqldump.cnf --all-databases >mysql-backup.sql

I wrapped this in a small shell script, which can be used by rsnapshot. Finally, adding this line to /etc/rsnapshot.conf

backup_script	/bin/sh /usr/local/sbin/mysqldump.sh	mysqldump

completes the task of automating the daily MySQL backup. Remember, rsnapshot requires tab characters between elements.

Post a comment

All comments are held for moderation; Markdown and basic HTML formatting accepted. If you want to stay anonymous, leave name, e-mail and website empty.