AWSRDS多可用區+EC2例項跑mysql從庫的測試

科技小能手發表於2017-11-12
MySql

RDS配置多可用區的時候，主庫有自動failover的功能，具體生效時間以例項大小和配置有關。線上有一個業務之前一主兩從都是用的RDS的例項，但是後來出於成本考慮想要把mysql從RDS遷移至EC2。由於不能停業務，最終決定主庫繼續放在RDS，在EC2上重做從庫。


既然決定實施，那就要測一下主庫failover的時候的對從庫的影響。


1，申請一個配置有多可用區的測試例項：masterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com


2，授權複製帳號和測試帳號（sysbench做測試）

1
2
3
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`172.31.%` IDENTIFIED BY `repl`; 
grant select,create,index,insert,update,delete,drop  on *.* to hatest@`172.31.%` identified by `123456`;
flush privileges;


3，得到主庫上的binlog位置

1
2
3
4
5
6
7
8
>show master statusG
*************************** 1. row ***************************
             File: mysql-bin-changelog.000023
         Position: 5768524
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)


4，在EC2準備一臺從庫，指定好主從關係

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
>CHANGE MASTER TO MASTER_HOST=`masterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com`, MASTER_PORT=3306, MASTER_LOG_FILE=`mysql-bin-changelog.000023`, MASTER_LOG_POS=5768524, MASTER_USER=`repl`, MASTER_PASSWORD=`repl`; 
Query OK, 0 rows affected, 2 warnings (0.02 sec)
>start slave;
Query OK, 0 rows affected (0.00 sec)
>show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: masterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000023
          Read_Master_Log_Pos: 5768524
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 293
        Relay_Master_Log_File: mysql-bin-changelog.000023
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5768524
              Relay_Log_Space: 460
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 459205842
                  Master_UUID: 46da27c8-007c-11e7-9b5b-06051bcfdaae
             Master_Info_File: /data/dbdata/mysqldata/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


5，用sysbench往RDS寫入資料的時候，通過重啟例項來模擬failover

1
/usr/local/sysbench/bin/sysbench --mysql-host=masterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=hatest --mysql-password=123456   --mysql-db=failovertest --oltp-tables-count=5 --oltp-table-size=6000000  --max-requests=100000000 --test=/usr/local/sysbench/tests/db/select.lua prepare


6，此時連mysql的時候會報錯，管理後臺顯示該例項正在修復中

1
2
$ mysql -uroot -p -hmasterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com 
ERROR 2003 (HY000): Can`t connect to MySQL server on `masterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com` (110)


7，檢視slave的複製狀態是否正常。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
>show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: masterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000024
          Read_Master_Log_Pos: 24121968
               Relay_Log_File: relay-log.000004
                Relay_Log_Pos: 24122141
        Relay_Master_Log_File: mysql-bin-changelog.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 24121968
              Relay_Log_Space: 24122365
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 459205842
                  Master_UUID: 46da27c8-007c-11e7-9b5b-06051bcfdaae
             Master_Info_File: /data/dbdata/mysqldata/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

多次執行show slave statusG命令發現複製狀態都顯示正常，但是位置一直卡在mysql-bin-changelog.000024 24121968這裡不動，此時RDS上面的主庫一直有用sysbench寫資料進來。可以斷定雖然復現狀態顯示正常，但是其實連線已經失效。


8，我們在RDS主庫上面檢視一下binary log的資訊可以發現從庫已經執行完了mysql-bin-changelog.000024日誌的所有event,從日誌偏移量可以看出mysql-bin-changelog.000024是failover之前的日誌，mysql-bin-changelog.000025是failover之後的日誌。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
>show binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.000020 | 134243795 |
| mysql-bin-changelog.000021 | 111694820 |
| mysql-bin-changelog.000022 | 134243505 |
| mysql-bin-changelog.000023 | 134244377 |
| mysql-bin-changelog.000024 |  24121968 |
| mysql-bin-changelog.000025 | 134244377 |
| mysql-bin-changelog.000026 | 134243504 |
| mysql-bin-changelog.000027 | 134243503 |
| mysql-bin-changelog.000028 | 114316703 |
+----------------------------+-----------+
9 rows in set (0.01 sec)


9，怎樣才能讓從庫繼續從RDS主庫拉日誌呢，撓頭想了一下之後給你頓時有了思路，由於EC2的從庫連線RDS是用的域名，failover後這個域名已經指向了另外一個ip，而當前複製的連線一直沒有釋放，在是連的之前對應的ip。於是在從庫上面執行stop slave;start slave;再檢視複製狀態時，發現已經正常，從庫已經開始拉failover之後的日誌了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
>show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: masterfailovertest.cojn00n8zy2d.us-east-1.rds.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000025
          Read_Master_Log_Pos: 86524981
               Relay_Log_File: relay-log.000006
                Relay_Log_Pos: 63976502
        Relay_Master_Log_File: mysql-bin-changelog.000025
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 63976329
              Relay_Log_Space: 86525494
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 191
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 459205842
                  Master_UUID: 46da27c8-007c-11e7-9b5b-06051bcfdaae
             Master_Info_File: /data/dbdata/mysqldata/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: update
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


測試完畢，由此可見主庫放RDS，從庫放EC2這樣的形式是可行的，但是當檢測到主庫failover時需要馬上在從庫上面執行stop slave;start slave;命令，這樣從庫才不至於讓從庫落後太久。

本文轉自 emma_cql 51CTO部落格，原文連結:http://blog.51cto.com/chenql/1903187


相關文章