troubleshooting Question

MySQL InnodDB Import from mysqldump takes forever.

Avatar of Andras Czömpöl
Andras CzömpölFlag for Austria asked on
Windows Server 2012MySQL ServerStorage Software
2 Comments1 Solution371 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Aaron Tomosky
Director, SD-WAN Solutions

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros