欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql lvs keepalived 双主热备

程序员文章站 2022-05-07 09:13:34
...

操作系统

 

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 {  
      157704487@qq.com 
      }  
      notification_email_from 157704487@qq.com  
      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 {  
      157704487@qq.com 
      }  
      notification_email_from 157704487@qq.com  
      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