Backing up Your MySQL databases with mysqldump

Backing up your databases is a little bit different than backing up your files. You don't need to backup the entire database directory; rather you just need to backup a dump file. MySQL provides a great way to to this: mysqldump

The Basic mysqldump Command

To begin, you'll want to create a directory to put the backup files into:

sudo mkdir /backup/db

Producing a backup file from MySQL is very easy. You just need to use the mysqldump utility.

(The following command needs to be entered on a single line)

sudo mysqldump -u dbuser -h dbserver -p dbpassword --all-databases > /backup/db/all-db.sql

You can also create backups for specific databases:

sudo mysqldump -u dbuser -h dbserver -p dbpassword --database db01 > /backup/db/db01.sql
sudo mysqldump -u dbuser -h dbserver -p dbpassword --database db02 > /backup/db/db02.sql

Essentially, these commands will place both the SQL commands to re-create your database tables, views, stored procedures, etc. as well as the data contained within the database in the destination file.

Using --tab with mysqldump

You can also separate the CREATE logic from the data with mysqlbackup
(Note that you'll probably want to make a directory for each database):

sudo mysqlbackup -u dbuser -h dbserver -p dbpassword --tab=/backup/db01 db01
sudo mysqlbackup -u dbuser -h dbserver -p dbpassword --tab=/backup/db02 db02

  • Why might I want to use --tab? »
  • « OK — Hide this

Using --tab will create separate data (tab delimited .txt files) and CREATE logic (.sql files) for each table in your database. If the tables are dumped into separate files, then using --tab will allow rsync or rsnapshot to make incremental backups within your database .

For example, suppose you have one table corrupt in your database. If you have backed up with --tab, then you can restore just the corrupted table without having to revert the entire database back to the backup point.

Do It Yourself mysqldump Scripting

If you only have a database or two that you need to backup, you'll probably just want to write a short shell script to do the backup for you. All that you need to do is:

  1. Paste one of the commands from above into a file (making sure to remove sudo).
  2. Save the file in the /backup directory (e.g.: /backup/mysql-backups.sh)
  3. Make the backup script executable:

    sudo chmod +x /backup/mysql-backups.sh

Using AutoMySQLBackup

If you have several databases that change frequently, then you'll probably want to take a look at using AutoMySQLBackup. Essentially, AutoMySQLBackup is a shell script for mysqldump written by a professional who has been kind enough to share.

Not only does it handle daily backups, it also keeps weekly and monthly backups too! To use AutoMySQLBackup:

1. Download AutoMySQLBackup from the SourceForge site.
You can download the file anywhere, but ultimately you'll want to save it in your /backup directory.
2. Edit the script file to suit your enviornment
Editing is pretty straight forward -- and the file is very well documented with comments. If you understand the basics of mysqlbackup, then the file is a snap to configure.
  • Show me some suggested edits »
  • « OK — Hide this

Basic Options

USERNAME=your-sql-username
PASSWORD=your-sql-password
DBHOST=hostname-of-your-db-server
DBNAMES="all"
BACKUPDIR="/backup/db"
MAILCONTENT="log"
MAXATTSIZE="4000"
MAILADDR="your-email@domain.com"

Advanced Options

MDBNAMES="${DBNAMES}"
DBEXCLUDE=""
CREATE_DATABASE=yes
SEPDIR=yes
DOWEEKLY=6
COMP=bzip
COMMCOMP=no

The remainder of the default options should suit most backup schemes just fine.

3. Make /backup/automysqlbackup.sh executable
sudo chmod +x /backup/automysqlbackup.sh

Automate the Database Backup with cron

Once you have your backup script created and saved, you need only to add a cron job to automate it. To automate a cron job for your script:

  1. At terminal, add a cron job for the superuser:

    sudo crontab -e

  2. Enter the daily schedule command for automysqlbackup.sh

    For example, to run automysqlbackup.sh every night at 11:42 PM, you would enter:

    42 23 * * * /backup/automysqlbackup.sh

    • What are the crontab fields? »
    • « Hide this

    The fields in crontab (separated by s p a c e s or tabs) are:

    [minute] [hour] [day of month] [month] [weekday] [command] [command args]
    Use commas to designate a more than one value: 00,09,11,22
    Use hyphens to designate a range of values: 0-6

  3. Save your crontab file and close your text editor. You\'re all set!

Should you have questions or need help: