MySQL Innodb Cluster搭建
本次测试使用三台机器搭建mysql innodb cluster production mode,官方架构如下图所示：
机器列表：
1、创建数据库实例
首先在三台主机上创建3307数据库实例，数据库版本为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
标红为必须设置的参数，其他GR配置前提：
必须使用innodb存储引擎，主要为了避免事务提交时冲突。
必须定义主键。
节点间网络延迟小。
使用IPV4协议。
每个节点上启动MySQL数据库实例，注意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创建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自动检查当前实例配置是否满足创建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用户只限定本地登陆，需要重新创建用户
Account Name: cluster@10.191.143.%
继续检测
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
发现参数需要调整，自动调整。
创建用户后重新使用新用户验证后创建集群
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节点创建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节点的mysqlshell中加入2节点
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)
结果报错：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.'
因为新创建实例后我们修改了root用户密码，并创建了新的用户导致binlog日志gtid变化，启用GR时候报错。登陆2节点数据库，执行reset master将日志清除后重新执行
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节点上重新加入集群实例：
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节点上执行一次。
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创建成功。
测试集群，在10.191.143.24上创建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上检查数据成功复制，但只能读不能写：
