Backing up And Restoring MySQL Innodb Database

Backing up And Restoring MySQL Innodb Database

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> USE db_name

mysql> SET AUTOCOMMIT = 0;

mysql> SET UNIQUE_CHECKS = 0;

mysql> SET FOREIGN_KEY_CHECKS = 0;

mysql> SOURCE dump_file_name

mysql> SET UNIQUE_CHECKS = 1;

mysql> SET FOREIGN_KEY_CHECKS = 1;

mysql> COMMIT;

mysql> SET AUTOCOMMIT = 1;

Note: after done some testings, specifying the autocommit = 0 command will not work, if your sql dump file contains LOCK TABLES WRITE statement. Because the LOCK TABLES WRITE statement seems automatically does "COMMIT;".

According to mysql manual says: Locks may be used to emulate transactions or to get more speed when updating tables.

http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html

Note: how I do testing is I tried to import and export sql dump file, opened up the dump file, and observe the binary log file.