MySQL Backup and Maintenance

Anyone operating a MySQL database server should have a sound maintenance and backup plan to either eliminate or significantly reduce the risk of data corruption or complete loss of data.  This article is the result of a recent event in which I nearly lost all my data.   MySQL can be effectively maintained using the following three task plan:

  1. Enable MySQL built-in auto recovery features
  2. Perform routine table checks, optimization, and repairs
  3. Database and binary log backups

Enable MySQL Built-in Auto-recovery Features

MySQL has a built-in auto recovery capability that can be used to add an additional level of recovery protection that helps guard against data corruption when the MySQL process is killed, dies, or fails.  There is a base set of recovery tasks the MySQL server will perform on InnoDB and Falcon storage based tables when it experiences a problem.  If you are running MyISAM table storage, you can explicitly enable an additional level of recovery that is specific to MyISAM tables only.  The following should be added to the startup/shutdown script or passed as an additional argument to the mysqld_safe script when starting the MySQL server:


where level:
BACKUP - create a backup of the table if the repair will require a change to the original table
FORCE -  force the recovery adjustments to a table even if more than 1 row of data will be lost
QUICK - quick recovery
DEFAULT - recover without any special handling

It is recommended that the following be used in the startup/shutdown script:


Every time MySQL starts up, this MyISAM recovery tasks will take place, checking and/or automatically correcting erros found.

Perform Routine Table Checks, Optimization, and Repairs

Tables should be actively maintained and checked regularly as a proactive measure of problem prevention.  When problems do arise, this step will help minimize the impact of the problem on the server and data.  The mysqlcheck binary program can be used to perform most of these checks.  The mysqlcheck program can be used to perform database table consistency checks, table optimization, table analysis, or table repair by passing different arguments to the CLI.

TIP: you can create renamed binary copies if the mysqlcheck OR use symbolic links to make calls to the CLI more intuitive.  This is because there is a special feature in the code that permits the default behavior of the CLI to change when the file or link is named in a certain fashion.
You can achieve this behavior by:

cp mysqlcheck mysqlrepair
cp mysqlcheck mysqloptimize
cp mysqlcheck mysqlanalyze


ln -s mysqlcheck mysqlrepair
ln -s mysqlcheck mysqloptimize
ln -s mysqlcheck mysqlanalyze

It is rec commended that this CLI be added to the crontab of the UNIX user account that mysql runs under.

create a ~/.my.cnf file with login credentials so that the MySQL CLIs can run non-interactively in cron and be able to authenticate


Protect the file by tightening the UNIX file permissions so that only the UNIX user and/or root may view the file, since it has a password in clear text.

chmod 600 ~/.my.cnf
ls -lta .my.cnf
-rw------- 1 mysql mysql 37 2008-12-14 12:01 .my.cnf

Add the following to the crontab of the UNIX user (in most cases mysql) that MySQL runs under:

0 0 * * 0 /usr/local/mysql/bin/mysqlcheck --all-databases --check-only-changed --silent

When a table is being checked by mysqlcheck, it cannot be updated.  So, performing mysqlcheck on large tables should be used at your discretion.  It may not make sense to perform mysqlcheck on large tables if you cannot afford the table to be unavailable for updates.
Alternatively, you could create the .my.cnf in the /root directory on Linux systems, and place the job into the /etc/cron.daily directory.  On Fedora 10, I placed the .my.cnf file in the /root directory.  An executable shell script with the mysqlcheck command was placed in the /etc/cron.daily directory. The /etc/crontab script on linux calls a run-parts script which runs all scripts it finds in the /etc/cron.daily, /etc/cron.hourly, /etc/cron.weekly, and /etc/cron.monthly directories.

Database and Binary Log Backup

Database backups can be used to restore data, provide a means for transporting the data to a different database system, or used to establish a replication partner.  Whatever the reason, you should always have a sound MySQL backup and restore plan.  MySQL backups can be performed one of two ways:

  • text-formatted backups – MySQL provides a CLI called mysqldump that can be used to write the contents of database tables to ASCII text dump files.  This tool has the ability to dump the SQL to rebuild the table schema, as well as, the data rows.
  • Binary backups – Binary backups can be made by simply copying the physical data files containing the table contents.  These backups can be performed using standard UNIX commands such as cp, tar, rsync.  Additionally, there is a utility called mysqlhotcopy.

There are pros and cons for each method of backup.  Most of the binary backup methods require file operations to be performed without the database running.  The mysqldump CLI can be used while the server is running, but it is considerably slower than binary backup methods.  Mysqldump output is very portable which means the dumps can be transferred and loaded on different systems.  Binary backup files, in some cases, are  not quite as portable.  It is important to simply choose the best method given your environment and requirements.  In this article, we choose the mysqldump utility.  Once a method is chosen, it is important to develop and follow a regimen to achieve consistent and usable backups.  The following are some useful guidelines:

  • perform regularly scheduled backups.  Establish and schedule when the mysqldump program is run.
  • configure MySQL with binary logging enabled.  Binary logging is essentially a transaction log of database commits.  To fully recover a system that has crashed, you need to restore the last full dump, as well as, all binary logs that contain transactions subsequent to that dump.  Enabling binary logging will not represent much overhead to the server.
  • consistently name all database dumps.  There are two identifiers that should be used in naming your database dumps.  There is the name of the database itself and the time-stamp of when the dump was created.  These two identifiers should be used in the file names to help easily identify backup sets.
  • filesystem backup should be performed.  You must ensure that the dumps are not only backed up to the local filesystems, but you should ensure that they are copied to an alternate filesystem, preferably on a different host or backup medium.  This will ensure that if you lost the entire system running the MySQL application, you could rebuild and ultimately restore the database.
  • create rolling backup sets.  A backup strategy should limit the sets of backup files to a specific time span.  In other words, expire or remove older sets of backup files.  Do this to prevent backup sets from filling your disk with redundant and/or old copies of the backup data.  For example, you could keep a rolling set of backup files that consist of one full dump of each database per day.

The easiest way to set up a consistent and regularly scheduled backup is to create a shell script and simply cron that job to according to your desired schedule.  The script below is an example of a backup strategy:

DATETIME=`date +%m%d%Y`

# individual database backups
for DB in db1 db2 db_n
        /usr/bin/mysqldump ${DB} > ${DUMPDIR}/${DB}.${DATETIME}
        /usr/bin/gzip -f ${DUMPDIR}/${DB}.${DATETIME}

# full database backup includes all databases
/usr/bin/mysqldump --all-databases > ${DUMPDIR}/full-dump.${DATETIME}
/usr/bin/gzip -f ${DUMPDIR}/full-dump.${DATETIME}

# Remove older backups > 7 days
/usr/bin/find ${DUMPDIR} -mtime +7 -print0 | xargs rm -rf
/usr/bin/find /mnt/samba -mtime +7 -print0 | xargs rm -rf 

# code to make off-line copies could go here....
# e.g. to samba mount point
/bin/cp -r ${DUMPDIR} /mnt/samba

In this script, several databases db1, db2, db_n are backed up using mysqldump into a dumps directory.  The files are compressed with gzip to reduce file size.  Additionally, a full dump is made as well.  The script also removes any dump file that has older than one week old.  Lastly, the script could be made to create off-line copies of the data as well to either NAS, tape, CD-RW, or some other off-line filesystem.  At this point, all that’s left is to ensure this gets defined in cron, and that the files are copied off to another filesystem or backup medium.  Once again, this backup script could easily be placed in to the /etc/cron.daily directory on a Linux system or it could be added to a user’s crontab file.