How to restore large MySql database faster
Working with large data has its own thrills and challenges. Recently we faced a situation which forced us to innovate.
One of our clients is a large cement manufacturing firm. We have given them an analytics solution on top of Axiom platform.
The solution uses a MySql database, which has grown to around 7GB over the period of time. It is deployed in their own data centres, access to which is restricted. That means no longer we can provide support from our comfortable development centre. We have to personally go to one of their offices in our city, access the data centre via VPN and get the required information and debug in case of an error.
In some cases we need to restore the database in our local environment to debug. Now restoring 7GB (700 MB zipped) MySql database using traditional backup and restore utility is a very time consuming process especially when some large tables are using InnoDB. It used to take around 10 hours for backup and restore. We kept losing precious time in debugging process. We didn’t find any satisfactory solution on the Internet. We had to find a solution for this on our own.
Fortunately our brilliant team figured out a permanent solution. They devised a backup technique which reduces the restore time for large MySql databases.
Here is how it works:
After connecting to the remote MySql database…
- Generate a query to create table schema, procedures and functions of the source DB
- Generate a query to find all indexes of all tables except foreign key constraints of the source DB
- Generate a query to drop all the indexes found in step 2
- Generate a query to insert all data of the source DB
- Generate a query to create all the indexes found in step 2
- Write all queries in the above order in one .sql file which is your new MySql backup. Zip it using LZ4 compression.
- Now just restore the DB from this file using normal restore utility of MySql.
Initially on an Amazon Large instance, it used to take 2 hours to backup 7GB unzipped (700MB zipped) MySql database using mysqldump utility and 8 hours to restore i.e. Total 10 hours.
With this technique it takes 10 mins to backup the same database and 50 mins to restore i.e. savings of 9 hours!
npm install mysql
We are wondering if building an online tool would be a good idea as well.
Please let us know what you think.