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

  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 backups.sh 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 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!

JunHu: JunHu/Memo/DailyMysqlBackups (last edited 2008-10-03 20:19:18 by localhost)