Mysql query hangs on import

Posted on

Question :

I dumped all my databases in order to import them into a slave.

mysqldump --all-databases > dump.sql

The import seems to be very very slow.
Checking the processlist, I saw a simple insert on a 20 rows table that took 400+seconds.

I had to ignore some big and not critical tables from the dump in order to import in a reasonable time.

I don’t understand if I’m doing something wrong, the whole dump was about 800MB and the “big” table I ignored was no more than 170K records, which I don’t think it’s big at all…

Also I often dump a single database to load it on a testing environment, usually without problem. It seems the –all-databases dump is very slow.

Any suggestion welcome.

Answer :

I suspect you’re suffering ‘out-of-the-box’ syndrome. For the whole world to know… MySQL config out of the box sucks.

Go to and create better configuration based on the questions asked in the form. This should provide you with a better starting point. Once the output is generated, add it to your configuration file (/etc/my.cnf) and restart MySQL; review the mysql error log to ensure that your mysql instance started cleanly (beware of paths, permissions and the innodb_log_file_size size differences since that will require disposal of the old ib_log* files before new ones can be created). Once done, try your import again.

Leave a Reply

Your email address will not be published. Required fields are marked *