Mysql – Intentionally slow down importing of (Percona) MySQL 5.7 dump

MySQLmysql-5.7mysqldumppercona-serverreplication

I need to move a very large MySQL table (250M rows, 120 columns. 120GB of data, 310GB of index) from one database instance to another (on AWS). For reasons beyond the scope of this question, this is the only option we are considering.

Our set up uses cross-region replication on AWS with many slaves on different regions. Updates to a MySQL table are written to the binlog and replicated to these slaves immediately using a private tunnel.

The host database includes only this one table, and the table's contents are static. The target database includes hundreds of other tables with active transactions that must continue to be replicated to other regions with minimal replication lag. This is the root of the problem: if I simply import this massive dump (mysql -u username -p database_name < file.sql), the writes to this single table with overwhelm the binlog and cause replication lag for other tables on slaves, which is unacceptable.

What I'm looking for is this: Is there a way I can intentionally slow down this import to prevent it from causing a replication lag on transactions on other tables?

I'm looking for the most straightforward approach to accomplish this with minimal effort. You can, of course, write a custom script that batches the writes for you and sleeps for a period of time between each batch, but I'm trying to avoid that if possible.

This is the version of MySQL I'm running on both instances:

$ SHOW VARIABLES LIKE "%version%";

innodb_version  5.7.12-5
protocol_version    10
slave_type_conversions  
tls_version TLSv1,TLSv1.1,TLSv1.2
version 5.7.12-5-log
version_comment Percona Server (GPL), Release '5', Revision 'a2f663a'
version_compile_machine x86_64
version_compile_os  debian-linux-gnu

Best Answer

Consider writing a script (in your favorite language) to read the dump file one line at a time and send that line to STDOUT, which will be piped into mysql. And add a tiny sleep in the loop that does that.

To get fancier, you could fetch Seconds_behind_master to tweak the sleep up or down. (I think at least one of Percona's tools does this.)

Related Question