MySQL高可用之mysql+keepalived

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

主主配置

  • 环境准备：

    OS：CentOS7

    master：192.168.30.129

    backup：192.168.30.130

    VIP：192.168.30.131

  • 拷贝playbook安装mysql：

在master和backup上安装mysql

  1. # cd ansible-playbook/mysql/
  3. # 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 

    1. -> master_host='192.168.30.130',                #这里填backup的IP
    2. -> master_user='replication',
    3. -> master_password='replication',
    4. -> master_log_file='mysql-bin.000001',
    5. -> 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

    1. -> master_host='192.168.30.129',                #这里填master的IP
    2. -> master_user='replication',
    3. -> master_password='replication',
    4. -> master_log_file='mysql-bin.000001',
    5. -> master_log_pos=618;

    mysql> start slave;

  • 分别查看同步状态：

master查看：

  1. mysql> show slave status\G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.30.130
  5.                   Master_User: replication
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000001
  9.           Read_Master_Log_Pos: 1082
  10.                Relay_Log_File: test2-relay-bin.000002
  11.                 Relay_Log_Pos: 784
  12.         Relay_Master_Log_File: mysql-bin.000001
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes

backup查看：

  1. mysql> show slave status\G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.30.129
  5.                   Master_User: replication
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000001
  9.           Read_Master_Log_Pos: 618
  10.                Relay_Log_File: test3-relay-bin.000002
  11.                 Relay_Log_Pos: 320
  12.         Relay_Master_Log_File: mysql-bin.000001
  13.              Slave_IO_Running: Yes
  14.             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上查看：

  1. mysql> show databases;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. | mysql              |
  7. | performance_schema |
  8. | sys                |
  9. | test               |
  10. +--------------------+
  12. mysql> use test;
  14. mysql> show tables;
  15. +----------------+
  16. | Tables_in_test |
  17. +----------------+
  18. | user           |
  19. +----------------+
  21. mysql> select number,name from user;
  22. +--------+------+
  23. | number | name |
  24. +--------+------+
  25. |      1 | lzx  |
  26. +--------+------+

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

配置keepalived实现热备

  • master安装keepalived并配置：

    yum install -y keepalived

    vim /etc/keepalived/keepalived.conf

    ! Configuration File for keepalived

    global_defs {
    notification_email { 

    1.  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 { 

    1. state BACKUP
    2. interface ens33             #根据实际网络接口进行更改
    3. virtual_router_id 51
    4. priority 100            #优先级，master设置为100
    5. advert_int 1
    6. nopreempt               #不主动抢占资源，只在master上设置
    7. authentication { 
    8.     auth_type PASS
    9.     auth_pass 1111
    10. }
    11. virtual_ipaddress { 
    12.     192.168.30.131
    13. }

    }

    virtual_server 192.168.30.131 3306 { 

    1. delay_loop 2
    2. #lb_algo rr
    3. #lb_kind NAT
    4. persistence_timeout 50
    5. protocol TCP
    7. real_server 192.168.30.129 3306 {                #检测本地mysql
    8.     weight 3
    9.     notify_down /tmp/mysql.sh               #当mysql服务down时，执行此脚本，杀死keepalived实现切换
    10.     TCP_CHECK { 
    11.         connect_timeout 3
    12.         nb_get_retry 3
    13.         delay_before_retry 3
    14.     }
    15. }

    }

  • backup安装keepalived并配置：

    yum install -y keepalived

    vim /etc/keepalived/keepalived.conf

    ! Configuration File for keepalived

    global_defs {
    notification_email { 

    1.  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 { 

    1. state BACKUP
    2. interface ens33             #根据实际网络接口进行更改
    3. virtual_router_id 51
    4. priority 90             #优先级，backup设置为90
    5. advert_int 1
    6. #nopreempt #主动抢占资源
    7. authentication { 
    8.     auth_type PASS
    9.     auth_pass 1111
    10. }   
    11. virtual_ipaddress { 
    12.     192.168.30.131
    13. }

    }

    virtual_server 192.168.30.131 3306 { 

    1. delay_loop 2
    2. #lb_algo rr
    3. #lb_kind NAT
    4. persistence_timeout 50
    5. protocol TCP
    7. real_server 192.168.30.130 3306 {                #检测本地mysql
    8.     weight 3
    9.     notify_down /tmp/mysql.sh               #当mysql服务down时，执行此脚本，杀死keepalived实现切换
    10.     TCP_CHECK {  
    11.         connect_timeout 3
    12.         nb_get_retry 3
    13.         delay_before_retry 3
    14.     }   
    15. }

    }

  • 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连接，看是否连接成功。

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

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

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

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

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

master上查看是否有VIP：

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

可以看到VIP在master上。

停掉master上mysql服务：

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

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

到backup上查看是否有VIP：

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

可以看到VIP在backup上。

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

  1. Apr  8 15:27:16 test2 systemd: Stopping MySQL Server...
  2. Apr  8 15:27:16 test2 Keepalived_healthcheckers[11048]: TCP connection to [192.168.30.129]:3306 failed.
  3. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: TCP connection to [192.168.30.129]:3306 failed.
  4. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Check on service [192.168.30.129]:3306 failed after 1 retry.
  5. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Removing service [192.168.30.129]:3306 from VS [192.168.30.131]:3306
  6. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: IPVS (cmd 1160, errno 2): No such destination
  7. 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
  8. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Lost quorum 1-0=1 > 0 for VS [192.168.30.131]:3306
  9. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Remote SMTP server [127.0.0.1]:25 connected.
  10. Apr  8 15:27:19 test2 Keepalived_vrrp[11049]: VRRP_Instance(VI_1) sent 0 priority
  11. Apr  8 15:27:19 test2 Keepalived_vrrp[11049]: VRRP_Instance(VI_1) removing protocol VIPs.
  12. Apr  8 15:27:19 test2 Keepalived[11047]: Stopping
  13. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: IPVS (cmd 1156, errno 2): No such file or directory
  14. Apr  8 15:27:19 test2 Keepalived_healthcheckers[11048]: Stopped
  15. Apr  8 15:27:20 test2 Keepalived_vrrp[11049]: Stopped
  16. Apr  8 15:27:20 test2 Keepalived[11047]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
  17. Apr  8 15:27:27 test2 systemd: Stopped MySQL Server.
  1. 恢复master服务器故障，看是否主动抢占资源，成为活动服务器。

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

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

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

不过需要注意的是：

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

也就是说：

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

发表评论

表情：
评论列表 （有 0 条评论，472人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql高可用

    中小型企业mysql一般使用什么l高可用方案 传感器是一种检测装置，能感受到被测量的信息，并能将感受到的信息，按一定规律变换成为电信号或其他所需形式的信息输出，以满足信息

    忘是亡心i忘是亡心i/ 0 赞/ 195 阅读

    相关 MySQL高可用

    [MySQL高可用][MySQL] MySQL数据库简单介绍 MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一。但不可忽略的是它本身的功能的确很强大。随着

    淡淡的烟草味﹌淡淡的烟草味﹌/ 0 赞/ 506 阅读