Mysql双主高可用

在aws上使用keepalived做高可用，aws禁止keepalived使用广播的模式，所以使用单播

10.0.5.173        Master-A

10.0.6.7            Master-B

数据库安装

mkdir -p /data/mysql/{conf,data,logs/{binlog,error,slow}}
chmod 777 /data/mysql/logs/* -R

Master-A 配置文件

[mysqld]
server_id=8
gtid-mode=ON
enforce-gtid-consistency=true
binlog-format = ROW
expire_logs_days = 7
binlog_rows_query_log_events = 1
character_set_server = utf8
log-error=/var/logs/error/mysqld_err.log
log-bin=/var/logs/binlog/mysqld_bin
port = 3306
#在默认情况下mysql会阻止主从同步的数据库function的创建,这会导致我们在导入sql文件时如果有创建function或者使用function的语句将会报错。
log-bin-trust-function-creators=1
#步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_increment=2
#设定数据库中自动增长的起点，两台mysql的起点必须不同，这样才能避免两台服务器同步时出现主键冲突
auto_increment_offset=1

slow_query_log_file=/var/logs/slow/slow.log
long_query_time=3

Master-B配置文件

[mysqld]
server_id=66
gtid-mode=ON
enforce-gtid-consistency=true
binlog-format = ROW
expire_logs_days = 7
binlog_rows_query_log_events = 1
character_set_server = utf8
log-error=/var/logs/error/mysqld_err.log
log-bin=/var/logs/binlog/mysqld_bin
port = 3306
#在默认情况下mysql会阻止主从同步的数据库function的创建,这会导致我们在导入sql文件时如果有创建function或者使用function的语句将会报错。
log-bin-trust-function-creators=1
#步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_increment=2
#设定数据库中自动增长的起点，两台mysql的起点必须不同，这样才能避免两台服务器同步时出现主键冲突
auto_increment_offset=2
slow_query_log_file=/var/logs/slow/slow.log
long_query_time=3

启动Master-A，Master-B

docker run \
 --restart=always  \
 -p 3306:3306 \
 --name mysql \
 -v /etc/localtime:/etc/localtime \
 -v /data/mysql/conf:/etc/mysql/conf.d \
 -v /data/mysql/logs:/var/logs \
 -v /data/mysql/data:/var/lib/mysql   \
 -e MYSQL_ROOT_PASSWORD='123456' \
 -d mysql:5.7

建立主从同步

 Master-A创建同步用户

GRANT EXECUTE, REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED by '123456';

Master-B建立到Master-A的主从同步

change master to
        master_host='10.0.6.78',
        master_user='repl',
        master_password='123456',
        master_port=3306,
        master_auto_position=1;
        
start slave;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.5.173
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld_bin.000004
          Read_Master_Log_Pos: 491
               Relay_Log_File: acc5ffdbab53-relay-bin.000004
                Relay_Log_Pos: 706
        Relay_Master_Log_File: mysqld_bin.000004
             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: 491
              Relay_Log_Space: 1175
              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: 8
                  Master_UUID: 39e3b74a-c6f0-11ed-a6af-0242ac110002
             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: 39e3b74a-c6f0-11ed-a6af-0242ac110002:1-6
            Executed_Gtid_Set: 39e3b74a-c6f0-11ed-a6af-0242ac110002:1-6
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Master-A建立到Master-B的主从同步

change master to
        master_host='10.0.6.151',
        master_user='repl',
        master_password='123456',
        master_port=3306,
        master_auto_position=1;
        
start slave;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.6.7
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: 4e991df14ae6-relay-bin.000002
                Relay_Log_Pos: 369
        Relay_Master_Log_File: mysqld_bin.000001
             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: 154
              Relay_Log_Space: 583
              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: 66
                  Master_UUID: c99083b8-c6f0-11ed-ac18-0242ac110002
             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: 39e3b74a-c6f0-11ed-a6af-0242ac110002:1-6
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

2. keepalived建立高可用

安装keepalived
yum -y install keepalived

keepalived配置文件

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }

   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.10.13
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_script check_run {
script "/etc/keepalived/check/check.sh"  ##mysql健康检查脚本
interval 2
weight  -20
fall 2
rise 1
}
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100            #另一台主机的改为90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
 track_script {
 check_run
 }
unicast_src_ip 10.0.5.131         #源IP，即本机IP
unicast_peer {
        10.0.5.93				  #单播目标IP，即目标IP
}
    virtual_ipaddress {
        10.0.5.100/32
    }
    notify_master /etc/keepalived/scripts/add_ip.sh          #当此服务器状态变为MASTER时运行的脚本
    notify_backup /etc/keepalived/scripts/del_ip.sh           #当此服务器状态变为BACKUP时运行的脚本
}

check.sh

#!/bin/bash
mysql_port=`netstat -an | grep ":3306" | awk '$1 == "tcp" && $NF == "LISTEN" {print $0}'| wc -l`
#mysql_con=`docker ps | grep mysql | awk '{print $NF}'`
if [ ${mysql_port} -eq 1 ];then
    exit 0
else
    exit 1
fi

VIP在Master-A时执行的脚本

使用aws命令添加IP，需要先配置aws configure 密钥在IAM中生成

[root@prod-master-a scripts]# cat add_ip.sh 
#!/bin/bash
VIP=10.0.5.100
ENI=eni-01ec39cd052b  ## 这是本机eth0 网卡的ID，另一server不同
LOG=/etc/keepalived/failover.log
`date "+%F %T"`>>${LOG}
aws ec2 assign-private-ip-addresses --network-interface-id $ENI --private-ip-addresses $VIP --allow-reassignment >> ${LOG} 2>&1
ip address add $VIP/32 dev eth0 >> ${LOG} 2>&1
echo -e "##### 完成主的切换 ######\n" >> ${LOG}

VIP在Master-B时执行的脚本

#!/bin/bash
VIP=10.0.5.100
LOG=/etc/keepalived/failover.log
`date "+%F %T"`>>${LOG}
ip address del $VIP/32 dev eth0 >> ${LOG} 2>&1
echo -e "##### 完成${VIP}移除 ######\n" >> ${LOG}

给脚本都添加可执行权限，启动keepalived。启动完成后，停启mysql进行测试