MYCAT realization of database read/write separation

What is read-write separation

In the database cluster schema, let the main library handle the transactional query, and the library is only responsible for processing the select query, so that the two division of labor is clear to improve the overall database read and write performance. Of course, another function of the primary database is to synchronize the data changes caused by transactional queries to the slave library, which is the write operation.

Benefits of Read and write separations

1) share the server pressure to improve the machine's system processing efficiency

Read/write separation is suitable for reading far more than writing scenarios, if there is a server, when select many, update and delete will be in these select Access data congestion, waiting for select end, concurrency performance is not high, and master and slave only responsible for the respective write and read, A great degree of relief for X-Lock and S-lock contention;

If we have 1 Master 3 from, not considering the one-sided setting mentioned in the above 1 from the library, assume that there are 10 writes and 150 reads in 1 minutes now. So, the 1 Master 3 writes from the equivalent of a total of 40, while the total number of reads does not change, so on average each server assumes 10 writes and 50 reads (the main library does not assume the read operation). Therefore, although the write is not changed, the read is greatly distributed, which improves the system performance. In addition, when the read is allocated, the performance of the write is increased indirectly. Therefore, the overall performance improved, the white is to take the machine and bandwidth Exchange performance;

2) Increase redundancy, improve service availability, and adjust another database server to restore service from the library as quickly as possible after it has crashed

Mycat principle

Mycat is an open source distributed database system, but because the database generally has its own database engine, and Mycat does not belong to its own unique database engine, all in strict sense is not considered a complete database system, can only be said to be a bridge between the application and the database of middleware.

650) this.width=650; "Src=" Http://www.lichengbing.cn/ueditor/php/upload/image/20160719/1468897464811589.png " Title= "1468897464811589.png" alt= "Mycat.png"/>

Before the advent of Mycat middleware, MySQL master-slave replication cluster, if you want to achieve read and write separation, is generally implemented in the program segment, which brings a problem, that is, the data segment and program coupling is too high, if the database address has changed, Then my program to make corresponding changes, if the database accidentally hung up, it also means that the program is not available, and for many applications, and can not be accepted;

The introduction of Mycat middleware is very good to decouple the program and database, so that the program only needs to focus on the address of the database middleware, without knowing how the underlying database provides the service, and a large number of common data aggregation, transaction, data source switching and other work are handled by the middleware;

Mycat Middleware is the principle of data processing, from one of the original library, is cut into multiple shard database, all of the fragmented database cluster constitutes the completion of the database storage, a bit similar to the RAID0 in the disk array.

Mycat Configuration Installation

Environment Readiness: Ensure that multiple instances of/data/3306/and/data/3307 have implemented simple asynchronous master-slave replication

1) Installing the JDK

Http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html #注意版本必须JDK7或者更高版本

[Email protected] tools]# RPM-IVH jdk-8u91-linux-x64.rpm Preparing ... ################################### ######## [100%] 1:jdk1.8.0_91 ########################################### [100%] #我这里下载的rpm包, installation is more convenient

2) Download Mycat

https://github.com/MyCATApache/Mycat-download## Test Here is the Mycat-server-1.4-release version of the extract copy to the/application/mycat directory

3) Create user

The main library, Web users have the right to add and revise check

Mysql> Grant Select,update,delete,insert on lilongzi.* to [email protected] ' 172.16.2.% ' identifiedby ' 123456 ';

Since the library is only responsible for reading data, all web only SELECT permissions

Mysql> Grant Select on lilongzi.* to [e-mail protected] ' 172.16.2.% ' identified by ' 123456 ';

4) Modify the configuration file

[[email protected] conf]# vim /application/mycat/conf/server.xml  #MyCAT对外的 "Virtual database" Config file S ">32</property> -->        </system>         <user name= "web" >  #web为主库和分库刚建立的用户                  <property name= "Password ">123456</property>  #用户密码                  <property name= "schemas" >lilongzi</property>  #数据库名称          </user>        < User name= "Web_r" >  #web_r表示只给读权限                  <property name= "Password" >123456</property>                 <property name= "Schemas" >lilongzi</ property>                 <property name= "ReadOnly" >true</property>        < /user>

[[email protected] conf]# vim /application/mycat/conf/server.xml  #详细主库及读写分离模式配置文件         <schema name= "Lilongzi"  checkSQLschema= "false"  sqlmaxlimit= "         datanode=" "DN1" >          #name = your database name  , add datanode= "DN1"   means database only on DN1, no library          <table name= "Test"  primarykey= "ID"  type= "global"   Datanode= "Dn1,dn2,dn3"  />         #table修改为你的表名, if there is more than one table, Can be added in this format        </schema>         <!-- <datanode name= "dn1$0-743"  datahost= "Localhost1"  database= "db$0-743"                  />  -->     &nBsp;  <datanode name= "DN1"  datahost= "Localhost1"  database= "Lilongzi"  />         <datanode name= "DN2"  datahost= "Localhost1"   Database= "Lilongzi"  />        <datanode name= "DN3"  datahost= "Localhost1"  database= "Lilongzi"  />         <datahost name= "Localhost1"  maxcon= " mincon="  balance= "1"                  writetype= "0"  dbType= "MySQL"  dbdriver= "native"  switchtype= "1"                  slavethreshold= ">        "         
In this, there are two parameters to note, balance and switchtype.
Among them, balance refers to the load balancer type, the current value has 4 kinds:
1. balance= "0", does not open the read and write separation mechanism, all read operations are sent to the currently available writehost.
2. Balance= "1", all readhost and stand by writehost participate in the load balancing of the SELECT statement, simply speaking, when the dual master dual slave mode (M1->S1,M2->S2, and M1 and M2 are mainly prepared), under normal circumstances, M2,S1,S2 all participate in load balancing of the SELECT statement.
3. Balance= "2", all read operations are randomly distributed on writehost, Readhost.
4. Balance= "3", all read requests randomly distributed to wiriterhost corresponding readhost execution, Writerhost does not bear reading pressure
Writetype indicates write mode
Writetype= "0", all operations sent to the first writehost of the configuration
Writetype= "1", randomly sent to all configured Writehost
Writetype= "2", do not perform write operation
Switchtype refers to the mode of switching, the current value also has 4 kinds:
1. switchtype= '-1 ' means no automatic switching
2. switchtype= ' 1 ' default value, indicating automatic switching
3. Switchtype= ' 2 ' is based on the state of MySQL master-slave synchronization to decide whether to switch, heartbeat statement for show slave status
4. Switchtype= ' 3 ' based on the switch mechanism of MySQL galary cluster (for cluster) (1.4.1), the heartbeat statement is show status like ' wsrep% '.

5) Start Mycat
[[Email protected] bin]# ./mycat console &running mycat-server...wrapper   | --> wrapper started as consolewrapper  | launching  a jvm...jvm 1    | java hotspot (TM)  64-Bit Server  vm warning: ignoring option maxpermsize=64m; support was removed  in 8.0jvm 1    | Wrapper  (version 3.2.3)  http:// wrapper.tanukisoftware.orgjvm 1    |   copyright 1999-2006  tanuki software, inc.  all rights reserved.jvm 1    |  jvm 1    | log4j 2016-07-09 06:04:28 [./conf/log4j.xml]  load completed.jvm 1    | MyCAT Server startup  Successfully. see logs in lOgs/mycat.log[[email protected] bin]# lsof -i:{8066,9066}command   pid  USER   FD   TYPE DEVICE SIZE/OFF NODE NAMEjava     87340 root   46u  IPv6 212352       0t0  TCP *:9066  (LISTEN)   #虚拟schema管理端口java     87340  root   50u  IPv6 212354      0t0   tcp *:8066  (LISTEN)   #虚拟schema登陆端口 # to see this, our Mycat has started.

6) Next we need to verify that the read/write separation has been realized ...
Method:
Stop the SQL thread from the library and let the library read the Binlog of the main library, but cannot initiate the SQL thread to write to its own database, so that we can simulate access, insert a piece of data, in the main library should be able to see the newly inserted data, but the client of the simulated access does not see, the read and write separation is successful.
We simulate Web user access on other host A to log in to view data
[[email protected] ~]# mysql -uweb -p123456 -h172.16.2.10 -p8066 # Note that the login port of the virtual schema is used here 8066mysql> show databases;+----------+| database |+----------+|  lilongzi |+----------+1 row in set  (0.00 sec) mysql> use  lilongzi;database changedmysql> show tables;+--------------------+| tables in  lilongzi |+--------------------+| customer            | |  customer_addr      | |  employee           | |  goods              | |  hotnews            | |  orders             | |  order_items        | |  test               | # Test for our changed table | travelrecord       |+--------------------+9 rows  in set  (0.00 sec) mysql> select * from test;+----+--------+|  id | name   |+----+--------+|  1 |  Xiao Ming    |+----+--- -----+1 row in set  (0.00&NBSP;SEC)
We stopped the SQL thread from library 3307
mysql> stop slave Sql_thread; Query OK, 0 rows Affected (0.00 sec)
Remotely inserting a data test on host a
mysql> INSERT INTO test values (2, ' test2 '); ERROR 1105 (HY000): Duplicate entry ' 2 ' for key ' PRIMARY ' mysql> select * from test;+----+--------+| ID |  Name |+----+--------+| 1 | Xiao Ming | #立马查一下发现是看不到的 +----+--------+1 row in Set (0.01 sec)
But above the main library,
Mysql> SELECT * from test;+----+--------+| ID |  Name |+----+--------+| 1 |  Xiao Ming | | 2 | Test2 | #显示已经写入成功 +----+--------+2 rows in Set (0.00 sec)
From the library side.
Mysql> SELECT * from test;+----+--------+| ID |  Name |+----+--------+| 1 | Xiao Ming | #同样看不到 +----+--------+1 row in Set (0.00 sec)

Indicates a successful read-write separation.

This article comes from the "change from every day" blog, so be sure to keep this source http://lilongzi.blog.51cto.com/5519072/1828768
MYCAT realization of database read/write separation