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:

    Filed under: Linux, mysql Continue reading