networked day to day technical issues

8Apr/120

Fast MySQL database restore / import from full dump files

With MySQL Community Edition in most of the cases you have two ways of creating a full database backup:

  • using the command line utility mysqldump which works with both Myisam and Innodb tables, while the database server is running
  • shutting down the MySQL server and performing a copy of the full data dir in case of Innodb databases or just the database folder in the data dir in case of Innodb based databases

The full list of methods to do backups is available on Mysql's site.

While a binary backup will be the fastest to "restore" it has limitations, mainly that if using Innodb storage engine then you have to restore the whole MySQL instance and not just the specific database; and that you can safely restore on the same Mysql version (though it may work on newer ones too).
On the other hand a db dump created using mysqldump will allow you to restore only the needed database (or all of them if you want to and you have a full dump of all databases), it will allow you to restore on different Mysql versions as long as the features required are supported (if restoring on an older MySQL version) and it is also the most disk space efficient way to restore (see how MySQL manages disk space for Innodb tables)

The problem lies in the details and when restoring a large dump created with mysqldump you can disover it can take even days if the dump file is large (i've seen it for a 30GB dump file which isn't that large). The problem lies in the fact that the dump file is a series of SQL statements and each INSERT will trigger and index update.

To speed as much as possible a dump file import do as much as possible from the following list:

  1. using mysqldump create the dump files using the --opt option.

    Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.

    The most important one of the list is --disable-keys

    For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading
    the dump file faster because the indexes are created after all rows are inserted.

  2. Disable unique checks, foreign key checks and autocommit, explanation on mysql's site. You can do this either by editing the SQL dump file and adding at the top
    SET autocommit=0;
    SET unique_checks=0;
    SET foreign_key_checks=0;

    and at the end of the file append

    COMMIT;

    or you can edit /etc/mysql/my.cnf (adjust path according to your case) and in the [mysqld] section add

    init_connect='SET autocommit=0,unique_checks=0,foreign_key_checks=0'

    Save and restart mysqld in order for the new configuration changes to take effect.
    Be sure to read the explanation from Mysql in order to understand the impact of those three commands. Generally it is safe to run them if no other database is running on the same mysqld instance, but don't take my word on it.

  3. In mysqld config file (my.cnf) in the [mysqld] section set:
    innodb_flush_log_at_trx_commit = 2

    If you have a powerloss during the import then you will have data loss when running with this option set to a value different from 1. Again read the explanation from MySQL's site in order to understand the impact. If no other databases are running inside the MySQL instance this should be safe

Depending on your constrains you can choose to implement only part of the above advice.
Once the import is completed be sure to revert the changes in my.cnf and restart mysqld.

Filed under: Linux, mysql Leave a comment