Question :
I have a 200+GB mysql dump file that I need to import into the local sql server running on my development machine. I attempted a naive dump restore using the mysql < db_backup.sql approach and managed to restore around 85% of the database before hitting a
mysql server has gone away
error. This took in the region of 5 days.
I’d like to know if there’s a way of comparing the partially restored DB to the dump file to understand which tables are still pending or if there’s a significantly (at least 4x) quicker method to restore from a huge dump file.
Any advice would be appreciated. Thanks.
Answer :
Thank You for additional information
If it standard and default settings, it not optimal for huge data, so You can start from:
- locate my.cnf file (it could be few different locations, depending
from installation) - in mysql run SHOW VARIABLES LIKE ‘innodb_file_per_table’; if it OFF –
good to drop ibdata file and re-create it
adjust for start :
innodb_buffer_pool_size = 4G (or 8G)
innodb_log_file_size = 1G
innodb_file_per_table= ON (if it OFF)
innodb_flush_log_at_trx_commit = 0 (because it development machine)
innodb_log_buffer_size = 64M
max_allowed_packet = 128M
It must increase speed of work
if innodb_file_per_table= OFF it mean all You databases in single files and mysql will not release space when You delete and/or optimise tables
with innodb_file_per_table= ON, effect from TRUNCATE/OPTIMISE available momentary and this is important for dev machine.
All above work always and if You decide re-load all dump, and if will go other way.
For already restored:
- check in MySQL “SHOW DATABASES”, by default it dump and restore in
alphabetical order, so question will be for last in list - for last database, compare table by table with original (if available), but better re-load it completely
I not sure – what inside Your huge dump, so can not give universal recommendations – how to properly split it.
The restore was faster although still slower than desired after the recommended tuning of mysql settings..
I looked into some 3rd party utilities and found the following : https://github.com/deviantintegral/mysql-parallel
This required recreating the dump in a specific format but after that the restore was orders of magnitude faster, writing to DB at over 1gb/minute.
Hope, this has been fixed, in future I recommend to use mydumper for take backup of huge database and restore it. Native mysqldump and restore is pretty slow, because it’s single threaded, but in mydumper you can use upto 62 threads.