Saturday, January 17, 2004

Daily (Nightly) MySQL Backups

Introduction

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 our Cron and SCP Backup Document

Document

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!