物理备份
rm -rf /var/lib/mysql/*
systemctl stop mysqld
rm -rf /var/lib/mysql
把刚才的备份文件拷贝到的当前库下
scp 192.168.4.50:/root/mysql.bak /var/lib/mysql/
重启正常
重新初始化数据库
1、停数据库服务
systemctl stop mysqld
2、删除库
rm -rf /var/lib/mysql/*
3、重新启动，mysql数据库会自己检测
[root@51 mysql]# grep password /var/log/mysqld.log | tail
2018-09-13T02:33:34.691299Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:33:34.691305Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:33:36.106363Z 0 [ERROR] unknown variable ‘validate_password_policy=0’
2018-09-13T02:33:37.992951Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:33:37.992957Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:33:41.240994Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:33:41.240996Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:48:15.931350Z 0 [Note] Shutting down plugin ‘sha256_password’
2018-09-13T02:48:15.931352Z 0 [Note] Shutting down plugin ‘mysql_native_password’
2018-09-13T02:48:27.688311Z 1 [Note] A temporary password is generated for root@localhost: p>su6#SVqixP
MHA规划
IP 规划
角色 IP 地址 主机名
Master 数据库服务器 192.168.4.51 master51
备用 1 master 数据库服务器 192.168.4.52 master52
备用 2 master 数据库服务器 192.168.4.53 master53
第 1 台 slave 服务器 192.168.4.54 slave54
第 2 台 slave 服务器 192.168.4.55 slave55
Mha_manager 服务器 192.168.4.56 mgm56
VIP 地址 192.168.4.100
第一步，安装软件包
所有主机上安装 Perl 依赖包
[root@db108 share]# ls perl-*.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
所有主机上授权root作为监控用户
[root@db108 share]# yum -y install perl-*.rpm
mysql> grant all on *.* to ‘root’@’%’ identified by
‘123456’; // 在所有数据节点上授权监控用户
代码如下
[root@51 mha-soft-student]# mysql -uroot -p123456
mysql: [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 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
mysql>
mysql>
mysql> grant all on *.* to ‘root’@’%’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
（52-55省略）
#注意#在备用master上面也要设置相同的授权复制用户
（也就是说在51、52、53都要设置repluser用户，并且要一样）
mysql> grant replication slave on *.* to repluser@”%” identified by “123456”;
Query OK, 0 rows affected, 1 warning (10.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)
安装软件包
在所有数据库服务器上安装 mha-node 包
]# yum -y install perl-DBD-mysql perl-DBI
]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
代码如下
[root@51 mha-soft-student]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
已加载插件：langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
正在检查 mha4mysql-node-0.56-0.el6.noarch.rpm: mha4mysql-node-0.56-0.el6.noarch
mha4mysql-node-0.56-0.el6.noarch.rpm 将被安装
正在解决依赖关系
–> 正在检查事务
—> 软件包 mha4mysql-node.noarch.0.0.56-0.el6 将被 安装
–> 解决依赖关系完成
依赖关系解决
==========================================================================================================
Package 架构 版本 源 大小
==========================================================================================================
正在安装:
mha4mysql-node noarch 0.56-0.el6 /mha4mysql-node-0.56-0.el6.noarch 102 k
事务概要
==========================================================================================================
安装 1 软件包
总计：102 k
安装大小：102 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : mha4mysql-node-0.56-0.el6.noarch 1/1
验证中 : mha4mysql-node-0.56-0.el6.noarch 1/1
已安装:
mha4mysql-node.noarch 0:0.56-0.el6
完毕！
（52-55省略）
在管理主机上安装 mha_node 和 mha-manager包
]# yum -y install perl-DBD-mysql perl-DBI
]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
]# yum -y install perl-ExtUtils-* perl-CPAN-*
]#tar -zxf mha4mysql-manager-0.56.tar.gz
]#cd mha4mysql-manager-0.56
]# perl Makefile.pl
]# make
]# make install
代码如下
node的安装
[root@56 mha-soft-student]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
已加载插件：langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
正在检查 mha4mysql-node-0.56-0.el6.noarch.rpm: mha4mysql-node-0.56-0.el6.noarch
mha4mysql-node-0.56-0.el6.noarch.rpm 将被安装
正在解决依赖关系
–> 正在检查事务
—> 软件包 mha4mysql-node.noarch.0.0.56-0.el6 将被 安装
–> 正在处理依赖关系 perl(DBD::mysql)，它被软件包 mha4mysql-node-0.56-0.el6.noarch 需要
–> 正在检查事务
—> 软件包 perl-DBD-MySQL.x86_64.0.4.023-5.el7 将被 安装
–> 解决依赖关系完成
依赖关系解决
==========================================================================================================
Package 架构 版本 源 大小
==========================================================================================================
正在安装:
mha4mysql-node noarch 0.56-0.el6 /mha4mysql-node-0.56-0.el6.noarch 102 k
为依赖而安装:
perl-DBD-MySQL x86_64 4.023-5.el7 192.168.4.254_rhel7 140 k
事务概要
==========================================================================================================
安装 1 软件包 (+1 依赖软件包)
总计：242 k
总下载量：140 k
安装大小：425 k
Downloading packages:
perl-DBD-MySQL-4.023-5.el7.x86_64.rpm | 140 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : perl-DBD-MySQL-4.023-5.el7.x86_64 1/2
正在安装 : mha4mysql-node-0.56-0.el6.noarch 2/2
验证中 : perl-DBD-MySQL-4.023-5.el7.x86_64 1/2
验证中 : mha4mysql-node-0.56-0.el6.noarch 2/2
已安装:
mha4mysql-node.noarch 0:0.56-0.el6
作为依赖被安装:
perl-DBD-MySQL.x86_64 0:4.023-5.el7
完毕！
manager安装
首先安装依赖包
[root@56 mha4mysql-manager-0.56]# yum install -y perl-ExtUtils-* perl-CPAN-*
已安装:
perl-CPAN.noarch 0:1.9800-292.el7 perl-CPAN-Meta.noarch 0:2.120921-5.el7
perl-CPAN-Meta-Requirements.noarch 0:2.122-7.el7 perl-CPAN-Meta-YAML.noarch 0:0.008-14.el7
perl-ExtUtils-CBuilder.noarch 1:0.28.2.6-292.el7 perl-ExtUtils-Embed.noarch 0:1.30-292.el7
perl-ExtUtils-Install.noarch 0:1.58-292.el7 perl-ExtUtils-MakeMaker.noarch 0:6.68-3.el7
perl-ExtUtils-Manifest.noarch 0:1.61-244.el7 perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7
作为依赖被安装:
gdbm-devel.x86_64 0:1.10-8.el7 glibc-devel.x86_64 0:2.17-196.el7
glibc-headers.x86_64 0:2.17-196.el7 kernel-headers.x86_64 0:3.10.0-693.el7
libdb-devel.x86_64 0:5.3.21-20.el7 perl-Digest.noarch 0:1.17-245.el7
perl-Digest-SHA.x86_64 1:5.85-4.el7 perl-IPC-Cmd.noarch 1:0.80-4.el7
perl-JSON-PP.noarch 0:2.27202-2.el7 perl-Locale-Maketext.noarch 0:1.23-3.el7
perl-Locale-Maketext-Simple.noarch 1:0.21-292.el7 perl-Module-CoreList.noarch 1:2.76.02-292.el7
perl-Module-Load.noarch 1:0.24-3.el7 perl-Module-Load-Conditional.noarch 0:0.54-3.el7
perl-Module-Metadata.noarch 0:1.000018-2.el7 perl-Params-Check.noarch 1:0.38-2.el7
perl-Parse-CPAN-Meta.noarch 1:1.4404-5.el7 perl-Perl-OSType.noarch 0:1.003-3.el7
perl-Test-Harness.noarch 0:3.28-3.el7 perl-devel.x86_64 4:5.16.3-292.el7
perl-local-lib.noarch 0:1.008010-4.el7 perl-version.x86_64 3:0.99.07-2.el7
pyparsing.noarch 0:1.5.6-9.el7 systemtap-sdt-devel.x86_64 0:3.1-3.el7
[root@56 mha4mysql-manager-0.56]# pwd
/root/soft/mysql/mha-soft-student/mha4mysql-manager-0.56
[root@56 mha4mysql-manager-0.56]# ll
总用量 44
-rw-r–r–. 1 4984 users 78 4月 1 2014 AUTHORS
drwxr-xr-x. 2 4984 users 252 4月 1 2014 bin
-rw-r–r–. 1 4984 users 17987 4月 1 2014 COPYING
drwxr-xr-x. 2 4984 users 94 4月 1 2014 debian
drwxr-xr-x. 3 4984 users 20 4月 1 2014 inc
drwxr-xr-x. 3 4984 users 17 4月 1 2014 lib
-rwxr-xr-x. 1 4984 users 846 4月 1 2014 Makefile.PL
-rw-r–r–. 1 4984 users 4875 4月 1 2014 MANIFEST
-rw-r–r–. 1 4984 users 555 4月 1 2014 META.yml
-rw-r–r–. 1 4984 users 258 4月 1 2014 README
drwxr-xr-x. 2 4984 users 35 4月 1 2014 rpm
drwxr-xr-x. 4 4984 users 33 4月 1 2014 samples
drwxr-xr-x. 2 4984 users 49 4月 1 2014 t
drwxr-xr-x. 3 4984 users 53 4月 1 2014 tests
[root@56 mha4mysql-manager-0.56]# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies…
[Core Features]
– DBI …loaded. (1.627)
– DBD::mysql …loaded. (4.023)
– Time::HiRes …loaded. (1.9725)
– Config::Tiny …loaded. (2.14)
– Log::Dispatch …loaded. (2.41)
– Parallel::ForkManager …loaded. (1.18)
– MHA::NodeConst …loaded. (0.56)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete…
Looks good
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
[root@56 mha4mysql-manager-0.56]# make
cp lib/MHA/ManagerUtil.pm blib/lib/MHA/ManagerUtil.pm
cp lib/MHA/Config.pm blib/lib/MHA/Config.pm
cp lib/MHA/HealthCheck.pm blib/lib/MHA/HealthCheck.pm
cp lib/MHA/ManagerConst.pm blib/lib/MHA/ManagerConst.pm
cp lib/MHA/ServerManager.pm blib/lib/MHA/ServerManager.pm
cp lib/MHA/ManagerAdmin.pm blib/lib/MHA/ManagerAdmin.pm
cp lib/MHA/FileStatus.pm blib/lib/MHA/FileStatus.pm
cp lib/MHA/ManagerAdminWrapper.pm blib/lib/MHA/ManagerAdminWrapper.pm
cp lib/MHA/MasterFailover.pm blib/lib/MHA/MasterFailover.pm
cp lib/MHA/MasterRotate.pm blib/lib/MHA/MasterRotate.pm
cp lib/MHA/MasterMonitor.pm blib/lib/MHA/MasterMonitor.pm
cp lib/MHA/SSHCheck.pm blib/lib/MHA/SSHCheck.pm
cp lib/MHA/Server.pm blib/lib/MHA/Server.pm
cp lib/MHA/DBHelper.pm blib/lib/MHA/DBHelper.pm
cp bin/masterha_stop blib/script/masterha_stop
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_stop
cp bin/masterha_conf_host blib/script/masterha_conf_host
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_conf_host
cp bin/masterha_check_repl blib/script/masterha_check_repl
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_check_repl
cp bin/masterha_check_status blib/script/masterha_check_status
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_check_status
cp bin/masterha_master_monitor blib/script/masterha_master_monitor
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_master_monitor
cp bin/masterha_check_ssh blib/script/masterha_check_ssh
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_check_ssh
cp bin/masterha_master_switch blib/script/masterha_master_switch
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_master_switch
cp bin/masterha_secondary_check blib/script/masterha_secondary_check
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_secondary_check
cp bin/masterha_manager blib/script/masterha_manager
/usr/bin/perl “-Iinc” -MExtUtils::MY -e ‘MY->fixin(shift)’ — blib/script/masterha_manager
Manifying blib/man1/masterha_stop.1
Manifying blib/man1/masterha_conf_host.1
Manifying blib/man1/masterha_check_repl.1
Manifying blib/man1/masterha_check_status.1
Manifying blib/man1/masterha_master_monitor.1
Manifying blib/man1/masterha_check_ssh.1
Manifying blib/man1/masterha_master_switch.1
Manifying blib/man1/masterha_secondary_check.1
Manifying blib/man1/masterha_manager.1
[root@56 mha4mysql-manager-0.56]# make install
Installing /usr/local/share/perl5/MHA/ManagerUtil.pm
Installing /usr/local/share/perl5/MHA/Config.pm
Installing /usr/local/share/perl5/MHA/HealthCheck.pm
Installing /usr/local/share/perl5/MHA/ManagerConst.pm
Installing /usr/local/share/perl5/MHA/ServerManager.pm
Installing /usr/local/share/perl5/MHA/ManagerAdmin.pm
Installing /usr/local/share/perl5/MHA/FileStatus.pm
Installing /usr/local/share/perl5/MHA/ManagerAdminWrapper.pm
Installing /usr/local/share/perl5/MHA/MasterFailover.pm
Installing /usr/local/share/perl5/MHA/MasterRotate.pm
Installing /usr/local/share/perl5/MHA/MasterMonitor.pm
Installing /usr/local/share/perl5/MHA/SSHCheck.pm
Installing /usr/local/share/perl5/MHA/Server.pm
Installing /usr/local/share/perl5/MHA/DBHelper.pm
Installing /usr/local/share/man/man1/masterha_stop.1
Installing /usr/local/share/man/man1/masterha_conf_host.1
Installing /usr/local/share/man/man1/masterha_check_repl.1
Installing /usr/local/share/man/man1/masterha_check_status.1
Installing /usr/local/share/man/man1/masterha_master_monitor.1
Installing /usr/local/share/man/man1/masterha_check_ssh.1
Installing /usr/local/share/man/man1/masterha_master_switch.1
Installing /usr/local/share/man/man1/masterha_secondary_check.1
Installing /usr/local/share/man/man1/masterha_manager.1
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
Appending installation info to /usr/lib64/perl5/perllocal.pod
[root@56 mha4mysql-manager-0.56]#
至此，所有软件包安装完毕。
第二步，配置ssh密钥对认证登陆
所有数据库服务器彼此之间互相以 root 用户 ssh 秘钥对认证登录
配置管理主机以 root 用户 ssh 秘钥对认证登录所有数据节点主机
# ssh-key-gen
# ssh-copy-id [email protected]
代码如下
[root@51 mysql]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:V4KUYduUrvcT9nAUrNm9oZ4OiL+67UrKFCzcNKjcZoQ root@51
The key’s randomart image is:
+—[RSA 2048]—-+
| +o.. . |
| . . o.=. o |
| E o o o.o .+ o |
|. = + . .oo o..|
| o * + S.. .. o|
| o . . o.o +… |
| . o o +.=. |
| o o o +o. |
| o +==. .o |
+—-[SHA256]—–+
[root@51 mysql]# for i in {1..6};do ssh-copy-id 192.168.4.5$i;done
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”
The authenticity of host ‘192.168.4.51 (192.168.4.51)’ can’t be established.
ECDSA key fingerprint is SHA256:59Byua15gv4TAVEm7YutMtmgXfxGTYbzSfO84kICu0E.
ECDSA key fingerprint is MD5:7e:6e:95:ed:84:fe:5b:8e:26:94:73:9b:c3:4b:90:cb.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed — if you are prompted now it is to install the new keys
[email protected]’s password:
Number of key(s) added: 1
（52-56省略）
安装 manager 软件包 后产生的命令
命令 作用
masterha_check_ssh 检查 MHA 的 SSH 配置状况
masterha_check_repl 检查 MySQL 复制状况
masterha_manger 启动 MHA
masterha_check_status 检测 MHA 运行状态
masterha_master_monitor 检测 master 是否宕机
第三步，配置MHA集群
#################################################################################
注意：再说一遍，在主库和备用主库都要设置repluser用户，且配置要相同
mysql> grant replication slave on *.* to repluser@”%” identified by “123456”;
Query OK, 0 rows affected, 1 warning (10.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)
然后，在所有的库，包括主库和从库都要设置一个监视用户admin，也要一模一样的配置
mysql> grant all on *.* to ‘root’@’%’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
##################################################################################
（1）、配置主从同步
51上开半同步复制，配置id,bin_log日志格式mixed
[root@51 mha-soft-student]# vim /etc/my.cnf
[root@51 mha-soft-student]# systemctl restart mysqld
[root@51 mha-soft-student]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
validate_password_policy = 0
validate_password_length = 6
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=master51
server_id=51
binlog_format=”mixed”
plugin-load=”rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
relay_log_purge=off
备注：解释如下
relay_log_purge=off 不自动删除本机的中继日志文件
异步复制( Asynchronous replication )
– 主库在执行完客户端提交的事务后会立即将结果返给客户端,并不关心从库是否已经接收并处理。
• 全同步复制( Fully synchronous replication )
– 当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。
• 半同步复制( Semisynchronous replication )
– 介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到 relay log 中才返回给客户端
• 查看是否允许动态加载模块
– 默认允许
mysql> show variables like “have _ dynamic _ loading”;
安装完插件后,半同步复制默认是关闭的
主: mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
从: mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
查看: mysql> show variables like “rpl_semi_sync_ %_enabled”;
以下语句分别是开启主的半同步和从的半同步
plugin-load=”rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
]# Vim /etc/my.cnf
[mysqld]
Server_id=id 号
log-bin= 日志名
Binlog_format=“mixed”
log_slave_updates
log-bin=master51 启用 binlog 日志
server_id=51 制定服务器的ID号
binlog_format=”mixed” 指定日志格式
添加主从同步授权用户
mysql> set global relay_log_purge=off;// 不自动删除本机的中继日志文件(在上面的配置文件中已经写了)
mysql> grant replication slave on *.* to repluser@”%” identified by”123456″;
代码如下
[root@51 mha-soft-student]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@”%” identified by “123456”;
52(备份master)上面开半同步复制，写server_id，指binlog日志名
[root@52 mha-soft-student]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
validate_password_policy = 0
validate_password_length = 6
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=52
log-bin=master52
binlog_format=”mixed”
plugin-load=”rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
relay_log_purge=off
将该库指向51master主库
代码如下
//
先在51上确认master信息
mysql> show master status;
+—————–+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————–+———-+————–+——————+——————-+
| master51.000003 | 441 | | | |
+—————–+———-+————–+——————+——————-+
1 row in set (0.00 sec)
//
[root@52 mha-soft-student]# mysql -uroot -p123456
mysql> change master to master_host=”192.168.4.51″,master_user=”repluser”,master_password=”123456″,master_log_file=”master51.000003″,master_log_pos=441；mysql> change master to master_host=”192.168.4.51″,master_user=”repluser”,master_password=”123456″,master_log_file=”master51.000003″,master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.32 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000003
Read_Master_Log_Pos: 441
Relay_Log_File: 52-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000003
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: 441
Relay_Log_Space: 523
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: 51
Master_UUID: 7db35892-b6ff-11e8-a87c-52540061c690
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
53(备份master)上面开半同步复制，写server_id，指binlog日志名
[root@53 mha-soft-student]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password_policy=0
validate_password_length=6
server_id=53
log_bin=master53
binlog_format=”mixed”
plugin-load=”rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
relay_log_purge=off
将该库指向51master主库
[root@53 mha-soft-student]# mysql -uroot -p123456
mysql> change master to master_host=”192.168.4.51″,master_user=”repluser”,master_password=”123456″,master_log_file=”master51.000003″,master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.59 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000003
Read_Master_Log_Pos: 441
Relay_Log_File: 53-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000003
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: 441
Relay_Log_Space: 523
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: 51
Master_UUID: 7db35892-b6ff-11e8-a87c-52540061c690
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
54(slave)指id号就行
[root@54 mha-soft-student]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password_policy=0
validate_password_length=6
server_id=54
将54从库指向51主库
[root@54 mha-soft-student]# mysql -uroot -p123456
mysql> change master to master_host=”192.168.4.51″,master_user=”repluser”,master_password=”123456″,master_log_file=”master51.000003″,master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.81 sec)
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000003
Read_Master_Log_Pos: 441
Relay_Log_File: 54-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000003
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: 441
Relay_Log_Space: 523
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: 51
Master_UUID: 7db35892-b6ff-11e8-a87c-52540061c690
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
55从库同54一样，指id即可
[root@55 mha-soft-student]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password_policy=0
validate_password_length=6
server_id=55
将55从库指向51主库
[root@55 mha-soft-student]# mysql -uroot -p123456
mysql> change master to master_host=”192.168.4.51″,master_user=”repluser”,master_password=”123456″,master_log_file=”master51.000003″,master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.50 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000003
Read_Master_Log_Pos: 441
Relay_Log_File: 55-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000003
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: 441
Relay_Log_Space: 523
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: 51
Master_UUID: 7db35892-b6ff-11e8-a87c-52540061c690
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
（2）配置管理主机
管理节点主机配置文件
]# cp mha4mysql-manager-0.56/bin/* /usr/local/bin/
]#mkdir /etc/mha_manager/
[root@host114 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha_manager/
]# vim /etc/mha_manager/app1.cnf
[server default]
manager_workdir=/etc/mha_manager
manager_log=/etc/mha_manager/manager.log
master_ip_failover_script=/usr/local/bin/master_ip_failover // 自动failover 时候的切换脚本
ssh_user=root
ssh_port=22
repl_user=repluser // 主从同步用户名
repl_password=123456 // 主从同步密码
user=root // 连接数据库服务器用户名
password=123456 // 密码配置管理主机 ( 续 1)
[server1]
hostname=192.168.4.51
port=3306
[server2]
hostname=192.168.4.52
port=3306
candidate_master=1 // 设置为候选 master
[server3]
hostname=192.168.4.53
port=3306
candidate_master=1 // 设置为候选 master配置管理主机 ( 续 2)
[server4]
hostname=192.168.4.54
port=3306
no_master=1 // 不竞选 master
[server5]
hostname=192.168.4.55
port=3306
no_master=1 // 不竞选 master
代码如下
默认manager安装时候已经把所有的配置文件拷贝到/bin目录下面了
[root@56 mha4mysql-manager-0.56]# pwd
/root/soft/mysql/mha-soft-student/mha4mysql-manager-0.56
[root@56 mha4mysql-manager-0.56]# cp bin/* /usr/local/bin/
cp：是否覆盖”/usr/local/bin/masterha_check_repl”？ n
cp：是否覆盖”/usr/local/bin/masterha_check_ssh”？ n
cp：是否覆盖”/usr/local/bin/masterha_check_status”？ n
cp：是否覆盖”/usr/local/bin/masterha_conf_host”？ n
cp：是否覆盖”/usr/local/bin/masterha_manager”？ n
cp：是否覆盖”/usr/local/bin/masterha_master_monitor”？ n
cp：是否覆盖”/usr/local/bin/masterha_master_switch”？ n
cp：是否覆盖”/usr/local/bin/masterha_secondary_check”？ n
cp：是否覆盖”/usr/local/bin/masterha_stop”？ n
拷贝配置文件并编辑
[root@56 mha4mysql-manager-0.56]# mkdir /etc/mha_manager/
[root@56 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha_manager/
[root@56 mha4mysql-manager-0.56]# ll /etc/mha_manager/
总用量 4
-rw-r–r–. 1 root root 257 9月 13 14:51 app1.cnf
[root@56 mha4mysql-manager-0.56]# vim /etc/mha_manager/app1.cnf
[root@56 mha4mysql-manager-0.56]# grep -v “^#” /etc/mha_manager/app1.cnf | grep -v “^$”
[server default]
manager_workdir=/etc/mha_manager
manager_log=/etc/mha_manager/manager.log
master_ip_failover_script=/usr/local/bin/master_ip_failover
ssh_user=root
ssh_port=22
repl_user=repluser
repl_password=123456
user=root
password=123456
[server1]
hostname=192.168.4.51
port=3306
[server2]
hostname=192.168.4.52
port=3306
candidate_master=1
[server3]
hostname=192.168.4.53
port=3306
candidate_master=1
[server4]
hostname=192.168.4.54
port=3306
no_master=1
[server5]
hostname=192.168.4.55
port=3306
no_master=1
根据配置文件，把脚本拷贝到相应的位置
[root@56 mysql]# ll
总用量 1137164
-rw-r–r–. 1 root root 3971 5月 22 11:01 master_ip_failover
-rw-r–r–. 1 root root 17333732 5月 22 11:01 maxscale-2.1.2-1.rhel.7.x86_64.rpm
drwxr-xr-x. 3 root root 4096 9月 13 11:28 mha-soft-student
-rw-r–r–. 1 root root 7663894 5月 22 10:56 Mycat-server-1.4-beta-20150604171601-linux.tar.gz
-rw-r–r–. 1 root root 569651200 5月 22 11:01 mysql-5.7.17.tar
-rw-r–r–. 1 root root 131072 5月 22 11:01 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
-rw-r–r–. 1 7155 31415 25037548 11月 30 2016 mysql-community-client-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 277812 11月 30 2016 mysql-community-common-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 3774276 11月 30 2016 mysql-community-devel-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 45474744 11月 30 2016 mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 23925668 11月 30 2016 mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 126047424 11月 30 2016 mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 2237116 11月 30 2016 mysql-community-libs-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 2112724 11月 30 2016 mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 54571580 11月 30 2016 mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 169496800 11月 30 2016 mysql-community-server-5.7.17-1.el7.x86_64.rpm
-rw-r–r–. 1 7155 31415 116682212 11月 30 2016 mysql-community-test-5.7.17-1.el7.x86_64.rpm
[root@56 mysql]# cp master_ip_failover /usr/local/bin/
[root@56 mysql]# chmod +x /usr/local/bin/master_ip_failover
[root@56 mysql]# ll /usr/local/bin/master_ip_failover
-rwxr-xr-x. 1 root root 3972 9月 13 15:02 /usr/local/bin/master_ip_failover
至此配置完成
第四步，测试MHA集群
#################################################################################
注意：再说一遍，在主库和备用主库都要设置repluser用户，且配置要相同
mysql> grant replication slave on *.* to repluser@”%” identified by “123456”;
Query OK, 0 rows affected, 1 warning (10.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)
然后，在所有的库，包括主库和从库都要设置一个监视用户admin，也要一模一样的配置
mysql> grant all on *.* to ‘root’@’%’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
##################################################################################
在管理节点上 通过 master_check_ssh 做 ssh 检查
]# /usr/local/bin/masterha_check_ssh –conf= 管理节点主机主配置文件
代码如下
[root@56 mysql]# /usr/local/bin/masterha_check_ssh –conf=/etc/mha_manager/app1.cnf
Thu Sep 13 15:08:00 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Sep 13 15:08:00 2018 – [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Thu Sep 13 15:08:00 2018 – [info] Reading server configuration from /etc/mha_manager/app1.cnf..
Thu Sep 13 15:08:00 2018 – [info] Starting SSH connection tests..
Thu Sep 13 15:08:04 2018 – [debug]
Thu Sep 13 15:08:01 2018 – [debug] Connecting via SSH from [email protected](192.168.4.52:22) to [email protected](192.168.4.51:22)..
Thu Sep 13 15:08:01 2018 – [debug] ok.
Thu Sep 13 15:08:01 2018 – [debug] Connecting via SSH from [email protected](192.168.4.52:22) to [email protected](192.168.4.53:22)..
Thu Sep 13 15:08:02 2018 – [debug] ok.
Thu Sep 13 15:08:02 2018 – [debug] Connecting via SSH from [email protected](192.168.4.52:22) to [email protected](192.168.4.54:22)..
Thu Sep 13 15:08:03 2018 – [debug] ok.
Thu Sep 13 15:08:03 2018 – [debug] Connecting via SSH from [email protected](192.168.4.52:22) to [email protected](192.168.4.55:22)..
Thu Sep 13 15:08:03 2018 – [debug] ok.
Thu Sep 13 15:08:04 2018 – [debug]
Thu Sep 13 15:08:00 2018 – [debug] Connecting via SSH from [email protected](192.168.4.51:22) to [email protected](192.168.4.52:22)..
Thu Sep 13 15:08:01 2018 – [debug] ok.
Thu Sep 13 15:08:01 2018 – [debug] Connecting via SSH from [email protected](192.168.4.51:22) to [email protected](192.168.4.53:22)..
Thu Sep 13 15:08:02 2018 – [debug] ok.
Thu Sep 13 15:08:02 2018 – [debug] Connecting via SSH from [email protected](192.168.4.51:22) to [email protected](192.168.4.54:22)..
Thu Sep 13 15:08:02 2018 – [debug] ok.
Thu Sep 13 15:08:02 2018 – [debug] Connecting via SSH from [email protected](192.168.4.51:22) to [email protected](192.168.4.55:22)..
Thu Sep 13 15:08:03 2018 – [debug] ok.
Thu Sep 13 15:08:05 2018 – [debug]
Thu Sep 13 15:08:01 2018 – [debug] Connecting via SSH from [email protected](192.168.4.53:22) to [email protected](192.168.4.51:22)..
Thu Sep 13 15:08:02 2018 – [debug] ok.
Thu Sep 13 15:08:02 2018 – [debug] Connecting via SSH from [email protected](192.168.4.53:22) to [email protected](192.168.4.52:22)..
Thu Sep 13 15:08:03 2018 – [debug] ok.
Thu Sep 13 15:08:03 2018 – [debug] Connecting via SSH from [email protected](192.168.4.53:22) to [email protected](192.168.4.54:22)..
Thu Sep 13 15:08:04 2018 – [debug] ok.
Thu Sep 13 15:08:04 2018 – [debug] Connecting via SSH from [email protected](192.168.4.53:22) to [email protected](192.168.4.55:22)..
Thu Sep 13 15:08:04 2018 – [debug] ok.
Thu Sep 13 15:08:05 2018 – [debug]
Thu Sep 13 15:08:02 2018 – [debug] Connecting via SSH from [email protected](192.168.4.54:22) to [email protected](192.168.4.51:22)..
Thu Sep 13 15:08:03 2018 – [debug] ok.
Thu Sep 13 15:08:03 2018 – [debug] Connecting via SSH from [email protected](192.168.4.54:22) to [email protected](192.168.4.52:22)..
Thu Sep 13 15:08:03 2018 – [debug] ok.
Thu Sep 13 15:08:03 2018 – [debug] Connecting via SSH from [email protected](192.168.4.54:22) to [email protected](192.168.4.53:22)..
Thu Sep 13 15:08:04 2018 – [debug] ok.
Thu Sep 13 15:08:04 2018 – [debug] Connecting via SSH from [email protected](192.168.4.54:22) to [email protected](192.168.4.55:22)..
Thu Sep 13 15:08:05 2018 – [debug] ok.
Thu Sep 13 15:08:06 2018 – [debug]
Thu Sep 13 15:08:02 2018 – [debug] Connecting via SSH from [email protected](192.168.4.55:22) to [email protected](192.168.4.51:22)..
Thu Sep 13 15:08:03 2018 – [debug] ok.
Thu Sep 13 15:08:03 2018 – [debug] Connecting via SSH from [email protected](192.168.4.55:22) to [email protected](192.168.4.52:22)..
Thu Sep 13 15:08:04 2018 – [debug] ok.
Thu Sep 13 15:08:04 2018 – [debug] Connecting via SSH from [email protected](192.168.4.55:22) to [email protected](192.168.4.53:22)..
Thu Sep 13 15:08:04 2018 – [debug] ok.
Thu Sep 13 15:08:04 2018 – [debug] Connecting via SSH from [email protected](192.168.4.55:22) to [email protected](192.168.4.54:22)..
Thu Sep 13 15:08:05 2018 – [debug] ok.
Thu Sep 13 15:08:06 2018 – [info] All SSH connection tests passed successfully.
测试主从同步状态
• 在管理节点上监控复制环境: 通过 masterha_check_repl 脚本查看整个集群的状态
]# /usr/local/bin/masterha_check_repl –conf= 管理节点主机主配置文件
#######################################################################
注意检测之前需要先把配置文件里面的脚本注释以下，避免检测出错
######################################################################
[root@56 mysql]# /usr/local/bin/masterha_check_repl –conf=/etc/mha_manager/app1.cnf
Thu Sep 13 15:40:41 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Sep 13 15:40:41 2018 – [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Thu Sep 13 15:40:41 2018 – [info] Reading server configuration from /etc/mha_manager/app1.cnf..
Thu Sep 13 15:40:41 2018 – [info] MHA::MasterMonitor version 0.56.
Thu Sep 13 15:40:42 2018 – [info] GTID failover mode = 0
Thu Sep 13 15:40:42 2018 – [info] Dead Servers:
Thu Sep 13 15:40:42 2018 – [info] Alive Servers:
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.51(192.168.4.51:3306)
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.52(192.168.4.52:3306)
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.53(192.168.4.53:3306)
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.54(192.168.4.54:3306)
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.55(192.168.4.55:3306)
Thu Sep 13 15:40:42 2018 – [info] Alive Slaves:
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.52(192.168.4.52:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Sep 13 15:40:42 2018 – [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Sep 13 15:40:42 2018 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.53(192.168.4.53:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Sep 13 15:40:42 2018 – [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Sep 13 15:40:42 2018 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.54(192.168.4.54:3306) Version=5.7.17 (oldest major version between slaves) log-bin:disabled
Thu Sep 13 15:40:42 2018 – [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Sep 13 15:40:42 2018 – [info] Not candidate for the new Master (no_master is set)
Thu Sep 13 15:40:42 2018 – [info] 192.168.4.55(192.168.4.55:3306) Version=5.7.17 (oldest major version between slaves) log-bin:disabled
Thu Sep 13 15:40:42 2018 – [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Sep 13 15:40:42 2018 – [info] Not candidate for the new Master (no_master is set)
Thu Sep 13 15:40:42 2018 – [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Thu Sep 13 15:40:42 2018 – [info] Checking slave configurations..
Thu Sep 13 15:40:42 2018 – [info] read_only=1 is not set on slave 192.168.4.52(192.168.4.52:3306).
Thu Sep 13 15:40:42 2018 – [info] read_only=1 is not set on slave 192.168.4.53(192.168.4.53:3306).
Thu Sep 13 15:40:42 2018 – [info] read_only=1 is not set on slave 192.168.4.54(192.168.4.54:3306).
Thu Sep 13 15:40:42 2018 – [warning] relay_log_purge=0 is not set on slave 192.168.4.54(192.168.4.54:3306).
Thu Sep 13 15:40:42 2018 – [warning] log-bin is not set on slave 192.168.4.54(192.168.4.54:3306). This host cannot be a master.
Thu Sep 13 15:40:42 2018 – [info] read_only=1 is not set on slave 192.168.4.55(192.168.4.55:3306).
Thu Sep 13 15:40:42 2018 – [warning] relay_log_purge=0 is not set on slave 192.168.4.55(192.168.4.55:3306).
Thu Sep 13 15:40:42 2018 – [warning] log-bin is not set on slave 192.168.4.55(192.168.4.55:3306). This host cannot be a master.
Thu Sep 13 15:40:42 2018 – [info] Checking replication filtering settings..
Thu Sep 13 15:40:42 2018 – [info] binlog_do_db= , binlog_ignore_db=
Thu Sep 13 15:40:42 2018 – [info] Replication filtering check ok.
Thu Sep 13 15:40:42 2018 – [info] GTID (with auto-pos) is not supported
Thu Sep 13 15:40:42 2018 – [info] Starting SSH connection tests..
Thu Sep 13 15:40:48 2018 – [info] All SSH connection tests passed successfully.
Thu Sep 13 15:40:48 2018 – [info] Checking MHA Node version..
Thu Sep 13 15:40:50 2018 – [info] Version check ok.
Thu Sep 13 15:40:50 2018 – [info] Checking SSH publickey authentication settings on the current master..
Thu Sep 13 15:40:50 2018 – [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Thu Sep 13 15:40:50 2018 – [info] Master MHA Node version is 0.56.
Thu Sep 13 15:40:50 2018 – [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)..
Thu Sep 13 15:40:50 2018 – [info] Executing command: save_binary_logs –command=test –start_pos=4 –binlog_dir=/var/lib/mysql,/var/log/mysql –output_file=/var/tmp/save_binary_logs_test –manager_version=0.56 –start_file=master51.000003
Thu Sep 13 15:40:50 2018 – [info] Connecting to [email protected](192.168.4.51:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to master51.000003
Thu Sep 13 15:40:51 2018 – [info] Binlog setting check done.
Thu Sep 13 15:40:51 2018 – [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Sep 13 15:40:51 2018 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user=’root’ –slave_host=192.168.4.52 –slave_ip=192.168.4.52 –slave_port=3306 –workdir=/var/tmp –target_version=5.7.17-log –manager_version=0.56 –relay_log_info=/var/lib/mysql/relay-log.info –relay_dir=/var/lib/mysql/ –slave_pass=xxx
Thu Sep 13 15:40:51 2018 – [info] Connecting to [email protected](192.168.4.52:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to 52-relay-bin.000004
Temporary relay log file is /var/lib/mysql/52-relay-bin.000004
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Sep 13 15:40:52 2018 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user=’root’ –slave_host=192.168.4.53 –slave_ip=192.168.4.53 –slave_port=3306 –workdir=/var/tmp –target_version=5.7.17-log –manager_version=0.56 –relay_log_info=/var/lib/mysql/relay-log.info –relay_dir=/var/lib/mysql/ –slave_pass=xxx
Thu Sep 13 15:40:52 2018 – [info] Connecting to [email protected](192.168.4.53:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to 53-relay-bin.000002
Temporary relay log file is /var/lib/mysql/53-relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Sep 13 15:40:53 2018 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user=’root’ –slave_host=192.168.4.54 –slave_ip=192.168.4.54 –slave_port=3306 –workdir=/var/tmp –target_version=5.7.17 –manager_version=0.56 –relay_log_info=/var/lib/mysql/relay-log.info –relay_dir=/var/lib/mysql/ –slave_pass=xxx
Thu Sep 13 15:40:53 2018 – [info] Connecting to [email protected](192.168.4.54:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to 54-relay-bin.000002
Temporary relay log file is /var/lib/mysql/54-relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Sep 13 15:40:55 2018 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user=’root’ –slave_host=192.168.4.55 –slave_ip=192.168.4.55 –slave_port=3306 –workdir=/var/tmp –target_version=5.7.17 –manager_version=0.56 –relay_log_info=/var/lib/mysql/relay-log.info –relay_dir=/var/lib/mysql/ –slave_pass=xxx
Thu Sep 13 15:40:55 2018 – [info] Connecting to [email protected](192.168.4.55:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info … ok.
Relay log found at /var/lib/mysql, up to 55-relay-bin.000002
Temporary relay log file is /var/lib/mysql/55-relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Sep 13 15:40:57 2018 – [info] Slaves settings check done.
Thu Sep 13 15:40:57 2018 – [info]
192.168.4.51(192.168.4.51:3306) (current master)
+–192.168.4.52(192.168.4.52:3306)
+–192.168.4.53(192.168.4.53:3306)
+–192.168.4.54(192.168.4.54:3306)
+–192.168.4.55(192.168.4.55:3306)
Thu Sep 13 15:40:57 2018 – [info] Checking replication health on 192.168.4.52..
Thu Sep 13 15:40:57 2018 – [info] ok.
Thu Sep 13 15:40:57 2018 – [info] Checking replication health on 192.168.4.53..
Thu Sep 13 15:40:57 2018 – [info] ok.
Thu Sep 13 15:40:57 2018 – [info] Checking replication health on 192.168.4.54..
Thu Sep 13 15:40:57 2018 – [info] ok.
Thu Sep 13 15:40:57 2018 – [info] Checking replication health on 192.168.4.55..
Thu Sep 13 15:40:57 2018 – [info] ok.
Thu Sep 13 15:40:57 2018 – [warning] master_ip_failover_script is not defined.
Thu Sep 13 15:40:57 2018 – [warning] shutdown_script is not defined.
Thu Sep 13 15:40:57 2018 – [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
第五步 启动并测试一下
开启 MHA Manager 监控
– masterha_manager // 启动命令
– –remove_dead_master_conf // 不在 app1.cnf 文件
里删除宕机的主库的信息
– –ignore_last_failover // 忽略 .health 文件
masterha_manager–conf=配置文件地址 –remove_dead_master_conf –ignore_last_failover
代码如下
[root@56 mysql]# masterha_manager –conf=/etc/mha_manager/app1.cnf –remove_dead_master_conf –ignore_last_failover
Thu Sep 13 15:53:48 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Sep 13 15:53:48 2018 – [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Thu Sep 13 15:53:48 2018 – [info] Reading server configuration from /etc/mha_manager/app1.cnf..
查看状态 : masterha_check_status
检查 mha 服务状态:
（重新开一个终端测试）
[root@56 ~]# masterha_check_status –conf=/etc/mha_manager/app1.cnf
app1 (pid:6153) is running(0:PING_OK), master:192.168.4.51
• 停止服务 : masterha_stop
停止 mha 服务
[root@56 ~]# masterha_stop –conf=/etc/mha_manager/app1.cnf
Stopped app1 successfully.
测试高可用
修改故障切换脚本
在脚本里添加如下行
（脚本在sample里面有）
/root/soft/mysql/mha-soft-student/mha4mysql-manager-0.56/samples/scripts
[root@56 scripts]# ll
总用量 32
-rwxr-xr-x. 1 4984 users 3648 9月 13 15:00 master_ip_failover
-rwxr-xr-x. 1 4984 users 9870 9月 13 15:00 master_ip_online_change
-rwxr-xr-x. 1 4984 users 11867 4月 1 2014 power_manager
-rwxr-xr-x. 1 4984 users 1360 4月 1 2014 send_report
[root@mangle ~]# vim /usr/local/bin/master_ip_failover
$new_master_password
);
my $vip = ‘192.168.1.100/24’; # Virtual IP
my $key = “1”;
my $ssh_start_vip = “/sbin/ifconfig eth0:$key $vip”;
my $ssh_stop_vip = “/sbin/ifconfig eth0:$key down”;
GetOptions(
当主库服务器宕机后,在备用 1 主库数据库服务器上查看 VIP 地址
首先看下目前的主库时哪个，可以看到是51
[root@56 mha_manager]# masterha_check_status –conf=/etc/mha_manager/app1.cnf
app1 master is down and failover is running(50:FAILOVER_RUNNING). master:192.168.4.51
Check /etc/mha_manager/manager.log for details.
然后到51去把主库DOWN掉，模拟宕机
[root@51 mha-soft-student]# systemctl stop mysqld
再检查一下状态可以发现，已经实现自动切换到虚拟IP（我实验的时候设置的是66）
[root@56 mha_manager]# masterha_check_status –conf=/etc/mha_manager/app1.cnf
app1 master is down and failover is running(50:FAILOVER_RUNNING). master:192.168.4.51
Check /etc/mha_manager/manager.log for details.
[root@56 mha_manager]# masterha_check_status –conf=/etc/mha_manager/app1.cnf
app1 is stopped(2:NOT_RUNNING).
可以去查一下切换的日志，可以看到已经自动切换到了52
—– Failover Report —–
app1: MySQL Master failover 192.168.4.51(192.168.4.51:3306) to 192.168.4.52(192.168.4.52:3306) succeeded
Master 192.168.4.51(192.168.4.51:3306) is down!
Check MHA Manager logs at 56:/etc/mha_manager/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.4.51(192.168.4.51:3306)
The latest slave 192.168.4.52(192.168.4.52:3306) has all relay logs for recovery.
Selected 192.168.4.52(192.168.4.52:3306) as a new master.
192.168.4.52(192.168.4.52:3306): OK: Applying all logs succeeded.
192.168.4.52(192.168.4.52:3306): OK: Activated master IP address.
192.168.4.55(192.168.4.55:3306): This host has the latest relay log events.
192.168.4.54(192.168.4.54:3306): This host has the latest relay log events.
192.168.4.53(192.168.4.53:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.4.54(192.168.4.54:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.4.52(192.168.4.52:3306)
192.168.4.53(192.168.4.53:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.4.52(192.168.4.52:3306)
192.168.4.55(192.168.4.55:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.4.52(192.168.4.52:3306)
192.168.4.52(192.168.4.52:3306): Resetting slave info succeeded.
Master failover to 192.168.4.52(192.168.4.52:3306) completed successfully.
到52上去看一下，可以发现已经生成了一个虚拟IP 66（我脚本里面写的是66）
[root@52 ~]# ip a s
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
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 pfifo_fast state UP qlen 1000
link/ether 52:54:00:7a:a2:ad brd ff:ff:ff:ff:ff:ff
inet 192.168.4.52/24 brd 192.168.4.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.4.66/24 brd 192.168.4.255 scope global secondary eth0:1
valid_lft forever preferred_lft forever
inet6 fe80::c372:3251:9b6b:9111/64 scope link
valid_lft forever preferred_lft forever
查看一下MHA的配置文件，可以发现[server1]已经被删除了，即没有server1的信息了
[root@56 mha_manager]# grep -v “^#” /etc/mha_manager/app1.cnf | grep -v “^$”
[server default]
manager_log=/etc/mha_manager/manager.log
manager_workdir=/etc/mha_manager
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=123456
repl_password=123456
repl_user=repluser
ssh_port=22
ssh_user=root
user=root
[server2]
candidate_master=1
hostname=192.168.4.52
port=3306
[server3]
candidate_master=1
hostname=192.168.4.53
port=3306
[server4]
hostname=192.168.4.54
no_master=1
port=3306
[server5]
hostname=192.168.4.55
no_master=1
port=3306
找个客户端登陆这个虚拟IP192.168.4.66，是可以正常使用的
[root@room9pc52 ~]# mysql -h 192.168.4.66 -u root -p123456
mysql: [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 41
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
说明：
从上面的输出可以看出整个MHA的切换过程，共包括以下的步骤：
1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作（这个我这里还没有实现，需要研究）
3.复制dead maste和最新slave相差的relay log，并保存到MHA Manger具体的目录下
4.识别含有最新更新的slave
5.应用从master保存的二进制日志事件（binlog events）
6.提升一个slave为新的master进行复制
7.使其他的slave连接新的master进行复制
最后启动MHA Manger监控，查看集群里面现在谁是master（在切换后监控就停止了。。。还有东西没搞对？）后来在官方网站看到这句话就明白了 。
Running MHA Manager from daemontools
Currently MHA Manager process does not run as a daemon. If failover completed successfully or the master process was killed by accident, the manager stops working. To run as a daemon, daemontool. or any external daemon program can be used. Here is an example to run from daemontools.