Devart Blog

How To: Export/Import Large MySQL Database

Posted by on July 23rd, 2010

MySQL is frequently referred to as a database for Web applications. Partially it is really so, because MySQL became popular owing to its simplicity, high speed, and bounding with PHP. Developers of small Web projects often choose MySQL as a back end of their sites. Does this mean that MySQL can be used only for small databases? Not at all. There are lots of databases size of data in which is measured in gigabytes. Besides MySQL servers are frequently clustered to increase their performance. When a DBAs work with large amounts of data, they frequently have to make backup copies correctly and effectively, i. e. to export MySQL databases to SQL (or MySQL backup). It is extremely important to import MySQL database from SQL correctly is when restoring a corrupted database and when migrating a database from one server to another.

What should be taken into account when exporting a large MySQL database?

Making a backup copy of a large database takes a lot of time. During this time some user can try to modify data in this database. But we want to get an all-of-a-piece database snapshot. For different tables this can be done in different ways:

  • for InnoDB tables a separate transaction should be started
  • MyISAM tables should be locked by FLUSH TABLES WITH READ LOCK

Except taking a lot of time export and import of a MySQL database implies transferring a large amount of data between client and server. The most effective way of reducing the amount of data transferred through the net and the amount of the used disk space is compression. All points mentioned above are taken into account in the utility for making database backups included in the set of tools available in dbForge Studio for MySQL. The rest of the article is written referring to this utility.

Step-by-step MySQL backup procedure

1. Set compression for the connection

Using compression for a connection allows to reduce net traffic owing to strings compression. The Use Compression option is available on the Advanced page of the Database Connection Properties dialog. But if the amount of string data per record is not large, you won’t gain much this way.

2. Choose a database for export and open Database Backup Wizard

In the Database Explorer tree choose the nod of the needed database or connection. Choose Backup Database from the pop-up menu. On the image below we show the main settings of MySQL backup. Pay your attention to the fact that we are using compression to reduce the disk space usage. Besides using compression allows to add comments to the backup.

Database Backup Wizard - General Page

3. Set options to create an all-of-a-piece database snapshot

Earlier in this article we wrote about the necessity of avoiding data corruption while creating a database backup. There are options to achieve this, and they are marked red on the image below:

Database Backup Wizard - Options Page

4. Make a backup of your MySQL database

After performing all necessary settings press the Backup button and wait until the application finishes export. During the operation you will be able to see the progress of backup creation and its stages.

Step-by-step MySQL import procedure

So, we’ve performed database export and compressed the results into a ZIP-archive. And now we have to import this database on the new server. Will we have to decompress a large script and try to execute it manually? Not at all. Database Import can be performed neatly and easily with the help of Database Restore Wizard

1. Choose a database to import and open Database Restore Wizard

In the Database Explorer tree choose the nod of the needed database or connection. Choose Restore Database from the pop-up menu. Choose the backup file in the window that opened. After you’ve chosen the file you should see approximately the same as shown on the image below:

Database Restore Wizard

The program finds the *.sql file in the archive automatically and shows the comment we’ve added when creating the backup. Now we are ready to import our MySQL database.

2. Import the database

After pressing the Restore button the program will automatically restore the database from the backup.

Conclusion

In this article we’ve reviewed some aspects of making backups of MySQL databases and illustrated capabilities of dbForge Studio for MySQL regarding export/import. You can download dbForge Studio for MySQL here.

2 Responses to “How To: Export/Import Large MySQL Database”

  1. Mark R Says:

    Importing / Exporting large MySQL databases can’t be done at all with mysqldump because it is too slow – especially for restore.

    This is why any significant sized system needs to do something else – typically either volume snapshots, rsync, filesystem dump from replication slaves, etc.

    What you’re suggesting simply isn’t practical with a multi-Tb database server.

  2. Aleksandr Serdyuk Says:

    I agree with you that in case when you need a regular backup for multi-terabyte database it is faster to suspend MySQL server and dump file system. Because our product is more developer-oriented than DBA-oriented, article merely talks about making development snapshots (i.e. exports). And there are cases when database developer simply is not allowed to accomplish all operations you described. So he must wait for export to complete.

Leave a Reply