The second article of the MYCAT study note. MYSQL read-write separation and log analysis--master-Slave Multi-node

1 Environmental Description

The first article of the environment "MYCAT Study notes". MYSQL read-write separation and log analysis--master-slave single node http://www.cnblogs.com/kaye0110/p/5134588.html

Added a set of MySQL instances, Port 3308, synchronization of host by Binlog mode

localhost:3306 host, configured as Writehost in Mycat 1

localhost:3307 from Machine A, configured as Readhost in Mycat

localhost:3308 from machine B, configured as Writehost in Mycat 2

2 Mycat Configuration

Basic configuration refer to the previous article, the difference is switchtype and balance and heartbeat monitoring statements

According to the parameter description, it is not meaningful to configure readhost here

Switchtype Property

-1 means no automatic switching -1 default, automatic switching

2 based on MySQL master-Slave synchronization status decide whether to switch

Heartbeat statement for show slave status

-3 switching mechanism based on MySQL galera cluster (for cluster) (1.4.1)

Heartbeat statement for show status like ' wsrep% '

Balance= "0", does not open the read-write separation mechanism, all read operations are sent to the currently available writehost.

<datahost name= "Localhost1" maxcon= "+" mincon= "balance=" 0 "
　　writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "2" slavethreshold= ">"
show slave status

<!--can have multi write hosts--
<writehost host= "hostM1" url= "localhost:3306" user= "root" password= "root123" >
<!--can have multi read hosts--
<readhost host= "hostS1" url= "localhost:3307" user= "root" password= "root123"/>
</writeHost>

<writehost host= "hostM2" url= "localhost:3308" user= "root" password= "root123" ></writeHost>

</dataHost>

3 Basic SQL Operation validation

Look directly at the insert operation of the log, the current data is directly into the 3306 port of the host.

01/17 10:53:58.596 DEBUG [$_NIOREACTOR-1-RW] (serverqueryhandler.java:56)-serverconnection [Id=1, Schema=TESTDB, host =0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=testdb]insert into AAA (Id,context) VALUES (7, ' Insert by M1 ')

01/17 10:53:58.601 DEBUG [$_NIOREACTOR-1-RW] (nonblockingsession.java:113)-serverconnection [Id=1, Schema=TESTDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=testdb]insert into AAA (Id,context) VALUES (7, ' Insert by M1 '), route={
1-Dn1{insert into AAA (Id,context) VALUES (7, ' Insert by M1 ')}
} RRS

01/17 10:53:58.601 DEBUG [$_NIOREACTOR-1-RW] (mysqlconnection.java:445)-con need syn, total syn cmd 1 commands SET Sessio N TRANSACTION Isolation level repeatable Read;schema change:false con:mysqlconnection [id=1, lasttime=1452999238601, User=root, schema=mycat_sync_test, old Shema=mycat_sync_test, Borrowed=true, Fromslavedb=false, threadId=43, charset= UTF8, txisolation=0, autocommit=true, Attachment=dn1{insert into AAA (Id,context) VALUES (7, ' Insert by M1 ')}, Resphandler =singlenodehandler [Node=dn1{insert into AAA (Id,context) VALUES (7, ' Insert by M1 ')}, Packetid=0], host=localhost, PO rt=3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=true]

Take a look at the log of the select operation, the data is obtained from the M1 node, which is the 3306-port MySQL Host

01/17 10:54:04.466 DEBUG [$_NIOREACTOR-1-RW] (serverqueryhandler.java:56)-serverconnection [Id=1, Schema=TESTDB, host =0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=testdb]select * from AAA

01/17 10:54:04.467 DEBUG [$_NIOREACTOR-1-RW] (enchachepool.java:70)-sqlroutecache hit cache, Key:testdbselect * from AAA

01/17 10:54:04.467 DEBUG [$_NIOREACTOR-1-RW] (nonblockingsession.java:113)-serverconnection [Id=1, Schema=TESTDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=testdb]select * from AAA, route={
1-Dn1{select *
From AAA
LIMIT 100}
} RRS
01/17 10:54:04.467 DEBUG [$_NIOREACTOR-1-RW] (physicaldbpool.java:452)-Select Read Source hostM1 for Datahost: Localhost1

01/17 10:54:04.468 DEBUG [$_NIOREACTOR-1-RW] (mysqlconnection.java:445)-con need syn, total syn cmd 1 commands SET Sessio N TRANSACTION Isolation level repeatable Read;schema change:false con:mysqlconnection [id=3, lasttime=1452999244468, User=root, schema=mycat_sync_test, old Shema=mycat_sync_test, Borrowed=true, Fromslavedb=false, threadId=38, charset= UTF8, txisolation=0, autocommit=true, Attachment=dn1{select *
From AAA
LIMIT, Resphandler=singlenodehandler [Node=dn1{select *
From AAA
LIMIT [], packetid=0], Host=localhost, port=3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]

4 host offline, read/write verification

Stop the MySQL host 3306 service through Windows service and start verifying the Mycat master-slave automatic switching effect.

Database SQL authentication, the first two queries directly return the exception, the third time has been able to return the data normally

Mysql> SELECT * from AAA;
ERROR 1184 (HY000): Connection Refused:no Further information
Mysql> SELECT * from AAA;
ERROR 1184 (HY000): Connection Refused:no Further information
Mysql> SELECT * from AAA;
+----+----------------+
| ID | Context |
+----+----------------+
| 1 | Hello 1 |
| 2 | Hello 2 |
| 3 | Hello3 |
| 4 | Hello4 |
| 5 | Hell World5 |
| 6 | New MySQL 3308 |
| 7 | Insert by M1 |
+----+----------------+
7 Rows in Set (0.00 sec)

Look again at the Logs/mycat.log log document

The data was obtained from DN1, but it has been changed from hostM2.

01/17 11:09:04.975 DEBUG [$_NIOREACTOR-1-RW] (serverqueryhandler.java:56)-serverconnection [Id=1, Schema=TESTDB, host =0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=testdb]select * from AAA
01/17 11:09:04.980 DEBUG [$_NIOREACTOR-1-RW] (enchachepool.java:76)-sqlroutecache Miss Cache, Key:testdbselect * from Aaa
01/17 11:09:05.110 DEBUG [$_NIOREACTOR-1-RW] (enchachepool.java:59)-sqlroutecache add cache, Key:testdbselect * from AAA Value:select * from AAA, route={
1-Dn1{select *
From AAA
LIMIT 100}
}
01/17 11:09:05.111 DEBUG [$_NIOREACTOR-1-RW] (nonblockingsession.java:113)-serverconnection [Id=1, Schema=TESTDB, Host=0:0:0:0:0:0:0:1, User=test,txisolation=3, Autocommit=true, Schema=testdb]select * from AAA, route={
1-Dn1{select *
From AAA
LIMIT 100}
} RRS
01/17 11:09:05.113 DEBUG [$_NIOREACTOR-1-RW] (physicaldbpool.java:452)-Select Read Source hostM2 for Datahost:localhost1
01/17 11:09:05.114 DEBUG [$_NIOREACTOR-1-RW] (mysqlconnection.java:445)-con need syn, total syn cmd 1 commands SET Sessio N TRANSACTION Isolation level repeatable Read;schema change:false con:mysqlconnection [id=14, lasttime=1453000145114, User=root, schema=mycat_sync_test, old Shema=mycat_sync_test, Borrowed=true, Fromslavedb=false, threadId=10, charset= UTF8, txisolation=0, autocommit=true, Attachment=dn1{select *
From AAA
LIMIT, Resphandler=singlenodehandler [Node=dn1{select *
From AAA
LIMIT [], packetid=0], Host=localhost, port=3308, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]

At the same time Mycat appear warning, notify HostM2 cut to host, hostS1 failure.

01/17 11:09:17.412 WARN [$_NIOREACTOR-0-RW] (mysqldetector.java:139)- found MySQL master/slave Replication ERR!!! Dbhostconfig [HOSTNAME=HOSTM2, Url=localhost:3308]error reconnecting to master ' [email protected]:3306 '-retry-time:60 retries:86400
01/17 11:09:17.413 DEBUG [$_NIOREACTOR-1-RW] (physicaldatasource.java:403)-release channel mysqlconnection [id=11, lasttime=1453000157394, User=root, schema=mycat_sync_test, old Shema=mycat_sync_test, Borrowed=true, fromSlaveDB= True, threadid=53, Charset=utf8, txisolation=0, Autocommit=true, Attachment=null, Resphandler=null, Host=localhost, port=3307, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]
01/17 11:09:17.413 WARN [$_NIOREACTOR-1-RW] (mysqldetector.java:139)-found MySQL master/slave Replication ERR!!! Dbhostconfig [Hostname=hosts1,Url=localhost:3307]error reconnecting to master ' [email protected]:3306 '-retry-time:60 retries:86400
01/17 11:09:18.418 INFO [$_nioconnector] (abstractconnection.java:458)-close Connection,reason: Java.net.ConnectException:Connection Refused:no further information, mysqlconnection [Id=0, lasttime=1453000157394, User=root, schema=mycat_sync_test, old Shema=mycat_sync_test, Borrowed=false, Fromslavedb=false, ThreadId=0, charset= UTF8, txisolation=0, autocommit=true, Attachment=null, Resphandler=null, Host=localhost, port=3306, StatusSync=null, Writequeue=0, Modifiedsqlexecuted=false]
01/17 11:09:18.418 INFO [$_nioconnector] (sqljob.java:111)-can ' t get connection for sql:show slave status
01/17 11:09:18.418 INFO [$_nioconnector] (physicaldatasource.java:373)-not Ilde Connection in pool,create New connection for hostM1 of schema Mycat_sync_test

Validation via MYCAT database insert operation

Mysql> INSERT INTO AAA (Id,context) VALUES (8, ' Insert by M2 ');
Query OK, 1 row affected (0.01 sec)

Mysql> SELECT * from AAA;
+----+----------------+
| ID | Context |
+----+----------------+
| 1 | Hello 1 |
| 2 | Hello 2 |
| 3 | Hello3 |
| 4 | Hello4 |
| 5 | Hell World5 |
| 6 | New MySQL 3308 |
| 7 | Insert by M1 |
| 8 | Insert by M2 |
+----+----------------+
8 rows in Set (0.00 sec)

Look at the physical machine 3307, the query is not up-to-date data

Mysql> Use Mycat_sync_test
Database changed
Mysql> SELECT * from AAA;
+----+----------------+
| ID | Context |
+----+----------------+
| 1 | Hello 1 |
| 2 | Hello 2 |
| 3 | Hello3 |
| 4 | Hello4 |
| 5 | Hell World5 |
| 6 | New MySQL 3308 |
| 7 | Insert by M1 |
+----+----------------+
7 Rows in Set (0.00 sec)

Then look at the physical machine 3308 M2 situation, the query has the latest data, indicating that Mycat automatically switch normal.

Mysql> Use Mycat_sync_test
Database changed
Mysql> SELECT * from AAA;
+----+----------------+
| ID | Context |
+----+----------------+
| 1 | Hello 1 |
| 2 | Hello 2 |
| 3 | Hello3 |
| 4 | Hello4 |
| 5 | Hell World5 |
| 6 | New MySQL 3308 |
| 7 | Insert by M1 |
| 8 | Insert by M2 |
+----+----------------+
8 rows in Set (0.00 sec)

5 Mycat monitoring and management function

9066 Port Login Mycat management function

Mysql-u Test-ptest-p 9066

Mysql> Show @ @datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+--
-------+
| DATANODE | NAME | TYPE | HOST | PORT | w/r | ACTIVE | IDLE | SIZE | EXECUTE |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+
| DN2 | hostM1 | MySQL | localhost | 3306 | W | 0 | 0 | 1000 |
| DN2 | hostM2 | MySQL | localhost | 3308 | W | 0 | 8 | 1000 |161 |
| DN2 | hostS1 | MySQL | localhost | 3307 | T: 0 | 8 | 1000 |137 |
| DN1 | hostM1 | MySQL | localhost | 3306 | W | 0 | 0 | 1000 |
| DN1 | hostM2 | MySQL | localhost | 3308 | W | 0 | 8 | 1000 |161 |
| DN1 | hostS1 | MySQL | localhost | 3307 | T: 0 | 8 | 1000 |137 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+
6 rows in Set (0.02 sec)

6 Additional Instructions

Go back and verify that the master-slave recovery function

Another article also mentioned can be consulted: http://www.2cto.com/database/201511/448344.html

The end of this article.

The second article of the MYCAT study note. MYSQL read-write separation and log analysis--master-Slave Multi-node