MyCAT读写分离与MySQL主从同步

很多开发框架在框架底层就引入了读写分离的机制，面对一些没有底层支持读写分离的框架，如何实现读写分离呢？

Mycat

一个开源的高性能的数据库中间件产品，支持读写分离，支持MySQL主从，数据切分以及galera cluster集群。

它是一个开源的分布式数据库系统，是一个实现了 MySQL 协议的的

Server，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用

MySQL 原生（Native）协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，

其核心功能是分表分库，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。

安装指南

1. 安装JDK (略)
2. 安装MySQL (略)
3. 安装MyCAT
4. http://dl.mycat.io/1.6-RELEASE/ 目前最新的稳定版本为1.6
5. 下载指定系统的压缩包，解压以后即可使用。免安装。

使用指南

配置

1. server.xml 系统配置文件

<user name="root">

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

<property name="schemas">dsy</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>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

user标签用来添加用户，管理用户权限。

1. schema.xml 管理逻辑库和逻辑表的配置文件

<schema name="dsy" checkSQLschema="false" sqlMaxLimit="100">

<!-- auto sharding by id (long) -->

<table name="xn_user_info" dataNode="dn1"/>

</schema>

• 1
• 2
• 3
• 4

解释：schema标签定义逻辑库的名字，table定义逻辑表。dataNode属性表示数据节点也就是数据分片。如果数据没有分片，通常只有一个就行了。

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

• 1

解释：创建一个名字叫做dn1的数据节点，该节点使用名字为localhost1数据库实例上的dsy这个库组成数据分片。

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

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

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost host="hostM1" url="localhost:3306" user="root"

password="root">

<!-- can have multi read hosts -->

<!-- <readHost host="hostS2" url="localhost:3306" user="root" password="root" /> -->

<!-- <readHost host="hostS2" url="192.168.0.164:3306" user="root" password="root" /> -->

</writeHost>

<writeHost host="hostS1" url="192.168.0.164:3306" user="root"

password="root" />

<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->

</dataHost>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

解释：创建一个名为localhost1的数据库实例。连接池最大链接数为1000。初始化的连接池数为10。dbType标识使用mysql的二进制协议。 dbDriver使用native表示可以使用mysql和mariadb，可选值还有JDBC驱动。switchType=1表示自动切换。如果switchType设置为2并且slaveThreshold=”100，维持心跳的语句改为show slave status，意味着开启MySQL主从复制状态绑定的读写分离与切换机制。

heartbeat是采用select user这条语句来维持心跳。

writehost是用来定义写实例。在一个 dataHost 内可以定义多个 writeHost 和 readHost。但是，如果 writeHost 指定的后端数据库宕机，那么这个 writeHost 绑定的所有 readHost 都将不可用。另一方面，由于这个 writeHost 宕机系统会自动的检测到，并切换到备用的 writeHost 上去。

balance解释0不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。1全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。2所有读操作都随机的在 writeHost、readhost 上分发。3所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。writeType负载均衡类型解释0所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个 writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .1所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。switchType切换解释-1表示不自动切换1表示自动切换2基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status3基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like ‘wsrep%’。

读写分离配置

上述dataHost配置里，两个平级的writeHost节点。是一种方案。

或者在第一个writeHost里配置一个readHost节点来做读写分离。为第二种方案。

第二种当写挂了的时候，读也同事不可用。但是第一种可以继续使用。建议使用第一种配置。也就是上面的代码里写的那样。

事务内部的一切操作都会走写节点，所以读操作不要加事务。

启动服务

进入 MYCAT/bin 目录 执行 ./mycat start

连接mycat

和mysql命令一致 mysql -uroot -p123456 -h127.0.0.1 -P8066 -Ddsy

如果看到以下字样说明正常运行了。

Your MySQL connection id is 1

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

配置MySQL主从同步

现有主机A：192.168.0.107

主机B：192.168.0.164

做主从同步。A为master节点B为slave节点。

1. A,B主机上都开启binlog功能。

配置文件在/etc/my.cnf。取消log-bin=mysql-bin前面的注释。

1. 在A主机上创建一个slave用户，只赋给他REPLICATION SLAVE 权限即可。

1. 在A主机上执行以下SQL语句

FLUSH LOGS

SHOW MASTER STATUS

• 1
• 2

确保在执行FLUSH LOGS时刻，AB两个库的数据完全一致。

执行完SHOW MASTER STATUS以后记录下binlog文件和pos位置。

1. 在B主机上将master节点指向A主机

CHANGE MASTER TO MASTER_HOST = '192.168.0.107', MASTER_USER = 'slave', MASTER_PASSWORD = 'slave', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin.000002' MASTER_LOG_POS = 105 ;

• 1

1. 启动主从同步

START SLAVE

• 1

1. 查看主从同步状态

SHOW SLAVE STATUS

• 1

如果看到Slave_IO_Running和Slave_SQL_Running都是Yes

这说明mysql主从同步配置完毕

踩坑指南

尽量保证AB主机上的mysql版本一致。

如果无法保证，尽量保证mysqlbinlog版本一致。

如果连mysqlbinlog版本都无法保证一致，最起码主节点的mysqlbinlog不能高于从主机的mysqlbinlog。

我在测试的时候主节点的mysqlbinlog是V4.4的，从节点的mysqlbinlog是V3.3的。简直是噩梦。

老版本的mysqlbinlog不能正常读取新版本下产生的binlog日志文件。