今天学习了 MySQL 高可用的相关方案， MySQL HA 方案很多，这里仅介绍使用 Keepalived 和 MySQL 本身的主从复制实现高可用。
一 环境信息
主库 192.168.2.37/3306 主库VIP 192.168.2.137
备库 192.168.2.38/3306 备库VIP: 192.168.2.138
版本 5.6.20
系统: RHEL 6.2
备注： 主备节点 mysql 安装略，主备节点都分配单独 VIP，此 HA 方案应用程序不同时连两个节点，例如，应用程序仅连主库，当主库发生故障时，才切换到连备库。
二 主从复制搭建
创建复制用户
1
2
3
4
5
|
root@localhost:(none)>create user 'rep1'@'192.168.2.%' identified by 'rep1abcd1243d';
Query OK, 0 rows affected (0.02 sec)
root@localhost:(none)>GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'192.168.2.%';
Query OK, 0 rows affected (0.01 sec)
|
备注: 创建新用户并赋予 REPLICATION SLAVE 权限，这个用户用来接收主节点的 binary log.
主节点 my.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
basedir = /opt/mysql
datadir = /database/mysql/data
port = 3306
server_id = 1
log_bin = "/database/mysql/data/binlog/bin-log"
binlog_format = "STATEMENT"
master-info-repository=TABLE
log_output = 'FILE,TABLE'
slow_query_log = on
long_query_time = 1
max_connections = 800
max_user_connections = 100
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
prompt="u@h:d>"
|
备节点 my.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
basedir = /opt/mysql
datadir = /database/mysql/data
port = 3306
server_id = 2
report-host=192.168.2.38(db2)
log_bin = "/database/mysql/data/binlog/bin-log"
binlog_format = "STATEMENT"
master-info-repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=on
log_output = 'FILE,TABLE'
slow_query_log = on
long_query_time = 1
max_connections = 1000
max_user_connections = 100
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
prompt="u@h:d>"
|
主库上执行
1
2
3
4
5
6
7
8
9
|
CHANGE MASTER TO
MASTER_HOST='192.168.2.38',
MASTER_PORT=3306,
MASTER_USER='rep1',
MASTER_PASSWORD='rep1abcd1243d',
MASTER_LOG_FILE='bin-log.000023',
MASTER_LOG_POS=2298;
mysql> start slave;
|
备库上执行
1
2
3
4
5
6
7
8
9
|
CHANGE MASTER TO
MASTER_HOST='192.168.2.37',
MASTER_PORT=3306,
MASTER_USER='rep1',
MASTER_PASSWORD='rep1abcd1243d',
MASTER_LOG_FILE='bin-log.000023',
MASTER_LOG_POS=552;
mysql> start slave;
|
备注：MASTER_LOG_FILE 和 MASTER_LOG_POS 参数根据实际情况调整下， 其它主从复制相关步骤略，具体参考 MySQL：主从复制(Replication)搭建
三 安装 Keepalived
下载
1
|
wget http://www.keepalived.org/software/keepalived-1.2.13.tar.gz
|
安装 OpenSSL , popt 包
1
|
yum -y install openssl openssl-devel popt
|
解压安装
1
2
3
4
5
|
[root@db1 soft_bak]# tar xvf keepalived-1.2.13.tar.gz
# ./configure --prefix=/usr/local/keepalived --sysconf=/etc
# make
# make install
|
遇到的错误
1
2
3
|
[root@db1 ~]# /etc/init.d/keepalived start
Starting keepalived: /bin/bash: keepalived: command not found
[FAILED]
|
解决方法
1
2
3
4
5
6
7
|
[root@db1 etc]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin
[root@db1 etc]# service keepalived restart
Stopping keepalived: [OK ]
Starting keepalived: [OK ]
[root@db1 etc]# chkconfig keepalived off
|
四 配置 Keepalived
主库 keepalived 配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
[root@db1 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
xxx@163.com
}
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_BALANCE
}
vrrp_script check_mysql_alived {
script "/usr/local/bin/mysql_moniter.sh"
interval 5
}
vrrp_instance VI_1 {
state BACKUP
nopreempt # 主VIP 为非抢占模式
interface eth0
virtual_router_id 61
priority 100 # 注意这里将优先级设置比备节点高
advert_int 1
authentication {
auth_type PASS
auth_pass 7BwhNLYpF6Ihs
}
track_script {
check_mysql_alived
}
virtual_ipaddress {
192.168.2.137
}
# smtp_alert
#notify_master /usr/local/bin/active_standby.sh
}
vrrp_instance VI_2 {
state BACKUP
preempt # 备VIP 为抢占模式
interface eth0
virtual_router_id 62
priority 90 # 注意这里将优先级设置比备节点低
advert_int 1
authentication {
auth_type PASS
auth_pass 7BwhNLYpF6Ihs
}
track_script {
check_mysql_alived
}
virtual_ipaddress {
192.168.2.138
}
# smtp_alert
#notify_master /usr/local/bin/active_standby.sh
}
|
备库 keepalived 配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
[root@db2 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
xxx@163.com
}
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_BALANCE
}
vrrp_script check_mysql_alived {
script "/usr/local/bin/mysql_moniter.sh"
interval 5
}
vrrp_instance VI_1 {
state BACKUP
nopreempt
interface eth0
virtual_router_id 61
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 7BwhNLYpF6Ihs
}
track_script {
check_mysql_alived
}
virtual_ipaddress {
192.168.2.137
}
# smtp_alert
#notify_master /usr/local/bin/active_standby.sh
}
vrrp_instance VI_2 {
state BACKUP
preempt
interface eth0
virtual_router_id 62
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 7BwhNLYpF6Ihs
}
track_script {
check_mysql_alived
}
virtual_ipaddress {
192.168.2.138
}
# smtp_alert
#notify_master /usr/local/bin/active_standby.sh
}
|
/usr/local/bin/mysql_moniter.sh 脚本
1
2
3
4
5
6
7
8
9
10
11
|
MYSQL_PORT=3306
nmap localhost -p ${MYSQL_PORT} | grep "${MYSQL_PORT}/tcp open"
if [ $? -ne 0 ]; then
exit 1
else
exit 0
fi
|
备注： 这里通过检测 mysql 服务端口方式判断 mysqld 服务是否正常。
五 HA 测试
1) 停主库
1
2
|
[root@db1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ [OK ]
|
备注： 发现主库 VIP 192.168.2.137 漂到备库上了，切换正常。
2) 停主 Keepalived 服务
1
2
|
[root@db1 ~]# /etc/init.d/keepalived stop
Stopping keepalived:
|
备注： 发现主库 VIP 192.168.2.137 漂到备库上了，切换正常。
3) 关主库主机
1
|
[root@db1 ~]# shutdown -h now
|
备注： 发现主库 VIP 192.168.2.137 漂到备库上了，切换正常。
六 总结
此文通过 keepalived + MySQL 主从实现了 HA， 但这种方法并不严谨，因为在切换前并没有主备延迟判断，当备库落后主库很长时间后，显然不适合切换了，建议将主备延迟监控起来。如果主备延迟超过阀值，则发出告警。
相关脚本需后续完善。
七 参考
原创文章，作者：bd101bd101，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/239621.html
