Database restoration for site with DB size more than 25 GB

[abhijitkumbhar001]

Hello,

I want to restore a SQL file with size more than 25 GB what process I need to follow that, and how much time it will take ?

[revant_one]

@revant_one

In mysql shell execute, use {db_name}; source /path/to/database.sql. This will restore the database from backup on fresh db. We use source from mysql shell as it re-connects in case of disconnection or timeout during restoration of huge database. Simple alternate command will be mysql -uroot -p{root-password} -h{hostname} {db_name} < /path/to/database.sql

More: Moving to docker - #6 by revant_one

[brian_pond]

@abhijitkumbhar001

how much time it will take ?

The answer depends a lot on hardware, and a few other factors.

Last year I did several SQL restores of a ~22 GB database. I did not take accurate measurements, but the average time was approximately 60-90 minutes.

[pioneerpathan]

For the comparatively fast DB restoration, stop all the processes except the MySQL process, if possible. So that all resources will be utilized for the MySQL process. In our case, tabVersion and tabWorkflowAction tables were having a high volume of data. You may observe the restoration process by SHOW PROCESS LIST; MySQL command in a separate terminal during restoration.

As someone rightly said, it depends on the hardware. We had a DB of around 60 GB size which took 2 hours and 30 minutes approximately. Make sure to tune the “max_allowed_packet” MySQL configuration parameter to avoid abrupt termination of the data restoration process.

[mysqld]
max_allowed_packet              = 2G