06 Jul 2008
MySQL Backups

Even if you consistently back up your filesystem, you should also back up your databases separately, using an appropriate tool to maintain their relational integrity and to provide for recovery independent of the filesystem.

MySQL is a pervasive, lightweight RDBMS that is used with popular applications such as WordPress and Bugzilla. The mysqldump utility which comes with MySQL provides a quick and simple way to create database backups.

While some situations my require more elaborate techniques, these scripts, using mysqldump, should be fine for the most common uses. They are run nightly via cron and keep a week’s worth of backups. Note: these are just examples. If you use them, you do so at your own risk. You’ll want to adjust them for your own situation.

This first script (text) runs on my laptop. Because it accesses the local server, has full permissions, and the databases are small, it locks and backs up everything at once.

  1. #!/bin/bash
  2. # @(#) $Id$
  3.  
  4. BCKDIR=$HOME/backups/mysql
  5. BCKFILE="mysql-backup-`date +%a`.sql"
  6.  
  7. [ ! -d $BCKDIR ] && mkdir -p $BCKDIR
  8.  
  9. echo "`date` : starting backup"
  10.  
  11. mysqldump > $BCKDIR/$BCKFILE    \
  12.     –opt                       \
  13.     –allow-keywords            \
  14.     –comments                  \
  15.     –create-options            \
  16.     –disable-keys              \
  17.     –flush-logs                \
  18.     –flush-privileges          \
  19.     –lock-all-tables           \
  20.     –all-databases
  21.  
  22. echo "`date` : completed"
  23.  
  24. ls -lh $BCKDIR/$BCKFILE
  25.  
  26. # end

This second script (text) runs on my blog host; the actual database is on another host. It differs from the previous script in that the flush-logs and lock-all-tables can’t be used, only a single database is backed up, and a password is required. I’ve also added the uptime command so that I can see how the server is performing when backups are run. Note: I don’t recommend embedding passwords in scripts, but it makes the example simpler.

  1. #!/bin/bash
  2. # @(#) $Id$
  3.  
  4. DBNAME=cutedbname
  5. DBHOST=mysql.example.com
  6. DBPASS=noway
  7.  
  8. BCKDIR=$HOME/backups/mysql
  9. BCKFILE="$DBNAME-`date +%a`.sql"
  10.  
  11. uptime
  12.  
  13. [ ! -d $BCKDIR ] && mkdir -p $BCKDIR
  14.  
  15. echo "`date` : starting backup"
  16.  
  17. # I don't have RELOAD privs, so can't use
  18. #   –flush-logs
  19. #   –lock-all-tables
  20.  
  21. mysqldump > $BCKDIR/$BCKFILE    \
  22.     —host=$DBHOST              \
  23.     –opt                       \
  24.     –allow-keywords            \
  25.     –comments                  \
  26.     –create-options            \
  27.     –disable-keys              \
  28.     —password=$DBPASS          \
  29.     $DBNAME
  30.  
  31. echo "`date` : completed"
  32.  
  33. chmod u=rw,g=,o= $BCKDIR/$BCKFILE
  34. ls -lh $BCKDIR/$BCKFILE
  35.  
  36. uptime
  37.  
  38. # end

In both cases, cron emails the log file to me. A typical message looks like this:

  1.  04:13:02 up 7 days, 19:24,  1 user,  load average: 1.68, 2.60, 2.87
  2. Fri Jul  4 04:13:02 CDT 2008 : starting backup
  3. Fri Jul  4 04:13:02 CDT 2008 : completed
  4. -rw——-  1 xuser xgroup 572K 2008-07-04 04:13 /home/xuser/backups/mysql/cutedbname-Fri.sql
  5.  04:13:02 up 7 days, 19:24,  1 user,  load average: 1.68, 2.60, 2.87

I periodically rsync my entire remote site to local storage — having the backups on the same remote host as your live data won’t do you any good if your host loses a disk (and have stale or corrupted backups), or falls off the ‘Net. Of course my own filesystem backups take care of having long term, off-the-machine backups of my local stuff. I’ll discuss the filesystem backups in a future post.

I hope you find these scripts useful, and welcome comments, critiques, or suggestions for alternative methods.

Update (7/7): added link to filesystem backups

Category: programming
Tags: , , ,
(comments closed) | (trackbacks closed) | Permalink | Subscribe to comments |

Site last updated 2015-01-12 @ 13:31:07; This content last updated 2009-09-08 @ 06:10:01