MyCat实现MySQL一主两从读写分离

原创

那海蓝蓝 2021-04-10 10:06:25 博主文章分类：MyCat实战 ©著作权

文章标签 java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者那海蓝蓝的原创作品，请联系作者获取转载授权，否则将追究法律责任

环境搭建（一主两从）：

macat： 192.168.8.30

master：192.168.8.31

slave1： 192.168.8.32

slave2： 192.168.8.33

工具包：

java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64

Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

一、安装JDK和MyCat

系统已安装JDK，查看安装路径

1

2

3

4

[root@mycat ~]# java -version

openjdk version "1.8.0_161"

OpenJDK Runtime Environment (build 1.8.0_161-b14)

OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode)

1 2	`[root@mycat ~]# ls -l /usr/bin/java` `lrwxrwxrwx. 1 root root 22 Oct 22 10:30` `/usr/bin/java` `->` `/etc/alternatives/java`

1 2	`[root@mycat ~]# ls -l /etc/alternatives/java` `lrwxrwxrwx. 1 root root 71 Oct 22 10:30` `/etc/alternatives/java` `->` `/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64/jre/bin/java`

安装MyCat

1

2

3

cd /software

tar zxvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

cp -r mycat /usr/local/

配置环境变量

[root@mycat ~]# cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64

export JRE_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64/jre

export MYCAT_HOME=/usr/local/mycat

PATH=$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin:/usr/bin:/sbin:/bin:$MYCAT_HOME/bin:$JAVA_HOME/bin

export PATH

二、创建测试库

master创建，slave同步

1

2

3

4

5

6

create database testdb1;

create table testdb1.t11(name1 varchar(40),name2 varchar(40),name3 varchar(40));

create database testdb2;

create table testdb2.t21(name1 varchar(40),name2 varchar(40),name3 varchar(40));

create database testdb3;

create table testdb3.t31(name1 varchar(40),name2 varchar(40),name3 varchar(40));

三、配置schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="

<schema name="mycatdb1" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>

<schema name="mycatdb2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"></schema>

<schema name="mycatdb3" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn3"></schema>

<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"

/> -->

<dataNode name="dn1" dataHost="localhost1" database="testdb1" />

<dataNode name="dn2" dataHost="localhost1" database="testdb2" />

<dataNode name="dn3" dataHost="localhost1" database="testdb3" />

<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />

<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />

<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />

<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="192.168.8.31" url="192.168.8.31:3306" user="root" password="mysql">

<readHost host="192.168.8.32" url="192.168.8.32:3306" user="root" password="mysql" />

<readHost host="192.168.8.33" url="192.168.8.33:3306" user="root" password="mysql" />

</writeHost>

<writeHost host="192.168.8.32" url="192.168.8.32:3306" user="root" password="mysql" />

</dataHost>

<!--

<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">

<heartbeat> </heartbeat>

<writeHost host="hostM1" url="sequoiadb://:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost>

</dataHost>

<dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>

<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>

<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost>

<dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

<dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">

<heartbeat> </heartbeat>

<writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </dataHost> -->

<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"

dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"

url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>

</dataHost> -->

</mycat:schema>

schema标签：schema name属性指定逻辑库名，dataNode属性指定下边的dataNode

其中 checkSQLschema 表明是否检查并过滤 SQL 中包含schema的情况，如逻辑库为mycatdb1，则可能写为 select * from mycatdb1.t11，此时会自动过滤 mycatdb1，SQL变为select * from t11，若不会出现上述写法，则可以关闭属性为false。

sqlMaxLimit默认返回的最大记录数限制，MyCat1.4 版本里面，用户的Limit参数会覆盖掉MyCat的sqlMaxLimit默认设置

dataNode标签：dataHost指定下边的dataHost，database指定具体的database
dataHost标签：balance指的负载均衡类型，switchType指的是切换的模式

schema中的每一个dataHost中的host属性值必须唯一，否则会出现主从在所有dataHost中全部切换的现象

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

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

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

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

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

因此，该配置文件中的 balance="1"意味着作为stand by writeHost的hostS1和hostS2将参与select语句的负载均衡，这就实现了主从的读写分离

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

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

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

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

MyCat1.4 开始支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

MyCat心跳检查语句配置为 show slave status，dataHost上定义两个新属性：switchType="2"与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制，MyCat 心跳机制通过检测show slave status 中的"Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running"

三个字段来确定当前主从同步的状态以及 Seconds_Behind_Master 主从复制时延，当 Seconds_Behind_Master 大于 slaveThreshold 时，读写分离筛选器会过滤掉此Slave机器，防止读到很久之前的旧数据，而当主节点宕机后，切换逻辑会检查Slave上的Seconds_Behind_Master 是否为 0，为 0 时则表示主从同步，可以安全切换，否则不会切换。

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

四、配置server.xml

<user name="root" defaultAccount="true">

<property name="password">mysql</property>

<property name="schemas">mycatdb1,mycatdb2,mycatdb3</property>

<!--

<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">mycatdb1,mycatdb2,mycatdb3</property>

<property name="readOnly">true</property>

</user>

五、修改MyCat日志级别

vi log4j2.xml

修改<asyncRoot level="info" includeLocation="true">

为 <asyncRoot level="debug" includeLocation="true">

六、启动mycat

1 2	`[root@mycat conf]# /usr/local/mycat/bin/mycat start` `Starting Mycat-server...`

查看日志

1

2

3

4

5

6

7

STATUS | wrapper | 2018/11/21 11:08:50 | --> Wrapper Started as Daemon

STATUS | wrapper | 2018/11/21 11:08:51 | Launching a JVM...

INFO | jvm 1 | 2018/11/21 11:08:51 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0

INFO | jvm 1 | 2018/11/21 11:08:54 | Wrapper (Version 3.2.3)

INFO | jvm 1 | 2018/11/21 11:08:54 |

INFO | jvm 1 | 2018/11/21 11:08:58 | MyCAT Server startup successfully. see logs in logs/mycat.log

查看端口

1 2	`[root@mycat conf]# netstat -an\|grep 8066` `tcp6 0 0 :::8066`

查看mycat进程

1

2

3

4

[root@mycat conf]# ps -ef|grep mycat

avahi 646 1 0 09:56 ? 00:00:03 avahi-daemon: running [mycat.local]

root 2653 1 0 11:08 ? 00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/myca

root      2655  2653  7 11:08 ?        00:00:07 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=

false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/asm-4.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.6.jar:lib/curator-client-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/curator-recipes-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/fastjson-1.2.12.jar:lib/guava-19.0.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/jline-0.9.94.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.so:lib/log4j-1.2-api-2.5.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.5.jar:lib/log4j-core-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/Mycat-server-1.6.6.1-release.jar:lib/mysql-binlog-connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.7.0.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3

.4.6.jar -Dwrapper.key=G87oQ7EZv67RXK9D -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=2653 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start

七、登录mysql，查看逻辑库

[root@mycat ~]# mysql -uroot -pmysql -P8066 -h192.168.8.30

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.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB)

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 |

+----------+

| mycatdb1 |

| mycatdb2 |

| mycatdb3 |

+----------+

3 rows in set (0.01 sec)

mysql> use mycatdb1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+-------------------+

| Tables_in_testdb1 |

+-------------------+

| t11 |

+-------------------+

1 row in set (0.04 sec)

mysql> select * from t11;

Empty set (0.22 sec)

八、读写分离验证

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave1 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave1 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave1 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave2 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave1 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave2 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave2 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave2 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave1 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave2 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave1 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave1 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave2 |

+------------+

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+

| @@hostname |

+------------+

| slave2 |

可以看到读操作全部在slave1和slave2

注意：这里的负载均衡并不是在slave1和slave2上边轮询，而是总体保持负载均衡。

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select * from mycatdb1.t11"

mysql: [Warning] Using a password on the command line interface can be insecure.

+--------+--------+--------+

| name1 | name2 | name3 |

+--------+--------+--------+

| master | master | master |

+--------+--------+--------+

九、主从切换测试

关闭master上的MySQL进程

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1184 (HY000) at line 1: .ConnectException: Connection refused

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1184 (HY000) at line 1: .ConnectException: Connection refused

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]

# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "insert into mycatdb1.t11 values(@@hostname,@@hostname,@@hostname)"mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mycat conf]# mysql -uroot -pmysql -P8066 -h192.168.8.30 -e "select * from mycatdb1.t11"

mysql: [Warning] Using a password on the command line interface can be insecure.

+--------+--------+--------+

| name1 | name2 | name3 |

+--------+--------+--------+

| master | master | master |

| slave1 | slave1 | slave1 |

+--------+--------+--------+

短暂的连接失败之后，写操作连进slave1，主从切换成功。