Mysql 读写分离

配置Java环境

tar xf jdk-8u161-linux-x64.tar.gz -C /usr/local/
mv /usr/local/{jdk1.8.0_161,jdk}
[root@master ~]# vim /etc/profile.d/jdk.sh
 
export JAVA_HOME=/usr/local/jdk
 
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
 
export PATH=$JAVA_HOME/bin:$PATH
[root@master ~]# exec bash
[root@master ~]# java -version
java version "1.8.0_161"
Java(TM) SE Runtime Environment (build 1.8.0_161-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.161-b12, mixed mode)

　　下载mycat地址：https://github.com/MyCATApache/Mycat-download/blob/master/1.5-RELEASE/Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz

1 2	`[root@master ~]# tar xf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz` `[root@master ~]# mv mycat /usr/local/`

　　配置环境变量

[root@master ~]# cat /etc/profile.d/mycat.sh 
MYCAT_HOME=/usr/local/mycat
PATH=$MYCAT_HOME/bin:$PATH
[root@master ~]# exec bash

　配置mycat(mycat的用户账号和授权信息是在conf/server.xml文件中配置

<!--配置文件里面的账号密码是给前端的，告诉前端能用那些账号连接数据库。也就是图上的mysql-cilent。-->
<!--此处需要注意的是：是在34行到43行修改文件。(千万不要全部删除替换)-->
</system>
<user name="root">    <!--这个账号就是用来给前端登录mycat的账号的。定义一下->
        <property name="password">123456</property>
        <property name="schemas">test</property> <!--定义操作的那个数据库->
</user>
      
<user name="user">
        <property name="password">user</property>
        <property name="schemas">test</property>
        <property name="readOnly">true</property> <!--只读->
</user>

编辑MyCAT的配置文件schema.xml，关于dataHost的配置信息如下：

vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
        <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
        </schema>
        <dataNode name="dn1" dataHost="dthost" database="test"/>
        <dataHost name="dthost" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="A76" url="192.168.19.76:3306" user="mycat" password="123456">
        </writeHost>
        <writeHost host="A77" url="192.168.19.77:3306" user="mycat" password="123456" />
        </dataHost>
</mycat:schema>

有两个参数需要注意，balance和 switchType。

其中，balance指的负载均衡类型，目前的取值有4种：

balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上

balance="1"：全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡

balance="2"：所有读操作都随机的在writeHost、readhost上分发。

balance="3":所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

switchType指的是切换的模式，目前的取值也有4种

switchType='-1':表示不自动切换

switchType='1':默认值，表示自动切换

switchType='2':基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status

switchType='3':基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'

MySQL主从提前配置好的；两台数据创建用户

mysql> grant all on *.* to mycat@'192.168.10.16' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql>  flush privileges;
Query OK, 0 rows affected (0.02 sec)

　　启动Mycat并查看端口：

[root@master conf]# mycat start
Starting Mycat-server...
[root@master conf]# cat /usr/local/mycat/logs/wrapper.log 
STATUS | wrapper  | 2020/04/16 09:36:55 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2020/04/16 09:36:55 | Launching a JVM...
INFO   | jvm 1    | 2020/04/16 09:36:55 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO   | jvm 1    | 2020/04/16 09:36:55 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2020/04/16 09:36:55 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2020/04/16 09:36:55 | 
INFO   | jvm 1    | 2020/04/16 09:36:56 | log4j 2020-04-16 09:36:56 [./conf/log4j.xml] load completed.
INFO   | jvm 1    | 2020/04/16 09:36:58 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@master conf]# ss -lntp | grep 9066
LISTEN     0      100         :::9066                    :::*                   users:(("java",pid=9343,fd=60))

　　测试

[root@master ~]# mysql -uroot -p123456 -h192.168.10.16 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+----------+
| DATABASE |
+----------+
| test     |
+----------+
1 row in set (0.01 sec)
 
mysql> select * from test.emp;
+-------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+-------+-------+-------+------+---------------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
+-------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (42.59 sec)

　　在mycat打开日志debug功能；并重启

[root@master conf]# vim log4j.xml 
 
 - you may not use 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 law or agreed to in writing, software
 - distributed under the License is distributed on an "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 log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="ConsoleAppender" class="org.apache.log4j.ConsoleAppender">
  <layout class="org.apache.log4j.PatternLayout">
   <param name="ConversionPattern" value="%d{MM-dd HH:mm:ss.SSS}  %5p [%t] (%F:%L) -%m%n" />
  </layout>
 </appender>
  <appender name="FILE" class="org.apache.log4j.RollingFileAppender">
    <param name="file" value="${MYCAT_HOME}/logs/mycat.log" />
    <param name="Append" value="false"/>
    <param name="MaxFileSize" value="1000KB"/>
    <param name="MaxBackupIndex" value="10"/>
    <param name="encoding" value="UTF-8" />
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d{MM/dd HH:mm:ss.SSS}  %5p [%t] (%F:%L) -%m%n" />
    </layout>
  </appender>
 
 
  <root>
    <level value="debug" />
"log4j.xml" 42L, 1649C                
[root@master conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

　　查询

mysql> select * from test.emp;
+-------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+-------+-------+-------+------+---------------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
+-------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.04 sec)
日志
04/16 10:00:36.873  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=11, lastTime=1587002436867, user=mycat, schema=test, old shema=test, borrow
ed=true, fromSlaveDB=false, threadId=11, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{select * from test.emp}, respHandler=SingleNodeHandler [node=dn1{select * from test.emp}, packetId=11], host=192.168.10.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]04/16 10:00:36.873  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1587002436867, user=mycat, schema=test, old shema=test, borrowed=
true, fromSlaveDB=false, threadId=11, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

　　插入数据

mysql> insert into test.emp values (7491, 'ALL', 'SALE', 7678, '1981-02-20', 1698, 308, 37);
Query OK, 1 row affected (0.12 sec)
 
04/16 10:04:15.321  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=null, host=192.168.10.17, user=root,txIsolation=3, autocommit=true, schema=null]ins
ert into test.emp values (7491, 'ALL', 'SALE', 7678, '1981-02-20', 1698, 308, 37), route={   1 -> dn1{insert into test.emp values (7491, 'ALL', 'SALE', 7678, '1981-02-20', 1698, 308, 37)}