I am Charley Cai

Due to the way Innodb tables are structured, you cannot perform a hotback up of this database without using third party tools. Therefore you must take your database offline for a small period of time while you take the backup.
Backing up MySQL
MySQL backups are performed using the common mysqldump tool. This is a command line utility that ships with MySQL and you use at as follows:

% mysqldump –user=user –password=pass –opt DBNAME > dumpfile.sql

You may also need to specify the –host= parameter to force the hostname you are connecting to. This depends largely on how you’ve setup your user security. This will produce a text file with a series of INSERT/DROP/CREATE SQL statements that will recreate the database.

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.

Restoring a backup
Restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This turns off all the checks and auto-commits. This is a safe operation to do if you are fully restoring a database since the previous dump has already been validated for legal keys. If however you are importing, or adding to an existing database, then this step is not advisable.

You can then easily import the SQL file into MySQL using:

% mysql –user=user –password=pass DBNAME < dumpfile.sql

or using

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name
mysql> SET FOREIGN_KEY_CHECKS = 1;
mysql> COMMIT;

Backing up without taking MySQL offline
You cannot perform this with Innodb with the tools supplied with MySQL. However you can get around this limitation by running two MySQL database instances. You setup another MySQL database running on another machine, that is a replication server. Replication features come part of the standard MySQL installation. This replication server can be taken offline for as long as you wish while the backup is performed from the replicated server. Once online, it will resync itself up with any operations that were done while it was offline.

Tags: mysql innodb backup restore

This entry was posted on Monday, June 8th, 2009 at 12:44 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.