MySQL双主搭建+keepalived高可用的实现

一、测试环境准备

	节点1	节点2
IP地址	192.168.101.77	192.168.101.79
MySQL版本	8.0.32	8.0.32

二、主从搭建

1.创建复制用户

节点1执行：

mysql> CREATE USER 'repl'@'%' IDENTIFIED  BY '123123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
mysql> show master status;

2.创建复制关系

节点2执行

mysql> 
change master to    
master_host='192.168.101.77',    
MASTER_PORT=3306,    
master_user='repl',  
master_password='123123' ,  
MASTER_AUTO_POSITION=1;

3.开启复制，确认复制是否成功

开启复制
mysql> start slave;
查看复制状态（下面是正常状态）
root@localhost:(none)>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.101.77
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 197
               Relay_Log_File: localhost-relay-bin.000004
                Relay_Log_Pos: 413
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 197
              Relay_Log_Space: 2098
              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: 1
                  Master_UUID: f6ca767b-2144-11ee-80e4-080027a4fdbc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: f6ca767b-2144-11ee-80e4-080027a4fdbc:4-8
            Executed_Gtid_Set: f6ca767b-2144-11ee-80e4-080027a4fdbc:1-8
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

可在节点1插入数据，然后在节点2查询数据是否同步，确定同步是否正常。

4.同步排错

报错1：

Last_IO_Error: error connecting to master 'repl@192.168.101.77:3306' - retry-time: 60 retries: 21 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决方法：

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password  BY '123123';

报错2：

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '75433121-6a95-11ef-8b71-080027123ed0:1-3', and the missing transactions are 'f6ca767b-2144-11ee-80e4-080027a4fdbc:1-3''

解决方法：

root@localhost:(none)>stop slave;
Query OK, 0 rows affected, 1 warning (0.11 sec)

root@localhost:(none)>reset master;
Query OK, 0 rows affected (0.10 sec)

root@localhost:(none)>reset slave;
Query OK, 0 rows affected, 1 warning (0.20 sec)

root@localhost:(none)>set @@GLOBAL.GTID_PURGED='f6ca767b-2144-11ee-80e4-080027a4fdbc:1-3';
Query OK, 0 rows affected (0.06 sec)

root@localhost:(none)>start slave;
Query OK, 0 rows affected, 1 warning (0.10 sec)

三、双主配置

双主配置也就是将上面的主从操作反向操作一遍。

1.创建复制用户

mysql> CREATE USER 'repl1'@'%' IDENTIFIED  BY '123123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'%';
mysql> FLUSH PRIVILEGES;
mysql> show master status;

2.创建复制关系

mysql>
change master to
master_host='192.168.101.79',
MASTER_PORT=3306,
master_user='repl',
master_password='123123' ,
MASTER_AUTO_POSITION=1;

3.开启复制，确认复制是否成功

mysql> start slave;
mysql>show slave status \G;

4.双主测试

节点1插入数据，节点2查询
节点2插入数据，节点1查询

四、keepalived配置

1.keepalived安装、启停操作

yum -y install keepalived
systemctl status keepalived
systemctl start keepalived
systemctl stop keepalived

2.keepalived配置文件

vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    notification_email {
      acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
    notification_email_from Alexandre.Cassen@firewall.loc
   router_id MYCAT_HA # 定义名称
}
vrrp_script chk_mycat_alive {
    script "/etc/keepalived/mysql_check.sh"    # 返回状态码为0表示正常,检测脚本为true;返回状态码非0表示异常,检测脚本为false
    interval 2                                 # 检测脚本执行的间隔，单位是秒
    weight 20
}
vrrp_instance VI_1 {
      state MASTER     #主节点为MASTER，备节点为BACKUP
      interface enp0s3    #根据实际网卡名配置
      virtual_router_id 88
      priority 100
      advert_int 1
      authentication {
         auth_type PASS
         auth_pass qwert
    }
    track_script {
        chk_mycat_alive            # 调用检测脚本
    }
   virtual_ipaddress {
         192.168.101.199/24            # 定义虚拟ip(VIP)
    }
}

3.keepalived配置文件

服务状态检查脚本

cat /etc/keepalived/mysql_check.sh
#!/bin/bash
count=`ps -C mysqld --no-heading|wc -l`
time=$(date "+%Y-%m-%d %H:%M:%S")
if [ $count = 0 ]; then
    echo "$time : count=$count, mysql is not running..." >> /var/log/keepalived_check.log
    exit 1 # 返回1说明脚本非正常执行,mysql不在运行中
else
    echo "$time : count=$count, mysql is running..." >> /var/log/keepalived_check.log
    exit 0 # 返回0说明脚本正常执行,mysql正在运行中
    fi