Mysql双主复制搭建和基于keepalived的故障转移
来源：互联网 发布：北京大学网络投诉 编辑：程序博客网 时间：2024/06/01 17:25
1.前言
前面我们介绍了mysql的主从复制(http://blog.csdn.net/wjf870128/article/details/45149573)、mysql5.6新出的GTID复制技术(http://blog.csdn.net/wjf870128/article/details/45151669)及mysql的半同步复制技术(http://blog.csdn.net/wjf870128/article/details/45170089)。
当前越来越多的互联网行业使用Mysql的主从复制功能实现读写分离，来降低对数据库端的业务负载。通过在master上进行写操作；slave节点上进行只读操作来提高数据库端的响应速度。如下图：
但是如果是基于M-S架构的话，如果master端出现故障，我们的只读业务同样会受到影响，因此呢，我们可以通过构建M-M-S架构，来实现对master节点单点故障的排除，同时保证了slave端能够接收到复制信息。架构图如下：
Mysql5.6中实现了GTID的复制技术，通过change master to MASTER_AUTO_POSITION = 1，数据库服务器可以自动的从master中根据transaction id抓取需要的日志进行前滚恢复，我们可以利用这一新特性，可以实现master1故障恢复时的自动同步功能。
2.基于GTID的双主搭建
mysql的安装、GTID和半同步复制的原理和搭建过程前文已经介绍过，这里就省略了
。
2.1 环境
OS：Redhat 6.5 32位
DB：5.6.23-enterprise-commercial-advanced-log
Master1：
ip：192.168.163.8
hostname：mysql-rep01
Master2：
ip：192.168.163.9
hostname：mysql-rep02
2.2 Master1节点配置/etc/my.cnf
<span style="font-size:12px;">explicit_defaults_for_timestamp log_bin = mysql-binbinlog_format = rowgtid_mode=ONlog-slave-updatesenforce-gtid-consistencyrpl_semi_sync_master_enabled=1rpl_semi_sync_slave_enabled=1auto_increment_increment=2auto_increment_offset=1server_id = 1datadir = /var/lib/mysqlport = 3306socket=/var/lib/mysql/mysql.sockrelay-log=mysql-rep01-relay-binrelay-log-recovery=1relay-log-purge=1relay-log-info-repository=tablemaster-info-repository=table
2.3 Master2节点配置/etc/my.cnf
<span style="font-size:12px;">explicit_defaults_for_timestamplog_bin = mysql-binserver_id = 2datadir = /var/lib/mysqlport = 3306socket=/var/lib/mysql/mysql.sockbinlog_format = rowgtid_mode=ONlog-slave-updatesenforce-gtid-consistencyrpl_semi_sync_master_enabled=1rpl_semi_sync_slave_enabled=1auto_increment_increment=2auto_increment_offset=2relay-log=mysql-rep02-relay-binrelay-log-recovery=1relay-log-purge=1relay-log-info-repository=tablemaster-info-repository=table
2.4 创建复制用户
master1
mysql> CREATE USER 'repl'@'mysql-rep02' IDENTIFIED BY 'slavepass';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'mysql-rep02';
master2
mysql> CREATE USER 'repl'@'mysql-rep01' IDENTIFIED BY 'slavepass';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'mysql-rep01';
2.5 节点复制配置
master1
mysql> CHANGE MASTER TO -> MASTER_HOST='mysql-rep02', -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_AUTO_POSITION=1, -> MASTER_CONNECT_RETRY=45, -> MASTER_RETRY_COUNT=5, -> MASTER_HEARTBEAT_PERIOD=30;
mysql> CHANGE MASTER TO -> MASTER_HOST='mysql-rep01', -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_AUTO_POSITION=1, -> MASTER_CONNECT_RETRY=45, -> MASTER_RETRY_COUNT=5, -> MASTER_HEARTBEAT_PERIOD=30;
2.6 启动slave
在两个节点中执行：
mysql> start slave;
2.7查看节点状态
查看节点master1中的状态
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-rep02 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000020 Read_Master_Log_Pos: 231 Relay_Log_File: mysql-rep01-relay-bin.000019 Relay_Log_Pos: 401 Relay_Master_Log_File: mysql-bin.000020 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 231 Relay_Log_Space: 1492 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: f18073c6-de8d-11e4-aa0e-0050563260e0 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 3 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: f18073c6-de8d-11e4-aa0e-0050563260e0:44-49 Executed_Gtid_Set: 485d870d-ddf8-11e4-af9e-000c2957f0bc:1-68,f18073c6-de8d-11e4-aa0e-0050563260e0:1-49 Auto_Position: 1
mysql> show processlist;+----+-----------------+-------------------+------+------------------+------+-----------------------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-------------------+------+------------------+------+-----------------------------------------------------------------------------+------------------+| 3 | root | localhost | NULL | Query | 0 | init | show processlist || 20 | event_scheduler | localhost | NULL | Daemon | 4367 | Waiting on empty queue | NULL || 27 | repl | mysql-rep02:47007 | NULL | Binlog Dump GTID | 4271 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL || 30 | system user | | NULL | Connect | 4195 | Waiting for master to send event | NULL || 31 | system user | | NULL | Connect | 4194 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |+----+-----------------+-------------------+------+------------------+------+-----------------------------------------------------------------------------+------------------+
查看节点master2的状态
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-rep01 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000032 Read_Master_Log_Pos: 612 Relay_Log_File: mysql-rep02-relay-bin.000048 Relay_Log_Pos: 408 Relay_Master_Log_File: mysql-bin.000032 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 612 Relay_Log_Space: 587 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 485d870d-ddf8-11e4-af9e-000c2957f0bc Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 3 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 485d870d-ddf8-11e4-af9e-000c2957f0bc:1-68,f18073c6-de8d-11e4-aa0e-0050563260e0:1-49 Auto_Position: 11 row in set (0.00 sec)
mysql> show processlist;+----+-------------+-------------------+------+------------------+------+-----------------------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+-------------------+------+------------------+------+-----------------------------------------------------------------------------+------------------+| 1 | system user | | NULL | Connect | 4340 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL || 2 | system user | | NULL | Connect | 4341 | Waiting for master to send event | NULL || 3 | root | localhost | NULL | Query | 0 | init | show processlist || 7 | repl | mysql-rep01:43897 | NULL | Binlog Dump GTID | 4264 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |+----+-------------+-------------------+------+------------------+------+-----------------------------------------------------------------------------+------------------+
2.8 验证双主复制
一般而言，架构为M-M-S中，master2是不参与相关的写操作的，防止相关冲突的发生。这里，我们通过设置
auto_increment_incrementauto_increment_offset
来防止auto_increment字段产生的主键冲突问题。但是仍然不能避免同一行的锁冲突问题，因此生产环境当中通常将Master2当作只读服务器。查看双主复制验证如下：
master1：
mysql> use test;mysql> create table test_ic(id int unsigned not null auto_increment,var varchar(10) not null,primary key(id));Query OK, 0 rows affected (0.10 sec)mysql> insert into test_ic(var) values('1a');Query OK, 1 row affected (0.05 sec)mysql> select * from test_ic;+----+-----+| id | var |+----+-----+| 1 | 1a |+----+-----+1 row in set (0.00 sec)master2：mysql> use test;Database changedmysql> select * from test_ic;+----+-----+| id | var |+----+-----+| 1 | 1a |+----+-----+1 row in set (0.00 sec)mysql> insert into test_ic(var) values('2b');Query OK, 1 row affected (0.05 sec)mysql> select * from test_ic;+----+-----+| id | var |+----+-----+| 1 | 1a || 2 | 2b |+----+-----+2 rows in set (0.00 sec)master1：mysql> select * from test_ic;+----+-----+| id | var |+----+-----+| 1 | 1a || 2 | 2b |+----+-----+2 rows in set (0.00 sec)可见master1和master2中同时写入没有问题，双主复制配置成功。
由于GTID的特性，当一个节点宕机重新连接的时候会主动同步来自另一节点的事务达到一致。
3.KeepAlived实现故障转移
3.1 KeepAlived作用
KeepAlived在此的作用是通过自定义的脚本来检测Mysql服务器的状态，如果一台Mysql服务器宕机或者出现故障，KeepAlived通过脚本检测到，将有故障的机器从集群中剔除；当Mysql服务器恢复正常的时候再加入到集群当中。
3.2 配置环境
OS：Redhat 6.5 32位
Soft：keepalived-1.2.16.tar.gz
VIP：192.168.163.10
Master1：
ip：192.168.163.8
hostname：mysql-rep01
Master2：
ip：192.168.163.9
hostname：mysql-rep02
3.3 安装步骤
master1和master2节点上同时安装：
[root@mysql-rep01 keepalived-1.2.16]# yum -y install gcc openssl-devel openssl popt popt-devel [root@mysql-rep01 keepalived]# tar zxf keepalived-1.2.16.tar.gz [root@mysql-rep01 keepalived]# cd keepalived-1.2.16[root@mysql-rep01 keepalived-1.2.16]# ./configure [root@mysql-rep01 keepalived-1.2.16]# make&&make install
[root@mysql-rep01 init.d]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/[root@mysql-rep01 init.d]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/[root@mysql-rep01 init.d]# mkdir /etc/keepalived[root@mysql-rep01 init.d]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/[root@mysql-rep01 init.d]# cp /usr/local/sbin/keepalived /usr/sbin[root@mysql-rep01 init.d]# chkconfig --add keepalived[root@mysql-rep01 init.d]# chkconfig --level 345 keepalived on[root@mysql-rep01 init.d]# chkconfig --list|grep -i keepalivedkeepalived 0:off 1:off 2:off 3:on 4:on 5:on 6:off
3.4 配置步骤
KeepAlived启动的时候默认使用/etc/keepalived/keepalived.conf作为启动文件，这里我用是贺春旸老师在<<Mysql管理之道>>一书中用到的配置和脚本(书中变量因为是基于5.5的可能有错误，下面的是我改正后的5.6版本的脚本)。如下：master1：/etc/keepalived/keepalived.conf
! Configuration File for keepalivedglobal_defs { router_id KeepAlive_MySQL}vrrp_script check_run { script "/home/sh/mysql_check.sh" interval 30}vrrp_sync_group VG1 {group { VI_1 }}vrrp_instance VI_1 { state BACKUP interface eth0 virtual_router_id 51 priority 100 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } track_script { check_run } notify_master "/home/sh/master.sh" notify_backup "/home/sh/backup.sh" notify_stop "/home/sh/stop.sh" virtual_ipaddress { 192.168.163.10 }}master2：
! Configuration File for keepalivedglobal_defs { router_id KeepAlive_MySQL}vrrp_script check_run { script "/home/sh/mysql_check.sh" interval 30}vrrp_sync_group VG1 {group { VI_1 }}vrrp_instance VI_1 { state BACKUP interface eth1 virtual_router_id 51 priority 90 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } track_script { check_run } notify_master "/home/sh/master.sh" notify_backup "/home/sh/backup.sh notify_stop "/home/sh/stop.sh virtual_ipaddress { 192.168.163.10 }}注：
为防止master1宕机恢复正常后于master2进行vip的争执接管，这里我们使用的conf文件中状态为backup，同时指定nopreempt参数来防止二次切换的发生。
/home/sh/mysql_check.sh 是监控脚本
#!/bin/bash. /root/.bash_profilecount=1while truedomysql -uroot -ppassword -e "show status;">/dev/null 2>&1i=$?ps aux|grep mysqld|grep -v grep>/dev/null 2>&1j=$?if [ $i = 0 ]&&[ $j = 0 ]thenexit 0else if [ $i = 1 ]&&[ $j = 0 ] then exit 0 else if [ $count -gt 5 ] then break fi let count++ continue fifidone
/home/sh/master.sh
#!/bin/bash. /root/.bash_profileMaster_Log_File=$(mysql -uroot -ppassword -e "show slave status\G"|grep -w Master_Log_File|awk -F": " '{print $2}')Relay_Master_Log_File=$(mysql -uroot -ppassword -e "show slave status\G"|grep -w Relay_Master_Log_File|awk -F": " '{print $2}')Read_Master_Log_Pos=$(mysql -uroot -ppassword -e "show slave status\G"|grep -w Read_Master_Log_Pos|awk -F": " '{print $2}')Exec_Master_Log_Pos=$(mysql -uroot -ppassword -e "show slave status\G"|grep -w Exec_Master_Log_Pos|awk -F": " '{print $2}')i=1while truedoif [ $Master_Log_File = $Relay_Master_Log_File ]&&[ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ]then echo "ok" breakelse sleep 1 if [ $i -gt 60 ] then break fi continue let i++fidonemysql -uroot -ppassword -e "stop slave;"mysql -uroot -ppassword -e "set global innodb_support_xa=1;"mysql -uroot -ppassword -e "set global sync_binlog=0;"mysql -uroot -ppassword -e "set global innodb_flush_log_at_trx_commit=2;"mysql -uroot -ppassword -e "set global event_scheduler=1;"mysql -uroot -ppassword -e "flush logs;"mysql -uroot -ppassword -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt/home/sh/backup.sh
#!/bin/bash. /root/.bash_profilemysql -uroot -ppassword -e "set global innodb_support_xa=1;"mysql -uroot -ppassword -e "set global sync_binlog=0;"mysql -uroot -ppassword -e "set global innodb_flush_log_at_trx_commit=2;"mysql -uroot -ppassword -e "set global event_scheduler=0;"
/home/sh/stop.sh
#!/bin/bash. /root/.bash_profilemysql -uroot -ppassword -e "set global innodb_support_xa=1;"mysql -uroot -ppassword -e "set global sync_binlog=1;"mysql -uroot -ppassword -e "set global innodb_flush_log_at_trx_commit=1;"M_File1=$(mysql -uroot -ppassword -e "show master status\G"|awk -F': ' '/File/{print $2}')M_Positon1=$(mysql -uroot -ppassword -e "show master status\G"|awk -F': ' '/Position/{print $2}')sleep 1M_File2=$(mysql -uroot -ppassword -e "show master status\G"|awk -F': ' '/File/{print $2}')M_Positon2=$(mysql -uroot -ppassword -e "show master status\G"|awk -F': ' '/Position/{print $2}')i=1while truedoif [ $M_File1 = $M_File2 ]&&[ $M_Positon1 -eq $M_Positon2 ]then echo "ok" breakelse sleep 1 if [ $i -gt 60 ] then break fi continue let i++fidone3.5 启动KeepAlived
[root@mysql-rep01 sh]# service keepalived startStarting keepalived: [ OK ][root@mysql-rep01 sh]# ip addr1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000 link/ether 00:0c:29:57:f0:bc brd ff:ff:ff:ff:ff:ff inet 192.168.163.8/24 brd 192.168.163.255 scope global eth0 <span style="color:#ff6666;">inet 192.168.163.10/32 scope global eth0</span> inet6 fe80::20c:29ff:fe57:f0bc/64 scope link valid_lft forever preferred_lft forever
此时KeepAlived就配置完成了。当master1出现故障的时候，master2上的keepalived会自动接管VIP。
0 0
- Mysql双主复制搭建和基于keepalived的故障转移
- MySQL主主复制，mysql主从复制，MySQL+keepalived故障转移。
- mysql集群2-双主故障转移keepalived
- MySQL:基于双主复制的keepalived的HA方案
- Redis + Keepalived主从集群的搭建及故障转移
- 基于 MySQL 5.6 keepalived 的双主搭建
- 基于MySQL 5.7多源复制+Keepalived搭建高可用
- mysql + keepalived 双主复制结构 keepalived配置文件keepalived.conf
- keepalived+redis 实现高可用的自动故障转移failover
- 基于keepalived双主高可用复制搭建实验
- 基于主主复制的mysql双机热备+keepalived实现高可用性
- 基于MySQL 5.7多源复制及Keepalived搭建三节点高可用架构
- keepalived的双主搭建
- MySQL 高可用：mysql+Lvs+Keepalived 负载均衡及故障转移
- MySQL高可用方案：基于MHA实现的自动故障转移群集
- MySQL高可用方案：基于MHA实现的自动故障转移群集
- MySql基于GTID主从复制的搭建
- mysql主主复制和keepalived配置过程
- 计算机网络面试知识汇总（不定期更新）
- 0x 颜色对应代码
- 七、Linux网络编程-TCP客户/服务器模型、回射客户/服务器
- subString
- HTML5 postMessage 消息传输与 POST 跨域通信
- Mysql双主复制搭建和基于keepalived的故障转移
- 企业IT项目开发之七宗罪（上篇）
- struts需要的jar包
- UINavigationController
- Java中GC的工作原理
- 指针与引用
- 多处最优服务次序问题
- javascript 生成笛卡尔积
- JVM自定义参数
