Avatar of Andras Czömpöl
Andras CzömpölFlag for Austria

asked on 

MySQL InnodDB Import from mysqldump takes forever.

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
Windows Server 2012MySQL ServerStorage Software

Avatar of undefined
Last Comment
Andras Czömpöl
ASKER CERTIFIED SOLUTION
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Andras Czömpöl

ASKER

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!
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo