内容简介:双主 + keepalived 是一个比较简单的 MySQL 高可用架构,适用于中小 MySQL 集群,今天就说说怎么用 keepalived 做 MySQL 的高可用。简单地说,keepalived 就是通过管理 VIP 来实现机器的高可用的,在使用 keepalived 的情况下,只有一台服务器能够提供服务(通过 VIP 来实现),当 Master 主机宕机后,VIP 会自动飘移到另一台服务器keepalived 采用 Master/Slave 模式, 在 Master 上设置配置文件的 VIP,当 M
双主 + keepalived 是一个比较简单的 MySQL 高可用架构,适用于中小 MySQL 集群,今天就说说怎么用 keepalived 做 MySQL 的高可用。
1 概述
1.1 keepalived 简介
简单地说,keepalived 就是通过管理 VIP 来实现机器的高可用的,在使用 keepalived 的情况下,只有一台服务器能够提供服务(通过 VIP 来实现),当 Master 主机宕机后,VIP 会自动飘移到另一台服务器
keepalived 采用 Master/Slave 模式, 在 Master 上设置配置文件的 VIP,当 Master 宕机后,VIP 自动漂移到另一台 keepalived 服务器上
keepalived 可以用来做各种软件的高可用集群,它会一直检测服务器的状态,如果有一台服务器宕机,或工作出现故障,keepalived 将检测到,并将有故障的服务器从系统中剔除,同时使用其他服务器代替该服务器的工作,当服务器工作正常后 keepalived 自动将服务器加入到服务器群中。
1.2 keepalived 配合双主
keepalived 使用默认配置只能做到主机级别的高可用,但是我们的 MySQL 要做高可用至少要增加以下功能
- 能够检测 MySQL 服务状态
-
主节点
read_only=0
,备节点read_only=1
- 切换时,备节点要等待主节点同步完成
所以,keepalived 实现 MySQL 高可用需要使用自定义脚本来进行扩展
2 环境准备
2.1 数据库环境
操作前已经准备好了一套主主架构数据库,搭建方法参考 MySQL集群搭建(2)-主主从模式
节点信息
IP | 系统 | 端口 | MySQL版本 | 节点 | 读写 | 说明 |
---|---|---|---|---|---|---|
10.0.0.247 | Centos6.5 | 3306 | 5.7.9 | Master | 读写 | 主节点 |
10.0.0.248 | Centos6.5 | 3306 | 5.7.9 | Standby | 只读,可切换为读写 | 备主节点 |
VIP 信息
简称 | VIP | 类型 |
---|---|---|
RW-VIP | 10.0.0.237 | 读写VIP |
Master 参考配置
[client] port = 3306 default-character-set=utf8mb4 socket = /data/mysql_db/test_db/mysql.sock [mysqld] datadir = /data/mysql_db/test_db basedir = /usr/local/mysql57 tmpdir = /tmp socket = /data/mysql_db/test_db/mysql.sock pid-file = /data/mysql_db/test_db/mysql.pid skip-external-locking = 1 skip-name-resolve = 1 port = 3306 server_id = 2473306 default-storage-engine = InnoDB character-set-server = utf8mb4 default_password_lifetime=0 auto_increment_offset = 1 auto_increment_increment = 2 #### log #### log_timestamps=system log_bin = /data/mysql_log/test_db/mysql-bin log_bin_index = /data/mysql_log/test_db/mysql-bin.index binlog_format = row relay_log_recovery=ON relay_log=/data/mysql_log/test_db/mysql-relay-bin relay_log_index=/data/mysql_log/test_db/mysql-relay-bin.index log_error = /data/mysql_log/test_db/mysql-error.log #### replication #### log_slave_updates = 1 replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.% #### semi sync replication settings ##### plugin_dir=/usr/local/mysql57/lib/plugin plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1
Slave 参考配置
[client] port = 3306 default-character-set=utf8mb4 socket = /data/mysql_db/test_db/mysql.sock [mysqld] datadir = /data/mysql_db/test_db basedir = /usr/local/mysql57 tmpdir = /tmp socket = /data/mysql_db/test_db/mysql.sock pid-file = /data/mysql_db/test_db/mysql.pid skip-external-locking = 1 skip-name-resolve = 1 port = 3306 server_id = 2483306 default-storage-engine = InnoDB character-set-server = utf8mb4 default_password_lifetime=0 auto_increment_offset = 2 auto_increment_increment = 2 #### log #### log_timestamps=system log_bin = /data/mysql_log/test_db/mysql-bin log_bin_index = /data/mysql_log/test_db/mysql-bin.index binlog_format = row relay_log_recovery=ON relay_log=/data/mysql_log/test_db/mysql-relay-bin relay_log_index=/data/mysql_log/test_db/mysql-relay-bin.index log_error = /data/mysql_log/test_db/mysql-error.log #### replication #### log_slave_updates = 1 replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.% #### semi sync replication settings ##### plugin_dir=/usr/local/mysql57/lib/plugin plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1
2.2 创建监控用的账号
- 由于是测试环境,账号密码设置比较随便 create user monitor@'localhost' identified by 'monitor'; grant all on *.* to monitor@'localhost'; flush privileges;
2.3 安装 keepalived
我们在 Master 和 Slave 上部署 keepalived
1). yum 安装
如果有对应的 yum
源,直接安装就可以了
yum install -y keepalived
2). 源码安装
下载安装包, 下载地址 keepalived
, 使用 1.2.24
版本举例
# 安装依赖 yum install -y gcc popt-devel openssl openssl-devel libssl-dev libnl-devel popt-devel libnfnetlink-devel # 下载包 wget http://www.keepalived.org/software/keepalived-1.2.24.tar.gz # 解压安装 tar -xvz -f keepalived-1.2.24.tar.gz cd keepalived-1.2.24 ./configure --prefix=/usr/local/keepalived make && make install cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ mkdir /etc/keepalived/ cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
3 配置高可用
3.1 keepalived 配置
打开 /etc/keepalived/keepalived.conf
文件, 按照实际情况加上下面的配置
global_defs { router_id MYSQL_MM # 标识 vrrp_skip_check_adv_addr vrrp_strict # 严格执行 VRRP 协议规范 vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script check_mysql { script "/bin/sh /etc/keepalived/keepalived_mysql_check.sh" # 检查脚本 interval 10 # 检查周期 } vrrp_instance MYSQL_MM { state BACKUP # 都设为 BACKUP,避免起来后抢占 interface eth0 # 网卡名称,根据实际情况填写 virtual_router_id 243 # 用来区分 VRRP 组播的标记,取值 0-255 priority 100 advert_int 1 nopreempt # 设为非抢占 authentication { auth_type PASS auth_pass 1111 } # Master 节点可以注释掉下面语句,防止启动 keepalived 的时候执行脚本 notify_master "/bin/sh /etc/keepalived/keepalived_mysql_start.sh" # 变为 MASTER 时执行 virtual_ipaddress { 10.0.0.237 } # Slave 节点可以注释下面检查脚本,Slave 没有必要一直检查 track_script { check_mysql } }
3.2 配置检查脚本
打开 /etc/keepalived/keepalived_mysql_check.sh
, 写入检测脚本
#!/bin/sh # @Author: chengqm # MySQL 检测脚本 MyPath=$(cd $(dirname $0); pwd) cd $MyPath ThisTime=`date '+%F %T'` log_file='/var/log/keepalived_mysql.log' # MySQL 连接方式,根据实际情况调整 export MYSQL_PWD='monitor' MYSQL_USER='monitor' MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock" mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} " # 美化输出 function techo() { message=$1 message_level=$2 if [ -e $message_level ];then message_level='info' fi echo "`date '+%F %T'` - [${message_level}] $message" >> $log_file } # 检查函数, 正常返回 0 function check { ret=`$mysql_connect -N -e 'select 1 as value'` if [ $? -ne 0 ] || [ $ret -ne '1' ];then return 1 else return 0 fi } function read_only { param=$1 $mysql_connect -e "set global read_only = ${param}" techo "设置是否只读 read_only ${param}" } # 失效转移 function failover { techo "开始执行失效转移" # 1. 停止 keepalived killall keepalived # 2. 如果还能执行的话,设为 read_only read_only 1 if [ $? -eq 0 ];then # 3. 如果还能执行,kill 所有的连接 $mysql_connect -e "select concat('KILL ',id,';') from information_schema.processlist where user!='root' AND db is not null into outfile '/tmp/kill.txt.${ThisTime}';" if [ $? -eq 0 ];then $mysql_connect -e "source /tmp/kill.txt.${ThisTime};" fi fi # 4. 其他操作,比如说自动关机 techo "失效转移执行成功,当前数据库关闭访问" } # 有问题检查 4 次 for ((i=1; i<=4; i ++)) do check if [ $? -eq 0 ];then techo "MySQL is ok" # 正常退出脚本 exit 0 else techo "Connection failed $i time(s)" sleep 1 fi done techo '无法连接当前数据库' # 失效转移 failover
注意:脚本没有经过严格测试,需要根据实际情况调整
3.3 配置提升为 Master 时执行的脚本
打开 /bin/sh /etc/keepalived/keepalived_mysql_start.sh"
, 写入脚本内容
#!/bin/sh # @Author: chengqm # keepalived 变为 Master 时执行 MyPath=$(cd $(dirname $0); pwd) cd $MyPath ThisTime=`date '+%F %T'` log_file='/var/log/keepalived_mysql.log' # MySQL 连接方式,根据实际情况调整 export MYSQL_PWD='monitor' MYSQL_USER='monitor' MYSQL_SOCKET="/data/mysql_db/test_db/mysql.sock" mysql_connect="mysql -u${MYSQL_USER} -S${MYSQL_SOCKET} " # 美化输出 function techo() { message=$1 message_level=$2 if [ -e $message_level ];then message_level='info' fi echo "`date '+%F %T'` - [${message_level}] $message" >> $log_file } # 检查函数, 正常返回 0 function check { ret=`$mysql_connect -N -e 'select 1 as value'` if [ $? -ne 0 ] || [ $ret -ne '1' ];then return 1 else return 0 fi } # 获取 slave status 的信息 function slave_info() { tmp_file=/tmp/slave_info.tmp $mysql_connect -e 'show slave status\G' > /tmp/slave_info.tmp slave_sql=`grep 'Slave_SQL_Running:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z" | awk -F":" '{print $2}'` seconds_behind_master=`grep 'Seconds_Behind_Master:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z" | awk -F":" '{print $2}'` master_log_file=`grep 'Master_Log_File:' $tmp_file | head -1 | sed 's/\s*//g' | tr "A-Z" "a-z" | awk -F":" '{print $2}'` master_log_pos=`grep 'Read_Master_Log_Pos:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z" | awk -F":" '{print $2}'` relay_master_log_file=`grep 'Relay_Master_Log_File:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z" | awk -F":" '{print $2}'` exec_master_log_pos=`grep 'Exec_Master_Log_Pos:' $tmp_file | sed 's/\s*//g' | tr "A-Z" "a-z" | awk -F":" '{print $2}'` } # 设置是否可读 function read_only { param=$1 $mysql_connect -e "set global read_only = ${param}" techo "设置是否只读 read_only ${param}" } # 处理数据同步 function sync_master_log() { # 如果是数据一致性优先,等待同步完毕。如果是服务可用性优先,可以注销下面的代码 slave_info if [ $slave_sql == "yes" ];then techo "当前同步位置 Master ${master_log_file} ${master_log_pos}" techo "等待同步到 Master ${master_log_file} ${master_log_pos}" $mysql_connect -e "select master_pos_wait('$master_log_file', $master_log_pos);" > /dev/null techo "同步完毕" fi } techo "当前数据库提升为主库" check if [ $? -ne 0 ];then techo "无法连接当前数据库" exit 1 fi # 等待同步 sync_master_log # 设为可写 read_only 0
注意:脚本没有经过严格测试,需要根据实际情况调整
3.4 启动 keepalived
由于配置了 BACKUP 模式,所以两个 keepalived 先起来的是主,先后在主备节点执行
/etc/init.d/keepalived start
检查 /var/log/message
日志,确认 keepalived 没有报错
检查 Master IP 状态, 确认设置了 VIP
[root@cluster01 shell]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether fa:16:3e:de:80:33 brd ff:ff:ff:ff:ff:ff inet 10.0.0.247/16 brd 10.0.255.255 scope global eth0 inet 10.0.0.237/32 scope global eth0 inet6 fe80::f816:3eff:fede:8033/64 scope link valid_lft forever preferred_lft forever
检查 MySQL 检测脚本执行情况,确认正常运行
[root@cluster01 ~]# tail -f /var/log/keepalived_mysql.log ... 2019-01-28 15:04:18 - [info] MySQL is ok 2019-01-28 15:04:28 - [info] MySQL is ok
4 失效转移测试
在 mytest
库里新建 nowdate
测试表,只有 id
和 ctime
字段,然后每秒插入一条数据
[root@cluster03 ~]# while true; do date;mysql -h10.0.0.237 -P3306 -umytest -e 'use mytest;insert into nowdate values (null, now());'; sleep 1;done Mon Jan 28 15:04:26 CST 2019 Mon Jan 28 15:04:27 CST 2019 ...
kill 掉 Master 进程
killall mysqld
查看旧 Master 日志
2019-01-28 15:04:48 - [info] MySQL is ok 2019-01-28 15:04:58 - [info] Connection failed 1 time(s) 2019-01-28 15:04:59 - [info] Connection failed 2 time(s) 2019-01-28 15:05:00 - [info] Connection failed 3 time(s) 2019-01-28 15:05:01 - [info] Connection failed 4 time(s) 2019-01-28 15:05:02 - [info] 无法连接当前数据库 2019-01-28 15:05:02 - [info] 开始执行失效转移 2019-01-28 15:05:02 - [info] 设置是否只读 read_only 1 2019-01-28 15:05:02 - [info] 失效转移执行成功,当前数据库关闭访问
查看新 Master 日志
2019-01-28 15:05:04 - [info] 当前数据库提升为主库 2019-01-28 15:05:04 - [info] 当前同步位置 Master mysql-bin.000015 32338 2019-01-28 15:05:04 - [info] 等待同步到 Master mysql-bin.000015 32338 2019-01-28 15:05:04 - [info] 同步完毕 2019-01-28 15:05:04 - [info] 设置是否只读 read_only 0 2019-01-28 15:05:05 - [info] MySQL is ok
查看新 Master IP,确认 VIP 已经飘过来了
[root@cluster02 ~]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether fa:16:3e:66:7e:e8 brd ff:ff:ff:ff:ff:ff inet 10.0.0.248/16 brd 10.0.255.255 scope global eth0 inet 10.0.0.237/32 scope global eth0 inet6 fe80::f816:3eff:fe66:7ee8/64 scope link valid_lft forever preferred_lft forever
查看插入数据执行情况,大概有 12 秒是不可用的
Mon Jan 28 15:04:51 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:04:52 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:04:53 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:04:54 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:04:55 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:04:56 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:04:57 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:04:58 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:05:00 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:05:01 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:05:02 CST 2019 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.237' (111) Mon Jan 28 15:05:03 CST 2019
失效切换成功
5 总结
使用双主 + keepalived 的优点是部署简单,双主加半同步情况下,理论上不会丢数据,适用于中小型 MySQL 集群。缺点也比较明显,就是增加从节点的情况下,从节点不会主动切换同步对象,而且脚本需要自己实现,有一定风险。
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- Elasticsearch 集群搭建和集群原理
- Zookeeper学习系列【二】Zookeeper 集群章节之集群搭建
- Spark集群环境搭建
- Zookeeper搭建集群
- FastDFS集群搭建
- Zookeeper集群环境搭建
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
推荐系统与深度学习
黄昕、赵伟、王本友、吕慧伟、杨敏 / 清华大学出版社 / 2019-1-1 / 65.00元
本书的内容设置由浅入深,从传统的推荐算法过渡到近年兴起的深度学习技术。不管是初学者,还是有一定经验的从业人员,相信都能从本书的不同章节中有所收获。 区别于其他推荐算法书籍,本书引入了已被实践证明效果较好的深度学习推荐技术,包括Word2Vec、Wide & Deep、DeepFM、GAN 等技术应用,并给出了相关的实践代码;除了在算法层面讲解推荐系统的实现,还从工程层面详细阐述推荐系统如何搭建.一起来看看 《推荐系统与深度学习》 这本书的介绍吧!