当前位置：首页 > news >正文

Mysql+keepalived搭建

news 来源：原创 2024/5/3 2:57:44

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
这样又切换到主的上面来了。