Mysql+keepalived搭建

11lxm

　　Mysql+Keepalived环境搭建详细文档


create table user(
id int(4) primary key auto_increment not null,
username varchar(20),
password varchar(20));

CREATE TABLE `repl_test` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

插入脚本：
vi insert.sh
#!/bin/sh
/usr/local/mysql/bin/mysql -h192.168.1.238  -udba -ptomcat2008  -e"use qin;insert into repl_test values(1),(2),(3),(4),(5);";
/usr/local/mysql/bin/mysql -h192.168.1.238  -udba -ptomcat2008  -e"use qin; insert into user (username,password) values ('Europa','123');;";

查询脚本：
more count.sh
#!/bin/sh
/usr/local/mysql/bin/mysql -h192.168.1.238  -udba -ptomcat2008  -e"use qin;select count(*) from  repl_test;";
/usr/local/mysql/bin/mysql -h192.168.1.238  -udba -ptomcat2008  -e"use qin;select count(*) from  user ;";


环境：
安装

Yum install kernel-xen* -y
Yum install openssl*

MySQL-VIP：192.168.1.238
MySQL-master1：192.168.1.17
MySQL-master2：192.168.1.20
vi /etc/hosts
192.168.1.17     lnx17   lnx17
192.168.1.20     lnx20   lnx20

一、MySQL Master配置
1.修改/etc/my.cnf
----master1--------
log-bin=mysql-bin
server-id = 1
binlog-do-db=qin
binlog-ignore-db=mysql
replicate-do-db=qin
replicate-ignore-db=mysql
log-slave-updates
#slave-skip-errors=all
sync_binlog=1
#auto_increment_increment=2
#auto_increment_offset=1

《注意:本人注掉了slave-skip-errors=all。auto_increment_increment=2，auto_increment_offset=1
如不注掉，本人觉得会有点问题，至少不能自动增长时ID不能自动增长，而会1,3,5,增长。》


------master2----------
log-bin=mysql-bin
server-id = 2
binlog-do-db=qin
binlog-ignore-db=mysql
replicate-do-db=qin
replicate-ignore-db=mysql
log-slave-updates
#slave-skip-errors=all
sync_binlog=1
#auto_increment_increment=2
#auto_increment_offset=2

2、将192.168.1.17设为192.168.1.20的主服务器
在192.168.1.17上新建授权用户
grant replication slave on *.* to 'rep'@'%' identified by 'tomcat2008' ;flush privileges;
MySQL> show master status;  
+------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| MySQL-bin.000003 |      374 |              |                  |   
+-----------------
-+----------+--------------+------------------+  

在192.168.1.20上将192.168.1.17设为自己的主服务器
MySQL> change master to master_host='192.168.1.17',master_user='rep',master_password='tomcat2008',master_log_file='mysql-bin.000003',master_log_pos= 2595;
MySQL> start slave;  
MySQL> show slave status\G
看到：
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  
表示配置成功。

3、将192.168.1.20设为192.168.1.17的主服务器


MySQL>grant replication slave on *.* to 'rep'@'%' identified by 'tomcat2008' ;flush privileges;
MySQL> show master status;  
+------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| MySQL-bin.000003 |      374 |              |                  |   
+------------------+----------+--------------+------------------+   

在192.168.1.17上，将192.168.1.20设为自己的主服务器

MySQL> change master to master_host='192.168.1.20',master_user='rep',master_password='tomcat2008',master_log_file='mysql-bin.000002',master_log_pos=2759;
MySQL> start slave;  
MySQL> show slave status\G   
看到：
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  
表示配置成功。

4、MySQL同步测试

二、keepalived安装及配置

1、192.168.1.17服务器上keepalived安装及配置
下载keepalived
wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
安装keepalived
Yum install kernel-xen* -y
Yum install openssl*
#tar zxvf keepalived-1.2.7.tar.gz  
#cd keepalived-1.2.7  
# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-348.6.1.el5xen-x86_64/
#make && make install
配置keepalived

我们自己在新建一个配置文件，默认情况下keepalived启动时会去
/etc/keepalived目录下找配置文件
#mkdir /etc/keepalived
#vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived  
global_defs {  
     notification_email {  
     qinang@eneysh.com  
     }  
     notification_email_from qinang@eneysh.com  
     smtp_server 127.0.0.1  
     smtp_connect_timeout 30  
     router_id MySQL-ha  
     }  

vrrp_instance VI_1 {  
     state lnx21   #两台配置此处均是BACKUP
     interface eth0  
     virtual_router_id 51  
     priority 100   #优先级，另一台改为90   
     advert_int 1  
     nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
     authentication {  
     auth_type PASS  
     auth_pass 1111  
     }  h_pass tomcat2008
     virtual_ipaddress {  
     192.168.1.238
     }  
     }  

virtual_server 192.168.1.238 3306 {  
     delay_loop 2    #每个2秒检查一次real_server状态   
     lb_algo wrr   #LVS算法  
     lb_kind DR    #LVS模式  
     persistence_timeout 60    #会话保持时间  

     protocol TCP  
     real_server 192.168.1.17 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
批量杀进程pkill keepalived

2、192.168.1.20上keepalived安装及配置

下载keepalived
wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
安装keepalived
Yum install kernel-xen* -y
Yum install openssl*
#tar zxvf keepalived-1.2.7.tar.gz  
#cd keepalived-1.2.7  
# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-348.6.1.el5xen-x86_64/
#make && make install
配置keepalived
pkill keepalived;/usr/local/keepalived/sbin/keepalived -D

这台配置和上面基本一样，但有两个地方不同：优先级为90、无抢占设置
#mkdir /etc/keepalived
#vi /etc/keepalived/keepalived.conf
/etc/keepalived
[root@lnx20 keepalived]# more keepalived.conf
! Configuration File for keepalived  
global_defs {  
     notification_email {  
     qinang@eneysh.com  
     }  
     notification_email_from qinang@eneysh.com  
     smtp_server 127.0.0.1  
     smtp_connect_timeout 30  
     router_id MySQL-ha  
     }  

vrrp_instance VI_1 {  
     state lnx20
     interface eth0  
     virtual_router_id 51  
     priority 90  
     advert_int 1  
     authentication {  
     auth_type PASS  
     auth_pass tomcat2008
     }  
     virtual_ipaddress {  
     192.168.1.238
     }  
     }  

virtual_server 192.168.1.238 3306 {  
     delay_loop 2  
     lb_algo wrr  
     lb_kind DR  
     persistence_timeout 60  
     protocol TCP  
     real_server 192.168.1.20 3306 {  
     weight 3  
     notify_down /usr/local/mysql/bin/MySQL.sh  
     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


pkill keepalived

/usr/local/keepalived/sbin/keepalived -D
配置完毕
跟踪 tcpdump vrrp

测试，让脚本往数据库的表中插入数据。一直插入。然后把主的mysql停止掉。那么会自动切换到从的服务器。
后期主服务器mysql恢复正常，首先要先在从的服务器执行pkill keepalived ;再在主的上面执行
pkill keepalived;/usr/local/keepalived/sbin/keepalived -D ;然后再在从的上面执行/usr/local/keepalived/sbin/keepalived -D
这样又切换到主的上面来了。
　　

　　

　　以上文档完全是根据网上资料修改过来的。不过本人做了实验。是OK的。

　　

　　

　　

　　

　　

　　
xp_cmdshell 'net use \\192.168.0.1\c$ "密码" /user:用户'