Use Mycat to implement MySQL's sub-Library sub-table, read/write separation, master-slave switching

Mycat and MySQL instance deployment scenarios:
Mycat:
ip:10.20.8.57,port:3310/3311
Mysql:
db1-m1,ip:10.20.8.126,port:3306
db1-m2,ip:10.20.8.126,port:3307
db2-m1,ip:10.25.80.7,port:3307
The architecture diagram is as follows:

Configure Mycat
Server.xml:

<! DOCTYPE mycat:server SYSTEM "server.dtd" ><mycat:server xmlns:mycat= "http://io.mycat/" > <system> &        Lt;property name= "Usesqlstat" >1</property> <property name= "Useglobletablecheck" >0</property> <property name= "Defaultsqlparser" >druidparser</property> <property name= "Sequncehandlertype" & gt;2</property> <property name= "Processorbufferpooltype" >0</property> <property name= "s Erverport ">3310</property> <!--Mycat using ports--<property name=" Managerport ">3311</prop        Erty> <!--mycat Management Port--<property name= "Handledistributedtransactions" >0</property> <property name= "Useoffheapformerge" >1</property> <property name= "Memorypagesize" >1m</prope rty> <property name= "spillsfilebuffersize" >1k</property> <property name= "Usestreamoutput" & Gt;0</property>        <property name= "Systemreservememorysize" >389m</property> </system> <user name= "root" >        <property name= "password" >123456</property> <property name= "schemas" >db</property> <property name= "ReadOnly" >false</property> </user></mycat:server>

Schema.xml:

<?xml version= "1.0"? ><! DOCTYPE mycat:schema SYSTEM "schema.dtd" ><mycat:schema xmlns:mycat= "http://io.mycat/" > <!--database configuration,                             Corresponds to database in Server.xml-<schema name= "db" Checksqlschema= "false" sqlmaxlimit= ">"    <table name= "T1" datanode= "dn1,dn2" rule= "Mod-long"/> <!--to 2 modulo, see Rule.xml-</schema> <!--shard Configuration--<datanode name= "dn1" datahost= "Shard1" database= "db1"/> <data Node name= "DN2" datahost= "Shard2" database= "DB2"/> <!--Physical database configuration--<datahost name= "Shard1" maxcon= "100        0 "mincon=" balance= "3" writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= ">" 

Modify the following configuration items in Rule.xml:

<tableRule name="mod-long"> <rule> <columns>id</columns> <!-- t1的分片列 --> <algorithm>mod-long</algorithm> </rule></tableRule><function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property> <!-- count值与分片个数相同 --></function>

The meanings of each label in the configuration file can be found in the article: Mycat Key configuration Instructions

Start Mycat:

[[email protected] bin]# ./mycat startStarting Mycat-server...[[email protected] bin]# ./mycat statusMycat-server is running (27020).[[email protected] bin]# mysql -uroot -p123456 -Ddb -h127.0.0.1 -P3310

Sub-database Sub-table verification:

  mysql> show tables;+--------------+| Tables in db |+--------------+| T1 |+--------------+1 row in Set (0.00 sec) mysql> desc t1;+---------+-------------+------+-----+---------+--- ----+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| ID | Int (11) | YES | | NULL | || db_name | varchar (20) | YES | | NULL | |+---------+-------------+------+-----+---------+-------+2 rows in Set (0.01 sec) mysql> insert into T1 (id,db_name) VALUES (1,database ()); Query OK, 1 row affected (0.01 sec) mysql> insert into T1 (id,db_name) VALUES (2,database ()); Query OK, 1 row affected (0.03 sec) mysql> select * from T1; +------+---------+| ID | Db_name |+------+---------+| 2 | DB1 | --id=2, 0 for 2 modulo, so insert dn1 | 1 | DB2 | --id=1, the 2 modulo is 1, so insert DN2 +------+---------+ 2 rows in Set (0.01 sec) The above query results indicate that two times the data inserted into the DB1, DB2, the realization of the library  

read-Write separation validation:

Mysql> select * from T1; +------+---------+| ID |    Db_name |+------+---------+| 2 |     DB1 |    --From the db1-m2 on Shard1 | 1 |     DB2 | --db2-m1+------+---------+2 rows in Set (0.01 sec) from Shard2 see the log, The above query results are from db2-m1:2018-05-08 15:03:39.385 DEBUG [$_NIOREACTOR-0-RW] in DN1 (Port 3307) and DN2 (DB1-M2) ( Io.mycat.server.NonBlockingSession.execute (nonblockingsession.java:110))-serverconnection [Id=1, Schema=db, host= 127.0.0.1, User=root,txisolation=3, Autocommit=true, Schema=db]select * from T1, route={1, dn1{select *from T1LIMI T 2-dn2{select *from t1limit 100}} ... 2018-05-08 15:03:39.391 DEBUG [$_NIOREACTOR-0-RW] ( Io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.rowEofResponse (multinodequeryhandler.java:311))-On row End Reseponse Mysqlconnection [id=29, lasttime=1525763019368, User=root, SCHEMA=DB1, old shema=db1, Borrowed=true, Fromslavedb=true, threadid=511, Charset=utf8, txisolation=3, Autocommit=true, Attachment=dn1{select *FROM T1LIMIT 100} ResPH[EMAIL PROTECTED]66328EC4, host=10.20.8.126, port=3307, Statussync=null, writequeue=0, modifiedSQLExecuted= FALSE] ... 2018-05-08 15:03:39.392 DEBUG [$_NIOREACTOR-0-RW] ( Io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.rowEofResponse (multinodequeryhandler.java:311))-On row End Reseponse Mysqlconnection [id=3, lasttime=1525763019387, User=root, SCHEMA=DB2, old Shema=db2, Borrowed=true, Fromslavedb=false, threadid=28, Charset=utf8, txisolation=3, Autocommit=true, Attachment=dn2{select *FROM T1LIMIT 100} , RESPH[EMAIL PROTECTED]66328EC4, host=10.25.80.7, port=3307, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false] ...

Master-Slave Switching:
By looking at the Dnindex.properties file under the mycat/conf directory, you can know that the writehost,0 that Mycat is using represents the first schema.xml under Datahost tags in writehost.

[[email protected] conf]# cat dnindex.properties #update#Tue May 08 12:59:24 CST 2018shard2=0shard1=0               --此时状态正常，mycat选取每个dataHost标签中的第一个writeHost作为写入入口

Switch to the 10.20.8.126 host and manually stop the DB1-M1

10.20.8.126:3306:Master > mysqladmin -uroot -p123456 shutdown

Back to 10.20.8.57 (Mycat host), view dnindex.properties again

[[email protected] conf]# cat dnindex.properties #update#Tue May 08 15:12:12 CST 2018shard2=0shard1=1              --db1-M1被shutdown后，mycat在shard1上的writeHost切换至db1-M2

Switch to 10.20.8.57 (Mycat host), execute the following insert command

mysql> insert into t1(id,db_name) values(4,database());           Query OK, 1 row affected (0.01 sec)查看日志可知，Mycat此时选择通过db1-M2（端口3307）写入数据：2018-05-08 15:13:44.987 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341)) - release connection MySQLConnection [id=24, lastTime=1525763624968, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=506, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into t1(id,db_name) values(4,database())}, respHandler=SingleNodeHandler [node=dn1{insert into t1(id,db_name) values(4,database())}, packetId=1], host=10.20.8.126, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

Since writetype= "0" is configured in Datahost, Mycat will still choose db1-m2 as shard1 even after db1-m1 restart recovery
Writehost.
Verify:

切换至10.20.8.126主机，并手动启动db1-M110.20.8.126:3306:Master > mysqld_safe &在Mycat中插入验证数据mysql> insert into t1(id,db_name) values(6,database());Query OK, 1 row affected (0.02 sec)查看日志可知，数据仍通过db1-M2（端口3307）写入：2018-05-08 15:16:09.579 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341)) - release connection MySQLConnection [id=32, lastTime=1525763769548, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=514, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into t1(id,db_name) values(6,database())}, respHandler=SingleNodeHandler [node=dn1{insert into t1(id,db_name) values(6,database())}, packetId=1], host=10.20.8.126, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

At this time want to let Mycat on the shard1 on the writehost to DB1-M1, just modify dnindex.properties for shard1=1, and restart shard1=0.

Use Mycat to implement MySQL's sub-Library sub-table, read/write separation, master-slave switching