spring mysql双主架构_keepalived实现mysql双主高可用

一、地址规划

VIP：10.10.0.228

MasterA IP:10.10.0.224

MasterB IP:10.10.0.226

二、mysql双热热备master-master

1、安装mysql(编译或yum安装)

2、配置MasterA,修改配置文件,添加以下内容

# vim /etc/my.cnf

log-bin=mysql-bin

log=/home/mysqld.log

server-id=1

binlog-do-db=data1

auto-increment-increment=2

auto-increment-offset=2

配置MasterB,修改配置文件,添加以下内容

# vim /etc/my.cnf

log-bin=mysql-bin

log=/home/mysqld.log

server-id=2

binlog-do-db=data1

auto-increment-increment=2

auto-increment-offset=2

3、授权用户

MasterA:

mysql> grant replication slave on *.* to user@10.10.0.226 identified by '123456';

mysql> flush privileges;

MasterB:

mysql> grant replication slave on *.* to user@10.10.0.224 identified by '123456';

mysql> flush privileges;

4、准备复制

MasterA:

mysql> show master status\G

MasterB:

mysql> show master status\G

5、配置同步

MasterA:

mysql> change master to master_host='10.10.0.226',master_user='user',

master_password='123456',master_log_file='mysql-bin.000010',master_log_pos=106;

MasterB:

mysql> change master to master_host='10.10.0.224',master_user='user',

master_password='123456',master_log_file='mysql-bin.000010',master_log_pos=106;

6、测试验证同步

MasterA:

Mysql>show slave status \G

Slave_IO_Running: Yes这两个为yes表示正常

Slave_SQL_Running: Yes

Master B:

Mysql>show slave status \G

Slave_IO_Running: Yes这两个为yes表示正常

Slave_SQL_Running: Yes

Master A:

创建数据库

mysql>create databases data1;

Master B:

mysql> show databases;

可以发现数据库data1

Master B:

在small数据库里创建一张表，T1：

mysql> create table t1 select * from mysql.user;

Master A：

查看data1下面有t1这个表格：

mysql> show tables;

三、配置keepalived实现高可用

MasterA:

# yum install keepalived -y

# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

xxx@qq.com

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_slave

}

vrrp_script check_mysqld {

script "/etc/keepalived/check_slave.sh 127.0.0.1"

interval 2

weight 21

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_mysqld

}

virtual_ipaddress {

10.10.0.228/12 dev eth0 label eth0:0

}

}

MasterB:

1、安装keepalived

# yum install keepalived -y

2、配置

# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

xxx@qq.com

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_slave

}

vrrp_script check_mysqld {

script "/etc/keepalived/check_slave.pl 127.0.0.1"

interval 2

weight 21

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 80

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_mysqld

}

virtual_ipaddress {

10.10.0.228/12 dev eth0 label eth0:0

}

}

3、在MasterA和MasterB创建监控脚本,监控mysql服务是否启动以及mysql同步是否正常

# vim /etc/keepalived/check_slave.sh

#!/bin/bash

Mysql=$(mysql -N -s -e "select 10")

if [ $? -ne 0 ] || [ "$Mysql" -ne "10" ]

then

service keepalived stop

exit 1

else

SlaveStatus=($(mysql -e "show slave status\G;"|grep "_Behind|_Running"|awk '{print  $NF}'))

if [ "$SlaveStatus[0]" = "No" ] || [  "${SlaveStatus[1]}" = "No" ]

then

service keepalived stop

fi

fi

4、启动keepalived

# service keepalived start

可以参考

http://emg2012.blog.51cto.com/3705315/1616393