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 https://tools.percona.com/wizard 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.