主从复制参考这篇文章,主从缺点是如果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 中该选项已经不是必须项。