mysql小白系列_11 MHA

一.MHA是什么？能干什么的

  • (1)以Perl语言写的一套Mysql故障切换方案，一个脚本管理工具
  • (2)保障数据库的高可用性
  • (3)修复多个slave之间的差异日志，最终使所有的slave保持数据一致
  • (4)能在0-30秒内选定新Master，实现mysql故障转移

二.MHA组成部分

MHA manager 管理节点
可以部署在一台单独的机器上，管理多套一主多从的集群，也可以部署在一台slave之上，作用：

  • (1)运行一些管理和监控工具
  • (2使用masterha_manager工具自动监控mysql master和实现master故障切换
  • (3以及手工实现master故障切换、在线master转移、连接检查等

MHA node 数据节点
image
必须部署在每个独立的Mysql服务器之上，包括master和slave，作用：

  • (1)保存二进制日志binlog
    如果能够访问故障的master，即mysqld挂了，ssh还能连接OS，拷贝master的二进制日志
  • (2)应用差异中继日志
    从拥有最新日志的slave上生成差异中继日志，然后应用差异日志
  • (3)清除中继日志
    在不停止SQL线程的情况下删除中继日志

三.工作原理

image

  • (1)当master出现故障时，通过对比slave之间的IO线程读取master binlog的位置，选取最接近的slave作为latest slave
  • (2)其他slave通过与latest slave对比，生成差异中继日志
  • (3)在latest slave上应用故障master中保存的binlog（如果能访问ssh），同时将latest slave提升为master
  • (4)最后在其他slave上应用差异中继日志并开始从新的master进行主从复制

四.工作流程

image

  • (1)从宕机崩溃的master保存二进制日志事件(binlog events)---binlog冗余方案？
  • (2)识别含有最新更新的slave---如何识别？Master_Log_File、Read_Master_Log_Pos
  • (3)应用差异中继日志到其他slave---所有slave保持一致
  • (4)应用从master保存的二进制日志事件---有差异则补差异
  • (5)提升一个slave作为新的master，并记录binlog file和position
  • (6)使其他的slave连接新的master进行复制
  • (7)完成manager主进程offline

五.MHA工具

Manager工具：

  • masterha_check_ssh 检查MHA的ssh配置
  • masterha_check_repl 检查mysql复制状态工具
  • masterha_manager 启动MHA进程
  • masterha_check_status 检查当前MHA运行状态
  • masterha_master_monitor 监控master是否宕机
  • masterha_master_switch 自动或者手动控制故障转移
  • masterha_conf_host 添加或删除配置的server信息

Node工具

  • save_binary_logs 保存和复制master的二进制日志
  • apply_diff_relay_logs 识别差异的中继日志事件并应用于其他salve
  • filter_mysqlbinlog 去除不必要的rollback事件(obsolete)
  • purge_relay_logs 清楚中继日志（不会阻塞SQL线程）

六.boost方式安装mysql5.7并配置主从

(1)系统环境

  • centos7.1
  • mysql-boost-5.7.21.tar.gz
  • 1主2从
    • master 172.16.3.155
    • slave01 172.16.3.153
    • slave02 172.16.3.154
    • MHA 172.16.3.152

(2)系统前置配置

1.SSH关闭dns
[root@master ~]# vi /etc/ssh/sshd_config
UseDNS no
[root@master ~]# systemctl restart sshd
[root@master ~]#
2.关闭selinux
[root@master ~]# vi /etc/selinux/config
SELINUX=permissive
[root@master ~]# setenforce 0
[root@master ~]# getenforce
Permissive
3.设置防火墙信任
[root@master ~]# firewall-cmd --set-default-zone=trusted
success
4.设置文件和资源使用
[root@master ~]# vi /etc/security/limits.conf
*       soft    nofile  65535
*       hard    nofile  65535
*       soft    nproc   65535
*       hard    nproc  65535
5.设置虚拟内存使用
[root@master ~]# sysctl -a |grep swappiness
vm.swappiness = 30
[root@master ~]# sysctl vm.swappiness=10
vm.swappiness = 10
[root@master ~]# echo 10 > /proc/sys/vm/swappiness
[root@master ~]#  sysctl -a |grep swappiness
vm.swappiness = 10
[root@master ~]# echo "vm.swappiness=10" > /etc/sysctl.conf
6.安装YUM源
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql57-community-release-el7-11.noarch.rpm 
yum install get bash-completion.noarch lrzsz -y
7.安装依赖包

yum install -y gcc gcc-c++ ncurses-devel bison zlib libxml openssl openssl-devel ncurses

可以一起yum安装cmake：
yum install -y cmake gcc gcc-c++ ncurses-devel bison zlib libxml openssl openssl-devel ncurses

8.编译安装cmake
wget https://cmake.org/files/v3.4/cmake-3.4.1.tar.gz
tar -zxvf cmake-3.4.1.tar.gz
cd cmake-3.4.1
./bootstrap 
gmake && make install

(3)安装mysql

1.创建mysql用户和组
groupadd mysql
useradd -r -M -g mysql mysql -s /sbin/nologin

-r创建系统用户，即不创建home目录

2.创建文件存储目录
mkdir -p /mysqlData/{data,tmp,logs/undolog,logs/RELAY_LOG,logs/binlog}
3.下载mysql源码包并解压
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-boost-5.7.16.tar.gz
tar -xvf mysql-boost-5.7.16.tar.gz
cd mysql-5.7.16
4.编译安装mysql
cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DWITH_ARCHIVE_STORAGE_ENGINE=1 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 
-DMYSQL_DATADIR=/mysqlData/data 
-DMYSQL_TCP_PORT=3306 
-DENABLE_DOWNLOADS=1 
-DWITH_EMBEDDED_SERVER=1 
-DWITH_BOOST=boost

make && make install

编译时指定ssl

cmake . 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DWITH_ARCHIVE_STORAGE_ENGINE=1 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 
-DMYSQL_DATADIR=/mysqlData/data 
-DMYSQL_TCP_PORT=3316 
-DENABLE_DOWNLOADS=1 
-DWITH_EMBEDDED_SERVER=1 
-DWITH_BOOST=boost 
-DWITH_SSL=yes 

make && make install

cmake参数 https://dev.mysql.com/doc/internals/en/listing-configuration-options.html

5.my.cnf

http://imysql.com/my-cnf-wizard.html
内存2G

6.修改mysql所有相关属主
chown -R mysql:mysql /usr/local/mysql /mysqlData /etc/my.cnf
7.拷贝启动
cp /dvd/mysql-5.7.16/support-files/mysql.server /etc/init.d/mysqld
vi /etc/profile
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
export PATH  
source /etc/profile
8.初始化mysql数据库
cd /usr/local/mysql
bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
9.开启SSL新功能
bin/mysql_ssl_rsa_setup --defaults-file=/etc/my.cnf --user=mysql
chown -R mysql:mysql /mysqlData/data

为什么编译的时候不开启？如果编译的时候添加了

10.加入自动启动
  • 1.chkconfig启动 6.5
chkconfig mysqld on
  • 2.systemctl启动
cat "/usr/lib/systemd/system/mysqld.service"
[Unit]
Description=MySQL DBMS

[Service]
LimitNOFILE=65535
Type=simple
User=mysql
Group=mysql
PIDFile=/mysqlData/tmp/master.pid
ExecStart=/usr/local/mysql/bin/mysqld_safe --datadir=/mysqlData/data/
ExecStop=/bin/kill -9 $MAINPID

[Install]
WantedBy=multi-user.target

systemctl enable mysqld
systemctl list-unit-files |grep mysqld

改造mysqld.service: http://blog.csdn.net/wojiushiwo945you/article/details/77304109
实际生产还是人肉启动

11.初始化root密码
alter user 'root'@'localhost' identified by '123456';
12.删除testDB和移除匿名用户
/usr/local/mysql/bin/mysql_secure_installation

(4)配置主从

1.主库创建复制账号
grant replication slave,replication client on *.* to 'repl'@'172.16.3.%' identified by 'Rep12#3@';
2.从库指向master
CHANGE MASTER TO
MASTER_HOST='172.16.3.155',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Rep12#3@',
master_auto_position=1;

记得更改server_id
MASTER_LOG_FILE='binlog.00000x',
MASTER_LOG_POS=xxx;

3.从库启动复制
start slave;
show slave statusG
set global read_only=1;
4.master.info和relay-log.info

master和relay-log的信息可以存放在文件FILE，也可以存放在table中

  • 以明文方式存储
  • 查看复制信息存储方式
SHOW VARIABLES WHERE variable_name IN ('relay_log_recovery','relay_log_info_repository','master_info_repository');
  • 修改存储方式为table
#命令号临时修改
stop slave;
set GLOBAL  relay_log_info_repository='TABLE';
#my.cnf修改重启生效
relay_log_info_repository = TABLE
master_info_repository    = TABLE
relay_log_recovery        = on
  • SQL查看主从同步方式
select  *   from mysql.slave_master_info;
select  *   from mysql.slave_relay_log_info;

(5)启动半同步复制

1.加入semi参数
###################semi sync replication settings##############
rpl_semi_sync_master_enabled = 1   
rpl_semi_sync_master_timeout = 5000 # 5 second
2.master安装半同步插件
root@master 15:36:  [(none)]> show global variables like '%rpl%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| rpl_stop_slave_timeout | 31536000 |
+------------------------+----------+
1 row in set (0.01 sec)
 
root@master 15:36:  [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.04 sec)

root@master 15:36:  [(none)]> show global variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)
3.slave安装半同步插件
root@slave01 15:44:  [(none)]> show variables like '%rpl%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| rpl_stop_slave_timeout | 31536000 |
+------------------------+----------+
1 row in set (0.00 sec)

root@slave01 15:44:  [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

root@slave01 15:44:  [(none)]> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | OFF      |
| rpl_semi_sync_slave_trace_level | 32       |
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+
3 rows in set (0.01 sec)
4.master启用半同步插件
root@master 15:37:  [(none)]> set global rpl_semi_sync_master_enabled=on;
Query OK, 0 rows affected (0.00 sec)

root@master 15:38:  [(none)]> show global variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)
5.slave启用半同步插件
root@slave01 15:55:  [(none)]> set global rpl_semi_sync_slave_enabled=on;
Query OK, 0 rows affected (0.00 sec)

root@slave01 15:55:  [(none)]> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)
6.master 配置半同步超时
root@master 15:38:  [(none)]> set global rpl_semi_sync_master_timeout = 5000;
Query OK, 0 rows affected (0.00 sec)

root@master 15:38:  [(none)]> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)

Rpl_semi_sync_master_status | ON 表示启用半同步
Rpl_semi_sync_master_clients | 0 表示启用半同步的从库有几个

7.重启mysqld
  • 插件目录位置：/usr/local/mysql/lib/plugin
  • master 安装semisync_master.so
  • slave 装semisync_slave.so
  • master my.cnf启用：rpl_semi_sync_master_enabled = 1，默认10秒超时，10秒超时转为异步同步
  • slave my.cnf启用：rpl_semi_sync_slave_enabled=on
8.slave查看半同步状态
  • slave01
root@slave01 16:14:  [(none)]> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |
| rpl_semi_sync_slave_trace_level | 32       |
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
  • slave02
root@slave01 16:13:  [(none)]> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |
| rpl_semi_sync_slave_trace_level | 32       |
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
9.因为MHA切换之后master和slave会互换角色，因此所有的msyql节点都需要安装半同步插件
  • master
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_master_enabled=on ;
  • slave
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=on ;
10.semi解释
Rpl_semi_sync_master_clients               # 显示成有多少个从服务器配置成了半同步复制模式
Rpl_semi_sync_master_net_avg_wait_time     #平均等待时间（默认毫秒）
Rpl_semi_sync_master_net_wait_time         #总共等待时间
Rpl_semi_sync_master_net_waits             #等待次数
Rpl_semi_sync_master_no_times              #关闭半同步复制的次数
Rpl_semi_sync_master_no_tx                 #显示从服务器确认的不成功提交数量
Rpl_semi_sync_master_status                #表示当前是异步模式还是半同步模式，on为半同步
Rpl_semi_sync_master_timefunc_failures     #调用时间函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time      #事务的平均传输时间
Rpl_semi_sync_master_tx_wait_time          #事务的总共传输时间
Rpl_semi_sync_master_tx_waits              #事务等待次数
Rpl_semi_sync_master_wait_pos_backtraverse # 网上有人理解为"后来的先到了，而先来的还没有到的次数"
Rpl_semi_sync_master_wait_sessions         #当前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx                #显示从服务器确认的成功提交数量

七.安装MHA

  • centos7.1
  • mysql-boost-5.7.21.tar.gz
  • 1主2从
    • master 172.16.3.155
    • slave01 172.16.3.153
    • slave02 172.16.3.154
1.所有节点安装perl依赖包
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes cpan perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
2.manager节点安装工具
wget https://raw.githubusercontent.com/yotoobo/linux/master/mha/mha4mysql-manager-0.56.tar.gz
tar xzvf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56
perl Makefile.PL
make && make install

命令文件：

Installing /usr/local/bin/masterha_stop
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_check_ssh
Installing /usr/local/bin/masterha_master_switch
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_manager

安装guide: https://github.com/yoshinorim/mha4mysql-manager/wiki/Installation
wget https://downloads.mariadb.com/MHA/mha4mysql-manager-0.56.tar.gz

3.node节点安装工具(所有节点，包括manager节点)
wget https://raw.githubusercontent.com/yotoobo/linux/master/mha/mha4mysql-node-0.56.tar.gz
tar xzvf mha4mysql-node-0.56.tar.gz
cd mha4mysql-node-0.56
perl Makefile.PL
make && make install

wget https://downloads.mariadb.com/MHA/mha4mysql-node-0.56.tar.gz

yum安装

# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
# #根据MHA角色安装对应的软件包即可
# yum -y --nogpgcheck install mha4mysql-node-0.57-0.el7.noarch.rpm
# yum -y install --nogpgcheck mha4mysql-manager-0.57-0.el7.noarch.rpm
  • 本地安装rpm
# yum -y --nogpgcheck install perl-DBD-MySQL*
# yum -y --nogpgcheck install perl-Config-Tiny*
# yum -y --nogpgcheck install perl-Parallel-ForkManager*
# yum -y --nogpgcheck install  perl-MailTools*
# yum -y --nogpgcheck install perl-Email-Date-Format*
# yum -y --nogpgcheck install perl-Mail-Sender*
# yum -y --nogpgcheck install perl-MIME-Types*
# yum -y --nogpgcheck install perl-MIME-Lite*
# yum -y --nogpgcheck install perl-Mail-Sendmail*
# yum -y --nogpgcheck install perl-Log-Dispatch*
# #根据MHA角色安装对应的软件包即可 
# yum -y --nogpgcheck install mha4mysql-node-0.57-0.el7.noarch.rpm
# yum -y install --nogpgcheck mha4mysql-manager-0.57-0.el7.noarch.rpm
作者：BruceLiu1
链接：https://www.jianshu.com/p/6173dae5ed7a
4.在master上创建MHA用户
grant  all privileges on *.* to 'mha'@'172.16.3.%' identified by 'Mha12#3@';

5.7更改密码

update mysql.user set authentication_string=password('') where User=”test” and Host=”localhost”
flush privileges;
5.节点间配置SSH互信
(1)基于mysql用户，配置mysql用户的sudo权限
  • 添加普通用户登录tty终端权限
vi /etc/sudoers
#Defaults   requiretty

开放普通用户执行sudo权限

vi /etc/sudoers.d/mysql
User_Alias  MYSQL_USERS = ALL
Runas_Alias MYSQL_RUNAS = root
Cmnd_Alias  MYSQL_CMNDS = ALL
MYSQL_USERS ALL = (MYSQL_RUNAS) NOPASSWD: MYSQL_CMNDS
(2)配置rsa免密码登录

要求：

  • 1.mha管理节点需要ssh免密码到所有mysql节点
  • 2.各个mysql节点需要互相ssh免密码

配置：

  • mha管理节点：
# 管理节点生成公私钥
ssh-keygen -t rsa
# 将公钥拷贝到其他3个节点
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.155
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.154
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.153
  • mysql节点各自生成公私钥，并将公钥拷贝给其他mysql节点
# 172.16.3.155
ssh-keygen -t rsa
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.154
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.153
# 172.16.3.154
ssh-keygen -t rsa
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.155
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.153
# 172.16.3.153
ssh-keygen -t rsa
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.155
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.154
  • 测试ssh是否免密码登录
6.配置MHA配置
(1)manager上创建配置目录和配置文件
vi /mysqlData/mha/etc/app.cnf
[server default]
user = mha
password = Mha12#3@
ssh_user = root
repl_user = repl
repl_password = Rep12#3@
ping_interval = 1
ping_type = SELECT

manager_workdir=/mysqlData/mha/etc/app
manager_log=/mysqlData/mha/log/manager.log
remote_workdir=/mysqlData/mha/etc/app
master_binlog_dir="/mysqlData/logs/binlog/"

secondary_check_script="masterha_secondary_check -s 172.16.3.151 -s 172.16.3.151"
master_ip_failover_script="/mysqlData/mha/etc/master_ip_failover"
master_ip_online_change_script="/mysqlData/mha/etc/master_ip_failover"

shutdown_script=""

report_script=""

#check_repl_delay=0

[server1]
hostname=master
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=1
ignore_fail=1

[server2]
hostname=slave01
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=1
ignore_fail=1

[server3]
hostname=slave02
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=0
ignore_fail=1

vim /mysqlData/mha/etc/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
 
use Getopt::Long;
 
my (
    $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
);
 
my $vip = '172.16.3.151';  # Virtual IP
my $gateway = '172.16.3.154';  # virtual IP gateway
my $key = "1";
my $int = "eth0";
my $ssh_start_vip = "/sbin/ifconfig $int:$key $vip/24";
my $ssh_stop_vip = "/sbin/ifconfig $int:$key down";
my $arp_effect = "/sbin/arping -f -q -c 5 -w 5 -I $int -s $vip -U $gateway";    # Virtual IP and gateway
#my $test = "echo successfull >/tmp/test.txt";
$ssh_user = "root";
GetOptions(
    'command=s'          => $command,
    'ssh_user=s'         => $ssh_user,
    'orig_master_host=s' => $orig_master_host,
    'orig_master_ip=s'   => $orig_master_ip,
    'orig_master_port=i' => $orig_master_port,
    'new_master_host=s'  => $new_master_host,
    'new_master_ip=s'    => $new_master_ip,
    'new_master_port=i'  => $new_master_port,
);
 
exit &main();
 
sub main {
 
    print "

IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===

";
 
    if ( $command eq "stop" || $command eq "stopssh" ) {
 
        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
        # If you manage master ip address at global catalog database,
        # invalidate orig_master_ip here.
        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host 
";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@
";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {
 
        # all arguments are passed.
        # If you manage master ip address at global catalog database,
        # activate new_master_ip here.
        # You can also grant write access (create user, set read_only=0, etc) here.
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host 
";
            &start_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
        print "Checking the Status of the script.. OK 
";
        #`ssh $ssh_user@cluster1 " $ssh_start_vip "`;
        &status();
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}
 
# A simple system call that enable the VIP on the new master
sub start_vip() {
    `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
    `ssh $ssh_user@$new_master_host " $arp_effect "`;
#    `ssh $ssh_user@$new_master_host " $test "`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
    `ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;
}

sub status() {
    print `ssh $ssh_user@$orig_master_host " ip add show $int "`;
}
 
sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_maste
r_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port
";
}
(2)启动MHA管理节点
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf  --ignore_last_failover &
  • 1.报错
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
  • 2.检查
masterha_check_ssh --conf=/mysqlData/mha/etc/app.cnf
  • 3.检查结果
from mysql@master(172.16.3.155:22) to mysql@slave01(172.16.3.153:22)
from mysql@slave01(172.16.3.153:22) to mysql@master(172.16.3.155:22)
from mysql@slave02(172.16.3.154:22) to mysql@master(172.16.3.155:22)
  • 4.很奇怪，测试是可免密码ssh的
# master to slave01
[mysql@master ~]$ ssh slave01
Last login: Tue Mar  6 14:13:57 2018 from master
[mysql@slave01 ~]$ exit
# slave01 to master
[mysql@slave01 ~]$ ssh master
Last login: Tue Mar  6 14:14:29 2018 from master
[mysql@master ~]$ 
# slave02 to master
[mysql@slave02 ~]$ ssh master
Last login: Tue Mar  6 16:40:12 2018 from slave01
[mysql@master ~]$
  • 5.mysql节点重新设置权限也不行
chown -R mysql:mysql /home/mysql
chmod 700 /home/mysql
chmod 700 /home/mysql/.ssh
chmod 644 /home/mysql/.ssh/authorized_keys
chmod 600 /home/mysql/.ssh/id_rsa

ssh_user改成root用户
此时VIP不会自己起来，作为一个后安装的管理工具，不会去更改现有的架构

7.在master上启动vip
# /sbin/ifconfig eth0:1 172.16.3.151 broadcast 172.16.3.255 netmask 255.255.255.0
# /sbin/arping -f -q -c 5 -w 5 -I eth0 -s 172.16.3.151 -U 172.16.3.254
8.MHA常用命令
# 检查ssh是成功免密码登录
/usr/local/bin/masterha_check_ssh --conf=/mysqlData/mha/etc/app.cnf
# 检查主从复制是否正常
/usr/local/bin/masterha_check_repl --conf=/mysqlData/mha/etc/app.cnf
# 启动MHA进程
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
# 检查MHA（app）状态
/usr/local/bin/masterha_check_status --conf=/mysqlData/mha/etc/app.cnf
# 停止MHA进程
/usr/local/bin/masterha_stop --conf=/mysqlData/mha/etc/app.cnf

八.模拟主库宕机failover

1.切换VIP

  • (1)主库kill进程
[root@master ~]# killall -r mysqld
[root@master ~]# ps -ef|grep mysql
root      1465 10243  0 17:13 pts/1    00:00:00 grep --color=auto mysql
  • (2)查看mha日志
Tue Mar  6 17:13:16 2018 - [info] Master failover to slave01(172.16.3.153:3306) completed successfully.
Tue Mar  6 17:13:16 2018 - [info] 
#
----- Failover Report -----
#
app: MySQL Master failover master(172.16.3.155:3306) to slave01(172.16.3.153:3306) succeeded
#
Master master(172.16.3.155:3306) is down!
#
Check MHA Manager logs at mha:/mysqlData/mha/log/manager.log for details.
#
Started automated(non-interactive) failover.
Invalidated master IP address on master(172.16.3.155:3306)
Selected slave01(172.16.3.153:3306) as a new master.
slave01(172.16.3.153:3306): OK: Applying all logs succeeded.
slave01(172.16.3.153:3306): OK: Activated master IP address.
slave02(172.16.3.154:3306): OK: Slave started, replicating from slave01(172.16.3.153:3306)
slave01(172.16.3.153:3306): Resetting slave info succeeded.
Master failover to slave01(172.16.3.153:3306) completed successfully.
  • (3)vip
[root@mha log]# cat manager.log |grep -i vip
Disabling the VIP on old master: master 
Enabling the VIP - 172.16.3.151/24 on the new master - slave01
  • (4)slave01上已经看到VIP过来了
[root@slave01 home]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:50:56:a3:30:bb brd ff:ff:ff:ff:ff:ff
    inet 172.16.3.153/24 brd 172.16.3.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.16.3.151/24 brd 172.16.3.255 scope global secondary eth0:1
       valid_lft forever preferred_lft forever
    inet6 fe80::250:56ff:fea3:30bb/64 scope link 
       valid_lft forever preferred_lft forever

2.将原来的master配置成slave

  • (1)查看新的master(slave01)
root@slave01 17:18:  [(none)]> show master statusG
*************************** 1. row ***************************
             File: binlog.000130
         Position: 1898
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-12,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1
1 row in set (0.00 sec)
root@slave01 17:18:  [(none)]> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)
  • (2)开启新的slave(master)，打开MySQL
[root@master ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
  • (3)检查数据库
root@master 17:21:  [(none)]> show master statusG
*************************** 1. row ***************************
             File: binlog.000010
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-12
1 row in set (0.00 sec)
#
root@master 17:21:  [(none)]> show slave statusG
Empty set (0.00 sec)
  • (4)在管理节点日志中查主库的日志文件和位置
[root@mha log]# cat manager.log |grep -i change
Tue Mar  6 17:13:11 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='slave01 or 172.16.3.153', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Tue Mar  6 17:13:14 2018 - [info]  Executed CHANGE MASTER.
  • (5)在slave连接master
CHANGE MASTER TO
MASTER_HOST='172.16.3.153',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_USER='repl',
MASTER_PASSWORD='Rep12#3@';
  • (6)查看状态
root@master 17:27:  [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.3.153
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000130
          Read_Master_Log_Pos: 1898
               Relay_Log_File: relaylog.000130
                Relay_Log_Pos: 485
        Relay_Master_Log_File: binlog.000130
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  • (7)启动管理节点
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
# 跳过切换错误启动
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf  --ignore_last_failover &

九.线上切换switchover

  • 1.master：关闭event_scheduler（即172.16.3.153）set global event_scheduler=off;
root@slave01 17:29:  [(none)]> show variables like '%event_schedu%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.02 sec)

本来就是OFF的，如果不是off，切换的时候报错如下

Wed Mar  7 11:24:41 2018 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln161] We should not start online master switch when one of connections are running long updates on the current master(slave01(172.16.3.153:3306)). Currently 1 update thread(s) are running.
Details:
{'Time' => '453','db' => undef,'Id' => '101','User' => 'event_scheduler','State' => 'Waiting on empty queue','Command' => 'Daemon','Info' => undef,'Host' => 'localhost'}
Wed Mar  7 11:24:41 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/local/bin/masterha_master_switch line 53.
  • 2.manager：关闭管理进程 （即172.16.3.152）
/usr/local/bin/masterha_stop --conf=/mysqlData/mha/etc/app.cnf

日志

Tue Mar  6 17:33:01 2018 - [info] Got terminate signal. Exit.
  • 3.manager：检查配置文件
[server1]
hostname=master
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=1
ignore_fail=1
#
[server2]
hostname=slave01
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
#candidate_master=1
#ignore_fail=1
#
[server3]
hostname=slave02
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=0
ignore_fail=1

/mysqlData/mha/etc/app.cnf 有没有被修改破坏。如果破坏需要重新编辑正确配置文件：/mysqlData/mha/etc/app.cnf
cp /mysqlData/mha/etc/app.cnf.bak /mysqlData/mha/etc/app.cnf
server1现在是从库

  • 4.开始切换：/usr/local/bin/masterha_master_switch --master_state=alive --conf=/mysqlData/mha/etc/app.cnf
[root@mha mysqlData]# /usr/local/bin/masterha_master_switch --master_state=alive --conf=/mysqlData/mha/etc/app.cnf
Tue Mar  6 17:35:20 2018 - [info] MHA::MasterRotate version 0.56.
Tue Mar  6 17:35:20 2018 - [info] Starting online master switch..
Tue Mar  6 17:35:20 2018 - [info] 
Tue Mar  6 17:35:20 2018 - [info] * Phase 1: Configuration Check Phase..
Tue Mar  6 17:35:20 2018 - [info] 
Tue Mar  6 17:35:20 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Mar  6 17:35:20 2018 - [info] Reading application default configuration from /mysqlData/mha/etc/app.cnf..
Tue Mar  6 17:35:20 2018 - [info] Reading server configuration from /mysqlData/mha/etc/app.cnf..
Tue Mar  6 17:35:21 2018 - [info] GTID failover mode = 1
Tue Mar  6 17:35:21 2018 - [info] Current Alive Master: slave01(172.16.3.153:3306)
Tue Mar  6 17:35:21 2018 - [info] Alive Slaves:
Tue Mar  6 17:35:21 2018 - [info]   master(172.16.3.155:3306)  Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Tue Mar  6 17:35:21 2018 - [info]     GTID ON
Tue Mar  6 17:35:21 2018 - [info]     Replicating from 172.16.3.153(172.16.3.153:3306)
Tue Mar  6 17:35:21 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Tue Mar  6 17:35:21 2018 - [info]   slave02(172.16.3.154:3306)  Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Tue Mar  6 17:35:21 2018 - [info]     GTID ON
Tue Mar  6 17:35:21 2018 - [info]     Replicating from 172.16.3.153(172.16.3.153:3306)
#
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on slave01(172.16.3.153:3306)? (YES/no): YES
Tue Mar  6 17:35:40 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Tue Mar  6 17:35:40 2018 - [info]  ok.
Tue Mar  6 17:35:40 2018 - [info] Checking MHA is not monitoring or doing failover..
Tue Mar  6 17:35:40 2018 - [info] Checking replication health on master..
Tue Mar  6 17:35:40 2018 - [info]  ok.
Tue Mar  6 17:35:40 2018 - [info] Checking replication health on slave02..
Tue Mar  6 17:35:40 2018 - [info]  ok.
Tue Mar  6 17:35:40 2018 - [info] Searching new master from slaves..
Tue Mar  6 17:35:40 2018 - [info]  Candidate masters from the configuration file:
Tue Mar  6 17:35:40 2018 - [info]   master(172.16.3.155:3306)  Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Tue Mar  6 17:35:40 2018 - [info]     GTID ON
Tue Mar  6 17:35:40 2018 - [info]     Replicating from 172.16.3.153(172.16.3.153:3306)
Tue Mar  6 17:35:40 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Tue Mar  6 17:35:40 2018 - [info]  Non-candidate masters:
Tue Mar  6 17:35:40 2018 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Tue Mar  6 17:35:40 2018 - [info] 
From:
slave01(172.16.3.153:3306) (current master)
 +--master(172.16.3.155:3306)
 +--slave02(172.16.3.154:3306)
#
To:
master(172.16.3.155:3306) (new master)
 +--slave02(172.16.3.154:3306)
#
Starting master switch from slave01(172.16.3.153:3306) to master(172.16.3.155:3306)? (yes/NO): yes
Tue Mar  6 17:35:48 2018 - [info] Checking whether master(172.16.3.155:3306) is ok for the new master..
Tue Mar  6 17:35:48 2018 - [info]  ok.
Tue Mar  6 17:35:48 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Mar  6 17:35:48 2018 - [info] 
Tue Mar  6 17:35:48 2018 - [info] * Phase 2: Rejecting updates Phase..
Tue Mar  6 17:35:48 2018 - [info] 
Tue Mar  6 17:35:48 2018 - [info] Executing master ip online change script to disable write on the current master:
Tue Mar  6 17:35:48 2018 - [info]   /mysqlData/mha/etc/master_ip_failover --command=stop --orig_master_host=slave01 --orig_master_ip=172.16.3.153 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='Mha12#3@' --new_master_host=master --new_master_ip=172.16.3.155 --new_master_port=3306 --new_master_user='mha' --new_master_password='Mha12#3@' --orig_master_ssh_user=root --new_master_ssh_user=root  
Unknown option: orig_master_user
Unknown option: orig_master_password
Unknown option: new_master_user
Unknown option: new_master_password
Unknown option: orig_master_ssh_user
Unknown option: new_master_ssh_user
#
#
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.3.151/24===
#
Disabling the VIP on old master: slave01 
mysql@slave01's password: 
SIOCSIFFLAGS: Operation not permitted
Tue Mar  6 17:35:53 2018 - [info]  ok.
Tue Mar  6 17:35:53 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Tue Mar  6 17:35:53 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
Tue Mar  6 17:35:53 2018 - [info]  ok.
Tue Mar  6 17:35:53 2018 - [info] Orig master binlog:pos is binlog.000130:1898.
Tue Mar  6 17:35:53 2018 - [info]  Waiting to execute all relay logs on master(172.16.3.155:3306)..
Tue Mar  6 17:35:53 2018 - [info]  master_pos_wait(binlog.000130:1898) completed on master(172.16.3.155:3306). Executed 0 events.
Tue Mar  6 17:35:53 2018 - [info]   done.
Tue Mar  6 17:35:53 2018 - [info] Getting new master's binlog name and position..
Tue Mar  6 17:35:53 2018 - [info]  binlog.000010:994
Tue Mar  6 17:35:53 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='master or 172.16.3.155', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Tue Mar  6 17:35:53 2018 - [info] Executing master ip online change script to allow write on the new master:
Tue Mar  6 17:35:53 2018 - [info]   /mysqlData/mha/etc/master_ip_failover --command=start --orig_master_host=slave01 --orig_master_ip=172.16.3.153 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='Mha12#3@' --new_master_host=master --new_master_ip=172.16.3.155 --new_master_port=3306 --new_master_user='mha' --new_master_password='Mha12#3@' --orig_master_ssh_user=root --new_master_ssh_user=root  
Unknown option: orig_master_user
Unknown option: orig_master_password
Unknown option: new_master_user
Unknown option: new_master_password
Unknown option: orig_master_ssh_user
Unknown option: new_master_ssh_user
#
#
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.3.151/24===
#
Enabling the VIP - 172.16.3.151/24 on the new master - master 
mysql@master's password: 
SIOCSIFADDR: Operation not permitted
SIOCSIFFLAGS: Operation not permitted
SIOCSIFNETMASK: Operation not permitted
mysql@master's password: 
bind: Cannot assign requested address
Tue Mar  6 17:35:58 2018 - [info]  ok.
Tue Mar  6 17:35:58 2018 - [info] 
Tue Mar  6 17:35:58 2018 - [info] * Switching slaves in parallel..
Tue Mar  6 17:35:58 2018 - [info] 
Tue Mar  6 17:35:58 2018 - [info] -- Slave switch on host slave02(172.16.3.154:3306) started, pid: 3631
Tue Mar  6 17:35:58 2018 - [info] 
Tue Mar  6 17:36:00 2018 - [info] Log messages from slave02 ...
Tue Mar  6 17:36:00 2018 - [info] 
Tue Mar  6 17:35:58 2018 - [info]  Waiting to execute all relay logs on slave02(172.16.3.154:3306)..
Tue Mar  6 17:35:58 2018 - [info]  master_pos_wait(binlog.000130:1898) completed on slave02(172.16.3.154:3306). Executed 0 events.
Tue Mar  6 17:35:58 2018 - [info]   done.
Tue Mar  6 17:35:58 2018 - [info]  Resetting slave slave02(172.16.3.154:3306) and starting replication from the new master master(172.16.3.155:3306)..
Tue Mar  6 17:35:58 2018 - [info]  Executed CHANGE MASTER.
Tue Mar  6 17:35:59 2018 - [info]  Slave started.
Tue Mar  6 17:36:00 2018 - [info] End of log messages from slave02 ...
Tue Mar  6 17:36:00 2018 - [info] 
Tue Mar  6 17:36:00 2018 - [info] -- Slave switch on host slave02(172.16.3.154:3306) succeeded.
Tue Mar  6 17:36:00 2018 - [info] Unlocking all tables on the orig master:
Tue Mar  6 17:36:00 2018 - [info] Executing UNLOCK TABLES..
Tue Mar  6 17:36:00 2018 - [info]  ok.
Tue Mar  6 17:36:00 2018 - [info] All new slave servers switched successfully.
Tue Mar  6 17:36:00 2018 - [info] 
Tue Mar  6 17:36:00 2018 - [info] * Phase 5: New master cleanup phase..
Tue Mar  6 17:36:00 2018 - [info] 
Tue Mar  6 17:36:00 2018 - [info]  master: Resetting slave info succeeded.
Tue Mar  6 17:36:00 2018 - [info] Switching master to master(172.16.3.155:3306) completed successfully.
  • 5.new master(old slave)
root@master 17:36:  [(none)]> show master statusG
*************************** 1. row ***************************
             File: binlog.000010
         Position: 994
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-12,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1
1 row in set (0.00 sec)
  • 6.new slave(old master)
    日志内容
CHANGE MASTER TO MASTER_HOST='master or 172.16.3.155', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx

启动slave复制

root@slave01 17:40:  [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='172.16.3.155',
    -> MASTER_PORT=3306,
    -> MASTER_AUTO_POSITION=1,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='Rep12#3@';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#
root@slave01 17:40:  [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
#
root@slave01 17:42:  [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.3.155
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000010
          Read_Master_Log_Pos: 994
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  • 7.启动管理节点/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
Tue Mar  6 17:43:45 2018 - [info]  OK.
Tue Mar  6 17:43:45 2018 - [warning] shutdown_script is not defined.
Tue Mar  6 17:43:45 2018 - [info] Set master ping interval 1 seconds.
Tue Mar  6 17:43:45 2018 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Tue Mar  6 17:43:45 2018 - [info] Starting ping health check on master(172.16.3.155:3306)..
Tue Mar  6 17:43:45 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

http://blog.csdn.net/zengxuewen2045/article/details/51605613
https://www.2cto.com/database/201412/363712.html

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jenvid/p/8516781.html