MyCAT读写分离与MySQL主从同步

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

Mycat

一个开源的高性能的数据库中间件产品，支持读写分离，支持MySQL主从，数据切分以及galera cluster集群。
它是一个开源的分布式数据库系统，是一个实现了 MySQL 协议的的
Server，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用
MySQL 原生（Native）协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，
其核心功能是分表分库，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。

安装指南

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

使用指南

配置

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>

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

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

<schema name="dsy" checkSQLschema="false" sqlMaxLimit="100">
		<!-- auto sharding by id (long) -->
		<table name="xn_user_info" dataNode="dn1"/>
	</schema>

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

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

解释：创建一个名字叫做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>

解释：创建一个名为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 status
3	基于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前面的注释。
2. 在A主机上创建一个slave用户，只赋给他REPLICATION SLAVE 权限即可。
3. 在A主机上执行以下SQL语句

FLUSH LOGS
SHOW MASTER STATUS

确保在执行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. 启动主从同步

START SLAVE

1. 查看主从同步状态

SHOW SLAVE STATUS

如果看到Slave_IO_Running和Slave_SQL_Running都是Yes
这说明mysql主从同步配置完毕

踩坑指南

尽量保证AB主机上的mysql版本一致。
如果无法保证，尽量保证mysqlbinlog版本一致。
如果连mysqlbinlog版本都无法保证一致，最起码主节点的mysqlbinlog不能高于从主机的mysqlbinlog。
我在测试的时候主节点的mysqlbinlog是V4.4的，从节点的mysqlbinlog是V3.3的。简直是噩梦。
老版本的mysqlbinlog不能正常读取新版本下产生的binlog日志文件。

1.主从同名莫名异常，暂不清楚原因，先写解决方案,执行以下sql

stop slave;
set global sql_slave_skip_counter =1;
start slave;
show slave status;

以上方案可以解决主从异常情况，但是需要注意：
遇到该问题，要想到要怎样满足复制，而不是跳过该事务；不建议跳过错误，遇到错误应该修正过来，再连接主库复制，否则从库的数据会越来越不一致！

手工修复操作有些慢，可以针对1062和1032错误，写一个自动化监控改正脚本。

注意：若经常数据不一致，选择业务低峰期，检验一次数据（pt-table-checksum），查看是否数据一致，若检查出太多的数据不一致，该从库就不可再用了，再创建一个从库！

检测的命令(在主库运行该命令)
注意需要主库和从库有一个可以访问的用户，即在主库上建一个以主库ip为host的用户，赋予权限，这样会自动同步到从库里，从库也能正常接收主库发来的请求
pt-table-checksum --host='主库的host' --user='user' --password='password' --port='3306' --databases=zs_www --recursion-method=processlist --no-check-binlog-format