How to Move MySQL Data Between Amazon RDS Instances

amazon-rdsMySQL

We have an RDS instance with about 200G of data in it. A few issues cropped up on our end, and we need to both change the collation and charset of all our tables, as well as have all the data encrypted at rest. RDS supports encryption, but you can't enable it on an already running instance, and can't restore un-encrypted snapshots.

That said, my current strategy is the following:

Create a new RDS instance with encryption enabled, and with the right charset/collation enabled in the associated ParameterGroup.
Take our application down, and start dumping the data from the old instance into the new instance with something like:

mysqldump -C -h xxxxxxx.rds.amazonaws.com --routines --triggers --single-transaction --quick --add-drop-database --default-character-set=utf8mb4 --disable-key -u user -pxxxxxx --ssl_ca xxxx mydatabase | mysql -u user -pxxxxx -h xxxxxx.rds.amazonaws.com --ssl_ca xxxx mydatabase

After data is in the new RDS instance, make all the necessary collation/charset changes to the database itself, tables, columns, change varchar sizes where needed, etc.

For the database: ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

For each table: ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

… etc

My question is… is there any faster way to do this!? I did a straight mysqldump from the primary database onto disk, and it took about 4 hours. I haven't run all the ALTER TABLE changes yet (only on a small dataset), but I'm assuming those operations are going to take a ton of time. All my tables are InnoDB tables if that helps. I was looking at Percona xtrabackup, but realized I can't use that with RDS.

Any thoughts appreciated!

Best Answer

pt-online-schema-change -- It will take longer, but it will not interrupt other activity except briefly.

Related Solutions

Mysql – moving a 13 gb table to amazon rds

I realize you've already worked around the issue, but here is the explanation and the fix for what you encountered. It's nice to be able to do it with the pipe, so you don't have to write that big file somewhere.

As you know, when you type a query into the mysql command line client, while that query is executing, the client doesn't accept any more input from you. That same thing essentially happens when you're stringing the these tools together with a pipe -- the target mysql client waits for the target server to return from the query before it can read more data from the pipe from mysqldump.

Meanwhile, as mysqldump is being blocked on writing to the pipe, because we're waiting for the target server to finish a query, it stops reading data from its socket connection. The OS will only buffer so much data before it stops accepting data from the origin server.

If we do something like ENABLE KEYS which takes a while, we hit a timeout... on the origin MySQL server. But the timeout value can be changed.

MySQL Server has two timers, net_write_timeout and net_read_timeout, which default to 60 and 30 seconds respectively, and which will cause the serve to tear down a client connection when the timer is exceeded while blocking on a write to or a read from the network.

Most likely, it's net_write_timeout you're hitting, so the origin server is giving up on the connection from mysqldump, which isn't accepting data fast enough (because it's blocked on its output). Or, more precisely, it isn't accepting data often enough. If any one statement takes too long to execute, the game is over, so, on the origin server:

SET GLOBAL net_write_timeout = 3600; # one hour

This is setting approximately how much time we can wait for any single query to finish executing on the destination server without the origin server timing out. Normally, you wouldn't want MySQL to sit and wait for an hour for a blocking client, so set the timer back after you're done.

MySQL – Converting Character Set Fails

The ALTER TABLE ... DEFAULT CHARACTER SET ... command sets the default character set and collation for the table. It does not affect the existing VARCHAR/TEXT columns and their data. Only newly created fields will use the specified charset and collation.

The ALTER TABLE ... CONVERT TO CHARACTER SET ... command changes the character sets and collations of all the existing VARCHAR/TEXT fields (and converts the data). It actually should change the default charset for the table as well. It is not specified obvious enough here: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html but it works in that way. On my MySQL 5.1.45 the default charset is changed by the CONVERT operation even it does not include VARCHAR-fields. Also the CONVERT operation may change the datatype if it cannot store the new length of data.

Related Question