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.

    ./backups.sh
    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)