MySql Innodb Cluster集群的构建

1
MySQL InnoDB cluster provides a complete high availability solution for MySQL. MySQL Shell includes AdminAPI which enables you to easily configure and administer a group of at least three MySQL server instances to function as an InnoDB cluster. Each MySQL server instance runs MySQL Group Replication, which provides the mechanism to replicate data within InnoDB clusters, with built-in failover.

1
2
3
4
5
6
7
8
9
10
11
cd /usr/local/
tar -xzvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.21-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql mysql

cd /usr/local/mysql/
mkdir log
chown -R mysql:mysql log
chown -R mysql:mysql /data/mysql_data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[mysqld]
datadir=/data/mysql_data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/log/mysqld.log
symbolic-links=0

log_slave_updates = ON
server_id = 100
relay_log_info_repository = TABLE
master_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
binlog_format = ROW
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port = 3306
binlog_checksum = NONE
enforce_gtid_consistency = ON
log_bin
gtid_mode = ON
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_connections=1000

1
2
3
4
5
6
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data

cp support-files/mysql.server /etc/init.d/mysql
chkconfig --add mysql
chkconfig --list mysql
/etc/init.d/mysql start

1
2
3
export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH
source /etc/profile 使环境变量生效

1
2
3
4
5
6
mysql> set password='123456';
mysql> flush privileges;
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
mysql> update mysql.user set authentication_string=PASSWORD('123456') where User='root';
mysql> flush privileges;
mysql> exit;

1
2
rpm -ivh mysql-shell-1.0.11-1.el7.x86_64.rpm
rpm -ivh mysql-router-2.1.6-1.el7.x86_64.rpm

1
2
3
4
5
6
7
8
9
10
//检查节点是否可用
[root@host-192-168-1-100~]# mysqlsh
mysql-js> dba.checkInstanceConfiguration("root@host-192-168-1-100:3306")
Please provide the password for 'root@host-192-168-1-100:3306':
Validating instance...

The instance 'host-192-168-1-100:3306' is valid for Cluster usage
{
    "status": "ok"
}

1
2
3
4
5
[root@host-192-168-1-100~]# mysqlsh
mysql-js> shell.connect('root@host-192-168-1-100:3306')
mysql-js> var cluster = dba.createCluster('myCluster')
mysql-js> cluster.addInstance('root@host-192-168-1-101:3306')
mysql-js> cluster.addInstance('root@host-192-168-1-102:3306')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql-js> dba.help('getCluster')

Retrieves a cluster from the Metadata Store.

SYNTAX
  <Dba>.getCluster([name])

WHERE
  name: Parameter to specify the name of the cluster to be returned.

DESCRIPTION

If name is not specified, the default cluster will be returned.

If name is specified, and no cluster with the indicated name is found, an error
will be raised.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@host-192-168-1-100~]# mysqlrouter --bootstrap root@host-192-168-1-100:3306 --user=mysqlrouter
Please enter MySQL password for root:
WARNING: The MySQL server does not have SSL configured and metadata used by the router may be transmitted unencrypted.

Bootstrapping system MySQL Router instance...
MySQL Router  has now been configured for the InnoDB cluster 'myCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'myCluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'myCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

Existing configurations backed up to /etc/mysqlrouter/mysqlrouter.conf.bak

//若是目前已经存在集群，想重新替换该集群，比如名称修改等，则使用下面的命令
mysqlrouter --bootstrap root@host-192-168-1-100:3306 --user=mysqlrouter --force

1
systemctl start mysqlrouter.service

1
2
3
4
5
6
7
8
9
10
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: 'host-192-168-1-101:3306' - Query failed. MySQL Error (3092): The server is not configureroperly to be an active member of the group. Please see more details on error log.. Query: START group_replication

通过如下方式进行处理

mysql> install plugin group_replication soname 'group_replication.so'; ##安装插件
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> START GROUP_REPLICATION;
mysql> select * from performance_schema.replication_group_members;

1
2
3
4
Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)

说明集群中的主节点已经不在该机器上，查询后更改机器重试一下即可；

1
2
3
4
5
6
7
8
Dba.getCluster: Dba.getCluster: Unable to get cluster. The instance 'host-192-168-1-101:3306' 
may belong to a different ReplicaSet as the one registered in the Metadata since the value of 'group_replication_group_name' 
does not match the one registered in the ReplicaSet's Metadata: possible split-brain scenario. Please connect to another member of the ReplicaSet to get the Cluster. (RuntimeError)

//最致命的错误，master/slave的数据不一致所致，没办法，只能重新来
dba.dropMetadataSchema();