MySQL主主复制

环境

OS:CentOS 6.7,MySQL 5.6

Master:192.168.137.10 hosts:master

Backup:192.168.137.20 hosts:slave

VIP:192.168.137.50

Master配置

修改cnf配置文件：

server_id =10

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.137.20', 

master_user = 'repl', 

master_password = 'repl', 

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

master_log_pos = 120;

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

start slave;

Backup配置

server_id =20

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.137.10', 

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/sbin/keepalived /usr/bin/

keepalived配置

master

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 100           #优先级，backup设置120 master设置150
 advert_int 1
 nopreempt             #不主动抢占资源，两台都设置
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 virtual_ipaddress {
 192.168.137.50  #虚拟IP
 }
}
virtual_server 192.168.137.50 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.137.10 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 #重试间隔时间

  }
}
}

backup

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 90           #优先级，backup设置120 master设置150
 advert_int 1
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 virtual_ipaddress {
 192.168.137.50
 }
}

virtual_server 192.168.137.50 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.137.20 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 #重试间隔时间

     }
   }
 }

注意：nopreempt（不主动抢占）针对优先级高的一方是有效的；如果不使用 nopreempt 那么优先级高的服务器每次重启都会把VIP抢占过去，这样会导致VIP频繁切换。正常情况下VIP切换到了哪一台服务器就把那台作为主不需要再抢占回去，目前测试nopreempt针对优先级低的一方没有效果，优先级低的不加上该参数也不会把优先级高的VIP抢占走。

将keepalived加入系统启动项

chkconfig --add keepalived

chkconfig  keepalived on

vim /usr/local/keepalived/mysql.sh

#!/bin/bash

pkill keepalived

修改文件可执行权限

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

启动Keepalived

service keepalived start

查看VIP状态

ip add

注意：在文件配置中使用备注千万要注意，要确保你的备注被识别为备注，之前就遇到过这样的问题在代码后使用#备注由于#和实际的代码挨在一起被程序当成了代码导致整个配置出现了问题，特别是这种问题很难找，所以代码中使用备注千万要小心。