配置Keepalived实现mysql双主高可用

ooioiiuu

本文为南非蚂蚁的书籍《循序渐进linux-第二版》-8.3.6的读笔记

在DB1和DB2上安装Keepalived软件
先安装编译工具
# yum install gcc* gcc-c++ autoconf automake

上传keepalived-1.2.19.tar.gz至/server/tools目录下
# cd /server/tools

# tar zxvf keepalived-1.2.19.tar.gz

# cd keepalived-1.2.19

# ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64/

# make
# make install
# ln -s /usr/local/sbin/keepalived /sbin/
# chkconfig --add keepalived
# chkconfig --level 35 keepalived on
# cd ../
安装完成后，进入DB1的配置过程
DB1服务器上/etc/keepalived/keepalived.conf的配置内容，其中绿色字体为新增部分
! 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.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_scripts check_mysqld {
   scripts "/etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1"     #检测mysql复制状态的脚本
interval 2
weight 21
   }
vrrp_instance HA_1 {
   state BACKUP       #在DB1和DB2上均配置为BACKUP


interface eth0
   virtual_router_id 80
priority 90
   advert_int 2
   nopereempt         #不抢占模式，只在优先级高的机器上设置即可，优先级低的机器上不设置


authentication {
auth_type PASS
auth_pass qweasdzxc
   }
   
   track_script {
   check_mysqld
   }
   
   virtual_ipaddress {
     10.24.24.110/24 dev eth0        #mysql的对外服务IP，即VIP
   }
}
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.200.16
        192.168.200.17
        192.168.200.18
    }
}
-----------------------------------------
# mkdir /etc/keepalived/mysqlcheck/
其中，/etc/keepalived/mysqlcheck/check_slave.pl文件的内容如下：
# vim /etc/keepalived/mysqlcheck/check_slave.pl
#!/usr/bin/perl -w
use DBI;
use DBD::mysql;
# CONFIG VARIABLES
$SBM = 120;
$db = "ywadmin";
$host = $ARGV[0];
$port = 3306;
$user = "root";
$pw = "jzh0024";
# SQL query
$query = "show slave status";
$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });
if (!defined($dbh)) {
    exit 1;
}
$sqlQuery = $dbh->prepare($query);
$sqlQuery->execute;
$Slave_IO_Running =  "";
$Slave_SQL_Running = "";
$Seconds_Behind_Master = "";
while (my $ref = $sqlQuery->fetchrow_hashref()) {
    $Slave_IO_Running = $ref->{'Slave_IO_Running'};
    $Slave_SQL_Running = $ref->{'Slave_SQL_Running'};
    $Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'};
}
$sqlQuery->finish;
$dbh->disconnect();
if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {
    exit 1;
} else {
    if ( $Seconds_Behind_Master > $SBM ) {
        exit 1;
    } else {
        exit 0;
    }
}
只需要修改红色的数据库名\数据库端口\用户名和密码即可
添加可执行权限
# chmod +x /etc/keepalived/mysqlcheck/check_slave.pl

将keepalived.conf和check_slave.pl文件复制到DB2服务器对应的位置，将DB2上的keepalived.conf中的priority值修改为90，同时去掉nopreempt选项
DB2上
[iyunv@DB2 keepalived]# mkdir /etc/keepalived/mysqlcheck/
[iyunv@DB2 keepalived]# cd /etc/keepalived/mysqlcheck/
上传check_slave.pl脚本
[iyunv@DB2 mysqlcheck]# rz
[iyunv@DB2 mysqlcheck]# ll
total 4
-rw-r--r-- 1 root root 919 Sep 12 14:57 check_slave.pl
[iyunv@DB2 mysqlcheck]# chmod +x check_slave.pl
完成所有配置后，分别启动DB1和DB2上启动keepalived服务，正常情况下VIP地址应该运行在DB1服务器上
[iyunv@DB1 tools]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]
[iyunv@DB2 keepalived]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]
======================================================

8.3.7 测试mysql主从同步功能
首先在DB1,DB2上添加远程访问授权;
DB1上授权

mysql> grant all on *.* to 'root'@'10.24.24.%' identified by 'jzh0024';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
DB2上授权
mysql> grant all on *.* to 'root'@'10.24.24.%' identified by 'jzh0024';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
1.在远程客户端通过VIP登录测试

[iyunv@mysql01 ~]# mysql -uroot -p -h 10.24.24.110
Enter password:
mysql> show variables like "%hostname%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | DB1   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "%server_id%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)
从SQL输出结果看,可以通过VIP登录,并且登录到了DB1服务器
2.数据复制功能测试

[iyunv@mysql01 ~]# mysql -uroot -p -h 10.24.24.110
Enter password:
mysql> create database repldb;
Query OK, 1 row affected (0.00 sec)
mysql> use repldb;
Database changed
mysql> create table repl_table(id int,email varchar(86),password varchar(40) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_repldb |
+------------------+
| repl_table       |
+------------------+
1 row in set (0.00 sec)
mysql> insert into repl_table (id,email,password) values(1,"ywliyq@163.com","qwessd");
Query OK, 1 row affected (0.00 sec)
mysql> select * from repl_table;
+------+----------------+----------+
| id   | email          | password |
+------+----------------+----------+
|    1 | ywliyq@163.com | qwessd   |
+------+----------------+----------+
1 row in set (0.00 sec)
在DB2数据库中查询数据是否已经同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| db02               |
| mysql              |
| repldb             |
| ywadmin            |
+--------------------+
6 rows in set (0.00 sec)
mysql> use repldb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_repldb |
+------------------+
| repl_table       |
+------------------+
1 row in set (0.00 sec)
mysql> select * from repl_table;
+------+----------------+----------+
| id   | email          | password |
+------+----------------+----------+
|    1 | ywliyq@163.com | qwessd   |
+------+----------------+----------+
1 row in set (0.00 sec)
证明在其他mysql客户端登录写入VIP的数据已经同步到DB2数据库中。
======================================================
8.3.8 测试keepalived实现mysql故障切换
为了测试keepalived实现的故障切换功能，需要模拟一些故障。
比如，可以断开DB1主机的网络、关闭DB1主机、关闭DB1上mysql服务等各种操作实现；
这里停止DB1服务器的网络连接，模拟DB1的mysql故障；
由于在DB1、DB2服务器上都添加了监控MYSQL运行状态的脚本 check_slave.pl，因此当关闭DB1的MYSQL日志接收功能后，keepalived会立刻检测到，接着执行切换操作
测试过程如下：
1.停止DB1服务器的网络连接

在远程mysql客户端以VIP地址登录到mysql系统中，不要退出这个连接；
中断DB1服务器的网络连接;
[iyunv@DB1 ~]# /etc/init.d/network stop

2.在mysql远程客户端测试
继续在刚才打开的远程mysql连接中执行命令
mysql> show variables like "%hostname%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | DB2   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "%server_id%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

接着重新开启DB1的网络连接，发现keepalived将不再执行切换操作了，因为上面将keepalived配置为抢占模式了。
此时，mysql服务将一直在DB2服务器上运行，每次切换的代价很大，因而关闭了keepalived的主动抢占模式。