0

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. – Nawaz Sohail Jun 30 '15 at 17:59
  • Nawaz! That was definitely the way to go... you got it =) – dylst Jul 1 '15 at 19:00
  • good if it helps you.. – Nawaz Sohail Jul 2 '15 at 15:42
0

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

  • would that be available with RDS? – Nawaz Sohail Jun 30 '15 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 '15 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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