Contents

1 一.MySQL数据库管理之主从数据恢复及高可用
- 1.1 1.MySQL数据库一主多从架构方案
  - 1.1.1 1.1选主可选方案介绍
  - 1.1.2 1.2小结从库提升主库
- 1.2 2. MHA服务
2 二. 部署主从复制
3 三.配置binlog-server
- 3.1 3.1修改mha配置文件
  - 3.1.1 3.2备份binlog
  - 3.1.2 3.3测试binlog备份
4 四.配置VIP漂移
5 五.mysql中间件Atlas

一.MySQL数据库管理之主从数据恢复及高可用

1.MySQL数据库一主多从架构方案

问题：假如主库宕机如何解决

说明：架构为一主两从，分别为Master1，Slave1，Slave2；事先选择Slave1作为主库宕机的接管者

Master同步使用VIP进行提供服务，所有的从库也都是通过VIP建立同步

1.1选主可选方案介绍

1.半同步从库-谷歌半同步插件

S1作为主库的接管者
第一：主库插入数据后，不返回给用户，同时写入到S1，然后成功返回给用户
优点：两台库同时写入数据
缺点：写入会慢，网络不稳定主库持续等待
解决措施：
1、连不上S1的时候自动转为异步
2、设置10秒超时，超过10秒转为异步
3、S1网络，硬件要好，不提供服务，干等接管

2.S1，什么也不干只做同步的从库，随时做接管；某百度部门用的此套方案

3.主库宕机现选接管着（不推荐浪费时间）

主库宕机有两种情况：
1、如果主库宕机了然而可以SSH连接过去，binglog数据没有丢，要把主库的binlog补全到所有从库
第一步：调整S1为M1的操作
调整配置文件read-only，授权用户假如为select，需要变成增删改查，开启binlog
rm –f master.info relay-log*
登录数据库reset master
重启数据库

第二步：其余所有从库的操作：
CHANGE MASTER TO MASTER_HOST=‘172.16.1.68’(vip可以取消不执行),MASTER_LOG_FILE=’mysql-bin.000012’,MASTER_LOG_POS=107;

2.如果主库连不上（事先没有指定从库为主库）
如果有半同步选择半同步作为主库，补全其他从库数据
选主的方法确保IO和SQL都读取了自己的LOG，并应用到了数据库
第一步: 登录所有从库show processlist，看两个线程的更新状态
mysql> show processlist\G;
*************************** 1. row ***************************
 Id: 9
 User: system user
 Host: 
 db: NULL
Command: Connect
 Time: 49931
 State: Waiting for master to send event
 Info: NULL
*************************** 2. row ***************************
 Id: 10
 User: system user
 Host: 
 db: NULL
Command: Connect
 Time: 49727
 State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Info: NULL
*************************** 3. row ***************************
 Id: 12
 User: root
 Host: localhost
 db: NULL
Command: Query
 Time: 0
 State: NULL
 Info: show processlist
3 rows in set (0.00 sec)

查看是否更新完毕，
第二步：分别登录从库查看其master.info文件 
cat /data/{3307,3308}/data/master.info 
确保更新完毕后，查看master.info确定哪个从库POS最大，经过查看没有延迟的情况POS差距最少，提升为主，然后提升为主的从库（S1）补全所有中继日志至每个从库
如果从库没有开启binlog需要用relaylog进行补全其他从库

第三步：切换角色
假如S1延迟的差距最少，提升S1为主库
先在S1上配置好VIP
调整S1为M1的操作
调整配置文件read-only，授权用户select，变成增删改查，开启binlog
rm –f master.info relay-log*
登录数据库reset master
重启数据库
其余所有从库的操作：
CHANGE MASTER TO ,MASTER_LOG_FILE=’mysql-bin.000012’,MASTER_LOG_POS=107;

1.2小结从库提升主库

停止从库进程stop slave;
retset master;
删除master.info及relay-log*文件
开启log-bin日志文件
如果配置文件存在log-slave-updates，read-only等一定要注释，有关从库的配置参数都需要注释，需修改为跟之前主库的权限一致
重新启动数据库
修复主库为从库
从库配置VIP
hosts文件如果解析的是读和写IP就需要进行修改，如果解析的是VIP就不需要修改

2. MHA服务

2.1 软件简介

MHA（master high available）目前在MySQL高可用方面是一个相对成熟的解决方案，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到0~30秒之内自动完成数据的故障切换操作，并且在进行故障切换过程中，MHA能最大程度上保证数据的一致性，以达到真正意义上的高可用。

MHA由两部分组成：MHA manager（管理节点）和MHA Node（数据节点）。MHA Manager可以独立部署在一台独立的机器上管理多个Master-Slave集群，也可以部署在一台Slave上。当Master出现故障时，它可以自动将最新数据库的Slave提升为新的Master，然后将所有其他的Slave重新指向新的Master.整个故障转移过程对应用程序是完全透明的

2.2 工作流程

从宕机崩溃的master保存二进制日志事件（binlog events）

识别含义最新更新的slave

应用差异的中继日志（relay log）到其他的slave

提升一个slave为新的master

使其他的slave链接新的master进行复制

mha可以装在任何一台机器上，不一定要装在mysql服务器上，他可以装别的机器上，不需要做高可用。

2.3上传源码包并解压（所有节点）

MHA下载包链接：https://pan.baidu.com/s/1bGCq_vA-gMSmYIQb1WOVSA 密码：ev2y

#进入存放安装包目录
[root@node ~]# mkdir -p /server/tools
[root@node ~]# cd /server/tools
[root@node tools]# rz  mha4mysql-manager-0.56.tar.gz
[root@node tools]# rz  mha4mysql-node-0.56.tar.gz
[root@node tools]# tar xf mha4mysql-node-0.56.tar.gz
[root@node tools]# tar xf mha4mysql-manager-0.56.tar.gz

2.4 MHA工具介绍

MHA软件由两部分组成，manager工具包和Node工具包，具体的说明如下：

1.manager工具包主要包括以下几个工具：

[root@node tools]# cd mha4mysql-manager-0.56/
[root@node mha4mysql-manager-0.56]# cd bin/
[root@node bin]# ll
total 40
-rwxr-xr-x 1 4984 users 1995 Apr 1 2014 masterha_check_repl      #检查MySQL复制情况
-rwxr-xr-x 1 4984 users 1779 Apr 1 2014 masterha_check_ssh       #检查MHA的SSH配置情况
-rwxr-xr-x 1 4984 users 1865 Apr 1 2014 masterha_check_status    #检查当前MHA运行状态
-rwxr-xr-x 1 4984 users 3201 Apr 1 2014 masterha_conf_host       #添加或删除配置的host信息
-rwxr-xr-x 1 4984 users 2517 Apr 1 2014 masterha_manager         #启动MHA
-rwxr-xr-x 1 4984 users 2165 Apr 1 2014 masterha_master_monitor  #检测master是否宕机
-rwxr-xr-x 1 4984 users 2373 Apr 1 2014 masterha_master_switch   #控制故障转移（自动或手动）
-rwxr-xr-x 1 4984 users 5171 Apr 1 2014 masterha_secondary_check #试图建立TCP连接从远程服务器
-rwxr-xr-x 1 4984 users 1739 Apr 1 2014 masterha_stop            #停止MHA

2.Node工具包主要包括以下几个工具

[root@node bin]# cd /server/tools/mha4mysql-node-0.56/
[root@node mha4mysql-node-0.56]# cd bin/
[root@node bin]# ll
total 44
-rwxr-xr-x 1 4984 users 16367 Apr 1 2014 apply_diff_relay_logs  #识别差异的中继日志事件
-rwxr-xr-x 1 4984 users 4807 Apr 1 2014 filter_mysqlbinlog      #去除不必要的rollback事件
-rwxr-xr-x 1 4984 users 8261 Apr 1 2014 purge_relay_logs        #清除中继日志
-rwxr-xr-x 1 4984 users 7525 Apr 1 2014 save_binary_logs        #保存和复制master的二进制日志

二. 部署主从复制

2.1 环境检查

mysql-db01

[root@node ~]# cat /etc/redhat-release     #系统版本
CentOS Linux release 7.2.1511 (Core) 
[root@node ~]# uname -r                    #内核版本
3.10.0-327.el7.x86_64
[root@node ~]# hostname -I
118.190.201.68 172.16.1.68 
[root@node ~]# getenforce 
Disabled
[root@node ~]# systemctl status firewalld.service #停止

mysql-db02

[root@mysql-db02 ~]# cat /etc/redhat-release 
CentOS Linux release 7.2.1511 (Core) 
[root@mysql-db02 ~]# uname -r
3.10.0-327.el7.x86_64
[root@mysql-db02 ~]# hostname -I
118.190.201.69 172.16.1.69

mysql-db03

[root@mysql-db03 ~]# cat /etc/redhat-release 
CentOS Linux release 7.2.1511 (Core) 
[root@mysql-db03 ~]# uname -r
3.10.0-327.el7.x86_64
[root@mysql-db03 ~]# hostname -I
118.190.201.70 172.16.1.70

主机做host解析3台服务器

118.190.201.38 mysql-db03
118.190.201.68 mysql-db01
118.190.201.69 msyql-db02

2.2配置主从复制

2.1先决条件

主库和从库都要开启binlog
主库和从库server-id不同
要有主从复制用户

2.2主库操作

2.2.1修改配置文件

#编辑mysql配置文件
[root@mysql-db01 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1，从库必须大于1
server_id =1
#开启binlog日志
log_bin=mysql-bin

重启数据库生效

2.2.2创建主从复制用户

#登录数据库
[root@mysql-db01 ~]# mysql -uroot -p12345678 -S /data/3306/mysql.sock
#创建rep用户
mysql> grant replication slave on *.* to rep@'118.190.201.%' identified by '123456';
设置别名
~]# echo 'alias mysql3306="mysql -uroot -p12345678 -S /data/3306/mysql.sock"' >>/etc/profile
~]# source /etc/profile

2.3从库操作

2.3.1修改配置文件

#修改mysql-db02配置文件
[root@mysql-db02 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1，从库必须大于1
server_id =5
#开启binlog日志
log-bin=mysql-bin
#重启mysql
[root@mysql-db02 ~]# /etc/init.d/mysqld restart

#修改mysql-db03配置文件
[root@mysql-db03 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1，从库必须大于1
server_id =10
#开启binlog日志
log-bin=mysql-bin
#重启mysql
[root@mysql-db03 ~]# /etc/init.d/mysqld restart

注：在以往如果是基于binlog日志的主从复制，则必须要记住主库的master状态信息。

mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000029 |      258 |
+------------------+----------+

2.4开启GTID

#没开启之前先看一下GTID的状态
mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF   |
| gtid_executed            |       |
| gtid_mode                | OFF   |
| gtid_owned               |       |
| gtid_purged              |       |
+--------------------------+-------+
#编辑mysql配置文件（主库从库都需要修改）
[root@mysql-db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加
[mysqld]
gtid_mode=ON
log_slave_updates
enforce_gtid_consistency
#重启数据库
[root@mysql-db01 ~]# /etc/init.d/mysqld restart
#检查GTID状态
mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    | #执行GTID一致
| gtid_executed            |       |
| gtid_mode                | ON    | #开启GTID模块
| gtid_owned               |       |
| gtid_purged              |       |
+--------------------------+-------+

2.4.1什么是GTID （ Global transaction identifiers ）：

gtid简介借鉴于IMAJ博客园，若有侵权，告知速删

MySQL-5.6.2开始支持，MySQL-5.6.10后完善，GTID 分成两部分，一部分是服务的UUid,UUID保存在mysql数据目录的auto.cnf文件中,
这是一个非常重要的文件，不能删除，这一部分是不会变的。另外一部分就是事务ID了，随着事务的增加，值一次递增,如下图

+---------------+----------+--------------+------------------+--------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |            Executed_Gtid_Set               |
+---------------+----------+--------------+------------------+--------------------------------------------+
| binlog.000029 | 23556    |              |                  | 724afcc2-29d6-11e4-9902-000c290c0121:1-362 |
+---------------+----------+--------------+------------------+--------------------------------------------+
GTID:724afcc2-29d6-11e4-9902-000c290c0121:1-362
UUID:724afcc2-29d6-11e4-9902-000c290c0121
transactionId:1-362

在整个复制架构中GTID 是不变化的,即使在多个连环主从中也不会变。
例如：ServerA —>ServerB —->ServerC
GTID从在ServerA ,ServerB,ServerC 中都是一样的。

二、 GTID的工作原理：
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

三、 GTID的优点：
1.一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次
2.GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
3.减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机

那么GTID复制是怎么实现自动同步，自动对应位置的呢？
例如：ServerC <—–ServerA —-> ServerB
主机ServerA
备机:ServerB,ServerC
当主机ServerA 挂了之后，此时ServerB执行完了所有从ServerA 传过来的事务，
ServerC 延时一点。这个时候需要把 ServerB 提升为主机，Server C 继续为备机。
当ServerC 链接ServerC 之后,首先在自己的二进制文件中找到从ServerA 传过来的最新的GTID，
然后将这个GTID 发送到ServerB ,ServerB 获得这个GTID之后,就开始从这个GTID的下一个GTID
开始发送事务给ServerC。这种自我寻找复制位置的模式减少事务丢失的可能性以及故障恢复的时间。

四、 GTID的限制：
1.不支持非事务引擎
2.不支持create table … select 语句复制(主库直接报错)
原理：（会生成两个sql，一个是DDL创建表SQL，一个是insert into 插入数据的sql。
由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID ）
3.不允许一个SQL同时更新一个事务引擎表和非事务引擎表
4.在一个复制组中，必须要求统一开启GTID或者是关闭GTID
5.开启GTID需要重启（5.7除外）
6.开启GTID后，就不再使用原来的传统复制方式
7.对于create temporary table 和 drop temporary table语句不支持
8.不支持sql_slave_skip_counter

注：主库从库都需要开启GTID否则在做主从复制的时候就会报错：

[root@mysql-db02 ~]# mysql -uroot -p123456
mysql> change master to
-> master_host='118.190.201.68',
-> master_user='rep',
-> master_password='123456',
-> master_auto_position=1;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.
解决办法：
从库以需要在my.cnf文件里添加如下参数，重启
[mysqld]
gtid_mode=ON
log_slave_updates
enforce_gtid_consistency

2.5配置主从复制

从库执行如下语句：
mysql> change master to master_host='118.190.201.68',master_user='rep',master_password='123456',master_auto_position=1;
命令说明：
mysql> change master to
#主库IP
-> master_host='118.190.201.68',
#主库复制用户
-> master_user='rep',
#主库复制用户的密码
-> master_password='123456',
#GTID位置点
-> master_auto_position=1;
#开启slave
mysql> start slave;

然而查看slave状态的时候出现了如下错

"Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;\
these UUIDs must be different for replication to work." 这个错误提示。即主从架构中使用了相同的UUID。检查server_id系统
变量，已经是不同的设置

master_mysql> show variables like 'server_id';

slave_mysql> show variables like 'server_id';

查看是不同的。

但是查看/application/mysql/data/auto.cnf发现里面的UUID是相同的。原因是mysql是直接从节点1上拷贝过来而导致。

解决办法执行如下命令：
mv /application/mysql/data/auto.cnf{,.ori}

重启mysql解决

#查看slave状态
mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.16.10.12
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000004
 Read_Master_Log_Pos: 194
 Relay_Log_File: relay-bin.000004
 Relay_Log_Pos: 407
 Relay_Master_Log_File: mysql-bin.000004
 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: 194
 Relay_Log_Space: 879
 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: 0
Master_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: 12
 Master_UUID: 634c65b9-a63c-11eb-b407-000c295a9e39
 Master_Info_File: /var/lib/mysql/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
 Master_Retry_Count: 86400
 Master_Bind:
 Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
 Master_SSL_Crl:
 Master_SSL_Crlpath:
 Retrieved_Gtid_Set: 634c65b9-a63c-11eb-b407-000c295a9e39:1-2
 Executed_Gtid_Set: 61131005-a63c-11eb-88c1-000c29746152:1,
634c65b9-a63c-11eb-b407-000c295a9e39:1-2
 Auto_Position: 1
 Replicate_Rewrite_DB:
 Channel_Name:
 Master_TLS_Version:
1 row in set (0.00 sec)

2.6从库设置

#登录从库db02,db03
[root@mysql-db02 ~]# mysql -uroot -p123456
#禁用自动删除relay log 功能
mysql> set global relay_log_purge = 0;
#设置只读
mysql> set global read_only=1;
#编辑配置文件db02,db03从库
[root@mysql-db02 ~]# vim /etc/my.cnf
#在mysqld标签下添加
[mysqld]
#禁用自动删除relay log 永久生效
relay_log_purge = 0

3.部署MHA

3.1环境准备（所有节点）

#安装依赖包
[root@mysql-db01 ~]# yum install perl-DBD-MySQL -y
#进入安装包存放目录
[root@mysql-db01 ~]# cd /server/tools/
#上传mha安装包
[root@mysql-db01 tools]# rz -be
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-manager-0.56.tar.gz
mha4mysql-node-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56.tar.gz

[root@mysql-db01 tools]# ll
total 5136
-rw-r--r-- 1 root root 4963681 Jun 29 2017 Atlas-2.2.1.el6.x86_64.rpm
-rw-r--r-- 1 root root 87119 Jun 29 2017 mha4mysql-manager-0.56-0.el6.noarch.rpm
-rw-r--r-- 1 root root 113914 Jun 29 2017 mha4mysql-manager-0.56.tar.gz
-rw-r--r-- 1 root root 36326 Jun 29 2017 mha4mysql-node-0.56-0.el6.noarch.rpm
-rw-r--r-- 1 root root 50172 Jun 29 2017 mha4mysql-node-0.56.tar.gz

#安装node包
[root@mysql-db01 tools]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm  ##所有节点
Preparing...                ########################################### [100%]
   1:mha4mysql-node         ########################################### [100%]
#登录数据库
[root@mysql-db01 tools]# mysql -uroot -p123456
#添加mha管理账号
mysql> grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';
#查看是否添加成功
mysql> select user,host from mysql.user;
#主库上创建，从库会自动复制（在从库上查看）
mysql> select user,host from mysql.user;

3.2命令软连接（所有节点）

#如果不创建命令软连接，检测mha复制情况的时候会报错
[root@mysql-db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@mysql-db01 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql

3.3部署管理节点（mha-manager）

3.3.1在mysql-db03上部署管理节点为了防止主库宕机

#使用epel源
[root@mysql-db03 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
#安装manager依赖包
[root@mysql-db03 ~]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

##所有节点执行
##yum install mha4mysql* -y

#安装manager包
[root@mysql-db03 tools]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 
Preparing... ########################################### [100%]
1:mha4mysql-manager ########################################### [100%]

3.3.2编辑配置文件

#创建配置文件目录
[root@mysql-db03 ~]# mkdir -p /etc/mha
#创建日志目录
[root@mysql-db03 ~]# mkdir -p /var/log/mha/app1
#编辑mha配置文件
[root@mysql-db03 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager 
manager_workdir=/var/log/mha/app1 
master_binlog_dir=/application/mysql/data  ##若所有节点服务路径一样可以写主配置模块下，如果不一样需写到单个节点模块下
user=mha 
password=mha 
ping_interval=2
repl_password=123456
repl_user=rep
ssh_user=root 

[server1] 
hostname=118.190.201.68
port=3306 

[server2]
candidate_master=1    ##不管slave是否最新，优先提升为主
check_repl_delay=0    ##忽略延迟，强制提升为主
hostname=118.190.201.69
port=3306

[server3]
hostname=118.190.201.70
port=3306

【配置文件详解】

[server default]
#设置manager的工作目录
manager_workdir=/var/log/masterha/app1
#设置manager的日志
manager_log=/var/log/masterha/app1/manager.log 
#设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
master_binlog_dir=/data/mysql
#设置自动failover时候的切换脚本
master_ip_failover_script= /usr/local/bin/master_ip_failover
#设置手动切换时候的切换脚本
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
#设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
password=123456
#设置监控用户root
user=root
#设置监控主库，发送ping包的时间间隔，尝试三次没有回应的时候自动进行failover
ping_interval=1
#设置远端mysql在发生切换时binlog的保存位置
remote_workdir=/tmp
#设置复制用户的密码
repl_password=123456
#设置复制环境中的复制用户名 
repl_user=rep
#设置发生切换后发送的报警的脚本
report_script=/usr/local/send_report
#一旦MHA到server02的监控之间出现问题，MHA Manager将会尝试从server03登录到server02
secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=192.168.0.50 --master_port=3306
#设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）
shutdown_script=""
#设置ssh的登录用户名
ssh_user=root 

[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306
#设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
candidate_master=1
#默认情况下如果一个save落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢
复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了
candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的masterl
check_repl_delay=0

3.4配置ssh信任（所有节点）

#创建秘钥对
[root@mysql-db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
#发送公钥，包括自己
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@118.190.201.68
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@118.190.201.69
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@118.190.201.70
检测结果：
ssh root@118.190.201.70 hostname
ssh root@118.190.201.69 hostname
ssh root@118.190.201.68 hostname

3.5启动测试

#测试ssh
[root@mysql-db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
#看到如下字样，则测试成功
Tue Mar 7 01:03:33 2017 - [info] All SSH connection tests passed successfully.
#从库创建同步账号
mysql> grant replication slave on *.* to rep@'118.190.201.%' identified by '123456';
#测试复制
[root@mysql-db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
#看到如下字样，则测试成功
MySQL Replication Health is OK.

3.6启动MHA

#启动
[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

3.7切换master测试

#登录数据库（db02）
[root@mysql-db02 ~]# mysql -uroot –p123456
#检查复制情况
mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 118.190.201.69
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000006
 Read_Master_Log_Pos: 191
 Relay_Log_File: mysql-db02-relay-bin.000002
 Relay_Log_Pos: 361
 Relay_Master_Log_File: mysql-bin.000006
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

#登录数据库（db03）
[root@mysql-db03 ~]# mysql -uroot –p123456
#检查复制情况
mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 118.190.201.70
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000006
 Read_Master_Log_Pos: 191
 Relay_Log_File: mysql-db03-relay-bin.000002
 Relay_Log_Pos: 361
 Relay_Master_Log_File: mysql-bin.000006
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

#停掉主库
[root@mysql-db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL..... SUCCESS!
#登录数据库（db02）
[root@mysql-db02 ~]# mysql -uroot -p123456
#查看slave状态
mysql> show slave status\G
#db02的slave已经为空
Empty set (0.00 sec)
#登录数据库（db03）
[root@mysql-db03 ~]# mysql -uroot -p123456
#查看slave状态
mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 118.190.201.69
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000006
 Read_Master_Log_Pos: 191
 Relay_Log_File: mysql-db03-relay-bin.000002
 Relay_Log_Pos: 361
 Relay_Master_Log_File: mysql-bin.000006
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

贴一份切换主库日志文件内容

Thu Jun 28 11:20:50 2018 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Thu Jun 28 11:20:50 2018 - [info] Executing SSH check script: exit 0
Thu Jun 28 11:20:50 2018 - [info] HealthCheck: SSH to 118.190.201.68 is reachable.
Thu Jun 28 11:20:52 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '118.190.201.68' (111))
Thu Jun 28 11:20:52 2018 - [warning] Connection failed 2 time(s)..
Thu Jun 28 11:20:54 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '118.190.201.68' (111))
Thu Jun 28 11:20:54 2018 - [warning] Connection failed 3 time(s)..
Thu Jun 28 11:20:56 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '118.190.201.68' (111))
Thu Jun 28 11:20:56 2018 - [warning] Connection failed 4 time(s)..
Thu Jun 28 11:20:56 2018 - [warning] Master is not reachable from health checker!
Thu Jun 28 11:20:56 2018 - [warning] Master 118.190.201.68(118.190.201.68:3306) is not reachable!
Thu Jun 28 11:20:56 2018 - [warning] SSH is reachable.
Thu Jun 28 11:20:56 2018 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.cnf again, and trying to connect to all servers to check server status..
Thu Jun 28 11:20:56 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 28 11:20:56 2018 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 28 11:20:56 2018 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Jun 28 11:20:57 2018 - [info] GTID failover mode = 1
Thu Jun 28 11:20:57 2018 - [info] Dead Servers:
Thu Jun 28 11:20:57 2018 - [info] 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:57 2018 - [info] Alive Servers:
Thu Jun 28 11:20:57 2018 - [info] 118.190.201.69(118.190.201.69:3306)
Thu Jun 28 11:20:57 2018 - [info] 118.190.201.70(118.190.201.70:3306)
Thu Jun 28 11:20:57 2018 - [info] Alive Slaves:
Thu Jun 28 11:20:57 2018 - [info] 118.190.201.69(118.190.201.69:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:57 2018 - [info] GTID ON
Thu Jun 28 11:20:57 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:57 2018 - [info] 118.190.201.70(118.190.201.70:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:57 2018 - [info] GTID ON
Thu Jun 28 11:20:57 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:57 2018 - [info] Checking slave configurations..
Thu Jun 28 11:20:57 2018 - [info] Checking replication filtering settings..
Thu Jun 28 11:20:57 2018 - [info] Replication filtering check ok.
Thu Jun 28 11:20:57 2018 - [info] Master is down!
Thu Jun 28 11:20:57 2018 - [info] Terminating monitoring script.
Thu Jun 28 11:20:57 2018 - [info] Got exit code 20 (Master dead).
Thu Jun 28 11:20:57 2018 - [info] MHA::MasterFailover version 0.56.
Thu Jun 28 11:20:57 2018 - [info] Starting master failover.
Thu Jun 28 11:20:57 2018 - [info] 
Thu Jun 28 11:20:57 2018 - [info] * Phase 1: Configuration Check Phase..
Thu Jun 28 11:20:57 2018 - [info] 
Thu Jun 28 11:20:58 2018 - [info] GTID failover mode = 1
Thu Jun 28 11:20:58 2018 - [info] Dead Servers:
Thu Jun 28 11:20:58 2018 - [info] 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:58 2018 - [info] Checking master reachability via MySQL(double check)...
Thu Jun 28 11:20:58 2018 - [info] ok.
Thu Jun 28 11:20:58 2018 - [info] Alive Servers:
Thu Jun 28 11:20:58 2018 - [info] 118.190.201.69(118.190.201.69:3306)
Thu Jun 28 11:20:58 2018 - [info] 118.190.201.70(118.190.201.70:3306)
Thu Jun 28 11:20:58 2018 - [info] Alive Slaves:
Thu Jun 28 11:20:58 2018 - [info] 118.190.201.69(118.190.201.69:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:58 2018 - [info] GTID ON
Thu Jun 28 11:20:58 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:58 2018 - [info] 118.190.201.70(118.190.201.70:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:58 2018 - [info] GTID ON
Thu Jun 28 11:20:58 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:58 2018 - [info] Starting GTID based failover.
Thu Jun 28 11:20:58 2018 - [info] 
Thu Jun 28 11:20:58 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Jun 28 11:20:58 2018 - [info] 
Thu Jun 28 11:20:58 2018 - [info] * Phase 2: Dead Master Shutdown Phase..
Thu Jun 28 11:20:58 2018 - [info] 
Thu Jun 28 11:20:58 2018 - [info] Forcing shutdown so that applications never connect to the current master..
Thu Jun 28 11:20:58 2018 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Thu Jun 28 11:20:58 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu Jun 28 11:20:59 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] * Phase 3: Master Recovery Phase..
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] The latest binary log file/position on all slaves is mysql-bin.000007:797
Thu Jun 28 11:20:59 2018 - [info] Retrieved Gtid Set: 35b9205a-7a6e-11e8-bbdf-000c297fb4aa:1-3
Thu Jun 28 11:20:59 2018 - [info] Latest slaves (Slaves that received relay log files to the latest):
Thu Jun 28 11:20:59 2018 - [info] 118.190.201.69(118.190.201.69:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:59 2018 - [info] GTID ON
Thu Jun 28 11:20:59 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:59 2018 - [info] 118.190.201.70(118.190.201.70:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:59 2018 - [info] GTID ON
Thu Jun 28 11:20:59 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:59 2018 - [info] The oldest binary log file/position on all slaves is mysql-bin.000007:797
Thu Jun 28 11:20:59 2018 - [info] Retrieved Gtid Set: 35b9205a-7a6e-11e8-bbdf-000c297fb4aa:1-3
Thu Jun 28 11:20:59 2018 - [info] Oldest slaves:
Thu Jun 28 11:20:59 2018 - [info] 118.190.201.69(118.190.201.69:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:59 2018 - [info] GTID ON
Thu Jun 28 11:20:59 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:59 2018 - [info] 118.190.201.70(118.190.201.70:3306) Version=5.6.35-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:20:59 2018 - [info] GTID ON
Thu Jun 28 11:20:59 2018 - [info] Replicating from 118.190.201.68(118.190.201.68:3306)
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] * Phase 3.3: Determining New Master Phase..
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] Searching new master from slaves..
Thu Jun 28 11:20:59 2018 - [info] Candidate masters from the configuration file:
Thu Jun 28 11:20:59 2018 - [info] Non-candidate masters:
Thu Jun 28 11:20:59 2018 - [info] New master is 118.190.201.69(118.190.201.69:3306)
Thu Jun 28 11:20:59 2018 - [info] Starting master failover..
Thu Jun 28 11:20:59 2018 - [info] 
From:
118.190.201.68(118.190.201.68:3306) (current master)
 +--118.190.201.69(118.190.201.69:3306)
 +--118.190.201.70(118.190.201.70:3306)

To:
118.190.201.69(118.190.201.69:3306) (new master)
 +--118.190.201.70(118.190.201.70:3306)
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] * Phase 3.3: New Master Recovery Phase..
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] Waiting all logs to be applied.. 
Thu Jun 28 11:20:59 2018 - [info] done.
Thu Jun 28 11:20:59 2018 - [info] Getting new master's binlog name and position..
Thu Jun 28 11:20:59 2018 - [info] mysql-bin.000010:1054
Thu Jun 28 11:20:59 2018 - [info] All other slaves should start replication from here. Statement should be:\
 CHANGE MASTER TO MASTER_HOST='118.190.201.69', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Thu Jun 28 11:20:59 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000010, 1054, 35b9205a-7a6e-11e8-bbdf-000c297fb4aa:1-3,
b37d6833-7a6d-11e8-bbdb-000c29eca88c:1
Thu Jun 28 11:20:59 2018 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Thu Jun 28 11:20:59 2018 - [info] Setting read_only=0 on 118.190.201.69(118.190.201.69:3306)..
Thu Jun 28 11:20:59 2018 - [info] ok.
Thu Jun 28 11:20:59 2018 - [info] ** Finished master recovery successfully.
Thu Jun 28 11:20:59 2018 - [info] * Phase 3: Master Recovery Phase completed.
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] * Phase 4: Slaves Recovery Phase..
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] * Phase 4.1: Starting Slaves in parallel..
Thu Jun 28 11:20:59 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] -- Slave recovery on host 118.190.201.70(118.190.201.70:3306) started, pid: 4777. Check tmp log /var/log/mha/app1/118.190.201.70_3306_20180628112057.log if it takes time..
Thu Jun 28 11:21:01 2018 - [info] 
Thu Jun 28 11:21:01 2018 - [info] Log messages from 118.190.201.70 ...
Thu Jun 28 11:21:01 2018 - [info] 
Thu Jun 28 11:20:59 2018 - [info] Resetting slave 118.190.201.70(118.190.201.70:3306) and starting replication from the new master 118.190.201.69(118.190.201.69:3306)..
Thu Jun 28 11:21:00 2018 - [info] Executed CHANGE MASTER.
Thu Jun 28 11:21:01 2018 - [info] Slave started.
Thu Jun 28 11:21:01 2018 - [info] gtid_wait(35b9205a-7a6e-11e8-bbdf-000c297fb4aa:1-3,
b37d6833-7a6d-11e8-bbdb-000c29eca88c:1) completed on 118.190.201.70(118.190.201.70:3306). Executed 0 events.
Thu Jun 28 11:21:01 2018 - [info] End of log messages from 118.190.201.70.
Thu Jun 28 11:21:01 2018 - [info] -- Slave on host 118.190.201.70(118.190.201.70:3306) started.
Thu Jun 28 11:21:01 2018 - [info] All new slave servers recovered successfully.
Thu Jun 28 11:21:01 2018 - [info] 
Thu Jun 28 11:21:01 2018 - [info] * Phase 5: New master cleanup phase..
Thu Jun 28 11:21:01 2018 - [info] 
Thu Jun 28 11:21:01 2018 - [info] Resetting slave info on the new master..
Thu Jun 28 11:21:02 2018 - [info] 118.190.201.69: Resetting slave info succeeded.
Thu Jun 28 11:21:02 2018 - [info] Master failover to 118.190.201.69(118.190.201.69:3306) completed successfully.
Thu Jun 28 11:21:02 2018 - [info] Deleted server1 entry from /etc/mha/app1.cnf .
Thu Jun 28 11:21:02 2018 - [info] 

----- Failover Report -----

app1: MySQL Master failover 118.190.201.68(118.190.201.68:3306) to 118.190.201.69(118.190.201.69:3306) succeeded

Master 118.190.201.68(118.190.201.68:3306) is down!

Check MHA Manager logs at mysql-db03:/var/log/mha/app1/manager for details.

Started automated(non-interactive) failover.
Selected 118.190.201.69(118.190.201.69:3306) as a new master.
118.190.201.69(118.190.201.69:3306): OK: Applying all logs succeeded.
118.190.201.70(118.190.201.70:3306): OK: Slave started, replicating from 118.190.201.69(118.190.201.69:3306)
118.190.201.69(118.190.201.69:3306): Resetting slave info succeeded.
Master failover to 118.190.201.69(118.190.201.69:3306) completed successfully.

3.8手动进行测试

#所有节点停从库，执行如下操作
mysql> show slave status\G
mysql> stop slave;
mysql> reset slave all;
mysql> show slave status\G

#db02,db03执行如下语句
mysql> CHANGE MASTER TO MASTER_HOST='118.190.201.68', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123456'; 
mysql> start slave; 
mysql> show slave status\G 
##db03在
[server03]
标签下添加如下参数 
[server3] 
candidate_master=1 
check_repl_delay=0 
#启动MHA 
[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & 
#停止主库服务 
[root@mysql-db01 ~]# /etc/init.d/mysqld stop 
#从库查看 mysql> show slave status\G 
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event
Master_Host: 118.190.201.70
Master_User: rep 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mysql-bin.000010 
Read_Master_Log_Pos: 1311 
Relay_Log_File: relay-bin.000002 
Relay_Log_Pos: 712 
Relay_Master_Log_File: mysql-bin.000010 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

测试寻找位置点最新的binlog

#修复主库及启动MHA
[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & 
#在db03创建如下表 
use test; 
create table student( Sno int(10) NOT NULL COMMENT '学号', Sname varchar(16) NOT NULL COMMENT '姓名', Ssex char(2) NOT NULL COMMENT '性别', Sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄', Sdept varchar(16) default NULL COMMENT '学生所在系别', PRIMARY KEY (Sno) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
#批量插入数据脚本 
[root@mysql-db03 ~]# vim mysql_insert.sh 
#!/bin/bash 
cmdmysql="mysql -uroot -p123456" 
i=1 
while true do 
${cmdmysql} -e "insert into test.student values ("$i",'john"$i"','m','21','computer"$i"')"; 
((i++)) 
sleep 2 
done 
#执行脚本不用停 
[root@mysql-db03 ~]# sh mysql_insert.sh 

#一段时候后停止db01从库的slave 
mysql> stop slave; 
Query OK, 0 rows affected (0.02 sec) 

#然后等一会停止主库的服务 
[root@mysql-db03 ~]# /etc/init.d/mysqld stop 
#查看slave状态 
mysql> show slave status\G 
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event 
Master_Host: 118.190.201.68 
Master_User: rep 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mysql-bin.000010 
Read_Master_Log_Pos: 1311 
Relay_Log_File: relay-bin.000002 
Relay_Log_Pos: 712 
Relay_Master_Log_File: mysql-bin.000010 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

三.配置binlog-server

3.1修改mha配置文件

[root@mysql-db03 ~]# vim /etc/mha/app1.cnf
[binlog1]
no_master=1     ##不提升为主
hostname=118.190.201.70
master_binlog_dir=/data/mysql/binlog/   ##把主库的bin-log备份一份

3.2备份binlog

#创建备份binlog目录
[root@mysql-db03 ~]# mkdir -p /data/mysql/binlog/
#进入该目录
[root@mysql-db03 ~]# cd /data/mysql/binlog/
#备份binlog
[root@mysql-db03 binlog]# mysqlbinlog -R --host=118.190.201.69 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
注意：如果报错如下
ERROR: Got error reading packet from server: Cannot replicate anonymous transaction when @@GLOBAL.GTID_MODE = ON, at file ./mysql-bin.000001, position 154.; the first event 'mysql-bin.000001' at 4, the last event read from './mysql-bin.000001' at 219, the last byte read from './mysql-bin.000001' at 219.
主库执行如下命令：
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
#启动mha
[root@mysql-db03 binlog]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

3.3测试binlog备份

#查看binlog目录中的binlog
[root@mysql-db03 binlog]# ll
total 44
-rw-r--r-- 1 root root 285 Mar 8 03:11 mysql-bin.000001
#登录主库
[root@mysql-db01 ~]# mysql -uroot -p123456
#刷新binlog
mysql> flush logs;
#再次查看binlog目录
[root@mysql-db03 binlog]# ll
total 48
-rw-r--r-- 1 root root 285 Mar 8 03:11 mysql-bin.000001
-rw-r--r-- 1 root root 143 Mar 8 04:00 mysql-bin.000002

四.配置VIP漂移

4.1IP漂移的两种方式

通过keepalived的方式，管理虚拟IP的漂移
通过MHA自带脚本方式，管理虚拟IP的漂移

4.2MHA脚本方式

4.2.1修改配置文件

#编辑配置文件
[root@mysql-db03 ~]# vim /etc/mha/app1.cnf
#在[server default]标签下添加
[server default]
#使用MHA自带脚本
master_ip_failover_script=/usr/local/bin/master_ip_failover

#停止MHA
masterha_stop --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1

#检查是否停止
ps -ef |grep mha

#上传master_ip_failover脚本文件
cd /usr/local/bin
链接：https://pan.baidu.com/s/1jqtNhQCg-_sO1j3G2VZJMA 密码：21we

4.2.2编辑脚本

#根据配置文件中脚本路径编辑
[root@mysql-db03 bin]# vim /usr/local/bin/master_ip_failover
#修改以下几行内容
my $vip = '118.190.201.25/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; 
#添加执行权限，否则mha无法启动
[root@mysql-db03 ~]# chmod +x /usr/local/bin/master_ip_failover

#重新启动mha
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

4.2.3手动绑定VIP

#绑定vip
[root@mysql-db01 ~]# ifconfig eth0:0 118.190.201.25/24 up
或者
ip addr add 118.190.201.25/24 dev eth0 label eth0:0
#查看vip
[root@mysql-db01 ~]# ip a |grep eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
 inet 10.0.0.51/24 brd 10.0.0.255 scope global eth0
inet 118.190.201.25/24 brd 118.190.201.255 scope global secondary eth0:0

4.3测试ip漂移

#登录db02
[root@mysql-db02 ~]# mysql -uroot -p123456
#查看slave信息
mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 118.190.201.69
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000007
 Read_Master_Log_Pos: 191
 Relay_Log_File: mysql-db02-relay-bin.000002
 Relay_Log_Pos: 361
 Relay_Master_Log_File: mysql-bin.000007
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
#停掉主库
[root@mysql-db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL..... SUCCESS!
#在db03上查看从库slave信息

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 118.190.201.68
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000006
 Read_Master_Log_Pos: 191
 Relay_Log_File: mysql-db03-relay-bin.000002
 Relay_Log_Pos: 361
 Relay_Master_Log_File: mysql-bin.000006
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
#在db01上查看vip信息
[root@mysql-db01 mysql]# ip a|grep eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
 inet 118.190.201.68/24 brd 118.190.201.255 scope global eth0
 inet 118.190.201.25/24 brd 118.190.201.255 scope global secondary eth0:0
#在db02上查看vip信息
[root@mysql-db02 mysql]# ip a|grep eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
 inet 118.190.201.69/24 brd 118.190.201.255 scope global eth0

五.mysql中间件Atlas

5.1Atlas主要功能

读写分离
从库负载均衡
IP过滤
自动分表
DBA可平滑上下线DB
自动摘除宕机的DB

5.2Atlas相对于官方MySQL-Proxy的优势

将主流程中所有Lua代码用C重写，Lua仅用于管理接口
重写网络模型、线程模型
实现了真正意义上的连接池
优化了锁机制、性能提高数十倍

5.3安装Atlas

官方提供的Atlas有两种：

Atlas (普通) : Atlas-2.2.1.el6.x86_64.rpm

Atlas (分表) : Atlas-sharding_1.0.1-el6.x86_64.rpm

这里我们只需要下载普通的即可。

#在主库安装，进入安装包目录
[root@mysql-db01 ~]# cd /server/tools/
#下载Atlas
[root@mysql-db01 tools]# 
wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
#安装
[root@mysql-db01 tools]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 
Preparing...                 ########################################### [100%]
 1:Atlas                     ########################################### [100%]

6.2编辑配置文件

#进入Atlas工具目录
[root@mysql-db01 ~]# cd /usr/local/mysql-proxy/bin/
#生成密码
[root@mysql-db01 bin]# ./encrypt 123456
#修改Atlas配置文件
[root@mysql-db01 ~]# vim /usr/local/mysql-proxy/conf/test.cnf
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 118.190.201.25:3307##代理端口my.cnf配置文件也需要更改为3307端口，重启服务
#Atlas后端连接的MySQL从库的IP和端口
proxy-read-only-backend-addresses = 118.190.201.69:3307,118.190.201.70:3307##代理端口my.cnf配置文件也需要更改为3307端口
#用户名与其对应的加密过的MySQL密码
pwds = atlas:/iZxz+0GRoA=,root:/iZxz+0GRoA=
#SQL日志的开关
sql-log = ON
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:3306
#默认字符集，设置该项后客户端不再需要执行SET NAMES语句
charset = utf8

#重启服务db01.db02.db03MySQL服务
/etc/init.d/mysqld restart
#db02.db03执行如下语句
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='118.190.201.68', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123456';
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5.3启动Atlas

[root@mysql-db01 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started

#查看端口
[root@mysql-db01 bin]# netstat -lntup|grep mysql-proxy
tcp        0       0 0.0.0.0:2345        0.0.0.0:*        LISTEN         7392/mysql-proxy 
tcp        0       0 0.0.0.0:3306        0.0.0.0:*        LISTEN         7392/mysql-proxy

#说明：
#监听3306的为Atlas对外提供代理的端口；监听2345是Atlas的管理监听端口

5.4Atlas管理操作

#用atlas管理用户登录
[root@mysql-db01 ~]# mysql -uuser -ppwd -h127.0.0.1 -P2345
#查看可用命令帮助
mysql> select * from help;
#查看后端代理的库
mysql> SELECT * FROM backends;
+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 118.190.201.68:3307 | up    | rw   |
|           2 | 118.190.201.68:3307 | up    | ro   |
|           3 | 118.190.201.68:3307 | up    | ro   |
+-------------+---------------------+-------+------+
#平滑摘除mysql
mysql> REMOVE BACKEND 2;
Empty set (0.00 sec)

#检查是否摘除成功
mysql> SELECT * FROM backends;
+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 118.190.201.68:3307 | up    | rw   |
|           2 | 118.190.201.69:3307 | up    | ro   |
+-------------+---------------------+-------+------+
#保存到配置文件中
mysql> SAVE CONFIG;
Empty set (0.06 sec)

5.5 连接数据库查看负载

通过atlas登陆数据，注意，使用的是数据库上的用户及密码

[root@mysql-db01 ~]# mysql -uatlas -p123456 -h127.0.0.1 -P3306
#第一次查询
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 5     |
+---------------+-------+
1 row in set (0.02 sec)
#第二次查询
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
1 row in set (0.00 sec)

5.6小结

安装Atlas之后需要修改的地方有：
配置文件my.cnf的端口号
从库change master to的端口号
配置文件mha的app1的端口号

贴一份my.cnf的配置内容

[root@mysql-db03 tools]# cat /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
basedir = /application/mysql
datadir = /application/mysql/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error = /application/mysql/error.log
#log-slow-queries = /application/mysql/slow.log
#pid-file = /application/mysql/mysql.pid
log-bin = /application/mysql/mysql-bin
relay-log = /application/mysql/relay-bin
relay-log-info-file = /application/mysql/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 10
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 320M
#innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
gtid_mode=ON
log_slave_updates
enforce_gtid_consistency
relay_log_purge = 0

[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
#log-error=/application/mysql/mysql_3306.err
#pid-file=/application/mysql/mysqld.pid

如无特殊说明，文章均为本站原创，转载请注明出处

转载请注明来源：MySQL之高可用及读写分离
本文永久链接地址：https://www.xionghaier.cn/archives/473.html

该文章由 John 发布

这货来去如风，什么鬼都没留下！！！

MySQL之高可用及读写分离