MySQL master-slave read-write separation configuration based on mycat and examples

1.mycat Binary Package Installation

TAR-ZXVF mycat-server-1.6.5-release-20180122220033-linux.tar.gz
CD Mycat
MV mycat/opt/

Useradd Mycat
Chown-r Mycat:mycat Mycat

2.mysql operation
Building the main library environment
Omitted......

Create a database
CREATE DATABASE ' integration01 ' DEFAULT CHARACTER SET UTF8;

Create a physical table

CREATE TABLE ' user ' (
' id ' int (one) not NULL auto_increment COMMENT ' primary key ',
' Borrow_nid ' varchar (the DEFAULT NULL COMMENT ' borrowing number '),
' Create_user ' varchar (+) DEFAULT ' admin ' COMMENT ' creator ',
' Create_time ' datetime not NULL DEFAULT current_timestamp COMMENT ' creation time ',
' Update_time ' datetime not NULL DEFAULT current_timestamp on update current_timestamp COMMENT ' Update Time ',
PRIMARY KEY (' id ')
Engine=innodb DEFAULT Charset=utf8 comment= ' signed data record form ' after the contract;

And so on, has been created from User1,user2,user3 to User10.

3. Modify the configuration file

[[email protected] conf]# cat schema.xml
<?xml version= "1.0"?
<! DOCTYPE mycat:schema SYSTEM "Schema.dtd",
<mycat:schema xmlns:mycat= "http://io.mycat/";
<schema Name= "Integration01" checksqlschema= "false" sqlmaxlimit= "" ",
<table name=" user "primarykey=" id " Autoincrement= "true" datanode= "dn1" ></table;
</schema>
<datanode name= "dn1" datahost= " Canal3 "database=" Integration01 "/>
<datahost name=" Canal3 "maxcon=" + "mincon=" balance= "3" writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "2" slavethreshold= "" ";
<writehost host= "10.40.20.44" url= "10.40.20.44:3307" user= "root" password= "chengce243" >
<readhost host= "10.40.20.44" url= "10.40.20.44:3308" user= "root" password= "chengce243"/>
</ Writehost>
</datahost>
</mycat:schema>

(1) Set balance= "1" and writetype= "0"
Balance parameter settings:
1. balance= "0", all read operations are sent to the currently available writehost.
2. Balance= "1", all read operations are sent randomly to readhost.
3. Balance= "2", all read operations are randomly distributed on writehost, Readhost
Writetype parameter settings:
1. Writetype= "0", all write operations are sent to the available writehost.
2. Writetype= "1", all write operations are sent randomly to readhost.
3. Writetype= "2", all the write operations are randomly in Writehost, readhost points on the hair.
"Readhost belongs to Writehost, meaning that it gets synchronized data from that writehost, so when the writehost it belongs to is down, it will no longer participate in the read-write separation, which is" not working, "because at this point, its data is" unreliable The Based on this consideration, currently in the Mycat 1.3 and 1.4 versions, if you want to support MySQL's standard configuration for a master-and-Slave, and when the primary node is down, the data can be read from the node, it needs to be configured in Mycat to two writehost and set banlance=1. ”

(2) Set switchtype= "2" and slavethreshold= "100"
The Mycat heartbeat Check statement is configured as show slave status, and two new attributes are defined on Datahost: switchtype= "2" and slavethreshold= "100", This means that the read-write separation and switching mechanism of the MySQL master-slave replication state binding is turned on. Mycat heartbeat mechanism by detecting "seconds_behind_master", "slave_io_running", "slave_sql_running" in Show slave status Three fields to determine the state of the current master-slave synchronization and the Seconds_behind_master master-slave replication delay. “

[email protected] conf]# cat Server.xml
<?xml version= "1.0" encoding= "UTF-8"?>
<!----Licensed under the Apache License, Version 2.0 (the "License");
-You are not a use of this file except in compliance with the License. -You
May obtain a copy of the License at--http://www.apache.org/licenses/LICENSE-2.0
--unless required by applicable or agreed to writing, software-
Distributed under the License is distributed on a "as is" BASIS,-without
Warranties or CONDITIONS of any KIND, either express OR implied. -See the
License for the specific language governing permissions And-limitations
Under the License. -
<! DOCTYPE mycat:server SYSTEM "SERVER.DTD" >
<mycat:server xmlns:mycat= "http://io.mycat/" >
<system>
<property name= "Nonepasswordlogin" >0</property> <!--0 to require password login, 1 for no password login, default is 0, set to 1 you need to specify the default account--
<property name= "UseHandshakeV10" >1</property>
<property name= "Usesqlstat" >0</property> <!--1 To turn on real-time stats, 0 to OFF
<property name= "Useglobletablecheck" >0</property> <!--1 To enable full overtime consistency detection, 0 off-

<property name= "Sequncehandlertype" >2</property>
<property name= "Subqueryrelationshipcheck" >false</property> < If there is an associated query in the subquery!--, check the associated field for the Shard field. Default False--
<!--<property name= "usecompression" >1</property>--> <!--1 To turn on MySQL compression protocol--
<!--<property name= "fakemysqlversion" >5.6.20</property>--> <!--set the emulated MySQL version number--
<!--<property name= "Processorbufferchunk" >40960</property>-
<!--
<property name= "Processors" >1</property>
<property name= "Processorexecutor" >32</property>
-
<!--default to type 0:directbytebufferpool | Type 1 Bytebufferarena | Type 2 Nettybufferpool
<property name= "Processorbufferpooltype" >0</property>
<!--default is 65535 64K maximum text length for SQL resolution--
<!--<property name= "Maxstringliterallength" >65535</property>-->
<!--<property name= "Sequncehandlertype" >0</property>-->
<!--<property name= "Backsocketnodelay" >1</property>-->
<!--<property name= "Frontsocketnodelay" >1</property>-->
<!--<property name= "Processorexecutor" >16</property>-->
<!--
<property name= "ServerPort" >8066</property> <property name= "Managerport" >9066</property>
<property name= "IdleTimeout" >300000</property> <property name= "Bindip" >0.0.0.0</property>
<property name= "Frontwritequeuesize" >4096</property> <property name= "processors" >32</ Property>--
<!--Distributed transaction switches, 0 for non-filtering of distributed transactions, 1 for filtering distributed transactions (not filtering if only global tables are involved within a distributed transaction), 2 for non-filtering of distributed transactions, but for logging distributed transaction logs--
<property name= "Handledistributedtransactions" >0</property>

<!--
Off heap for Merge/order/group/limit 1 open 0 off
-
<property name= "Useoffheapformerge" >1</property>

<!--
Unit is M
-
<property name= "Memorypagesize" >64k</property>

<!--
Unit is K
-
<property name= "Spillsfilebuffersize" >1k</property>

<property name= "Usestreamoutput" >0</property>

<!--
Unit is M
-
<property name= "Systemreservememorysize" >384m</property>

<!--whether to use zookeeper coordinated switching--
<property name= "Usezkswitch" >false</property>

<!--XA Recovery log Path--
<!--<property name= "Xarecoverylogbasedir" >./</property>-->

<!--XA Recovery log journal name--
<!--<property name= "Xarecoverylogbasename" >tmlog</property>-->

</system>

<!--global SQL firewall Settings--
<!--white list you can use the wildcard% or *-->
<!--such as <!--such as <!--such as <!--such as <!--in these configurations, you can log on as the root account for 127.0.0.1
<!--
<firewall>
<whitehost>
</whitehost>
<blacklist check= "false" >
</blacklist>
</firewall>
-

<user name= "root" defaultaccount= "true" >
<property name= "Password" >123456</property>
<property name= "Schemas" >integration01</property>

<!--table-level DML permissions Settings-
</user>

<user name= "User" >
<property name= "Password" >user</property>
<property name= "Schemas" >integration01</property>
<property name= "ReadOnly" >false</property>
</user>

</mycat:server>

[[email protected] conf]# cat rule.xml
<?xml version= "1.0" encoding= "UTF-8"?
<!----Licensed Under the Apache License, Version 2.0 (the "License");
-You may not use this file except in compliance with the License.-You
may obtain a copy of the License at--H ttp://www.apache.org/licenses/license-2.0
--unless required by applicable law or agreed to in writing, software-distributed under the License is distributed on a "as is" BASIS,-without
warranties OR CONDITIONS of any KIND, ei Ther express or implied. -See the
License for the specific language governing permissions And-limitations
under the License-->
& lt;! DOCTYPE mycat:rule SYSTEM "Rule.dtd",
<mycat:rule xmlns:mycat= "http://io.mycat/";
<tablerule Name = "Rule1";
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tablerule>

<tablerule name= "Rule2" >
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>

<tablerule name= "Sharding-by-intfile" >
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tablerule name= "Auto-sharding-long" >
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tablerule name= "Mod-long" >
<rule>
<columns>id</columns>


<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tablerule name= "Sharding-by-murmur" >
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tablerule name= "Crc32slot" >
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tablerule name= "Sharding-by-month" >
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tablerule name= "Latest-month-calldate" >
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>

<tablerule name= "Auto-sharding-rang-mod" >
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>

<tablerule name= "Jch" >
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>

<function name= "murmur"
class= "Io.mycat.route.function.PartitionByMurmurHash";
<property name= "Seed" >0</property><!--default is 0-->
<property name= "Count" >2</property><!-- The number of database nodes to be partitioned must be specified, otherwise the Shard--> cannot be partitioned;
<property name= "Virtualbuckettimes" >160</property><!-- An actual database node is mapped to so many virtual nodes, the default is 160 times times, that is, the number of virtual nodes is 160 times times the number of physical nodes-->
<!--<property name= "Weightmapfile" > The weight of the weightmapfile</property> node, the node without the specified weight, is 1 by default. Fill in the format of the properties file with the integer value from 0 to count-1 that is, the node index is key, and the node weight value is the value. All weight values must be positive integers, or 1 instead of-->
<!--<property name= "Bucketmappath" >/etc/mycat/bucketmappath</property >
to observe the distribution of the physical nodes and virtual nodes when testing, if this attribute is specified, the Murmur hash value of the virtual node and the mapping of the physical node are output to this file by line, there is no default value, if not specified, it will not output anything-->
</function>

<function name= "Crc32slot"
class= "Io.mycat.route.function.PartitionByCRC32PreSlot" >
<property name= "Count" >2</property><!--the number of database nodes to be fragmented must be specified, otherwise it cannot be fragmented--
</function>
<function name= "Hash-int"
class= "Io.mycat.route.function.PartitionByFileMap" >
<property name= "MapFile" >partition-hash-int.txt</property>
</function>
<function name= "Rang-long"
class= "Io.mycat.route.function.AutoPartitionByLong" >
<property name= "MapFile" >autopartition-long.txt</property>
</function>
<function name= "Mod-long" class= "Io.mycat.route.function.PartitionByMod" >
<!--How many data nodes--
<property name= "Count" >10</property>
</function>

<function name= "func1" class= "Io.mycat.route.function.PartitionByLong" >
<property name= "Partitioncount" >8</property>
<property name= "Partitionlength" >128</property>
</function>
<function name= "Latestmonth"
class= "Io.mycat.route.function.LatestMonthPartion" >
<property name= "Splitoneday" >24</property>
</function>
<function name= "Partbymonth"
class= "Io.mycat.route.function.PartitionByMonth" >
<property name= "DateFormat" >yyyy-MM-dd</property>
<property name= "Sbegindate" >2015-01-01</property>
</function>

<function name= "Rang-mod" class= "Io.mycat.route.function.PartitionByRangeMod" >
<property name= "MapFile" >partition-range-mod.txt</property>
</function>

<function name= "Jump-consistent-hash" class= "Io.mycat.route.function.PartitionByJumpConsistentHash" >
<property name= "Totalbuckets" >3</property>
</function>
</mycat:rule>

4. Query sub-table

[Email protected] ~]# mysql-uuser-puser-h10.40.20.44-p8066 Integration01
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.6.29-mycat-1.6.5-release-20180122220033 mycat Server (OPENCLOUNDDB)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Show tables;
+-------------------------+
| Tables in Integration01 |
+-------------------------+
| user |
+-------------------------+
1 row in Set (0.00 sec)

Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES ("201802051008610", ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) values (one, ' 201802051008611 ', ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES ("201802051008612", ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES ("201802051008613", ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) values (+, ' 201802051008614 ', ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES ("201802051008615", ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) values (+, ' 201802051008616 ', ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) values (+, ' 201802051008617 ', ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES ("201802051008618", ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES (+ ' 201802051008619 ', ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) values (+, ' 201802051008620 ', ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES (+ ' 201802051008621 ', ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES ("201802051008622", ' DBA ', Now (), now () );
Insert into User (Id,borrow_nid,create_user,create_time,update_time) VALUES ("201802051008623", ' DBA ', Now (), now () );

Mysql> Explain select * from user;
+-----------+--------------------------------+
| Data_node | SQL |
+-----------+--------------------------------+
| DN1 | SELECT * from user1 LIMIT 100 |
| DN1 | SELECT * from User10 LIMIT 100 |
| DN1 | SELECT * from User2 LIMIT 100 |
| DN1 | SELECT * from User3 LIMIT 100 |
| DN1 | SELECT * from User4 LIMIT 100 |
| DN1 | SELECT * from User5 LIMIT 100 |
| DN1 | SELECT * from User6 LIMIT 100 |
| DN1 | SELECT * from User7 LIMIT 100 |
| DN1 | SELECT * from User8 LIMIT 100 |
| DN1 | SELECT * from User9 LIMIT 100 |
+-----------+--------------------------------+

MySQL master-slave read-write separation configuration based on mycat and examples