1.架構確認
innodb cluster 基本架構如下
innodb-node (Primary) : innodb 架構主節點，通常也會是 R/W 節點
innodb-node (Secondary) : innodb 的抄寫節點，通常可以做為 R/O 的節點
innodb-node (Secondary) : innodb 的抄寫節點，通常可以做為 R/O 的節點
2.建立主機
192.168.1.61 innodb-node1
192.168.1.62 innodb-node2
192.168.1.63 innodb-node3
3.環境準備
3.1. MySQL
sudo apt install mysql-server3.2. 設定 my.con
sudo vi /etc/mysql/my.cnfmy.cnf內容
[mysqld]
#當前 Node 的 ID 不得重複
server-id=61
gtid_mode=on
enforce_gtid_consistency=on
relay_log_info_repository=table
master_info_repository=table
log-bin=mysqlbin
log-slave-updates=1
binlog_format=row
binlog_checksum=NONE
binlog_transaction_dependency_tracking=WRITESET
#前墜加上 loose 的用途在於如果plung 之前不會載入
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
#需修改為當前 Node 的 IP
loose-group_replication_local_address="192.168.1.61:33061"
loose-group_replication_group_seeds="192.168.1.61:33061,192.168.1.62:33061,192.168.1.63:33061"
loose-group_replication_bootstrap_group=off
#白名單設置
loose-group_replication_ip_allowlist="192.168.1.61,192.168.1.62,192.168.1.63"重啟 MySQL
sudo systemctl restart mysql.service3.3. 安裝 MySQL shell
匯入官方的 mysql apt repository
https://dev.mysql.com/downloads/repo/apt/
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb && \
sudo dpkg -i ./mysql-apt-config_0.8.22-1_all.deb && \
sudo apt update && \
sudo apt install -y mysql-shell然後再重複上面的動作把 Secondary node 建立起來
4.設定 cluster
開啟 mysqlsh
mysqlsh連線資料庫主機
shell.connect('root@innodb-node1')
### 完成連線成功會出現下面的訊息
Creating a session to 'root@innodb-node1'
Please provide the password for 'root@innodb-node1': ********
Save password for 'brs@innodb-node1'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 8 (X protocol)
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)
No default schema selected; type \use <schema> to set one.
<Session:root@innodb-node1:33060>
MySQL innodb-node1:33060+ ssl JS >確認目前設定是否符合
dba.checkInstanceConfiguration('root@innodb-node1:3306')
### 如果沒問題就會出現 "ok"，但有時候會有一些設置的建議
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as innodb-node1: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.
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 'innodb-node1:3306' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}建立一個新的 cluster group
var cluster=dba.createCluster('innodbcluster')
### 建立完成，同時裡面有提到一個 cluster 原則上至少要有三個 Node
A new InnoDB cluster will be created on instance 'innodb-node1:3306'.
Validating instance configuration at innodb-node1:3306...
This instance reports its own address as innodb-node1:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'innodb-node1:33061'. Use the localAddress option to override.
Creating InnoDB cluster 'innodbcluster' on 'innodb-node1:3306'...
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.加入其他的 Node
cluster.addInstance('root@innodb-node2:3306')
### 執行後開進入設定
NOTE: The target instance 'innodb-node2:3306' 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 'innodb-node2:3306' 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):
Validating instance configuration at innodb-node2:3306...
This instance reports its own address as innodb-node2:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'innodb-node2:33061'. Use the localAddress option to override.
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: innodb-node2:3306 is being cloned from innodb-node1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: innodb-node2:3306 is shutting down...
* Waiting for server restart... ready
* innodb-node2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.66 MB transferred in about 1 second (~73.66 MB/s)
State recovery already finished for 'innodb-node2:3306'
The instance 'innodb-node2:3306' was successfully added to the cluster.確認 cluster 狀態
cluster.status()
### 確認當前狀態是否正確
{
"clusterName": "innodbcluster",
"defaultReplicaSet": {
"name": "default",
"primary": "innodb-node1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"innodb-node1:3306": {
"address": "innodb-node1:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
},
"innodb-node2:3306": {
"address": "innodb-node2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
},
"innodb-node3:3306": {
"address": "innodb-node3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "innodb-node1:3306"
}5.補充
MySQL connect 一般來說建議是建立一個專用的，不要使用 root
如果是建立專用的有部分權限需要加進去
CREATE USER 'rep'@'%' IDENTIFIED BY '12345678';
GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'rep'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'rep'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'rep'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'rep'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'rep'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;