How to Backup a MyISAM / InnoDB MySQL Database

Whichever MySQL storage engine you use, built in recovery is far from perfect and it’s only a matter of time until you’ll experience some corruption / data loss.

Plan ahead and implement daily or weekly backups of all data.

There are four primary ways to backup a MySQL database:

1. IBBackup

The recommended way to bakcup a MySQL database, is by using a script called InnoDBBackup by the makers of InnoDB.

Pros: Works with both MyISAM and InnoDB. Fast. Doesn’t lock tables
Cons: Takes a few hours to setup

2. MySQLDump

Available from the early days of MySQL, MySQLDump is a command line utility provided by MySQL that works very well

To run, use:

mysqldump –user=user –single-transaction –password=pass –opt DBNAME > dumpfile.sql

The –opt flag is very important. This is shorthand to pass in many flags at once; –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset. This ensures that your database is in a good state while the backup is performed, including restricting all write access while the backup is in operation. Any locks placed will be automatically removed when this utility finishes.

The –single-transaction flag is important for InnoDB tables. It starts a transaction and prevents any changes to the data while backup is running, to maintain data consistency.

Pros: Works with all table types.
Cons: Locks tables. Your database will be inaccessible while backup is running.

Note: If your database only consists of MyISAM tables, consider using mysqlhotcopy. It’s faster, but doesn’t work with InnoDB tables.

3. File copy

Copy the entire /data MySQL folder

Pros: Very easy to setup
Cons: You have to manually shutdown the database BEFORE starting the file copy, otherwise the backup will faill

4. MySQL Replication

Read our step-by-step guide about how to setup MySQL replication

Pros: The replication server can double as a live database, for performance (master-slave) and uptime (master-master)
Cons: Takes a few hours to setup