Backup and Restore Large MySQL Database on Windows Platform Fast
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. Read more