mysql mycat 双主 读写分离

主从复制参考这篇文章，主从缺点是如果master挂掉，slave即使提供读写功能，等master恢复正常会造成主从数据不同步影响，双主可解决这个问题，双主master提供写，master，slave负载均衡读，有条件的使用双主双从架构更好。
双主mysql配置文件
两个节点只需要改server-id和auto_increment_offset即可。

server-id = 88
auto_increment_offset=1 #该服务器自增列的初始值
auto_increment_increment=2  #该服务器自增列增量
log-bin=/var/lib/mysql/mysql-bin
binlog-do-db=pmtalk
binlog-ignore-db=mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
replicate-do-db=pmtalk
expire_logs_days=14
slave-parallel-type=LOGICAL_CLOCK # 多线程复制
slave-parallel-workers=4  # 线程数
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
max_relay_log_size = 0
relay_log_purge = 1
relay_log_recovery = 1
sync_relay_log =0
sync_relay_log_info = 0
log-slave-updates=1
slave-skip-errors=1032 # 跳过从库删除不存在错误，1062主键错误

主主连通

CHANGE MASTER TO MASTER_HOST='192.168.0.88',MASTER_PORT=3306,MASTER_USER='slave87',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;

mycat配置文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="pmtalk" />
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" 
			  writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
         <!-- <heartbeat>select user()</heartbeat> -->
	 <heartbeat>show slave status</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="192.168.0.88:3306" user="mycat1"
				   password="mycat1">
				   <!-- can have multi read hosts -->
			       	   <readHost host="hostS1" url="192.168.0.87:3306" user="mycat2" password="mycat2" weight="10" /> 
		</writeHost>
		<writeHost host="hostM2" url="192.168.0.87:3306" user="mycat2"
			password="mycat2">
                      <readHost host="hostS2" url="192.168.0.88:3306" user="mycat1" password="mycat1" weight="1" /> 
		</writeHost>
		
	</dataHost>
</mycat:schema>

gtid复制模式

server-id=88
binlog-do-db=pmtalk_open
replicate-do-db=pmtalk_open
binlog-ignore-db=mysql
slave-parallel-type=LOGICAL_CLOCK # 多线程复制
slave-parallel-workers=4  # 线程数
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
max_relay_log_size = 0
relay_log_purge = 1
relay_log_recovery = 1
sync_relay_log =0
sync_relay_log_info = 0
expire_logs_days=14
log-slave-updates=1
log-bin=/var/lib/mysql/mysql-bin
slave-skip-errors=1032 # 跳过从库删除不存在错误，1062主键错误
auto_increment_offset=1 #该服务器自增列的初始值
auto_increment_increment=1  #该服务器自增列增量
gtid_mode = on    #开启gtid模式
enforce_gtid_consistency = on
skip_slave_start=0    #不跳过slave复制线程，即自动开启slave，否则重启数据不会自动同步

server-id=87
binlog-do-db=pmtalk_open
replicate-do-db=pmtalk_open
binlog-ignore-db=mysql
slave-parallel-type=LOGICAL_CLOCK # 多线程复制
slave-parallel-workers=4  # 线程数
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
max_relay_log_size = 0
relay_log_purge = 1
relay_log_recovery = 1
sync_relay_log =0
sync_relay_log_info = 0
expire_logs_days=14
log-slave-updates=1
log-bin=/var/lib/mysql/mysql-bin
slave-skip-errors=1032 # 跳过从库删除不存在错误，1062主键错误
auto_increment_offset=1 #该服务器自增列的初始值
auto_increment_increment=2  #该服务器自增列增量
gtid_mode = on    #开启gtid模式
enforce_gtid_consistency = on
skip_slave_start=0    #不跳过slave复制线程，即自动开启slave，否则重启数据不会自动同步

连通

CHANGE MASTER TO MASTER_HOST='192.168.0.87',MASTER_PORT=3306,MASTER_USER='slave88',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

CHANGE MASTER TO MASTER_HOST='192.168.0.88',MASTER_PORT=3306,MASTER_USER='slave87',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

关闭

stop salve;
reset slave all;

gtid模式数据不一致解决办法

reset master;
stop slave;
reset slave all;
重新建立连接
start slave;

有了skip_slave_start,除非使用START SLAVE命令,否则从服务器不会开始复制。

设置log_slave_updates,让从服务器更新记录日志,有助于在必要时把从切换成主。

在 MySQL 5.6 版本时，基于 GTID 的复制中 log-slave-updates 选项是必须的。但是其增大了从服务器的IO负载,
而在 MySQL 5.7 中该选项已经不是必须项。