MySQL InnodDB Import from mysqldump takes forever.

Andras Czömpöl
Andras Czömpöl used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Director of Solutions Consulting
Commented:
I don't see you mention autocommit=0 or some of the other suggestions found here
https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial