文章目錄

• 簡介
• 部署InnoDB ReplicaSet
• • 要求
  • 環境
  • • 規劃
    • 前期準備
  • 安裝MySQL
  • 安裝MySQL Shell
  • 配置InnoDB ReplicaSet
  • 配置MySQL Router
• InnoDB ReplicaSet日常管理
• 參考文檔
• 參考文檔

簡介

MySQL Shell的AdminAPI新增了 對InnoDB ReplicaSet的支持，可以用與InnoDB Cluster類似的方式管理一組運行基于GTID的異步復制的MySQL實例。InnoDB ReplicaSet由一個主和多個從組成。您可以使用ReplicaSet對象和AdminAPI操作，例如檢查InnoDB ReplicaSet的狀態，并在發生故障時手動故障轉移到新的主數據庫。與InnoDB Cluster類似，MySQL Router支持針對InnoDB ReplicaSet的引導，這意味著可以自動配置MySQL Router以使用InnoDB ReplicaSet，而無需手動配置它。這使InnoDB ReplicaSet成為啟動和運行MySQL復制和MySQL Router的快速簡便方法，使其非常適合橫向擴展讀取。

與InnoDB Cluster相比，InnoDB ReplicaSet具有如下限制：

• 沒有自動故障切換，如果主不可用，則需要手動觸發故障轉移
• 無法防止因意外或不可用導致的部分數據丟失(因為是異步復制)
• 無法防止崩潰或不可用后出現不一致情況。

部署InnoDB ReplicaSet

要求

• 僅支持運行MySQL 8.0及更高版本的實例
• 僅支持基于GTID的復制，二進制日志文件位置復制與InnoDB ReplicaSet不兼容
• 僅支持基于行的復制（RBR），不支持基于語句的復制（SBR）
• 不支持復制過濾器
• 在任何實例下都不允許Unmanaged replication channels
• 一個ReplicaSet最多包含一個主實例，并且支持一個或多個第二實例。盡管可以添加到副本集的輔助副本數量沒有限制，但是連接到副本集的每個MySQL Router都會監視每個實例。因此，添加到ReplicaSet的實例越多，必須執行的監視越多。
• ReplicaSet必須完全由MySQL Shell管理。例如，復制帳戶由MySQL Shell創建和管理。不支持在MySQL Shell之外對實例進行配置更改，例如，直接使用SQL語句更改主實例。應該始終將MySQL Shell與InnoDB ReplicaSet一起使用。

環境

規劃

IP:PORT主機名server-id系統版本MySQL版本MySQL Shell版本MySQL Router版本

192.168.240.81:3310	innodb_cluster_01	81	CentOS7.6	8.0.21	mysql-shell-8.0.21	mysql-router-8.0.21
192.168.240.82:3310	innodb_cluster_02	82	CentOS7.6	8.0.21	-	-
192.168.240.83:3310	innodb_cluster_03	83	CentOS7.6	8.0.21	-	-

MySQL Shell ,Rpm包下載地址:https://dev.mysql.com/downloads/shell/

MySQL Router,Rpm包下載地址:https://dev.mysql.com/downloads/router/

MySQL ，二進制包下載地址：https://dev.mysql.com/downloads/mysql/

前期準備

設置主機名

InnoDB ReplicaSet集群內部節點主機名必須唯一，可以通過hostname命令設置

按照先前規劃，填寫/etc/hosts文件

[root@localhost ~]# cat /etc/hosts 192.168.240.81 innodb_rs_01 192.168.240.82 innodb_rs_02 192.168.240.83 innodb_rs_03

修改本機主機名(每臺機根據情況修改自己主機名)

hostnamectl set-hostname innodb_rs_01

關閉防火墻與selinux

systemctl stop firewalld setenforce 0

安裝MySQL

把MySQL二進制安裝包上傳到/usr/local/src目錄下

cd /usr/local/src tar xf mysql-8.0.21-linux-glibc2.17-x86_64-minimal.tar.xz mv mysql-8.0.21-linux-glibc2.17-x86_64-minimal /usr/local/mysql8.0 echo 'export PATH=/usr/local/mysql8.0/bin:$PATH' >> /etc/profile . /etc/profile

創建目錄結構(所有節點)

useradd -M -s /sbin/nologin mysql mkdir -pv /datadir/{temp,log,data} chown -R mysql:mysql /datadir

初始化數據庫(所有節點)

mysqld --datadir=/datadir/data --user=mysql --initialize-insecure chown -R mysql:mysql /datadir

修改配置文件(所有節點)

[mysqld] basedir=/usr/local/mysql8.0 user=mysql port=3310 datadir=/datadir/data log-error=/datadir/log/err.log pid-file=/datadir/temp/mysqld.pid socket=/datadir/temp/mysqld.sock symbolic-links=0 server_id=81 gtid-mode=on enforce-gtid-consistency=truelog_bin=/datadir/log/binlog binlog_format=ROW[client] socket=/datadir/temp/mysqld.sock

只需要修改server_id這一項即可(保證所有MySQL服務器都不同)

啟動數據庫(所有節點)

mysqld_safe --defaults-file=/etc/my.cnf --daemonize

進入mysql，創建ir用戶(所有節點)

set sql_log_bin=OFF; CREATE USER 'ir'@'%' IDENTIFIED BY '123456'; GRANT ALL ON *.* to 'ir'@'%' WITH GRANT OPTION; set sql_log_bin=ON;

安裝MySQL Shell

上傳MySQL Shell包到innodb_rs_01的/usr/local/src目錄下

cd /usr/local/src yum install mysql-shell-8.0.21-1.el7.x86_64.rpm -y

配置InnoDB ReplicaSet

進入MySQL Shell（innodb_rs_01節點執行）

mysqlsh

創建rsadmin的集群管理員(此處以innodb_rs_01節點為例)

MySQL JS > dba.configureReplicaSetInstance('ir@innodb_rs_01:3310', {clusterAdmin: "'rsadmin'@'%'"}); Please provide the password for 'ir@innodb_rs_01:3310': ******* # 此處填寫ir用戶的密碼 Save password for 'ir@innodb_rs_01:3310'? [Y]es/[N]o/Ne[v]er (default No): y # 是否記住密碼，如果選擇y則下次連接無需重新輸入密碼 Configuring local MySQL instance listening at port 3310 for use in an InnoDB ReplicaSet...This instance reports its own address as innodb_rs_01:3310 Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. Password for new account: ******# 此處填寫'rsadmin'@'%' 想要設置的密碼 Confirm password: ****** # 確認'rsadmin'@'%'密碼The instance 'innodb_rs_01:3310' is valid to be used in an InnoDB ReplicaSet. Cluster admin user 'rsadmin'@'%' created. The instance 'innodb_rs_01:3310' is already ready to be used in an InnoDB ReplicaSet. # 表示已經就緒，可以創建innodb ReplicaSet

dba.configureReplicaSetInstance()函數可以創建管理員帳戶。使用正確的權限來創建帳戶來管理InnoDB集群和InnoDB副本集。

在同一集群或副本集的所有實例中，管理員帳戶必須具有相同的用戶名和密碼。

創建Innodb ReplicaSet（innodb_rs_01節點執行）

mysql-js> \connect ir@innodb_rs_01:3310 MySQL innodb_rs_01:3310 ssl JS > var rs = dba.createReplicaSet("example") A new replicaset with instance 'innodb_rs_01:3310' will be created.* Checking MySQL instance at innodb_rs_01:3310This instance reports its own address as innodb_rs_01:3310 innodb_rs_01:3310: Instance configuration is suitable.* Updating metadata...ReplicaSet object successfully created for innodb_rs_01:3310. Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

查看ReplicaSet狀態（innodb_rs_01節點執行）

MySQL innodb_rs_01:3310 ssl JS > rs.status() {"replicaSet": { "name": "example", # 復制集名稱"primary": "innodb_rs_01:3310", # 主節點"status": "AVAILABLE", # 狀態：可用"statusText": "All instances available.", # 狀態備注信息"topology": { # 當前拓撲"innodb_rs_01:3310": { "address": "innodb_rs_01:3310", # 復制集通訊地址"instanceRole": "PRIMARY", # 角色，主/從"mode": "R/W", # 模式，主節點可讀可寫，從節點只讀"status": "ONLINE" # 節點狀態}},"type": "ASYNC" # 復制類型 異步} }

添加innodb_rs_02:3310實例進入集群（innodb_rs_01節點執行）

MySQL innodb_rs_01:3310 ssl JS > rs.addInstance('innodb_rs_02:3310') Adding instance to the replicaset...* Performing validation checksThis instance reports its own address as innodb_rs_02:3310 innodb_rs_02:3310: Instance configuration is suitable.* Checking async replication topology...* Checking transaction state of the instance...NOTE: The target instance 'innodb_rs_02:3310' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'innodb_rs_02:3310' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.# 輸入I，選擇增量恢復，需要確保所有實例GTID打開，以及沒有被purged的事務，且gitd set必須是主節點的子集 Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): i * Updating topology ** Configuring innodb_rs_02:3310 to replicate from innodb_rs_01:3310 ** Waiting for new instance to synchronize with PRIMARY...The instance 'innodb_rs_02:3310' was added to the replicaset and is replicating from innodb_rs_01:3310.

添加innodb_rs_03:3310實例進入集群（innodb_rs_01節點執行）

MySQL innodb_rs_01:3310 ssl JS > rs.addInstance('innodb_rs_03:3310') Adding instance to the replicaset...* Performing validation checksThis instance reports its own address as innodb_rs_03:3310 innodb_rs_03:3310: Instance configuration is suitable.* Checking async replication topology...* Checking transaction state of the instance...NOTE: The target instance 'innodb_rs_03:3310' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'innodb_rs_03:3310' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.# 輸入I，選擇增量恢復，需要確保所有實例GTID打開，以及沒有被purged的事務，且gitd set必須是主節點的子集 Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): i * Updating topology ** Configuring innodb_rs_03:3310 to replicate from innodb_rs_01:3310 ** Waiting for new instance to synchronize with PRIMARY...The instance 'innodb_rs_03:3310' was added to the replicaset and is replicating from innodb_rs_01:3310.

查看集群狀態

MySQL innodb_rs_01:3310 ssl JS > rs.status() {"replicaSet": {"name": "example",# 復制集名稱"primary": "innodb_rs_01:3310",# 主節點"status": "AVAILABLE",# 狀態：可用"statusText": "All instances available.", # 狀態備注信息"topology": { # 當前拓撲"innodb_rs_01:3310": {"address": "innodb_rs_01:3310", # 復制集通訊地址"instanceRole": "PRIMARY", # 角色，主/從"mode": "R/W", # 模式，主節點可讀可寫，從節點只讀"status": "ONLINE" # 節點狀態},"innodb_rs_02:3310": {"address": "innodb_rs_02:3310","instanceRole": "SECONDARY","mode": "R/O","replication": {"applierStatus": "APPLIED_ALL","applierThreadState": "Slave has read all relay log; waiting for more updates","receiverStatus": "ON","receiverThreadState": "Waiting for master to send event","replicationLag": null},"status": "ONLINE"},"innodb_rs_03:3310": {"address": "innodb_rs_03:3310","instanceRole": "SECONDARY","mode": "R/O","replication": {"applierStatus": "APPLIED_ALL","applierThreadState": "Slave has read all relay log; waiting for more updates","receiverStatus": "ON","receiverThreadState": "Waiting for master to send event","replicationLag": null},"status": "ONLINE"}},"type": "ASYNC" # 復制類型 異步} }

配置MySQL Router

上傳MySQL Router RPM包innodb_rs_03節點上的/usr/local/src目錄下

cd /usr/local/src yum install mysql-router-community-8.0.21-1.el7.x86_64.rpm -y

MySQL Router對接InnoDB Cluster

mysqlrouter --bootstrap ir@innodb_rs_01:3310 --user=mysqlrouter

啟動MySQL Router

systemctl start mysqlrouter

會啟動4個端口

• 6446 - 對于傳統MySQL協議讀寫會話，MySQL路由器將傳入連接重定向到Primary服務器實例。
• 6447 - 對于傳統MySQL協議只讀會話，MySQL路由器將傳入連接重定向到其中一個從服務器實例。
• 64460 - 對于X協議讀寫會話，MySQL路由器將傳入連接重定向到Primary服務器實例。
• 64470 - 對于X協議只讀會話，MySQL路由器將傳入連接重定向到其中一個從服務器實例。

連接讀寫端口,進行測試

# mysql -uir -p -P6446 -h192.168.240.83 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 109 Server version: 8.0.21 MySQL Community Server - GPLCopyright (c) 2000, 2020, 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 '\h' for help. Type '\c' to clear the current input statement.mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 81 | +-------------+ 1 row in set (0.00 sec)

InnoDB ReplicaSet日常管理

連接InnoDB 復制集

# 連接其中一個復制集節點MySQL JS > \c ir@innodb_rs_02:3310 # 獲取復制集連接MySQL innodb_rs_02:3310 ssl JS > var rs = dba.getReplicaSet() # 后續就可以直接使用rs對象管理集群

查看集群狀態

MySQL innodb_rs_02:3310 ssl JS > rs.status() {"replicaSet": {"name": "example","primary": "innodb_rs_01:3310","status": "AVAILABLE","statusText": "All instances available.","topology": {"innodb_rs_01:3310": {"address": "innodb_rs_01:3310","instanceRole": "PRIMARY","mode": "R/W","status": "ONLINE"},"innodb_rs_02:3310": {"address": "innodb_rs_02:3310","instanceRole": "SECONDARY","mode": "R/O","replication": {"applierStatus": "APPLIED_ALL","applierThreadState": "Slave has read all relay log; waiting for more updates","receiverStatus": "ON","receiverThreadState": "Waiting for master to send event","replicationLag": null},"status": "ONLINE"},"innodb_rs_03:3310": {"address": "innodb_rs_03:3310","instanceRole": "SECONDARY","mode": "R/O","replication": {"applierStatus": "APPLIED_ALL","applierThreadState": "Slave has read all relay log; waiting for more updates","receiverStatus": "ON","receiverThreadState": "Waiting for master to send event","replicationLag": null},"status": "ONLINE"}},"type": "ASYNC"} }

新增實例

# 新增innodb_rs_03:3310節點 MySQL innodb_rs_02:3310 ssl JS > rs.addInstance('innodb_rs_03:3310')

強制切換主節點

# 當主節點down了，就需要強制切換主 rs.forcePrimaryInstance('innodb_rs_03:3310')

設置主節點

MySQL innodb_rs_02:3310 ssl JS > rs.setPrimaryInstance('innodb_rs_03:3310') innodb_rs_03:3310 will be promoted to PRIMARY of 'example'. The current PRIMARY is innodb_rs_01:3310.* Connecting to replicaset instancess ** Connecting to innodb_rs_01:3310 ** Connecting to innodb_rs_02:3310 ** Connecting to innodb_rs_03:3310 ** Connecting to innodb_rs_01:3310 ** Connecting to innodb_rs_02:3310 ** Connecting to innodb_rs_03:3310* Performing validation checks ** Checking async replication topology... ** Checking transaction state of the instance...* Synchronizing transaction backlog at innodb_rs_03:3310* Updating metadata* Acquiring locks in replicaset instances ** Pre-synchronizing SECONDARIES ** Acquiring global lock at PRIMARY ** Acquiring global lock at SECONDARIES* Updating replication topology ** Configuring innodb_rs_01:3310 to replicate from innodb_rs_03:3310 ** Changing replication source of innodb_rs_02:3310 to innodb_rs_03:3310innodb_rs_03:3310 was promoted to PRIMARY.

移除實例

# 從ReplicaSet中移除innodb_rs_03:3310節點 MySQL innodb_rs_02:3310 ssl JS > rs.removeInstance('innodb_rs_03:3310')

查看MySQL Router信息

MySQL innodb_rs_02:3310 ssl JS > rs.listRouters() {"replicaSetName": "example","routers": {"innodb_rs_03::system": {"hostname": "innodb_rs_03","lastCheckIn": "2020-08-06 00:09:40","roPort": 6447,"roXPort": 64470,"rwPort": 6446,"rwXPort": 64460,"version": "8.0.21"}} }

參考文檔

)

7. 查看MySQL Router信息```jsMySQL innodb_rs_02:3310 ssl JS > rs.listRouters() {"replicaSetName": "example","routers": {"innodb_rs_03::system": {"hostname": "innodb_rs_03","lastCheckIn": "2020-08-06 00:09:40","roPort": 6447,"roXPort": 64470,"rwPort": 6446,"rwXPort": 64460,"version": "8.0.21"}} }

參考文檔

• https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-replicasets.html

總結

以上是生活随笔為你收集整理的InnoDB ReplicaSet的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。