MySQL高可用方案——双主

MySQL的高可用方案有很多种,双主、MHA、MMM等等,这里只是写下最简单的双主这种高可用方案。

一、配置MySQL互为主从

1.1 环境准备

OS IP hostname service
Centos 7.5 192.168.1.1 db01 MySQL+Keepalived
Centos 7.5 192.168.1.2 db02 MySQL+Keepalived

本篇博文就不再部署MySQL服务了,如果没有MySQL的环境可以参考博文手撕MySQL 5.7.29源码编译安装.md

1.2 开启二进制日志及中继日志

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=1
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
pid-file=/usr/local/mysql/data/mysqld.pid
binlog_format = mixed     #指定二进制格式
log-bin=/usr/local/mysql/data/log_bin            #指定二进制日志文件
relay-log=/usr/local/mysql/data/relay-bin        #指定中继日志
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=1
[root@db01 ~]# systemctl restart mysqld


[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=2
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
pid-file=/usr/local/mysql/data/mysqld.pid
binlog_format = mixed
log-bin=/usr/local/mysql/data/log_bin
relay-log=/usr/local/mysql/data/relay-bin
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=2
[root@db02 ~]# systemctl restart mysqld

注意:mysql01和mysql02只有server-id和auto_increment_offset不同!

mysql中有自增长字段,在做数据库的主主同步时需要设置自增长的两个相关配置:auto_increment_offset和auto_increment_increment。

  • auto-increment-increment表示自增长字段每次递增的量,其默认值是1。它的值应设为整个结构中服务器的总数,我这里用到两台服务器,所以值设为2;
  • auto-increment-offset是用来设定数据库中自动增长的起点(即初始值),因为这两台服务器都设定了一次自动增长值2,所以它们的起点必须得不同,这样才能避免两台服务器数据同步时出现主键冲突;

关于“binlog_format = mixed”配置项,是用来定义二进制日志的格式的,有以下三个值可选,如下:

  • STATEMENT:基于sql语句来记录二进制日志,比如有些sql语句可能会影响上百条数据的改动,那么也只是记录一条sql语句。优点:可以减少二进制日志的大小,减少日志写入的I/O量。缺点:需要进行数据恢复时,某些自定义的存储过程或函数可能会失效,数据可能无法恢复;
  • ROW:基于行来记录二进制日志,如果某一条SQL语句影响了多行数据,那么将会记录多条二进制日志,优点:可以通过二进制日志来精准的恢复数据。缺点:当发生变化的数据量较大时,会给磁盘I/O带来一定的压力;
  • mixed:基于混合模式来记录二进制日志。MySQL自行判断是基于行还是基于sql语句来记录日志,建议采用这种格式,如果基于sql语句来记录就可以精准记录数据的变化,那么就会基于sql语句,如果sql语句中包含存储过程或环境变量等,那么就会基于行来记录;

关于二进制日志的更多介绍,建议参考MySQL官方文档

注意:可以在my.cnf文件中添加“binlog_do_db=数据库名”配置项(可以添加多个)来指定要同步的数据库!

1.3 将db01设置为db02的主服务器

① 防火墙放行3306端口的流量(两台主机都需要放行3306端口,如果防火墙没有开启,则可忽略)
[root@db01 ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@db01 ~]# firewall-cmd --reload
② db01上创建授权用户
[root@db01 ~]# mysql -uroot -p123
mysql> grant replication slave on *.* to test@'192.168.1.%' identified by '123';
③ 查看db01的当前binlog状态信息
mysql> show master status \G        #查看该服务器的binlog信息
*************************** 1. row ***************************
             File: log_bin.000001        #该值一会会用到
         Position: 452             #该值一会会用到
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
④ 在db02上指定db01为master,并开启slave功能
[root@db02 ~]# mysql -uroot -p123
mysql> change master to master_host='192.168.1.1',
    -> master_user='test',
    -> master_password='123',
    -> master_log_file='log_bin.000001',    #该值需与master上的值一致
    -> master_log_pos=452;        #同上
mysql> start slave;           #开启slave功能
mysql> show slave status \G       #查看slave状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000001
          Read_Master_Log_Pos: 452
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000001
             Slave_IO_Running: Yes       #保证该线程是YES状态
            Slave_SQL_Running: Yes       #同上

只要上面两个值为yes,则表示主从没有问题:

  • IO线程是去master上面读取二进制日志到本地的中继日志中;
  • SQL线程是将本地的中继日志中的内容转换为sql语句并执行;

1.4 将db02设置为db01的主服务器

① db02创建授权用户
[root@db02 ~]# mysql -uroot -p123
mysql> grant replication slave on *.* to test01@'192.168.1.%' identified by '123';
#授权的用户可以和db01服务器上的一致,也可以不一致
mysql> flush privileges;     #刷新权限
② 查看db02的当前binlog状态信息
[root@db02 ~]# mysql -uroot -p123
mysql> show master status \G  #查看该服务器的binlog信息,获取File和 Position对应的值
*************************** 1. row ***************************
             File: log_bin.000001
         Position: 611
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
③ 在db01上指定db02为master,并开启slave功能
[root@db01 ~]# mysql -uroot -p123
mysql> change master to master_host='192.168.1.2',
    -> master_user='test01',
    -> master_password='123',
    -> master_log_file='log_bin.000001',
    -> master_log_pos=611;
mysql> start slave;          #启动slave
mysql> show slave status \G     #查看slave状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.2
                  Master_User: test01
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000001
          Read_Master_Log_Pos: 611
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000001
             Slave_IO_Running: Yes       #保证该线程是YES状态
            Slave_SQL_Running: Yes       #同上

1.5 测试同步

① db01创建测试数据
[root@db01 ~]# mysql -uroot -p123
mysql> create database test;
mysql> use test
mysql> create table t1(id int,name varchar(4));
mysql> insert into t1 values(1,'a'),(2,'b');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
② 确认db02已经同步数据并插入新的数据
[root@db02 ~]# mysql -uroot -p123
mysql> use test
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
mysql> insert into t1 values(3,'c'),(4,'d');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
③ 确认db01已经同步数据
[root@db01 ~]# mysql -uroot -p123
mysql> use test
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+

至此,现在任何一台MySQL上更新数据都会同步到另一台MySQL,MySQL同步完成!

注意:若主MySQL服务器已经存在,只是后期业务拓展才搭建从服务器,在配置数据库同步前应先将MySQL服务器的要同步的数据库拷贝到从服务器上(如先在主MySQL上备份数据库,再用备份再从MySQL服务器上恢复)。

二、配置keepalived高可用

2.1 安装Keepalived

[root@db01 ~]# yum -y install keepalived
[root@db02 ~]# yum -y install keepalived

2.2 配置防火墙放行相关流量

注意:两台主机都需要执行以下命令,以便放行相关流量。192.168.1.100是keepalived的组播地址,使用的是vrrp协议。

[root@db01 ~]# firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --in-interface ens33 --destination 192.168.1.100 --protocol vrrp -j ACCEPT
[root@db01 ~]# firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface ens33 --destination 192.168.1.100 --protocol vrrp -j ACCEPT
[root@db01 ~]# firewall-cmd --reload

2.3 修改主机db01的Keepalived配置文件

[root@db01 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id db01       #route_id必须唯一
}

vrrp_instance VI_1 {
    state BACKUP   #指定角色为backup,两台MySQL服务器的角色均为backup,设置backup将根据优先级决定主从
    interface ens33    #指定承载虚拟IP的网卡
    virtual_router_id 51    #指定组,同一个集群内的值必须一致。并且不可和局域网中的其他组冲突
    priority 100        #优先级范围为:0~100
    advert_int 1        #发vrrp包的时间间隔,即多久进行一次master选举(可认为是健康检查时间间隔)
    nopreempt      #不抢占,即允许一个priority比较低的节点作为master
    authentication {             #认证区域
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {            #VIP区域,指定vip地址
        192.168.1.100
    }
}

virtual_server 192.168.1.100 3306 {     #设置虚拟服务器,需要指定虚拟IP地址和服务端口,IP与端口之间用空格隔开
    delay_loop 2        #设置运行情况检查时间,单位是秒
    lb_algo rr          #设置后端调度算法
    lb_kind DR          #设置lvs实现负载均衡的机制,有NAT、TUN、DR三个模式,DR模式效率最高
    persistence_timeout 60       #会话保持时间,单位是秒
    protocol TCP        #指定转发协议类型,有TCP和UDP两种


    real_server 192.168.1.1 3306 {      #配置服务节点,这里指定的也就是本机的真实IP
        weight 1         #设置权重
    notify_down /etc/keepalived/bin/mysql.sh      #检测到real_server的MySQL服务宕机后执行的脚本
    TCP_CHECK {
        connect_port 3306    #健康检查端口
        connect_timeout 3     #连接超时时间
        retry 3         #重试次数
        delay_before_retry 3      #重连间隔时间
     }
   }
}
[root@db01 ~]# mkdir /etc/keepalived/bin
[root@db01 ~]# vim /etc/keepalived/bin/mysql.sh
#!/bin/bash
pkill keepalived    #停止keepalived服务
[root@db01 ~]# chmod +x /etc/keepalived/bin/mysql.sh
#创建需要的脚本并赋予权限
[root@db01 ~]# systemctl start keepalived
[root@db01 ~]# ip a show ens33    
#必须使用ip a命令才可以查看到,ifconfig命令查看不到
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:66:72:13 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.1/24 brd 192.168.1.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.1.100/32 scope global ens33    #可以看到指定的VIP已经绑定到ens33上
       valid_lft forever preferred_lft forever
    inet6 fe80::9f3:b94e:5f5d:8070/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

2.4 修改主机db02的Keepalived配置文件

[root@db02 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id db02       #更改router_id,此处在热备组中必须要唯一
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90       #更改优先级
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.100
    }
}

virtual_server 192.168.1.100 3306 {
    delay_loop 2
    lb_algo rr
    lb_kind DR
    persistence_timeout 60
    protocol TCP

    real_server 192.168.1.2 3306 {    #更改为本机的IP地址及监听端口
        weight 1
    notify_down /etc/keepalived/bin/mysql.sh
    TCP_CHECK {
        connect_port 3306
        connect_timeout 3
        retry 3
        delay_before_retry 3
     }
   }
}
[root@db02 ~]# mkdir /etc/keepalived/bin
[root@db02 ~]# vim /etc/keepalived/bin/mysql.sh
#!/bin/bash
pkill keepalived
[root@db02 ~]# chmod +x /etc/keepalived/bin/mysql.sh
#创建需要的脚本并赋予权限
[root@db02 ~]# systemctl start keepalived

至此,即可实现MySQL的双主效果(只要VIP所在的节点,MySQL服务端口无法连接,那么VIP将切换至另一台节点,即使宕机的mysql服务器恢复,也不会对VIP进行抢占)。虽然有两台MySQL数据库,但是其使用keepalived提供的虚拟IP地址来对外提供服务,不管这个虚拟Ip地址落在哪台服务器上,都可以保证数据的一致性,因为它们互为主从,并且keepalived的状态都为backup,也设置了不抢占(减少VIP的切换次数),这样可以大大的避免keepalived的脑裂问题!

上一篇:MySQL数据库升级总结


下一篇:MySQL数据库管理员密码忘记如何修改?