MySQL Server 8.0 InnoDB Cluster 安裝

MySQL InnoDB Cluster 是最簡易設定的 MySQL 資料庫叢集, 至少要三個節點, 搭配 MySQL Router 來介接應用程式, 達到高可靠性與讀寫分離功能, 本次利用 Percona Server for MySQL 8.0 來實作 InnoDB Cluster, 顧名思義必須是 InnoDB 資料庫引擎來做叢集, 而現今 InnoDB 也仍是效能極佳的資料庫引擎。

本次使用的測試環境為三台 Debian Linux 10 虛擬機, 分別命名 hostname 為 database1, database2, database3
IP位址的配發:
192.168.1.211 database1
192.168.1.212 database2
192.168.1.213 database3

以root身份編輯 /etc/hosts, 在這三台機器名稱IP對應加入
nano /etc/hosts

貼入並儲存
192.168.1.211 database1
192.168.1.212 database2
192.168.1.213 database3


分別在這三台機器都安裝 Percona Server for MySQL 8.0:
安裝 Percona APT repository
apt-get install gnupg2
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb


設定使用 Percona Server for MySQL 8.0
percona-release setup ps80


安裝 Percona Server for MySQL 8.0 與 Percona MySQL Shell 套件
apt-get install percona-server-server percona-mysql-shell


修改 MySQL Server 設定檔
nano /etc/mysql/mysql.conf.d/mysqld.cnf

貼入並儲存
bind-address = 192.168.1.211
log_timestamps = SYSTEM
server-id = 1

給 database1 使用, 其他則給予 server-id = 2, server-id = 3 識別, 當然 bind-address 也依照各機IP位址設定

重新啟動
service mysql restart


同樣在每台機器設定 root@% 帳號的遠端登入權限
mysql -u root -p

進入MySQL command line, 並使用以下SQL語法增加 root@% 帳號
create user 'root'@'%' identified with mysql_native_password by 'mYpa33w0rd';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;


接著就是改用 MySQL Shell 來設定 InnoDB Cluster, 首先在 database1 機器上
mysqlsh --uri root@database1:3306

會顯示這樣的訊息, MySQL Shell 可支援 SQL, Python 與 Javascript
MySQL Shell 8.0.19

Copyright (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 a session to 'root@database1:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 14
Server version: 8.0.19-10 Percona Server (GPL), Release '10', Revision 'f446c04'
No default schema selected; type \use <schema> to set one.


讓此 MySQL Server 可以建立 InnoDB Cluster
dba.configureLocalInstance()

輸出訊息範例
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as database1: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.

NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum | CRC32 | NONE | 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 'database1:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at database1:3306 was restarted.

過程會兩次詢問設定, 都按"y"使用預設值
當然其他機器也要做, 接著先退出 MySQL Shell
\quit

重啟 MySQL Server
service mysql restart


回到 MySQL Shell
mysqlsh --uri root@database1:3306

檢查設定
dba.checkInstanceConfiguration('root@database1:3306')

輸出訊息範例
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as database1: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 'database1:3306' is valid to be used in an InnoDB cluster.

{
"status": "ok"
}

每台機器都完成以上步驟, hostname "database1" 依樣畫葫蘆變更即可!

回到 database1 MySQL Shell, 來建立InnoDB Cluster
var cluster = dba.createCluster('MyInnodbCluster');

輸出訊息範例
A new InnoDB cluster will be created on instance 'database1:3306'.

Validating instance configuration at database1:3306...

This instance reports its own address as database1:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'database1:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'MyInnodbCluster' on 'database1: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.


加入 database2 到 InnoDB Cluster
cluster.addInstance('root@database2:3306');

輸出訊息範例
NOTE: The target instance 'database2: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 'database2: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): c
NOTE: Group Replication will communicate with other members using 'database2:33061'. Use the localAddress option to override.

Validating instance configuration at database2:3306...

This instance reports its own address as database2:3306

Instance configuration is suitable.
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: database2:3306 is being cloned from database1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed

NOTE: database2:3306 is shutting down...

* Waiting for server restart... ready
* database2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 59.62 MB transferred in about 1 second (~1.00 B/s)

State recovery already finished for 'database2:3306'

The instance 'database2:3306' was successfully added to the cluster.

由於新建的 MySQL Server 沒有打開GTID相關設定, 故有警告
過程會詢問recovery method, 按下預設值的"c", 即"Clone"方法, 同樣也加入 database3
cluster.addInstance('root@database3:3306');


檢視 InnoDB Cluster 狀態
cluster.status()

輸出訊息範例
{
"clusterName": "MyInnodbCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "database1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"database1:3306": {
"address": "database1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"database2:3306": {
"address": "database2:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"database3:3306": {
"address": "database3:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "database1:3306"
}

可以看到叢集已經建立好了, database1 能讀寫, 其他則是唯讀

退出 MySQL Shell
\quit


來測試一下叢集, 簡單建個資料庫好了
mysql -u root -p

建立一個測試資料庫
create database testdb;

使用 "testdb"
use testdb;

建個資料表
CREATE TABLE guestlist (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)\G

在 database2 與 database3 機器上都可以看到新的資料庫與資料表也跟著建立了

到 database2 上驗證看看吧
mysql -u root -p

進入 MySQL command line
use testdb;

使用 "testdb", 檢視 "guestlist" 資料表結構
describe guestlist;

輸出訊息
+-----------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+-----------------------------------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| firstname | varchar(30) | NO | | NULL | |
| lastname | varchar(30) | NO | | NULL | |
| email | varchar(50) | YES | | NULL | |
| reg_date | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-----------+--------------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.01 sec)


嘗試把 database1 虛擬機關閉, 然後再開機
poweroff


在別的 Mysql Shell 看到 Primary Server 已經跑到 database2 身上
{
"clusterName": "MyInnodbCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "database2:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"database1:3306": {
"address": "database1:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"database2:3306": {
"address": "database2:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"database3:3306": {
"address": "database3:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "database2:3306"
}


把 database1 改回 Primary Server
cluster.setPrimaryInstance('database1:3306')

在三節點架構下, 只能允許一台離線

重新檢視叢集狀態
var cluster = dba.getCluster('MyInnodbCluster')
cluster.status()


可以看到 database1 回到 R/W mode
{
"clusterName": "MyInnodbCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "database1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"database1:3306": {
"address": "database1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"database2:3306": {
"address": "database2:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
},
"database3:3306": {
"address": "database3:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.19"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "database2:3306"
}


與應用程式介接, 目前我嘗試使用 ProxySQL 感覺不適合, 恐怕還是得要用 MySQL Router 吧!
God's in his heaven. All's right with the world.
2020-05-08 14:47 發佈
Ryan Lai wrote:
MySQL Inno...(恕刪)


專業!!
雖然看不太懂 但還是很支持你 MySQL Server 8.0 InnoDB Cluster 安裝
評分
複製連結