本文介绍mysql 8版本下的Innodb Cluster配置与测试过程，其核心还是mysql的组复制功能，通过使用mysql shell简化了组复制的配置过程，同时使用mysql route中间件实现了故障的自动转移以及读写分离等功能。之前测试mysql组复制的时候有提出过中间件的问题，mysql-route是个不错的解决方案，前文传送门：https://blog.51cto.com/ylw6006/1976829

一、环境介绍

操作系统：centos linux 7.2 64bit
mysql社区版: 8.0.11
mysql shell版本：8.0.11
mysql route版本：8.0.11

二、下载软件包

1、mysql
https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

2、mysql-router
https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-8.0.11-linux-glibc2.12-x86-64bit.tar.gz

3、mysql-shell
https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.11-linux-glibc2.12-x86-64bit.tar.gz

三、安装软件包

（其他两台主机同步配置）

1、配置hosts文件# cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.115.5 vm1192.168.115.6 vm2192.168.115.7 vm32、安装mysql-shell# tar -zxvpf mysql-shell-8.0.11-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/# cd /usr/local/# ln -s mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/ mysql-shell3、安装mysql-route# tar -zxvpf mysql-router-8.0.11-linux-glibc2.12-x86-64bit.tar.gz  -C /usr/local/# cd /usr/local/# ln -s mysql-router-8.0.11-linux-glibc2.12-x86-64bit/ mysql-router4、安装和初始化mysql# tar -zxvpf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz -C /usr/local/# cd /usr/local/# ln -s mysql-8.0.11-linux-glibc2.12-x86_64/ mysql# groupadd -g 27 mysql # useradd -g mysql -u 27 -d /dev/null -s /sbin/nologin mysql# mkdir -p /mydata# chown -R mysql.mysql /mydata/# cat /usr/local/mysql/my.cnf[client]password = 123456[mysqld]innodb_buffer_pool_size = 256Msocket          = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 100Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 16Mthread_cache_size = 4max_connections=1500character_set_server=utf8group_concat_max_len=65535log_bin_trust_function_creators=1log_queries_not_using_indexes = ONlog_throttle_queries_not_using_indexes = 2log-bin=mysql-binbinlog_format=rowdefault_authentication_plugin = mysql_native_passwordlong_query_time=2slow_query_log=1slow_query_log_file=/mydata/slow-query.logft_min_word_len=1innodb_ft_min_token_size=1server-id=1155lower_case_table_names = 1skip-name-resolveinnodb_file_per_table=1gtid_mode = ONenforce_gtid_consistency = ONslave_parallel_workers=4master_verify_checksum = 1slave_sql_verify_checksum = 1log-slave-updates=truesql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESslave-parallel-type=LOGICAL_CLOCKslave-preserve-commit-order=ONmaster_info_repository = TABLErelay_log_info_repository = TABLE# /usr/local/mysql/bin/mysqld  --defaults-file=/usr/local/mysql/my.cnf --initialize  --user=mysql --basedir=/usr/local/mysql/ --datadir=/mydata --initialize-insecure# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld# egrep 'basedir|datadir' /etc/init.d/mysqld |head -n 5#   basedir=<path-to-mysql-installation-directory># - Add the path to the mysql-installation-directory to the basedir variable# If you change base dir, you must also change datadir. These may getbasedir=/usr/local/mysqldatadir=/mydata# chmod +x /etc/init.d/mysqld # chkconfig --add mysqld# mv /etc/my.cnf /etc/my.cnf.bak# ln -s /usr/local/mysql/my.cnf  /etc/# service mysqld startmysql> set sql_log_bin=0;     mysql> update mysql.user set host='%' where user='root';mysql> alter user  'root'@'localhost' identified with mysql_native_password by '123456';mysql> create user test@'%' identified by '123456';mysql> grant all privileges on *.* to test@'%';mysql> flush privileges;mysql> set sql_log_bin=1;

四、检查配置

vm1执行即可

使用JS命令，检查每台服务器的配置。三台服务的mysql都要执行检查# /usr/local/mysql-shell/bin/mysqlsh  MySQL  JS > dba.checkInstanceConfiguration('test@vm1:3306')Please provide the password for 'test@vm1:3306': ******Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...This instance reports its own address as vm1Clients 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 detectedChecking instance configuration...Instance configuration is compatible with InnoDB clusterThe instance 'vm1:3306' is valid for InnoDB cluster usage.{    "status": "ok"}执行 dba.checkInstanceConfiguration，当输出 "status": "ok"的时候，表示该服务器检查通过

五、创建组复制

vm1执行即可

 # /usr/local/mysql-shell/bin/mysqlsh --uri test@vm1:3306  MySQL  vm1:3306 ssl  JS > var cluster = dba.createCluster('main')A new InnoDB cluster will be created on instance 'root@vm1:3306'.Validating instance at vm1:3306...This instance reports its own address as vm1Instance configuration is suitable.Creating InnoDB cluster 'main' on 'root@vm1: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 toone server failure.JS > cluster.addInstance('root@vm2:3306') JS > cluster.addInstance('root@vm3:3306')  MySQL  vm1:3306 ssl  JS > cluster.status(){    "clusterName": "main",     "defaultReplicaSet": {        "name": "default",         "primary": "vm1:3306",         "ssl": "REQUIRED",         "status": "OK",         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",         "topology": {            "vm1:3306": {                "address": "vm1:3306",                 "mode": "R/W",                 "readReplicas": {},                 "role": "HA",                 "status": "ONLINE"            },             "vm2:3306": {                "address": "vm2:3306",                 "mode": "R/O",                 "readReplicas": {},                 "role": "HA",                 "status": "ONLINE"            },             "vm3:3306": {                "address": "vm3:3306",                 "mode": "R/O",                 "readReplicas": {},                 "role": "HA",                 "status": "ONLINE"            }        }    },     "groupInformationSourceMember": "mysql://root@vm1:3306"}

六、配置mysql-route

vm1执行即可

# useradd  mysql-router# /usr/local/mysql-router/bin/mysqlrouter --bootstrap root@vm3:3306 --user mysql-router   --directory /data/mysqlrouter --user=root --conf-use-sockets --force  Please enter MySQL password for root: Reconfiguring MySQL Router instance at '/data/mysqlrouter'...MySQL Router  has now been configured for the InnoDB cluster 'main'.The following connection information can be used to connect to the cluster.Classic MySQL protocol connections to cluster 'main':- Read/Write Connections: localhost:6446- Read/Write Connections: /data/mysqlrouter/mysql.sock- Read/Only Connections: localhost:6447- Read/Only Connections: /data/mysqlrouter/mysqlro.sockX protocol connections to cluster 'main':- Read/Write Connections: localhost:64460- Read/Write Connections: /data/mysqlrouter/mysqlx.sock- Read/Only Connections: localhost:64470- Read/Only Connections: /data/mysqlrouter/mysqlxro.sock# /data/mysqlrouter/start.sh # netstat -anpt |grep routertcp        0      0 0.0.0.0:64460           0.0.0.0:*               LISTEN      4865/mysqlrouter    tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      4865/mysqlrouter    tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      4865/mysqlrouter    tcp        0      0 0.0.0.0:64470           0.0.0.0:*               LISTEN      4865/mysqlrouter    tcp        0      0 192.168.115.5:6446      192.168.115.1:63131     ESTABLISHED 4865/mysqlrouter    tcp        0      0 192.168.115.5:36458     192.168.115.5:3306      ESTABLISHED 4865/mysqlrouter    tcp        0      0 192.168.115.5:46488     192.168.115.7:3306      ESTABLISHED 4865/mysqlrouter 

七、连接测试

[root@vm1 ~]# /usr/local/mysql/bin/mysql -u root -h 192.168.115.5 -P 6446 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 87Server version: 8.0.11 MySQL Community Server - GPLCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> SELECT @@port;+--------+| @@port |+--------+|   3306 |+--------+1 row in set (0.00 sec)mysql> select user,host from mysql.user;+----------------------------------+-----------+| user                             | host      |+----------------------------------+-----------+| mysql_innodb_cluster_r0467305354 | %         || mysql_innodb_cluster_r0467308990 | %         || mysql_innodb_cluster_r0467310325 | %         || mysql_router1_2x0gxmyjbatp       | %         || mysql_router1_x017h4ui76aq       | %         || root                             | %         || test                             | %         || mysql.infoschema                 | localhost || mysql.session                    | localhost || mysql.sys                        | localhost || mysql_innodb_cluster_r0467286081 | localhost || mysql_innodb_cluster_r0467305354 | localhost || mysql_innodb_cluster_r0467308990 | localhost || mysql_innodb_cluster_r0467310325 | localhost |+----------------------------------+-----------+14 rows in set (0.00 sec)[root@vm1 ~]#  /usr/local/mysql/bin/mysql -u root -h 192.168.115.5 -P 6447 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 233Server version: 8.0.11 MySQL Community Server - GPLCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> create database yang;ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statementmysql> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+| group_replication_applier | 7c8712b3-8b2c-11e8-b41f-000c2994965c | vm1         |        3306 | ONLINE       | SECONDARY   | 8.0.11         || group_replication_applier | 862443de-8b2c-11e8-bf4c-000c29c9b5f9 | vm3         |        3306 | ONLINE       | PRIMARY     | 8.0.11         || group_replication_applier | 8852b2af-8b2c-11e8-8487-000c2971a45f | vm2         |        3306 | ONLINE       | SECONDARY   | 8.0.11         |+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+3 rows in set (0.37 sec)