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!

  • What if you setup a new server and setup replication and do all the changes on slave first and let the slave gets updated as you need and then switch over, this way you would have minimum down time. is that doable. Would share the steps in sequence once it sounds good to you. Jun 30, 2015 at 17:59
  • Nawaz! That was definitely the way to go... you got it =)
    – dylst
    Jul 1, 2015 at 19:00
  • good if it helps you.. Jul 2, 2015 at 15:42

1 Answer 1


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

  • would that be available with RDS? Jun 30, 2015 at 17:57
  • It looks like it might be available with RDS, as it takes a -h (host) option. I haven't tried this yet. I'm actually able to work around my constraints by creating an RDS Read Replica, running my alters against it, then promoting the replica to be master. The scripts took about 2 days to run, but it's all done now and all that's left is the promotion.
    – dylst
    Jun 30, 2015 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.