{"id":3512,"date":"2025-11-04T11:45:00","date_gmt":"2025-11-04T11:45:00","guid":{"rendered":"https:\/\/kedar.nitty-witty.com\/blog\/?p=3512"},"modified":"2025-11-04T11:43:07","modified_gmt":"2025-11-04T11:43:07","slug":"how-to-checksum-on-aws-rds-for-mysql-replication-8-0-8-4","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/how-to-checksum-on-aws-rds-for-mysql-replication-8-0-8-4","title":{"rendered":"How to checksum on AWS RDS for MySQL Replication 8.0 > 8.4"},"content":{"rendered":"\n

During a MySQL upgrade process the RDS replication between MySQL 8.0 and 8.4<\/strong> was configured. I wanted to ensure the data was consistent for any random reason. This post is a quick work-log of how I tested and validated consistency using Percona Toolkit<\/strong>‘s pt-table-checksum. And yes it is possible to perform checksum on AWS RDS.<\/p>\n\n\n\n

Environment Setup<\/h2>\n\n\n\n

mysql80.kedarnittywitty.rds.amazonaws.com – MySQL 8.0
mysql84.kedarnittywitty.rds.amazonaws.com – MySQL 8.4<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Replication configuration<\/h2>\n\n\n\n
# mysql -hmysql80.kedarnittywitty.rds.amazonaws.com
...
Server version: 8.0.42 Source distribution
...
mysql> CREATE USER IF NOT EXISTS 'repl_user'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.22 sec)

mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
Query OK, 0 rows affected (0.22 sec)


mysql> show binary logs;
+----------------------------+-----------+-----------+
| Log_name                   | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 |      1520 | No        |
| mysql-bin-changelog.000002 |       600 | No        |
| mysql-bin-changelog.000003 |       694 | No        |
+----------------------------+-----------+-----------+
<\/code><\/pre>\n\n\n\n
Setup Replica Using RDS Procedures<\/h2>\n\n\n\n

mysql> CALL mysql.rds_set_external_source ('mysql80.kedarnittywitty1.rds.amazonaws.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.000003', '4', 0);
Query OK, 0 rows affected (0.25 sec)

mysql> CALL mysql.rds_start_replication;
+-----------------------------------------------------------+
| Message                                                   |
+-----------------------------------------------------------+
| Replication started. Replication is now running normally. |
+-----------------------------------------------------------+
1 row in set (3.25 sec)

Query OK, 0 rows affected (3.25 sec)

mysql> SHOW REPLICA STATUS\\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: mysql80.kedarnittywitty.rds.amazonaws.com
                  Source_User: repl_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin-changelog.000004
          Read_Source_Log_Pos: 544
               Relay_Log_File: relaylog.000004
                Relay_Log_Pos: 781
        Relay_Source_Log_File: mysql-bin-changelog.000004
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: innodb_memcache.cache_policies,innodb_memcache.config_options,mysql.plugin,mysql.rds_configuration,mysql.rds_history,mysql.rds_monitor,mysql.rds_replication_status,mysql.rds_sysinfo,mysql.rds_upgrade_prechecks
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 544
              Relay_Log_Space: 1042
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1813606236
                  Source_UUID: 53d56fdd-823f-11f0-a05d-0271b16d16ab
             Source_Info_File: mysql.slave_source_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.21 sec)
<\/code><\/pre>\n\n\n\n
Create Test Database and Table<\/h2>\n\n\n\n
mysql -hmysql80.kedarnittywitty.rds.amazonaws.com

mysql> create database checksum_rds;
Query OK, 1 row affected (0.22 sec)

mysql> use checksum_rds;
Database changed
mysql> create table pttablechecksum (
    -> id int not null auto_increment primary key,
    -> val varchar(10) ) engine=innodb;
Query OK, 0 rows affected (0.24 sec)

mysql> insert into pttablechecksum values (null,'a'), (null,'a'), (null,'a'), (null,'a'), (null,'a'), (null,'a'), (null,'a'), (null,'a'), (null,'a');
Query OK, 1 row affected (0.21 sec)<\/pre>\n\n\n\n
Create Data Inconsistency<\/h2>\n\n\n\n
Simulate drift<\/strong> by manually modifying replica data:<\/p>\n\n\n\n
mysql -hmysql84.kedarnittywitty.rds.amazonaws.com

mysql> use checksum_rds;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> show tables;
+------------------------+
| Tables_in_checksum_rds |
+------------------------+
| pttablechecksum        |
+------------------------+
1 row in set (0.31 sec)

mysql> select count(*) from pttablechecksum;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.22 sec)

mysql> delete from pttablechecksum where id=8;
Query OK, 1 row affected (0.27 sec)
<\/code><\/pre>\n\n\n\n
Verify Divergence:<\/strong><\/p>\n\n\n\n
root@nitty-witty:~# mysql  -hmysql80.kedarnittywitty.rds.amazonaws.com -e \"select * from checksum_rds.pttablechecksum\"
+----+------+
| id | val  |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | a    |
|  7 | a    |
|  8 | a    |
|  9 | a    |
| 10 | a    |
+----+------+
root@nitty-witty:~# mysql  -hmysql84.kedarnittywitty.rds.amazonaws.com -e \"select * from checksum_rds.pttablechecksum\"
+----+------+
| id | val  |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | a    |
|  7 | a    |
|  9 | a    |
+----+------+
<\/code><\/pre>\n\n\n\n
Setup Percona Toolkit for checksum<\/h2>\n\n\n\n
# Download Toolkit for MySQL 8.4 from https:\/\/www.percona.com\/percona-toolkit & install

root@nitty-witty:~# dpkg -i percona-toolkit_3.7.0-2.noble_amd64.deb

# Create required database and table

mysql> create database percona;
Query OK, 1 row affected (0.22 sec)

drop table if exists dsns;
CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
<\/pre>\n\n\n\n
Execute Checksum Validation<\/h2>\n\n\n\n
# Running checksum\n\nroot@nitty-witty:~# pt-table-checksum --host=mysql80.kedarnittywitty.rds.amazonaws.com --user=admin --password=password --databases=checksum_rds  --no-check-binlog-format --no-check-replication-filters --chunk-size=3 --recursion-method=processlist\nChecking if all tables can be checksummed ...\nStarting checksum ...\n            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE\n08-26T10:25:07      0      2       10          1       6       0   0.498 checksum_rds.pttablechecksum<\/pre>\n\n\n\n
Analyze Checksum Results<\/h2>\n\n\n\n
root@nitty-witty:~# mysql -hmysql84.kedarnittywitty.rds.amazonaws.com
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 818
Server version: 8.4.5 Source distribution

Copyright (c) 2009-2025 Percona LLC and\/or its affiliates
Copyright (c) 2000, 2025, Oracle and\/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and\/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

mysql> select * from percona.checksums;
+--------------+-----------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db           | tbl             | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | source_crc | source_cnt | ts                  |
+--------------+-----------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| checksum_rds | pttablechecksum |     1 |   0.005242 | PRIMARY     | 1              | 3              | 4e1a991  |        3 | 4e1a991    |          3 | 2025-08-26 10:25:06 |
| checksum_rds | pttablechecksum |     2 |   0.004853 | PRIMARY     | 4              | 6              | b6c17581 |        3 | b6c17581   |          3 | 2025-08-26 10:25:06 |
| checksum_rds | pttablechecksum |     3 |    0.00527 | PRIMARY     | 7              | 9              | bf30be61 |        2 | 8ba15c31   |          3 | 2025-08-26 10:25:07 |
| checksum_rds | pttablechecksum |     4 |   0.007311 | PRIMARY     | 10             | 10             | 0        |        0 | bc272b27   |          1 | 2025-08-26 10:25:07 |
| checksum_rds | pttablechecksum |     5 |   0.004934 | PRIMARY     | NULL           | 1              | 0        |        0 | 0          |          0 | 2025-08-26 10:25:07 |
| checksum_rds | pttablechecksum |     6 |   0.005274 | PRIMARY     | 10             | NULL           | 0        |        0 | 0          |          0 | 2025-08-26 10:25:07 |
+--------------+-----------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
6 rows in set (0.00 sec)<\/pre>\n\n\n\n
Find All Inconsistent Tables<\/h2>\n\n\n\n
SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM percona.checksums
WHERE (source_cnt <> this_cnt 
       OR source_crc <> this_crc 
       OR ISNULL(source_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;
<\/pre>\n\n\n\n
Use pt-table-sync to fix data discrepancy<\/h2>\n\n\n\n
root@nitty-witty:~# pt-table-sync --verbose --print --replicate=percona.checksums --sync-to-source --tables checksum_rds.pttablechecksum h=mysql84.kedarnittywitty.rds.amazonaws.com,u=admin,p=password > checksum_rds.pttablechecksum.sql\nroot@nitty-witty:~# less checksum_rds.pttablechecksum.sql\nroot@nitty-witty:~#\nroot@nitty-witty:~# cat checksum_rds.pttablechecksum.sql\n# Syncing via replication h=mysql84.kedarnittywitty.rds.amazonaws.com,p=...,u=admin\n# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE\nREPLACE INTO `checksum_rds`.`pttablechecksum`(`id`, `val`) VALUES ('8', 'a') \/*percona-toolkit src_db:checksum_rds src_tbl:pttablechecksum src_dsn:P=3306,h=mysql80.kedarnittywitty.rds.amazonaws.com,p=...,u=admin dst_db:checksum_rds dst_tbl:pttablechecksum dst_dsn:h=mysql84.kedarnittywitty.rds.amazonaws.com,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:72602 user:root host:nitty-witty*\/;\n#      0       1      0      0 Chunk     12:15:52 12:15:52 2    checksum_rds.pttablechecksum\nREPLACE INTO `checksum_rds`.`pttablechecksum`(`id`, `val`) VALUES ('10', 'a') \/*percona-toolkit src_db:checksum_rds src_tbl:pttablechecksum src_dsn:P=3306,h=mysql80.kedarnittywitty.rds.amazonaws.com,p=...,u=admin dst_db:checksum_rds dst_tbl:pttablechecksum dst_dsn:h=mysql84.kedarnittywitty.rds.amazonaws.com,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:72602 user:root host:nitty-witty*\/;\n#      0       1      0      0 Chunk     12:15:52 12:15:52 2    checksum_rds.pttablechecksum<\/pre>\n\n\n\n
\n
TL;DR – For checksum in RDS, SESSION_VARIABLES_ADMIN is your friend.<\/p>\n<\/blockquote>\n\n\n\n
Checksum for RDS<\/h2>\n\n\n\n
The pt-table-checksum is one of the important tool from Percona Toolkit and it works perfectly OK on RDS. Note that here we used “admin” user for running the checksum. For Non-admin users, you will need SESSION_VARIABLES_ADMIN<\/strong><\/code> privilege:<\/p>\n\n\n\n
GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'checksum_user'@'%'
<\/code><\/pre>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"During a MySQL upgrade process the RDS replication between MySQL 8.0 and 8.4 was configured. I wanted to ensure the data was consistent for any random reason. This post is…\n","protected":false},"author":1,"featured_media":3537,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[868,869,8,870],"tags":[1152,1154,758,1090,1151,1153],"class_list":{"0":"post-3512","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-aws-rds","8":"category-mariadb","9":"category-mysql","10":"category-mysql-tools","11":"tag-checksum","12":"tag-data-validation","13":"tag-mysql-8-0","14":"tag-mysql-8-4","15":"tag-mysql-rds","16":"tag-mysql-rds-8-4"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=3512"}],"version-history":[{"count":2,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3512\/revisions"}],"predecessor-version":[{"id":3538,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3512\/revisions\/3538"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media\/3537"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=3512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=3512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=3512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}