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

mysql> create user 'repl'@'%' identified  by '123123';
mysql> grant replication slave on *.* to 'repl'@'%';
mysql> flush privileges;
mysql> show master status;

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

开启复制
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查询数据是否同步，确定同步是否正常。

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';

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)

mysql> create user 'repl1'@'%' identified  by '123123';
mysql> grant replication slave on *.* to 'repl1'@'%';
mysql> flush privileges;
mysql> show master status;

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

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

yum -y install keepalived
systemctl status keepalived
systemctl start keepalived
systemctl stop 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)
    }
}

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