mycat读写分离介绍

原创

xiang_acrhe 2018-09-06 10:03:45 博主文章分类：linux服务部署维护 ©著作权

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

安装mycat实现mysql负载均衡

事先准备好两台及以上数据库服务器 mycat支持特性如（官网mycat.io，以下是官网介绍的支持特性）: SQL92标准，支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群。支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster 基于Nio实现，有效管理线程，解决高并发问题。数据的多片自动路由与聚合，sum,count,max等常用的聚合函数,支持跨库分页。单库内部任意join，跨库2表join，甚至基于caltlet的多表join。通过全局表，ER关系的分片策略，实现了高效的多表join查询。多租户方案。分布式事务（弱xa）。XA分布式事务（1.6.5）。全局序列号，解决分布式下的主键生成问题。分片规则丰富，插件化开发，易于扩展。强大的web，命令行监控。前端作为MySQL通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉。密码加密，服务降级，IP白名单，SQL黑名单、sql注入×××拦截 prepare预编译指令（1.6），非堆内存(Direct Memory)聚合计算（1.6） PostgreSQL的native协议（1.6）。mysql和oracle存储过程，out参数、多结果集返回（1.6）。zookeeper协调主从切换、zk序列、配置zk化（1.6）。库内分表（1.6），集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理(2.0)

事先准备两台数据库

安装好jdk的环境，mycat启动需要依赖jdk运行，jdk下载后解压到相应目录下 jdk变量如下示例，可根据jdk版本修改变量路径，将PATH变量路径写入/etc/profile文件中

JAVA_HOME=/usr/local/jdk1.8
JAVA_BIN=/usr/local/jdk1.8/bin
JRE_HOME=/usr/local/jdk1.8/jre
PATH=$PATH:/usr/local/jdk1.8/bin:/usr/local/jdk1.8/jre/bin
CLASSPATH=/usr/local/jdk1.8/jre/lib:/usr/local/jdk1.8/lib:/usr/local/jdk1.8/jre/lib/charsets.jar

下载mycat安装包，解压后直接移动到/usr/local/目录下

[root@www src]# tar zxf Mycat-server-1.6.6.1-test-20180709095126-linux.tar.gz -C .
[root@www src]# mv mycat/ /usr/local/

创建mycat运行的用户和组，并把mycat的目录给予mycat用户所属主所属组权限

[root@www src]# groupadd mycat
[root@www src]# useradd -r -g mycat mycat
[root@www src]# chown -R mycat:mycat /usr/local/mycat/

mycat作为中间件并不存储任何数据，后端链接mysql物理服务器，schem.xml用来链接后端的mysql服务器的(文件可配置分库分表策略，分片节点也在其中)

修改mycat配置文件/usr/local/mycat/conf/schema.xml 在配置文件的vriteHost标签中配置mysql的地址、用户名和密码 <readHost host=>中配置的是从节点的数据库连接用户和密码，我们现在先只在writeHost中配置(这台服务器安装了mysql) 所有读操作分配到readhost上、负载均衡发送到第一个writehost，第一个无法提供服务后会分发到第二个writehost上、

[root@www mycat]# vim conf/schema.xml
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
         writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100"                                     maxRetryCount="4">
     <heartbeat>select user()</heartbeat>

<writeHost host="hostM1" url="localhost:3306" user="root"
                                   password="pwd@123">
                   <!-- can have multi read hosts -->
                       <readHost host="hostS2" url="192.168.1.223:3306" user="root" password="pwd@123" />
                       <readHost host="hostS3" url="192.168.1.220:3306" user="slave" password="pwd@123" />
              
    </writeHost>
</dataHost>

schema.xml中配置负载均衡需要注意的地方此处有三点需要注意：

balance=”1”，writeType=”0” ,switchType=”1” 
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 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有， 1.3 没有。

writeType属性，负载均衡类型，配置项有3类

writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
writeType="1"，所有写操作都随机的发送到配置的 writeHost。
writeType="2"，没实现

switchType属性

-1表示不切换
1默认值，自动切换
2基于mysql，主从同步状态决定是否切换
3基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'

mycat支持双主多从，可以配置两个writeHost节点，多个readHost节点

配置mycat的用户密码，在文件中找到user name标签中配置mycat的用户名和密码，保存 server.xml跟读写分离策略关系不大，但是需要使用此文件来配置连接mycat用户的权限，这里简单配置用户和密码第一个user是指定主数据库的登录写入用户名和密码第二个user指定从数据库的读取用户名和密码(可能不需要指定)

  <user name="repl" defaultAccount="true">
                <property name="password">pwd@123</property>
                <property name="schemas">TESTDB</property>

                <!-- 表级 DML 权限设置 -->
                <!--            
                <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="slave">
                <property name="password">pwd@123</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>

启动mycat服务 /usr/locar/mycat/bin/mycat start 查看mycat启动监听的端口，如果监听到8066端口，说明mycat启动成功

[root@www local]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@www local]# netstat -ntlp |grep 8066
tcp6 0 0 :::8066 :::* LISTEN 25766/java 
连接mycat，验证是否能够通过mycat去访问mysql数据库
[root@www mycat]# mysql -uroot -ppwd@123 -h127.0.0.1 -P8066 -DTESTDB
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-test-20180709095126 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, 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 |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)

mysql>

mycat服务验证

mycat的主从延时切换技术 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时则表示主从同步，可以安全切换，否则不会切换。

登入mycat的操作端 mysql -uroot -ppwd@123 -h127.0.0.1 -P8066 -DTESTDB 创建测试用的表这里报错Invalid DataSource:1。修改server.xml文件，指定主数据库写入的用户名和密码

mysql -uroot -ppwd@123 -h127.0.0.1 -P8066 -DTESTDB
mysql> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:1

报错** Invalid DataSource:0 ** 需要创建schema.xml中的一个配置里的三个数据库，是没有这三个库造成的，退出mycat在mysql终端里进行新建库操作

mysql> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0

创建mycat所需的三个库(schema.xml配置文件中指定的库)

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> create database db2;
Query OK, 1 row affected (0.01 sec)

mysql> create database db3;
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

在mysql主库里创建好三个库后，登入mycat，执行新建表操作，可见成功创建一个新表

Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.6.1-test-20180709095126 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, 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> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.12 sec)

mycat读写分离验证

mycat无法与mysql读写问题解决完成后上述创建好了一个表后，进行sql查询，中间件服务mycat建议不要和master数据库再一个服务器上，验证master停止mysql服务后，mycat无法提供正常的sql读取操作 在所有从服务器上创建master授权访问的用户，用于master上的mycat查询数据使用，授权对象是192.168.1.234，有多个从的话都需要授权192.168.1.234访问

mysql> grant all on *.* to 'slave'@192.168.1.234 identified by 'pwd@123' ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit
Bye

修改mycat的schema.xml配置文件，在readhost中指定从的授权用户

<writeHost host="hostM1" url="localhost:3306" user="root"
                                   password="pwd@123">
                     <!-- can have multi read hosts -->
                         <readHost host="hostS2" url="192.168.1.223:3306" user="root" password="pwd@123" />
                         <readHost host="hostS3" url="192.168.1.220:3306" user="slave" password="pwd@123" />
                </writeHost>

在其中一个slave从上创建一个master主上没有的数据，真实环境部署不建议这么操作首先查看master主上的travelrecord的表内容，确认和slave从上数据不一致

mysql> use db2;


Database changed
mysql> select * from travelrecord;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 5000001 | aaa.com | 2016-01-02 | 100  | 10   |
+---------+---------+------------+------+------+
row in set (0.00 sec)

mysql> use db1;
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> select * from travelrecord;
+------+---------+------------+------+------+
| id   | user_id | traveldate | fee  | days |
+------+---------+------------+------+------+
| 1    | aaa.com | 2016-01-01 | 100  | 10   |
| 2233 | aaa.com | 2016-01-02 | 100  | 10   |
+------+---------+------------+------+------+
rows in set (0.00 sec)

在slave中插入一条master上没有的数据，查看slave从上的表内容

mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(23333,@@hostname,20160102,100,10);
Query OK, 1 row affected (0.02 sec)

mysql> use db1;
Database changed
mysql> select * from travelrecord;
+-------+--------------+------------+------+------+
| id    | user_id      | traveldate | fee  | days |
+-------+--------------+------------+------+------+
| 1     | aaa.com      | 2016-01-01 | 100  | 10   |
| 2233  | aaa.com      | 2016-01-02 | 100  | 10   |
| 23333 | www.abcd.com | 2016-01-02 | 100  | 10   |
+-------+--------------+------------+------+------+
3 rows in set (0.00 sec)

mysql> use db2;
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> select * from travelrecord;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 5000001 | aaa.com | 2016-01-02 | 100  | 10   |
+---------+---------+------------+------+------+
1 row in set (0.00 sec)

这里注意master中没有www.abcd.com这条数据，如果执行查询操作，master不会给出这个查询的结果 在master上登入mycat，多次查询数据

mysql> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
| 5000001 | aaa.com      | 2016-01-02 | 100  | 10   |
| 1       | aaa.com      | 2016-01-01 | 100  | 10   |
| 2233    | aaa.com      | 2016-01-02 | 100  | 10   |
| 23333   | www.abcd.com | 2016-01-02 | 100  | 10   |
+---------+--------------+------------+------+------+
4 rows in set (0.00 sec)

mysql> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
| 5000001 | aaa.com      | 2016-01-02 | 100  | 10   |
| 1       | aaa.com      | 2016-01-01 | 100  | 10   |
| 2233    | aaa.com      | 2016-01-02 | 100  | 10   |
| 23333   | www.abcd.com | 2016-01-02 | 100  | 10   |
+---------+--------------+------------+------+------+
4 rows in set (0.00 sec)

mysql> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
| 1       | aaa.com      | 2016-01-01 | 100  | 10   |
| 2233    | aaa.com      | 2016-01-02 | 100  | 10   |
| 23333   | www.abcd.com | 2016-01-02 | 100  | 10   |
| 5000001 | aaa.com      | 2016-01-02 | 100  | 10   |
+---------+--------------+------------+------+------+
4 rows in set (0.00 sec)

可以从mycat的查询结果中得到显而易见的结果，读数据会从slave上读取