What does logical replication for MySQL mean

0

In the documentation regarding blue/green deployments on RDS MySQL it says:

The staging environment stays in sync with the current production environment using logical replication.

Source: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-overview.html

It is not clear for me what does logical replication mean. I was unable to find more information in AWS documentation nor in the official MySQL documentation. Is it statement-based, row-based or mixed? Does it use GTID or is "standard" binlog replication? Are there any pitfalls (e.g. triggers) in using this replication. What schema changes can be tolerated on the green environment? Any further information will be helpful.

2 Answers
0

RDS supports both physical and logical replication. For MySQL, MariaDb, Aurora MySql Blue Green Deployments RDS uses logical binlog replication. And it will use the binlog_format that is specified for the source database. It will only use GTID if your source database is configured with GTID. And yes you can make schema changes to green but it is your responsibility to not introduce breaking changes to the binlog replication to green. For example you cannot drop a column from a table on the green side because that might break binlog replication.

https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-features-differing-tables.html

AWS
MODERATOR
philaws
answered a year ago
0

It's not an easy question to answer in a post like that so I will try to include references to respond where applicable.

I know its not your question, but for awareness, the PostgreSQL logical replication is detailed in our documentation [1]. It is native in PG10 or one can use pgLogical. For MySQL, logical replication approaches include Cross-Region Read Replicas, MySQL binlogs, and tools like AWS Database Migration Service (which uses binlogs under the hood). This re:Post article describes how you would set this up [2]. The MySQL documentation details the binlog replication advantages and disadvantages [3].

If you use DMS, the documentation describes limitations and best practices worth knowing to plan a migration correctly [4]. Finally, this blog post details how to manage a breaking schema change in Amazon Aurora [5].

For reference and because you ask about blue-green deployment specifically, this blog post describes the recently released Blue-Green feature in RDS [6] that links to the documentation link you posted. I recommend to go through this blog post [7] as well to understand how the replication works behind the scene.

Hope that helps.

Jon

References:

[1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html#AuroraPostgreSQL.Replication.Logical.PostgreSQL-Example

[2] https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-cross-region-replica/

[3] https://dev.mysql.com/doc/mysql-replication-excerpt/5.6/en/replication-formats.html

[4] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

[5] https://aws.amazon.com/blogs/database/upgrade-amazon-rds-for-mysql-or-mariadb-database-schemas-with-minimal-downtime/

[6] https://aws.amazon.com/blogs/aws/new-fully-managed-blue-green-deployments-in-amazon-aurora-and-amazon-rds/

[7] https://aws.amazon.com/blogs/database/performing-major-version-upgrades-for-amazon-aurora-mysql-with-minimum-downtime/

profile pictureAWS
EXPERT
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions