之前，我介紹過一篇 。
MySQL Innodb Cluster = MySQL Shell + MySQL Router + MySQL Group Replication(MGR)
全程由 MySQL Shell 來管理操作 MGR 的聚合套件。MySQL 8.0.19 發佈後，這種組合延伸到 MySQL Replication（主從複製），也就是 MySQL Shell + MySQL Router + MySQL Replication。
MySQL 主從複製，相信大家已經非常瞭解了，原理大概如下圖：
一、環境準備
舉個例子，搭建主從的基本步驟大概如下：
- 主機 A：192.168.2.171:4010
- 從機 B：192.168.2.171:4011
- 從機 C：192.168.2.171:4012
主機和從機經過簡單的配置，後期其他配置自己任意搭配（不同的主機要將 server-id 分別改成不同的就行了）
<code># my.cnfserver-id = 4010log-bin=yttenforce_gtid_consistency=ongtid_mode=onlog_slave_updates=onmaster-info-repository=tablerelay-log-info-repository=tableslave_parallel_type=logicalslave_parallel_workers=4/<code>
重啟所有實例
<code>systemctl restart mysql@{4010,4011,4012}/<code>然後在 A 上創建主從需要的用戶，並且分別在 B 和 C 上執行 change master to 命令配置從機即可。
我上面只是涉及到主從從零開始的場景，如果說主機 A 有數據，還得重加步驟。關於這點資料很多，我這裡也就不多介紹了。那麼對主從的管理沒有搭建這麼簡單，稍微要繁瑣些，譬如要實現如下的功能點：
1. 主從級別互換：主機降級為從機；從機升級為主機。
2. 從機由於異常，無法恢復正常；或者恢復正常了，發現數據和主機有差異，必須要重做從機，恢復主從關係。
3. 列出主從關係，可能要自己寫個小腳本或者是利用 MySQL Utility 工具集或者是其他的工具集合。
那接下來來看看 MySQL 8.0.19 中 MySQL Shell 新特性：副本集以及管理。
二、副本集
副本集也就是極度簡化了對 MySQL 主從的相關管理操作。
那接下來還是用剛才那三個實例舉例說明 MySQL Shell 怎麼簡化了相關操作。
進入 MySQL X 端口 40100
<code>root@ytt-pc:/home/ytt/scripts# mysqlsh mysqlx://ytt@ytt-pc:40100MySQL Shell 8.0.19Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates.Other names may be trademarks of their respective owners.Type '\\help' or '\\?' for help; '\\quit' to exit.Creating an X protocol session to 'ytt@ytt-pc:40100'Fetching schema names for autocompletion... Press ^C to stop.Your MySQL connection id is 12 (X protocol)Server version: 8.0.19 MySQL Community Server - GPLNo default schema selected; type \\\\use <schema> to set one./<schema>/<code>
2.1 創建副本集 ytt_replicaset
<code>MySQL ytt-pc:40100+ ssl JS > var c1 = dba.createReplicaSet("ytt_replicaset")A new replicaset with instance 'ytt-pc:4010' will be created.* Checking MySQL instance at ytt-pc:4010This instance reports its own address as ytt-pc:4010ytt-pc:4010: Instance configuration is suitable.* Updating metadata...ReplicaSet object successfully created for ytt-pc:4010.Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status./<code>查看幫助
可以看到所有的方法：c1.help()
查看當前副本集的狀態
默認 RW，單機運行
<code> MySQL ytt-pc:40100+ ssl JS > c1.status(){ "replicaSet": { "name": "ytt_replicaset", "primary": "ytt-pc:4010", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "ytt-pc:4010": { "address": "ytt-pc:4010", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" } }, "type": "ASYNC" }}/<code>添加從機
默認用克隆的方式複製數據
<code> MySQL ytt-pc:40100+ ssl JS > c1.addInstance('ytt@ytt-pc:4011')Adding instance to the replicaset...* Performing validation checksThis instance reports its own address as ytt-pc:4011ytt-pc:4011: Instance configuration is suitable.* Checking async replication topology...* Checking transaction state of the instance...WARNING: A GTID set check of the MySQL instance at 'ytt-pc:4011' determined that it contains transactions that do not originate from the replicaset, which must be discarded before it can join the replicaset.ytt-pc:4011 has the following errant GTIDs that do not exist in the replicaset:a19a4ac4-3a98-11ea-8f47-9cb6d0e27d15:1-7WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of ytt-pc:4011 with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.Please select a recovery method [C]lone/[A]bort (default Abort): C* Updating topologyWaiting for clone process of the new member to complete. Press ^C to abort the operation.* Waiting for clone to finish...NOTE: ytt-pc:4011 is being cloned from ytt-pc:4010** Stage DROP DATA: Completed** Clone Transfer FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% CompletedNOTE: ytt-pc:4011 is shutting down...* Waiting for server restart... ready* ytt-pc:4011 has restarted, waiting for clone to finish...* Clone process has finished: 59.64 MB transferred in about 1 second (~1.00 B/s)** Configuring ytt-pc:4011 to replicate from ytt-pc:4010** Waiting for new instance to synchronize with PRIMARY...The instance 'ytt-pc:4011' was added to the replicaset and is replicating from ytt-pc:4010./<code>用同樣的方法添加實例 C
<code>MySQL ytt-pc:40100+ ssl JS > c1.addInstance('ytt@ytt-pc:4012')/<code>接下來看看新的主從狀態，此時 ytt-pc:4010 為主機，ytt-pc:4011 和 ytt-pc:4012 為從機，並且狀態都正常
<code> MySQL ytt-pc:40100+ ssl JS > c1.status(){ "replicaSet": { "name": "ytt_replicaset", "primary": "ytt-pc:4010", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "ytt-pc:4010": { "address": "ytt-pc:4010", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" }, "ytt-pc:4011": { "address": "ytt-pc:4011", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for master to send event", "replicationLag": null }, "status": "ONLINE" }, "ytt-pc:4012": { "address": "ytt-pc:4012", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for master to send event", "replicationLag": null }, "status": "ONLINE" } }, "type": "ASYNC" }} MySQL ytt-pc:40100+ ssl JS >/<code>2.2 配置 MySQLRouter 路由實例
先用 MySQLrouter 來配置讀寫分離，並且分配虛擬端口
<code>root@ytt-pc:/home/ytt/scripts# mysqlrouter --bootstrap ytt@ytt-pc:4010 --user=mysqlrouter --force-password-validation --report-host ytt-pcPlease enter MySQL password for ytt:# Bootstrapping system MySQL Router instance...- Creating account(s) (only those that are needed, if any)- Verifying account (using it to run SQL queries that would be run by Router)- Storing account in keyring- Adjusting permissions of generated files- Creating configuration /etc/mysqlrouter/mysqlrouter.confExisting configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'# MySQL Router configured for the InnoDB ReplicaSet 'ytt_replicaset'After this MySQL Router has been started with the generated configuration/<code>
重啟 mysqlrouter
<code>$ /etc/init.d/mysqlrouter restartor$ systemctl start mysqlrouteror$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf//重啟完畢the cluster 'ytt_replicaset' can be reached by connecting to:## MySQL Classic protocol- Read/Write Connections: ytt-pc:6446- Read/Only Connections: ytt-pc:6447## MySQL X protocol- Read/Write Connections: ytt-pc:64460- Read/Only Connections: ytt-pc:64470重啟mysqlrouterroot@ytt-pc:/home/ytt/scripts# systemctl restart mysqlrouter/<code>
查看 router 路由
用 listRouters 方法詳細的展示出 mysqlrouter 的路由信息，寫端口為 6446/64460，讀端口為 6447/64470
<code> MySQL ytt-pc:40100+ ssl JS > c1.listRouters(){ "replicaSetName": "ytt_replicaset", "routers": { "ytt-pc::system": { "hostname": "ytt-pc", "lastCheckIn": "2020-01-19 17:26:08", "roPort": 6447, "roXPort": 64470, "rwPort": 6446, "rwXPort": 64460, "version": "8.0.19" } }}/<code>2.3 測試讀寫端口
連接 6446，路由到實例 4010
<code>root@ytt-pc:/home/ytt/scripts# mysql -uytt -p -hytt-pc -P6446 -e "select @@port" -ssEnter password:4010/<code>
連接到 6447，隨機路由到實例 4011 或者 4012
<code>root@ytt-pc:/home/ytt/scripts# mysql -uytt -p -hytt-pc -P6447 -e "select @@port" -ssEnter password:4011root@ytt-pc:/home/ytt/scripts# mysql -uytt -p -hytt-pc -P6447 -e "select @@port" -ssEnter password:4012/<code>
以上簡單的演示下 mysql shell 操作主從複製的基本方法，下來分別看看主從級別互換、從實例刪除、從實例加入場景。
2.4 場景實驗
斷開連接
<code>MySQL ytt-pc:40100+ ssl JS > c1.disconnect();/<code>
重新得到副本集的名稱
<code>MySQL ytt-pc:40100+ ssl JS > var c1 = dba.getReplicaSet()You are connected to a member of replicaset 'ytt_replicaset'./<code>
提升從機為新主機，這裡操作的實例是 ytt-pc:4012
<code> MySQL ytt-pc:40100+ ssl JS > c1.setPrimaryInstance('ytt@ytt-pc:4012');ytt-pc:4012 will be promoted to PRIMARY of 'ytt_replicaset'.The current PRIMARY is ytt-pc:4010.- Connecting to replicaset instances ** Connecting to ytt-pc:4010 ** Connecting to ytt-pc:4011 ** Connecting to ytt-pc:4012 ** Connecting to ytt-pc:4010 ** Connecting to ytt-pc:4011 ** Connecting to ytt-pc:4012- Performing validation checks ** Checking async replication topology... ** Checking transaction state of the instance...- Synchronizing transaction backlog at ytt-pc:4012- Updating metadata- Acquiring locks in replicaset instances ** Pre-synchronizing SECONDARIES ** Acquiring global lock at PRIMARY ** Acquiring global lock at SECONDARIES- Updating replication topology ** Configuring ytt-pc:4010 to replicate from ytt-pc:4012 ** Changing replication source of ytt-pc:4011 to ytt-pc:4012ytt-pc:4012 was promoted to PRIMARY./<code>查看新狀態，4012 為新的主機，4011 和 4010 為從機
<code> MySQL ytt-pc:40100+ ssl JS > c1.status(){ "replicaSet": { "name": "ytt_replicaset", "primary": "ytt-pc:4012", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "ytt-pc:4010": { "address": "ytt-pc:4010", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for master to send event", "replicationLag": null }, "status": "ONLINE" }, "ytt-pc:4011": { "address": "ytt-pc:4011", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for master to send event", "replicationLag": null }, "status": "ONLINE" }, "ytt-pc:4012": { "address": "ytt-pc:4012", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" } }, "type": "ASYNC" }} MySQL ytt-pc:40100+ ssl JS >/<code>從副本集中移除實例 4010
<code> MySQL ytt-pc:40100+ ssl JS > c1.removeInstance("ytt@ytt-pc:4010");The instance 'ytt-pc:4010' was removed from the replicaset./<code>從結果看到，4010 被成功移除，接下來再把 4010 添加進去
<code> MySQL ytt-pc:40100+ ssl JS > c1.addInstance("ytt@ytt-pc:4010")Adding instance to the replicaset...* Updating topology** Configuring ytt-pc:4010 to replicate from ytt-pc:4012** Waiting for new instance to synchronize with PRIMARY...The instance 'ytt-pc:4010' was added to the replicaset and is replicating from ytt-pc:4012./<code>三、總結
這篇介紹了 MySQL Shell 的新特性，管理 MySQL 主從複製，可以看到比傳統的方式要簡單的多。
分享到:
閱讀更多 愛可生 的文章
