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