Mysql+MHA高可用配置

最新推荐文章于 2024-08-06 17:23:20 发布
最新推荐文章于 2024-08-06 17:23:20 发布
阅读量539 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： 企业级运维方案
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_41789003/article/details/81007819

配置环境：

版本：redhat6.5
master：server1（172.25.254.1）
Candicate slave：server2（172.25.254.2）
slave：server3（172.25.254.3）
manager：server4（172.25.254.4）

一、搭建主从复制

1、master机搭建

[root@server1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
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> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      710 |              |                  | f41fd867-8519-11e8-a35f-52540099c29d:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> grant replication slave on *.* to wuyanzu@'172.25.254.%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |     1007 |              |                  | f41fd867-8519-11e8-a35f-52540099c29d:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

2、slave机搭建（server2同server3）

[root@server2 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
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> change master to master_host='172.25.254.1',master_user='wuyanzu',master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.68 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.1
                  Master_User: wuyanzu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1007
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 1220
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

二、安装MHA软件

1、3个节点安装

mha4mysql-node-0.56-0.el6.noarch.rpm
server1，server2和server3都安装

[root@server1 mha]# rpm -qa | grep mha4   ##安装的mha node软件
mha4mysql-node-0.56-0.el6.noarch
[root@server1 mha]# rpm -qa | grep perl   ##需要的依赖包
perl-DBD-MySQL-4.013-3.el6.x86_64
perl-Pod-Escapes-1.04-136.el6.x86_64
perl-Pod-Simple-3.13-136.el6.x86_64
perl-Module-Pluggable-3.90-136.el6.x86_64
perl-DBI-1.609-4.el6.x86_64
perl-Error-0.17015-4.el6.noarch
perl-Git-1.7.1-3.el6_4.1.noarch
perl-libs-5.10.1-136.el6.x86_64
perl-version-0.77-136.el6.x86_64
perl-5.10.1-136.el6.x86_64

2、管理节点安装manager

[root@server4 ~]# rpm -qa | grep mha
mha4mysql-manager-0.56-0.el6.noarch
[root@server4 ~]# rpm -qa | grep perl
perl-DBI-1.609-4.el6.x86_64
perl-Digest-SHA-5.47-136.el6.x86_64
perl-ExtUtils-ParseXS-2.2003.0-136.el6.x86_64
perl-devel-5.10.1-136.el6.x86_64
perl-Email-Date-Format-1.002-5.el6.noarch
perl-TimeDate-1.16-11.1.el6.noarch
perl-MIME-Lite-3.027-2.el6.noarch
perl-Parallel-ForkManager-0.7.9-1.el6.noarch
perl-Log-Dispatch-2.27-1.el6.noarch
perl-Pod-Escapes-1.04-136.el6.x86_64
perl-Pod-Simple-3.13-136.el6.x86_64
perl-Module-Pluggable-3.90-136.el6.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
perl-Test-Harness-3.17-136.el6.x86_64
perl-ExtUtils-MakeMaker-6.55-136.el6.x86_64
perl-CPAN-1.9402-136.el6.x86_64
perl-MIME-Types-1.28-2.el6.noarch
perl-Config-Tiny-2.12-7.1.el6.noarch
perl-MailTools-2.04-4.el6.noarch
perl-Mail-Sender-0.8.16-3.el6.noarch
perl-Params-Validate-0.92-3.el6.x86_64
perl-Mail-Sendmail-0.79-12.el6.noarch
perl-Time-HiRes-1.9721-136.el6.x86_64
perl-libs-5.10.1-136.el6.x86_64
perl-version-0.77-136.el6.x86_64
perl-5.10.1-136.el6.x86_64

3、免密配置

1、方法1

[root@server4 ~]# ssh-keygen 
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
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:
11:bd:98:f5:22:27:33:a0:df:46:85:de:cd:7d:1c:b4 root@server4
The key's randomart image is:
+--[ RSA 2048]----+
|        .o    .. |
|      . ..+    ..|
|     . o.* = . E.|
|    .   X.= + . o|
|     . oS* .   . |
|      . o        |
|       .         |
|                 |
|                 |
+-----------------+
[root@server4 ~]# cd /root/.ssh/
[root@server4 .ssh]# yum install -y rsync
[root@server4 .ssh]# ssh-copy-id server4
The authenticity of host 'server4 (172.25.254.4)' can't be established.
RSA key fingerprint is 57:9d:a3:b0:00:cb:7e:c0:8a:a5:75:55:de:53:19:7b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server4,172.25.254.4' (RSA) to the list of known hosts.
root@server4's password: 
Now try logging into the machine, with "ssh 'server4'", and check in:

  .ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

[root@server4 .ssh]# rsync -p * server1:/root/.ssh/
root@server1's password: 
[root@server4 .ssh]# rsync -p * server2:/root/.ssh/
root@server2's password: 
[root@server4 .ssh]# rsync -p * server3:/root/.ssh/
root@server3's password: 
[root@server4 .ssh]# ssh server1
[root@server1 ~]# logout
Connection to server1 closed.
[root@server4 .ssh]# ssh server2
Last login: Wed Jul 11 23:34:42 2018 from server1
[root@server2 ~]# logout
Connection to server2 closed.
[root@server4 .ssh]# ssh server3
Last login: Wed Jul 11 23:35:15 2018 from server4
[root@server3 ~]# logout
Connection to server3 closed.

其他三台主机也ssh测试连接是否免密
方法2：
四台机子都需要配置比较麻烦

[root@server1 ~]# ssh-keygen -t rsa ##Enter 即可,选择默认方式
[root@server1 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.254.2
[root@server1 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.254.3
[root@server1 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.25.254.4

三、mha配置

1、manage节点配置

[root@server4 masterha]# pwd
/etc/masterha    ##此目录为mkdir目录
[root@server4 masterha]# vim app.cnf

[server default]
manager_log=/etc/masterha/mha.log
manager_workdir=/etc/masterha/
master_binlog_dir=/var/lib/mysql
#master_ip_online_change_script=/etc/masterha/master_ip_online_change
password=westos
ping_interval=1
remote_workdir=/tmp
repl_password=westos
repl_user=wuyanzu
ssh_user=root
user=root

[server1]
hostname=172.25.254.1
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=172.25.254.2
port=3306

[server3]
hostname=172.25.254.3
port=3306

2、slave节点配置

server2和server3配置relay log的清除方式和slave配置只读，但不要写入配置文件，因为master机down掉后可能随时会升级成master,

[root@server2 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
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> set global relay_log_purge=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global read_only=on;
Query OK, 0 rows affected (0.00 sec)

3、检测ssh配置

[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Wed Jul 11 23:55:26 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jul 11 23:55:26 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Wed Jul 11 23:55:26 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Wed Jul 11 23:55:26 2018 - [info] Starting SSH connection tests..
Wed Jul 11 23:55:26 2018 - [debug] 
Wed Jul 11 23:55:26 2018 - [debug]  Connecting via SSH from root@172.25.254.1(172.25.254.1:22) to root@172.25.254.2(172.25.254.2:22)..
Wed Jul 11 23:55:26 2018 - [debug]   ok.
Wed Jul 11 23:55:26 2018 - [debug]  Connecting via SSH from root@172.25.254.1(172.25.254.1:22) to root@172.25.254.3(172.25.254.3:22)..
Wed Jul 11 23:55:26 2018 - [debug]   ok.
Wed Jul 11 23:55:27 2018 - [debug] 
Wed Jul 11 23:55:26 2018 - [debug]  Connecting via SSH from root@172.25.254.2(172.25.254.2:22) to root@172.25.254.1(172.25.254.1:22)..
Wed Jul 11 23:55:26 2018 - [debug]   ok.
Wed Jul 11 23:55:26 2018 - [debug]  Connecting via SSH from root@172.25.254.2(172.25.254.2:22) to root@172.25.254.3(172.25.254.3:22)..
Wed Jul 11 23:55:27 2018 - [debug]   ok.
Wed Jul 11 23:55:27 2018 - [debug] 
Wed Jul 11 23:55:27 2018 - [debug]  Connecting via SSH from root@172.25.254.3(172.25.254.3:22) to root@172.25.254.1(172.25.254.1:22)..
Wed Jul 11 23:55:27 2018 - [debug]   ok.
Wed Jul 11 23:55:27 2018 - [debug]  Connecting via SSH from root@172.25.254.3(172.25.254.3:22) to root@172.25.254.2(172.25.254.2:22)..
Wed Jul 11 23:55:27 2018 - [debug]   ok.
Wed Jul 11 23:55:27 2018 - [info] All SSH connection tests passed successfully.

4、检测repl环境

注意报错

[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf 
Wed Jul 11 23:56:50 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jul 11 23:56:50 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Wed Jul 11 23:56:50 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Wed Jul 11 23:56:50 2018 - [info] MHA::MasterMonitor version 0.56.
Wed Jul 11 23:56:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.254.2(172.25.254.2:3306) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Wed Jul 11 23:56:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.254.3(172.25.254.3:3306o_master=1) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Wed Jul 11 23:56:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.254.1(172.25.254.1:3306) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Wed Jul 11 23:56:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Wed Jul 11 23:56:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 326
Wed Jul 11 23:56:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Wed Jul 11 23:56:50 2018 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!
[root@server4 ~]# vim /etc/masterha/app.
[root@server4 ~]# vim /etc/masterha/app.cnf 
[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf 
Thu Jul 12 00:00:50 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jul 12 00:00:50 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:00:50 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:00:50 2018 - [info] MHA::MasterMonitor version 0.56.
Thu Jul 12 00:00:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.254.3(172.25.254.3:3306o_master=1) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Thu Jul 12 00:00:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.254.2(172.25.254.2:3306) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Thu Jul 12 00:00:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.254.1(172.25.254.1:3306) :1045:Access denied for user 'root'@'server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Thu Jul 12 00:00:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Thu Jul 12 00:00:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 326
Thu Jul 12 00:00:50 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Thu Jul 12 00:00:50 2018 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

解决方法:master机给与监控权限

[root@server1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
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> grant all on *.* to root@'172.25.254.%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.13 sec)

继续查看

[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf 
Thu Jul 12 00:04:51 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jul 12 00:04:51 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:04:51 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:04:51 2018 - [info] MHA::MasterMonitor version 0.56.
Thu Jul 12 00:04:51 2018 - [info] GTID failover mode = 1
Thu Jul 12 00:04:51 2018 - [info] Dead Servers:
Thu Jul 12 00:04:51 2018 - [info] Alive Servers:
Thu Jul 12 00:04:51 2018 - [info]   172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:04:51 2018 - [info]   172.25.254.2(172.25.254.2:3306)
Thu Jul 12 00:04:51 2018 - [info]   172.25.254.3(172.25.254.3:3306o_master=1)
Thu Jul 12 00:04:51 2018 - [info] Alive Slaves:
Thu Jul 12 00:04:51 2018 - [info]   172.25.254.2(172.25.254.2:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:04:51 2018 - [info]     GTID ON
Thu Jul 12 00:04:51 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:04:51 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Jul 12 00:04:51 2018 - [info]   172.25.254.3(172.25.254.3:3306o_master=1)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:04:51 2018 - [info]     GTID ON
Thu Jul 12 00:04:51 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:04:51 2018 - [info] Current Alive Master: 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:04:51 2018 - [info] Checking slave configurations..
Thu Jul 12 00:04:51 2018 - [info] Checking replication filtering settings..
Thu Jul 12 00:04:51 2018 - [info]  binlog_do_db= , binlog_ignore_db= 
Thu Jul 12 00:04:51 2018 - [info]  Replication filtering check ok.
Thu Jul 12 00:04:51 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu Jul 12 00:04:51 2018 - [info] Checking SSH publickey authentication settings on the current master..
Thu Jul 12 00:04:51 2018 - [info] HealthCheck: SSH to 172.25.254.1 is reachable.
Thu Jul 12 00:04:51 2018 - [info] 
172.25.254.1(172.25.254.1:3306) (current master)
 +--172.25.254.2(172.25.254.2:3306)
 +--172.25.254.3(172.25.254.3:3306o_master=1)

Thu Jul 12 00:04:51 2018 - [info] Checking replication health on 172.25.254.2..
Thu Jul 12 00:04:51 2018 - [info]  ok.
Thu Jul 12 00:04:51 2018 - [info] Checking replication health on 172.25.254.3..
Thu Jul 12 00:04:51 2018 - [info]  ok.
Thu Jul 12 00:04:51 2018 - [warning] master_ip_failover_script is not defined.
Thu Jul 12 00:04:51 2018 - [warning] shutdown_script is not defined.
Thu Jul 12 00:04:51 2018 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

四、测试

1、manager机开启监控

[root@server4 ~]# nohup masterha_manager --conf=/etc/masterha/app.cnf &
[1] 1201
[root@server4 ~]# nohup: ignoring input and appending output to `nohup.out'

[root@server4 ~]#

2、测试

将master机的mysql down掉后

1527 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysq
 1772 pts/0    Sl     0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/m
 1969 pts/0    R+     0:00 ps ax
[root@server1 ~]# kill -9 1527
[root@server1 ~]# kill -9 1772

manager机会自动生成日志等文件

[root@server4 masterha]# ls
app.cnf  app.failover.complete  mha.log
[root@server4 masterha]# cat mha.log
----- Failover Report -----

app: MySQL Master failover 172.25.254.1(172.25.254.1:3306) to 172.25.254.2(172.25.254.2:3306) succeeded

Master 172.25.254.1(172.25.254.1:3306) is down!

Check MHA Manager logs at server4:/etc/masterha/mha.log for details.

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

日志文件中提示master已经由2接管
此时我们在server2和server3分别查看
server2

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| mysql-bin.000002 |      710 |              |                  | 00a648ff-851a-11e8-9159-525400d98192:1-3,
f41fd867-8519-11e8-a35f-52540099c29d:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

server3

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.2    ##master指向已经从原来的1指向了3
                  Master_User: wuyanzu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 710
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 923
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

将server的mysql开启，并将他手动设置为slave，指向新的master

Starting mysqld:                                           [  OK  ]
[root@server1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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> show slave status;
Empty set (0.00 sec)

mysql> change master to master_host='172.25.254.2',master_user='wuyanzu',master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.2
                  Master_User: wuyanzu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 710
               Relay_Log_File: server1-relay-bin.000002
                Relay_Log_Pos: 923
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

五、master的在线切换

1、将manager机上配置文件中的备用master指向注释掉

[server default]
manager_log=/etc/masterha/mha.log
manager_workdir=/etc/masterha/
master_binlog_dir=/var/lib/mysql
#master_ip_online_change_script=/etc/masterha/master_ip_online_change
password=westos
ping_interval=1
remote_workdir=/tmp
repl_password=westos
repl_user=wuyanzu
ssh_user=root
user=root

[server1]
hostname=172.25.254.1
port=3306

[server2]
#candidate_master=1
#check_repl_delay=0
hostname=172.25.254.2
port=3306

[server3]
hostname=172.25.254.3
port=3306

2、手动将master从server2切换到server1

[root@server4 masterha]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.254.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Thu Jul 12 00:31:38 2018 - [info] MHA::MasterRotate version 0.56.
Thu Jul 12 00:31:38 2018 - [info] Starting online master switch..
Thu Jul 12 00:31:38 2018 - [info] 
Thu Jul 12 00:31:38 2018 - [info] * Phase 1: Configuration Check Phase..
Thu Jul 12 00:31:38 2018 - [info] 
Thu Jul 12 00:31:38 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jul 12 00:31:38 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:31:38 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:31:38 2018 - [info] GTID failover mode = 1
Thu Jul 12 00:31:38 2018 - [info] Current Alive Master: 172.25.254.2(172.25.254.2:3306)
Thu Jul 12 00:31:38 2018 - [info] Alive Slaves:
Thu Jul 12 00:31:38 2018 - [info]   172.25.254.1(172.25.254.1:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:31:38 2018 - [info]     GTID ON
Thu Jul 12 00:31:38 2018 - [info]     Replicating from 172.25.254.2(172.25.254.2:3306)
Thu Jul 12 00:31:38 2018 - [info]   172.25.254.3(172.25.254.3:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:31:38 2018 - [info]     GTID ON
Thu Jul 12 00:31:38 2018 - [info]     Replicating from 172.25.254.2(172.25.254.2:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.254.2(172.25.254.2:3306)? (YES/no): yes
Thu Jul 12 00:31:40 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Thu Jul 12 00:31:40 2018 - [info]  ok.
Thu Jul 12 00:31:40 2018 - [info] Checking MHA is not monitoring or doing failover..
Thu Jul 12 00:31:40 2018 - [info] Checking replication health on 172.25.254.1..
Thu Jul 12 00:31:40 2018 - [info]  ok.
Thu Jul 12 00:31:40 2018 - [info] Checking replication health on 172.25.254.3..
Thu Jul 12 00:31:40 2018 - [info]  ok.
Thu Jul 12 00:31:40 2018 - [info] 172.25.254.1 can be new master.
Thu Jul 12 00:31:40 2018 - [info] 
From:
172.25.254.2(172.25.254.2:3306) (current master)
 +--172.25.254.1(172.25.254.1:3306)
 +--172.25.254.3(172.25.254.3:3306)

To:
172.25.254.1(172.25.254.1:3306) (new master)
 +--172.25.254.3(172.25.254.3:3306)
 +--172.25.254.2(172.25.254.2:3306)

Starting master switch from 172.25.254.2(172.25.254.2:3306) to 172.25.254.1(172.25.254.1:3306)? (yes/NO): yes
Thu Jul 12 00:31:48 2018 - [info] Checking whether 172.25.254.1(172.25.254.1:3306) is ok for the new master..
Thu Jul 12 00:31:48 2018 - [info]  ok.
Thu Jul 12 00:31:48 2018 - [info] 172.25.254.2(172.25.254.2:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Thu Jul 12 00:31:49 2018 - [info] 172.25.254.2(172.25.254.2:3306): Resetting slave pointing to the dummy host.
Thu Jul 12 00:31:49 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Jul 12 00:31:49 2018 - [info] 
Thu Jul 12 00:31:49 2018 - [info] * Phase 2: Rejecting updates Phase..
Thu Jul 12 00:31:49 2018 - [info] 
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Thu Jul 12 00:31:53 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Thu Jul 12 00:31:53 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
Thu Jul 12 00:31:53 2018 - [info]  ok.
Thu Jul 12 00:31:53 2018 - [info] Orig master binlog:pos is mysql-bin.000002:710.
Thu Jul 12 00:31:53 2018 - [info]  Waiting to execute all relay logs on 172.25.254.1(172.25.254.1:3306)..
Thu Jul 12 00:31:53 2018 - [info]  master_pos_wait(mysql-bin.000002:710) completed on 172.25.254.1(172.25.254.1:3306). Executed 0 events.
Thu Jul 12 00:31:53 2018 - [info]   done.
Thu Jul 12 00:31:53 2018 - [info] Getting new master's binlog name and position..
Thu Jul 12 00:31:53 2018 - [info]  mysql-bin.000003:194
Thu Jul 12 00:31:53 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.1', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='wuyanzu', MASTER_PASSWORD='xxx';
Thu Jul 12 00:31:53 2018 - [info] 
Thu Jul 12 00:31:53 2018 - [info] * Switching slaves in parallel..
Thu Jul 12 00:31:53 2018 - [info] 
Thu Jul 12 00:31:53 2018 - [info] -- Slave switch on host 172.25.254.3(172.25.254.3:3306) started, pid: 1380
Thu Jul 12 00:31:53 2018 - [info] 
Thu Jul 12 00:31:53 2018 - [info] Log messages from 172.25.254.3 ...
Thu Jul 12 00:31:53 2018 - [info] 
Thu Jul 12 00:31:53 2018 - [info]  Waiting to execute all relay logs on 172.25.254.3(172.25.254.3:3306)..
Thu Jul 12 00:31:53 2018 - [info]  master_pos_wait(mysql-bin.000002:710) completed on 172.25.254.3(172.25.254.3:3306). Executed 0 events.
Thu Jul 12 00:31:53 2018 - [info]   done.
Thu Jul 12 00:31:53 2018 - [info]  Resetting slave 172.25.254.3(172.25.254.3:3306) and starting replication from the new master 172.25.254.1(172.25.254.1:3306)..
Thu Jul 12 00:31:53 2018 - [info]  Executed CHANGE MASTER.
Thu Jul 12 00:31:53 2018 - [info]  Slave started.
Thu Jul 12 00:31:53 2018 - [info] End of log messages from 172.25.254.3 ...
Thu Jul 12 00:31:53 2018 - [info] 
Thu Jul 12 00:31:53 2018 - [info] -- Slave switch on host 172.25.254.3(172.25.254.3:3306) succeeded.
Thu Jul 12 00:31:53 2018 - [info] Unlocking all tables on the orig master:
Thu Jul 12 00:31:53 2018 - [info] Executing UNLOCK TABLES..
Thu Jul 12 00:31:53 2018 - [info]  ok.
Thu Jul 12 00:31:53 2018 - [info] Starting orig master as a new slave..
Thu Jul 12 00:31:53 2018 - [info]  Resetting slave 172.25.254.2(172.25.254.2:3306) and starting replication from the new master 172.25.254.1(172.25.254.1:3306)..
Thu Jul 12 00:31:54 2018 - [info]  Executed CHANGE MASTER.
Thu Jul 12 00:31:54 2018 - [info]  Slave started.
Thu Jul 12 00:31:54 2018 - [info] All new slave servers switched successfully.
Thu Jul 12 00:31:54 2018 - [info] 
Thu Jul 12 00:31:54 2018 - [info] * Phase 5: New master cleanup phase..
Thu Jul 12 00:31:54 2018 - [info] 
Thu Jul 12 00:31:54 2018 - [info]  172.25.254.1: Resetting slave info succeeded.
Thu Jul 12 00:31:54 2018 - [info] Switching master to 172.25.254.1(172.25.254.1:3306) completed successfully.

分别的server1，server2和server3查看
server1

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| mysql-bin.000003 |      194 |              |                  | 00a648ff-851a-11e8-9159-525400d98192:1-3,
f41fd867-8519-11e8-a35f-52540099c29d:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

server2

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.1
                  Master_User: wuyanzu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 194
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server3

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.1
                  Master_User: wuyanzu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 194
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

注意：手动切换操作并不会被记录在mha的日志文件中

[root@server4 masterha]# cat mha.log   ##查看日志并没有手动切换操作记录
----- Failover Report -----

app: MySQL Master failover 172.25.254.1(172.25.254.1:3306) to 172.25.254.2(172.25.254.2:3306) succeeded

Master 172.25.254.1(172.25.254.1:3306) is down!

Check MHA Manager logs at server4:/etc/masterha/mha.log for details.

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

六、master手动切换

配置文件同在线切换
若master down，没有 nohup masterha_manager，则需手动切换

 2004 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysq
 2253 pts/0    Sl     0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/m
 2300 pts/0    R+     0:00 ps ax
[root@server1 ~]# kill -9 2004
[root@server1 ~]# kill -9 2253

manger节点管理：
masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=dead --dead_master_host=172.25.254.1 -dead_master_port=3306 --new_master_host=172.25.254.2 --new_master_port=3306 --ignore_last_failover

[root@server4 masterha]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=dead --dead_master_host=172.25.254.1 -dead_master_port=3306 --new_master_host=172.25.254.2 --new_master_port=3306 --ignore_last_failover
--dead_master_ip=<dead_master_ip> is not set. Using 172.25.254.1.
Thu Jul 12 00:43:33 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jul 12 00:43:33 2018 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:43:33 2018 - [info] Reading server configuration from /etc/masterha/app.cnf..
Thu Jul 12 00:43:33 2018 - [info] MHA::MasterFailover version 0.56.
Thu Jul 12 00:43:33 2018 - [info] Starting master failover.
Thu Jul 12 00:43:33 2018 - [info] 
Thu Jul 12 00:43:33 2018 - [info] * Phase 1: Configuration Check Phase..
Thu Jul 12 00:43:33 2018 - [info] 
Thu Jul 12 00:43:33 2018 - [info] GTID failover mode = 1
Thu Jul 12 00:43:33 2018 - [info] Dead Servers:
Thu Jul 12 00:43:33 2018 - [info]   172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:43:33 2018 - [info] Checking master reachability via MySQL(double check)...
Thu Jul 12 00:43:33 2018 - [info]  ok.
Thu Jul 12 00:43:33 2018 - [info] Alive Servers:
Thu Jul 12 00:43:33 2018 - [info]   172.25.254.2(172.25.254.2:3306)
Thu Jul 12 00:43:33 2018 - [info]   172.25.254.3(172.25.254.3:3306)
Thu Jul 12 00:43:33 2018 - [info] Alive Slaves:
Thu Jul 12 00:43:33 2018 - [info]   172.25.254.2(172.25.254.2:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:43:33 2018 - [info]     GTID ON
Thu Jul 12 00:43:33 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:43:33 2018 - [info]   172.25.254.3(172.25.254.3:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:43:33 2018 - [info]     GTID ON
Thu Jul 12 00:43:33 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Master 172.25.254.1(172.25.254.1:3306) is dead. Proceed? (yes/NO): yes
Thu Jul 12 00:43:39 2018 - [info] Starting GTID based failover.
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] * Phase 2: Dead Master Shutdown Phase..
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] HealthCheck: SSH to 172.25.254.1 is reachable.
Thu Jul 12 00:43:39 2018 - [info] Forcing shutdown so that applications never connect to the current master..
Thu Jul 12 00:43:39 2018 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Thu Jul 12 00:43:39 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu Jul 12 00:43:39 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] * Phase 3: Master Recovery Phase..
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:194
Thu Jul 12 00:43:39 2018 - [info] Latest slaves (Slaves that received relay log files to the latest):
Thu Jul 12 00:43:39 2018 - [info]   172.25.254.2(172.25.254.2:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:43:39 2018 - [info]     GTID ON
Thu Jul 12 00:43:39 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:43:39 2018 - [info]   172.25.254.3(172.25.254.3:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:43:39 2018 - [info]     GTID ON
Thu Jul 12 00:43:39 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:43:39 2018 - [info] The oldest binary log file/position on all slaves is mysql-bin.000003:194
Thu Jul 12 00:43:39 2018 - [info] Oldest slaves:
Thu Jul 12 00:43:39 2018 - [info]   172.25.254.2(172.25.254.2:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:43:39 2018 - [info]     GTID ON
Thu Jul 12 00:43:39 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:43:39 2018 - [info]   172.25.254.3(172.25.254.3:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jul 12 00:43:39 2018 - [info]     GTID ON
Thu Jul 12 00:43:39 2018 - [info]     Replicating from 172.25.254.1(172.25.254.1:3306)
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] * Phase 3.3: Determining New Master Phase..
Thu Jul 12 00:43:39 2018 - [info] 
Thu Jul 12 00:43:39 2018 - [info] 172.25.254.2 can be new master.
Thu Jul 12 00:43:39 2018 - [info] New master is 172.25.254.2(172.25.254.2:3306)
Thu Jul 12 00:43:39 2018 - [info] Starting master failover..
Thu Jul 12 00:43:39 2018 - [info] 
From:
172.25.254.1(172.25.254.1:3306) (current master)
 +--172.25.254.2(172.25.254.2:3306)
 +--172.25.254.3(172.25.254.3:3306)

To:
172.25.254.2(172.25.254.2:3306) (new master)
 +--172.25.254.3(172.25.254.3:3306)

Starting master switch from 172.25.254.1(172.25.254.1:3306) to 172.25.254.2(172.25.254.2:3306)? (yes/NO): yes
Thu Jul 12 00:43:43 2018 - [info] New master decided manually is 172.25.254.2(172.25.254.2:3306)
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] * Phase 3.3: New Master Recovery Phase..
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info]  Waiting all logs to be applied.. 
Thu Jul 12 00:43:43 2018 - [info]   done.
Thu Jul 12 00:43:43 2018 - [info] Getting new master's binlog name and position..
Thu Jul 12 00:43:43 2018 - [info]  mysql-bin.000002:710
Thu Jul 12 00:43:43 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.2', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='wuyanzu', MASTER_PASSWORD='xxx';
Thu Jul 12 00:43:43 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000002, 710, 00a648ff-851a-11e8-9159-525400d98192:1-3,
f41fd867-8519-11e8-a35f-52540099c29d:1-5
Thu Jul 12 00:43:43 2018 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Thu Jul 12 00:43:43 2018 - [info] ** Finished master recovery successfully.
Thu Jul 12 00:43:43 2018 - [info] * Phase 3: Master Recovery Phase completed.
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] * Phase 4: Slaves Recovery Phase..
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] * Phase 4.1: Starting Slaves in parallel..
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] -- Slave recovery on host 172.25.254.3(172.25.254.3:3306) started, pid: 1394. Check tmp log /etc/masterha//172.25.254.3_3306_20180712004333.log if it takes time..
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] Log messages from 172.25.254.3 ...
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info]  Resetting slave 172.25.254.3(172.25.254.3:3306) and starting replication from the new master 172.25.254.2(172.25.254.2:3306)..
Thu Jul 12 00:43:43 2018 - [info]  Executed CHANGE MASTER.
Thu Jul 12 00:43:43 2018 - [info]  Slave started.
Thu Jul 12 00:43:43 2018 - [info]  gtid_wait(00a648ff-851a-11e8-9159-525400d98192:1-3,
f41fd867-8519-11e8-a35f-52540099c29d:1-5) completed on 172.25.254.3(172.25.254.3:3306). Executed 0 events.
Thu Jul 12 00:43:43 2018 - [info] End of log messages from 172.25.254.3.
Thu Jul 12 00:43:43 2018 - [info] -- Slave on host 172.25.254.3(172.25.254.3:3306) started.
Thu Jul 12 00:43:43 2018 - [info] All new slave servers recovered successfully.
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] * Phase 5: New master cleanup phase..
Thu Jul 12 00:43:43 2018 - [info] 
Thu Jul 12 00:43:43 2018 - [info] Resetting slave info on the new master..
Thu Jul 12 00:43:44 2018 - [info]  172.25.254.2: Resetting slave info succeeded.
Thu Jul 12 00:43:44 2018 - [info] Master failover to 172.25.254.2(172.25.254.2:3306) completed successfully.
Thu Jul 12 00:43:44 2018 - [info] 

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

app: MySQL Master failover 172.25.254.1(172.25.254.1:3306) to 172.25.254.2(172.25.254.2:3306) succeeded

Master 172.25.254.1(172.25.254.1:3306) is down!

Check MHA Manager logs at server4 for details.

Started manual(interactive) failover.
Selected 172.25.254.2(172.25.254.2:3306) as a new master.
172.25.254.2(172.25.254.2:3306): OK: Applying all logs succeeded.
172.25.254.3(172.25.254.3:3306): OK: Slave started, replicating from 172.25.254.2(172.25.254.2:3306)
172.25.254.2(172.25.254.2:3306): Resetting slave info succeeded.
Master failover to 172.25.254.2(172.25.254.2:3306) completed successfully.

server3查看:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.2
                  Master_User: wuyanzu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 710
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

七、vip配置

重新将server1的mysql打开，且设置为slave，此时server2为master，server1和server3为slave

1、在server2添加一个虚拟ip

[root@server2 mysql]# ip addr add 172.25.254.100/24 dev eth0
[root@server2 mysql]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:d9:81:92 brd ff:ff:ff:ff:ff:ff
    inet 172.25.254.2/24 brd 172.25.254.255 scope global eth0
    inet 172.25.254.100/24 scope global secondary eth0
    inet6 fe80::5054:ff:fed9:8192/64 scope link 
       valid_lft forever preferred_lft forever

2、manager节点配置

1、修改app.cnf文件

[server default]
manager_log=/etc/masterha/mha.log
manager_workdir=/etc/masterha/
master_binlog_dir=/var/lib/mysql
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=westos
ping_interval=1
remote_workdir=/tmp
repl_password=westos
repl_user=wuyanzu
ssh_user=root
user=root

[server1]
hostname=172.25.254.1
port=3306

[server2]
#candidate_master=1
#check_repl_delay=0
hostname=172.25.254.2
port=3306

[server3]
hostname=172.25.254.3
port=3306

2、在/usr/local/bin/编辑两个脚本文件

[root@server4 bin]# cd /usr/local/bin/
[root@server4 bin]# cat vim master_ip_failover 
cat: vim: No such file or directory
#!/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.25.254.100/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";

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 "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

    if ( $command eq "stop" || $command eq "stopssh" ) {

        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {

        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &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 \n";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
     return 0  unless  ($ssh_user);
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
[root@server4 bin]# cat master_ip_online_change 
#!/usr/bin/env perl
use strict;  
use warnings FATAL =>'all';  
  
use Getopt::Long;  
  
my $vip = '172.25.254.100/24';  # Virtual IP  
my $key = "1";  
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";  
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";  
my $exit_code = 0;  
  
my (  
  $command,              $orig_master_is_new_slave, $orig_master_host,  
  $orig_master_ip,       $orig_master_port,         $orig_master_user,  
  $orig_master_password, $orig_master_ssh_user,     $new_master_host,  
  $new_master_ip,        $new_master_port,          $new_master_user,  
  $new_master_password,  $new_master_ssh_user,  
);  
GetOptions(  
  'command=s'                => \$command,  
  'orig_master_is_new_slave' => \$orig_master_is_new_slave,  
  'orig_master_host=s'       => \$orig_master_host,  
  'orig_master_ip=s'         => \$orig_master_ip,  
  'orig_master_port=i'       => \$orig_master_port,  
  'orig_master_user=s'       => \$orig_master_user,  
  'orig_master_password=s'   => \$orig_master_password,  
  'orig_master_ssh_user=s'   => \$orig_master_ssh_user,  
  'new_master_host=s'        => \$new_master_host,  
  'new_master_ip=s'          => \$new_master_ip,  
  'new_master_port=i'        => \$new_master_port,  
  'new_master_user=s'        => \$new_master_user,  
  'new_master_password=s'    => \$new_master_password,  
  'new_master_ssh_user=s'    => \$new_master_ssh_user,  
);  
  
  
exit &main();  
  
sub main {  
  
#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";  
  
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 "\n\n\n***************************************************************\n";  
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";  
            print "***************************************************************\n\n\n\n";  
&stop_vip();  
            $exit_code = 0;  
        };  
        if ($@) {  
            warn "Got Error: $@\n";  
            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 "\n\n\n***************************************************************\n";  
            print "Enabling the VIP - $vip on new master: $new_master_host \n";  
            print "***************************************************************\n\n\n\n";  
&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 \n";  
        `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`;  
        exit 0;  
}  
else {  
&usage();  
        exit 1;  
}  
}  
  
# A simple system call that enable the VIP on the new master  
sub start_vip() {  
`ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;  
}  
# A simple system call that disable the VIP on the old_master  
sub stop_vip() {  
`ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;  
}  
  
sub usage {  
print  
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";  
}

给这两个脚本文件给到执行权限

[root@server4 bin]# ll master_ip_*
-rwxrwxrwx 1 root root 2173 Jul 12 01:00 master_ip_failover
-rwxrwxrwx 1 root root 3848 Jul 12 01:13 master_ip_online_change

测试：
在线将master切换到server1上，查看server1和server2的ip

[root@server5 bin]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.51.5 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

172.25.254.2(172.25.254.2:3306) (current master)
 +--172.25.254.1(172.25.254.1:3306)
 +--172.25.254.3(172.25.254.3:3306)

To:
172.25.254.1(172.25.254.1:3306) (new master)
 +--172.25.254.3(172.25.254.3:3306)
 +--172.25.254.2(172.25.254.2:3306)

***************************************************************
Disabling the VIP - 172.25.254.100/24 on old master: 172.25.254.2
***************************************************************
***************************************************************
Enabling the VIP - 172.25.254.100/24 on new master: 172.25.254.1 
***************************************************************

server2查看ip
vip已经没有了且server2变成了slave

[root@server2 mysql]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:d9:81:92 brd ff:ff:ff:ff:ff:ff
    inet 172.25.254.2/24 brd 172.25.254.255 scope global eth0
    inet6 fe80::5054:ff:fed9:8192/64 scope link 
       valid_lft forever preferred_lft forever
 mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.1
                  Master_User: wuyanzu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 194
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server1

[root@server1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:99:c2:9d brd ff:ff:ff:ff:ff:ff
    inet 172.25.254.1/24 brd 172.25.254.255 scope global eth0
    inet 172.25.254.100/24 scope global secondary eth0
    inet6 fe80::5054:ff:fe99:c29d/64 scope link 
       valid_lft forever preferred_lft forever
[root@server1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
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> show slave status\G;
Empty set (0.00 sec)

ERROR: 
No query specified
MySQL+MHA高可用服务集群搭建
fangxin_zonghuo的博客
05-10 294
文章目录一、MHA概念1、MHA 的组成2、MHA 的特点二、搭建MySQL+MHA1、所有服务器，关闭系统防火墙和安全机制2、修改 master（192.168.10.12）、Slave1（192.168.10.13）、Slave2（192.168.10.14） 节点的主机名 一、MHA概念 （1）MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 （2）MHA 的出现就是解决MySQL 单点的问题。 （3）MySQL故障切换过程中，MHA
mysql 5.7+ MHA+Atlas
02-27
mysql 5.7+ MHA+Atlas/
MySQL数据之-----MHA很高很高的高可用！！！！
DAHA2的博客
07-26 275
目录MHA概述MHA的简介特点搭建MHA配置主从配置MHA MHA概述 MHA的简介 MHA 目前在MySQL高可用方面是一个相对成熟的解决方案，MHA是由日本开发的，是一套优秀的MySQL故障切换和主从复制的高可用软件。在MySQL故障切换过程中，MHA能做到0·30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能够最大程度上保证数据库的一致性，以达到真正意义上高可用。 MHA是基于标准的MySQL主从复制 特点 自动故障切换过程中，MHA总会试图从宕机的主服务器上保存二进制日
MySQL的MHA
gcc001224的博客
06-30 3113
MHA解决了mysql数据库单点故障，提高了数据的安全性。MHA Node（数据节点） MHA Node 运行在每台 MySQL 服务器上。HA Manager（管理节点） MHA Manager 可以单独部署在一台独立的机器上，管理多个 master-slave 集群；也可以部署在一台 slave 节点上。 MHA Manager 会定时探测集群中的 master 节点。当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的 slave 重新指向新的
mysql+MHA部署
mrh666的博客
02-13 283
介绍MHA ​ MHA是一位日本MySQL大佬（youshimaton）用Perl写的一套MySQL故障切换方案，在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。 MHA有两部分组成​ node和Manager node MHA是基于MySQL 主从复制环境的，在该环境...
MySQL+MHA+Atlas（高可用+读写分离）
屈帅波的技术博客
07-30 1876
实验环境 master（sdb1） 192.168.74.230 Master备用（sdb2）192.168.74.231 （如果master没问题这个是从，一旦masterdown会切换为主） Slave （sdb3） 192.168.74.232 MHA在sdb3 实验环境切记关闭防火墙以及selinux或者设置规则（本次使用的系统Centos7） [root@sdb1...
mysql+lvs+keepalive+mha高可用
07-19
MySQL数据库在许多业务环境中是核心组件，为了保证其服务的连续性和稳定性，通常需要构建高可用...本教程的压缩包文件"MySQL+LVS+Keepalived+MHA-高可用群集"中应包含了详细的配置文件和指导，供读者深入学习和实践。
MySQL+MHA+Atlas高可用
10-27
MHA高可用配置、安装过程可能出现的问题以及解决方法，配置文件
(带目录版)MySQL LVS+Keepalived+MHA 高可用群集 应用部署操作手册
04-07
### MySQL LVS+Keepalived+MHA 高可用群集应用部署操作手册知识点解析 #### MHA 架构介绍与工作原理 - **MHA（Master High Availability）概述** - MHA 是一套用于MySQL高可用环境下的故障切换和主从提升方案，由...
数据库+mha+mha+数据库高可用
09-08
数据库高可用性是现代企业IT系统...总之，MHA是提高MySQL数据库高可用性的有力工具，通过合理的配置和维护，可以显著提升系统的稳定性和可靠性。在处理大规模数据服务时，了解并掌握MHA的使用对于IT专业人士至关重要。
lvs+keepalived+mha+mysql架构配置说明
08-30
lvs+keepalived+mha+mysql 架构配置说明 LVS（Linux Virtual Server）是一种开源的负载均衡...通过本文的配置说明，我们可以实现一个高可用的 LVS+Keepalived+MHA+MySQL 架构，确保 MySQL 服务的高可用性和稳定性。
MYSQL 之搭建MySQL+MHA
xiaobai316的博客
11-02 450
文章目录实验思路实验环境配置MySQL一主两从在Master节点查看二进制文件和同步点在 Slave1、Slave2 节点执行同步操作Slave1、Slave2 节点设置为只读模式主从复制验证slave查询库验证安装 MHA 软件在所有服务器上配置无密码认证在 manager 节点上配置 MHA 实验思路 1、MHA架构 数据库安装 一主两从 MHA搭建 2、故障模拟 模拟主库失效 备选主库成为主库 原故障主库恢复重新加入到MHA成为从库 实验环境 MHA manger 192.168.3.11 先安装mh
mysql实现MHA
最新发布
ibertine_P的博客
08-06 895
高可用模式下的故障切换，基于主从复制，单点故障和主从复制不能切换的问题，架构需要奇数台，至少需要3台，故障切换过程0-30秒，vip地址，根据vip地址所在的主机，确定主备。主和备不是优先级确定的，主从复制的时候就确定了主，备是在MHA的过程中确定。MHA NODE 数据节点，每台mysql和管理服务器都要安装 监控服务器状态以及收集数据MHA的manager管理节点管理mysql的高可用集群可以单独部署在一台独立的服务器，也可以部署多个实现主备之间切换，主发生故障，切换到备。1、manager来实现主备切
MySQL服务MySQL+MHA高可用服务集群搭建
IHBOS的博客
04-24 435
这里写目录标题MySQL MHAMHA概念MHA 的组成MHA 的特点搭建MySQL+MHA1、所有服务器，关闭系统防火墙和安全机制2、修改 master（192.168.172.10）、Slave1（192.168.172.20）、Slave2（192.168.172.30） 节点的主机名3、修改三台MySQL服务器的主配置文件/etc/my.cnf4、配置MySQL一主两从5、主从复制验证6、安装 MHA 软件在 MHA manager 节点上安装 manager 组件7、在所有服务器上配置无密码认证在
MySQL + MHA高可用 ：一主多从的好东西
dsd的博客
05-27 577
Table of Contents 1. MHA介绍 1.1 MHA软件组成 1.2 MHA工作原理 1.3 MHA工具 2.MySQL + MHA高可用 2.1 实验环境 2.2 配置主从复制 2.2.1 修改配置文件 2.2.2 安全初始化 2.3 测试 2.4 配置MHA 2.4.1安装mha-node(所有节点) 2.4.2 安装mha-manager(管理节点) 2.4.3 管理节点配置免密登陆 2.4.4 配置mha 2.5 测试 2.5.1 手动切换 ...
mysql中的mha简介_mha 简介
weixin_36354355的博客
02-18 650
什么是 MHAMHA(Master High Availability) 是自动的 master 故障转移和 slave 提升的软件包。它是基于标准的 MySQL 复制(异步/半同步).MHA 有两部分组成:MHA Manager(管理节点)和 MHA Node(数据节点)。MHA Manager 可以单独部署在一台独立机器上管理多个 master-slave 集群,也可以部署在一台 slave ...
Mysql+MHA高可用
qq_45103767的博客
05-19 352
MHA高可用(一主双从) 一 、准备三台服务器（Centos7.x) 10.0.0.41 10.0.0.42 10.0.0.43 二、在三台服务器上同步时间 [root@ localhost ~]# echo "*/5* * * * /usr/sbin/ntpdate ntp1.aliyun.com >/dev/null 2>&1" >>/var/spool/cron/root 修改主机名（主） [root@ localhost ~]# vim /etc/hostname
【MySql】mysql之MHA高可用配置及故障切换
种一颗树最好的时间是十年前，其次是现在！
08-19 757
① 作用：mysql的高可用 + 故障切换② 核心部分：MHA组件：主要的功能：做MHA 启动、关闭管理和检测mysql各种健康状态在发生故障时，尽可能的保存二进制日志，并且实现故障切换（VIP地址飘逸）③ MHA需要配置的文件（2个）:命令工具 ，定义的是基于VIP的检测和故障转移 （VIP从master----->新的 master）mha的主要配置文件，主要定义了mha的工作目录、日志。mysql二进制日志位置用mha的登录mysql的用户、密码使用从服务器。...
数据库mysql高可用架构及其MHA详解
zhangjikuan的专栏
05-14 3072
数据库高可用架构及其MHA的详细方案分享，不愿码字了，直接PPT传上了，主要参考MHA的wiki，关于mysql架构优缺点也是根据wiki介绍画成的图 MHA git wiki: https://github.com/yoshinorim/mha4mysql-manager/wiki
MySQL LVS+Keepalived+MHA高可用集群部署指南
通过这个部署操作手册，读者可以学习到如何设计和实施一个高可用的MySQL集群环境，包括如何配置LVS、Keepalived，以及如何利用MHA来管理和维护在高并发和故障情况下的数据服务。这对于IT专业人士来说是一个宝贵的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值