Mysql+mycat for read-write separation

Centos7

Master	slave	mycat1.6	Client
192.168.41.10	192.168.41.11	192.168.41.12	192.168.41.13

Lab environment shuts down its own firewall
To configure all hosts Hosts file:

1, Master (41.10):
Vim/etc/my.cnf

Systemctl Restart mysqld \ Restart Service read configuration file parameters
1) Configure the replication user and root user rights

2, slave (41.11)
Configuring the/ETC/MY.CNF configuration file

Systemctl Restart Mysqld
mysql> Change Master to master_host= ' 192.168.41.10 ', master_user= ' myslave ', master_password= ' 123.com ', Master_log_ File= ' mysql-bin.000002 ', master_log_pos=1334;
mysql> start slave;
Mysql> show Slave status\g;
Slave_io_running:yes
Slave_sql_running:yes
Mysql> Grant All on.to [email protected] '% ' identified by ' 123.com ';
mysql> flush Privileges;
Back to master host Create test library
mysql> CREATE DATABASE test;
3. Configuration Mycat (41.12)
1) Deploy Mycat
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[Email protected] ~]# tar zxf mycat-server-1.6-release-20161028204710-linux.tar.gz-c/usr/local
2) Load Environment variables
[Email protected] ~]# Vi/etc/profile
.....
Export path= $PATH:/usr/local/java/bin
Export Java_home=/usr/local/java
Export Mycat_home=/usr/local/mycat
Export path= $PATH:/usr/local/mycat/bin
[Email protected] ~]# Source/etc/profile
3) configuration Serve.xml
[Email protected] ~]# cd/usr/local/mycat/conf/
[Email protected] conf]# vim Server.xml

......
<user name= "root" >
<property name= "Password" >123.com</property>
<property name= "Schemas" >test</property>

  <!            --table-level DML permission settings--> <!                    --<privileges check= "false" >                            <schema name= "TESTDB" dml= "0110" >                            <table name= "tb01" dml= "0000" ></table>                    <table name= "tb02" dml= "1111" ></table>            </schema>                        </privileges>    -->    </user>            <user name= "User" >            <property name= "Password" >user</property>            <property name= "Schemas" >test</property>    <property name= "ReadOnly" >true</property> </user>  

</mycat:server>
4) Configuration Schema.xml
[Email protected] conf]# vim Schema.xml
<?xml version= "1.0"?>
<! DOCTYPE mycat:schema SYSTEM "SCHEMA.DTD" >
<mycat:schema xmlns:mycat= "http://io.mycat/" >

    <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">    </schema>    <dataNode name="dn1" dataHost="host01" database="test" />    <dataHost name="host01" maxCon="1000" minCon="10" balance="1"                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">            

  balance? Property Load balancer Type, currently has 4 kinds of values:  

balance= "0", does not open the read-write separation mechanism, all read operations are sent to the currently available Writehost On 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 , the M2,S1,S2 participates in load balancing of the SELECT statement. Balance= "2", all read operations are randomly distributed on the Writehost, Readhost. Balance= "3", all read requests are randomly distributed to wiriterhost corresponding readhost execution, Writerhost does not burden reading pressure, note balance=3 only in 1.4 and later versions have, 1.3 not.

Writetype property, Load balancer type, currently has 3 kinds of values:
writetype= "0", all writes sent to the first writehost of the configuration, the first one hangs to the second writehost to survive, After the reboot has been switched, the switchover is recorded in the configuration file: Dnindex.properties. Writetype= "1", all write operations are sent randomly to the configured writehost. Writetype= "2", not realized.

Switchtype? Property
-1 to not automatically switch 1 default, automatically switch 2 based on the status of MySQL master-slave synchronization to determine whether to switch

5) Start Mycat server
[[email protected] logs]# Mycat start
[[email protected] logs]# TAILF wrapper.log #观察启动日志, easy troubleshooting
[Email protected] logs]# SS-ANPT | grep java
LISTEN 0 1 127.0.0.1:32000 : Users: (("Java", pid=40133,fd=4))
LISTEN 0::: 50632::: Users: (("Java", pid=40133,fd=51))
LISTEN 0::: 9066::: Users: (("Java", pid=40133,fd=69))
LISTEN 0::: 33782::: Users: (("Java", pid=40133,fd=53))
LISTEN 0::: 1984:: : Users: (("Java", pid=40133,fd=52))
LISTEN 0::: 8066:::* Users: (("Java", pid=40133,fd=73))

4, Client (41.13) verify read/write separation

   ①登录到连接端口

[Email protected] ~]# mysql-h 192.168.41.12-p 8066-uroot-p123.com
mysql> show databases;
+----------+
| DATABASE |
+----------+
| Test |
+----------+
1 row in Set (0.01 sec)

mysql> Use test
Database changed
Mysql> CREATE TABLE TB (id int);
Query OK, 0 rows affected (0.03 sec)

Mysql> INSERT into TB values (1);
Query OK, 1 row affected (0.05 sec)

Mysql> SELECT * from TB;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.02 sec)
② Login Management Port
[Email protected] ~]# mysql-p9066-uroot-p123.com-h 192.168.41.12
Mysql> Show @ @datasource;
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+-------- ----+
| DATANODE | NAME | TYPE | HOST | PORT | w/r | ACTIVE | IDLE | SIZE | EXECUTE | Read_load | Write_load |
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+-------- ----+
| DN1 | hostM1 | MySQL | 192.168.1.12 | 3306 | W | 0 | 10 | 1000 | 49 | 0 | 2 |
| DN1 | hostS1 | MySQL | 192.168.1.10 | 3306 | R | 0 | 6 | 1000 | 44 | 2 | 0 |
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+-------- ----+
Observing two host load changes from the management port, it is found that the Read_load is 2 in hostS1 and the Write_load is 2 in hostM1, which indicates that the read/write separation has been realized

Mysql+mycat for read-write separation