Daily (Nightly) MySQL Backups
This document will explain how to do automated daily MySQL backups to a remote using Cron and SCP. Instructions
These instructions will walk you through what it takes.
Follow the steps in ../CronScpBackup
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.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 backups.sh or something.
chmod 644 ~/.ssh/authorized_keys
Test the script. Test the script to see if it works ok.
./backups.sh
After it completes, you should check the BACKUPS directory on the remote host and make sure the backup file is there.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!