Daily (Nightly) MySQL Backups
This document will explain how to do automated daily MySQL backups to a remote using Cron and SCP.
InstructionsThese instructions will walk you through what it takes.
1. Follow the steps in our Cron and SCP Backup DocumentDocument
2. Create a user in mysql to use for backupsThis user should only have SELECT privileges on the particular database to backup and only from localhost.
The LOCK TABLES is required for doing a mysqldump.
mysql -uroot -p mysql
> Enter password
mysql> GRANT SELECT,LOCK TABLES ON databasename.* TO backupuser@localhost;
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 backups.sh or something. chmod 644 ~/.ssh/authorized_keys
4. Test the scriptTest 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 backups.sh /etc/cron.daily/That should run every night at some point, whenever the cron.daily is set to run in your crontab. Voila!