Mysql+keepalived搭建

yonght

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
[iyunv@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的。

midea2

与时俱进，你我共赴高潮!

jgugugiug

俺从不写措字，但俺写通假字!

mm111222

所有刻骨铭心的爱都灵魂游离于床上的瞬间！

快乐的老鼠

丑，但是丑的特别，也就是特别的丑！

ABKYH

脱了衣服我是禽兽，穿上衣服我是衣冠禽兽!

352262

走，MM，咱们化蝶去……