Mysql Innodb Cluster test

This article describes the InnoDB cluster configuration and testing process under MySQL version 8, the core of which is the MySQL group replication feature, which simplifies the configuration of group replication by using MySQL shell while using MySQL The route middleware realizes the functions of automatic fault transfer and read/write separation. Before testing the MySQL group replication has raised the problem of middleware, Mysql-route is a good solution, the front Portal: http://blog.51cto.com/ylw6006/1976829

I. Introduction to the Environment

Operating system: CentOS Linux 7.2 64bit
MySQL Community Edition: 8.0.11
MySQL Shell version: 8.0.11
MySQL route version: 8.0.11

Second, download the package

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

Third, install the package

(The other two hosts are configured synchronously)

1. Configure the Hosts file # cat/etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 Local Host Localhost.localdomain localhost6 localhost6.localdomain6192.168.115.5 vm1192.168.115.6 vm2192.168.115.7 vm32, Installation 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, installing 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, installing, and initializing 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 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 aa basedir=<path-to-mysql-installation-directory>#-Add the path to The mysql-installation-directory to the Basedir variable# If Your change base is 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.cn     f/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 [email protected] '% ' identified by ' 123456 ';mysql> grant all PR Ivileges on * * to [email protected] '% ';mysql> flush PrivIleges;mysql> set sql_log_bin=1; 

Iv. Checking the configuration

VM1 execution can

使用JS命令，检查每台服务器的配置。三台服务的mysql都要执行检查# /usr/local/mysql-shell/bin/mysqlsh  MySQL  JS > dba.checkInstanceConfiguration(‘[email protected]:3306‘)Please provide the password for ‘[email protected]: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"的时候，表示该服务器检查通过

V. Create a group copy

VM1 execution can

 #/usr/local/mysql-shell/bin/mysqlsh--uri [email protected]:3306 mysql vm1:3306 SSL JS > var cluster = dba.cr Eatecluster (' main ') A new InnoDB cluster would be is created on instance ' [email protected]:3306 '. Validating instance at Vm1:3306...this instance reports it own address as vm1instance configuration is suitable. Creating InnoDB cluster ' main ' on ' [email protected]:3306 ' ... Adding Seed Instance ... Cluster successfully created. Use the Cluster.addinstance () to add MySQL instances. At least 3 instances is needed for the cluster to being able to withstand up Toone server failure. JS > Cluster.addinstance (' [email protected]:3306 ') JS > Cluster.addinstance (' [email protected]:3306 ') MySQL vm1:3306 SSL JS > Cluster.status () {"clustername": "Main", "Defaultreplicaset": {"name": "De Fault "," PRIMARY ":" vm1:3306 "," SSL ":" REQUIRED "," status ":" OK "," statustext ":" Cluster I s 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": {"ad                 Dress ":" vm3:3306 "," mode ":" r/o "," Readreplicas ": {}," role ":" HA ", "Status": "ONLINE"}}, "Groupinformationsourcemember": "Mysql://[email protect ed]:3306 "}

Six, configuration Mysql-route

VM1 execution can

# useradd mysql-router#/usr/local/mysql-router/bin/mysqlrouter--bootstrap [email protected]: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 have 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:/da Ta/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

VII. Connection Test

[[email protected] ~]#/usr/local/mysql/bin/mysql-u root-h 192.168.115.5-p 6446-penter password:welcome to the M  Ysql Monitor. Commands End With; or \g.your MySQL connection ID is 87Server version:8.0.11 mysql Community server-gplcopyright (c) $, 2018, Oracle an d/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names 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) [[email protected] ~]#/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) 2018, Oracle a nd/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names 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)

Mysql Innodb cluster test