Data Consistency for RDS for MySQL: The 8.0 Version

data consistency rds mysql 8In a previous blog post on Data Consistency for RDS for MySQL, we presented a workaround to manage run pt-table-checksum on RDS instances. However, if your instance is running a MySQL 8.0.X version, there’s a simpler way to check data consistency.

Starting with 8.0.1, MySQL introduced something called “Dynamic Privileges” which is a solution to grant more granulated privileges to the users, instead of the almighty SUPER privilege.

So what was the issue with pt-table-checksum and RDS again? Since there’s no SUPER privileges for any user, there was no way for the tool to change the binlog_format to STATEMENT… but not anymore.

The solution when using 8.0 is to grant a privilege called SYSTEM_VARIABLES_ADMIN, and with that privilege, the user granted with it can now execute “set global binlog_format = STATEMENT” without being rejected.

Hands-On

Before going to the steps, my setup is a primary RDS 8.0.20 with a read replica, the same version. A table called “inconsistency” with, well, an inconsistency introduced.

Primary:

Replica:

Now to the actual check. The first step is to grant the privilege. Here I have my “percona” user:

The second and final step is to execute pt-table-checksum. That’s it!

So we can see here that there is 1 DIFF reported. Also, the exit status of “16” is confirmed (16 means “At least one diff was found”).

The difference between both instances is in the 3rd chunk, that from id =7 to id=9:

Which is the 9th row, wherein the primary it says “pepe” and in the replica says “papa”.

So if you are running RDS for MySQL with the 8 series, pt-table-checksum is back to being something you can use thanks to the dynamic privileges. Yet another reason to upgrade to MySQL 8.0!

Share this post

Leave a Reply