Keepalived + Mysql 双主

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.csdn.net/gzh0222/article/details/7962954