MYSQL's Cluster

Core architecture

• MySQL 5.7 introduces the group Replication feature, which enables automatic host elections between a set of MySQL servers to form a master multi-slave structure. After advanced configuration, multi-master and multi-slave structures can be implemented.
• MySQL Router is a lightweight, transparent middleware that automatically acquires the status of the above clusters, plans SQL statements, and allocates them to a reasonable MySQL backend for execution.
• MySQL Shell is an interactive program that supports both JavaScript and SQL, so you can quickly configure InnoDB Cluster.
  MySQL InnoDB Cluser | Mysql 5.7 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 DB1
192.168.10.124 DB2
192.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.rpm

Yum Install mysql57-community-release-el7-11.noarch.rpm
Yum Install Mysql-shell-y
Yum Install Mysql-router-y
Set permissions for the relevant user, the production environment may not be the root user
Grant all privileges in . to ' root ' @ ' percent ' 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 |
+----------------------------------+---------------+----------------+-------------------------------------- ------------+

To 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 ':
Detecting the configuration file ...
Found configuration file at standard location:/etc/my.cnf
Does 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 ')
Provide the password for ' [email protected]:3306 ':
Validating instance ...

The instance ' db1:3306 ' is valid for Cluster usage
{
"Status": "OK"
}

Landing

Mysqlsh--uri [Email protected]:3306

Create cluster Main

mysql-js> var cluster = dba.createcluster (' main ')
A new InnoDB cluster would be created on instance ' [email protected]:3306 '.

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
One server failure.

Add child nodes

Mysql-js> cluster.addinstance (' [Email protected]:3306 ')
Mysql-js> cluster.addinstance (' [Email protected]:3306 ')

View node Information

Mysql-js> Cluster.status ()

Persist the configuration to MY.CNF

Mysql-js> \connect DB1
Mysql-js> dba.configurelocalinstance (' db1:3306 ')

View basic information

Mysql-js> Cluster.describe ();

After exiting, view the node information again

var cluster = Dba.getcluster ();
Cluster.status ();
Mysql-route settings

This command updates the configuration information in the/etc/mysqlrouter/mysqlrouter.conf, which can be the other machine selected here for DB2

[Email protected] ~]# mysqlrouter--bootstrap [email protected]:3306--user Mysqlrouter

Please enter MySQL password for root:
Warning:the MySQL server does not has SSL configured and metadata used by the router may be 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 Reading and writing
• Read/only connections:localhost:6447 Read Only

X protocol connections to cluster ' main ':

• Read/write connections:localhost:64460
• Read/only connections:localhost:64470

Existing configurations backed up To/etc/mysqlrouter/mysqlrouter.conf.bak
[Email protected] ~]# systemctl start Mysqlrouter

Start

Systemctl Start Mysqlrouter
Systemctl Enable Mysqlrouter

View ports

[Email protected] ~]# Netstat-lntup
Active 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/mysqlrouter
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:64470 0.0.0.0: LISTEN 2958/mysqlrouter

Verify

Mysql-u root-h 127.0.0.1-p 6446-p

SELECT @ @port;
SELECT @ @hostname;
Fault simulation

# #关闭 DB1 database, automatically switch to the following:

"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 the command

mysql> show databases;
ERROR (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection id:20
Current database: NONE
Mysql> SELECT @ @hostname;
+------------+
| @ @hostname |
+------------+
| DB1 |
+------------+

# #重启节点后, need to join manually
"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.

All nodes are rebooted, rejoin

Mysqlsh--uri [Email protected]:3306
mysql-js> var cluster = Dba.rebootclusterfromcompleteoutage ();

Reconfiguring the default cluster from complete outage ...

The instance ' db2:3306 ' is part of the cluster configuration.
Would you like to rejoin it to the cluster? [y| N]: Y

The instance ' db3:3306 ' is part of the cluster configuration.
Would you like to rejoin it to the cluster? [y| N]: Y

The cluster was successfully rebooted.
Summary of error:

# #如果节点在加入集群前, write operation, error when joining the cluster
Error:error joining instance to cluster: ' db2:3306 '-Query failed. MySQL Error (3092): The server is a configured properly to being an active member of the group. Please see more details on error log. Query:start group_replication (RuntimeError)

# #登陆 DB2 database performs reset master;

If "status" is present: "No_quorum" performs the fix, re-joins the pending test

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
What state does the node have?

* ONLINE  - 节点状态正常。* OFFLINE  -   实例在运行，但没有加入任何Cluster。* RECOVERING - 实例已加入Cluster，正在同步数据。* ERROR  -  同步数据发生异常。* UNREACHABLE -  与其他节点通讯中断，可能是网络问题，可能是节点crash。* MISSING 节点已加入集群，但未启动group replication

What are the states of the cluster

* OK – 所有节点处于online状态，有冗余节点。* OK_PARTIAL – 有节点不可用，但仍有冗余节点。* OK_NO_TOLERANCE – 有足够的online节点，但没有冗余，例如：两个节点的Cluster，其中一个挂了，集群就不可用了。* NO_QUORUM – 有节点处于online状态，但达不到法定节点数，此状态下Cluster无法写入，只能读取。* UNKNOWN – 不是online或recovering状态，尝试连接其他实例查看状态。* UNAVAILABLE – 组内节点全是offline状态，但实例在运行，可能实例刚重启还没加入Cluster。

MYSQL's Cluster