首页 > 代码库 > 配置Keepalived实现mysql双主高可用
配置Keepalived实现mysql双主高可用
本文为南非蚂蚁的书籍《循序渐进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上
[root@DB2 keepalived]# mkdir /etc/keepalived/mysqlcheck/
[root@DB2 keepalived]# cd /etc/keepalived/mysqlcheck/
上传check_slave.pl脚本
[root@DB2 mysqlcheck]# rz
[root@DB2 mysqlcheck]# ll
total 4
-rw-r--r-- 1 root root 919 Sep 12 14:57 check_slave.pl
[root@DB2 mysqlcheck]# chmod +x check_slave.pl
完成所有配置后，分别启动DB1和DB2上启动keepalived服务，正常情况下VIP地址应该运行在DB1服务器上
[root@DB1 tools]# /etc/init.d/keepalived start
Starting keepalived: [ OK ]
[root@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登录测试
[root@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.数据复制功能测试
[root@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服务器的网络连接;
[root@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的主动抢占模式。
本文出自 “Linux运维的自我修养” 博客，请务必保留此出处http://ywliyq.blog.51cto.com/11433965/1856964
配置Keepalived实现mysql双主高可用