Mysql5.6双主复制

通过lvs和mysql slaves的组合应用，数据库查询类的请求可靠性已被大大提升，但是出于核心地位的master节点仍是单点。

通过了解mysql的复制特性可以知道，master节点的数据是有冗余的，slave节点就是它的冗余，若但从数据角度上看，可以说master节点不存在单点，因为即使master节点出现故障，正产情况下数据仍然是安全的(只要slave节点没有跟着宕机)。

Mysql中的主或从两个角色都是逻辑上定义的，也就是说slave节点和普通的实例没有区别，也是可以对外提供服务的，从这个角度上看，如果master节点遇到故障，随便挑一台slave出来做为新的master节点就好了。这个过程也就是通常讲的故障切换。

传统复制环境中的master节点和slave节点进行切换后，存在两方面问题，一是当某个slave节点升级为master节点后，即便原master节点从故障中恢复，也回不到复制环境中了，至于原来复制环境中的其他slave，稍有意外，就同样也不属于复制环境了。

一. 实现故障随意切换

第一个问题，由于slave节点是单向从master节点接收数据，一旦故障切换后，原master节点没有数据来源，与新的master节点数据差异越来越大，因此无法继续成为复制环境中的一员，如果能够让master节点也能获取并应用salve节点产生的二进制日志，另其互为主从，那么无论怎样切换，另一个节点也不会脱离复制环境了

1. 实现master和slave的双向复制

--查询slave节点的二进制日志文件和读写位置

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000006 |      120 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

--在master节点，使其从slave节点的指定位置开始读取二进制日志

mysql> change master to 
    -> master_host='192.168.48.51',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='mysqld',
    -> master_log_file='binlog.000006',
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.38 sec)

--启动master节点的slave服务

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

--验证数据同步即可

2. 双向复制的隐患

Mysql的复制环境，主从之间一致性完全依赖于二进制日志，这个二进制日志不管是生成、接收还是应用，都是逻辑上的，逻辑是没办法保证两者完全相同的。

比如两个节点同时执行写操作，并且写入同一个对象，在高并发的环境中时很有可能出现的。这种场景，如果两端写入不同的记录，sql语句执行后出现问题的几率会比较小，不过若两端同时写入相同的记录，操作风险就比较高了。语句有可能正常进行，但日志发送到slave节点在应用时有较大几率出现问题。

尤其是mysql表对象的主键通常都是自增长的，插入记录往往无需指定主键值，这种情况下，若同时在两个节点分别向一个对象中插入数据，即使明确指定列值是不同的，两边产生的主键也极有可能重复。

模拟上述情景：暂停一端的slave服务，然后在另一端执行插入操作，而后转回被暂停的节点上也执行插入操作，再启动该节点的slave服务

--暂停一端(192.168.48.51)的salve服务

mysql> stop slave;
Query OK, 0 rows affected (0.32 sec)

--在另一端(192.168.48.50)插入数据

mysql> insert into test1 (v1) values('192.168.48.50');
Query OK, 1 row affected (0.33 sec)

--在暂停复制的节点也插入一条数据

mysql> select * from test1;
Empty set (0.00 sec)

mysql> insert into test1 (v1) values('192.168.48.51')
    -> ;
Query OK, 1 row affected (0.06 sec)

--启动本地salve服务

               Last_SQL_Error: Could not execute Write_rows event on table test.test1; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000006, end_log_pos 452

--另外一个节点(192.168.48.50)上也在报这个错误

3. 处理sql线程错误

处理salve服务应用过程中出现的错误，从操作上分为两个步骤：

a. 修复导出出现错误的sql语句涉及的数据，就本例而言，由于主键冲突导致两端写入的数据未能同步，手工在两端各自执行报错的sql语句是不行的，一方面会导致数据不一致，另一方面是治标不治本，后面还会出现数据冲突的错误，以致没完没了，如果可以的话最好删除源端对应的记录，然后重新执行一遍

b. 跳过错误，继续应用后面的日志。Mysql复制环境中的slave节点在应用二进制日志时，只要碰到错误，就会暂停应用进程，在错误被修正之前，该线程是无法启动的。然而mysql的复制属于逻辑复制，因此遇到无法在salve节点应用的事件，可以选择先跳过它，继续后面的变更事件，对此，mysql提供了sql_slave_skip_counter系统变量，该变量用于执行跳过最近的n次事件，默认为0.

基于上面的两点，处理sql线程应用错误：

--在两个节点上都要执行

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

--在任意节点上执行

mysql> delete from test1 where v1 in ('192.168.48.50','192.168.48.51');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1 (v1) values('192.168.48.50');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test1 (v1) values('192.168.48.51');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+----+---------------+
| id | v1            |
+----+---------------+
|  2 | 192.168.48.50 |
|  3 | 192.168.48.51 |
+----+---------------+
2 rows in set (0.00 sec)

3. 避免自增列值冲突

要解决自增列值冲突问题，简单且直接的方式，是同时只允许应用层连接双主环境中的某一个节点，或者说写入操作只允许在一个节点上执行，这样就可以避免自增列因为多节点并发写的缘故造成重复，不过它并不能完全避免自增列冲突。

比如当前提供写入服务的master节点出现故障，由于一些原因，二进制日志未能完全传播到待切换的master节点，那么在新的master节点上在生成自增列的列值时，就会生成原master节点已经生成过的自增值，当原master节点从故障中恢复过来时，复制进程的应用又会出现错误。

可以从mysql自增列的生成规则上考虑这个问题：

Auto_increment_increment：执行递增列增长时的递增值，范围从1-65535，默认值为1，也可以指定为0，不过当指定为0时，效果等同于1.

Auto_increment_offset：指定自增列增长时的偏移量，也就是自增列的初始值。

结合这两个值，我们可以为不同的实例指定不同的自增值规则，比如讲两个节点的递增至都改为2，其中一个节点的偏移量设置为1，另外一个节点的偏移量改为2，这样其中一个节点生成的值始终为奇数，另外一个节点始终为偶数。但这样设定之后，批量插入的序号之间会有间隙(gap)，如果业务对记录序号连续性有要求，就不适用了。

二. keepalived实现ip自动漂移

由于双机读写可能会遇到众多的问题，所以实际应用中，最好的方式还是由前端应用层执行写操作时只连接DB环境中的某一个master节点，另外的节点将其视为slave节点，对外提供只读服务。尽管没有了负载均衡的能力，但加强了高可用性并降低了应用层开发的难度。当发生故障时，原master节点的ip能够漂移到新的master节点(当前slave)，这样切换对前端应用也是透明的，无需进行额外改造。

这次将keepalived安装到运行mysql服务的两个master节点上。设定keepalived服务持有vip：192.168.48.56 ，这个ip地址由keepalived服务托管，他会自动将之绑定到某一台mysql实例，前端应用连接该ip地址即可，当绑定的实例出现故障时，keepalived自动把vip绑定到另一台处于活动状态的mysql实例。

1. 在两台master节点安装keepalived

略

2. 配置master节点

--keepalived配置文件

[root@mysql keepalived]# vi /etc/keepalived/keepalived.conf
vrrp_script check_run {
script "/usr/local/keepalived/bin/ka_check_mysql.sh"
interval 10
}

vrrp_instance VPS {
state BACKUP #初始时指定两台服务器均为备份状态，以避免服务重启时可能造成的震荡(master角色争夺)
interface eth0
virtual_router_id 34
priority 100 #优先级，另一节点本参数值可设置的捎小一些。
advert_int 1
nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的不设置
authentication {
auth_type PASS
auth_pass 3141
}
virtual_ipaddress {
192.168.48.56
}
track_script {
check_run
}
}

--创建mysql检测脚本

[root@mysql ~]# cat /usr/local/keepalived/bin/ka_check_mysql.sh
#!/bin/bash
MYSQL_USER=root
MYSQL_PASS=mysqld
MYSQL_CMD=/u01/my3306/bin/mysql
CHECK_TIME=3 #check three times
MYSQL_OK=1 #MYSQL_OK values to 1 when Mysql service working fine,else values to 0

function check_mysql_health() {
$MYSQL_CMD -u${MYSQL_USER} -p${MYSQL_PASS} -S /u01/my3306/run/mysql.sock -e "show status;" > /dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}

while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_health
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi

if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done

--启动keepalived服务

[root@mysql keepalived]# service keepalived start
Starting keepalived:                                       [  OK  ]
--keepalived持有的虚拟ip，通过ifconfig命令查不到，通过ip addr可以查看到

此时vip在此master节点上

[root@mysql bin]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:38:88:35 brd ff:ff:ff:ff:ff:ff
    inet 192.168.48.50/24 brd 192.168.48.255 scope global eth0
    inet 192.168.48.56/32 scope global eth0
    inet6 fe80::20c:29ff:fe38:8835/64 scope link 
       valid_lft forever preferred_lft forever

现在应用就可以通过192.168.48.56这个vip来访问mysql实例了。

3. 配置另外一台master节点

配置另外一台master节点，唯一的不同是权重值低于第一个节点

4. 高可用测试

当前的检测脚本中，设定检测时间为10s，也就是说当停止mysql服务后，最长可能需要等待10s，keepalived检测到异常，然后出发ip地址漂移。

三. 双主架构设计

前的架构，如果相应写请求的master节点发生故障，vip切换至另外一台master节点后，旧的master节点所属的slave节点数据就无法更新，只有等到master节点恢复正常后，才能恢复数据。所以可以将slave节点分配个不同的master节点，保证master节点有slave在提供服务

 

注：mysql数据库有一个read_only的系统参数，用于控制系统是否允许执行写操作，建议在slave节点开启，即设置为1以避免用户将数据写入slave节点。