Mysql高级教程

[root@mysql-node10 mysql-5.7.44]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定安装路径
-DMYSQL_DATADIR=/data/mysql \ #指定数据目录
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ #指定套接字文件
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #指定启用INNODB存储引擎，默认
用myisam
-DWITH_EXTRA_CHARSETS=all \ #扩展字符集
-DDEFAULT_CHARSET=utf8mb4 \ #指定默认字符集
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \ #指定默认校验字符集
-DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/ #指定c++库依赖
[root@mysql-node10 mysql-5.7.44]# make -j2 #-j2 表示有几个
核心就跑几个进程
[root@mysql-node10 mysql-5.7.44# make install

#生成数据目录
[root@mysql-node1 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 ~]# mkdir -p /data/mysql
[root@mysql-node1 ~]# chown mysql.mysql /data/mysql/
#修改配置文件
[root@node10 my.cnf.d]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql #指定数据目录
socket=/data/mysql/mysql.sock #指定套接字
symbolic-links=0 #数据只能存放到数据目录中，禁止链接到数据目录
#数据库初始化建立mysql基本数据
[root@node10 ~]# mysqld --initialize --user=mysql
[root@node10 ~]# /etc/init.d/mysqld start
[root@node10 ~]# chkconfig mysqld on
#数据库安全初始化
[root@node10 ~]# mysql_secure_installation
Securing the MySQL server deployment.

[root@mysql2 mysql]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK #基于组提交
slave-parallel-workers=16 #开启线程数量
master_info_repository=TABLE #master信息在表中记录，默认记录在/data/mysql/master.info
relay_log_info_repository=TABLE #回放日志信息在表中记录，默认记录在/data/mysql/relay-log.info
relay_log_recovery=ON #日志回放恢复功能开启[root@mysql2 mysql]# /etc/init.d/mysqld restartmysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect |   19 | Slave has read all relay log; waiting for more updates | NULL             |
|  3 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
|  4 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
|  5 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 10 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 11 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |   19 | Waiting for an event from Coordinator                  | NULL             |
| 20 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
18 rows in set (0.00 sec)

3.半同步模式

半同步模式原理

1. 用户线程写入完成后 master 中的 dump 会把日志推送到 slave 端

2.slave 中的 io 线程接收后保存到 relaylog 中继日志

3. 保存完成后 slave 向 master 端返回 ack

4. 在未接受到 slave 的 ack 时 master 端时不做提交的，一直处于等待当收到 ack 后提交到存储引擎

5. 在 5.6 版本中用到的时 after_commit 模式， after_commit 模式时先提交在等待 ack 返回后输出 ok

gtid模式

当激活 GITD 之后

当 master 出现问题后， slave2 和 master 的数据最接近，会被作为新的 master

slave1 指向新的 master ，但是他不会去检测新的 master 的 pos id ，只需要继续读取自己 gtid_next 即可

设置 gtid

#在master端开启gtid模式
[root@mysql1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)#在slave端开启gtid模式
[root@mysql2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)mysql> change master to master_host='172.25.254.130',master_user='haha',master_password='haha',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.130Master_User: hahaMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 154Relay_Log_File: mysql2-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 575Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 84bc7655-68c7-11ef-aa09-000c29d16b0bMaster_Info_File: /data/mysql/master.infoSQL_Delay: 60SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 1 #功能开启Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:
1 row in set (0.00 sec)[root@mysql3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=3
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host='172.25.254.130',master_user='haha',master_password='haha',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql>  start slave;
Query OK, 0 rows affected (0.00 sec)mysql>  show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.130Master_User: hahaMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 154Relay_Log_File: mysql3-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 575Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 84bc7655-68c7-11ef-aa09-000c29d16b0bMaster_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 1 #功能开启Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:
1 row in set (0.00 sec)

启用半同步模式

在master端配置启用半同步模式

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装半同步插件
Query OK, 0 rows affected (0.00 sec)mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)mysql> set global rpl_semi_sync_master_enabled=1; #打开半同步功能
Query OK, 0 rows affected (0.00 sec)mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)mysql> show variables like 'rpl_semi_sync%'; #查看半同步功能状态
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)[root@mysql1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 #开启1半同步功能
symbolic-links=0[root@mysql1 ~]# /etc/init.d/mysqld restart

在slave端配置启用半同步模式

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)mysql>  show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)mysql>  set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)mysql> stop slave io_thread; #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread; ##重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)mysql>  show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)[root@mysql2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
symbolic-links=0[root@mysql2 ~]# /etc/init.d/mysqld restart#mysql3一模一样的配置

4.mysql高可用之组复制 (MGR)

MySQL Group Replication( 简称 MGR ) 是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩 展的解决方案

组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务

MySQL 组复制分单主模式和多主模式，传统的 mysql 复制技术仅解决了数据同步的问题

MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致

提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定

如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，

这是一种内置的自动裂脑保护机制

MGR 由组通信系统 ( Group Communication System ， GCS ) 协议支持,该系统提供故障检测机制、组成员服务以及安全且有序的消息传递

组复制流程

首先我们将多个节点共同组成一个复制组，在执行读写（ RW ）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里 “ 大多数人 ” （对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于(N/2+1) ，这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO ）事务则不需要经过组内同意，直接提交即可。

5.mysql-router（mysql路由）

• 是一个对应用程序透明的InnoDB Cluster连接路由服务，提供负载均衡、应用连接故障转移和客户端路由。
• 利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略来处理连接，使其连接到正确的MySQL数据库服务器

##安装mysql-router
[root@mysql1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm

#配置mysql-router
[root@mysql1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations =172.25.254.150:3306,172.25.254.160:3306
routing_strategy = round-robin[root@mysql1 ~]# systemctl start mysqlrouter.service

测试：

#端口已开启
[root@mysql1 ~]# netstat -antlupe | grep 7001
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      997        24641      2136/mysqlrouter
tcp        0      0 172.25.254.130:52704    172.25.254.130:7001     TIME_WAIT   0          0          -#注意：测试之前，确保mysql-router上的mysql关闭，其余两台mysql服务器开启，并且两台mysql服务器上有允许远程登录的用户[root@mysql1 ~]# mysql -uhehe -phehe -h 172.25.254.130 -P 7001
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 2
Server version: 5.7.44-log Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.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> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)mysql> \q
Bye
[root@mysql1 ~]# mysql -uhehe -phehe -h 172.25.254.130 -P 7001
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 4
Server version: 5.7.44-log Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.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> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.01 sec)

6.mysql高可用之MHA

#在master节点上
[root@mysql1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@mysql1 ~]# rm -rf /data/mysql/*
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0[root@mysql1 ~]# mysqld --user mysql --initialize
[root@mysql1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql1.org.err'.SUCCESS!
[root@mysql1 ~]# mysql_secure_installation[root@mysql1 ~]#mysql -urootmysql> create user 'test'@'%' identified by 'test';
Query OK, 0 rows affected (0.01 sec)mysql> grant replication slave on *.* to test@'%';
Query OK, 0 rows affected (0.00 sec)mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)mysql> \q
Bye

#在slave1和slave2上配置
[root@mysql2 ~]# /etc/init.d/mysqld stop
[root@mysql2 ~]# rm -rf /data/mysql/*
[root@mysql2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0[root@mysql2 ~]# mysqld --user mysql --initialize
[[root@mysql2 ~]# /etc/init.d/mysqld start
[root@mysql2 ~]# mysql -uroot 
mysql> alter user root@localhost identified by 'root';
Query OK, 0 rows affected (0.00 sec)mysql> change master to master_host='172.25.254.130',master_user='test',master_password='test',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)mysql> show status like 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

安装MHA所需要的软件

[root@mysql-mha ~]# ls
anaconda-ks.cfg  master_ip_failover  master_ip_online_change  MHA-7.zip
[root@mysql-mha ~]# unzip MHA-7.zip
Archive:  MHA-7.zipcreating: MHA-7/inflating: MHA-7/mha4mysql-manager-0.58-0.el7.centos.noarch.rpminflating: MHA-7/mha4mysql-manager-0.58.tar.gzinflating: MHA-7/mha4mysql-node-0.58-0.el7.centos.noarch.rpminflating: MHA-7/perl-Config-Tiny-2.14-7.el7.noarch.rpminflating: MHA-7/perl-Email-Date-Format-1.002-15.el7.noarch.rpminflating: MHA-7/perl-Log-Dispatch-2.41-1.el7.1.noarch.rpminflating: MHA-7/perl-Mail-Sender-0.8.23-1.el7.noarch.rpminflating: MHA-7/perl-Mail-Sendmail-0.79-21.el7.noarch.rpminflating: MHA-7/perl-MIME-Lite-3.030-1.el7.noarch.rpminflating: MHA-7/perl-MIME-Types-1.38-2.el7.noarch.rpminflating: MHA-7/perl-Net-Telnet-3.03-19.el7.noarch.rpminflating: MHA-7/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@mysql-mha ~]# ls
anaconda-ks.cfg  master_ip_failover  master_ip_online_change  MHA-7  MHA-7.zip
[root@mysql-mha ~]# cd MHA-7
[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm[root@mysql-mha MHA-7]# ssh-keygen[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.130
[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.150
[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.160[root@mysql-mha MHA-7]# yum install *.rpm -y[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm  root@172.25.254.130:/root
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm  root@172.25.254.150:/root
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm  root@172.25.254.160:/root#在slave安装
[root@mysql1 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql2 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql3 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

[root@mysql-mha ~]# masterha_master_switch \
--conf=/etc/masterha/app1.cnf \ #指定配置文件
--master_state=alive \ #指定master节点状态
--new_master_host=172.25.254.150 \ #指定新master节点
--new_master_port=3306 \ #指定新master节点端口
--orig_master_is_new_slave \ #原始master会变成新的slave
--running_updates_limit=10000 \ #切换超时时间#切换过程
[root@mysql-mha ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.150 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Sat Sep  7 08:51:32 2024 - [info] MHA::MasterRotate version 0.58.
Sat Sep  7 08:51:32 2024 - [info] Starting online master switch..
Sat Sep  7 08:51:32 2024 - [info]
Sat Sep  7 08:51:32 2024 - [info] * Phase 1: Configuration Check Phase..
Sat Sep  7 08:51:32 2024 - [info]
Sat Sep  7 08:51:32 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep  7 08:51:32 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Sep  7 08:51:32 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Sep  7 08:51:33 2024 - [info] GTID failover mode = 1
Sat Sep  7 08:51:33 2024 - [info] Current Alive Master: 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 08:51:33 2024 - [info] Alive Slaves:
Sat Sep  7 08:51:33 2024 - [info]   172.25.254.150(172.25.254.150:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 08:51:33 2024 - [info]     GTID ON
Sat Sep  7 08:51:33 2024 - [info]     Replicating from 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 08:51:33 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Sep  7 08:51:33 2024 - [info]   172.25.254.160(172.25.254.160:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 08:51:33 2024 - [info]     GTID ON
Sat Sep  7 08:51:33 2024 - [info]     Replicating from 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 08:51:33 2024 - [info]     Not candidate for the new Master (no_master is set)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.130(172.25.254.130:3306)? (YES/no): yes
Sat Sep  7 08:51:36 2024 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Sat Sep  7 08:51:36 2024 - [info]  ok.
Sat Sep  7 08:51:36 2024 - [info] Checking MHA is not monitoring or doing failover..
Sat Sep  7 08:51:36 2024 - [info] Checking replication health on 172.25.254.150..
Sat Sep  7 08:51:36 2024 - [info]  ok.
Sat Sep  7 08:51:36 2024 - [info] Checking replication health on 172.25.254.160..
Sat Sep  7 08:51:36 2024 - [info]  ok.
Sat Sep  7 08:51:36 2024 - [info] 172.25.254.150 can be new master.
Sat Sep  7 08:51:36 2024 - [info]
From:
172.25.254.130(172.25.254.130:3306) (current master)+--172.25.254.150(172.25.254.150:3306)+--172.25.254.160(172.25.254.160:3306)To:
172.25.254.150(172.25.254.150:3306) (new master)+--172.25.254.160(172.25.254.160:3306)+--172.25.254.130(172.25.254.130:3306)Starting master switch from 172.25.254.130(172.25.254.130:3306) to 172.25.254.150(172.25.254.150:3306)? (yes/NO): yes
Sat Sep  7 08:51:40 2024 - [info] Checking whether 172.25.254.150(172.25.254.150:3306) is ok for the new master..
Sat Sep  7 08:51:40 2024 - [info]  ok.
Sat Sep  7 08:51:40 2024 - [info] 172.25.254.130(172.25.254.130:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Sat Sep  7 08:51:40 2024 - [info] 172.25.254.130(172.25.254.130:3306): Resetting slave pointing to the dummy host.
Sat Sep  7 08:51:40 2024 - [info] ** Phase 1: Configuration Check Phase completed.
Sat Sep  7 08:51:40 2024 - [info]
Sat Sep  7 08:51:40 2024 - [info] * Phase 2: Rejecting updates Phase..
Sat Sep  7 08:51:40 2024 - [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
Sat Sep  7 08:51:57 2024 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Sat Sep  7 08:51:57 2024 - [info] Executing FLUSH TABLES WITH READ LOCK..
Sat Sep  7 08:51:57 2024 - [info]  ok.
Sat Sep  7 08:51:57 2024 - [info] Orig master binlog:pos is mysql-bin.000002:1119.
Sat Sep  7 08:51:57 2024 - [info]  Waiting to execute all relay logs on 172.25.254.150(172.25.254.150:3306)..
Sat Sep  7 08:51:57 2024 - [info]  master_pos_wait(mysql-bin.000002:1119) completed on 172.25.254.150(172.25.254.150:3306). Executed 0 events.
Sat Sep  7 08:51:57 2024 - [info]   done.
Sat Sep  7 08:51:57 2024 - [info] Getting new master's binlog name and position..
Sat Sep  7 08:51:57 2024 - [info]  mysql-bin.000002:1363
Sat Sep  7 08:51:57 2024 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.150', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='test', MASTER_PASSWORD='xxx';
Sat Sep  7 08:51:57 2024 - [info]
Sat Sep  7 08:51:57 2024 - [info] * Switching slaves in parallel..
Sat Sep  7 08:51:57 2024 - [info]
Sat Sep  7 08:51:57 2024 - [info] -- Slave switch on host 172.25.254.160(172.25.254.160:3306) started, pid: 1770
Sat Sep  7 08:51:57 2024 - [info]
Sat Sep  7 08:51:59 2024 - [info] Log messages from 172.25.254.160 ...
Sat Sep  7 08:51:59 2024 - [info]
Sat Sep  7 08:51:57 2024 - [info]  Waiting to execute all relay logs on 172.25.254.160(172.25.254.160:3306)..
Sat Sep  7 08:51:57 2024 - [info]  master_pos_wait(mysql-bin.000002:1119) completed on 172.25.254.160(172.25.254.160:3306). Executed 0 events.
Sat Sep  7 08:51:57 2024 - [info]   done.
Sat Sep  7 08:51:57 2024 - [info]  Resetting slave 172.25.254.160(172.25.254.160:3306) and starting replication from the new master 172.25.254.150(172.25.254.150:3306)..
Sat Sep  7 08:51:57 2024 - [info]  Executed CHANGE MASTER.
Sat Sep  7 08:51:58 2024 - [info]  Slave started.
Sat Sep  7 08:51:59 2024 - [info] End of log messages from 172.25.254.160 ...
Sat Sep  7 08:51:59 2024 - [info]
Sat Sep  7 08:51:59 2024 - [info] -- Slave switch on host 172.25.254.160(172.25.254.160:3306) succeeded.
Sat Sep  7 08:51:59 2024 - [info] Unlocking all tables on the orig master:
Sat Sep  7 08:51:59 2024 - [info] Executing UNLOCK TABLES..
Sat Sep  7 08:51:59 2024 - [info]  ok.
Sat Sep  7 08:51:59 2024 - [info] Starting orig master as a new slave..
Sat Sep  7 08:51:59 2024 - [info]  Resetting slave 172.25.254.130(172.25.254.130:3306) and starting replication from the new master 172.25.254.150(172.25.254.150:3306)..
Sat Sep  7 08:51:59 2024 - [info]  Executed CHANGE MASTER.
Sat Sep  7 08:52:00 2024 - [info]  Slave started.
Sat Sep  7 08:52:00 2024 - [info] All new slave servers switched successfully.
Sat Sep  7 08:52:00 2024 - [info]
Sat Sep  7 08:52:00 2024 - [info] * Phase 5: New master cleanup phase..
Sat Sep  7 08:52:00 2024 - [info]
Sat Sep  7 08:52:00 2024 - [info]  172.25.254.150: Resetting slave info succeeded.
Sat Sep  7 08:52:00 2024 - [info] Switching master to 172.25.254.150(172.25.254.150:3306) completed successfully.

检测

[root@mysql-mha ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf              Sat Sep  7 09:01:28 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep  7 09:01:28 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:01:28 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:01:28 2024 - [info] Starting SSH connection tests..
Sat Sep  7 09:01:29 2024 - [debug]
Sat Sep  7 09:01:28 2024 - [debug]  Connecting via SSH from root@172.25.254.130(172.25.254.130:22) to root@172.25.254.150(172.25.254.150:22)..
Sat Sep  7 09:01:28 2024 - [debug]   ok.
Sat Sep  7 09:01:28 2024 - [debug]  Connecting via SSH from root@172.25.254.130(172.25.254.130:22) to root@172.25.254.160(172.25.254.160:22)..
Sat Sep  7 09:01:28 2024 - [debug]   ok.
Sat Sep  7 09:01:29 2024 - [debug]
Sat Sep  7 09:01:28 2024 - [debug]  Connecting via SSH from root@172.25.254.150(172.25.254.150:22) to root@172.25.254.130(172.25.254.130:22)..
Sat Sep  7 09:01:29 2024 - [debug]   ok.
Sat Sep  7 09:01:29 2024 - [debug]  Connecting via SSH from root@172.25.254.150(172.25.254.150:22) to root@172.25.254.160(172.25.254.160:22)..
Sat Sep  7 09:01:29 2024 - [debug]   ok.
Sat Sep  7 09:01:30 2024 - [debug]
Sat Sep  7 09:01:29 2024 - [debug]  Connecting via SSH from root@172.25.254.160(172.25.254.160:22) to root@172.25.254.130(172.25.254.130:22)..
Sat Sep  7 09:01:29 2024 - [debug]   ok.
Sat Sep  7 09:01:29 2024 - [debug]  Connecting via SSH from root@172.25.254.160(172.25.254.160:22) to root@172.25.254.150(172.25.254.150:22)..
Sat Sep  7 09:01:29 2024 - [debug]   ok.
Sat Sep  7 09:01:30 2024 - [info] All SSH connection tests passed successfully.
[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf             Sat Sep  7 09:01:40 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep  7 09:01:40 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:01:40 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:01:40 2024 - [info] MHA::MasterMonitor version 0.58.
Sat Sep  7 09:01:41 2024 - [info] GTID failover mode = 1
Sat Sep  7 09:01:41 2024 - [info] Dead Servers:
Sat Sep  7 09:01:41 2024 - [info] Alive Servers:
Sat Sep  7 09:01:41 2024 - [info]   172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:01:41 2024 - [info]   172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:01:41 2024 - [info]   172.25.254.160(172.25.254.160:3306)
Sat Sep  7 09:01:41 2024 - [info] Alive Slaves:
Sat Sep  7 09:01:41 2024 - [info]   172.25.254.130(172.25.254.130:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:01:41 2024 - [info]     GTID ON
Sat Sep  7 09:01:41 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:01:41 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Sep  7 09:01:41 2024 - [info]   172.25.254.160(172.25.254.160:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:01:41 2024 - [info]     GTID ON
Sat Sep  7 09:01:41 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:01:41 2024 - [info]     Not candidate for the new Master (no_master is set)
Sat Sep  7 09:01:41 2024 - [info] Current Alive Master: 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:01:41 2024 - [info] Checking slave configurations..
Sat Sep  7 09:01:41 2024 - [info]  read_only=1 is not set on slave 172.25.254.160(172.25.254.160:3306).
Sat Sep  7 09:01:41 2024 - [info] Checking replication filtering settings..
Sat Sep  7 09:01:41 2024 - [info]  binlog_do_db= , binlog_ignore_db=
Sat Sep  7 09:01:41 2024 - [info]  Replication filtering check ok.
Sat Sep  7 09:01:41 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Sep  7 09:01:41 2024 - [info] Checking SSH publickey authentication settings on the current master..
Sat Sep  7 09:01:42 2024 - [info] HealthCheck: SSH to 172.25.254.150 is reachable.
Sat Sep  7 09:01:42 2024 - [info]
172.25.254.150(172.25.254.150:3306) (current master)+--172.25.254.130(172.25.254.130:3306)+--172.25.254.160(172.25.254.160:3306)Sat Sep  7 09:01:42 2024 - [info] Checking replication health on 172.25.254.130..
Sat Sep  7 09:01:42 2024 - [info]  ok.
Sat Sep  7 09:01:42 2024 - [info] Checking replication health on 172.25.254.160..
Sat Sep  7 09:01:42 2024 - [info]  ok.
Sat Sep  7 09:01:42 2024 - [warning] master_ip_failover_script is not defined.
Sat Sep  7 09:01:42 2024 - [warning] shutdown_script is not defined.
Sat Sep  7 09:01:42 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.

#模拟master故障
[root@mysql2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL........... SUCCESS!#--ignore_last_failover 表示忽略在/etc/masterha/目录中在切换过程中生成的锁文件
[root@mysql-mha ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.150 --dead_master_port=3306 --new_master_host=172.25.254.130 --new_master_port=3306 --ignore_last_failover 
--dead_master_ip=<dead_master_ip> is not set. Using 172.25.254.150.
Sat Sep  7 09:06:39 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep  7 09:06:39 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:06:39 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:06:39 2024 - [info] MHA::MasterFailover version 0.58.
Sat Sep  7 09:06:39 2024 - [info] Starting master failover.
Sat Sep  7 09:06:39 2024 - [info]
Sat Sep  7 09:06:39 2024 - [info] * Phase 1: Configuration Check Phase..
Sat Sep  7 09:06:39 2024 - [info]
Sat Sep  7 09:06:40 2024 - [info] GTID failover mode = 1
Sat Sep  7 09:06:40 2024 - [info] Dead Servers:
Sat Sep  7 09:06:40 2024 - [info]   172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:06:40 2024 - [info] Checking master reachability via MySQL(double check)...
Sat Sep  7 09:06:40 2024 - [info]  ok.
Sat Sep  7 09:06:40 2024 - [info] Alive Servers:
Sat Sep  7 09:06:40 2024 - [info]   172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:06:40 2024 - [info]   172.25.254.160(172.25.254.160:3306)
Sat Sep  7 09:06:40 2024 - [info] Alive Slaves:
Sat Sep  7 09:06:40 2024 - [info]   172.25.254.130(172.25.254.130:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:06:40 2024 - [info]     GTID ON
Sat Sep  7 09:06:40 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:06:40 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Sep  7 09:06:40 2024 - [info]   172.25.254.160(172.25.254.160:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:06:40 2024 - [info]     GTID ON
Sat Sep  7 09:06:40 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:06:40 2024 - [info]     Not candidate for the new Master (no_master is set)
Master 172.25.254.150(172.25.254.150:3306) is dead. Proceed? (yes/NO): yes
Sat Sep  7 09:07:06 2024 - [info] Starting GTID based failover.
Sat Sep  7 09:07:06 2024 - [info]
Sat Sep  7 09:07:06 2024 - [info] ** Phase 1: Configuration Check Phase completed.
Sat Sep  7 09:07:06 2024 - [info]
Sat Sep  7 09:07:06 2024 - [info] * Phase 2: Dead Master Shutdown Phase..
Sat Sep  7 09:07:06 2024 - [info]
Sat Sep  7 09:07:11 2024 - [warning] HealthCheck: Got timeout on checking SSH connection to 172.25.254.150! at /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm line 343, <STDIN> line 1.
Sat Sep  7 09:07:11 2024 - [info] Forcing shutdown so that applications never connect to the current master..
Sat Sep  7 09:07:11 2024 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Sat Sep  7 09:07:11 2024 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sat Sep  7 09:07:11 2024 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sat Sep  7 09:07:11 2024 - [info]
Sat Sep  7 09:07:11 2024 - [info] * Phase 3: Master Recovery Phase..
Sat Sep  7 09:07:11 2024 - [info]
Sat Sep  7 09:07:11 2024 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sat Sep  7 09:07:11 2024 - [info]
Sat Sep  7 09:07:11 2024 - [info] The latest binary log file/position on all slaves is mysql-bin.000002:1363
Sat Sep  7 09:07:11 2024 - [info] Retrieved Gtid Set: 6408ca0c-6cad-11ef-9898-000c297763e2:1
Sat Sep  7 09:07:11 2024 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sat Sep  7 09:07:11 2024 - [info]   172.25.254.130(172.25.254.130:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:07:11 2024 - [info]     GTID ON
Sat Sep  7 09:07:11 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:07:11 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Sep  7 09:07:11 2024 - [info]   172.25.254.160(172.25.254.160:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:07:11 2024 - [info]     GTID ON
Sat Sep  7 09:07:11 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:07:11 2024 - [info]     Not candidate for the new Master (no_master is set)
Sat Sep  7 09:07:11 2024 - [info] The oldest binary log file/position on all slaves is mysql-bin.000002:1363
Sat Sep  7 09:07:11 2024 - [info] Retrieved Gtid Set: 6408ca0c-6cad-11ef-9898-000c297763e2:1
Sat Sep  7 09:07:11 2024 - [info] Oldest slaves:
Sat Sep  7 09:07:11 2024 - [info]   172.25.254.130(172.25.254.130:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:07:11 2024 - [info]     GTID ON
Sat Sep  7 09:07:11 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:07:11 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Sep  7 09:07:11 2024 - [info]   172.25.254.160(172.25.254.160:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:07:11 2024 - [info]     GTID ON
Sat Sep  7 09:07:11 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:07:11 2024 - [info]     Not candidate for the new Master (no_master is set)
Sat Sep  7 09:07:11 2024 - [info]
Sat Sep  7 09:07:11 2024 - [info] * Phase 3.3: Determining New Master Phase..
Sat Sep  7 09:07:11 2024 - [info]
Sat Sep  7 09:07:11 2024 - [info] 172.25.254.130 can be new master.
Sat Sep  7 09:07:11 2024 - [info] New master is 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:07:11 2024 - [info] Starting master failover..
Sat Sep  7 09:07:11 2024 - [info]
From:
172.25.254.150(172.25.254.150:3306) (current master)+--172.25.254.130(172.25.254.130:3306)+--172.25.254.160(172.25.254.160:3306)To:
172.25.254.130(172.25.254.130:3306) (new master)+--172.25.254.160(172.25.254.160:3306)Starting master switch from 172.25.254.150(172.25.254.150:3306) to 172.25.254.130(172.25.254.130:3306)? (yes/NO): yes
Sat Sep  7 09:07:16 2024 - [info] New master decided manually is 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:07:16 2024 - [info]
Sat Sep  7 09:07:16 2024 - [info] * Phase 3.3: New Master Recovery Phase..
Sat Sep  7 09:07:16 2024 - [info]
Sat Sep  7 09:07:16 2024 - [info]  Waiting all logs to be applied..
Sat Sep  7 09:07:16 2024 - [info]   done.
Sat Sep  7 09:07:16 2024 - [info] Getting new master's binlog name and position..
Sat Sep  7 09:07:16 2024 - [info]  mysql-bin.000002:1363
Sat Sep  7 09:07:16 2024 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.130', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='test', MASTER_PASSWORD='xxx';
Sat Sep  7 09:07:16 2024 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000002, 1363, 6408ca0c-6cad-11ef-9898-000c297763e2:1,
c67cab0f-6cac-11ef-9eae-000c29d16b0b:1-4
Sat Sep  7 09:07:16 2024 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Sat Sep  7 09:07:16 2024 - [info] Setting read_only=0 on 172.25.254.130(172.25.254.130:3306)..
Sat Sep  7 09:07:16 2024 - [info]  ok.
Sat Sep  7 09:07:16 2024 - [info] ** Finished master recovery successfully.
Sat Sep  7 09:07:16 2024 - [info] * Phase 3: Master Recovery Phase completed.
Sat Sep  7 09:07:16 2024 - [info]
Sat Sep  7 09:07:16 2024 - [info] * Phase 4: Slaves Recovery Phase..
Sat Sep  7 09:07:16 2024 - [info]
Sat Sep  7 09:07:16 2024 - [info]
Sat Sep  7 09:07:16 2024 - [info] * Phase 4.1: Starting Slaves in parallel..
Sat Sep  7 09:07:16 2024 - [info]
Sat Sep  7 09:07:16 2024 - [info] -- Slave recovery on host 172.25.254.160(172.25.254.160:3306) started, pid: 1837. Check tmp log /etc/masterha/172.25.254.160_3306_20240907090638.log if it takes time..
Sat Sep  7 09:07:17 2024 - [info]
Sat Sep  7 09:07:17 2024 - [info] Log messages from 172.25.254.160 ...
Sat Sep  7 09:07:17 2024 - [info]
Sat Sep  7 09:07:16 2024 - [info]  Resetting slave 172.25.254.160(172.25.254.160:3306) and starting replication from the new master 172.25.254.130(172.25.254.130:3306)..
Sat Sep  7 09:07:16 2024 - [info]  Executed CHANGE MASTER.
Sat Sep  7 09:07:16 2024 - [info]  Slave started.
Sat Sep  7 09:07:16 2024 - [info]  gtid_wait(6408ca0c-6cad-11ef-9898-000c297763e2:1,
c67cab0f-6cac-11ef-9eae-000c29d16b0b:1-4) completed on 172.25.254.160(172.25.254.160:3306). Executed 0 events.
Sat Sep  7 09:07:17 2024 - [info] End of log messages from 172.25.254.160.
Sat Sep  7 09:07:17 2024 - [info] -- Slave on host 172.25.254.160(172.25.254.160:3306) started.
Sat Sep  7 09:07:17 2024 - [info] All new slave servers recovered successfully.
Sat Sep  7 09:07:17 2024 - [info]
Sat Sep  7 09:07:17 2024 - [info] * Phase 5: New master cleanup phase..
Sat Sep  7 09:07:17 2024 - [info]
Sat Sep  7 09:07:17 2024 - [info] Resetting slave info on the new master..
Sat Sep  7 09:07:17 2024 - [info]  172.25.254.130: Resetting slave info succeeded.
Sat Sep  7 09:07:17 2024 - [info] Master failover to 172.25.254.130(172.25.254.130:3306) completed successfully.
Sat Sep  7 09:07:17 2024 - [info]----- Failover Report -----app1: MySQL Master failover 172.25.254.150(172.25.254.150:3306) to 172.25.254.130(172.25.254.130:3306) succeededMaster 172.25.254.150(172.25.254.150:3306) is down!Check MHA Manager logs at mysql-mha for details.Started manual(interactive) failover.
Selected 172.25.254.130(172.25.254.130:3306) as a new master.
172.25.254.130(172.25.254.130:3306): OK: Applying all logs succeeded.
172.25.254.160(172.25.254.160:3306): OK: Slave started, replicating from 172.25.254.130(172.25.254.130:3306)
172.25.254.130(172.25.254.130:3306): Resetting slave info succeeded.
Master failover to 172.25.254.130(172.25.254.130:3306) completed successfully.

[root@mysql2 ~]# /etc/init.d/mysqld start[root@mysql2 ~]# mysql -uroot -p
mysql> change master to master_host='172.25.254.130',master_user='test',master_password='test',master_auto_position=1;
mysql> start slave;#测试
[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf             Sat Sep  7 09:26:21 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep  7 09:26:21 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:26:21 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Sep  7 09:26:21 2024 - [info] MHA::MasterMonitor version 0.58.
Sat Sep  7 09:26:22 2024 - [info] GTID failover mode = 1
Sat Sep  7 09:26:22 2024 - [info] Dead Servers:
Sat Sep  7 09:26:22 2024 - [info] Alive Servers:
Sat Sep  7 09:26:22 2024 - [info]   172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:26:22 2024 - [info]   172.25.254.150(172.25.254.150:3306)
Sat Sep  7 09:26:22 2024 - [info]   172.25.254.160(172.25.254.160:3306)
Sat Sep  7 09:26:22 2024 - [info] Alive Slaves:
Sat Sep  7 09:26:22 2024 - [info]   172.25.254.150(172.25.254.150:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:26:22 2024 - [info]     GTID ON
Sat Sep  7 09:26:22 2024 - [info]     Replicating from 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:26:22 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Sep  7 09:26:22 2024 - [info]   172.25.254.160(172.25.254.160:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 09:26:22 2024 - [info]     GTID ON
Sat Sep  7 09:26:22 2024 - [info]     Replicating from 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:26:22 2024 - [info]     Not candidate for the new Master (no_master is set)
Sat Sep  7 09:26:22 2024 - [info] Current Alive Master: 172.25.254.130(172.25.254.130:3306)
Sat Sep  7 09:26:22 2024 - [info] Checking slave configurations..
Sat Sep  7 09:26:22 2024 - [info]  read_only=1 is not set on slave 172.25.254.150(172.25.254.150:3306).
Sat Sep  7 09:26:22 2024 - [info]  read_only=1 is not set on slave 172.25.254.160(172.25.254.160:3306).
Sat Sep  7 09:26:22 2024 - [info] Checking replication filtering settings..
Sat Sep  7 09:26:22 2024 - [info]  binlog_do_db= , binlog_ignore_db=
Sat Sep  7 09:26:22 2024 - [info]  Replication filtering check ok.
Sat Sep  7 09:26:22 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Sep  7 09:26:22 2024 - [info] Checking SSH publickey authentication settings on the current master..
Sat Sep  7 09:26:22 2024 - [info] HealthCheck: SSH to 172.25.254.130 is reachable.
Sat Sep  7 09:26:22 2024 - [info]
172.25.254.130(172.25.254.130:3306) (current master)+--172.25.254.150(172.25.254.150:3306)+--172.25.254.160(172.25.254.160:3306)Sat Sep  7 09:26:22 2024 - [info] Checking replication health on 172.25.254.150..
Sat Sep  7 09:26:22 2024 - [info]  ok.
Sat Sep  7 09:26:22 2024 - [info] Checking replication health on 172.25.254.160..
Sat Sep  7 09:26:22 2024 - [info]  ok.
Sat Sep  7 09:26:22 2024 - [warning] master_ip_failover_script is not defined.
Sat Sep  7 09:26:22 2024 - [warning] shutdown_script is not defined.
Sat Sep  7 09:26:22 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.

[root@mysql-mha masterha]# rm -fr app1.failover.complete #删除切换锁文件#监控程序通过指定配置文件监控master状态，当master出问题后自动切换并退出避免重复做故障切换[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf[root@mysql-mha ~]# cat /etc/masterha/manager.log[root@mysql1 ~]# /etc/init.d/mysqld start
Shutting down MySQL........... SUCCESS!mysql> change master to master_host='172.25.254.150',master_user='test',master_password='test',master_auto_position=1;mysql> start slave;[root@mysql-mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Sat Sep  7 10:10:21 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep  7 10:10:21 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Sep  7 10:10:21 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Sep  7 10:10:21 2024 - [info] MHA::MasterMonitor version 0.58.
Sat Sep  7 10:10:22 2024 - [info] GTID failover mode = 1
Sat Sep  7 10:10:22 2024 - [info] Dead Servers:
Sat Sep  7 10:10:22 2024 - [info] Alive Servers:
Sat Sep  7 10:10:22 2024 - [info]   172.25.254.130(172.25.254.130:3306)
Sat Sep  7 10:10:22 2024 - [info]   172.25.254.150(172.25.254.150:3306)
Sat Sep  7 10:10:22 2024 - [info]   172.25.254.160(172.25.254.160:3306)
Sat Sep  7 10:10:22 2024 - [info] Alive Slaves:
Sat Sep  7 10:10:22 2024 - [info]   172.25.254.130(172.25.254.130:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 10:10:22 2024 - [info]     GTID ON
Sat Sep  7 10:10:22 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 10:10:22 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Sep  7 10:10:22 2024 - [info]   172.25.254.160(172.25.254.160:3306)  Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Sep  7 10:10:22 2024 - [info]     GTID ON
Sat Sep  7 10:10:22 2024 - [info]     Replicating from 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 10:10:22 2024 - [info]     Not candidate for the new Master (no_master is set)
Sat Sep  7 10:10:22 2024 - [info] Current Alive Master: 172.25.254.150(172.25.254.150:3306)
Sat Sep  7 10:10:22 2024 - [info] Checking slave configurations..
Sat Sep  7 10:10:22 2024 - [info]  read_only=1 is not set on slave 172.25.254.130(172.25.254.130:3306).
Sat Sep  7 10:10:22 2024 - [info]  read_only=1 is not set on slave 172.25.254.160(172.25.254.160:3306).
Sat Sep  7 10:10:22 2024 - [info] Checking replication filtering settings..
Sat Sep  7 10:10:22 2024 - [info]  binlog_do_db= , binlog_ignore_db=
Sat Sep  7 10:10:22 2024 - [info]  Replication filtering check ok.
Sat Sep  7 10:10:22 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Sep  7 10:10:22 2024 - [info] Checking SSH publickey authentication settings on the current master..
Sat Sep  7 10:10:27 2024 - [warning] HealthCheck: Got timeout on checking SSH connection to 172.25.254.150! at /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm line 343.
Sat Sep  7 10:10:27 2024 - [info]
172.25.254.150(172.25.254.150:3306) (current master)+--172.25.254.130(172.25.254.130:3306)+--172.25.254.160(172.25.254.160:3306)Sat Sep  7 10:10:27 2024 - [info] Checking replication health on 172.25.254.130..
Sat Sep  7 10:10:27 2024 - [info]  ok.
Sat Sep  7 10:10:27 2024 - [info] Checking replication health on 172.25.254.160..
Sat Sep  7 10:10:27 2024 - [info]  ok.
Sat Sep  7 10:10:27 2024 - [warning] master_ip_failover_script is not defined.
Sat Sep  7 10:10:27 2024 - [warning] shutdown_script is not defined.
Sat Sep  7 10:10:27 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.

[root@mysql-mha ~]# ls
anaconda-ks.cfg  master_ip_failover  master_ip_online_change  MHA-7  MHA-7.zip[root@mysql-mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/[root@mysql-mha ~]# chmod +x /usr/local/bin/master_ip_*[root@mysql-mha ~]# vim /usr/local/bin/master_ip_failover
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";[root@mysql-mha ~]# vim /usr/local/bin/master_ip_online_change
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";[root@mysql-mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf #启动监
控程序[root@mysql1 ~]# ip a a 172.25.254.100 dev eth0 #在master节点添加VIP

[root@mysql1 ~]# /etc/init.d/mysqld stop #关闭主节点服务[root@mysql2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:77:63:e2 brd ff:ff:ff:ff:ff:ffinet 172.25.254.150/24 brd 172.25.254.255 scope global noprefixroute eth0valid_lft forever preferred_lft foreverinet 172.25.254.131/32 scope global eth0valid_lft forever preferred_lft foreverinet 172.25.254.100/24 scope global secondary eth0 #vip漂移到新的master主机上valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe77:63e2/64 scope linkvalid_lft forever preferred_lft forever

[root@mysql1 ~]# /etc/init.d/mysqld start[root@mysql-mha masterha]# rm  app1.failover.complete[root@mysql1 ~]#mysql -uroot -pmysql> change master to master_host='172.25.254.150',master_user='test',master_password='test',master_auto_position=1;mysql>start slave；

[root@mysql-mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.130 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000[root@mysql1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:d1:6b:0b brd ff:ff:ff:ff:ff:ffinet 172.25.254.130/24 brd 172.25.254.255 scope global noprefixroute eth0valid_lft forever preferred_lft foreverinet 172.25.254.100/24 scope global secondary eth0 #vip漂移回来valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fed1:6b0b/64 scope linkvalid_lft forever preferred_lft forever