mysql主主同步+keepalived

fgew2

1.安装数据库，配置主主，然后在keepalived
数据库安装完毕
yum install mysql-server yum install mysql-devel
========================================================
[iyunv@localhost ~]# rpm -qa|grep mysql
mysql-devel-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-5.1.73-3.el6_5.x86_64
mysql-server-5.1.73-3.el6_5.x86_64



mysqladmin -uroot password yaoshao123
主主同步
http://www.iyunv.com/xiaoit/p/3977843.html
http://www.iyunv.com/Linux/2013-05/83784.htm
（1）同步帐号
yao yaoshaoyun
grant replication slave on *.* to 'yao'@'%' identified by 'yaoshao123';
create database zabbix;
(2)vi /etc/my.conf
主
port = 3306
log_bin=/var/lib/mysql/mysql-binlog
server-id=1
binlog_do_db=zabbix
从
server-id=2
master-host=192.168.31.154
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db=zabbix
（3）主主新增添加以下

####server-id=1 原本为主########
#[mysqld]
master-host=192.168.31.154
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db = zabbix
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
####server-id=2 原本为备########
#[mysqld]

log_bin=/var/lib/mysql/mysql-binlog
binlog_do_db=zabbix
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2

开始同步，最好锁表，好让从服务器定位同步位置。初次同步完成后，记得解锁。
flush tables with read lock;
unlock tables;
分别查看两台mysql>show slave status是看到
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
说明同步成功


下面是两台的my.conf
A 192.168.31.154
#---------------byyao-------------
port = 3306
log_bin=/var/lib/mysql/mysql-binlog
server-id=1
binlog_do_db=zabbix
#------------zhuzhu--------------
master-host=192.168.31.109
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db = zabbix
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
#--------------byyao--------------

B192.168.31.109
#--------------byyao------------
server-id=2
master-host=192.168.31.154
master-user=yao
master-password=yaoshao123
master-port=3306
master-connect-retry=5
replicate-do-db=zabbix
#-------------zhuzhu------------
log_bin=/var/lib/mysql/mysql-binlog
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
#--------------byyao------------

主主同步效果图





往从数据库新建一个表yaoshao，在主数据库查看




----------------------------------------keepalived 配置-----------------------------------
vi /etc/keepalived/keepalived.conf

主AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
vrrp_instance VI_1 {
   state backup       #两台配置此处均是BACKUP
   interface eth0
   virtual_router_id 51
   priority 100       #优先级，另一台改为90
   advert_int 1
   nopreempt          #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
   authentication {
       auth_type PASS
       auth_pass 1111
   }
   virtual_ipaddress {
       192.168.31.122
   }
}

virtual_server 192.168.31.122 3306 {
   delay_loop 6
   lb_algo wrr
   lb_kind DR
   persistence_timeout 50        #会话保持时间
   protocol TCP

real_server 192.168.31.154 3306 {
       weight 3
       notify_down /usr/shell/killkeepalived.sh    #检测到mysql服务挂了就执行这个脚本（脚本
要自己写哈）
       TCP_CHECK {
       connect_timeout 10        #连接超时时间
       nb_get_retry 3            #重连次数
       delay_before_retry 3      #重连间隔时间
       connect_port 3306         #健康检查端口
        }
       }
}



备BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
vrrp_instance VI_1 {
   state backup
   interface eth0
   virtual_router_id 51
   priority 90
   advert_int 1
   authentication {
       auth_type PASS
       auth_pass 1111
   }
   virtual_ipaddress {
       192.168.31.122
   }
}

virtual_server 192.168.31.122 3306 {
   delay_loop 6
   lb_algo wrr
   lb_kind DR
   persistence_timeout 50
   protocol TCP

real_server 192.168.31.109 3306 {
       weight 3
       notify_down /usr/shell/killkeepalived.sh
       TCP_CHECK {
       connect_timeout 10
       nb_get_retry 3
       delay_before_retry 3
       connect_port 3306
           }
       }
}
------------------------脚本--两台都一样-----直接关闭，然后在corntab里尝试对mysqld和keepalived重启--------------
[iyunv@yao ~]# cat /usr/shell/killkeepalived.sh
#/bin/sh
/sbin/service keepalived stop
-----------------或者先判断尝试重启MYSQL不行在关闭keepalived--------
#!/bin/bash
#PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
A=`ps -C mysqld --no-header|wc -l`
if [ $A -eq 0 ];then
/etc/init.d/mysqld start ##这个地方写你nginx命令的路径
sleep 3
if [`ps -C mysqld --no-header|wc -l` -eq 0 ];then
killall keepalived
fi
fi


------------------------测试效果--------------------
两台机子都开起来，mysql -h 192.168.32.122 -uroot -pyaoshao123
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    19
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| B                  |
| test               |
| zabbix             |
+--------------------+
4 rows in set (0.04 sec)

===========关闭B这一台================
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    143
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| A                  |
| test               |
| zabbix             |
+--------------------+

稍微延迟

OK搞定了。。。。。