mysql keepalived主主同步_mysql主主同步+keepalived

1.安装数据库，配置主主，然后在keepalived

数据库安装完毕

yum install mysql-server yum install mysql-devel

========================================================

[root@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.cnblogs.com/xiaoit/p/3977843.html

http://www.linuxidc.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重启--------------

[root@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搞定了。。。。。