Data Consistency for RDS for MySQL: The 8.0 Version

In 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:

mysql> select * from dani.inconsistency;
+----+--------------+---------------+
| id | string_field | numeric_field |
+----+--------------+---------------+
|  1 | casa         |             1 |
|  2 | caza         |             2 |
|  3 | auto         |             3 |
|  4 | auto         |             3 |
|  5 | auto         |             4 |
|  6 | auto         |             5 |
|  7 | autos        |             5 |
|  8 | autos        |             6 |
|  9 | pepe         |             1 |
+----+--------------+---------------+
9 rows in set (0.00 sec)

Replica:

mysql> select * from dani.inconsistency;
+----+--------------+---------------+
| id | string_field | numeric_field |
+----+--------------+---------------+
|  1 | casa         |             1 |
|  2 | caza         |             2 |
|  3 | auto         |             3 |
|  4 | auto         |             3 |
|  5 | auto         |             4 |
|  6 | auto         |             5 |
|  7 | autos        |             5 |
|  8 | autos        |             6 |
|  9 | papa         |             1 |
+----+--------------+---------------+
9 rows in set (0.00 sec)

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

mysql> grant system_variables_admin on *.* to percona;
Query OK, 0 rows affected (0.01 sec)

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

[root@ip-192-168-1-200~]# pt-table-checksum --host=dgb-pmm2.can0nprz8rtd.us-east-1.rds.amazonaws.com --user=percona --password=xxxxxxx --databases=dani --recursion-method dsn=h=dgb-pmm2.can0nprz8rtd.us-east-1.rds.amazonaws.com,D=percona,t=dsns --no-check-binlog-format --no-check-replication-filters --chunk-size=3
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
09-10T20:20:17      0      1        9          0       5       0   0.175 dani.inconsistency
[root@ip-192-168-1-200 ~]# echo $?
16

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:

mysql> SELECT * FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) GROUP BY db, tbl\G
*************************** 1. row ***************************
            db: dani
           tbl: inconsistency
         chunk: 3
    chunk_time: 0.00951
   chunk_index: PRIMARY
lower_boundary: 7
upper_boundary: 9
      this_crc: 32ab17eb
      this_cnt: 3
    master_crc: 2d705b07
    master_cnt: 3
            ts: 2020-09-10 20:20:17
1 row in set (0.00 sec)

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!

by Daniel Guzmán Burgos via Percona Database Performance Blog