We help IT Professionals succeed at work.

MySQL InnodDB Import from mysqldump takes forever.

356 Views
Last Modified: 2018-08-14
HI,

I have a problem with my Mysql DB (InnodDB tables). I'll take mysqldump backups every 12 hours with the following command:

MySqlDump --defaults-file=$MySqlBackupConf --host=localhost --result-file $MySqlBackupFile --default-character-set=utf8 --events --triggers --routines --single-transaction=TRUE --log-error=$MySqlBackupLogFile --verbose $Item

Backup takes 8 minutes, size is about 2.4GB

The resotre is made with the Data Import from Workbench but it takes long, long, long... (5-6 hours or more):

mysql.exe --defaults-file="c:\...\tmp8zrchd.cnf"  --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=database_2  < "C:\\Users\\user\\Documents\\dumps\\Dump20170315.sql"

The OS is Windows Server 2012 R2, Mysql 5.7.16. I tried the following settings but no change:

innodb_log_buffer_size=256M
innodb_buffer_pool_size=3G
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit = 0
innodb_write_io_threads = 12

SET GLOBAL FOREIGN_KEY_CHECK=0

Can anyone help with this?

Thx
Comment
Watch Question

Director, SD-WAN Solutions
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Andras CzömpölIT Specialist

Author

Commented:
Thx. Acctually in the and was the SET unique_checks=0; what helped. Still it takes 1 hour to import a 2.4GB backup sql. So, I guess I have to refactor the database structure.

Thank You!