mysql lvs keepalived 双主热备

操作系统

 

Red Hat Enterprise Linux Server release 5.6 (Tikanga)

  

mysql-vip:10.10.11.254

mysql-master1:10.10.11.251

mysql-master2:10.10.11.253

 

 

mysql版本：mysql5.5.19

ipvsadm版本：ipvsadm-1.24.tar.gz

keepalived-1.2.1.tar.gz

 

1 安装lvs

 

ln -sv /usr/src/kernels/2.6.18-194.el5-i686/ /usr/src/linux      

注：这个很重要，不然编译不过。因为编译时要用kernel-source,而Makefile文件设置到kernel-source路径是/usr/src/linux

 

tar zxvf ipvsadm-1.24.tar.gz

 

cd ipvsadm-1.24 

 

make && make install

 

ipvsadm

 

 

检查当前加载的内核模块，是否存在ip_vs模块 

lsmod | grep ip_vs

ip_vs                 122113  0

 

 

2 安装mysql 5.5.19   主主复制

 

不同的地方就是server-id,主服务器配置文件不用修改,从服务器的配置文件server-id=10.其他的内容基本相同. 

 

将10.10.11.251设为10.10.11.253的主服务器，在10.10.11.251新建授权用户 

mysql> grant replication slave on *.* to 'replication'@'%' identified by 'replication'; 

 

得到binlog日志文件名和偏移量 

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      261 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

在10.10.11.253上将10.10.11.251设置为自己的主服务器 

change master to master_host='10.10.11.251',master_user='replication',master_password='replication',master_log_file='mysql-bin.000003',master_log_pos=107;

 

 

将10.10.11.253设为10.10.11.251的主服务器，在10.10.11.253新建授权用户 

mysql> grant replication slave on *.* to 'replication'@'%' identified by 'replication';

 

得到binlog日志文件名和偏移量

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      261 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

在10.10.11.251上将10.10.11.253设置为自己的主服务器

mysql> change master to master_host='10.10.11.253',master_user='replication',master_password='replication',master_log_file='mysql-bin.000005',master_log_pos=195;

 

 

 

启用复制功能（两台机器） 
mysql> start slave; 
mysql> show slave status/G 

 

 

 

出现的问题

当我在执行start slave这条命令时，系统提示

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO，

执行show slave status;又提示Empty set (0.00 sec)，想不通问题在哪里

后来发现，原来slave已经默认开启，要先关闭再开启

执行 slave stop;

再执行

change master to master_host='192.168.1.222',master_user='repl',master_password='123456', master_log_file='log.000003' ,master_log_pos=98;

然后执行 slave start;

这时 再执行show slave status\G

 

     

首先在Master上用 
show processlist;   查看下进程是否Sleep太多。发现很正常。
show master status; 也正常。
再跑到Slave上查看
show slave status;
错误提示：
Error 'Duplicate entry '1' for key 1' on query. Default database: 'movivi1'. Query: 'INSERT INTO `v1vid0_user_samename` VALUES(null,1,'123','11','4545','123')'

Slave_SQL_Running 为 NO
Seconds_Behind_Master 为 (null)

可见是Slave不同步

解决： 
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;

之后Slave会和Master去同步 主要看Seconds_Behind_Master是否为0，直到为0时就已经同步

 

 

 

2 安装keepalived

安装keepalived 
# tar zxvf keepalived-1.2.1.tar.gz 
# cd keepalived-1.2.1 
# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18 -194.el5-i686/ 
# make && make install

 

 

cp /usr/local/keepalived/sbin/keepalived /usr/sbin   #设置环境

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig 

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d  设置keepalived 为系统服务

mkdir /etc/keepalived

cp /usr/local/webserver/keepalived/etc/keepalived/keepalived.conf /etc/keepalived

chkconfig --add keepalived    #此种方式不适合mysql keepalived 主主

chkconfig --level 345 keepalived on

chkconfig --list|grep keepalived

 

用如下方式：keepalived必须在mysql启动后启动，否则会因为mysql没有启动而自动关闭

 

 

 

192.168.15.168上配置keepalived   
global_defs {  
      notification_email {  
      [e-mail] 
      }  
      notification_email_from [e-mail]  
      smtp_server 127.0.0.1  
      smtp_connect_timeout 30  
      router_id MySQL-ha  
      }  
vrrp_instance VI_1 {  
      state BACKUP   #两台配置此处均是BACKUP  
      interface eth0  
      virtual_router_id 51  
      priority 100   #优先级，另一台改为90  
      advert_int 1  
      nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置  
      authentication {  
      auth_type PASS  
      auth_pass 1111  
      }  
      virtual_ipaddress {  
      192.168.15.170  
      }  
      }  
virtual_server 192.168.15.170 3306 {  
      delay_loop 2   #每个2秒检查一次real_server状态  
      lb_algo wrr   #LVS算法  
      lb_kind DR    #LVS模式  
      persistence_timeout 60   #会话保持时间  
      protocol TCP  
      real_server 192.168.15.168 3306 {  
      weight 3  
      notify_down /usr/local/MySQL/bin/MySQL.sh  #检测到服务down后执行的脚本  
      TCP_CHECK {  
      connect_timeout 10    #连接超时时间  
      nb_get_retry 3       #重连次数  
      delay_before_retry 3   #重连间隔时间  
      connect_port 3306   #健康检查端口  
      }  
      } 
编写检测服务down后所要执行的脚本 
#vi /usr/local/MySQL/bin/MySQL.sh  
#!/bin/sh  
pkill keepalived  
#chmod +x /usr/local/MySQL/bin/MySQL.sh 
启动keepalived 
#/usr/local/keepalived/sbin/keepalived –D 
#ps -aux | grep keepalived

 

 

 

192.168.15.169上配置keepalived 
# mkdir /etc/keepalived 
# vi /etc/keepalived/keepalived.conf 
! Configuration File for keepalived  
global_defs {  
      notification_email {  
      [e-mail] 
      }  
      notification_email_from [e-mail]  
      smtp_server 127.0.0.1  
      smtp_connect_timeout 30  
      router_id MySQL-ha  
      }  
vrrp_instance VI_1 {  
      state BACKUP   #两台配置此处均是BACKUP  
      interface eth0  
      virtual_router_id 51  
      priority 90   #优先级 
      advert_int 1   
      authentication {  
      auth_type PASS  
      auth_pass 1111  
      }  
      virtual_ipaddress {  
      192.168.15.170  
      }  
      }  
virtual_server 192.168.15.170 3306 {  
      delay_loop 2   #每个2秒检查一次real_server状态  
      lb_algo wrr   #LVS算法  
      lb_kind DR    #LVS模式  
      persistence_timeout 60   #会话保持时间  
      protocol TCP  
      real_server 192.168.15.169 3306 {  
      weight 3  
      notify_down /usr/local/MySQL/bin/MySQL.sh  #检测到服务down后执行的脚本  
      TCP_CHECK {  
      connect_timeout 10    #连接超时时间  
      nb_get_retry 3       #重连次数  
      delay_before_retry 3   #重连间隔时间  
      connect_port 3306   #健康检查端口  
      }  
      } 
编写检测服务down后所要执行的脚本 
#vi /usr/local/MySQL/bin/MySQL.sh  
#!/bin/sh  
pkill keepalived  
#chmod +x /usr/local/MySQL/bin/MySQL.sh 
启动keepalived 
#/usr/local/keepalived/sbin/keepalived –D 
#ps -aux | grep keepalived

 

 

     检测lvs路由到哪台机器

 

[root@misdata2 ~]# ipvsadm -L -n --stats 
IP Virtual Server version 1.2.1 (size=4096) 
Prot LocalAddress:Port               Conns   InPkts  OutPkts  InBytes OutBytes 
  -> RemoteAddress:Port 
TCP  192.168.5.57:3306                   0        0        0        0        0 
  -> 192.168.5.59:3306                   0        0        0        0        

 

    可以用同网段的另一台服务器 arping 1 这个虚拟IP
    看看对应该的MAC地址 可以知道虚拟IP对应的真实服务器

 

[root@MimmsApp1 ~]# arping 192.168.5.57 
ARPING 192.168.5.57 from 192.168.5.50 eth0 
Unicast reply from 192.168.5.57 [78:2B:CB:69:3A:DD]  0.725ms 
Unicast reply from 192.168.5.57 [78:2B:CB:68:FC:41]  0.765ms 
Unicast reply from 192.168.5.57 [78:2B:CB:68:FC:41]  0.748ms 

 

查看keepalived 是否运行

 

[root@misdata1 ~]# /etc/init.d/keepalived status 
keepalived (pid  7113) is running... 

 

keepalived停止、启动

 

[root@misdata2 ~]# /etc/init.d/keepalived stop |stop|restart 

 

keepalived 日志

 

[root@misdata2 ~]# tail -f /var/log/messages 

 

查看mysql是否运行

 

[root@misdata1 ~]# /etc/init.d/mysql status 
MySQL running (6970)                                       [  OK  ] 

 

 

 

 

 

 

在windows客户端ping -t 10.10.11.254

keepalived故障转移测试 
在windows 客户端一直ping 192.168.15.170 然后分别关闭192.168.15.168 192.168.15.169 上的keepalived 看是否自动切换 

 

 

keepalived的日志文件路径为：tail -f /var/log/messages