How to configure mysqldump
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
[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
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.