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:
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:
Both steps are pretty easy to setup and use. Here’s a short going through
This is even easier
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.