首先在搭建前分析,想用keepalived实现mysql高可用,那么先的对keepalived+lvs跟mysql主主复制有所了解。因为mysql+keepalived架构是在keepalived+lvs跟mysql主主复制的基础上实现的lvs+keepalived跟mysql主主复制,在前面的博文中已经介绍过了这里不啰嗦了。
lvs+keepalived链接:
mysql主主复制链接:
我们的目标是,两台mysql服务器如果其中有一台mysql服务器挂掉后,另外一台能立马接替其进行工作。因此我们就必须保证两台mysql数据库的数据完全一样,而且当挂掉的那一台重新启动的话,不再会被客户端继被访问,而是会充当备机跟现在工作的mysql进行数据同步,一直到提供服务的那台挂掉后再接替其工作。如此周而复始的实现了mysql的高可用。在通常情况下实现这种模式的keepalived无疑是最好的选择。因为有虚拟IP的原因,如果有一台mysql挂掉了,keepalived会从服务器群中剔除,而客户端访问会被切换到另外一台接替其工作的机器上。
在搭建的时候一些注意事项,mysql主主复制跟lvs+keepalived搭建的注意事情前面博文已经提高这里就不再啰嗦。在搭建完毕后,因为arp的原因不能够实现自由切换。这就是要注意的重点。
1 2 3 4 5 6 7 8 | vim /etc/sysctl.conf #.............前面部分省去 #######以下是文件末尾添加的部分###### net.ipv4.conf.all.arp_ignore = 1 net.ipv4.conf.all.arp_announce = 2 net.ipv4.conf.lo.arp_ignore = 1 net.ipv4.conf.lo.arp_announce = 2 ##这4行的主要作用是本地arp不作回应## |
添加完后保存退出,并执行:sysctl -p使其生效
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [root@mysql-ha1 ~]# sysctl -p net.ipv4.ip_forward = 0 net.ipv4.conf.default.rp_filter = 1 net.ipv4.conf.default.accept_source_route = 0 kernel.sysrq = 0 kernel.core_uses_pid = 1 kernel.msgmnb = 65536 kernel.msgmax = 65536 kernel.shmmax = 68719476736 kernel.shmall = 4294967296 net.ipv4.conf.all.send_redirects = 0 net.ipv4.conf.default.send_redirects = 0 net.ipv4.conf.eth0.send_redirects = 0 net.ipv4.conf.eth0.send_redirects = 0 net.ipv4.conf.all.arp_ignore = 1 net.ipv4.conf.all.arp_announce = 2 net.ipv4.conf.lo.arp_ignore = 1 net.ipv4.conf.lo.arp_announce = 2 |
下面具体操作,首先有两台mysql机器已经搭建好了mysql的主主复制
vip:192.168.5.55
mysql-ha1:192.168.5.234
mysql-ha2:192.168.5.155
在两台mysql机器上都安装ipvsadm,keepalived,首先在mysql-ha1(192.168.5.234)安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [root@mysql-ha1 src]# wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz [root@mysql-ha1 src]# wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.26.tar.gz [root@mysql-ha1 src]# yum -y install kernel-devel make gcc openssl-devel libnl* popt* [root@mysql-ha1 src]# ln -s /usr/src/kernels/2.6.32-220.13.1.el6.x86_64/ /usr/src/linux [root@mysql-ha1 src]# tar -zxvf ipvsadm-1.26.tar.gz [root@mysql-ha1 src]# cd ipvsadm-1.26/ [root@mysql-ha1 ipvsadm-1.26]# make [root@mysql-ha1 ipvsadm-1.26]# make install [root@mysql-ha1 ipvsadm-1.26]# cd .. [root@mysql-ha1 src]# tar -zxvf keepalived-1.2.2.tar.gz [root@mysql-ha1 src]# cd keepalived-1.2.2/ [root@mysql-ha1 keepalived-1.2.2]# ./configure --with-kernel-dir=/usr/src/kernels/2.6.32-358.2.1.el6.x86_64/ [root@mysql-ha1 keepalived-1.2.2]# make && make install [root@mysql-ha1 keepalived-1.2.2]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ [root@mysql-ha1 keepalived-1.2.2]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/ [root@mysql-ha1 keepalived-1.2.2]# mkdir /etc/keepalived [root@mysql-ha1 keepalived-1.2.2]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/ [root@mysql-ha1 keepalived-1.2.2]# cp /usr/local/sbin/keepalived /usr/sbin/ |
在mysql-ha2(192.168.5.155)也同样进行安装
然后我们编辑keepalived配置文件,下面是mysql-ha1(192.168.5.234)上的配置文件内容
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 | [root@mysql-ha1 ~]# >/etc/keepalived/keepalived.conf [root@mysql-ha1 ~]# vim /etc/keepalived/keepalived.conf #! Configuration File for keepalived global_defs { router_id mysql-ha1 #修改为自己的主机名 } ##################第一部分################### vrrp_instance VI_1 { state BACKUP #都修改成BACKUP interface eth0 virtual_router_id 60 #默认51 主从都修改为60 priority 100 #在mysql-ha2上LVS上修改成80 advert_int 1 nopreempt #不抢占资源,意思就是它活了之后也不会再把主抢回来 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.5.55 } } ##################第二部分################### virtual_server 192.168.5.55 3306 { delay_loop 6 lb_algo wrr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP real_server 192.168.5.234 3306 { weight 1 notify_down /usr/local/mysql/bin/mysql.sh TCP_CHECK { connect_timeout 10 nb_get_retry 3 connect_port 3306 } } } |
这里需要注意的是,notify_down /usr/local/mysql/bin/mysql.sh这个选项,这个是是在keepalived检测不到mysql的时候要执行的脚本,从上面的配置文件来看real服务器只有本机。那么,keeaplived如果启动,客户端也只是访问本机的mysql。nopreempt这个选下也得注意,这个是不抢占资源在优先级高的机器上配置就可以。
看下这个脚本的内容:
1 2 3 4 | [root@mysql-ha1 ~]# vim /usr/local/mysql/bin/mysql.sh #!/bin/bash pkill keepalived [root@mysql-ha1 ~]# chmod +x /usr/local/mysql/bin/mysql.sh |
脚本内容就一条命令:pkill keepalived,主要作用是如果本机的mysql挂掉了,那么同时会杀死本机的keepalived,这样另外一台就会接替他工作,虚拟IP也会被另一台接管,如果不杀死keepalived虚拟IP不会被另一台接管,mysql访问也就不会切换过去。
测试下如果,mysql服务器挂掉,脚本是否可以杀死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 | [root@mysql-ha1 ~]# ps aux | grep keepalived root 2518 0.0 0.0 41796 936 ? Ss 19:35 0:00 keepalived -D root 2519 0.0 0.1 43900 2152 ? S 19:35 0:00 keepalived -D root 2520 0.0 0.0 43900 1572 ? S 19:35 0:00 keepalived -D root 2610 0.0 0.0 103240 860 pts/0 S+ 23:07 0:00 grep keepalived [root@mysql-ha1 ~]# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2237/mysqld tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1041/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1416/master tcp 0 0 0.0.0.0:5672 0.0.0.0:* LISTEN 1429/qpidd tcp 0 0 :::22 :::* LISTEN 1041/sshd tcp 0 0 ::1:25 :::* LISTEN 1416/master [root@mysql-ha1 ~]# service mysqld stop Shutting down MySQL... SUCCESS! [root@mysql-ha1 ~]# ps aux | grep keepalived root 2640 0.0 0.0 103240 856 pts/0 S+ 23:07 0:00 grep keepalived [root@mysql-ha1 ~]# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1041/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1416/master tcp 0 0 0.0.0.0:5672 0.0.0.0:* LISTEN 1429/qpidd tcp 0 0 :::22 :::* LISTEN 1041/sshd tcp 0 0 ::1:25 :::* LISTEN 1416/master [root@mysql-ha1 ~]# |
可以看到当mysqld服务器挂掉后,脚本生效同时杀死本机keepalived使得mysql故障转移。这时另一台keepalived会接替它工作。
同样看下mysql-ha2(192.168.5.155)上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 | [root@mysql-ha2 ~]# vim /etc/keepalived/keepalived.conf #! Configuration File for keepalived global_defs { router_id mysql-ha1 #修改为自己的主机名 } ##################第一部分################### vrrp_instance VI_1 { state BACKUP #都修改成BACKUP interface eth1 virtual_router_id 60 #默认51 主从都修改为60 priority 80 #在mysql-ha1上LVS上修改成100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.5.55 } } ##################第二部分################### virtual_server 192.168.5.55 3306 { delay_loop 6 lb_algo wrr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP real_server 192.168.5.155 3306 { weight 1 notify_down /usr/local/mysql/bin/mysql.sh TCP_CHECK { connect_timeout 10 nb_get_retry 3 connect_port 3306 } } } |
配置完毕后可以查看下keealived有没有检测到本机的mysql。(启动的时候先启动mysql在启动keepalived,要不mysql没起来启动keepalived会被脚本杀死。)
先看下mysql-ha1(192.168.5.234)
1 2 3 4 5 6 7 8 9 10 | [root@mysql-ha1 ~]# service mysqld start Starting MySQL.. SUCCESS! [root@mysql-ha1 ~]# /etc/init.d/keepalived start 正在启动 keepalived: [root@mysql-ha1 ~]# ipvsadm -Ln IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP 192.168.5.55:mysql wrr persistent 50 -> 192.168.5.234:mysql Local 1 0 0 |
然后再看下mysql-ha1(192.168.5.155)
1 2 3 4 5 6 7 8 9 10 | [root@mysql-ha2 ~]# service mysqld start Starting MySQL.. SUCCESS! [root@mysql-ha2 ~]# /etc/init.d/keepalived start 正在启动 keepalived: [确定] [root@mysql-ha2 ~]# ipvsadm -Ln IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP 192.168.5.55:3306 wrr persistent 50 -> 192.168.5.155:3306 Local 1 0 0 |
测试只开启mysql-ha1(192.168.5.234)mysql服务跟keepalived用客户端链接虚拟ip
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@mysql-ha1 ~]# ps aux | grep keepalived root 2977 0.0 0.0 41796 936 ? Ss 23:14 0:00 keepalived -D root 2978 0.0 0.1 43900 2156 ? S 23:14 0:00 keepalived -D root 2979 0.0 0.0 43900 1576 ? S 23:14 0:00 keepalived -D root 2982 0.0 0.0 103240 860 pts/0 S+ 23:16 0:00 grep keepalived [root@mysql-ha1 ~]# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2917/mysqld tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1041/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1416/master tcp 0 0 0.0.0.0:5672 0.0.0.0:* LISTEN 1429/qpidd tcp 0 0 :::22 :::* LISTEN 1041/sshd tcp 0 0 ::1:25 :::* LISTEN 1416/master |
mysql-ha2(192.168.5.155)
1 2 3 4 5 6 7 8 9 10 | [root@mysql-ha2 ~]# ps aux | grep keepalived root 10576 0.0 0.0 5980 756 pts/4 S+ 15:24 0:00 grep keepalived [root@mysql-ha2 ~]# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 4776/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1101/master tcp 0 0 0.0.0.0:5672 0.0.0.0:* LISTEN 1114/qpidd tcp 0 0 :::22 :::* LISTEN 4776/sshd tcp 0 0 ::1:25 :::* LISTEN 1101/master |
然后用客户端:192.168.5.10连接
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | C:\Users\Administrator>mysql -uduyunlong -p123456 -h192.168.5.55 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 Server version: 5.5.22-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.02 sec) mysql> |
然后把mysql-ha1(192.168.5.234)mysql服务关掉,开启mysql-ha2(192.168.5.155)上的mysql服务跟keepalived。然后不退出mysql,继续查看数据库:
mysql-ha1(192.168.5.234)
1 2 | [root@mysql-ha1 ~]# service mysqld stop Shutting down MySQL... SUCCESS! |
mysql-ha2(192.168.5.155)
1 2 3 4 5 | [root@mysql-ha2 ~]# service mysqld start Starting MySQL.. SUCCESS! [root@mysql-ha2 ~]# /etc/init.d/keepalived start 正在启动 keepalived: [确定] [root@mysql-ha2 ~]# |
客户端:192.168.5.10
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 4 Current database: *** NONE *** +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec) mysql> |
切换很快,大概在2到3秒之间!
MySQL的高可用,由一对互为主从的MySQL服务器组成,平时只有主库提供服务,备库不提供服务。当主库停止服务时,服务自动切换到备库。主备库通过双向复制,保证数据一致性。主备库以VIP对外统一服务接口。
MySQL对外服务接口的VIP,由Keepalived来实现心跳检查和动态漂移。因此MySQL和Keepalived部署在同一台服务器上,每台Keepalived配置的realserver即自身机器的MySQL服务。
两台服务器都安装MySQL,搭建主主复制架构。具体搭建略。
LVS源码安装
Keepalived源码安装
安装依赖包
yum -y install popt-devel
安装Keepalived
点击(此处)折叠或打开
mysqld和keepalived自启动
点击(此处)折叠或打开
这里的主备库IP配置如下
库 |
IP |
服务VIP |
主 |
10.0.0.77 |
10.0.0.76 |
备 |
10.0.0.78 |
Keepalived配置文件keepalived配置文件/etc/keepalived/keepalived.conf,主库为例
点击(此处)折叠或打开
从以下4个方面测试HA效果
keepalived down
mysqld down
网络故障
机器宕机
测试步骤和结果
keepalived down
步骤:kill master上的keepalived进程
结果:
>>slave的keepalived通知切换为master
mysqld down
步骤:kill master上的mysqld进程,或者提高master服务器的负载
结果:
>>master的keepalived通知因为mysqld down而停止keepalived
>>slave的keepalived通知切换为master<
来源: http://lib.csdn.net/article/linux/37781