## page was renamed from Memo/DailyMysqlBackups
= 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.

 1. Follow the steps in ../CronScpBackup
 1. 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.
 1. 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 
}}}
 1. 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.
 1. 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!