How to Export/Import Large MySQL Database with a MariaDB GUI Tool

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 when restoring a corrupted database and when migrating a database from one server to another.

Modern tools such as a MariaDB GUI tool can simplify backup, restore, and migration operations for both MySQL and MariaDB environments, especially when working with large databases and complex infrastructures.

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. This MariaDB GUI tool also helps automate backup and restore workflows for large databases. 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 node 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 available in dbForge Studio for MySQL, which can also serve as a convenient MariaDB GUI tool for database administration tasks.

1. Choose a database to import and open Database Restore Wizard In the Database Explorer, choose the node 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. Along with MySQL administration, the tool can also be used as a MariaDB GUI tool to simplify backup management, migration, and database maintenance workflows.