MySQL InnoDB Cluser | Mysql 5.7 Cluster

• At present, this cluster application in its own Django demo environment, temporarily stable operation.
• Welcome to Dabigatran 620176501 to discuss the application of Mysql cluster.

Core Architecture

* MySQL 5.7 引入了 Group Replication 功能，可以在一组 MySQL 服务器之间实现自动主机选举，形成一主多从结构。经过高级配置后，可以实现多主多从结构。* MySQL Router 是一个轻量级透明中间件，可以自动获取上述集群的状态，规划 SQL 语句，分配到合理的 MySQL 后端进行执行。* MySQL Shell 是一个同时支持 JavaScript 和 SQL 的交互程序，可以快速配置 InnoDB Cluster。

Deployment

• This time a total of 3 machines, set host name and hosts | Configure the Report_host field in each service my.cnf for your own hostname

  192.168.10.123 db1192.168.10.124 db2192.168.10.125 db3

• Installation mysql5.7.20, you can refer to the following installation basic, I built the time is used this.

  http://blog.51cto.com/hequan/1982428

• Installing Mysql-shell Mysql-route

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpmyum install mysql57-community-release-el7-11.noarch.rpmyum install  mysql-shell  -yyum install  mysql-router  -y

• Set permissions for the relevant user, the production environment may not be the root user

grant all   privileges  on *.*  to ‘root‘@‘%‘  identified by ‘123456‘;GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO [email protected]‘%‘ WITH GRANT OPTION;GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO [email protected]‘%‘ WITH GRANT OPTION;GRANT SELECT ON *.* TO [email protected]‘%‘ WITH GRANT OPTION;flush privileges;

Mysqlsh

[[email protected] ~]# mysqlsh## Check the MySQL configuration file (3 hosts to operate this step) dba.checkinstanceconfiguration (' [email  protected]:3306 ') +----------------------------------+---------------+----------------+------------------------ --------------------------+| Variable | Current Value | Required Value | Note |+----------------------------------+---------------+----------------+--- -----------------------------------------------+| Binlog_checksum | CRC32 | NONE | Update the server variable or restart the server | | Binlog_format | MIXED | ROW | Update the server variable or restart the server | | enforce_gtid_consistency | OFF | On | Restart The Server | | Gtid_mode | OFF | On | Restart The Server | |            Log_slave_updates    | 0 | On | Restart The Server | | Master_info_repository | FILE | TABLE | Restart The Server | | Relay_log_info_repository | FILE | TABLE | Restart The Server | | transaction_write_set_extraction | OFF | XXHASH64 | Restart the server |+----------------------------------+---------------+----------------+--- -----------------------------------------------+## Repair the MySQL configuration file, you must use root (3 hosts to operate this step) dba.configurelocalinstance (' [email protected]:3306 ') provide the password for ' [email protected]:3306 ':D etecting the Configuration file ... Found configuration file at the standard location:/etc/my.cnfdo do want to modify this file?    [Y|n]: [y|n]: y## Restart mysql## re-check (3 hosts to operate this step) dba.checkinstanceconfiguration (' [email protected]:3306 ') Please provide the Password for ' [email protected]:3306 ': Validating instance ... The instance ' db1:3306 ' is valid for Cluster usage{"status": "OK"}

## 登陆mysqlsh --uri [email protected]:3306   ## 创建集群     mainmysql-js> var cluster = dba.createCluster(‘main‘)A new InnoDB cluster will be created on instance ‘[email protected]:3306‘.Creating InnoDB cluster ‘main‘ on ‘[email protected]: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.## 添加子节点mysql-js> cluster.addInstance(‘[email protected]:3306‘)mysql-js> cluster.addInstance(‘[email protected]:3306‘)## 查看节点信息mysql-js> cluster.status()## 将配置 持久化，写入到  my.cnfmysql-js>  \connect db1mysql-js> dba.configureLocalInstance(‘db1:3306‘)## 查看基本信息mysql-js>  cluster.describe();## 退出之后，再查看节点信息var cluster = dba.getCluster();cluster.status();

Mysql-route settings

# # This command updates the configuration information in/etc/mysqlrouter/mysqlrouter.conf, which can be another machine selected here for db2[[email protected] ~]# Mysqlrouter-- Bootstrap [email protected]:3306--user mysqlrouterplease enter MySQL password for root:WARNING:The MySQL server doe s not has SSL configured and metadata used by the router is transmitted unencrypted. Bootstrapping system MySQL Router instance ... 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-read/only connections : localhost:6447 read-only x protocol connections to cluster ' main ':-Read/write connections:localhost:64460-read/only Conne Ctions:localhost:64470existing configurations backed up to/etc/mysqlrouter/mysqlrouter.conf.bak[[email  Protected] ~]# systemctl start mysqlrouter## start systemctl start Mysqlroutersystemctl enable mysqlrouter## view port [[email& Nbsp;protected] ~]# Netstat-lntupactive Internet connections (only servers) Proto recv-q send-q Local address Foreign Address State Pid/program name TCP 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 2958/my        Sqlrouter TCP 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 2958/mysqlrouter TCP 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 2958/mysqlrouter TCP 0 0 0.0.0.0:644 0.0.0.0:* LISTEN 2958/mysqlrouter## Verify mysql-u root-h 127.0.0.1-p 6446-pselect @ @port; s elect @ @hostname;

Fault simulation

##关闭 db1 数据库，自动切换如下："topology": {            "db1:3306": {                "address": "db1:3306",                "mode": "R/O",                "readReplicas": {},                "role": "HA",                "status": "(MISSING)"            },            "db2:3306": {                "address": "db2:3306",                "mode": "R/W",                "readReplicas": {},                "role": "HA",                "status": "ONLINE"            },            "db3:3306": {                "address": "db3:3306",                "mode": "R/O",                "readReplicas": {},                "role": "HA",                "status": "ONLINE"            }

  # #重启db2, execute command mysql> show databases; ERROR (HY000): Lost connection to MySQL server during querymysql> show databases; ERROR 2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection id:20current Database: * * * NONE ***mysql> SELECT @ @hostname; +------------+| @ @hostname |+------------+|                DB1 |+------------+# #重启节点后, you need to manually add "db2:3306": {"Address": "db2:3306", "mode": "r/o", "Readreplicas": {}, "role": "HA", "Status": "(MISSING)" Cluster.rejoininstance (' [email protected]:3306 ')  The instance ' db2:3306 is successfully added to the MySQL Cluster. 

## 所有节点都重启了，重新加入mysqlsh --uri [email protected]:3306mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();Reconfiguring the default cluster from complete outage...The instance ‘db2:3306‘ was part of the cluster configuration.Would you like to rejoin it to the cluster? [y|N]: yThe instance ‘db3:3306‘ was part of the cluster configuration.Would you like to rejoin it to the cluster? [y|N]: yThe cluster was successfully rebooted.

Summary of error:

##如果节点在加入集群前，执行了写操作，加入集群时会报错ERROR: Error joining instance to cluster: ‘db2:3306‘ - Query failed. MySQL Error (3092): 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 (RuntimeError)##登陆 db2 数据库 执行 reset master;

## 如果出现了   "status": "NO_QUORUM"     执行修复，重新加入   ## 暂未测试cluster.forceQuorumUsingPartitionOf("db1:3306")mysql-js> cluster.rejoinInstance(‘[email protected]:3306‘)mysql-js> cluster.rejoinInstance(‘[email protected]:3306‘)

Postscript:

Official Document: Https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-userguide.html

节点有哪状态    * ONLINE  - 节点状态正常。    * OFFLINE  -   实例在运行，但没有加入任何Cluster。    * RECOVERING - 实例已加入Cluster，正在同步数据。    * ERROR  -  同步数据发生异常。    * UNREACHABLE -  与其他节点通讯中断，可能是网络问题，可能是节点crash。    * MISSING 节点已加入集群，但未启动group replication集群有哪些状态    * OK – 所有节点处于online状态，有冗余节点。    * OK_PARTIAL – 有节点不可用，但仍有冗余节点。    * OK_NO_TOLERANCE – 有足够的online节点，但没有冗余，例如：两个节点的Cluster，其中一个挂了，集群就不可用了。    * NO_QUORUM – 有节点处于online状态，但达不到法定节点数，此状态下Cluster无法写入，只能读取。    * UNKNOWN – 不是online或recovering状态，尝试连接其他实例查看状态。    * UNAVAILABLE – 组内节点全是offline状态，但实例在运行，可能实例刚重启还没加入Cluster。

MySQL InnoDB Cluser | Mysql 5.7 Cluster