1.简介
1.1 软件介绍
- MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中,MHA能做到0~30秒之内字段完成数据库的故障切换,并且在进行故障切换过程中MHA能最大程度保证数据库的一致性,以达到真正意义上的高可用。
- MHA由两部分组成:MHA Manager(管理节点)和MHA Node(数据节点)。
MHA Manager可以独立部署在一*立的机器上管理多个Master-Slave集群,也可以部署在一台Slave上。当Master出现故障时,它可以自动将最新数据的Slave提升为新的Master,然后将所有其他的Slave重新指向新的Master。整个故障转移过程对应程序是完全透明的。
1.2 工作流程
-
从宕机崩溃的master保存二进制日志事件(binlog events);
-
识别含有最新更新的slave;
-
应用差异的中继日志(relay log)到其他的slave;
-
应用从master保存的二进制日志事件(binlog events);
-
提升一个slave为新的master;
-
使其他的slave连接新的master进行复制;
1.复制主库binlog日志出来
2.找出relaylog日志最全的从库
3.将最全的relaylog日志在所有从库中同步(第一次数据同步)
4.将之前最全的那个从库提升为主库
5.将复制出来的binlog日志,放到新提升的主库里
6.其他所有从库重新指向新提升的主库,继续主从复制
1.3 MHA工具介绍
MHA软件由两部分组成,Manager工具包和Node工具包,如下:
#Manager工具包主要包括以下几个工具:
masterha_check_ssh #检查MHA的SSH配置状况
masterha_check_repl #检查MySQL复制状况
masterha_check_status #检测当前MHA运行状态
masterha_master_monitor #检测master是否宕机
masterha_manger #启动MHA
masterha_master_switch #控制故障转移(自动或者手动)
masterha_conf_host #添加或删除配置的server信息
masterha_secondary_check #试图建立TCP连接从远程服务器
masterha_stop #停止MHA
#Node工具包主要包括以下几个工具:
save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件
filter_mysqlbinlog #去除不必要的ROLLBACK事件
purge_relay_logs #清除中继日志
1.4 MHA架构图
2.MySQL-MHA准备工作
2.1 实验环境:
主机名 | IP地址(NAT) | 描述 |
---|---|---|
mysql-master | eth0:192.168.200.131 | 系统:CentOS6.5(6.x都可以) 安装:mysql5.6 |
mysql-slaveA | eth0:192.168.200.145 | 系统:CentOS6.5(6.x都可以) 安装:mysql5.6 |
mysql-slaveB | eth0:192.168.200.146 | 系统:CentOS6.5(6.x都可以) 安装:mysql5.6 |
2.2 准备软件包
(1)mha管理节点安装包:
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-manager-0.56.tar.gz
(2)mha node节点安装包:
mha4mysql-node-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56.tar.gz
(3) mysql中间件:
Atlas-2.2.1.el6.x86_64.rpm
(4) mysql源码安装包
mysql-5.6.17-linux-glibc2.5-x86_64.tar
2.3 主机名映射
[root@localhost etc]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.131 MySQL-Master
192.168.200.145 MySQL-SlaveA
192.168.200.146 MySQL-SlaveB
2.4 关闭selinux和iptables
[root@localhost ~]# vim /etc/sysconfig/selinux
[root@localhost ~]# cat /etc/sysconfig/selinux | grep -v "#"
SELINUX=disabled
SELINUXTYPE=targeted
[root@localhost ~]# setenforce 0
[root@localhost ~]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
[root@localhost ~]# chkconfig iptables off
3.Mysql环境搭建
3.1 环境检查
mysql-master
#系统版本
[root@localhost bin]# cat /etc/redhat-release
CentOS release 6.5 (Final)
[root@localhost bin]# uname -r
2.6.32-431.el6.x86_64
[root@localhost bin]# hostname -I
192.168.0.51
mysql-slaveA
#系统版本
[root@localhost ~]# cat /etc/redhat-release
CentOS release 6.5 (Final)
[root@localhost ~]# uname -r
2.6.32-431.el6.x86_64
[root@localhost ~]# hostname -I
192.168.0.52
mysql-slaveB
#系统版本
[root@localhost ~]# cat /etc/redhat-release
CentOS release 6.5 (Final)
[root@localhost ~]# uname -r
2.6.32-431.el6.x86_64
[root@localhost ~]# hostname -I
192.168.0.53
3.2 安装mysql
3.2.1 安装3台
[root@localhost ~]# yum -y install ncurses-devel libaio
[root@localhost ~]# tar xf mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ln -s /usr/local/mysql-5.6.17-linux-glibc2.5-x86_64 /usr/local/mysql
[root@localhost ~]# useradd mysql -s /sbin/nologin -M
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
[root@localhost ~]# /bin/cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
[root@localhost ~]# /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
[root@localhost ~]# which mysqladmin
/usr/local/bin/mysqladmin
3.2.2 加入开启自启动mysql
[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]# chkconfig mysqld --list
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
3.2.3 配置密码
[root@chenguanzhou ~]# mysqladmin -uroot password '111111'
4.配置基于FTID的主从复制
4.1 先决条件
- 主库和从库都要开启binlog
- 主库和从库server-id不同
- 要有主从复制用户
4.2 主库操作(mysql-master)
4.2.1 修改配置文件
#修改主库配置文件/etc/my.cnf
[root@localhost mysql]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin #开启binlog日志
server_id = 1 #设置server_id
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
#重启动MySQL服务
[root@localhost mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
4.2.2 登陆MySQL删除不必要的用户并创建主从复制用户
(1)删除不必要的用户
mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
6 rows in set (0.00 sec)
mysql> drop user root@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user root@'::1';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user ' '@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user ' '@'mysql-db01';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | localhost |
| root | localhost.localdomain |
+------+-----------------------+
2 rows in set (0.00 sec)
(2)创建主从复制用户
mysql> grant replication slave on *.* to rep@'192.168.200.%' identified by '111111';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+------------------------+
| user | host |
+------+------------------------+
| rep | 192.168.0.% |
| root | localhost |
| root | localhost.localdomain |
+------+------------------------+
3 rows in set (0.00 sec)
mysql> show grants for rep@'192.168.0.%';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.200.% |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.200.%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.3 从库操作(mysql-slaveA和mysql-B)
4.3.1 修改配置文件
#slaveA和slaveB 两个配置文件只有一处不一样,都是这个配置文件修改
[root@localhost ~]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin #从binlog也要打开
server_id = 5 #仅需修改此项 A是5 B是10
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
[root@localhost ~]# /etc/init.d/mysqld restart #重启mysql
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
特别说明:
在以往如果是基于binlog日志的主从复制,则必须要记住主库的master状态信息。
但是在MySQL5.6版本里多了一个Gtid的功能,可以自动记录主从复制位置点的信息,并在日志中输出出来。
4.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 | |
+--------------------------+-------+
5 rows in set (0.00 sec)
编辑mysql配置文件(主库从库都需要修改)
[root@localhost ~]# vim /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
gtid_mode =ON #这行修改
log_slave_updates #这行修改
enforce_gtid_consistency #这行修改
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin
server_id = 1
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
修改完配置文件以后重启动数据库(主从库都重启数据库)
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
再次查看GTID状态
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot -p111111
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON | #执行GTID一致
| gtid_executed | |
| gtid_mode | ON | #开启GTID模块
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------+
5 rows in set (0.00 sec)
mysql>
4.5 配置主从复制(mysql-slaveA,mysql-slaveB)
#告诉从库主库是谁
mysql> change master to master_host='192.168.200.131',master_user='rep',master_password='111111',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql>
4.6 开启从库的主从复制功能(mysql-slaveA,mysql-slaveB)
mysql> start slave; #开启主从同步功能
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.131
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 151
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #此项yes代表成功
Slave_SQL_Running: Yes #此项yes代表成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
#省略
4.7 什么是GTID
-
GTID(Global Transaction)全局事务标识符:是一个唯一的标识符,它创建并与源服务器(主)上提交的每个事务相关联。此标识符不仅对其发起的服务器是唯一的,而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有1对1的映射。
-
GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。
-
GTID的具体形式:
3E11FA47-71CA-11E1-9E33-C80AA9429562:23
4.8 GTID的新特性
(1)支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sql thread)
(2)支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to 指向。在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,MySQL通过内部机制GTID自动找点同步。
(3)基于Row复制只保存改变的列,大大节省磁盘空间,网络,内存等
(4)支持把Master和Slave的相关信息记录在Table中;原来是记录在文件里,现在则记录在表里,增强可用性
(5)支持延迟复制
4.9 开启方法
#mysql配置文件:
[mysqld]
gtid_mode=ON
enforce_gtid_consistency
#查看
show global variables like '%gtid%';
4.10 从库设置(mysql-slaveA,mysql-slaveB)
#登陆从库
[root@localhost ~]# mysql -uroot -p111111
#临时禁用自动删除relay log功能
mysql> set global relay_log_purge = 0;
Query OK, 0 rows affected (0.00 sec)
#设置只读
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
编辑配置文件/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
relay_log_purge = 0 #永久禁用自动删除relay log功能
gtid_mode = ON
log_slave_updates
enforce_gtid_consistency
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
#省略
5.部署MHA
5.1 环境准备(所有节点mysql-master,mysql-slaveA,mysql-alaveB)
#光盘安装依赖包
[root@localhost ~]# yum -y install perl-DBD-MySQL
#安装mha4mysql-node-0.56-0.el6.noarch.rpm
[root@localhost rpm]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... ########################################### [100%]
1:mha4mysql-node ########################################### [100%]
[root@localhost rpm]# mysql -uroot -p111111
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all privileges on *.* to mha@'192.168.200.%' identified by '111111';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user where user='mha';
+------+---------------+
| user | host |
+------+---------------+
| mha | 192.168.200.% | #主库上创建从库会自动复制
+------+---------------+
1 row in set (0.00 sec)
mysql>
5.2 部署管理节点(mha-manager)
5.2.1 在mysql-slaveB上部署管理节点
#使用阿里云源+epel源
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
wget -O /etc/yum.repos.d/epel-6.repo http://mirrors.aliyun.com/repo/epel-6.repo
#安装manager依赖包(需要公网源)
[root@localhost rpm]# yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
#安装manager包
[root@localhost rpm]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
Preparing... ########################################### [100%]
1:mha4mysql-manager ########################################### [100%]
5.2.2 编辑配置文件
#创建配置文件目录
[root@mysql-db03 ~]# mkdir -p /etc/mha
#创建日志目录
[root@mysql-db03 ~]# mkdir -p /var/log/mha/mha1
#创建配置文件(默认没有)
[root@localhost ~]# cd /etc/mha/
[root@localhost mha]# ls
[root@localhost mha]# vim /etc/mha/mha1.cnf
[root@localhost mha]# cat /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager #manager管理日志存放路径
manager_workdir=/var/log/mha/mha1 #manager管理日志的目录路径
master_binlog_dir=/usr/local/mysql/data #binlog日志的存放路径
user=mha #管理账户
password=111111 #管理账户密码
ping_interval=2 #存活检查的间隔时间
repl_user=rep #主从复制的授权账户
repl_password=111111 #主从复制的授权账户密码
ssh_user=root #用于ssh连接的账户
[server1]
hostname=192.168.200.131
port=3306
[server2]
#candidate_master=1 #此条暂时注释掉(后面解释)
#check_repl_delay=0 #此条暂时注释掉(后面解释)
hostname=192.168.200.147
port=3306
[server3]
hostname=192.168.200.148
port=3306
#参数:candidate_master=1
#解释:设置为候选master,如果设置该参数以后,发生主从切换以后会将此从库提升为主库,即使这个主库不是集群中事件最新的slave
#参数:check_repl_delay=0
#解释:默认情况下如果一个slave落后master 100M的relay logs 的话,MHA将不会选择该slave作为一个新的master,因为对于这个slave的恢复需要花费很长时间,通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时,这个参数对于设置了candidate_master=1的主机非常有用,因为这个候选主在切换的过程中一定是新的master
5.3 配置ssh信任(所有节点mysql-master,mysql-slaveA,mysql-slaveB)
#发送mysql-master公钥,包括自己
[root@localhost rpm]# ssh-keygen -t dsa -P "" -f ~/.ssh/id_dsa
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.131
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.147
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.148
#发送mysql-slaveA公钥,包括自己
[root@localhost rpm]# ssh-keygen -t dsa -P "" -f ~/.ssh/id_dsa
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.131
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.147
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.148
#发送mysql-slaveB公钥,包括自己
[root@localhost rpm]# ssh-keygen -t dsa -P "" -f ~/.ssh/id_dsa
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.131
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.147
[root@localhost rpm]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.200.148
5.4 启动测试
5.4.1 ssh检查检测
[root@localhost ~]# masterha_check_ssh --conf=/etc/mha/mha1.cnf #ssh检查命令
Tue Sep 5 03:01:38 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Sep 5 03:01:38 2018 - [info] Reading application default configuration from /etc/mha/mha1.cnf..
Tue Sep 5 03:01:38 2018 - [info] Reading server configuration from /etc/mha/mha1.cnf..
Tue Sep 5 03:01:38 2018 - [info] Starting SSH connection tests..
..中间省略若干行..
Tue Sep 5 03:01:40 2018 - [debug] Connecting via SSH from root@192.168.0.53(192.168.0.53:22) to root@192.168.200.148(192.168.200.148:22)..
Tue Sep 5 03:01:40 2018 - [debug] ok.
Tue Sep 5 03:01:40 2018 - [info] All SSH connection tests passed successfully. #出现这个就表示成功
5.4.2 主从复制检测
(1)错误的主从复制检测
[root@mysql-db03 ~]# masterha_check_repl --conf=/etc/mha/mha1.cnf
如果出现下面截图错误
在mysql-db02和mysql-db03上添加主从复制的用户即可。 grant replication slave on *.* to rep@'192.168.200.%' identified by '111111';
5.5 启动MHA
#启动
[root@localhost ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
[root@localhost .ssh]# ps -ef | grep perl | grep -v grep
root 2879 959 0 00:35 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover
#说明:
nohup:启动命令
--conf:指定配置文件位置
--remove_dead_master_conf:如果有master down了,就去掉配置文件里该master的部分。
5.6 进行mha自动切换master的测试
初始状态:
(1)登陆mysql-slaveA(192.168.200.147)查看信息状态
[root@localhost .ssh]# mysql -uroot -p111111
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.131
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 191
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 401
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
#省略
(2)停掉mysql-master(192.168.200.131)上的MySQL服务
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL..... SUCCESS!
(3)查看mysql-slaveB上的MySQL从库同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.147 #现在主库ip
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003 #binlog日志
Read_Master_Log_Pos: 231 #binlog日志位置
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 401
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(4)查看mysql-slaveA上的MySQL,主库同步状态。
(5)查看mysql-slaveB上的mha进程状态
[root@localhost ~]# ps -ef | grep perl | grep -v grep #查询发现mha进程已经没了
[root@localhost ~]#
(6)查看mha配置文件信息
5.7 进行mha的故障还原测试
初始状态:
(1)将故障宕机的mysql-master的MySQL服务启动并授权进行从同步
[root@localhost .ssh]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
[root@localhost .ssh]# mysql -uroot -p111111
mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.147', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER__USER='rep', MASTER_PASSWORD='111111';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.147
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 231
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 401
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes #加入了同步
Slave_SQL_Running: Yes #加入了同步
Replicate_Do_DB:
Replicate_Ignore_DB:
(2)将mha配置文件里缺失的部分补全(管理端)
[root@localhost ~]# vim /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager
manager_workdir=/var/log/mha/mha1
master_binlog_dir=/usr/local/mysql/data
password=111111
ping_interval=2
repl_password=111111
repl_user=rep
ssh_user=root
user=mha
[server1] #补全
hostname=192.168.200.131
port=3306
[server2]
hostname=192.168.200.147
port=3306
[server3]
hostname=192.168.200.148
port=3306
(3)启动mha进程(管理端)
[root@localhost ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
[root@localhost ~]# ps -ef | grep perl | grep -v grep
root 3339 959 1 01:08 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover
(4)停掉mysql-slaveA上的MySQL服务
[root@localhost .ssh]# /etc/init.d/mysqld stop
Shutting down MySQL..... SUCCESS!
(5)查看mysql-slaveB上的主从同步状态:
[root@localhost ~]# mysql -uroot -p111111 -e 'show slave status\G'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.131 #恢复了
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 448
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
(6)启动mysql-db02上的MySQL服务
[root@localhost .ssh]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
[root@localhost .ssh]# mysql -uroot -p111111
mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.131', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='111111';
mysql> start slave;
mysql> show slave status\G #查看从同步状态
(7)再次补全mha配置文件后,启动mha进程(管理端)
[root@localhost ~]# vim /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager
manager_workdir=/var/log/mha/mha1
master_binlog_dir=/usr/local/mysql/data
password=111111
ping_interval=2
repl_password=111111
repl_user=rep
ssh_user=root
user=mha
[server1]
hostname=192.168.200.131
port=3306
[server2]
hoastname=192.168.200.147
port=3306
[server3]
hostname=192.168.200.148
port=3306
[root@localhost ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
[1] 3435
[root@localhost ~]# ps -ef | grep perl | grep -v grep
root 3435 959 2 01:16 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover
此时的初始状态还原为下图:
6.源码安装mha的方法
node节点的源码安装方法:
[root@mysql-db01 ~]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Params-Validate perl-CPAN perl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
[root@mysql-db01 ~]# tar xf mha4mysql-node-0.56.tar.gz -C /usr/src/
[root@mysql-db01 ~]# cd /usr/src/mha4mysql-node-0.56/
[root@mysql-db01 mha4mysql-node-0.56]# perl Makefile.PL
[root@mysql-db01 mha4mysql-node-0.56]# make && make install
manager节点的源码安装方法:
[root@mysql-db01 ~]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Params-Validate perl-CPAN perl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
[root@mysql-db01 ~]# tar xf mha4mysql-manager-0.56.tar.gz -C /usr/src/
[root@mysql-db01 ~]# cd /usr/src/mha4mysql-manager-0.56/
[root@mysql-db01 mha4mysql-manager-0.56]# perl Makefile.PL
[root@mysql-db01 mha4mysql-manager-0.56]# make && make install