Saturday, January 17, 2004

Daily (Nightly) MySQL Backups


This document will explain how to do automated daily MySQL backups to a remote using Cron and SCP.

These instructions will walk you through what it takes.

1. Follow the steps in our Cron and SCP Backup Document


2. Create a user in mysql to use for backups

This user should only have SELECT privileges on the particular database to backup and only from localhost.

mysql -uroot -p mysql

> Enter password

mysql> GRANT SELECT,LOCK TABLES ON databasename.* TO backupuser@localhost;

The LOCK TABLES is required for doing a mysqldump.

3. Now create a script for cron to run

This sample script below will dump the entire database identified by databasename

mysqldump --opt -ubackupuser DATABASENAME > /home/temp/databasename.sql

scp /home/temp/databasename.sql REMOTE_USER@REMOTE_HOST:BACKUPS/

Make sure the BACKUPS/ directory is available under the REMOTE_USER directory on the REMOTE_HOST computer that you wish to backup to. Save the script, call it or something. chmod 644 ~/.ssh/authorized_keys

4. Test the script

Test the script to see if it works ok.


After it completes, you should check the BACKUPS directory on the remote host and make sure the backup file is there.

5. Move script to /etc/cron.daily/ directory

mv /etc/cron.daily/

That should run every night at some point, whenever the cron.daily is set to run in your crontab. Voila!