Keepalived + Mysql 双主

buns

VIP    192.168.1.41
Master 192.168.1.42
Slave  192.168.1.43
1、配置
yum -y install mysql-server
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start
登录
mysql -u root

Master
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#开启binlog日志功能
log-bin=mysql-bin
#会打印mysql的所以sql语句
log=/var/log/mysql.log
#两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可
server-id=1
#需要同步的库名称
binlog-do-db=mysql
auto-increment-increment=2
auto-increment-offset=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
scp /etc/my.cnf 192.168.1.43:/etc/my.cnf
Slave
vim /etc/my.cnf
#修改Server-id
server-id=2

2、建授权用户
在Master上新建授权用户
grant replication slave on *.* to 'user01'@'192.168.1.%' identified by '123456';
在Slave服务器上建授权用户
grant replication slave on *.* to 'user01'@'192.168.1.%' identified by '123456';
3、将Master设为Slave的主服务器                                                                                                                                                                                                                                                                                                        
在192.168.1.43上将192.168.1.42设为自己的主服务器
在Master 上执行查询数据库日志号状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      387 | mysql        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在Slave 执行
MySQL> change master to master_host='192.168.1.42',master_user='user01',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=387;
Query OK, 0 rows affected (0.05 sec)
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes   \\如果此2项都为yes，master-master配置即成功

4、将Slave设为Master的主服务器
在192.168.1.42上将192.168.1.43设为自己的主服务器
在Slave 上执行查询数据库日志号状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      265 | mysql        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在Master 执行
MySQL> change master to master_host='192.168.1.43',master_user='user01',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=265;
Query OK, 0 rows affected (0.05 sec)
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes   \\如果此2项都为yes，master-master配置即成功
5、测试
如上述均正确配置，现在在任何一台MySQL上更新数据都会同步到另一台MySQL(仅限mysql库)
select user from mysql.user;
create user 'test'@'192.168.1.%' identified by 'www';
6、keepalived 部署并配置
在Master 上配置
yum install -y gcc openssl-devel popt-devel
tar zxvf keepalived-1.2.19.tar.gz
cd keepalived-1.2.19
./configure --prefix=/usr/local/keepalived
make && make install
cp  /usr/local/keepalived/etc/rc.d/init.d/keepalived   /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived   /etc/sysconfig/
mkdir /etc/keepalived/
cp /usr/local/keepalived/etc/keepalived/keepalived.conf   /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived  /usr/sbin/
chmod +x /etc/init.d/keepalived
vim /etc/keepalived/keepalived.conf
=============================================
! Configuration File for keepalived
global_defs {
notification_email {
story_media@126.com
}
notification_email_from root@LDR.local
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL-HA
}

vrrp_instance VI_1 {
#两台配置此处均是BACKUP
state BACKUP
#注意网卡名称
interface eth0
#多套keepalived 在同一个局域网内该ID不能重复
virtual_router_id 60
#优先级，另一台改为90
priority 100
advert_int 1
#不主动抢占资源，只在优先级高的机器上设置即可，优先级低的机器不设置
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.41
}
}
virtual_server 192.168.1.41 3306 {
#每个2秒检查一次real_server状态
delay_loop 2
#LVS算法
lb_algo wrr
lb_kind DR
#会话保持时间
persistence_timeout 60
protocol TCP
real_server 192.168.1.42 3306 {
weight 3
#检测到服务down后执行的脚本
notify_down /etc/init.d/my.sh
TCP_CHECK {
#连接超时时间
connect_timeout 10
#重连次数
nb_get_retry 3
#重连间隔时间
delay_before_retry 3
#健康检查端口
connect_port 3306
}
}
}
================================================
编写检测服务down后所要执行的脚本
注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服务状态，当发现real_server服务故障时，便触发此脚本；
vim /etc/init.d/my.sh
#!/bin/sh
pkill keepalived
chmod +x /etc/init.d/my.sh
service keepalived start
ps aux | grep keepalived
此处测试如果关闭mysql服务keepalived 会通过/etc/ini.d/my.sh 将keepalived 进程杀死，VIP自动被另外一台服务器接管
在Slave 上配置
yum install -y gcc openssl-devel popt-devel
tar zxvf keepalived-1.2.19.tar.gz
cd keepalived-1.2.19
./configure --prefix=/usr/local/keepalived
make && make install
cp  /usr/local/keepalived/etc/rc.d/init.d/keepalived   /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived   /etc/sysconfig/
mkdir /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived  /usr/sbin/
chmod +x /etc/init.d/keepalived
在Master 上执行如下命令拷贝配置文件
scp /etc/keepalived/keepalived.conf 192.168.1.43:/etc/keepalived/keepalived.conf
scp /etc/init.d/my.sh 192.168.1.43:/etc/init.d/my.sh
编辑配置文件
vim /etc/keepalived/keepalived.conf
==========================================
! Configuration File for keepalived
global_defs {
notification_email {
story_media@126.com
}
notification_email_from root@LDR.local
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL-HA
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 60
#优先级修改为90
priority 90
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.41
}
}
virtual_server 192.168.1.41 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
#本机真实IP
real_server 192.168.1.43 3306 {
weight 3
notify_down /etc/init.d/my.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
=========================================
7、测试
两台MySQL服务器都要授权允许从远程登录
MySQL> grant all privileges on *.* to www@'%' identified by '123456';
Query OK, 0 rowsaffected (0.00 sec)
MySQL> flush privileges;
Query OK, 0 rowsaffected (0.00 sec)
　　


8、排错

keepalived ip address associated with VRID not present in received packet





参考：

　　http://blog.iyunv.com/gzh0222/article/details/7962954