MySQL 主主复制+KeepAlive实现高可用

MySQL 主主复制+KeepAlive实现高可用

1 环境搭建

节点ip	角色
192.168.147.102	Master1
192.168.147.103	Master2
192.168.147.150	虚拟ip

2. MySQL主主环境搭建

2.1 MySQL的安装

2.1.1 MySQL下载

分别在两台节点上安装MySQL，采用rpm方式安装，首先从MySQL官网下载rpm包。选择对应的系统。

rpm包下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

此处,我们采用rpm方式安装MySQL 5.7.x系列。

使用SecureCRT SFTP工具（或者同类型的XShell中的XFTP）将安装包上传到Linux服务器上。

2.1.2 安装部分依赖

查看Linux上是否已经安装了mysql，如果有，卸载

一般cent os 7默认安装了Mariadb，这里需要卸载

### 查看Mariadb依赖
rpm -qa|grep mariadb
### 卸载
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

安装MySQL 5.7所需要的依赖

 yum -y install libaio perl net-tools

2.1.3 解压MySQL 5.7 安装包

 tar -xvf mysql-5.7.35-1.el7.x86_64.rpm-bundle.tar		

2.1.4 安装MySQL 5.7

 rpm -ivh mysql-community-common-5.7.35-1.el7.x86_64.rpm
 rpm -ivh mysql-community-libs-5.7.35-1.el7.x86_64.rpm
 rpm -ivh mysql-community-client-5.7.35-1.el7.x86_64.rpm
 rpm -ivh mysql-community-server-5.7.35-1.el7.x86_64.rpm 

2.2 初始化MySQL

2.2.1 启动MySQL

# 启动
service mysqld start
# 查看状态
service mysqld status

2.2.2 修改默认启动密码

查看临时密码，得知临时密码如下

[root@bigdata01 ~]# grep password /var/log/mysqld.log 
2021-09-05T06:46:31.997952Z 1 [Note] A temporary password is generated for root@localhost: frDpjttai8?0

# 登录mysql shell
mysql -uroot -p

# 使用临时密码mysql登录
mysql> set password = password("frDpjttai8?0")

2.2.3 开启远程连接，允许远程连接数据库

# 允许远程连接数据库
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Root123456+' WITH GRANT OPTION;
# 刷新权限
mysql> FLUSH PRIVILEGES;

2.2.4 检查防火墙状态

# 关闭防火墙
systemctl stop firewalld
# 查看当前防火墙状态
systemctl status firewalld
# 禁止开机自启防火墙
systemctl disable firewalld

2.3 测试连接

使用 navicat 或其他客户端连接工具连接, 提示连接成功即完远程连接授权

2.4 配置MySQL主主模式

2.4.1 修改MySQL配置文件

首先修改master1（192.168.147.102）的配置文件

vi /etc/my.cnf

# mysql.conf中配置文件
[root@bigdata01 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 配置server-id 每个MySQL实例的server-id都不能相同
server-id=1
# MySQL的日志文件名称
log-bin=mysql_master1
# 作为从库时 更新操作是否写入日志 on:写入 其他数据库以此数据库为主库时才能进行同步
log-slave-updates=on

# MySQL系统库的数据不需要同步
# 同步数据时忽略以下数据库，但是必须在使用use db的情况下才会忽略
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

# 使用通配符忽略MySQL系统库 这样在creae user时也不会进行同步
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%

# MySQL系统库的日志不计入binlog
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

在Master2(192.168.147.103)上也修改my.cnf文件，对刚配置的Master1配置文件稍加修改即可。

# master1机器
scp -r /etc/my.cnf root@bigdata02:/etc/

# 编辑my.cnf
vi /etc/my.cnf
# 需要修改的地方

# 配置master2的server-id
server-id=2
# MySQL的日志文件名称（非强制,建议修改）
log-bin=mysql_master2

完成配置文件修改后，分别在master1和master2上重启MySQL服务。

service mysqld restart

2.4.2 配置主主模式

先配置master1 -> master2 的主从

登录master1的数据库，并执行如下命令

# 对repl_master用户授权备份的权限 此处设置repl_master用户可以在192.168.147.103机器上登录
grant replication slave, replication client on *.* to 'repl_master'@'192.168.147.103' identified by 'Root123456+';
# 刷新权限
flush privileges;
# 查看Master1主节点的状态
show master status;

+----------------------+----------+--------------+-------------------------------------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+----------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql_master1.000001 |      630 |              | information_schema,mysql,performance_schema,sys |                   |
+----------------------+----------+--------------+-------------------------------------------------+-------------------+

此处需要记住bin log文件名称mysql_master1.000001及位置630

然后登录到master2的数据库，执行如下命令

mysql> CHANGE MASTER TO MASTER_HOST='192.168.147.102',MASTER_PORT=3306,MASTER_USER='repl_master', MASTER_PASSWORD='Root123456+',MASTER_LOG_FILE='mysql_master1.000001',MASTER_LOG_POS=630;
# 开启从库
mysql> start slave;
# 查看从库状态
mysql> show slave status \ G

第一次查看从库状态的时候， Slave_IO_Running: Connecting，后排查发现是用于授权repl_master用户的ip设置为master1(192.168.147.102)，故在master2机器上使用repl_master远程登录出错。

# 查看授权用户/ip/权限
select * from user;
# 修改授权host信息
update user set `Host` = '192.168.147.103' where `User`='repl_master';
# 刷新权限
flush privileges;

这样，Master1(192.168.147.102) -> Master2(192.168.147.103)的主从复制就搭建好了。然后，反过来，搭建Master2 -> Master1的主从复制。

先登录master2的数据库，执行如下命令

# 创建备份账号repl_master，并授权网段192.168.147.102
grant replication slave, replication client on *.* to 'repl_master'@'192.168.147.102' identified by 'Root123456+';
# 刷新权限
flush privileges;
# 查看master2节点的状态
mysql> show master status;
+----------------------+----------+--------------+-------------------------------------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+----------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql_master2.000001 |      630 |              | information_schema,mysql,performance_schema,sys |                   |
+----------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

再登录到master1的数据库，执行如下命令

CHANGE MASTER TO MASTER_HOST='192.168.147.103',MASTER_PORT=3306,MASTER_USER='repl_master', MASTER_PASSWORD='Root123456+',MASTER_LOG_FILE='mysql_master2.000001',MASTER_LOG_POS=630;
# 开启从库
start slave;
# 查看从库状态
show slave status \G;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.147.103
                  Master_User: repl_master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_master2.000001
          Read_Master_Log_Pos: 630
               Relay_Log_File: bigdata01-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql_master2.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: information_schema.%,mysql.%,performance_schema.%,sys.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 630
              Relay_Log_Space: 535
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 4a322df3-0ff4-11ec-ba12-000c29c15afc
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

这样，Master2 -> Master1的主从也就搭建好了, 可以使用navicate分别连接master1和master2,验证主主模式搭建是否正常。

3. Keep Alive实现故障切换

3.1 KeepAlive安装

分别在两台节点上安装keepAlived

下载地址：https://www.keepalived.org/download.html

此处选择keepalived-2.0.18版本进行下载。

3.1.1 安装依赖

# 安装gcc libnl/libnl-3
yum -y install gcc libnl libnl-devel  openssl openssl-devel

3.1.2 上传并解压

# 解压
tar -zxvf keepalived-2.0.18.tar.gz -C /data/

3.1.3安装

安装目录及配置文件

# 进入解压后的keepalived-2.0.18目录
cd /data/keepalived-2.0.18/
# 安装
./configure --prefix=/usr/local/keepalived

配置安装目录过程中出现的报错

configure: error: in `/data/keepalived-2.0.18':
configure: error: no acceptable C compiler found in $PATH

安装gcc依赖即可

 yum -y install gcc

configure: error: 
  !!! OpenSSL is not properly installed on your system. !!!
  !!! Can not include OpenSSL headers files.            !!!

安装openssl和openssl-devel：

yum -y install openssl openssl-devel

WARNING - this build will not support IPVS with IPv6. Please install libnl/libnl-3 dev libraries to support IPv6 with IPVS.

安装 libnl/libnl-3

yum -y install libnl libnl-devel  

安装了依赖后需要重新执行./configure --prefix=/usr/local/keepalived

安装

make && make install

3.2 配置系统服务及开机自启

# keepalived启动脚本变量引用文件（安装目录下）
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
# 将keepalived主程序加入到环境变量（安装目录下）
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/keepalived
# keepalived启动脚本（源码目录下），放到/etc/init.d/目录下就可以使用service命令便捷调用
cp /data/keepalived-2.0.18/keepalived/etc/init.d/keepalived /etc/init.d/keepalived
# 将配置文件放到默认路径下
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

配置开机自启

# 添加系统服务
chkconfig --add keepalived
# 添加开机自启 
chkconfig keepalived on

尝试启动

# 启动服务
systemctl start keepalived
# 查看状态
systemctl status keepalived

发现报错systemd[1]: Can't open PID file /run/keepalived.pid (yet?) after start: No such file or directory

使用pkill keepalived 清除进程后重新启动

发现依旧未成功启动。查看输出日志

cat /var/log/message

日志中出现interface eth0等字样，需要对keepalived.conf进行配置

Sep 10 01:31:08 bigdata01 Keepalived_vrrp[52160]: (Line 21) WARNING - interface eth0 for vrrp_instance VI_1 doesn't exist
Sep 10 01:31:08 bigdata01 Keepalived_vrrp[52160]: (Line 30) Default interface eth0 doesn't exist for static address 192.168.200.16.
Sep 10 01:31:08 bigdata01 Keepalived_vrrp[52160]: (Line 31) Default interface eth0 doesn't exist for static address 192.168.200.17.
Sep 10 01:31:08 bigdata01 Keepalived_vrrp[52160]: (Line 32) Default interface eth0 doesn't exist for static address 192.168.200.18.
Sep 10 01:31:08 bigdata01 Keepalived_vrrp[52160]: Non-existent interface specified in configuration
Sep 10 01:31:08 bigdata01 Keepalived_vrrp[52160]: Stopped - used 0.000000 user time, 0.001987 system time

3.3 配置master1上的Keepalived

# 进入配置文件目录
vi /etc/keepalived/keepalived.conf

keepalived中的配置文件内容

! Configuration File for keepalived

global_defs {
   notification_email {
     [e-mail]
     [e-mail]
     [e-mail]
   }
   notification_email_from [e-mail]
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id MASTER1_HA
}

vrrp_script chk_mysql_port {     
    script "/opt/chk_mysql.sh" 
    interval 2
    weight -5  
    fall 2        
    rise 1        
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 101
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.147.150
    }
    
    track_script {               
       chk_mysql_port             
    }
}

编写切换脚本，KeepAlived做心跳检测，如果Master服务停止，那么keepAlived选择kill，Slave的KeepAlived通过心跳发现这个情况，就会将VIP的请求接管。

[root@bigdata01 keepalived-2.0.18]# vi /opt/chk_mysql.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi

chmod 755 /opt/chk_mysql.sh
# 启动keepalived服务
[root@bigdata01 keepalived-2.0.18]# /etc/init.d/keepalived start
Starting keepalived (via systemctl):                       [  OK  ]

检查keepAlived是否启动成功

# 启动
systemctl start keepalived
# 查看状态
systemctl status keepalived
# 查看进程信息
ps -ef |grep keepalived

发现keepalived并未启动，报错信息为systemd: Can't open PID file /run/keepalived.pid (yet?) after start: No such file or directory

修改PID 文件位置

vi /lib/systemd/system/keepalived.service
# 将PID File修改为/var/run/keepalived.pid
PIDFile=/var/run/keepalived.pid

将KeepAlived.service配置改动生效

systemctl daemon-reload

再次启动报错SECURITY VIOLATION - scripts are being executed but script_security not enabled.

表示脚本已经执行，但脚本安全未允许

修改keepalived配置文件，在global_defs配置中添加： enable_script_security

global_defs {
    router_id lb-master
    enable_script_security
}

重新启动，发现KeepAlived已经正常运行。

查看对应的进程信息

[root@bigdata01 opt]# ps -ef |grep keepalived
root      52388      1  0 02:06 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root      52389  52388  0 02:06 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root      52393   1509  0 02:09 pts/0    00:00:00 grep --color=auto keepalived

使用ip addr查看网络信息已经可以在网卡ens33下找到虚拟ip

3.4 配置Master2上的Keepalived

Master2上的KeepAlived与Master1上的内容基本相同，只需修改priority,state, router_id

! Configuration File for keepalived

global_defs {
   notification_email {
     [e-mail]
     [e-mail]
     [e-mail]
   }
   notification_email_from [e-mail]
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id MASTER2_HA
   enable_script_security
}

vrrp_script chk_mysql_port {     
    script "/opt/chk_mysql.sh" 
    interval 2
    weight -5  
    fall 2        
    rise 1        
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 98
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.147.150
    }
    
    track_script {               
       chk_mysql_port             
    }
}

将chk_mysql.sh同样配置在/opt目录下

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    /etc/init.d/keepalived stop
fi

# 权限
chmod 755 /opt/chk_mysql.sh

修改 PIDFile

vi /lib/systemd/system/keepalived.service


[Unit]
Description=LVS and VRRP High Availability Monitor
After=network-online.target syslog.target
Wants=network-online.target

[Service]
Type=forking
PIDFile=/var/run/keepalived.pid
KillMode=process
EnvironmentFile=-/usr/local/keepalived/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

# 生效对pid修改
systemctl daemon-reload

启动KeepAlived

systemctl start keepalived

查看KeepAlived服务状态

systemctl status keepalived

3.5 验证故障切换

使用naviate 连接虚拟ip 提供的mysql服务。

查看当前虚拟ip所处的机器

master1

[root@bigdata01 ~]# ip addr |grep 192.168.147.150
    inet 192.168.147.150/32 scope global ens33

master2

[root@bigdata02 opt]# ip addr |grep 192.168.147.150

此刻虚拟ip在master1机器上，停止master1的mysql服务，验证虚拟ip是否会漂移到master2机器。

systemctl stop mysqld

查看master2节点上的ip信息

[root@bigdata02 opt]# ip addr |grep 192.168.147.150
    inet 192.168.147.150/32 scope global ens33

使用navaite连接虚拟ip 192.168.147.150,发现依旧可以提供服务。

随后，重新启动master1上的mysql服务及keepalived服务。

# 启动MySQL
systemctl start mysqld
# 启动keepalived
systemctl start keepalived

查看虚拟ip信息

[root@bigdata01 opt]# ip addr | grep 192.168.147.150
    inet 192.168.147.150/32 scope global ens33

发现虚拟ip又重新漂移到了master1节点。

至此，完成基于MySQL主主环境+KeepAlived 自动故障迁移。