MySQL5.7双主同步配置

1、MySQL同步机制介绍

MySQL同步机制基于master主服务器把所有对数据库的更新操作(更新、删除等)都记录在二进制日志里，并维护日志文件的一个索引以跟踪日志循环。从服务器在日志中读取最后一次成功更新的位置，并接收从那时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。因此，想要启用同步机制，在master端就必须启用二进制日志。每个slave从服务器接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所以slave必须在启用二进制日志时把master上已经存在的数据拷贝过来(利用快照或备份)。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。
MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行START SLAVE语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行SHOW PROCESSLIST语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的Binlog Dump线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。

2、MySql环境

操作系统：CentOS 7.4
mysql版本：mysql 5.7.28
MasterA-33服务器IP: 172.16.0.33
MasterB-34服务器IP: 172.16.0.34
VIP:172.16.0.50

3、Mysql安装

可参考以下网址：https://www.liuwg.com/archives/MysqlInstall

4、Mysql配置

1）、在MasterA-33服务器mysql配置文件/etc/my.cnf 中添加如下配置信息：

server-id=1                             #server的唯一标识
log_bin = mysqlbinlog                     #打开二进制功能,MASTER主服务器必须打开此项
relay_log = mysql-relay-bin
log_slave_updates =1
auto_increment_offset=1                 #自增id起始值，双主互备时offset设置为不同的值
auto_increment_increment=2               #每次自增数字
slave_parallel_type=logical_clock       ##MySQL5.7新增加的值，配置基于表的组提交并行复制，默认值为database（基于库进行多线程复制，MySQL5.6是基于库的方式进行多线程方式复制）建议改为logical_clock，基于表的组方式复制，提高复制的效率。

#replicate-ignore-db指定不需要同步主从的数据库
replicate-ignore-db = information_schema     
replicate-ignore-db = performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = sys

2）、在MasterB-34服务器mysql配置文件/etc/my.cnf 中添加如下配置信息：

server-id=2                             #server的唯一标识
log_bin = mysqlbinlog                     #打开二进制功能,MASTER主服务器必须打开此项
relay_log = mysql-relay-bin
log_slave_updates =1
auto_increment_offset=2                 #自增id起始值，双主互备时offset设置为不同的值
auto_increment_increment=2               #每次自增数字
slave_parallel_type=logical_clock       ##MySQL5.7新增加的值，配置基于表的组提交并行复制，默认值为database（基于库进行多线程复制，MySQL5.6是基于库的方式进行多线程方式复制）建议改为logical_clock，基于表的组方式复制，提高复制的效率。

#replicate-ignore-db指定不需要同步主从的数据库
replicate-ignore-db = information_schema     
replicate-ignore-db = performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = sys

3）、添加双主同步帐户

在MasterA-33与MasterB-34服务器上创建复制账号，在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限，即在MasterA数据库创建备份帐号backup，密码为：Password@123…。
MasterA-33创建帐号命令：

mysql> grant replication slave ,replication client on *.* to backupA@'172.16.%.%' identified by 'Password@123..';

mysql>flush privileges;

MasterB-34创建帐号命令：

mysql> grant replication slave ,replication client on *.* to backupB@'172.16.%.%' identified by 'Password@123..';

mysql>flush privileges;

4)、查看主库的状态

在MasterA-33执行如下命令：

mysql> show master status;

记录Mysql二进制文件的File和Position值，在MasterB-34执行如下命令,将MasterB-34配置为MasterA-33从服务器。

mysql> change master to master_host='172.16.0.33', master_user='backupA', master_password='Password@123..', master_port=3306, master_log_file='mysqlbinlog.000002',master_log_pos=477;

mysql> start slave;

mysql> show slave status\G

在MasterB-34执行如下命令：

mysql> show master status;

记录Mysql二进制文件的File和Position值，在MasterA-33执行如下命令,将MasterA-33配置为MasterB-34从服务器。

mysql> change master to master_host='172.16.0.34', master_user='backupB', master_password='Password@123..', master_port=3306, master_log_file='mysqlbinlog.000002',master_log_pos=472;

mysql> start slave;

mysql> show slave status\G

注：当已启动slave复制时,需要修改master信息,应先执行stop slave停止复制,然后重新修改,再启动复制，至此，Mysql双主同步配置已完成。

5、keepalived安装

可参考网址：https://www.liuwg.com/archives/keepalived

6、Keepalived配置

1）、在MasterA-33中keepalived的配置

首先要编辑keepalived.conf配置文件

[root@localhost /]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
 
global_defs {
   router_id centos33 #标识本节点的字条串，通常为hostname

   script_user root
   enable_script_security 
}

vrrp_instance VI_1 {
    state BACKUP         #此处两个都设置为BACKUP
    interface eth0       #vip 绑定端口
    virtual_router_id 51 #在同一个虚拟路由里，id 号必须相同；
    priority 100         #优先级;
    advert_int 1         #心跳间隔时间
    nopreempt            #启用非抢占式模式，仅在MasterA-33启用即可
    authentication {
        auth_type PASS   #认证
        auth_pass 1111   #密码
    }
    virtual_ipaddress {
        172.16.0.50    #虚拟ip
    }
}

virtual_server 172.16.0.50 3306 {
     delay_loop 2  #每2秒检查一次real_server存活
     lb_algo wrr   #带有权重的轮询
     lb_kind DR
     persistence_timeout 60 #同一IP的连接60秒内被分配到同一台真实服务器 
     protocol TCP
     real_server 172.16.0.33 3306 {
         weight 3   #权重为3
         notify_down /etc/keepalived/keepalived_shutdown.sh   #当mysq服down时，执行此脚本，杀死keepalived实现切换。
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             #nb_get_retry 3 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口，配置自己mysql服务端口
         }
     }
}

编写keepalived_shutdown.sh检测脚本：

[root@localhost /]# vi /etc/keepalived/keepalived_shutdown.sh

#!/bin/bash
#kill掉keepalived进程。
pkill keepalived

[root@localhost /]# chmod +x /etc/keepalived/keepalived_shutdown.sh

[root@localhost /]# systemctl restart keepalived   //重启keepalived服务

[root@localhost /]# tail -f /var/log/messages   //查看keepalived服务有没有报错信息。

2）、在MasterB-34中keepalived的配置

首先要编辑keepalived.conf配置文件

[root@localhost /]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
 
global_defs {
   router_id centos34 #标识本节点的字条串，通常为hostname

   script_user root
   enable_script_security 
}

vrrp_instance VI_1 {
    state BACKUP         #此处两个都设置为BACKUP
    interface eth0       #vip 绑定端口
    virtual_router_id 51 #在同一个虚拟路由里，id 号必须相同；
    priority 90         #优先级;
    advert_int 1         #心跳间隔时间
    #nopreempt            #在MasterA-34不启用非抢占式模式
    authentication {
        auth_type PASS   #认证
        auth_pass 1111   #密码
    }
    virtual_ipaddress {
        172.16.0.50    #虚拟ip
    }
}

virtual_server 172.16.0.50 3306 {
     delay_loop 2  #每2秒检查一次real_server存活
     lb_algo wrr   #带有权重的轮询
     lb_kind DR
     persistence_timeout 60 #同一IP的连接60秒内被分配到同一台真实服务器 
     protocol TCP
     real_server 172.16.0.34 3306 {
         weight 3   #权重为3
         notify_down /etc/keepalived/keepalived_shutdown.sh   #当mysq服down时，执行此脚本，杀死keepalived实现切换。
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             #nb_get_retry 3 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口，配置自己mysql服务端口
         }
     }
}

编写keepalived_shutdown.sh检测脚本：

[root@localhost /]# vi /etc/keepalived/keepalived_shutdown.sh

#!/bin/bash
#kill掉keepalived进程。
pkill keepalived

[root@localhost /]# chmod +x /etc/keepalived/keepalived_shutdown.sh

[root@localhost /]# systemctl restart keepalived   //重启keepalived服务

[root@localhost /]# tail -f /var/log/messages   //查看keepalived服务有没有报错信息。

7、VIP飘移测试

1）、在MasterA-33与MasterB-34中执行命令：ip addr 或 ip a s，查看VIP所在服务器

在MasterA-33执行命令显示如下：当前VIP在此服务器。

[root@localhost /]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:0c:29:0d:e7:4d brd ff:ff:ff:ff:ff:ff
    inet 172.16.0.33/24 brd 172.16.0.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.16.0.50/32 scope global eth0   //发现VIP在MasterA-33上。
       valid_lft forever preferred_lft forever
    inet6 fe80::44be:a765:23b4:c5d0/64 scope link 
       valid_lft forever preferred_lft forever

在MasterB-34执行命令显示如下，VIP不在此服务器。

[root@localhost /]#  ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:98:3e:cf brd ff:ff:ff:ff:ff:ff
    inet 172.16.0.34/24 brd 172.16.0.255 scope global noprefixroute dynamic eth0
       valid_lft 8672sec preferred_lft 8672sec
    inet6 fe80::3a59:5502:dc86:c73/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

2）、停用VIP所在服务器MasterA-33上的mysql服务，检查VIP是否飘移到MasterB-34服务器。

在MasterA-33上的mysql服务，并检查keepavlived服务是否被kill掉了。

[root@localhost /]# systemctl stop mysqld    //停用mysql
[root@localhost /]# ps -ef|grep keepalived    //检查keepalived服务已经不存在了
root     19692 13023  0 19:36 pts/2    00:00:00 grep --color=auto keepalived
[root@localhost /]# ip addr      //VIP已经不在MasterA-33上了。
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:0c:29:0d:e7:4d brd ff:ff:ff:ff:ff:ff
    inet 172.16.0.33/24 brd 172.16.0.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::44be:a765:23b4:c5d0/64 scope link 
       valid_lft forever preferred_lft forever

在MasterB-34上查看VIP是否已经漂移到该机器。

[root@localhost /]#  ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:98:3e:cf brd ff:ff:ff:ff:ff:ff
    inet 172.16.0.34/24 brd 172.16.0.255 scope global noprefixroute dynamic eth0
       valid_lft 7791sec preferred_lft 7791sec
    inet 172.16.0.50/32 scope global eth0    //发现VIP已经飘移到MasterB-34服务器，测试成功了。
       valid_lft forever preferred_lft forever
    inet6 fe80::3a59:5502:dc86:c73/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

总结

Keepalived+mysql双主同步配置方案需要注意的地方：

采用keepalived作为高可用方案时，两个节点最好都设置成BACKUP模式，避免因为意外情况下相互抢占导致两个节点内写入相同的数据而引发冲突。
把两个节点的auto_increment_increment（自增步长）和auto_increment_offset（字增起始值）设置成不同值，其目的是为了避免master节点意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原master上冲突，因此一开始就错开。
基于Keepalived+Mysql高可用方案可以为Mysql提供容错，缺点也很明显，只有一台Mysql对外提供服务，另外一台只能起到备份的功能，无法实现负载均衡的效果。

文章作者: 阳光•雨

文章连结: /archives/mysql-master-slave

微信扫一扫