MySQL Backup and Recovery Methodologies

By David Yin on February 17, 2008 6:03 PM | Permalink | Comments (0) | TrackBacks (0)

MySQL is very popular. Many open source or free software use MySQL as their main database engine.

To make our data safe, please remember to backup your data regularly.
There are two methodologies you can use to back up MySQL databases without spending money.

The first is to create a series of SQL statements that rebuild one or more databases. This is usually accomplished via the mysqldump script.

The second is to copy the database files themselves if you can ensure they're not being changed during the backup. This is currently possible only for MyI*SAM archive tables using the mysqlhotcopy script.

Let's go into the details of how to back up MySQL database.

Backing up MyISAM tables
The folloing command creates a SQL dump of all MySQL databases in a given instance, locks all tables as necessary, flushes the logs, and writes the name of the current binary log to the output. The second command couses MySQL to switch binary logfiles so that you can back up the logfiles that was used during the backup.

$ mysqldump --all-databases --lock-all-tables --flush-logs --master-data=2 > all_databases.sql

$ mysqladmin --flush-logs

Backing up InnoDB tables

$ mysqldump --all-databases --single-transaction --flush-logs --master-data=2
> all_databases.sql
$ mysqldump --database=mysql --lock-all-tables --flush-logs --master-data=2
> system.sql
$ mysqladmin --flush-logs

The first command backs up all databases, flushes the logs, and write the name of the current binary log to the output. The second command backs up just the mysql database using the --lock-all-tables option, which is the proper way to back up that database because it uses the MyISAM storage engine. The third command causes MySQL to switch binary logfiles so that you can back up the logfile that was used during the backup.

Resorte MySQL

$ mysql -u username -p < all_databases.sql

If you like this post? Buy me a coffee!