Simple strategy for MySQL backups

I now have a good amount of data in my blog that I would be very sad if I lost. As a precautionary measure I decided to build a little system that will backup my data regularly so I’m prepared in case of a disaster.

The strategy

The strategy is going to be very simple. I’m going to create a user in my database that has read permissions on the tables I want to backup. This user will run mysqldump from a different machine and will save the backups there. I will create a cron job that will do this once a day.

Creating a read-only MySQL user

Log into MySQL with a user that has enough power to create new users. And create a backup user:

CREATE USER 'backup-user'@'%' IDENTIFIED BY 'somepassword';
GRANT SELECT ON dbname.* TO 'backup-user'@'%';

Testing the user

With the user created, we can test our back-up powers:

mysqldump --single-transaction -u backup-user -h myhost -p'somepassword' dbname > $(date +%F_%S)-dbname.sql

You can see that the file we are dumping to has a weird name: $(date +%F_%S)-dbname.sql. This prepends the current date to the file name. This way you can create multiple backups in the same folder.

Creating a CRON job

Lastly we need to add this to a cron job:

19 4 * * * mysqldump --single-transaction -u backup-user -h myhost -p'somepassword' dbname > /backups/$(date +%F_%S)-dbname.sql

And the backup will be generated every day at 4 am.

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Time limit is exhausted. Please reload CAPTCHA.