I’ve been working on a PHP/MySQL project for the last few months that got to a point where database size matters in both running the queries as well as exporting (backing-up) and importing (restoring) the database. The database is about 800MB and it holds two tables with more than 1 million and half rows and other few tables with more than 500.000 rows each, all of them having indexes on 3 to 8 fields. I’m not gonna talk now about optimizing queries or the database structure to make a large database like this run faster, but rather focus on the second problem this database size and structure poses to a developer (at least on a Windows machine): using MySQL commands, even from the command line, to import a MySQL dump of this database, with millions of rows and large indexes needed to be rebuilt on each row takes hours, literally.
The closest working solution that I’ve come with about a month ago was to create a query that removed the indexes from the large tables, another one that would restore them back and do the import in 3 steps:
- run the first sql query to remove indexes on the problematic tables
- run the MySQL import for the whole MySQL dump (only import data, since dropping and creating tables would have put back the indexes before the import – which of course I didn;t want)
- run the second query to restore the indexes on the large tables
Using this technique I’ve managed to import the database in about 40 minutes, which is not that bad, compared to the normal MySQLdump import (with indexes in place) that would have taken hours. But still, it felt like there should have been a better solution to this.
And doing some further research on the matter, I’ve decided to try some MySQL administration tools, hoping they have implemented some optimized methods of doing the backup/restore (or export/import) for large MySQL databases. And the obvious first choice was MySQL’s own application – MySQL Administrator – which is part of a powerful package called MySQL GUI Tools (soon to be replaced with MySQL Workbench).
This is how MySQL Administrator interface looks like after starting and connecting it to the server
There are a lot of things you can do with this tool connected to your MySQL server – just go through all the links on the left panel – but, for now, I’m only going to discuss the two highlighted items there:
- Backup – which will be responsible for creating the mysqldump
- Restore – which does the magic – import the MySQL dump in a very efficient and fast way
Both steps are pretty easy to setup and use. Here’s a short going through
Backup your MySQL Database
- Select the “Backup” link in the left menu of MySQL Administrator GUI
- Click on “New Project” link on the buttom
- Insert the project name
- Select MySQL database(s) to backup
- Optional: go to “Advanced Options” tab and make the desired settings there
- Click save project
- Click “Execute Backup Now” if you want to make the backup at this moment
Restore your Large MySQL Database
This is even easier
- Go to the “Restore” link on the left panel
- Click on “Open Backup File” on the bottom right and browse to the MySQL backup file you want to restore
- Once selected the file you have the option to select another database (schema) in which to import the tables
- You also have some other options, but usually the default ones work fine
- Hit “Start Restore” button on the bottom right to start importing your large MySQL file
- That’s it!
Your import should start and go quite fast. During the export and import of the MySQL files, the application shows live progress bars as well as statistics with the amount of information processed, the number of tables, time elapsed and estimated etc. so it’s very easy to see where your export/import operation is and make an estimate on when it’ll finish.
Another big plus is that the MySQL Administrator tool doesn’t “kill” your processor(s) and RAM, as it’s the case with most other methods and applications I’ve tried!
Using this technique I’ve managed to do a complete backup and restore of the 800MB database described at the beginning of this post in about 12 minutes (4.5 minutes for export and 7.5minutes for import) – both locally and over a remote connection from a machine running Windows 7 and MySQL 5.1.