MySQL高可用之mysql+keepalived

Myth丶恋晨 2022-02-22 11:48 310阅读 0赞

### 主主配置 ###

*  环境准备：

OS：CentOS7
    
    master：192.168.30.129
    
    backup：192.168.30.130
    
    VIP：192.168.30.131

*  拷贝playbook安装mysql：

在master和backup上安装mysql

# cd ansible-playbook/mysql/
    
    # ansible-playbook mysql.yml

安装完后自动启动，mysql root密码为123456789。

*  修改配置文件：

# vim /etc/my.cnf #添加
    
    server_id = 1           # backup上设置为2
    log-bin = /data/mysql/mysql-bin
    log-bin-index=/data/mysql/my-bin.index
    binlog-ignore-db = mysql,information_schema             #忽略写入binlog日志的库
    auto-increment-increment = 2            #字段变化增量值
    auto-increment-offset = 1           #初始字段ID为1
    slave-skip-errors = all             #忽略所有复制产生的错误
    
    # systemctl restart mysqld

*  master上查看log bin日志和pos位置：

# mysql -uroot -p123456789
    
    mysql> GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.30.%' IDENTIFIED  BY 'replication';
    
    mysql> flush  privileges;
    
    mysql> show master status;
    +------------------+----------+--------------+--------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
    +------------------+----------+--------------+--------------------------+-------------------+
    | mysql-bin.000001 |      618 |              | mysql,information_schema |                   |
    +------------------+----------+--------------+--------------------------+-------------------+

*  master配置如下：

# mysql -uroot -p123456789
    
    mysql> change master to 
        -> master_host='192.168.30.130',                #这里填backup的IP
        -> master_user='replication',
        -> master_password='replication',
        -> master_log_file='mysql-bin.000001',
        -> master_log_pos=618;
    
    mysql> start slave;

*  backup配置如下：

# mysql -uroot -p123456789
    
    mysql> GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.30.%' IDENTIFIED  BY 'replication';
    
    mysql> flush  privileges;
    
    mysql> change master to
        -> master_host='192.168.30.129',                #这里填master的IP
        -> master_user='replication',
        -> master_password='replication',
        -> master_log_file='mysql-bin.000001',
        -> master_log_pos=618;
    
    mysql> start slave;

*  分别查看同步状态：

master查看：

mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.30.130
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 1082
                   Relay_Log_File: test2-relay-bin.000002
                    Relay_Log_Pos: 784
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

backup查看：

mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.30.129
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 618
                   Relay_Log_File: test3-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

`Slave_IO`和`Slave_SQL`是YES说明主主同步成功。

*  master上插入数据测试：

mysql> create database test;
    
    mysql> use test;
    
    mysql> create table user (number INT(10),name VARCHAR(255));
    
    mysql> insert into user values(01,'lzx');
    
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | user           |
    +----------------+

backup上查看：

可以看到已经成功同步过去，同样在backup插入到user表数据，一样同步过去，双主配置没有问题。

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

### 配置keepalived实现热备 ###

*  master安装keepalived并配置：

# yum install -y keepalived
    
    # vim /etc/keepalived/keepalived.conf
    
    ! Configuration File for keepalived
    
    global_defs { 
       notification_email { 
         lzx@test.com
       }
       notification_email_from admin@test.com
       smtp_server 127.0.0.1
       smtp_connect_timeout 30
       router_id MYSQL_HA
    }
    
    vrrp_instance VI_1 { 
        state BACKUP
        interface ens33             #根据实际网络接口进行更改
        virtual_router_id 51
        priority 100            #优先级，master设置为100
        advert_int 1
        nopreempt               #不主动抢占资源，只在master上设置
        authentication { 
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress { 
            192.168.30.131
        }
    }
    
    virtual_server 192.168.30.131 3306 { 
        delay_loop 2
        #lb_algo rr
        #lb_kind NAT
        persistence_timeout 50
        protocol TCP
    
        real_server 192.168.30.129 3306 {                #检测本地mysql
            weight 3
            notify_down /tmp/mysql.sh               #当mysql服务down时，执行此脚本，杀死keepalived实现切换
            TCP_CHECK { 
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }
        }
    }

*  backup安装keepalived并配置：

# yum install -y keepalived
    
    # vim /etc/keepalived/keepalived.conf
    
    ! Configuration File for keepalived
    
    global_defs { 
       notification_email { 
         lzx@test.com
       }
       notification_email_from admin@test.com
       smtp_server 127.0.0.1
       smtp_connect_timeout 30
       router_id MYSQL_HA
    }
    
    vrrp_instance VI_1 { 
        state BACKUP
        interface ens33             #根据实际网络接口进行更改
        virtual_router_id 51
        priority 90             #优先级，backup设置为90
        advert_int 1
        #nopreempt #主动抢占资源
        authentication { 
            auth_type PASS
            auth_pass 1111
        }   
        virtual_ipaddress { 
            192.168.30.131
        }   
    }   
    
    virtual_server 192.168.30.131 3306 { 
        delay_loop 2
        #lb_algo rr
        #lb_kind NAT
        persistence_timeout 50
        protocol TCP
        
        real_server 192.168.30.130 3306 {                #检测本地mysql
            weight 3
            notify_down /tmp/mysql.sh               #当mysql服务down时，执行此脚本，杀死keepalived实现切换
            TCP_CHECK {  
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
            }   
        }   
    }

*  master和backup上编辑mysql.sh：

# vim /tmp/mysql.sh
    
    #!/bin/bash
    pkill keepalived
    
    # chmod +x !$
    
    # systemctl start keepalived

*  两台mysql服务器授权允许root远程登录：

# mysql -uroot -p123456789
    
    mysql> grant all on *.* to 'root'@'192.168.30.%' identified by '123456789';
    
    mysql> flush privileges;

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

### 测试高可用 ###

1.  通过mysql客户端通过VIP连接，看是否连接成功。

这里我用同网段的另一台机器，连接测试：

# mysql -h192.168.30.131 -uroot -p123456789
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 114
    Server version: 5.7.25-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> select * from test.user;
    +--------+------+
    | number | name |
    +--------+------+
    |      1 | lzx  |
    +--------+------+
    1 row in set (0.01 sec)

可以看到，连接成功，且查询数据没有问题。

1.  停止master上mysql服务，是否能正常切换到backup上。

可以使用`ip addr`命令来查看VIP在哪台服务器上。

master上查看是否有VIP：

# ip addr
    1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
        link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
        inet 127.0.0.1/8 scope host lo
           valid_lft forever preferred_lft forever
        inet6 ::1/128 scope host 
           valid_lft forever preferred_lft forever
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
        link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
        inet 192.168.30.129/24 brd 192.168.30.255 scope global noprefixroute ens33
           valid_lft forever preferred_lft forever
        inet 192.168.30.131/32 scope global ens33
           valid_lft forever preferred_lft forever
        inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute 
           valid_lft forever preferred_lft forever

可以看到VIP在master上。

停掉master上mysql服务：

# systemctl stop mysqld
    
    # ps axu |grep keepalived
    root      11074  0.0  0.0 112708   988 pts/1    S+   15:28   0:00 grep --color=autokeepalived
    
    # ip addr
    1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
        link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
        inet 127.0.0.1/8 scope host lo
           valid_lft forever preferred_lft forever
        inet6 ::1/128 scope host 
           valid_lft forever preferred_lft forever
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
        link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
        inet 192.168.30.129/24 brd 192.168.30.255 scope global noprefixroute ens33
           valid_lft forever preferred_lft forever
        inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute 
           valid_lft forever preferred_lft forever

可以看到，keepalived在mysql服务停掉之后也被停掉，VIP不在master上。

到backup上查看是否有VIP：

# ip addr
    1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
        link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
        inet 127.0.0.1/8 scope host lo
           valid_lft forever preferred_lft forever
        inet6 ::1/128 scope host 
           valid_lft forever preferred_lft forever
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
        link/ether 00:0c:29:33:80:d5 brd ff:ff:ff:ff:ff:ff
        inet 192.168.30.130/24 brd 192.168.30.255 scope global noprefixroute ens33
           valid_lft forever preferred_lft forever
        inet 192.168.30.131/32 scope global ens33
           valid_lft forever preferred_lft forever
        inet6 fe80::4b20:2e16:a957:f9a1/64 scope link noprefixroute 
           valid_lft forever preferred_lft forever

可以看到VIP在backup上。

查看`/var/log/messages`日志，可以看到主备切换过程：

Apr  8 15:27:16 test2 systemd: Stopping MySQL Server...
    Apr  8 15:27:16 test2 Keepalived_healthcheckers[11048]: TCP connection to [192.168.30.129]:3306 failed.
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: TCP connection to [192.168.30.129]:3306 failed.
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Check on service [192.168.30.129]:3306 failed after 1 retry.
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Removing service [192.168.30.129]:3306 from VS [192.168.30.131]:3306
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: IPVS (cmd 1160, errno 2): No such destination
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Executing [/tmp/mysql.sh] for service [192.168.30.129]:3306 in VS [192.168.30.131]:3306
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Lost quorum 1-0=1 > 0 for VS [192.168.30.131]:3306
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Remote SMTP server [127.0.0.1]:25 connected.
    Apr  8 15:27:19 test2 Keepalived_vrrp[11049]: VRRP_Instance(VI_1) sent 0 priority
    Apr  8 15:27:19 test2 Keepalived_vrrp[11049]: VRRP_Instance(VI_1) removing protocol VIPs.
    Apr  8 15:27:19 test2 Keepalived[11047]: Stopping
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: IPVS (cmd 1156, errno 2): No such file or directory
    Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Stopped
    Apr  8 15:27:20 test2 Keepalived_vrrp[11049]: Stopped
    Apr  8 15:27:20 test2 Keepalived[11047]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
    Apr  8 15:27:27 test2 systemd: Stopped MySQL Server.

1.  恢复master服务器故障，看是否主动抢占资源，成为活动服务器。

master上启动mysql服务和keepalived服务：

# systemctl start mysqld
    
    # systemctl start keepalived
    
    # ip addr
    1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
        link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
        inet 127.0.0.1/8 scope host lo
           valid_lft forever preferred_lft forever
        inet6 ::1/128 scope host 
           valid_lft forever preferred_lft forever
    2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
        link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
        inet 192.168.30.129/24 brd 192.168.30.255 scope global noprefixroute ens33
           valid_lft forever preferred_lft forever
        inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute 
           valid_lft forever preferred_lft forever

可以看到，即使master故障恢复，也没有抢占资源，VIP仍然在backup上，这是因为之前已经配置了master为非抢占模式（nopreempt）。

不过需要注意的是：

**nopreempt这个参数只能用于state为BACKUP的情况，所以在配置的时候要把master和backup的state都设置成BACKUP，这样才会实现keepalived的非抢占模式！**

也就是说：

* 当state状态一个为MASTER，一个为BACKUP的时候，加不加nopreempt这个参数都是一样的效果。即都是根据priority优先级来决定谁抢占vip资源的，是抢占模式！
    
    * 当state状态都设置成BACKUP，如果不配置nopreempt参数，那么也是看priority优先级决定谁抢占vip资源，即也是抢占模式。
    
    * 当state状态都设置成BACKUP，如果配置nopreempt参数，那么就不会去考虑priority优先级了，是非抢占模式！即只有vip当前所在机器发生故障，另一台机器才能接管vip。
      即使优先级高的那一台机器恢复正常后也不会主动抢回vip，只能等到对方发生故障，才会将vip切回来。

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