How to Backup a MyISAM / InnoDB MySQL Database

MySQL is not perfect.

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:

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