Large MySQL database dump/restore Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds instance, instance snapshot in AWS have 700GB. MySQL version 5.6, using innodb.
At moment it's been 20 hours sience restoration is running, my estimations are that its going to finish in 4 - 6 hours, so the complete restore will be about 24 hours and that is way too much downtime for production environment.
My question: How to tune mysql in order to speed up restoration?
Thank you in Advance.

P.S
Dump was done in 3 and a half hours.
Have you established what you are bottlenecked on in your import? If you are CPU bottlenecked, there is a very limited amount you can do other than put CPUs with faster single-thread performance into your private cloud instance.

A few things that will help:

innodb_doublewrite=0
innodb_flush_logs_on_trx_commit=0
innodb_io_capacity=&lt;approximate amount of IOPS your storage can provide when using 16KB random writes&gt;
innodb_log_file_size=1G
innodb_log_files_in_group=24 # you will want to change this later!
performance_schema=0
slow_query_log=0
general_log=0


Note: Make sure you remove all of these options after your import is complete, they are dangerous and not sensible for regular runtime operation! These are equivalent of making your race car go faster through weight saving that comes from removing the roll cage and seat belts.

Enabled disk write cache on your import machine's virtual disk, if the driver supports such a thing.

If you can get enough RAM during the import, once your ib_logfile* files are created, stop mysqld, put those files and ibdata1 on tmpfs, and symlink them in. When you are done, stop mysqld and move them back.

If ZFS is an option for you, put your /var/lib/mysql on ZFS on a volume with the following settings:

compression=lz4
logbias=throughput
recordsize=16k
sync=disabled
xattr=sa


This will approximately double your write throughput compared to other file systems, due to compression and sync=disabled functionality. Make sure you set sync=standard after the import is complete.

If you combine all of the above and you are disk I/O limited, you will see a performance improvement in the multiples. If you are CPU limited, the above will give you double-figure % points of improvement.