新闻、帮助、产品更新动态

最新的业界新闻,产品系统更新开发动态,帮助教程和活动发布

KeepAlived保证Mysql主从自动切换

发布日:2021-11-04 14:36       阅读数:

环境准备
前面有几篇文章对于MySQL主从搭建做了一些铺垫:
 
文章一:MySQL中Binlog的常用设置
 
文章二:MySQL主从同步-原理&实践篇
 
先启动Master与Slave的2台mysql服务器,具体信息如下:
~  docker ps -aCONTAINER ID        IMAGE                    COMMAND             CREATED             STATUS              PORTS                     NAMES8f31266d08fc        docker-mysql-master:v1   "/usr/sbin/init"    49 minutes ago      Up 49 minutes       0.0.0.0:33063->3306/tcp   docker-mysql-clienta579aa381425        docker-mysql-slave:v1    "/usr/sbin/init"    19 hours ago        Up 19 hours         0.0.0.0:33062->3306/tcp   docker-mysql-slavea40a40c6bde7        docker-mysql-master:v1   "/usr/sbin/init"    19 hours ago        Up 19 hours         0.0.0.0:33061->3306/tcp   docker-mysql-master#进入master➜  ~  docker exec -it 8166c07dd6c7 bash[root@8166c07dd6c7 /]##进入slave➜  ~  docker exec -it 208c30295ec9 bash[root@208c30295ec9 /]#
Master机器(172.17.0.2)
create user 'master_account'@'%' identified by '123456';grant replication slave on *.* to 'master_account'@'%';flush privileges;change master to master_host='172.17.0.3',master_user='slave_account',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120;
Slave机器(172.17.0.3)
create user 'slave_account'@'%' identified by '123456';grant replication slave on *.* to 'slave_account'@'%';flush privileges;change master to master_host='172.17.0.2',master_user='master_account',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=862;
分别在Master与Slave机器验证,必须是互相同步OK的。
#并且保证主从是同步的mysql> show slave status\G;Slave_IO_Running: YesSlave_SQL_Running: Yes
SQL验证,分别在Master执行脚本需要在Slave上看到数据同步。
# MasterINSERT INTO test.person_01 (id, first_name, age, gender) VALUES (10, 'chenyuan', 20, 'M');# SlaveINSERT INTO test.person_01 (id, first_name, age, gender) VALUES (11, 'chenyuan11', 20, 'M');# 2边数据一致就OKmysql> select * from person_01;+------+------------+------+--------+| id   | first_name | age  | gender |+------+------------+------+--------+|    1 | Bob        |   25 | M      ||    2 | Jane       |   20 | F      ||    3 | Jack       |   30 | M      ||    4 | Bill       |   32 | M      ||    5 | Nick       |   22 | M      ||    6 | Kathy      |   18 | F      ||    7 | Steve      |   36 | M      ||    8 | Anne       |   25 | F      ||    1 | Vernon     |  300 | M      ||   10 | chenyuan   |   20 | M      ||   11 | chenyuan11 |   20 | M      |+------+------------+------+--------+11 rows in set (0.00 sec)
安装KeepAlived
安装好gcc,gcc-c++,make
yum install gcc gcc-c++ autoconf automakeyum install initscripts -y
分别在Maste机器、Slave机器安装好keepalived
# master[root@8166c07dd6c7 /]# yum install -y keepalived...Complete![root@8166c07dd6c7 /]# keepalived -vKeepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com># slave[root@208c30295ec9 /]# yum install keepalived...Complete![root@8166c07dd6c7 /]# keepalived -vKeepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com>
配置KeepAlived
配置Master机器keepalived
新增shutdown.sh脚本,并且赋值可以执行权限
chmod 755 shutdown.sh
内容如下:
#!/bin/bashpkill keepalived
配置keepalived.conf文件
[root@8166c07dd6c7 keepalived]# vi /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {   router_id HA_MySQL}vrrp_instance VI_1 {    state BACKUP    interface eth0    virtual_router_id 51    priority 100    advert_int 1    nopreempt    authentication {        auth_type PASS        auth_pass chenyuan    }    virtual_ipaddress {        172.17.0.4    }}virtual_server 172.17.0.99 3306 {    delay_loop 2    lb_algo wrr    lb_kind DR    persistence_timeout 50    protocol TCP    real_server 172.17.0.99 3306 {        weight 3        notify_down /etc/keepalived/bin/shutdown.sh        TCP_CHECK {            connect_timeout 3            nb_get_retry 3            delay_before_retry 3            connect_port 3306        }    }}
启动好keepalived服务
[root@a40a40c6bde7 bin]# systemctl start keepalived.service[root@a40a40c6bde7 bin]# ps aux | grep keepalivedroot       494  0.0  0.1 123016  2104 ?        Ss   14:59   0:00 keepalivedroot       495  0.0  0.3 125268  7164 ?        S    14:59   0:00 keepalivedroot       496  0.0  0.2 125140  5700 ?        S    14:59   0:00 keepalivedroot       515  0.0  0.1  12532  2164 pts/1    S+   14:59   0:00 grep --color=auto keepalived[root@a40a40c6bde7 bin]# systemctl stop keepalived.service
配置Slave机器keepalived
新增shutdown.sh脚本,并且赋值可以执行权限
chmod 755 shutdown.sh
内容如下:
#!/bin/bashpkill keepalived
配置keepalived.conf文件
! Configuration File for keepalivedglobal_defs {   router_id HA_MySQL}vrrp_instance VI_1 {    state BACKUP    interface eth0    virtual_router_id 51    priority 90    advert_int 1    # nopreempt    authentication {        auth_type PASS        auth_pass chenyuan    }    virtual_ipaddress {        172.17.0.99    }}virtual_server 172.17.0.99 3306 {    delay_loop 2    lb_algo wrr    lb_kind DR    persistence_timeout 50    protocol TCP    real_server 172.17.0.3 3306 {        weight 3        notify_down /etc/keepalived/bin/shutdown.sh        TCP_CHECK {            connect_timeout 3            nb_get_retry 3            delay_before_retry 3            connect_port 3306        }    }}
同样也是启动好keepalived服务。
 
查看虚拟IP是否已经起来
[root@a40a40c6bde7 mysql]# ip addr1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1    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 forever2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1    link/ipip 0.0.0.0 brd 0.0.0.03: ip6tnl0@NONE: <NOARP> mtu 1452 qdisc noop state DOWN group default qlen 1    link/tunnel6 :: brd ::21: eth0@if22: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default    link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff link-netnsid 0    inet 172.17.0.2/16 brd 172.17.255.255 scope global eth0       valid_lft forever preferred_lft forever    inet 172.17.0.99/32 scope global eth0       valid_lft forever preferred_lft forever
[root@a579aa381425 support-files]# ip addr1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1    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 forever2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1    link/ipip 0.0.0.0 brd 0.0.0.03: ip6tnl0@NONE: <NOARP> mtu 1452 qdisc noop state DOWN group default qlen 1    link/tunnel6 :: brd ::23: eth0@if24: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default    link/ether 02:42:ac:11:00:03 brd ff:ff:ff:ff:ff:ff link-netnsid 0    inet 172.17.0.3/16 brd 172.17.255.255 scope global eth0       valid_lft forever preferred_lft forever
由此可见,现在172.17.0.99/32`是在master节点上。
 
验证
通过docker-mysql-client机器来登录数据库,下面显示登录成功。
[root@8f31266d08fc bin]# ./mysql -h 172.17.0.99 -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 173Server version: 5.6.45-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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 1     |+---------------+-------+1 row in set (0.02 sec)
把mysql进程直接杀掉,类似于机器down的情况。然后再次查看server_id,短暂的失去联系,即可很快的恢复。
 
杀掉Master的进程:
[root@a40a40c6bde7 mysql]# ps aux | grep mysqlroot      2559  0.0  0.1  15268  2952 pts/2    S    10:13   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/a40a40c6bde7.pidmysql     2859  0.2 23.0 1686996 471820 pts/2  Sl   10:13   0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=a40a40c6bde7.err --pid-file=/usr/local/mysql/data/a40a40c6bde7.pid --socket=/tmp/mysql.sock --port=3306root      2909  0.0  0.1  12532  2084 pts/2    S+   10:27   0:00 grep --color=auto mysql[root@a40a40c6bde7 mysql]# kill -9 2559[root@a40a40c6bde7 mysql]# kill -9 2859
在docker-mysql-client节点上继续查看server_id。
mysql> show variables like 'server_id';ERROR 2013 (HY000): Lost connection to MySQL server during querymysql> show variables like 'server_id';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    62Current database: *** NONE ***mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 2     |+---------------+-------+1 row in set (0.00 sec)
最后还需要反过来验证一边,就是让Slave机器的mysql服务挂掉,让VIP切换到Master节点去。
 
遇到问题
不能启动keepalived服务
Failed to get D-Bus connection: Operation not permitteddocker run -itd --name docker-mysql-slave --privileged -v /Users/chenyuan/Data/docker/mysql-data-slave:/usr/local/mysql -v /Users/chenyuan/Tools:/root/Tools -e MYSQL_ROOT_PASSWORD=root -p 33062:3306 docker-mysql-slave:v1 /usr/sbin/init注意这里的--privileged 与 /usr/sbin/init
通过vip登录报错
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;FLUSH PRIVILEGES;
参考地址
https://www.jianshu.com/p/8a5308888ef1
https://www.jianshu.com/p/f693b5b08016
https://blog.csdn.net/shiyu1157758655/article/details/78672110
https://blog.csdn.net/u010533511/article/details/88168410







 




























编辑:航网科技   来源:腾讯云

本文版权归原作者所有 转载请注明出处

联系我们

客服部:深圳市龙华区龙胜商业大厦5楼B5区

业务部:深圳市南山区讯美科技广场2栋12楼1202

资质证书

  • Copyright © 2011-2020 www.hangw.com. All Rights Reserved 深圳航网科技有限公司 版权所有 增值电信业务经营许可证:粤B2-20201122 - 粤ICP备14085080号

    在线客服

    微信扫一扫咨询客服


    全国免费服务热线
    0755-36300002

    返回顶部