Mycat realizes MySQL's sub-database and table, read-write separation and master-slave switching.

Recently, I have mainly invested in the project of Ping an Cloud MySQL DRDS. I am mainly responsible for the configuration of zookeeper module and the writing of background scripts. In order to deepen the understanding of the overall architecture of DRDS, I think it is necessary to manually build a Mysql distributed database cluster based on Mycat. I now share with you the construction process. Further research will be done on the configuration of Mycat. Please look forward to it.

Deployment of Mycat and MySQL instances:

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:

10 druidparser 2 0 3310 3311 0 1 1m 1k 0 389m 123456 db false

Schema.xml:

Select user (); select user ()

Modify the following configuration items in rule.xml:

Id mod-long 2

For the meaning of the tags in the configuration file, please refer to the article: MyCat key configuration instructions

Start Mycat:

[root@SZB-L0059021 bin] #. / mycat startStarting Mycat-server... [root@SZB-L0059021 bin] #. / mycat statusMycat-server is running (27020). [root@SZB-L0059021 bin] # mysql-uroot-p123456-Ddb-h227.0.0.1-P3310

Sub-database and 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 Magi database ()) Query OK, 1 row affected (0.01 sec) mysql > insert into T1 (id,db_name) values; Query OK, 1 row affected (0.03 sec) mysql > select * from T1 +-+-+ | id | db_name | +-+-+ | 2 | db1 |-- id=2, 0 for 2, so insert dn1 | 1 | db2 |-- id=1, 1 for 2 Therefore, insert dn2 +-+ 2 rows in set (0.01sec) the above query results show that the data inserted twice falls into db1 and db2, and the sub-database is realized.

Read-write separation verification:

Mysql > select * from T1 +-+-+ | id | db_name | +-+-+ | 2 | db1 |-- from db1-M2 on shard1 | 1 | db2 |-- from db2-M1+-+-+2 rows in set (0.01 sec) on shard2. The above query results are obtained from db1-M2 (port 3307) in dn1 and db2-M1:2018-05-08 15 db2-M1:2018 in dn2 03Parade 39.385 DEBUG [$_ NIOREACTOR-0-RW] (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 t1LIMIT 100} 2-> dn2 {SELECT * FROM t1LIMIT 100}... 2018-05-08 15 SELECT 0391 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} RespHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@66328ec4, host=10.20.8.126, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]... 2018-05-08 15 port=3307 03statusSync=null 39.392 DEBUG [$_ NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.rowEofResponse (MultiNodeQueryHandler.java:311))-on rowe nd 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} RespHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@66328ec4, host=10.25.80.7, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]...

Master-slave switch:

By looking at the dnindex.properties file in the mycat/conf directory, you can see that the writeHost,0 that mycat is using represents the first writeHost under the dataHost tag in the schema.xml.

[root@SZB-L0059021 conf] # cat dnindex.properties # update#Tue May 08 12:59:24 CST 2018shard2=0shard1=0-when the state is normal, mycat selects the first writeHost in each dataHost tag as the write entry

Switch to 10.20.8.126 host and manually stop db1-M1

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

Go back to 10.20.8.57 (mycat host) and look at dnindex.properties again

[root@SZB-L0059021 conf] # cat dnindex.properties # update#Tue May 08 15:12:12 CST 2018shard2=0shard1=1-- after db1-M1 is shutdown, mycat's writeHost on shard1 switches to db1-M2

Switch to 10.20.8.57 (the host where mycat resides) and execute the following insert command

Mysql > insert into T1 (id,db_name) values (4Jing database ()) Query OK, 1 row affected (0.01sec) check the log Mycat now chooses to write data through db1-M2 (port 3307): 2018-05-08 15 DEBUG 13NIOREACTOR-0-RW 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 Magnum 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]

Because writeType= "0" is configured in dataHost, even after db1-M1 restart and recovery, Mycat will still choose db1-M2 as the shard1

WriteHost .

Verify:

Switch to the 10.20.8.126 host and manually start db1-M110.20.8.126:3306:Master > mysqld_safe & insert validation data mysql > insert into T1 (id,db_name) values (6 Magi database ()) into Mycat Query OK, 1 row affected (0.02 sec) check the log The data is still written through db1-M2 (port 3307): 2018-05-08 15 NIOREACTOR-0-RW 16charge 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 (6mgb ())}, respHandler=SingleNodeHandler [node=dn1 {insert into T1 (id,db_name) values (6jrtb ())} PacketId=1], host=10.20.8.126, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

At this point, if you want to change Mycat's writeHost on shard1 to db1-M1 again, simply change shard1=1 in dnindex.properties to shard1=0 and restart Mycat.