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
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!