mysql双机热备以及使用keepalived实现mysql双主高可用

落日映苍穹つ 2022-05-09 11:58 473阅读 0赞

根据搜集的资料安装测试并在安装测试过程中整理的文档，部分参考文档在相应位置有标记。如有不足希望不吝赐教。

--------------------

> mysql双机热备的方式有两种：
> 
>  *  主－从服务器双机热备
>  *  主－主服务器双机热备
> 
> 下文以主－主服务器双机热备为例，主－从配置类似。
> 
> 两台centos7，ip分别为：
> 
> 172.17.1.136
> 
> 172.17.3.134

**目录**

一、安装mysql

二、服务器配置

1.创建同步用户

2.修改mysql配置文件

三、重启并配置同步

1.重启两台主机上的mysql

2.查看两个服务器作为主服务器的状态

3.用change mster 语句指定同步位置

4.查看从服务器状态

四、验证

五、配置keepalived实现mysql双主高可用

1.下载安装keepalived

2.启动keepalived并查看日志

3.测试

4.其他

--------------------

# 一、安装mysql #

**本步骤两台主机都要执行一遍**。两台主机都要安装，mysql安装见《[CentOS7安装mysql8.0.12][CentOS7_mysql8.0.12]》或自行搜索。

安装完成后使用命令登录，密码为自己配置的密码：

mysql -uroot -p

创建一个测试数据库：

CREATE DATABASE `test`;

使用test库（use test;）创建一个测试空表：

create table tb_mobile( mobile VARCHAR(20) comment'手机号码', time timestamp DEFAULT now() comment'时间' );

![70][]

> 注意：网上资料中提到 配置之前要保证两个mysql数据一致，但因为本次配置两台mysql都为全新安装，因此未测试。

# 二、服务器配置 #

## 1.创建同步用户 ##

使用mysql库，

use mysql

然后分别为另一主机创建一个链接用户，该帐户必须授予REPLICATION SLAVE权限，因为mysql8在授权语句中不能出现IDENTIFIED BY ‘password’;，因此创建用户和授予权限需要分开执行：

1) 在136上执行创建用户并赋权：

CREATE USER 'replicate'@'172.17.3.134' IDENTIFIED WITH 'mysql_native_password' BY 'TestBicon@123';

> *因为**mysql8**的权限验证机制改变，因此在使用下面的命令创建用户时会有异常提示*
> 
> *create user 'replicate'@'172.17.3.134' identified by 'TestBicon@123';*

GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.3.134';

2) 在134上执行：

CREATE USER 'replicate'@'172.17.1.136' IDENTIFIED WITH 'mysql_native_password' BY 'TestBicon@123';

GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.1.136';

> 【注意】对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。
> 
> flush privileges;

![70 1][]

创建完成后我们可以通过使用对方主机对本机数据库进行访问，看下是否能链接成功【***注意****：链接对方主机时需要先退出本机**mysql**或者打开新的终端*】：

mysql -h172.17.3.134 -ureplicate -pTestBicon@123
    mysql -h172.17.1.136 -ureplicate -pTestBicon@123

如果登录成功，则可以对两台服务器进行双机热备操作。

## 2.修改mysql配置文件 ##

安装mysql后会在/etc/目录下生成一个my.cnf配置文件（如果没有可以从/usr/share/mysql目录下复制一份：cp /usr/share/mysql/my-medium.cnf /etc/my.cnf）,找到配置文件后编辑（vim /etc/my.cnf），在\[mysqld\]中添加（修改）如下内容：

1）172.17.1.136主机

> server-id=1 \# 唯一id
> 
> innodb\_flush\_log\_at\_trx\_commit=2 \# 默认为1
> 
> log-bin=mysql-bin-1 \#binlog日志文件名（可以任意命名）
> 
> \#binlog-do-db=test \# 记录日志的数据库（如果没有此项，表示同步所有的库）
> 
> binlog-ignore-db=mysql,information\_schema,performance\_schema,sys \# 不记录日志的数据库
> 
> sync\_binlog=1 \#开启binlog日志同步功能
> 
> slave\_skip\_errors=1146 \# 跳过1146错误

2）172.17.3.134主机

> server-id=2 \# 唯一id
> 
> innodb\_flush\_log\_at\_trx\_commit=2 \# 默认为1
> 
> log-bin=mysql-bin-2 \#binlog日志文件名（可以任意命名）
> 
> \#binlog-do-db=test \# 记录日志的数据库
> 
> binlog-ignore-db=mysql,information\_schema,performance\_schema,sys \# 不记录日志的数据库
> 
> sync\_binlog=1 \#开启binlog日志同步功能
> 
> slave\_skip\_errors=1146 \# 跳过1146错误

# 三、**重启并配置同步** #

## 1.重启两台主机上的mysql ##

两台主机分别执行：

systemctl restart mysqld
    或者
    service mysqld restart

## 2.查看两个服务器作为主服务器的状态 ##

show master status\G

![70 2][]

![70 3][]

## 3.用change mster 语句指定同步位置 ##

进入mysql操作界面（mysql -uroot -p），先执行如下指令：

unlock tables;

否则在执行stop slave;时会报如下异常：ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction。

1）172.17.1.136主机

先停步slave服务线程，这个是很重要的，如果不这样做会造成以下操作不成功。

stop slave;

然后：

change master to
    master_host='172.17.3.134',master_user='replicate',master_password='TestBicon@123',
    master_log_file='mysql-bin-2.000001',master_log_pos=749;

分别在服务器上重启从服务线程：

start slave;

2）172.17.3.134主机

先停步slave服务线程，这个是很重要的，如果不这样做会造成以下操作不成功。

stop slave;

然后：

change master to
    master_host='172.17.1.136',master_user='replicate',master_password='TestBicon@123',
    master_log_file='mysql-bin-1.000001',master_log_pos=749;

分别在服务器上重启从服务线程：

start slave;

## 4.查看从服务器状态 ##

show slave status\G

如果如下两项状态为yes则说明配置成功：

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

![70 4][]

![70 5][]

# 四、验证 #

在两台主机的mysql中插入/删除数据等操作，另一台会自动将操作同步。

# 五、**配置keepalived实现mysql双主高可用** #

上面的配置已经可以实现mysql双击热备，两台主机分别对外提供服务，如果服务链接某一台主机mysql故障，不能自动切换到另一台主机实现高可用，如果此可以满足需要或者不用切换，则可以不继续进行配置。下面将配置keepalived实现其高可用。

## 1.下载安装keepalived ##

官网地址：[http://www.keepalived.org][http_www.keepalived.org]（以下以2.0.6版本为例）

安装位置：/usr/local/    cd /usr/local/

使用下面命令下载并解压：

wget http://www.keepalived.org/software/keepalived-2.0.6.tar.gz
    
    tar -xzvf keepalived-2.0.6.tar.gz -C keepalived

在安装之前需要使用下面命令安装相关依赖：

yum -y install curl gcc openssl-devel libnl3-devel net-snmp-devel
    yum -y install libnfnetlink-devel
    yum -y install ipvsadm

然后安装keepalived：

cd keepalived
    ./configure
    make && make install

> PS:编译时出现问题（异常），根据异常信息进行相应的修改，如果出现缺少xx-devel等信息，安装xx-devel即可，其他请根据异常信息决定如果操作。

编译安装后，会根据系统环境，生成启动脚本，如下：

cat /usr/lib/systemd/system/keepalived.service

![70 6][]

将keepalved加进系统服务并且在/etc/sysconfig/写入配置文件以及将keepalived命令加进/usr/bin 和 /usr/sbin（下面命令中注释在使用中忽略）：

cp /usr/local/keepalived/keepalived/etc/init.d/keepalived /etc/init.d/keepalived  #最好查看下keeplaived是否有执行权限
    mkdir /etc/keepalived/
    cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
    cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
    cp /usr/local/sbin/keepalived /usr/bin/  #方便以后查看keepalived的版本，用keepalived  -v可以查看
    cp /usr/local/sbin/keepalived /usr/sbin/

拷贝完成后修改配置文件：

vim /etc/keepalived/keepalived.conf

**以下是配置的内容，根据实际修改，不可直接复制**

1）172.17.1.136主机

! Configuration File for keepalived
    
    global_defs {
       router_id LVS_DEVEL
       vrrp_skip_check_adv_addr
       #vrrp_strict
       vrrp_garp_interval 0
       vrrp_gna_interval 0
    }
    
    vrrp_instance VI_1 {
        state BACKUP
        interface ens33
        virtual_router_id 51
        priority 100
        advert_int 1
        nopreempt #不抢占模式，只有优先级高的机器上设置即可，优先级低的机器可不设置
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {
            172.17.3.210
        }
    }
    
    virtual_server 172.17.3.210 3306 {
        delay_loop 2
        lb_algo wrr
        lb_kind DR
        persistence_timeout 60 #会话保持时间
        protocol TCP
    
        real_server 172.17.1.136 3306 {
            weight 3
            notify_down /root/shutdown.sh #检测到服务down后执行的脚本
            TCP_CHECK {
                nb_get_retry 3　　　　#重连次数
                connect_timeout 10 #连接超时时间
                delay_before_retry 3 #重连间隔时间
                connect_port 3306 #健康检查端口
            }
        }
    }

2）172.17.3.134主机

! Configuration File for keepalived
    
    global_defs {
       router_id LVS_DEVEL # 标识本节点的字条串,通常为hostname
       vrrp_skip_check_adv_addr
       #vrrp_strict #这个最好注释掉，否则可能造成物理机的浏览器无法访问应用
       vrrp_garp_interval 0
       vrrp_gna_interval 0
    }
    
    vrrp_instance VI_1 {
        #state MASTER
        state BACKUP
        interface eno16777736
        virtual_router_id 51
        priority 100
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress { # Block limited to 20 IP addresses @IP
            172.17.3.210
        }
    }
    
    #虚拟服务器定义块
    virtual_server 172.17.3.210 3306 {
        delay_loop 2
        lb_algo wrr
        lb_kind DR
        persistence_timeout 60
        protocol TCP
    
        real_server 172.17.3.134 {
            weight 3
            notify_down /root/shutdown.sh
            TCP_CHECK {
                nb_get_retry 3　　　　#重连次数
                connect_timeout 10 #连接超时时间
                delay_before_retry 3 #重连间隔时间
                connect_port 3306 #健康检查端口
            }
        }
    }

编写检测服务down后所要执行的脚本shutdown.sh：

vim /root/shtdown.sh

内容为

> \#!/bin/bash
> 
> killall keepalive

*注：此脚本是上面配置文件notify\_down选项所用到的，keepalived使用notify\_down选项来检查real\_server的服务状态，当发现real\_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过killall keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP*

## 2.启动keepalived并查看日志 ##

chkconfig keepalived on
    service keepalived start	#启动服务
    service keepalived stop		#停止服务
    service keepalived restart	#重启服务
    
    systemctl enable keepalived.service		#设置开机启动

启动后查看日志：

tail -f /var/log/messages

![70 7][]

## 3.测试 ##

在远程客户端通过vip登陆测试

mysql -h 172.17.3.210 -uroot -p

![70 8][]

## 4.其他 ##

1）修改日志位置

修改日志文件存放位置/var/log/keepalived/keepalived.log

echo 'local0.* /var/log/keepalived/keepalived.log' >>/etc/rsyslog.conf

重启日志服务

systemctl restart rsyslog

2）keepalived配置参数

> **全局定义块**
> 
> 1、email通知（notification\_email、smtp\_server、smtp\_connect\_timeout）：用于服务有故障时发送邮件报警，可选项，不建议用。需要系统开启sendmail服务，建议用第三独立监控服务，如用nagios全面监控代替。
> 
> 2、lvs\_id：lvs负载均衡器标识，在一个网络内，它的值应该是唯一的。
> 
> 3、router\_id：用户标识本节点的名称，通常为hostname
> 
> 4、花括号｛｝：用来分隔定义块，必须成对出现。如果写漏了，keepalived运行时不会得到预期的结果。由于定义块存在嵌套关系，因此很容易遗漏结尾处的花括号，这点需要特别注意。

> **VRRP实例定义块**
> 
> vrrp\_sync\_group：同步vrrp级，用于确定失败切换（FailOver）包含的路由实例个数。即在有2个负载均衡器的场景，一旦某个负载均衡器失效，需要自动切换到另外一个负载均衡器的实例是哪
> 
> group：至少要包含一个vrrp实例，vrrp实例名称必须和vrrp\_instance定义的一致
> 
> vrrp\_instance：vrrp实例名
> 
> 1> state：实例状态，只有MASTER 和 BACKUP两种状态，并且需要全部大写。抢占模式下，其中MASTER为工作状态，BACKUP为备用状态。当MASTER所在的服务器失效时，BACKUP所在的服务会自动把它的状态由BACKUP切换到MASTER状态。当失效的MASTER所在的服务恢复时，BACKUP从MASTER恢复到BACKUP状态。
> 
> 2> interface：对外提供服务的网卡接口，即VIP绑定的网卡接口。如：eth0，eth1。当前主流的服务器都有2个或2个以上的接口（分别对应外网和内网），在选择网卡接口时，一定要核实清楚。
> 
> 3> mcast\_src\_ip：本机IP地址
> 
> 4> virtual\_router\_id：虚拟路由的ID号，每个节点设置必须一样，可选择IP最后一段使用，相同的 VRID 为一个组，他将决定多播的 MAC 地址。
> 
> 5> priority：节点优先级，取值范围0～254，MASTER要比BACKUP高
> 
> 6> advert\_int：MASTER与BACKUP节点间同步检查的时间间隔，单位为秒
> 
> 7> lvs\_sync\_daemon\_inteface：负载均衡器之间的监控接口,类似于 HA HeartBeat 的心跳线。但它的机制优于 Heartbeat，因为它没有“裂脑”这个问题,它是以优先级这个机制来规避这个麻烦的。在 DR 模式中，lvs\_sync\_daemon\_inteface与服务接口interface使用同一个网络接口
> 
> 8> authentication：验证类型和验证密码。类型主要有 PASS、AH 两种，通常使用PASS类型，据说AH使用时有问题。验证密码为明文，同一vrrp 实例MASTER与BACKUP使用相同的密码才能正常通信。
> 
> 9> smtp\_alert：有故障时是否激活邮件通知
> 
> 10> nopreempt：禁止抢占服务。默认情况，当MASTER服务挂掉之后，BACKUP自动升级为MASTER并接替它的任务，当MASTER服务恢复后，升级为MASTER的BACKUP服务又自动降为BACKUP，把工作权交给原MASTER。当配置了nopreempt，MASTER从挂掉到恢复，不再将服务抢占过来。
> 
> 11> virtual\_ipaddress：虚拟IP地址池，可以有多个IP，每个IP占一行，不需要指定子网掩码。注意：这个IP必须与我们的设定的vip保持一致。

> ** 虚拟服务器virtual\_server定义块**
> 
> virtual\_server：定义一个虚拟服务器，这个ip是virtual\_ipaddress中定义的其中一个，后面一个空格，然后加上虚拟服务的端口号。
> 
> 1> delay\_loop：健康检查时间间隔，单位：秒
> 
> 2> lb\_algo：负载均衡调度算法，互联网应用常用方式为wlc或rr
> 
> 3> lb\_kind：负载均衡转发规则。包括DR、NAT、TUN 3种，一般使用路由（DR）转发规则。
> 
> 4> persistence\_timeout：http服务会话保持时间，单位：秒
> 
> 5> protocol：转发协议，分为TCP和UDP两种
> 
> real\_server：真实服务器IP和端口，可以定义多个
> 
> 1> weight：负载权重，值越大，转发的优先级越高
> 
> 2> notify\_down：服务停止后执行的脚本
> 
> 3> TCP\_CHECK：服务有效性检测
> 
> \* connect\_port：服务连接端口
> 
> \* connect\_timeout：服务连接超时时长，单位：秒
> 
> \* nb\_get\_retry：服务连接失败重试次数
> 
> \* delay\_before\_retry：重试连接间隔，单位：秒

--------------------

END

[CentOS7_mysql8.0.12]: https://blog.csdn.net/qq_38591756/article/details/82958333
[70]: /images/20220509/d3b85cee5ed64a7cb4a752d9d73474a0.png
[70 1]: /images/20220509/ddbad026e2214181810ac60a780a2570.png
[70 2]: /images/20220509/ebbd079ceb4e46a3a18b73ee78b45c91.png
[70 3]: /images/20220509/9f8a594aba3b41c0821f76eccdb4011e.png
[70 4]: /images/20220509/654d9c63296d4cf5bef013d6ddc97dee.png
[70 5]: /images/20220509/73d4cafd08644c1392f0d743091a1437.png
[http_www.keepalived.org]: http://www.keepalived.org
[70 6]: /images/20220509/97a0f66c48614a7e9edaf764b887b15b.png
[70 7]: /images/20220509/2280c1fe7693458986a8417ab2cca256.png
[70 8]: /images/20220509/85b57ac0e51e4781b57bf27b5cb6873c.png