跳到內容
Ricky's Tech Explorer Log

On-prem MySQL 8 Replicate to RDS

Scenario

  • 版本資訊
    • 地端 MySQL 8.0.37 on Ubuntu 24.04 (使用預設安裝方式)
    • AWS RDS for MySQL 8.0.35
  • 將地端的 MySQL 8 以原生 replication 功能，複寫至 AWS RDS 的 MySQL 8 slave 資料庫中
    • 地端 MySQL 使用者，驗證方式預設採用 caching_sha2_password
    • 因 AWS RDS for MySQL 8 預設仍使用 mysql_native_password 驗證，建立複寫會失敗
    • 建立帳號需使用 mysql_native_password 方式建立，或於建立後，將其調整為 mysql_native_password 驗證方式
  • 本案例以 EC2 模擬地端 MySQL master 資料庫，複寫資料庫到 RDS 的 slave 資料庫中
    • 確保 EC2 可連線到 RDS 主機

流程與說明

  1. 安裝設定地端 Master MySQL 8
  2. 建立 Slave RDS for MySQL 8
  3. 資料搬遷
  4. 設定 RDS replication
  5. 確認 replication 狀態與疑難排解

執行步驟

安裝設定地端 Master MySQL 8

安裝 Master MySQL 8

安裝 MySQL 8，預設 Ubuntu 24.04 repo 就有，可直接使用

on AWS EC2
1
sudo apt update
2
sudo apt install mysql-server -y
3
sudo systemctl enable mysql
4
sudo systemctl start mysql
5
# (Optional) 設定密碼，正式環境一定要設定
6
sudo mysql_secure_installation

設定 Master MySQL 8

設定 MySQL 8 設定檔成 Master，修改設定檔中相關設定值

  • binlog_do_db 與 binlog_ignore_db 參數通常二選一
    • binlog_do_db：要複寫的資料庫，沒指定預設為全部資料庫都複寫
    • binlog_ignore_db：要忽略複寫的資料庫，沒指定預設為全部資料庫都不排除
  • 多個資料庫記錄方法如下範例
/etc/mysql/mysql.conf.d/mysqld.cnf
1
# 只有 lab1 與 lab2 資料庫要複寫，其他不會同步
2
binlog_do_db              = lab1
3
binlog_do_db              = lab2
4


5
# 只有 lab1 與 lab2 資料庫不要複寫，其他會同步
6
binlog_ignore_db          = lab1
7
binlog_ignore_db          = lab2

以下為完整設定範例，可依照需求自行調整

/etc/mysql/mysql.conf.d/mysqld.cnf
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
# 以下請自行選用調整
13
binlog_do_db              = dbo
14
# binlog_ignore_db          = lab2

設定完成後，重啟動 MySQL 服務

on AWS EC2
1
sudo systemctl restart mysql

可發現預設使用 caching_sha2_password 進行驗證

on AWS EC2 MySQL
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)

建立 Master MySQL 8 複寫帳號

  • 建立複寫專用帳號
    • % 可替換成指定來源 ip 以增加安全性
    • replicator 可替換其他帳號
    • replicatorpass 可替換其他密碼
    • 使用 mysql_native_password 驗證方式建立帳號
  • 設定帳號複寫權限：REPLICATION CLIENT 與 REPLICATION SLAVE
on AWS EC2 MySQL
1
CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'replicatorpass';
2
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'replicator'@'%';
3
FLUSH PRIVILEGES;

建立 Slave RDS for MySQL 8

使用 AWS GUI 介面進襲操作建立，並確定能使用 EC2 主機連線到 RDS 主機即可

資料搬遷

將 EC2 中資料庫匯出，如果沒有資料可使用 MySQL 官方範例資料庫，匯出前請進行以下操作

鎖定 Master MySQL 資料庫

on AWS EC2 MySQL
1
FLUSH TABLES WITH READ LOCK;
2
SET GLOBAL read_only = ON;

確認 binlog 狀態資訊

將 File、Position 資訊記錄下來，供後續使用，同時確定 Binlog_Do_DB 是否正確

on AWS EC2 MySQL
1
SHOW MASTER STATUS\G;;
2


3
# 預期結果
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)

資料匯出匯入

  • 將欲同步資料庫進行匯出，可使用官方提供之指令，或使用其他工具匯出均可
  • 接著把匯出資料匯入到 RDS for MySQL 中，使用指令或其他第三方工具均可

解除鎖定 Master MySQL 資料庫

on AWS EC2 MySQL
1
SET GLOBAL read_only = OFF;
2
UNLOCK TABLES;

設定 RDS replication

將前面 SHOW MASTER STATUS 取得之資訊填入 RDS for MySQL 的指令中

  • master_host 為主機之 ip 或 domain name
  • 更多關於 RDS for MySQL 設定參數可參考此官方文件
RDS for MySQL
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)

確認 replication 狀態與疑難排解

確認同步成功狀態

連線到 Slave 資料庫(RDS for MySQL)，輸入 SHOW SLAVE STATUS\G; 指令，可確認同步狀態

  • 其中 Slave_IO_Running 與 Slave_SQL_Running 狀態均要為 Yes
  • 且 Last_Error 與 Last_IO_Error 皆為空值
  • 留意 Read_Master_Log_Pos 、Exec_Master_Log_Pos 與 Relay_Log_Pos 數值
RDS for MySQL
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)
  • 測試新增一些資料，除了確認資料表同步外，可輸入 SHOW SLAVE STATUS\G; 確認同步是否成功
  • 可發現 Read_Master_Log_Pos 、Exec_Master_Log_Pos 與 Relay_Log_Pos 數值會隨之更新
RDS for MySQL
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)

疑難排解

Authentication requires secure connection
  • 出現 Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. 錯誤
RDS for MySQL
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)
  • 可以發現 replicator 帳號使用 caching_sha2_password 驗證
on AWS EC2 MySQL
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)
  • 使用以下指令調整複寫用帳號驗證方式，接著確認生效狀態即可
on AWS EC2 MySQL
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)

參考資料