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)
[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)}
草都可以从石头缝隙中长出来更可况你呢
