KeepAlived guarantees Mysql auto-switch

Environmental preparation

Previous articles paved the way for MySQL master slave building:

Article 1: Common settings for Binlog in MySQL

Article 2: MySQL Master-Slave Synchronization-Principle-Practice

Start the two mysql servers for Master and Slave with the following information:

➜  ~  docker ps -a
CONTAINER ID        IMAGE                    COMMAND             CREATED             STATUS              PORTS                     NAMES
8f31266d08fc        docker-mysql-master:v1   "/usr/sbin/init"    49 minutes ago      Up 49 minutes       0.0.0.0:33063->3306/tcp   docker-mysql-client
a579aa381425        docker-mysql-slave:v1    "/usr/sbin/init"    19 hours ago        Up 19 hours         0.0.0.0:33062->3306/tcp   docker-mysql-slave
a40a40c6bde7        docker-mysql-master:v1   "/usr/sbin/init"    19 hours ago        Up 19 hours         0.0.0.0:33061->3306/tcp   docker-mysql-master

#Enter master
➜  ~  docker exec -it 8166c07dd6c7 bash
[root@8166c07dd6c7 /]#

#Enter slave
➜  ~  docker exec -it 208c30295ec9 bash
[root@208c30295ec9 /]#

Master Machine (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 Machine (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;

Validation on Master and Slave machines must synchronize OK with each other.

#And keep the master and slave synchronized
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

SQL validation, where executing scripts separately on Master requires data synchronization to be seen on Slave.

# Master
INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (10, 'chenyuan', 20, 'M');
# Slave
INSERT INTO test.person_01 (id, first_name, age, gender) VALUES (11, 'chenyuan11', 20, 'M');
# 2-sided data consistent on OK
mysql> 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)

Install KeepAlived

Install gcc, gcc-c++, make

yum install gcc gcc-c++ autoconf automake
yum install initscripts -y

Install keepalived on the Maste r and Slave machines respectively

# master
[root@8166c07dd6c7 /]# yum install -y keepalived
...
Complete!
[root@8166c07dd6c7 /]# keepalived -v
Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com>

# slave
[root@208c30295ec9 /]# yum install keepalived
...
Complete!
[root@8166c07dd6c7 /]# keepalived -v
Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com>

Configure KeepAlived

Configure Master machine keepalived

Add shutdown.sh script and assign executable permissions

chmod 755 shutdown.sh

The contents are as follows:

#!/bin/bash
pkill keepalived

Configure keepalived.conf file

[root@8166c07dd6c7 keepalived]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_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
        }
    }
}

Start the keepalived service

[root@a40a40c6bde7 bin]# systemctl start keepalived.service
[root@a40a40c6bde7 bin]# ps aux | grep keepalived
root       494  0.0  0.1 123016  2104 ?        Ss   14:59   0:00 keepalived
root       495  0.0  0.3 125268  7164 ?        S    14:59   0:00 keepalived
root       496  0.0  0.2 125140  5700 ?        S    14:59   0:00 keepalived
root       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
Configure Slave machine keepalived

Add shutdown.sh script and assign executable permissions

chmod 755 shutdown.sh

The contents are as follows:

#!/bin/bash
pkill keepalived

Configure keepalived.conf file

! Configuration File for keepalived

global_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
        }
    }
}

Also start the keepalived service.

Check to see if virtual IP is up

[root@a40a40c6bde7 mysql]# ip addr
1: 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 forever
2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1
    link/ipip 0.0.0.0 brd 0.0.0.0
3: 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 addr
1: 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 forever
2: tunl0@NONE: <NOARP> mtu 1480 qdisc noop state DOWN group default qlen 1
    link/ipip 0.0.0.0 brd 0.0.0.0
3: 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

Thus, 172.17.0.99/32` is now on the master node.

Verification

Log in to the database through the docker-mysql-client machine, and the login success is shown below.

[root@8f31266d08fc bin]# ./mysql -h 172.17.0.99 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 173
Server 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 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>
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.02 sec)

Kill the mysql process directly, similar to machine down s.Then look at the server_id again, and if you briefly lose contact, you can quickly recover.

Process to kill Master:

[root@a40a40c6bde7 mysql]# ps aux | grep mysql
root      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.pid
mysql     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=3306
root      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

Continue to view the server_id on the docker-mysql-client node.

mysql> show variables like 'server_id';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show variables like 'server_id';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    62
Current database: *** NONE ***

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

Finally, one side of the validation that needs to be reversed is to have the Slave machine's mysql service hang up and the VIP switch to the Master node.

Problem encountered

Unable to start keepalived service

Failed to get D-Bus connection: Operation not permitted
docker 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
//Notice here--privileged and/usr/sbin/init

Logon error via vip

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Reference Address

Tags: Programming MySQL Docker yum Oracle

Posted on Wed, 15 Jan 2020 21:08:24 -0500 by powergen