Amazon Aurora, a key component of Amazon RDS, is a fully managed relational database engine compatible with MySQL and PostgreSQL, offering automated database clustering and replication. At Ensolvers we use it to manage several of our applications’ relational databases, ensuring they can comfortably and reliably scale to our customer’s needs.
In this context, this article discusses using RDS Blue/Green Deployments to migrate character sets of an Aurora RDS database seamlessly, assuming the reader has basic familiarity with Amazon RDS and MySQL concepts.
In one of our oldest projects, the production database was configured with MySQL 5.7's default character set: latin1. While adequate for Western European languages, it lacked support for international characters like Chinese, Russian, or even emojis. With our application expanding globally, using a modern and flexible character set became imperative. We opted for utf8mb4 due to its comprehensive support for a wide range of characters.
With more than a hundred tables and billions of rows, direct migration on the production database would incur significant downtime. To mitigate this, we utilized Amazon RDS Blue/Green Deployments, a process that involves duplicating the production database environment into a synchronized staging environment. This allows modifications to be made to the staging environment without impacting the production setup. When ready, the staging environment can be promoted to become the new production environment, with downtime typically under one minute.
Next, we'll break down the steps we followed to get this migration done:
The first step in the process is to set up the proper binlog format. For that we need to modify the binlog_format parameter to STATEMENT in the cluster parameter group, ensuring compatibility with required queries for character set modification.
The second step is to create the Blue/Green Deployment, which can be easily triggered from the RDS console, duplicating the database environment into a synchronized staging environment
After the Blue/Green deployment has been provisioned, now we proceed to execute all the changes. We proceed to execute the query to alter the database's default character set
And then we Iterate through tables and execute conversion queries for each
Due to the magnitude of the data, the latter took several hours to be run in all tables in the DB
Once the queries have finished running and the replication lag is close to 0, activate the switch over process, promoting the staging environment to production via the RDS console. This should be done during low-traffic hours to minimize application impact.
In this article, we've outlined our approach to migrating database character sets with zero downtime using RDS Blue/Green Deployments. By employing this strategy, we've successfully navigated the complexities of character set migration in a high-volume production environment, maintaining uninterrupted service delivery.
