MySQL is frequently referred to as a database for Web applications. Partially this is because MySQL became popular owing to its simplicity, high speed, and bounding with PHP. Developers of small Web projects often choose MySQL as the 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 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 users 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 reducing 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 disk space usage. Besides using compression allows adding comments to the backup.
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:
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, 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:
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 the capabilities of dbForge Studio for MySQL regarding export/import.
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.
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.