MySQL Innodb Cluster搭建
本次測試使用三臺機器搭建mysql innodb cluster production mode,官方架構(gòu)如下圖所示：
機器列表：
1雕擂、創(chuàng)建數(shù)據(jù)庫實例
首先在三臺主機上創(chuàng)建3307數(shù)據(jù)庫實例肉瓦，數(shù)據(jù)庫版本為mysql-8.0.13套耕，配置文件如下：
[mysqld]
server-id = 1
port = 3307
socket = /data/mysql8/3306/mysql.sock
basedir = /data/mysql8/mysql8
datadir = /data/mysql8/3306/data
pid-file = /data/mysql8/3306/mysql.pid
log-error = /data/mysql8/3306/mysql-error.log
plugin-dir = /data/mysql8/mysql8/lib/plugin
#gernal setting
lower_case_table_names = 1
max_binlog_size = 1G
sync_binlog=1
innodb_flush_log_at_trx_commit = 1
#semi-sync
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000
#rpl_semi_sync_slave_enabled=1
#innodb
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances =8
#MGR
log-bin = /data/mysql8/3306/bin/mysql-bin
relay-log = /data/mysql8/3306/bin/relay-log
log-slave-updates
binlog-format=row
gtid-mode=ON
enforce-gtid-consistency=true
master-info-repository=table
relay-log-info-repository=table
transaction-write-set-extraction=XXHASH64
標(biāo)紅為必須設(shè)置的參數(shù)，其他GR配置前提：
必須使用innodb存儲引擎，主要為了避免事務(wù)提交時沖突。
必須定義主鍵。
節(jié)點間網(wǎng)絡(luò)延遲小毅访。
使用IPV4協(xié)議。
每個節(jié)點上啟動MySQL數(shù)據(jù)庫實例盘榨，注意serverid配置：
./bin/mysqld --defaults-file=/data/mysql8/3306/3306.cfg --initialize-insecure
./bin/mysqld_safe --defaults-file=/data/mysql8/3306/3306.cfg --user=mysql &
mysql -uroot -p -S /data/mysql8/3306/mysql.sock
mysql> alter user root@’localhost’?identified by ‘xxxxxxx’;
mysql>flush privileges;
2俺抽、安裝mysql shell
需要使用python2.7及以上。
1)安裝python2.7
./configure
make && make install
2)解壓mysql shell
tar zxvf ?mysql-shell-8.0.13-linux-glibc2.12-x86-64bit.tar.gz
3)安裝router
Router安裝包被封裝在mysql8.0.13壓縮包中较曼，直接解壓使用即可磷斧。
3、部署innodb cluster
首先在10.191.143.24創(chuàng)建cluster
> mysqlsh --log-level=DEBUG3
JS> \connect mysql://root@localhost:3307?socket=(/data/mysql8/3306/mysql.sock)
JS> dba.verbose=2
JS > dba.configureInstance()
Mysqlsh自動檢查當(dāng)前實例配置是否滿足創(chuàng)建cluster
Configuring local MySQL instance listening at port 3307 for use in an InnoDB cluster...
This instance reports its own address as OaasSvr
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.
WARNING: User 'root' can only connect from localhost.
If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created.
1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel
Please select an option [1]: 2
檢測到root用戶只限定本地登陸，需要重新創(chuàng)建用戶
Account Name: cluster@10.191.143.%
繼續(xù)檢測
Some configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable ???????| Current Value | Required Value | Note ??????????????????????|
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32 ????????| NONE ??????????| Update the server variable |
+-----------------+---------------+----------------+----------------------------+
Do you want to perform the required configuration changes? [y/n]: y
發(fā)現(xiàn)參數(shù)需要調(diào)整弛饭，自動調(diào)整冕末。
創(chuàng)建用戶后重新使用新用戶驗證后創(chuàng)建集群
JS > \connect mysql://cluster@10.191.143.24:3307
Please provide the password for 'cluster@10.191.143.24:3307': *********
Save password for 'cluster@10.191.143.24:3307'? [Y]es/[N]o/Ne[v]er (default No): YY
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 22
Server version: 8.0.13 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
JS > dba.configureInstance()dba.configureInstance()
Configuring local MySQL instance listening at port 3307 for use in an InnoDB cluster...
This instance reports its own address as OaasSvr
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.
The instance '10.191.143.24:3307' is valid for InnoDB cluster usage.
JS > var cluster = dba.createCluster('testCluster')
Validating instance at 10.191.143.24:3307...
This instance reports its own address as OaasSvr
Instance configuration is suitable.
Creating InnoDB cluster 'testCluster' on 'cluster@10.191.143.24:3307'...
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 ?10.191.143.24:3307 ssl ?JS > cluster.status()cluster.status()
{
????"clusterName": "testCluster",
????"defaultReplicaSet": {
????????"name": "default",
????????"primary": "10.191.143.24:3307",
????????"ssl": "REQUIRED",
????????"status": "OK_NO_TOLERANCE",
????????"statusText": "Cluster is NOT tolerant to any failures.",
????????"topology": {
????????????"10.191.143.24:3307": {
????????????????"address": "10.191.143.24:3307",
????????????????"mode": "R/W",
????????????????"readReplicas": {},
????????????????"role": "HA",
????????????????"status": "ONLINE"
????????????}
????????}
????},
????"groupInformationSourceMember": "mysql://cluster@10.191.143.24:3307"
}
登陸2節(jié)點創(chuàng)建cluster用戶
JS > \connect mysql://root@localhost:3307?socket=(/data/mysql8/3306/mysql.sock)
JS >dba.configureInstance('root@localhost:3307',{clusterAdmin:"'cluster'@'10.191.143.%'",clusterAdminPassword:'XXXXX'});
Configuring local MySQL instance listening at port 3307 for use in an InnoDB cluster...
This instance reports its own address as AaasSvr
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.
Some configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable ???????| Current Value | Required Value | Note ??????????????????????|
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32 ????????| NONE ??????????| Update the server variable |
+-----------------+---------------+----------------+----------------------------+
Do you want to perform the required configuration changes? [y/n]: YY
Cluster admin user 'cluster'@'10.191.143.%' created.
Configuring instance...
The instance 'localhost:3307' was configured for use in an InnoDB cluster.
然后在1節(jié)點的mysqlshell中加入2節(jié)點
?MySQL ?10.191.143.24:3307 ssl ?JS > cluster.addInstance('cluster@10.191.143.25:3307')cluster.addInstance('cluster@10.191.143.25:3307')
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 ...
Validating instance at 10.191.143.25:3307...
This instance reports its own address as AaasSvr
Instance configuration is suitable.
Cluster.addInstance: WARNING: Not running locally on the server and can not access its error log.
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: '10.191.143.25:3307' - Query failed. MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication: MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log. (RuntimeError)
結(jié)果報錯：Group Replication join failed.
在10.191.143.25上查看錯誤日志
2018-11-06T06:17:35.694946Z 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 9f50c222-e0e3-11e8-a687-0050569719f1:1-2 > Group transactions: 638b7777-e174-11e8-90f6-0050569770fd:1-6,
8a673b74-e0dd-11e8-818c-0050569770fd:1-21'
2018-11-06T06:17:35.695067Z 0 [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
因為新創(chuàng)建實例后我們修改了root用戶密碼，并創(chuàng)建了新的用戶導(dǎo)致binlog日志gtid變化侣颂，啟用GR時候報錯档桃。登陸2節(jié)點數(shù)據(jù)庫，執(zhí)行reset master將日志清除后重新執(zhí)行
mysql> reset master;
Query OK, 0 rows affected (0.07 sec)
mysql> show master status\G
*************************** 1. row ***************************
?????????????File: mysql-bin.000001
?????????Position: 151
?????Binlog_Do_DB:
?Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
在1節(jié)點上重新加入集群實例：
?MySQL ?10.191.143.24:3307 ssl ?JS > ?cluster.addInstance('cluster@10.191.143.25:3307') cluster.addInstance('cluster@10.191.143.25:3307')
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 ...
Validating instance at 10.191.143.25:3307...
This instance reports its own address as AaasSvr
Instance configuration is suitable.
The instance 'cluster@10.191.143.25:3307' was successfully added to the cluster.
成功憔晒，同樣在3節(jié)點上執(zhí)行一次藻肄。
?MySQL ?10.191.143.24:3307 ssl ?JS > cluster.status()cluster.status()
{
????"clusterName": "testCluster",
????"defaultReplicaSet": {
????????"name": "default",
????????"primary": "10.191.143.24:3307",
????????"ssl": "REQUIRED",
????????"status": "OK",
????????"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
????????"topology": {
????????????"10.191.143.24:3307": {
????????????????"address": "10.191.143.24:3307",
????????????????"mode": "R/W",
????????????????"readReplicas": {},
????????????????"role": "HA",
????????????????"status": "ONLINE"
????????????},
????????????"10.191.143.25:3307": {
????????????????"address": "10.191.143.25:3307",
????????????????"mode": "R/O",
????????????????"readReplicas": {},
????????????????"role": "HA",
????????????????"status": "ONLINE"
????????????},
????????????"10.191.143.26:3307": {
????????????????"address": "10.191.143.26:3307",
????????????????"mode": "R/O",
????????????????"readReplicas": {},
????????????????"role": "HA",
????????????????"status": "ONLINE"
????????????}
????????}
????},
????"groupInformationSourceMember": "mysql://cluster@10.191.143.24:3307"
}
最終，innodb cluster創(chuàng)建成功拒担。
測試集群嘹屯，在10.191.143.24上創(chuàng)建test庫并新建test表：
10.191.143.24:
create database test;
use test;
mysql> create table test(id int(11),primary key (id) );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values (1);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test values(2),(3);
Query OK, 2 rows affected (0.03 sec)
Records: 2 ?Duplicates: 0 ?Warnings: 0
mysql>
10.191.143.25上檢查數(shù)據(jù)成功復(fù)制，但只能讀不能寫：
