MySQL 主主复制

环境

OS:CentOS 6.7,MySQL 5.6

Master:192.168.80.129  hosts:master

Backup:192.168.80.132 hosts:slave

VIP:192.168.80.133

 

Master配置

修改129的cnf配置文件：

server_id =129

log_bin=/usr/local/mysql/data/mysql_bin

max_binlog_size = 1G

binlog_format=MIXED

#binlog-do-db=

binlog-ignore-db=test #这里记住不要加入系统数据库，多个数据库以逗号隔开

#replicate-do-db=

replicate-ignore-db=test

log-slave-updates

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=1

 

重启mysql

Service mysql restart

查看binglog位置

SHOW MASTER STATUS

由于是刚创建默认是：mysql_bin.000001:120

 

创建复制用户：mysql –uroot -proot

GRANT  REPLICATION SLAVE ON *.* TO 'repl'@'192.168.80.%' IDENTIFIED  BY 'repl';

flush  privileges;

change master to 

master_host = '192.168.80.132', 

master_user = 'repl', 

master_password = 'repl', 

master_log_file = 'mysql_bin.000001',  #注意binlog的命名

master_log_pos = 120;

 

启动复制，先在132上创建完复制用户再执行

start slave;

 

Backup配置

server_id =132

log_bin=/usr/local/mysql/data/mysql_bin

max_binlog_size = 1G

binlog_format=MIXED

#binlog-do-db=

binlog-ignore-db=test

#replicate-do-db=

replicate-ignore-db=test #这里记住不要加入系统数据库，多个数据库以逗号隔开

log-slave-updates

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=2

 

重启mysql

Service mysql restart

查看binglog位置

SHOW MASTER STATUS

由于是刚创建默认是：mysql_bin.000001:120

 

创建复制用户：mysql –uroot -proot

GRANT  REPLICATION SLAVE ON *.* TO 'repl'@'192.168.80.%' IDENTIFIED  BY 'repl';

flush  privileges;

change master to 

master_host = '192.168.80.129', 

master_user = 'repl', 

master_password = 'repl', 

master_log_file = 'mysql-bin.000001',  #注意binlog的命名

master_log_pos = 120;

 

启动复制

start slave;

 

master keeplived安装配置

通过keeplived管理虚拟IP，并执行故障切换。

yum install openssl openssl-devel -y

wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

tar -xvf 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/keepalived.conf.bak

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

vim /etc/keepalived/keepalived.conf

 

! Configuration File forkeepalived

global_defs {

notification_email {

test@163.com

 }

notification_email_from  ha@163.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MYSQL      #组标识，双主相同  

 }

vrrp_instance VI_1 {

 state BACKUP           #两台都设置BACKUP

 interface eth0

 virtual_router_id 51       #主备相同

 priority 150           #优先级，backup设置120 master设置150

 advert_int 1

 nopreempt             #不主动抢占资源，两台都设置

 authentication {

 auth_type PASS

 auth_pass 1111

 }

 virtual_ipaddress {

 192.168.80.133  #虚拟IP

 }

}

virtual_server 192.168.80.133 3306 {

 delay_loop 2

 #lb_algo rr              #LVS算法，用不到，我们就关闭了

 #lb_kind DR              #LVS模式如果不关闭备用服务器不能通过VIP连接主MySQL

 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器

 protocol TCP

 real_server 192.168.80.129 3306 {#检测本地mysql，backup也要写检测本地mysql

 weight 3

 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时，执行此脚本，杀死keepalived实现切换

 TCP_CHECK {

 connect_timeout 3    #连接超时

 nb_get_retry 3       #重试次数

 delay_before_retry 3 #重试间隔时间

  }

}

}

 

将keepalived加入系统启动项

chkconfig --add keepalived

chkconfig  keepalived on

 

vim /usr/local/keepalived/mysql.sh

#!/bin/bash

#pkill keepalived

service keepalived stop

 

修改文件可执行权限

chmod +x /usr/local/keepalived/mysql.sh

启动Keepalived

service keepalived start

查看VIP状态

ip add

 

backup keepalived配置

只需要修改keepalived.conf的红色项就可以

 

 

问题：1.当master和backup都配置成“state BACKUP”无论怎样修改“nopreempt ”哪个keeplived重启，VIP就跳到哪个keeplived上了。

2.使用workbench连接VIP隔断时间就会自动中断，其它的客户端不会

3.如果master配置成了“state MASTER”当现在的VIP是backup隔段时间会被master抢占了VIP，master配置不配置“nopreempt ”都一样。

4.如果不加上“notify_down /usr/local/keepalived/mysql.sh”这个结束keepalived操作，如果不配置“nopreempt ”当一台宕机了另一台需要间隔10几分钟才会抢占VIP，如果配置了“nopreempt ”无论优先级高低都不会抢占VIP过来，所以这个步骤还是必须的。

 

备注：     作者：pursuer.chen     博客：http://blog.csdn.net/chast_cn 本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接。 《欢迎交流讨论》