MySQL InnoDB Cluster搭建安裝
2024-01-25 16:35 瀟湘隱者 閱讀(2345) 評論(0) 收藏 舉報環境介紹：
這里的MySQL Innodb Cluster搭建環境如下所示,總共有三臺機器搭建MySQL InnoDB Cluster：
操作系統： Red Hat Enterprise Linux release 8.8 (Ootpa)
數據庫版本：8.0.35 MySQL Community Server - GPL
第一步：環境檢查與配置
1：防火墻配置
測試環境可以關閉防火墻，生產環境一般會開啟防火墻。在防火墻上設置相關IP和端口。根據實際情況進行設置（需要root權限，一般由系統管理員設置）
關于如何設置防火墻限定IP地址，這里不做過多介紹，其實配置也很簡單。端口號根據實際情況配置。
# systemctl status firewalld.service
# systemctl stop firewalld.service
# systemctl status firewalld.service
# systemctl disable firewalld.service
2：關閉或配置SELinux
檢查是否關閉selinux
# /usr/sbin/sestatus
SELinux status: disabled
修改SELinux配置文件
如果沒有關閉selinux的話，我們建議關閉selinux，可以通過修改selinux的配置文件，將SELINUX=enforcing改為SELINUX=disabled。
#vi /etc/selinux/config
SELINUX=disabled
如果不想重啟，如果想驗證一下，可以重啟一下服務器。
setenforce 0
getenforce
如果公司要求開啟SELinux的話， 必須設置SELinux 策略，在SELinux 下允許MySQL連接
sudo setsebool -P mysql_connect_any 1
3：配置IP與主機名映射
編輯/etc/hosts
#MySQL InnoDB Cluster
192.168.9.200 mysqlu01
192.168.9.201 mysqlu02
192.168.9.202 mysqlu03
[注意]：三臺MySQL服務器都必須設置，不能只設置一臺服務器。請注意，不要遺漏。
4：修改內核參數
/etc/security/limits.conf
mysql hard nofile 65536
mysql soft nofile 65536
或者
* hard nofile 65536
* soft nofile 65536
5：配置標準大頁
grep -i huge /proc/meminfo
建議開啟標準大頁，這樣更有利于性能。
6：禁用透明大頁
第二步：安裝MySQL實例
1：安裝MySQL實例
使用mysql_auto_install.sh腳本自動安裝MySQL單實例，如果常規的安裝MySQL實例，參考文檔“MySQL 8.0的二進制安裝文檔.docx” ，三臺（多臺）服務器上安裝MySQL實例
$ cd /data/soft
$ sh mysql_auto_install.sh
2：修改參數文件中MGR參數配置
修改server_id=xxxx #規則，取IP地址的最后一位,然后在my.cnf中添加下面配置
##########################################################################################################################
# GTID SETTING
##########################################################################################################################
plugin_dir=/opt/mysql/mysql8.0/lib/plugin #設置plugin的路徑
enforce_gtid_consistency = ON #強制GTID的一致性
gtid-mode=ON #開啟GTID,必須開啟
master-info-repository=TABLE
relay-log-info-repository=TABLE #記錄同步的信息,便于管理和恢復
log-slave-updates = ON #需要記錄事務的binlog,用作以后的恢復用,哪怕不是寫入點,也需要
binlog-checksum=NONE #MGR本身不支持binlog的checksum校驗
slave-parallel-workers=8 #GTID的SQL線程
slave_preserve_commit_order=ON #GTID配置，SQL線程按照順序重放事物
#slave-parallel-type=LOGICAL_CLOCK #SQL線程工作模式。有兩種。
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
##########################################################################################################################
##########################################################################################################################
# 組復制設置
##########################################################################################################################
#記錄事務的算法,官網建議設置該參數使用 XXHASH64 算法
transaction_write_set_extraction = XXHASH64
plugin_load_add='group_replication.so'
#是否隨服務器啟動而自動啟動組復制,不建議直接啟動,怕故障恢復時有擾亂數據準確性的特殊情況
#loose-group_replication_start_on_boot = OFF
group_replication_start_on_boot = OFF
#開啟引導模式,添加組成員，用于第一次搭建MGR或重建MGR的時候使用,只需要在集群內的其中一臺開啟,
#loose-group_replication_bootstrap_group = OFF
group_replication_bootstrap_group = OFF
#IP地址白名單,默認只添加127.0.0.1,不會允許來自外部主機的連接,按需安全設置
#loose-group_replication_ip_whitelist = '127.0.0.1/8,192.168.9.0/24'
group_replication_ip_allowlist = '127.0.0.1/8,192.168.9.0/24'
#是否啟動單主模式，如果啟動，則本實例是主庫，提供讀寫，其他實例僅提供讀,如果為off就是多主模式了
loose-group_replication_single_primary_mode = ON
##ssl for mgr
group_replication_ssl_mode = REQUIRED
group_replication_recovery_use_ssl = ON
binlog_transaction_dependency_tracking=WRITESET
##########################################################################################################################
注意，這里使用mysql shell創建InnoDB Cluster時，不需要在參數文件中設置group_replication_group_name等參數，有些版本的參數也有所出入，請以實際情況為準。
修改后重啟每一臺MySQL服務。
$ sudo systemctl stop mysqld.service
$ sudo systemctl start mysqld.service
$ sudo systemctl status mysqld.service
安裝MySQL Shell
# yum localinstall mysql-shell-8.0.35-1.el8.x86_64.rpm
這里使用root用戶安裝。因為mysql用戶沒有權限安裝。可以只安裝一臺MySQL服務器，也可以三臺都安裝。
創建數據庫用戶
##手動創建需要設置log bin 為0，創建完成后設置回1，所有服務器執行
SET SQL_LOG_BIN=0;
CREATE USER icadmin@'192.168.9.%' IDENTIFIED BY '******';
GRANT ALL ON *.* TO icadmin@'192.168.9.%' WITH GRANT OPTION;
SET SQL_LOG_BIN=1;
注意：所有MySQL節點需要執行創建用戶操作，此用戶臨時使用，配置完集群后刪除。
mysqlsh
\c icadmin@192.168.9.200:7306
或
mysqlsh -h 192.168.9.200 -P 7306 -u icadmin -p
檢查實例是否符合InnoDB Cluster的參數及權限配置要求
dba.checkInstanceConfiguration('icadmin@192.168.9.200:7306')
dba.checkInstanceConfiguration('icadmin@192.168.9.201:7306')
dba.checkInstanceConfiguration('icadmin@192.168.9.202:7306')
檢查符合InnoDB Cluster的參數、權限配置符合要求的話，則會返回status為OK，否則會提示不符合要求信息。
MySQL 192.168.9.200:7306 ssl JS > dba.checkInstanceConfiguration('icadmin@192.168.9.201:7306')
Please provide the password for 'icadmin@192.168.9.201:7306': ****************
Save password for 'icadmin@192.168.9.201:7306'? [Y]es/[N]o/Ne[v]er (default No): yes
Validating MySQL instance at mysqlu02:7306 for use in an InnoDB cluster...
This instance reports its own address as mysqlu02:7306
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.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'mysqlu02:7306' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}
MySQL 192.168.9.200:7306 ssl JS >
如果MySQL的參數不符合要求，則上面輸出信息"status"不為"ok",則必須修改相關參數參數，重新檢測。
初始化InnoDB Cluster相關配置
dba.configureInstance('icadmin@192.168.9.200:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"});
dba.configureInstance('icadmin@192.168.9.201:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"});
dba.configureInstance('icadmin@192.168.9.202:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"});
或者
dba.configureInstance('root@192.168.9.200:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"},clusterAdminPassword:"*****"});
dba.configureInstance('root@192.168.9.201:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"},clusterAdminPassword:"*****"});
dba.configureInstance('root@192.168.9.202:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"},clusterAdminPassword:"*****"});
具體執行過程如下所示：
MySQL 192.168.9.200:7306 ssl JS > dba.configureInstance('icadmin@192.168.9.200:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"});
Configuring local MySQL instance listening at port 7306 for use in an InnoDB cluster...
This instance reports its own address as mysqlu01:7306
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 'icadmin'@'192.168.9.%' already exists and will not be created.
applierWorkerThreads will be set to the default value of 4.
The instance 'mysqlu01:7306' is valid to be used in an InnoDB cluster.
The instance 'mysqlu01:7306' is already ready to be used in an InnoDB cluster.
Successfully enabled parallel appliers.
MySQL 192.168.9.200:7306 ssl JS > dba.configureInstance('icadmin@192.168.9.201:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"});
Configuring MySQL instance at mysqlu02:7306 for use in an InnoDB cluster...
This instance reports its own address as mysqlu02:7306
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 'icadmin'@'192.168.9.%' already exists and will not be created.
applierWorkerThreads will be set to the default value of 4.
The instance 'mysqlu02:7306' is valid to be used in an InnoDB cluster.
The instance 'mysqlu02:7306' is already ready to be used in an InnoDB cluster.
Successfully enabled parallel appliers.
MySQL 192.168.9.200:7306 ssl JS > dba.configureInstance('icadmin@192.168.9.202:7306', {clusterAdmin: "'icadmin'@'192.168.9.%'"});
Configuring MySQL instance at mysqlu03:7306 for use in an InnoDB cluster...
This instance reports its own address as mysqlu03:7306
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 'icadmin'@'192.168.9.%' already exists and will not be created.
applierWorkerThreads will be set to the default value of 4.
The instance 'mysqlu03:7306' is valid to be used in an InnoDB cluster.
The instance 'mysqlu03:7306' is already ready to be used in an InnoDB cluster.
Successfully enabled parallel appliers.
MySQL 192.168.9.200:7306 ssl JS >
創建集群
var cluster = dba.createCluster('gsp_cluster');
具體操作如下所示：
MySQL 192.168.9.200:7306 ssl JS > var cluster = dba.createCluster('gsp_cluster');
A new InnoDB Cluster will be created on instance 'mysqlu01:7306'.
Validating instance configuration at 192.168.9.200:7306...
This instance reports its own address as mysqlu01:7306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysqlu01:7306'. Use the localAddress option to override.
* Checking connectivity and SSL configuration...
Creating InnoDB Cluster 'gsp_cluster' on 'mysqlu01:7306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
MySQL 192.168.9.200:7306 ssl JS >
查看創建集群狀態
var cluster = dba.getCluster()
cluster.status()
具體輸出如下所示：
MySQL 192.168.9.200:7306 ssl JS > var cluster = dba.getCluster()
MySQL 192.168.9.200:7306 ssl JS > cluster.status()
{
"clusterName": "gsp_cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "mysqlu01:7306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"mysqlu01:7306": {
"address": "mysqlu01:7306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.33"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "mysqlu01:7306"
}
MySQL 192.168.9.200:7306 ssl JS >
添加節點到集群
var cluster=dba.getCluster()
cluster.addInstance('icadmin@192.168.9.201:7306')
cluster.status()
cluster.addInstance('icadmin@192.168.9.202:7306')
cluster.status()
部分輸出如下所示：
MySQL 192.168.9.200:7306 ssl JS > cluster.addInstance('icadmin@192.168.9.202:7306')
NOTE: The target instance 'mysqlu03:7306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'mysqlu03:7306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster 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
Validating instance configuration at 192.168.9.202:7306...
This instance reports its own address as mysqlu03:7306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysqlu03:7306'. Use the localAddress option to override.
* Checking connectivity and SSL configuration...
A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: mysqlu03:7306 is being cloned from mysqlu02:7306
** 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: mysqlu03:7306 is shutting down...
* Waiting for server restart... ready
* mysqlu03:7306 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)
State recovery already finished for 'mysqlu03:7306'
The instance 'mysqlu03:7306' was successfully added to the cluster.
MySQL 192.168.9.200:7306 ssl JS > cluster.status()
{
"clusterName": "gsp_cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "mysqlu01:7306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"mysqlu01:7306": {
"address": "mysqlu01:7306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.33"
},
"mysqlu02:7306": {
"address": "mysqlu02:7306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.33"
},
"mysqlu03:7306": {
"address": "mysqlu03:7306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.33"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "mysqlu01:7306"
}
MySQL 192.168.9.200:7306 ssl JS >
檢查集群狀態
最后再檢查一次集群的狀態
var cluster=dba.getCluster()
cluster.status();
MySQL Router安裝
MySQL Router有兩種配置方式，如下所示：
手工配置，手工填寫后端 MGR 節點的地址，但是這樣MySQL Router就沒法感知 Primary 節點的變化，手工創建 MGR 時只能這么配置 引導模式自動進行配置,通過 mysql_innodb_cluster_metadata 元數據庫動態感知 Primary 節點的變化,實現對應用的透明，這也是 InnoDB Cluster 的標準配置方法。
bootstrap模式
bootstrap模式支持failover，但是必須結合InnoDB Cluster使用，在--directory指定的路徑下自動生成安裝目錄，配置文件里的端口為6446和6447
$ 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
配置環境變量
#在mysql用戶下編輯，加入下面配置信息
export PATH=$PATH:/opt/mysql/router/bin
執行下面命令，使之生效。
$ source ~/.bash_profile
#查看幫助信息
mysqlrouter --help
#創建目錄
mkdir -p /data/mysqlrouter
初始化腳本(例子1)
mysqlrouter --bootstrap icadmin@mysqlu01:7306 --directory /data/mysqlrouter --name='icrouter' --force-password-validation
具體如下所示：
$ mysqlrouter --bootstrap icadmin@mysqlu01:7306 --directory /data/mysqlrouter --name='iicrouter' --force-password-validation
Please enter MySQL password for icadmin:
# Bootstrapping MySQL Router 8.0.35 (MySQL Community - GPL) instance at '/data/mysqlrouter'...
- 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 'iicrouter' configured for the ClusterSet 'yicticcset'
After this MySQL Router has been started with the generated configuration
$ mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf
ClusterSet 'yicticcset' 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
初始化腳本(例子2)：
mysqlrouter --bootstrap icadmin@mysqlu01:7306 \
--directory /data/mysqlrouter \
--account iccrouter \
--user mysql --name icrouter \
--conf-bind-address="0.0.0.0" \
--account-host="192.168.9.%" --force-password-validation
注意：--conf-bind-address，如果想任何其它機器都能訪問mysql router的話，那么--conf-bind-address應該設置為0.0.0.0，如果設置為某個IP，表示只能在這個IP訪問mysql router，根據具體情況設置。
具體輸出如下所示：
[mysql@mysqlu01 mysqlrouter]$ mysqlrouter --bootstrap icadmin@mysqlu01:7306 \
> --directory /data/mysqlrouter \
> --account iccrouter \
> --user mysql --name icrouter \
> --conf-bind-address="192.168.9.200" \
> --account-host="192.168.9.%" --force-password-validation
Please enter MySQL password for icadmin:
# Reconfiguring MySQL Router 8.0.35 (MySQL Community - GPL) instance at '/data/mysqlrouter'...
Please enter MySQL password for iccrouter:
- Creating account(s) (only those that are needed, if any)
- Using existing certificates from the '/data/mysqlrouter/data' directory
- 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
Existing configurations backed up to '/data/mysqlrouter/mysqlrouter.conf.bak'
# MySQL Router 'icrouter' configured for the InnoDB Cluster 'gsp_cluster'
After this MySQL Router has been started with the generated configuration
$ mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf
InnoDB Cluster 'gsp_cluster' 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
[mysql@mysqlu01 mysqlrouter]$
查看MySQL Router信息
var cluster =dba.getCluster()
cluster.listRouters()
具體信息如下所示
配置MySQL Router的systemd服務，編輯配置/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
然后執行下面命令
# systemctl daemon-reload
# systemctl enable mysqlrouter.service
# systemctl status mysqlrouter.service
# systemctl start mysqlrouter.service
啟動MySQL Router后，可以通過下面命令查看/驗證其監聽端口是否開啟。
$ netstat -ntlp |grep mysqlrouter
$ ps -ef | grep mysqlrouter | grep -v grep
然后依葫蘆畫瓢，在另外一臺服務器上安裝MySQL Router,關于MySQL Router的安裝，一般應該在應用服務器或單獨的服務器上安裝MySQL Router，結合Keepalvied實現MySQL Router的高可用性，這里不做展開介紹。
掃描上面二維碼關注我
如果你真心覺得文章寫得不錯，而且對你有所幫助，那就不妨幫忙“推薦"一下，您的“推薦”和”打賞“將是我最大的寫作動力！
本文版權歸作者所有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接.
浙公網安備 33010602011771號