MySQL+Keepalived实现双机HA

视频的容积

host1与host3互为主从，即host1为host3的主机，同时也为host3的从机





host1 192.168.203.131

host2 192.168.203.132

host3 192.168.203.133



1.安装keepalived

www.keepalived.org



2.注意先清空防火墙

iptalbles -F



3.配置host1与host3互为主从并授权给host2上的登陆

grant all on *.* to 'replmonitor'@'192.168.203.%' identified by '000000' with grant option;



4.配置keealived.conf




! Configuration File for keepalived
host1上：
global_defs {
notification_email {
root@localhost.localdomain
}
notification_email_from root@localhost.localdomain
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id test-mysql-ha
}
vrrp_instance VI_1 {
state BACKUP
interface eth3 !当前机器的网络接口名称
virtual_router_id 51
priority 100  !优先级别
nopreempt   !重启后也不要拿回主服务权限
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.203.200  !虚拟ip地址，等会在host2上登陆用此ip
}
}
virtual_server 192.168.203.200 3306 { !此端口即为mysql的服务
delay_loop 1
lb_algo wrr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 192.168.203.131 3306 { !主机的ip地址
weight 1
notify_down /etc/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 10
bingto 192.168.203.200
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
host3上只需将上面加黑的部分做相应的更改并去掉nopreempt 即可
/etc/keepalived/mysql.sh的内容为：
#!/bin/bash
pkill keepalived





5.host1与host3开启keepalived，在host2上执行登陆mysql -h 192.168.203.200 -ureplmonitor -p （此host为虚拟的ip地址）

/etc/init.d/keepalived start



6.开始测试，我们将host1上的mysql停止

[iyunv@host1 keepalived]# /etc/init.d/mysql stop
Shutting down MySQL............ SUCCESS!
[iyunv@host1 keepalived]# ps -ef | grep keepalived
root      5409  5115  0 00:51 pts/2    00:00:00 grep keepalived



在host3上查询可知host3已经接管过虚拟的主机





我们继续在host2上执行操作语句





会首先提示连接失败并重新连接，并得到查询结果，故障失效迁移成功



7.在host3上执行：




mysql> delete from t1 where id in(4,5,6);
Query OK, 3 rows affected (0.01 sec)
在host1上重新开启mysql
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
|    5 |
|    6 |
+------+
5 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
数据全部同步过来，再停掉host3上的mysql，在host1上插入一行数据：
mysql> insert into t1 values(3);
在host2上进行查询：
mysql> select * from testdb.t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***
mysql> select * from testdb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
　　


可见数据也过来了，keepalive可以进行高效的数据迁移工作。