How to tune MySQL for restoration from MySQL dump?
You should be able to compute these numbers before the mysqldump.
Concerning the settings you gave in the question,
innodb_log_file_size=1G
This setting is WAY TOO BIG!!!
The innodb_log_file_size
is supposed to be 25% of innodb_buffer_pool_size
innodb_log_file_size=128M
Once you set this in /etc/my.cnf
, you must do the following to resize your InnoDB log files:
service mysql stop
rm -f /var/lib/mysql/ib_logfile[01]
service mysql start
As for the other setting
innodb_log_buffer_size=1G
You never want to cache a ton of data in here before sending them to the InnoDB Log Files, especially for mysqldump reloads or heavy transactional COMMITs. The should be an order of magnitude smaller.
innodb_log_buffer_size=32M
BTW You should disable binary logging before reloading. Otherwise, all the data lands in your binary logs. Please do one of the following:
- Make this ->
SET SQL_LOG_BIN=0;
the first line of the mysqldump file. - From MySQL command line, run
SET SQL_LOG_BIN=0;
then runsource < mysqldumpfile >
- Comment out log-bin from
/etc/my.cnf
and restart MySQL 5.1, load the mysqldump file, uncomment log-bin, and restart MySQL.
UPDATE 2011-07-24 20:30
If you have a mysqldump file /root/MyData.sql
, you can still run the commands like this
SET SQL_LOG_BIN=0;
source /root/MyData.sql
This falls under option 2.
Related videos on Youtube
Alex R
Updated on September 17, 2022Comments
-
Alex R almost 2 years
My little 5GB database which takes 5 minutes to dump via mysqldump, takes 9 hours to restore. Luckily I found this out during a test-run, not an actual emergency situation.
What are the best parameters to optimize to speed this up?
I have tried the following settings on my server with 2GB of RAM:
innodb_buffer_pool_size=512M innodb_additional_mem_pool_size=50M innodb_file_per_table innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 innodb_log_file_size=1G innodb_log_buffer_size=1G
The weird thing is that even with these aggressize settings,
top
only shows mysqld is barely using a fraction of the assigned memory:PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4421 mysql 20 0 247m 76m 5992 S 91 3.7 4:09.33 mysqld
-
Andy Lee Robinson almost 13 yearsSome sql files can be awkward to edit and add "SET SQL_LOG_BIN=0;" Instead: echo "SET SQL_LOG_BIN=0;" | gzip > sql_log_bin.sql.gz; then run this: zcat sql_log_bin.sql.gz backup.sql.gz | mysql
-
ppostma1 almost 9 yearsI just want to make sure you are using --opt with the mysqldump utility. This is a HUGE time saver when reloading. without it mysqldump creates the sql file row by row. --opt does: Adds locks, drop and recreate with all create options enabled, temporarily disables the keys for a quick reload and then turns keys back on at the end to rebuild the indexes after the writing is done. It concatenates several rows together in inserts to reduce filesize AND parsing time of each statement (by up to 70%) and enables --quick to reduce the load against the dumping DB when running. reduces load time by 80%
-
RolandoMySQLDBA almost 9 years@ppostma1 --opt is enabled by default. It says so in the documentation already : dev.mysql.com/doc/refman/5.6/en/…
-
ppostma1 almost 9 yearsI have 4 different flavors of linux running online servers and some distro's mysql package managers disable it! It can be the different between 12 hours to reload a WP database and 8 minutes when its on. I find the managers explanation is "availability of incremental backups". My best understanding is if the server is routinely backed up, it could be off by default. Then the user must manually add the --opt option to specify this is not an incremental backup attempt.
-
RolandoMySQLDBA almost 9 years@ppostma1 Oh that's scary !!! The documentation says it is on by default. My guess is that the distros that do not have
--opt
must have been compiled from source and the option forgotten. I'll definitely keep this issue in my back pocket for any future encounters like this. Thank You. -
Giacomo1968 almost 6 yearsThis is link only answer wrapped in a comment. Besides, while your Python script is helpful on it’s own, what if someone doesn’t want to use Python or a script? Isn’t the answer then just “Don’t set indexes until after all data has been inserted?”
-
Alex R almost 6 yearsThis is a great answer which would be useless (vague and non-prescriptive) if it were not accompanied by a link to a Reference Implementation.