Automated MySQL Database Backup for BackupPC

By | 22. April 2014

BackupPC is my preferred backup solution for quite a long time now, as it is relatively easy to set up and backup and recovery works like a charm. One tricky aspect is the automated backup of MySQL databases. With this post I would like to present my solution, which backs up all databases, stores them with a time stamp, rotates them every N days and provides a nice and quick way to be used with backuppc.

I will assume the SSH user used for backups is called “backuppc”. That is the user BackupPC uses to perform the backups.

First, create a special user in MySQL with only the required privileges to dump the databases:

This creates a new MySQL user called “backup” with read-only privileges on all available databases. Next, in the home directory of the backuppc user, create the .my.cnf file to store the user and password data. This allows for automated usage of mysql and mysqldump without the need to enter a password:

The last line sets the permissions for the file so that only the owner can read the files, which includes the password in cleartext.

Finally, we need a directory where the backups should be stored. I chose /var/backup for that.

This creates the folders, sets some directory permissions and finally creates the backup script, which will later be executed remotely by BackupPC prior to the backup. Now edit the /var/backup/backup.sh with an editor of your choice and enter the following script:

You can test this script by simply executing it. It should create one folder per database, including the first gzip’ed backup of this database. Line 11 might be of interest for you: I chose to filter some databases which I do not need to restore in case of a system wide crash. You might want to adopt this, however…

Finally, we have to tell BackupPC what to do with this file. Go to your host and edit the config. At “Xfer => RsyncShareName”, simply add the “/var/backup” directory. In “Backup Settings”, override the “DumpPreUserCmd” and insert the following line, which instructs BackupPC to ssh to your server and execute the backup.sh script before the actual backup.

Et voila, BackupPC now backups all databases on your system. Questions, comments and improvements are welcome!

6 thoughts on “Automated MySQL Database Backup for BackupPC

  1. Guntis

    How do you restore all databases in case of system wide crash? Do you have script for that to do it all at once or you need to restore them one by one?

    Reply
  2. Jessica

    This is so slick. Thanks so much for posting it. I hope I did this right. I changed to N=2. Does that mean it will delete files older than 2 days? Also, what are you using for the code display? That is just too cool.

    Reply
    1. Daniel Herrmann Post author

      Hi Jessica,

      indeed, N=2 means that only the last two copies will be stored.

      Reply
  3. Mikale

    Guntis,
    You are just import the database ?

    Thanks for this wonderful guide works good!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.