MySQL InnoDB ReplicaSet安裝文檔
2024-01-10 22:38 瀟湘隱者 閱讀(501) 評論(0) 收藏 舉報MySQL副本集介紹
MySQL InnoDB ReplicaSet(也叫MySQL副本集,有些地方也翻譯成MySQL復(fù)制集)是在 MySQL 8.0.19 版本（2020-01-13 Released）之后開始支持的，MySQL副本集中擁有一個primary節(jié)點，一個或多個secondary節(jié)點，它不像MySQL InnoDB Cluster一樣提供故障自愈和多主模式，但是它提供手工的方法添加、移除和配置相關(guān)節(jié)點.說得直白一點，MySQL副本集其實就是將MySQL主從復(fù)制、MySQL Shell、MySQL Router技術(shù)融合起來的一種技術(shù)，它的優(yōu)點是可以幫助用戶快速、簡單的部署和管理主從復(fù)制。但是它的不足與限制也非常多。這里僅僅學(xué)習(xí)、研究MySQL副本集，畢竟技術(shù)都是快速向前迭代發(fā)展的。說不定哪天就蛻變得非常驚艷。
MySQL副本集的限制
下面是官方文檔[1]中介紹的MySQL InnoDB ReplicaSet(MySQL副本集)的一些限制：
沒有自動故障轉(zhuǎn)移功能。在主節(jié)點不可用的情況下，需要使用 AdminAPI 手動觸發(fā)故障轉(zhuǎn)移，然后才能再次進行任何更改。 但是，輔助實例仍可供讀取數(shù)據(jù)。
No automatic failover. In events where the primary becomes unavailable, a failover needs to be triggered manually using AdminAPI before any changes are possible again. However, secondary instances remain available for reads.
無法防止由于意外停止或不可用而導(dǎo)致部分數(shù)據(jù)丟失：在意外停止時未完成的事務(wù)可能會丟失。
No protection from partial data loss due to an unexpected halt or unavailability: Transactions that are not complete at the time of the unexpected halt could be lost.
在意外退出或不可用后無法防止數(shù)據(jù)不一致。如果手動故障轉(zhuǎn)移提升了一個輔助實例，而前一個主實例仍然可用（例如，由于網(wǎng)絡(luò)分區(qū)）， 則裂腦情況可能會導(dǎo)致數(shù)據(jù)不一致。
No protection against inconsistencies after an unexpected exit or unavailability. If a manual failover promotes a secondary instance while the former primary is still available, for example, due to a network partition, the split-brain situation could introduce data inconsistencies.
InnoDB ReplicaSet 不支持多主模式。允許寫入所有成員的經(jīng)典復(fù)制拓撲無法保證數(shù)據(jù)一致性。
InnoDB ReplicaSet does not support a multi-primary mode. Data consistency cannot be guaranteed with classic replication topologies that allow writes to all members.
讀取橫向擴展是有限的。InnoDB ReplicaSet 基于異步復(fù)制，因此無法像 Group Replication 那樣調(diào)整流量控制。
Read scale-out is limited. InnoDB ReplicaSet is based on asynchronous replication, and therefore there is no possible tuning of flow control as there is with Group Replication.
所有從節(jié)點(次要成員)都從單一來源復(fù)制。對于某些特定的用例，這可能會影響單一來源，例如，大量的小更新。
All secondary members replicate from a single source. For some particular use-cases, this could impact the single source, for example, numerous small updates.
僅支持運行 MySQL 8.0 及更高版本的實例。
Only instances running MySQL version 8.0 and later are supported.
僅支持基于 GTID 的復(fù)制，二進制日志文件位置復(fù)制與 InnoDB ReplicaSet 不兼容。
Only GTID-based replication is supported, Binary log file position replication is incompatible with InnoDB ReplicaSet.
僅支持基于行的復(fù)制 (RBR)，不支持基于語句的復(fù)制 (SBR)。
Only Row-Based Replication (RBR) is supported, Statement-Based Replication (SBR) is unsupported.
不支持復(fù)制過濾 Replication filters are not supported.
任何實例上都不允許使用非托管復(fù)制通道 Unmanaged replication channels are not allowed on any instance.
一個 ReplicaSet 最多由一個主實例組成。 支持一個或多個輔助實例。 盡管可以添加到 ReplicaSet 的輔助節(jié)點數(shù)量沒有限制，但連接到 ReplicaSet 的每個 MySQL Router 都必須監(jiān)視每個實例。 因此，添加到 ReplicaSet 的實例越多，監(jiān)控就越多 A ReplicaSet consists of a maximum of one primary instance. One or multiple secondaries are supported. Although there is no limit to the number of secondaries you can add to a ReplicaSet, each MySQL Router connected to a ReplicaSet has to monitor each instance. Therefore, the more instances added to a ReplicaSet, the more monitoring there is.
ReplicaSet 必須由 MySQL Shell 管理。 例如，復(fù)制帳戶是由 MySQL Shell 創(chuàng)建和管理的。 不支持在 MySQL Shell 之外對實例進行配置更改，例如直接使用 SQL 語句更改主實例。 始終使用 MySQL Shell 來處理 InnoDB ReplicaSet。
The ReplicaSet must be managed by MySQL Shell. For example, the replication account is created and managed by MySQL Shell. Making configuration changes to the instance outside MySQL Shell, for example, using SQL statements directly to change the primary instance, is not supported. Always use MySQL Shell to work with InnoDB ReplicaSet.
MySQL副本集的部署
這里計劃在下面兩臺服務(wù)器上安裝MySQL InnoDB ReplicaSet(MySQL副本集)，MySQL數(shù)據(jù)庫版本為8.0.33，操作系統(tǒng)版本為RHEL 8.8
192.168.9.154 dbtest04
192.168.9.159 dbtest05
1：配置/etc/hosts
192.168.9.154 dbtest04
192.168.9.159 dbtest05
如上所示，在配置文件/etc/hosts中加入服務(wù)器的ip/hostname信息。注意需要在MySQL副本集所有primary節(jié)點和secondary節(jié)點上配置/etc/hosts，具體根據(jù)你MySQL副本集的實際情況配置。 注意事項：必須配置/etc/hosts，否則副本集在調(diào)用addInstance等函數(shù)時可能會遇到問題.因為互相默認使用hostname信息來連接的.
2：安裝MySQL Shell
這里使用root用戶安裝MySQL Shell，最好每個節(jié)點都安裝MySQL Shell，另外，MySQL Shell有許多安裝方式，有些安裝方式需要設(shè)置環(huán)境變量，而yum安裝方式不用額外去設(shè)置環(huán)境變量。
# yum localinstall mysql-shell-8.0.35-1.el8.x86_64.rpm
3：安裝MySQL單實例
這里略過MySQl單實例安裝，因為實在是太簡單了（個人一般使用mysql_auto_install.sh腳本安裝），不過需要注意參數(shù)文件my.cnf中一些參數(shù)設(shè)置，有一些基本參數(shù)需要你設(shè)置一下。下面是一些常見得參數(shù)（部分參數(shù)）
server_id=xxx #使用實際的數(shù)字替換，建議使用IP地址的最后一段數(shù)字
gtid_mode=on
enforce_gtid_consistency=on
binlog_transaction_dependency_tracking=writeset
否則，你后續(xù)操作可能遇到下面類似這樣的錯誤：
MySQL 192.168.9.154:3306 ssl JS > dba.configureReplicaSetInstance('rsadmin@192.168.9.154:3306', {clusterAdmin: "'repl'@'192.168.9.%'"});
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...
This instance reports its own address as dbtest04:3306
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: ***********
Confirm password: ***********
applierWorkerThreads will be set to the default value of 4.
NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: n
Dba.configureReplicaSetInstance: Cancelled (RuntimeError)
4：創(chuàng)建rsadmin管理賬號
在各個節(jié)點上創(chuàng)建賬號rsadmin(MySQL Shell使用),因為一般而言，root賬號的hostname限定為localhost（127.0.0.1）,所以我們單獨為MySQL Shell創(chuàng)建一個管理賬號。
create user rsadmin@'192.168.9.%' identified by 'gYj#jfdy874=d2';
grant all on *.* to 'rsadmin'@'192.168.9.%' with grant option;
flush privileges;
上面為了方便，授予rsadmin相當大的權(quán)限，你也可以限制一下它的權(quán)限
GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE USER ON *.* TO `rsadmin`@`192.168.9.%` WITH GRANT OPTION;
GRANT CLONE_ADMIN,CONNECTION_ADMIN,GROUP_REPLICATION_ADMIN
,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN
,ROLE_ADMIN,SYSTEM_VARIABLES_ADMIN
ON *.* TO `rsadmin`@`192.168.9.%` WITH GRANT OPTION;
GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `rsadmin`@`192.168.9.%` WITH GRANT OPTION;
5：檢查實例是否符合副本集的條件
在MySQL shell中檢查各個實例是否符合創(chuàng)建 ReplicaSet 的條件，如果發(fā)現(xiàn)需要修改的地方，就會提示確認修改。
\connect rsadmin@192.168.9.154:3306
dba.configureReplicaSetInstance('rsadmin@192.168.9.154:3306', {clusterAdmin: "'rsadmin'@'192.168.9.%'"});
dba.configureReplicaSetInstance('rsadmin@192.168.9.159:3306', {clusterAdmin: "'rsadmin'@'192.168.9.%'"});
如下所示：192.168.9.159的參數(shù)文件my.cnf沒有修改參數(shù)，會有錯誤提示：
MySQL 192.168.9.154:3306 ssl JS > dba.configureReplicaSetInstance('rsadmin@192.168.9.154:3306', {clusterAdmin: "'rsadmin'@'192.168.9.%'"});
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...
This instance reports its own address as dbtest04:3306
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.
User 'rsadmin'@'192.168.9.%' already exists and will not be created.
applierWorkerThreads will be set to the default value of 4.
The instance 'dbtest04:3306' is valid to be used in an InnoDB ReplicaSet.
The instance 'dbtest04:3306' is already ready to be used in an InnoDB ReplicaSet.
Successfully enabled parallel appliers.
MySQL 192.168.9.154:3306 ssl JS > dba.configureReplicaSetInstance('rsadmin@192.168.9.159:3306', {clusterAdmin: "'rsadmin'@'192.168.9.%'"});
Please provide the password for 'rsadmin@192.168.9.159:3306': **************
Save password for 'rsadmin@192.168.9.159:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring MySQL instance at dbtest05:3306 for use in an InnoDB ReplicaSet...
This instance reports its own address as dbtest05:3306
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.
User 'rsadmin'@'192.168.9.%' already exists and will not be created.
applierWorkerThreads will be set to the default value of 4.
NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Configuring instance...
The instance 'dbtest05:3306' was configured to be used in an InnoDB ReplicaSet.
Restarting MySQL...
NOTE: MySQL server at dbtest05:3306 was restarted.
MySQL 192.168.9.154:3306 ssl JS >
建議根據(jù)提示修改my.cnf文件后，重啟MySQL服務(wù)，然后重新驗證：
dba.configureReplicaSetInstance('rsadmin@192.168.9.159:3306', {clusterAdmin: "'rsadmin'@'192.168.9.%'"});
6：創(chuàng)建副本集kerry_repl
#創(chuàng)建名為kerry_repl的副本集，名字可以根據(jù)實際情況設(shè)置
var rs = dba.createReplicaSet("kerry_repl")
#添加replication信任(MySQL Shell 8.0.28 及之后的版本創(chuàng)建 InnoDB ReplicaSet 時,如果你有安全需要，可以通過下面函數(shù)設(shè)置)
rs.setOption('replicationAllowedHost', '192.168.9.0/24')
#檢查副本集的狀態(tài)
rs.status()
具體執(zhí)行過程如下所示：
MySQL 192.168.9.154:3306 ssl JS > var rs = dba.createReplicaSet("kerry_repl")
A new replicaset with instance 'dbtest04:3306' will be created.
* Checking MySQL instance at dbtest04:3306
This instance reports its own address as dbtest04:3306
dbtest04:3306: Instance configuration is suitable.
* Checking connectivity and SSL configuration...
* Updating metadata...
ReplicaSet object successfully created for dbtest04:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
MySQL 192.168.9.154:3306 ssl JS > rs.setOption('replicationAllowedHost', '192.168.9.0/24')
Internally managed replication users updated for ReplicaSet 'kerry_repl'
MySQL 192.168.9.154:3306 ssl JS > rs.status()
{
"replicaSet": {
"name": "kerry_repl",
"primary": "dbtest04:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"dbtest04:3306": {
"address": "dbtest04:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
MySQL 192.168.9.154:3306 ssl JS >
7：添加實例
rs.addInstance('192.168.9.159:3306')
rs.status()
具體操作如下所示
MySQL 192.168.9.154:3306 ssl JS > rs.addInstance('192.168.9.159:3306')
Adding instance to the replicaset...
* Performing validation checks
This instance reports its own address as dbtest05:3306
dbtest05:3306: Instance configuration is suitable.
* Checking async replication topology...
* Checking connectivity and SSL configuration...
* Checking transaction state of the instance...
NOTE: The target instance 'dbtest05:3306' 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 'dbtest05:3306' 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'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): c
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: dbtest05:3306 is being cloned from dbtest04:3306
** Stage DROP DATA: Completed
** Clone Transfer FILE COPY ============================================================ 0% Not Started PAGE COPY ============================================================ 0% Not Started REDO COPY ============================================================ 0% Not Started** Clone Transfer FILE COPY ============================================================ 0% In Progress PAGE COPY ============================================================ 0% Not Started REDO COPY ============================================================ 0% Not Started** Clone Transfer FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed
NOTE: dbtest05:3306 is shutting down...
* Waiting for server restart... ready
* dbtest05:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.65 MB transferred in about 1 second (~73.65 MB/s)
** Changing replication source of dbtest05:3306 to dbtest04:3306
** Waiting for new instance to synchronize with PRIMARY...
** Transactions replicated ============================================================ 0% ** Transactions replicated ###########################################################= 98% ** Transactions replicated ############################################################ 100%
The instance 'dbtest05:3306' was added to the replicaset and is replicating from dbtest04:3306.
* Waiting for instance 'dbtest05:3306' to synchronize the Metadata updates with the PRIMARY...
** Transactions replicated ============================================================ 0% ** Transactions replicated ##########################################################== 97% ** Transactions replicated ############################################################ 100%
MySQL 192.168.9.154:3306 ssl JS > rs.status()
{
"replicaSet": {
"name": "kerry_repl",
"primary": "dbtest04:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"dbtest04:3306": {
"address": "dbtest04:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"dbtest05:3306": {
"address": "dbtest05:3306",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Waiting for an event from Coordinator",
"applierWorkerThreads": 4,
"receiverStatus": "ON",
"receiverThreadState": "Waiting for source to send event",
"replicationLag": null,
"replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3"
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
MySQL 192.168.9.154:3306 ssl JS >
MySQL Router安裝
bootstrap模式支持failover，在--directory指定的路徑下自動生成安裝目錄，配置文件里的默認端口為6446和6447,這里將MySQL Router安裝在192.168.9.159上，如果資源充足的話，建議將MySQL Router安裝在單獨的一臺服務(wù)器上。
$ cd /data/soft
$ tar xvf mysql-router-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /opt/mysql
$ cd /opt/mysql/
$ ln -s mysql-router-8.0.35-linux-glibc2.28-x86_64/ router
配置環(huán)境變量
在mysql用戶下編輯~/.bash_profile，加入下面配置信息
export PATH=$PATH:/opt/mysql/router/bin
執(zhí)行下面命令使其生效
$ source ~/.bash_profile
#查看幫助信息
mysqlrouter --help
#創(chuàng)建mysqlrouter的數(shù)據(jù)目錄
mkdir -p /data/mysqlrouter
$ mysqlrouter --bootstrap rsadmin@dbtest04:3306 \
--directory /data/mysqlrouter \
--account rs_router \
--conf-bind-address="192.168.9.159" --account-host="192.168.9.%" \
--name='myrouter' --user mysql --force-password-validation
$ mysqlrouter --bootstrap mysqladmin@dbtest04:3306 \
> --directory /data/mysqlrouter \
> --account rs_router \
> --name='myrouter' --user mysql --force-password-validation
Please enter MySQL password for mysqladmin:
# Bootstrapping MySQL Router 8.0.35 (MySQL Community - GPL) instance at '/data/mysqlrouter'...
Please enter MySQL password for rs_router:
- 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 /data/mysqlrouter/mysqlrouter.conf
# MySQL Router 'myrouter' configured for the InnoDB ReplicaSet 'kerry_repl'
After this MySQL Router has been started with the generated configuration
$ mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf
InnoDB ReplicaSet 'kerry_repl' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:6448
- Read/Only Connections: localhost:6449
我們配置一下mysqlrouter的systemctl服務(wù)。使用root賬號創(chuàng)建文件/usr/lib/systemd/system/mysqlrouter.service,然后配置如下所示：
[Unit]
Description=MySQL Router
After=syslog.target
After=network.target
[Service]
Type=simple
User=mysql
Group=mysql
PIDFile=/data/mysqlrouter/mysqlrouter.pid
ExecStart=/opt/mysql/router/bin/mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf
Restart=on-failure
PrivateTmp=true
[Install]
WantedBy=multi-user.target
然后執(zhí)行下面命令
# systemctl daemon-reload
# systemctl enable mysqlrouter.service
# systemctl status mysqlrouter.service
# systemctl start mysqlrouter.service
到這邊MySQL副本集就已經(jīng)搭建成功，然后就是簡單的測試。這里就不作展開介紹了。
總結(jié)
MySQL副本集的安裝是非常簡單的，但是從官方文檔，我們可以看到MySQL副本集的限制與不足也是非常多。它跟MySQL主從復(fù)制的差別不是特別大。其最大的特點就是 使用MySQL Shell搭建非常簡單方便，MySQL Router可以檢測到MySQL副本集的節(jié)點變化。目前來說，生成中使用的價值不是非常大。
參考資料
1: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-replicaset.html
浙公網(wǎng)安備 33010602011771號