On-prem MySQL 8 Replicate to RDS

Scenario

Version information
- On-premises MySQL 8.0.37 on Ubuntu 24.04 (using default installation method)
- AWS RDS for MySQL 8.0.35
Replicate on-premises MySQL 8 to AWS RDS MySQL 8 slave database using native replication functionality
- On-premises MySQL user authentication method defaults to caching_sha2_password
- As AWS RDS for MySQL 8 still uses mysql_native_password authentication by default, replication setup will fail
- Need to create account using mysql_native_password method, or adjust to mysql_native_password authentication after creation
This case simulates on-premises MySQL master database using EC2, replicating database to RDS slave database
- Ensure EC2 can connect to RDS host

Process and Explanation

Install and configure on-premises Master MySQL 8
Create Slave RDS for MySQL 8
Data migration
Configure RDS replication
Verify replication status and troubleshooting

Execution Steps

Install and configure on-premises Master MySQL 8

Install Master MySQL 8

Install MySQL 8, which is available by default in Ubuntu 24.04 repo

1
sudo apt update
2
sudo apt install mysql-server -y
3
sudo systemctl enable mysql
4
sudo systemctl start mysql
5
# (Optional) Set password, mandatory in production environments
6
sudo mysql_secure_installation

Configure Master MySQL 8

Configure MySQL 8 configuration file as Master, modify relevant settings in the configuration file

Usually choose either binlog_do_db or binlog_ignore_db parameter
- binlog_do_db: Databases to replicate, defaults to all if not specified
- binlog_ignore_db: Databases to ignore for replication, defaults to none if not specified
Method for recording multiple databases as shown in the example below

1
# Only lab1 and lab2 databases will be replicated, others won't sync
2
binlog_do_db              = lab1
3
binlog_do_db              = lab2
4

5
# Only lab1 and lab2 databases won't be replicated, others will sync
6
binlog_ignore_db          = lab1
7
binlog_ignore_db          = lab2

Below is a complete configuration example, adjust as needed

1
# * Basic Settings
2
server-id = 101
3
auto-increment-increment  = 2
4
auto-increment-offset     = 2
5
# bind external address
6
bind-address              = 0.0.0.0
7
# log-bin enable
8
log_bin                   = /var/log/mysql/mysql-bin.log
9
expire_logs_days          = 3
10
max_binlog_size           = 100M
11
binlog_format             = mixed
12
# Adjust the following as needed
13
binlog_do_db              = dbo
14
# binlog_ignore_db          = lab2

After configuration, restart MySQL service

1
sudo systemctl restart mysql

You can see that caching_sha2_password is used for authentication by default

1
show variables like 'default_authentication_plugin';
2
+-------------------------------+-----------------------+
3
| Variable_name                 | Value                 |
4
+-------------------------------+-----------------------+
5
| default_authentication_plugin | caching_sha2_password |
6
+-------------------------------+-----------------------+
7
1 row in set (0.00 sec)

Create Master MySQL 8 replication account

Create a dedicated replication account
- Replace % with specific source IP for increased security
- Replace replicator with another account name
- Replace replicatorpass with another password
- Create account using mysql_native_password authentication method
Set account replication permissions: REPLICATION CLIENT and REPLICATION SLAVE

1
CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'replicatorpass';
2
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'replicator'@'%';
3
FLUSH PRIVILEGES;

Create Slave RDS for MySQL 8

Use AWS GUI interface to create, and ensure EC2 host can connect to RDS host

Data migration

Export the database from EC2. If there’s no data, you can use MySQL official sample database. Before exporting, perform the following operations

Lock Master MySQL database

1
FLUSH TABLES WITH READ LOCK;
2
SET GLOBAL read_only = ON;

Check binlog status information

Record the File and Position information for later use, and verify if Binlog_Do_DB is correct

1
SHOW MASTER STATUS\G;;
2

3
# Expected result
4
*************************** 1. row ***************************
5
             File: mysql-bin.000001
6
         Position: 1723
7
     Binlog_Do_DB: dbo
8
 Binlog_Ignore_DB:
9
Executed_Gtid_Set:
10
1 row in set (0.00 sec)

Data export and import

Export the database to be synchronized. You can use commands provided by official documentation or other tools for export
Then import the exported data into RDS for MySQL, using commands or other third-party tools

Unlock Master MySQL database

1
SET GLOBAL read_only = OFF;
2
UNLOCK TABLES;

Configure RDS replication

Fill in the information obtained from SHOW MASTER STATUS earlier into the RDS for MySQL command

master_host is the IP or domain name of the host
For more RDS for MySQL configuration parameters, refer to this official documentation

1
CALL mysql.rds_set_external_master ('master_host', 3306, 'replicator', 'replicatorpass', 'mysql-bin.000001', 1723, 0);
2
CALL mysql.rds_start_replication;
3
+-----------------------------------------------------+
4
| Message                                             |
5
+-----------------------------------------------------+
6
| Replication started. Slave is now running normally. |
7
+-----------------------------------------------------+
8
1 row in set (3.02 sec)

Verify replication status and troubleshooting

Confirm successful synchronization status

Connect to the Slave database (RDS for MySQL), enter the SHOW SLAVE STATUS\G; command to check synchronization status

Both Slave_IO_Running and Slave_SQL_Running statuses should be Yes
Last_Error and Last_IO_Error should both be empty
Pay attention to the values of Read_Master_Log_Pos, Exec_Master_Log_Pos, and Relay_Log_Pos

1
SHOW SLAVE STATUS\G;
2
*************************** 1. row ***************************
3
               Slave_IO_State: Waiting for master to send event
4
                  Master_Host: 10.250.1.41
5
                  Master_User: replicator
6
                  Master_Port: 3306
7
                Connect_Retry: 60
8
              Master_Log_File: mysql-bin.000001
9
          Read_Master_Log_Pos: 3199
10
               Relay_Log_File: relaylog.000017
11
                Relay_Log_Pos: 326
12
        Relay_Master_Log_File: mysql-bin.000001
13
             Slave_IO_Running: Yes
14
            Slave_SQL_Running: Yes
15
              Replicate_Do_DB:
16
          Replicate_Ignore_DB:
17
           Replicate_Do_Table:
18
       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
19
      Replicate_Wild_Do_Table:
20
  Replicate_Wild_Ignore_Table:
21
                   Last_Errno: 0
22
                   Last_Error:
23
                 Skip_Counter: 0
24
          Exec_Master_Log_Pos: 3199
25
              Relay_Log_Space: 901
26
              Until_Condition: None
27
               Until_Log_File:
28
                Until_Log_Pos: 0
29
           Master_SSL_Allowed: No
30
           Master_SSL_CA_File:
31
           Master_SSL_CA_Path:
32
              Master_SSL_Cert:
33
            Master_SSL_Cipher:
34
               Master_SSL_Key:
35
        Seconds_Behind_Master: 0
36
Master_SSL_Verify_Server_Cert: No
37
                Last_IO_Errno: 0
38
                Last_IO_Error:
39
               Last_SQL_Errno: 0
40
               Last_SQL_Error:
41
  Replicate_Ignore_Server_Ids:
42
             Master_Server_Id: 101
43
                  Master_UUID: fbba8d94-4e28-11ef-b9c6-06b99b39a34d
44
             Master_Info_File: mysql.slave_master_info
45
                    SQL_Delay: 0
46
          SQL_Remaining_Delay: NULL
47
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
48
           Master_Retry_Count: 86400
49
                  Master_Bind:
50
      Last_IO_Error_Timestamp:
51
     Last_SQL_Error_Timestamp:
52
               Master_SSL_Crl:
53
           Master_SSL_Crlpath:
54
           Retrieved_Gtid_Set:
55
            Executed_Gtid_Set:
56
                Auto_Position: 0
57
         Replicate_Rewrite_DB:
58
                 Channel_Name:
59
           Master_TLS_Version:
60
       Master_public_key_path:
61
        Get_master_public_key: 0
62
            Network_Namespace:
63
1 row in set, 1 warning (0.00 sec)

Test by adding some data. In addition to verifying table synchronization, you can enter SHOW SLAVE STATUS\G; to confirm if synchronization is successful
You will notice that the values of Read_Master_Log_Pos, Exec_Master_Log_Pos, and Relay_Log_Pos will update accordingly

1
mysql> SHOW SLAVE STATUS\G;
2
*************************** 1. row ***************************
3
               Slave_IO_State: Waiting for master to send event
4
                  Master_Host: 10.250.1.41
5
                  Master_User: replicator
6
                  Master_Port: 3306
7
                Connect_Retry: 60
8
              Master_Log_File: mysql-bin.000001
9
          Read_Master_Log_Pos: 3567
10
               Relay_Log_File: relaylog.000017
11
                Relay_Log_Pos: 694
12
        Relay_Master_Log_File: mysql-bin.000001
13
             Slave_IO_Running: Yes
14
            Slave_SQL_Running: Yes
15
              Replicate_Do_DB:
16
          Replicate_Ignore_DB:
17
           Replicate_Do_Table:
18
       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
19
      Replicate_Wild_Do_Table:
20
  Replicate_Wild_Ignore_Table:
21
                   Last_Errno: 0
22
                   Last_Error:
23
                 Skip_Counter: 0
24
          Exec_Master_Log_Pos: 3567
25
              Relay_Log_Space: 1269
26
              Until_Condition: None
27
               Until_Log_File:
28
                Until_Log_Pos: 0
29
           Master_SSL_Allowed: No
30
           Master_SSL_CA_File:
31
           Master_SSL_CA_Path:
32
              Master_SSL_Cert:
33
            Master_SSL_Cipher:
34
               Master_SSL_Key:
35
        Seconds_Behind_Master: 0
36
Master_SSL_Verify_Server_Cert: No
37
                Last_IO_Errno: 0
38
                Last_IO_Error:
39
               Last_SQL_Errno: 0
40
               Last_SQL_Error:
41
  Replicate_Ignore_Server_Ids:
42
             Master_Server_Id: 101
43
                  Master_UUID: fbba8d94-4e28-11ef-b9c6-06b99b39a34d
44
             Master_Info_File: mysql.slave_master_info
45
                    SQL_Delay: 0
46
          SQL_Remaining_Delay: NULL
47
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
48
           Master_Retry_Count: 86400
49
                  Master_Bind:
50
      Last_IO_Error_Timestamp:
51
     Last_SQL_Error_Timestamp:
52
               Master_SSL_Crl:
53
           Master_SSL_Crlpath:
54
           Retrieved_Gtid_Set:
55
            Executed_Gtid_Set:
56
                Auto_Position: 0
57
         Replicate_Rewrite_DB:
58
                 Channel_Name:
59
           Master_TLS_Version:
60
       Master_public_key_path:
61
        Get_master_public_key: 0
62
            Network_Namespace:
63
1 row in set, 1 warning (0.01 sec)

Troubleshooting

Authentication requires secure connection

Error appears: Authentication plugin' caching_sha2_password' reported error: Authentication requires secure connection.

1
mysql> SHOW SLAVE STATUS\G;
2
*************************** 1. row ***************************
3
               Slave_IO_State: Connecting to master
4
                  Master_Host: 10.250.1.41
5
                  Master_User: replicator
6
                  Master_Port: 3306
7
                Connect_Retry: 60
8
              Master_Log_File: mysql-bin.000001
9
          Read_Master_Log_Pos: 1723
10
               Relay_Log_File: relaylog.000001
11
                Relay_Log_Pos: 4
12
        Relay_Master_Log_File: mysql-bin.000001
13
             Slave_IO_Running: Connecting
14
            Slave_SQL_Running: Yes
15
              Replicate_Do_DB:
16
          Replicate_Ignore_DB:
17
           Replicate_Do_Table:
18
       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
19
      Replicate_Wild_Do_Table:
20
  Replicate_Wild_Ignore_Table:
21
                   Last_Errno: 0
22
                   Last_Error:
23
                 Skip_Counter: 0
24
          Exec_Master_Log_Pos: 1723
25
              Relay_Log_Space: 1984
26
              Until_Condition: None
27
               Until_Log_File:
28
                Until_Log_Pos: 0
29
           Master_SSL_Allowed: No
30
           Master_SSL_CA_File:
31
           Master_SSL_CA_Path:
32
              Master_SSL_Cert:
33
            Master_SSL_Cipher:
34
               Master_SSL_Key:
35
        Seconds_Behind_Master: NULL
36
Master_SSL_Verify_Server_Cert: No
37
                Last_IO_Errno: 2061
38
                Last_IO_Error: Error connecting to source '[email protected]:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
39
               Last_SQL_Errno: 0
40
               Last_SQL_Error:
41
  Replicate_Ignore_Server_Ids:
42
             Master_Server_Id: 0
43
                  Master_UUID:
44
             Master_Info_File: mysql.slave_master_info
45
                    SQL_Delay: 0
46
          SQL_Remaining_Delay: NULL
47
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
48
           Master_Retry_Count: 86400
49
                  Master_Bind:
50
      Last_IO_Error_Timestamp: 240730 07:11:09
51
     Last_SQL_Error_Timestamp:
52
               Master_SSL_Crl:
53
           Master_SSL_Crlpath:
54
           Retrieved_Gtid_Set:
55
            Executed_Gtid_Set:
56
                Auto_Position: 0
57
         Replicate_Rewrite_DB:
58
                 Channel_Name:
59
           Master_TLS_Version:
60
       Master_public_key_path:
61
        Get_master_public_key: 0
62
            Network_Namespace:
63
1 row in set, 1 warning (0.00 sec)

We can see that the replicator account uses caching_sha2_password authentication

1
SELECT user, host, plugin FROM mysql.user;
2
+------------------+-----------+-----------------------+
3
| user             | host      | plugin                |
4
+------------------+-----------+-----------------------+
5
| admin            | %         | caching_sha2_password |
6
| replicator       | %         | caching_sha2_password |
7
| debian-sys-maint | localhost | caching_sha2_password |
8
| mysql.infoschema | localhost | caching_sha2_password |
9
| mysql.session    | localhost | caching_sha2_password |
10
| mysql.sys        | localhost | caching_sha2_password |
11
| root             | localhost | auth_socket           |
12
+------------------+-----------+-----------------------+
13
7 rows in set (0.00 sec)

Use the following command to adjust the authentication method for the replication account, then confirm the effective status

1
ALTER USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'replicatorpass';
2
SELECT user, host, plugin FROM mysql.user;
3
+------------------+-----------+-----------------------+
4
| user             | host      | plugin                |
5
+------------------+-----------+-----------------------+
6
| admin            | %         | caching_sha2_password |
7
| replicator       | %         | mysql_native_password |
8
| debian-sys-maint | localhost | caching_sha2_password |
9
| mysql.infoschema | localhost | caching_sha2_password |
10
| mysql.session    | localhost | caching_sha2_password |
11
| mysql.sys        | localhost | caching_sha2_password |
12
| root             | localhost | auth_socket           |
13
+------------------+-----------+-----------------------+
14
7 rows in set (0.00 sec)