It’s really easy to set up automatic MySQL backups using mysqldump. First, you need to set up a user with SELECT and LOCK TABLES privileges. In this example the user doesn’t have a password.

CREATE USER 'autobackup'@'localhost';
GRANT SELECT, LOCK TABLES ON *.* TO 'autobackup'@'localhost';

Next create the cron job with crontab -e. This job is set to run every day at 5:20am.

20 5 * * * mysqldump --user=autobackup **dbname** | gzip -c > /var/backups/**dbname**-`/bin/date +\%Y\%m\%d`.sql.gz

Don’t forget to change dbname to the name of the database that you want to backup. And that’s it - you’re done! This cron job will create a backup of your database and save it to /var/backups with a filename based on the current date, e.g. /var/backups/**dbname**-20120503.sql.gz

Have any questions or comments? Feel free to reach out on Twitter: @Joseph_Wynn

This post was published on 3 May, 2012 and last updated on 1 January, 2017. It was posted in [MySQL, Server Administration] and tagged with []