2.7 lnmp架构_MySQL3

最新推荐文章于 2021-12-03 17:31:34 发布

Outlook(^_^)

最新推荐文章于 2021-12-03 17:31:34 发布

阅读量218

点赞数

分类专栏：企业实战文章标签： linux mysql

本文链接：https://blog.csdn.net/weixin_47133613/article/details/115604756

版权

企业实战专栏收录该内容

29 篇文章 0 订阅

订阅专栏

慢查询

set：超过多长时间认为超时
慢查询默认没开

开数据库
start

data里灭月slow文件

生产环境最好激活
在全局开启
模拟sleep（10）；
可以在slow日志里查询到

mysqldumpslow备份慢查询

mysql路由器

一主两从：一个负责写，2个负责读

server1（第一个节点：作为引导节点）

ON
strart
OFF

[root@server1 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@server1 ~]# mysql -pwestos
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.31-log Source distribution

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 group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.04 sec)

mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
±--------------------------±-------------------------------------±------------±------------±-------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
±--------------------------±-------------------------------------±------------±------------±-------------+
| group_replication_applier | fb85ede0-99d9-11eb-bc3d-5254005fb835 | server1 | 3306 | ONLINE |
±--------------------------±-------------------------------------±------------±------------±-------------+
1 row in set (0.00 sec)

server2：
stra

[root@server2 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@server2 ~]# mysql -pwestos
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.31-log Source distribution

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> start group_replication;
Query OK, 0 rows affected, 1 warning (5.78 sec)

3：start

[root@server3 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@server3 ~]# mysql -pwestos
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.31-log Source distribution

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> start group_replication;
Query OK, 0 rows affected, 1 warning (3.15 sec)

4：
安装rpm

[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
warning: mysql-router-community-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-router-community-8.0.21-1.e################################# [100%]

[root@server4 ~]# rpm -ql mysql-router-community
/etc/mysqlrouter
/etc/mysqlrouter/mysqlrouter.conf
/usr/bin/mysqlrouter
/usr/bin/mysqlrouter_keyring
/usr/bin/mysqlrouter_passwd
/usr/bin/mysqlrouter_plugin_info
/usr/lib/systemd/system/mysqlrouter.service
/usr/lib/tmpfiles.d/mysqlrouter.conf
/usr/lib64/mysqlrouter
/usr/lib64/mysqlrouter/http_auth_backend.so
/usr/lib64/mysqlrouter/http_auth_realm.so
/usr/lib64/mysqlrouter/http_server.so
/usr/lib64/mysqlrouter/keepalive.so
/usr/lib64/mysqlrouter/metadata_cache.so
/usr/lib64/mysqlrouter/mysql_protocol.so
/usr/lib64/mysqlrouter/private
/usr/lib64/mysqlrouter/private/libmysqlharness.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_http.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_http_auth_backend.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_http_auth_realm.so.1
/usr/lib64/mysqlrouter/private/libprotobuf-lite.so.3.11.4
/usr/lib64/mysqlrouter/rest_api.so
/usr/lib64/mysqlrouter/rest_metadata_cache.so
/usr/lib64/mysqlrouter/rest_router.so
/usr/lib64/mysqlrouter/rest_routing.so
/usr/lib64/mysqlrouter/router_protobuf.so
/usr/lib64/mysqlrouter/routing.so
/usr/share/doc/mysql-router-community-8.0.21
/usr/share/doc/mysql-router-community-8.0.21/LICENSE.router
/usr/share/doc/mysql-router-community-8.0.21/README.router
/usr/share/man/man1/mysqlrouter.1.gz
/usr/share/man/man1/mysqlrouter_passwd.1.gz
/usr/share/man/man1/mysqlrouter_plugin_info.1.gz
/var/log/mysqlrouter
/var/run/mysqlrouter

vim 配置文件
写入内容
（如果是一主两从，那么目的地只能写相应的master或者slave。如果是组复制，就不需要）

[root@server4 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.21.2:3306,172.25.21.3:3306,172.25.21.4:3306
routing_strategy = round-robin

[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.21.2:3306,172.25.21.3:3306,172.25.21.4:3306
routing_strategy = first-available

启动router脚本：systemctl

[root@server4 ~]# systemctl start mysqlrouter.service

安装net-tols

[root@server4 ~]# yum install -y net-tools
[root@server4 ~]# netstat -antuple | grep 700
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 998 33381 3860/mysqlrouter
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 998 27182 3860/mysqlrouter

21主机：
查询hPu（u是什么权限，对方给你一定的权限）
但是，这些，mysql不允许远程登陆
repl可以，但是只能复制

[root@foundation21 ~]# mysql -h 172.25.21.5 -P 7001 -u root -p
Enter password:
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘172.25.21.5’ (113)

[root@server4 ~]# systemctl disable --now firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@foundation21 ~]# mysql -h 172.25.21.5 -P 7001 -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@‘server4’ (using password: YES)

[root@foundation21 ~]# mysql -h 172.25.21.5 -P 7001 -u rpl_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31-log Source distribution

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 databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
±-------------------+
1 row in set (0.00 sec)

server1：
建立用户，授予grant权限select
建立用户，test库的所有权限
刷新：这2 个用户在3台主机都可以使用

mysql> grant select on . to user1@’%’ identified by ‘westos’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database westos;
Query OK, 1 row affected (0.00 sec)

mysql> create table westos.linux(
-> username varchar(6) not null,
-> password varchar(30) not null);
Query OK, 0 rows affected (0.03 sec)

mysql> grant select on westos.* to user2@’%’ identified by ‘westos’;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

21主机：7001端口，读端口
7002，写

root@foundation21 ~]# mysql -h 172.25.21.5 -P 7001 -u rpl_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31-log Source distribution

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 databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
±-------------------+
1 row in set (0.00 sec)

mysql> ^DBye
[root@foundation21 ~]# mysql -h 172.25.21.5 -P 7001 -u user1 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.31-log Source distribution

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.

[root@foundation21 ~]# mysql -h 172.25.21.5 -P 7002 -u user2 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31-log Source distribution

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> use westos;
Database changed
mysql> show tables;
±-----------------+
| Tables_in_westos |
±-----------------+
| linux |
±-----------------+
1 row in set (0.00 sec)

mysql> select * from linux;
Empty set (0.00 sec)

mysql> insert into linux values (yao,‘123’);

server1 ；
安装lsof

[root@server1 ~]# yum install -y lsof
[root@server1 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 6144 mysql 14u IPv6 32087 0t0 TCP *:mysql (LISTEN)
mysqld 6144 mysql 79u IPv6 32185 0t0 TCP server1:mysql->server4:54210 (ESTABLISHED)

[root@server2 ~]# yum install -y lsof
[root@server2 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 6320 mysql 31u IPv6 26967 0t0 TCP *:mysql (LISTEN)

21主机：
7001，

健康检查

server1：停掉mysqld

[root@server1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

21主机：
查询一下
去找21主机连的是谁

[root@foundation21 ~]# mysql -h 172.25.21.5 -P 7001 -u user1 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.31-log Source distribution

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> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
±-----------------+
| Tables_in_westos |
±-----------------+
| linux |
±-----------------+
1 row in set (0.00 sec)

lsof：查看mysq打开的服务

1挂了，被连接。
说明mysql后端都有检查（prosee）

[root@server2 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 6320 mysql 31u IPv6 26967 0t0 TCP *:mysql (LISTEN)
mysqld 6320 mysql 55u IPv6 27279 0t0 TCP server2:mysql->server4:39022 (ESTABLISHED)

也可以用netstat进行查询

[root@server2 ~]# netstat -antuple | grep 172.25.21.5
tcp6 0 0 172.25.21.3:3306 172.25.21.5:39026 ESTABLISHED 1000 27282 6320/mysqld

MHA高可用

123停掉stopmysqld
[root@server1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

[root@server2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

[root@server3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

一主两从（123内容一致）

1：rm data
配置文件里的组复制删除，看图
初始化
start
secure
musql -p
show
grant

[root@server1 ~]# cd /usr/local/mysql/
[root@server1 mysql]# cd data/
[root@server1 data]# rm -fr *
[root@server1 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

[root@server1 data]# mysqld --initialize --user=mysql
2021-04-11T03:32:15.046342Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-11T03:32:15.425162Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-11T03:32:15.504472Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-11T03:32:15.578562Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 82c65121-9a76-11eb-9fcc-5254005fb835.
2021-04-11T03:32:15.581686Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-04-11T03:32:16.111890Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-11T03:32:16.175729Z 1 [Note] A temporary password is generated for root@localhost: d4xjJifA#lzt
[root@server1 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to ‘/usr/local/mysql/data/server1.err’.
SUCCESS!
[root@server1 data]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :

… skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Dropping test database…
Success.
Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[root@server1 data]# mysql -pwestos
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31-log Source distribution

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\G;
*************************** 1. row ***************************
File: binlog.000002
Position: 682
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 82c65121-9a76-11eb-9fcc-5254005fb835:1-3
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> grant replication slave on . to repl@’%’ identified by ‘westos’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

2:
rm -fr
vim
初始化
start
secure
mysql
change master
wtart slave
show yes yes

[root@server2 ~]# cd /usr/local/mysql/data/
[root@server2 data]# rm -fr *
[root@server2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

[root@server2 data]# mysqld --initialize --user=mysql
2021-04-11T03:37:48.549100Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-11T03:37:48.939906Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-11T03:37:49.032570Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-11T03:37:49.108649Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4992f82e-9a77-11eb-b4d8-525400e26f27.
2021-04-11T03:37:49.111703Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-04-11T03:37:49.289195Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-11T03:37:49.490075Z 1 [Note] A temporary password is generated for root@localhost: DYqyhZeo9n)T
[root@server2 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to ‘/usr/local/mysql/data/server2.err’.
SUCCESS!
[root@server2 data]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

Press y|Y for Yes, any other key for No:
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Dropping test database…
Success.
Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[root@server2 data]# mysql -pwestos
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31-log Source distribution

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.21.2’,master_user=‘repl’,master_password=‘westos’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 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 event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 957
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 1164
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 957
Relay_Log_Space: 1373
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 82c65121-9a76-11eb-9fcc-5254005fb835
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 82c65121-9a76-11eb-9fcc-5254005fb835:1-4
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

3：
rm
vim
初始化
start
secure
mysql
change和2一样
（主从哈桑面的文件一样，因为大家都可能作为主）

[root@server3 ~]# cd /usr/local/mysql/data/
[root@server3 data]# rm -fr *
[root@server3 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
[root@server3 data]# mysqld --initialize --user=mysql
2021-04-11T03:43:52.436434Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-11T03:43:52.837997Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-11T03:43:52.921499Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-11T03:43:52.996109Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2277ca0c-9a78-11eb-8b34-5254003540d0.
2021-04-11T03:43:52.999442Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-04-11T03:43:53.306842Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-11T03:43:53.373861Z 1 [Note] A temporary password is generated for root@localhost: Aeh&?<y(1J<m
[root@server3 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to ‘/usr/local/mysql/data/server3.err’.
SUCCESS!
[root@server3 data]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

Press y|Y for Yes, any other key for No:
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Dropping test database…
Success.
Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[root@server3 data]# mysql -pwestos
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31-log Source distribution

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.21.2’,master_user=‘repl’,master_password=‘westos’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 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 event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 957
Relay_Log_File: server3-relay-bin.000002
Relay_Log_Pos: 1164
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 957
Relay_Log_Space: 1373
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 82c65121-9a76-11eb-9fcc-5254005fb835
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 82c65121-9a76-11eb-9fcc-5254005fb835:1-4
Executed_Gtid_Set: 5b1b1aee-9a78-11eb-a07e-5254003540d0:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

4：
安装
mirror
直接rpm *.rpm

[root@server4 ~]# cd MHA-7/
[root@server4 MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@server4 MHA-7]# yum install -y *.rpm
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:/root

1：
yum install

[root@server1 ~]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
error: Failed dependencies:
perl(DBD::mysql) is needed by mha4mysql-node-0.58-0.el7.centos.noarch
perl(DBI) is needed by mha4mysql-node-0.58-0.el7.centos.noarch
[root@server1 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2：

mha

[root@server2 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

3：
mha

[root@server3 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

4：
检测check
全局文件在etc/masterha_default.cnf

[root@server4 MHA-7]# rpm -ql mha4mysql-node
/usr/bin/apply_diff_relay_logs
/usr/bin/filter_mysqlbinlog
/usr/bin/purge_relay_logs
/usr/bin/save_binary_logs
/usr/share/man/man1/apply_diff_relay_logs.1.gz
/usr/share/man/man1/filter_mysqlbinlog.1.gz
/usr/share/man/man1/purge_relay_logs.1.gz
/usr/share/man/man1/save_binary_logs.1.gz
/usr/share/perl5/vendor_perl/MHA/BinlogHeaderParser.pm
/usr/share/perl5/vendor_perl/MHA/BinlogManager.pm
/usr/share/perl5/vendor_perl/MHA/BinlogPosFindManager.pm
/usr/share/perl5/vendor_perl/MHA/BinlogPosFinder.pm
/usr/share/perl5/vendor_perl/MHA/BinlogPosFinderElp.pm
/usr/share/perl5/vendor_perl/MHA/BinlogPosFinderXid.pm
/usr/share/perl5/vendor_perl/MHA/NodeConst.pm
/usr/share/perl5/vendor_perl/MHA/NodeUtil.pm
/usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm

复制源码的配置文件到自己建立的master目录里
cat 写到一个文件
（全局和局部在一起）

[root@server4 MHA-7]# mkdir /etc/masterha
[root@server4 MHA-7]# cd
[root@server4 ~]# cd MHA-7/
[root@server4 MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@server4 MHA-7]# cd mha4mysql-manager-0.58/
[root@server4 mha4mysql-manager-0.58]# ks
-bash: ks: command not found
[root@server4 mha4mysql-manager-0.58]# ls
AUTHORS COPYING lib MANIFEST README samples tests
bin debian Makefile.PL MANIFEST.SKIP rpm t
[root@server4 mha4mysql-manager-0.58]# cd samples/
[root@server4 samples]# ls
conf scripts
[root@server4 samples]# cd conf/
[root@server4 conf]# ls
app1.cnf masterha_default.cnf
[root@server4 conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app.cnf

vim app。cnf

[root@server4 conf]# cd /etc/masterha/
[root@server4 masterha]# ls
app.cnf
[root@server4 masterha]# vim app.cnf

[server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir= /usr/local/mysql/data
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.21.2 -s 172.25.21.3
ping_interval=3

master_ip_failover_script= /script/masterha/master_ip_failover

shutdown_script= /script/masterha/power_manager

report_script= /script/masterha/send_report

master_ip_online_change_script= /script/masterha/master_ip_online_change

manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log

[server1]
hostname=172.25.21.2

[server2]
hostname=172.25.21.3
candidate_master=1

[server3]
hostname=172.25.21.4
no_master=1

[root@server4 masterha]# mkdir app1

mkari app1

vim
默认1是master
23是从

预设置2为备选master（假如1挂了）

源码的binlog目录是data；var是rpm安装的目录

vim repl

复制的用户是rpl_user

ssh-skey
[root@server4 masterha]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:x7WOUaGPvmAg8cpe0cin3aEYq5N1Zefv6m70lMtFyOs root@server4
The key’s randomart image is:
±–[RSA 2048]----+
| . |
| . . |
| . . o. . |
| + o .o=…o .|
| . B Sooo + |
| . o.X.= =o + .|
| oo=.= +… o |
| .oo . . … E |
| o. .++o. |
±—[SHA256]-----+

免密123

[root@server4 masterha]# ssh-copy-id server1
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys
root@server1’s password:

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘server1’”
and check to make sure that only the key(s) you wanted were added.

[root@server4 masterha]# ssh-copy-id server2
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys
root@server2’s password:

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘server2’”
and check to make sure that only the key(s) you wanted were added.

[root@server4 masterha]# ssh-copy-id server3
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed – if you are prompted now it is to install the new keys
root@server3’s password:

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘server3’”
and check to make sure that only the key(s) you wanted were added.

指定配置文件
（里面的错误：主从之间也需要免密）

[root@server4 masterha]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Sun Apr 11 13:01:42 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 13:01:42 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:01:42 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:01:42 2021 - [info] Starting SSH connection tests…
Sun Apr 11 13:01:43 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Sun Apr 11 13:01:42 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.3(172.25.21.3:22)…
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Sun Apr 11 13:01:42 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.3(172.25.21.3:22) failed!
Sun Apr 11 13:01:43 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Sun Apr 11 13:01:43 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.2(172.25.21.2:22)…
Warning: Permanently added ‘172.25.21.2’ (ECDSA) to the list of known hosts.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Sun Apr 11 13:01:43 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.2(172.25.21.2:22) failed!
Sun Apr 11 13:01:44 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Sun Apr 11 13:01:43 2021 - [debug] Connecting via SSH from root@172.25.21.4(172.25.21.4:22) to root@172.25.21.2(172.25.21.2:22)…
Warning: Permanently added ‘172.25.21.2’ (ECDSA) to the list of known hosts.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Sun Apr 11 13:01:43 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.21.4(172.25.21.4:22) to root@172.25.21.2(172.25.21.2:22) failed!
SSH Configuration Check Failed!
at /usr/bin/masterha_check_ssh line 44.

123之间免密
scp 给123

[root@server4 masterha]# cd
[root@server4 ~]# scp -r .ssh/ server1:
known_hosts 100% 543 736.6KB/s 00:00
id_rsa 100% 1679 2.7MB/s 00:00
id_rsa.pub 100% 394 717.5KB/s 00:00
[root@server4 ~]# scp -r .ssh/ server2:
known_hosts 100% 543 698.7KB/s 00:00
id_rsa 100% 1679 2.5MB/s 00:00
id_rsa.pub 100% 394 732.6KB/s 00:00
[root@server4 ~]# scp -r .ssh/ server3:
known_hosts 100% 543 731.7KB/s 00:00
id_rsa 100% 1679 2.6MB/s 00:00
id_rsa.pub 100% 394 658.3KB/s 00:00

这时，server1可以免密登陆server2
[root@server1 ~]# ssh server2
Last login: Sun Apr 11 09:09:42 2021 from foundation21.ilt.example.com
[root@server2 ~]#

[root@server1 ~]# ssh server3
Last login: Sun Apr 11 09:09:28 2021 from foundation21.ilt.example.com
[root@server3 ~]#

同步免密

check

检测主从复制是否完备

[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Sun Apr 11 13:06:46 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 13:06:46 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:06:46 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:06:46 2021 - [info] Starting SSH connection tests…
Sun Apr 11 13:06:47 2021 - [debug]
Sun Apr 11 13:06:46 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.3(172.25.21.3:22)…
Sun Apr 11 13:06:46 2021 - [debug] ok.
Sun Apr 11 13:06:46 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.4(172.25.21.4:22)…
Sun Apr 11 13:06:47 2021 - [debug] ok.
Sun Apr 11 13:06:48 2021 - [debug]
Sun Apr 11 13:06:47 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.2(172.25.21.2:22)…
Sun Apr 11 13:06:47 2021 - [debug] ok.
Sun Apr 11 13:06:47 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.4(172.25.21.4:22)…
Sun Apr 11 13:06:47 2021 - [debug] ok.
Sun Apr 11 13:06:49 2021 - [debug]
Sun Apr 11 13:06:47 2021 - [debug] Connecting via SSH from root@172.25.21.4(172.25.21.4:22) to root@172.25.21.2(172.25.21.2:22)…
Sun Apr 11 13:06:47 2021 - [debug] ok.
Sun Apr 11 13:06:47 2021 - [debug] Connecting via SSH from root@172.25.21.4(172.25.21.4:22) to root@172.25.21.3(172.25.21.3:22)…
Sun Apr 11 13:06:48 2021 - [debug] ok.
Sun Apr 11 13:06:49 2021 - [info] All SSH connection tests passed successfully.

1：
管理员只有localhost
grant
刷新

[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf
Sun Apr 11 13:07:53 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 13:07:53 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:07:53 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:07:53 2021 - [info] MHA::MasterMonitor version 0.58.
Sun Apr 11 13:07:53 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.21.2(172.25.21.2:3306) :1045:Access denied for user ‘root’@‘server4’ (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Sun Apr 11 13:07:53 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.21.4(172.25.21.4:3306) :1045:Access denied for user ‘root’@‘server5’ (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Sun Apr 11 13:07:53 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Sun Apr 11 13:07:53 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Sun Apr 11 13:07:53 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.21.3(172.25.21.3:3306) :1045:Access denied for user ‘root’@‘server4’ (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Sun Apr 11 13:07:53 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Sun Apr 11 13:07:54 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Sun Apr 11 13:07:54 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
Sun Apr 11 13:07:54 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Sun Apr 11 13:07:54 2021 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

[root@server1 ~]# mysql -pwestos
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 10
Server version: 5.7.31-log Source distribution

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> alter user root@’%’ identified by ‘westos’;
ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’%’

mysql> grant all on . to root@’%’ identified by ‘westos’;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app.cnf
Sun Apr 11 13:15:06 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 13:15:06 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:15:06 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 13:15:06 2021 - [info] MHA::MasterMonitor version 0.58.
Sun Apr 11 13:15:07 2021 - [info] GTID failover mode = 1
Sun Apr 11 13:15:07 2021 - [info] Dead Servers:
Sun Apr 11 13:15:07 2021 - [info] Alive Servers:
Sun Apr 11 13:15:07 2021 - [info] 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 13:15:07 2021 - [info] 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 13:15:07 2021 - [info] 172.25.21.4(172.25.21.4:3306)
Sun Apr 11 13:15:07 2021 - [info] Alive Slaves:
Sun Apr 11 13:15:07 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 13:15:07 2021 - [info] GTID ON
Sun Apr 11 13:15:07 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 13:15:07 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Apr 11 13:15:07 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 13:15:07 2021 - [info] GTID ON
Sun Apr 11 13:15:07 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 13:15:07 2021 - [info] Not candidate for the new Master (no_master is set)
Sun Apr 11 13:15:07 2021 - [info] Current Alive Master: 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 13:15:07 2021 - [info] Checking slave configurations…
Sun Apr 11 13:15:07 2021 - [info] read_only=1 is not set on slave 172.25.21.3(172.25.21.3:3306).
Sun Apr 11 13:15:07 2021 - [info] read_only=1 is not set on slave 172.25.21.4(172.25.21.4:3306).
Sun Apr 11 13:15:07 2021 - [info] Checking replication filtering settings…
Sun Apr 11 13:15:07 2021 - [info] binlog_do_db= , binlog_ignore_db=
Sun Apr 11 13:15:07 2021 - [info] Replication filtering check ok.
Sun Apr 11 13:15:07 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sun Apr 11 13:15:07 2021 - [info] Checking SSH publickey authentication settings on the current master…
Sun Apr 11 13:15:07 2021 - [info] HealthCheck: SSH to 172.25.21.2 is reachable.
Sun Apr 11 13:15:07 2021 - [info]
172.25.21.2(172.25.21.2:3306) (current master)
±-172.25.21.3(172.25.21.3:3306)
±-172.25.21.4(172.25.21.4:3306)

Sun Apr 11 13:15:07 2021 - [info] Checking replication health on 172.25.21.3…
Sun Apr 11 13:15:07 2021 - [info] ok.
Sun Apr 11 13:15:07 2021 - [info] Checking replication health on 172.25.21.4…
Sun Apr 11 13:15:07 2021 - [info] ok.
Sun Apr 11 13:15:07 2021 - [warning] master_ip_failover_script is not defined.
Sun Apr 11 13:15:07 2021 - [warning] shutdown_script is not defined.
Sun Apr 11 13:15:07 2021 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

MHA高可用

手动切换

4：
切到2主机
YES
YES（新的master）
YES
结束

现在的master是2

[root@server4 ~]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.21.3 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Sun Apr 11 14:15:31 2021 - [info] MHA::MasterRotate version 0.58.
Sun Apr 11 14:15:31 2021 - [info] Starting online master switch…
Sun Apr 11 14:15:31 2021 - [info]
Sun Apr 11 14:15:31 2021 - [info] * Phase 1: Configuration Check Phase…
Sun Apr 11 14:15:31 2021 - [info]
Sun Apr 11 14:15:31 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 14:15:31 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:15:31 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:15:32 2021 - [info] GTID failover mode = 1
Sun Apr 11 14:15:32 2021 - [info] Current Alive Master: 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:15:32 2021 - [info] Alive Slaves:
Sun Apr 11 14:15:32 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:15:32 2021 - [info] GTID ON
Sun Apr 11 14:15:32 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:15:32 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Apr 11 14:15:32 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:15:32 2021 - [info] GTID ON
Sun Apr 11 14:15:32 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:15:32 2021 - [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.21.2(172.25.21.2:3306)? (YES/no): YES
Sun Apr 11 14:15:37 2021 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time…
Sun Apr 11 14:15:37 2021 - [info] ok.
Sun Apr 11 14:15:37 2021 - [info] Checking MHA is not monitoring or doing failover…
Sun Apr 11 14:15:37 2021 - [info] Checking replication health on 172.25.21.3…
Sun Apr 11 14:15:37 2021 - [info] ok.
Sun Apr 11 14:15:37 2021 - [info] Checking replication health on 172.25.21.4…
Sun Apr 11 14:15:37 2021 - [info] ok.
Sun Apr 11 14:15:37 2021 - [info] 172.25.21.3 can be new master.
Sun Apr 11 14:15:37 2021 - [info]
From:
172.25.21.2(172.25.21.2:3306) (current master)
±-172.25.21.3(172.25.21.3:3306)
±-172.25.21.4(172.25.21.4:3306)

To:
172.25.21.3(172.25.21.3:3306) (new master)
±-172.25.21.4(172.25.21.4:3306)
±-172.25.21.2(172.25.21.2:3306)

Starting master switch from 172.25.21.2(172.25.21.2:3306) to 172.25.21.3(172.25.21.3:3306)? (yes/NO): YES
Sun Apr 11 14:15:44 2021 - [info] Checking whether 172.25.21.3(172.25.21.3:3306) is ok for the new master…
Sun Apr 11 14:15:44 2021 - [info] ok.
Sun Apr 11 14:15:44 2021 - [info] 172.25.21.2(172.25.21.2:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Sun Apr 11 14:15:44 2021 - [info] 172.25.21.2(172.25.21.2:3306): Resetting slave pointing to the dummy host.
Sun Apr 11 14:15:44 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Apr 11 14:15:44 2021 - [info]
Sun Apr 11 14:15:44 2021 - [info] * Phase 2: Rejecting updates Phase…
Sun Apr 11 14:15:44 2021 - [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
Sun Apr 11 14:15:48 2021 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Sun Apr 11 14:15:48 2021 - [info] Executing FLUSH TABLES WITH READ LOCK…
Sun Apr 11 14:15:48 2021 - [info] ok.
Sun Apr 11 14:15:48 2021 - [info] Orig master binlog:pos is binlog.000002:1373.
Sun Apr 11 14:15:48 2021 - [info] Waiting to execute all relay logs on 172.25.21.3(172.25.21.3:3306)…
Sun Apr 11 14:15:48 2021 - [info] master_pos_wait(binlog.000002:1373) completed on 172.25.21.3(172.25.21.3:3306). Executed 0 events.
Sun Apr 11 14:15:48 2021 - [info] done.
Sun Apr 11 14:15:48 2021 - [info] Getting new master’s binlog name and position…
Sun Apr 11 14:15:48 2021 - [info] binlog.000002:1981
Sun Apr 11 14:15:48 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=‘172.25.21.3’, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘xxx’;
Sun Apr 11 14:15:48 2021 - [info]
Sun Apr 11 14:15:48 2021 - [info] * Switching slaves in parallel…
Sun Apr 11 14:15:48 2021 - [info]
Sun Apr 11 14:15:48 2021 - [info] – Slave switch on host 172.25.21.4(172.25.21.4:3306) started, pid: 14392
Sun Apr 11 14:15:48 2021 - [info]
Sun Apr 11 14:15:49 2021 - [info] Log messages from 172.25.21.4 …
Sun Apr 11 14:15:49 2021 - [info]
Sun Apr 11 14:15:48 2021 - [info] Waiting to execute all relay logs on 172.25.21.4(172.25.21.4:3306)…
Sun Apr 11 14:15:48 2021 - [info] master_pos_wait(binlog.000002:1373) completed on 172.25.21.4(172.25.21.4:3306). Executed 0 events.
Sun Apr 11 14:15:48 2021 - [info] done.
Sun Apr 11 14:15:48 2021 - [info] Resetting slave 172.25.21.4(172.25.21.4:3306) and starting replication from the new master 172.25.21.3(172.25.21.3:3306)…
Sun Apr 11 14:15:49 2021 - [info] Executed CHANGE MASTER.
Sun Apr 11 14:15:49 2021 - [info] Slave started.
Sun Apr 11 14:15:49 2021 - [info] End of log messages from 172.25.21.4 …
Sun Apr 11 14:15:49 2021 - [info]
Sun Apr 11 14:15:49 2021 - [info] – Slave switch on host 172.25.21.4(172.25.21.4:3306) succeeded.
Sun Apr 11 14:15:49 2021 - [info] Unlocking all tables on the orig master:
Sun Apr 11 14:15:49 2021 - [info] Executing UNLOCK TABLES…
Sun Apr 11 14:15:49 2021 - [info] ok.
Sun Apr 11 14:15:49 2021 - [info] Starting orig master as a new slave…
Sun Apr 11 14:15:49 2021 - [info] Resetting slave 172.25.21.2(172.25.21.2:3306) and starting replication from the new master 172.25.21.3(172.25.21.3:3306)…
Sun Apr 11 14:15:50 2021 - [info] Executed CHANGE MASTER.
Sun Apr 11 14:15:50 2021 - [info] Slave started.
Sun Apr 11 14:15:50 2021 - [info] All new slave servers switched successfully.
Sun Apr 11 14:15:50 2021 - [info]
Sun Apr 11 14:15:50 2021 - [info] * Phase 5: New master cleanup phase…
Sun Apr 11 14:15:50 2021 - [info]
Sun Apr 11 14:15:50 2021 - [info] 172.25.21.3: Resetting slave info succeeded.
Sun Apr 11 14:15:50 2021 - [info] Switching master to 172.25.21.3(172.25.21.3:3306) completed successfully.

1：
show slave
1现在作为slave
已经没有master信息
[root@server1 ~]# mysql -pwestos
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 19
Server version: 5.7.31-log Source distribution

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;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1981
Relay_Log_File: server1-relay-bin.000002
Relay_Log_Pos: 953
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1981
Relay_Log_Space: 1154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 4992f82e-9a77-11eb-b4d8-525400e26f27
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

[root@server3 ~]# mysql -pwestos
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 16
Server version: 5.7.31-log Source distribution

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;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1981
Relay_Log_File: server3-relay-bin.000002
Relay_Log_Pos: 953
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1981
Relay_Log_Space: 1154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 4992f82e-9a77-11eb-b4d8-525400e26f27
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
5b1b1aee-9a78-11eb-a07e-5254003540d0:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

2：
stop master挂掉
mysqld stop

[root@server2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

4：
master2挂掉，切会mastrer1
YES
YES
YES
结束

最后一个参数：避免频繁切换

[root@server4 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app.cnf --dead_master_host=172.25.21.3 --dead_master_port=3306 --new_master_host=172.25.21.2 --new_master_port=3306 --ignore_last_failover
–dead_master_ip=<dead_master_ip> is not set. Using 172.25.21.3.
Sun Apr 11 14:23:16 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 14:23:16 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:23:16 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:23:16 2021 - [info] MHA::MasterFailover version 0.58.
Sun Apr 11 14:23:16 2021 - [info] Starting master failover.
Sun Apr 11 14:23:16 2021 - [info]
Sun Apr 11 14:23:16 2021 - [info] * Phase 1: Configuration Check Phase…
Sun Apr 11 14:23:16 2021 - [info]
Sun Apr 11 14:23:17 2021 - [info] GTID failover mode = 1
Sun Apr 11 14:23:17 2021 - [info] Dead Servers:
Sun Apr 11 14:23:17 2021 - [info] 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:23:17 2021 - [info] Checking master reachability via MySQL(double check)…
Sun Apr 11 14:23:17 2021 - [info] ok.
Sun Apr 11 14:23:17 2021 - [info] Alive Servers:
Sun Apr 11 14:23:17 2021 - [info] 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:23:17 2021 - [info] 172.25.21.4(172.25.21.4:3306)
Sun Apr 11 14:23:17 2021 - [info] Alive Slaves:
Sun Apr 11 14:23:17 2021 - [info] 172.25.21.2(172.25.21.2:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:23:17 2021 - [info] GTID ON
Sun Apr 11 14:23:17 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:23:17 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:23:17 2021 - [info] GTID ON
Sun Apr 11 14:23:17 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:23:17 2021 - [info] Not candidate for the new Master (no_master is set)
Master 172.25.21.3(172.25.21.3:3306) is dead. Proceed? (yes/NO): YES
Sun Apr 11 14:24:01 2021 - [info] Starting GTID based failover.
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] * Phase 2: Dead Master Shutdown Phase…
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] HealthCheck: SSH to 172.25.21.3 is reachable.
Sun Apr 11 14:24:01 2021 - [info] Forcing shutdown so that applications never connect to the current master…
Sun Apr 11 14:24:01 2021 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Sun Apr 11 14:24:01 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Apr 11 14:24:01 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] * Phase 3: Master Recovery Phase…
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase…
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] The latest binary log file/position on all slaves is binlog.000002:1981
Sun Apr 11 14:24:01 2021 - [info] Retrieved Gtid Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3
Sun Apr 11 14:24:01 2021 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Apr 11 14:24:01 2021 - [info] 172.25.21.2(172.25.21.2:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:24:01 2021 - [info] GTID ON
Sun Apr 11 14:24:01 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:24:01 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:24:01 2021 - [info] GTID ON
Sun Apr 11 14:24:01 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:24:01 2021 - [info] Not candidate for the new Master (no_master is set)
Sun Apr 11 14:24:01 2021 - [info] The oldest binary log file/position on all slaves is binlog.000002:1981
Sun Apr 11 14:24:01 2021 - [info] Retrieved Gtid Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3
Sun Apr 11 14:24:01 2021 - [info] Oldest slaves:
Sun Apr 11 14:24:01 2021 - [info] 172.25.21.2(172.25.21.2:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:24:01 2021 - [info] GTID ON
Sun Apr 11 14:24:01 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:24:01 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:24:01 2021 - [info] GTID ON
Sun Apr 11 14:24:01 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:24:01 2021 - [info] Not candidate for the new Master (no_master is set)
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] * Phase 3.3: Determining New Master Phase…
Sun Apr 11 14:24:01 2021 - [info]
Sun Apr 11 14:24:01 2021 - [info] 172.25.21.2 can be new master.
Sun Apr 11 14:24:01 2021 - [info] New master is 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:24:01 2021 - [info] Starting master failover…
Sun Apr 11 14:24:01 2021 - [info]
From:
172.25.21.3(172.25.21.3:3306) (current master)
±-172.25.21.2(172.25.21.2:3306)
±-172.25.21.4(172.25.21.4:3306)

To:
172.25.21.2(172.25.21.2:3306) (new master)
±-172.25.21.4(172.25.21.4:3306)

Starting master switch from 172.25.21.3(172.25.21.3:3306) to 172.25.21.2(172.25.21.2:3306)? (yes/NO): YES
Sun Apr 11 14:24:54 2021 - [info] New master decided manually is 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:24:54 2021 - [info]
Sun Apr 11 14:24:54 2021 - [info] * Phase 3.3: New Master Recovery Phase…
Sun Apr 11 14:24:54 2021 - [info]
Sun Apr 11 14:24:54 2021 - [info] Waiting all logs to be applied…
Sun Apr 11 14:24:54 2021 - [info] done.
Sun Apr 11 14:24:54 2021 - [info] Getting new master’s binlog name and position…
Sun Apr 11 14:24:54 2021 - [info] binlog.000002:1905
Sun Apr 11 14:24:54 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=‘172.25.21.2’, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘xxx’;
Sun Apr 11 14:24:54 2021 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: binlog.000002, 1905, 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Sun Apr 11 14:24:54 2021 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Sun Apr 11 14:24:54 2021 - [info] Setting read_only=0 on 172.25.21.2(172.25.21.2:3306)…
Sun Apr 11 14:24:54 2021 - [info] ok.
Sun Apr 11 14:24:54 2021 - [info] ** Finished master recovery successfully.
Sun Apr 11 14:24:54 2021 - [info] * Phase 3: Master Recovery Phase completed.
Sun Apr 11 14:24:54 2021 - [info]
Sun Apr 11 14:24:54 2021 - [info] * Phase 4: Slaves Recovery Phase…
Sun Apr 11 14:24:54 2021 - [info]
Sun Apr 11 14:24:54 2021 - [info]
Sun Apr 11 14:24:54 2021 - [info] * Phase 4.1: Starting Slaves in parallel…
Sun Apr 11 14:24:54 2021 - [info]
Sun Apr 11 14:24:54 2021 - [info] – Slave recovery on host 172.25.21.4(172.25.21.4:3306) started, pid: 14409. Check tmp log /etc/masterha/app1/172.25.21.4_3306_20210411142316.log if it takes time…
Sun Apr 11 14:24:55 2021 - [info]
Sun Apr 11 14:24:55 2021 - [info] Log messages from 172.25.21.4 …
Sun Apr 11 14:24:55 2021 - [info]
Sun Apr 11 14:24:54 2021 - [info] Resetting slave 172.25.21.4(172.25.21.4:3306) and starting replication from the new master 172.25.21.2(172.25.21.2:3306)…
Sun Apr 11 14:24:54 2021 - [info] Executed CHANGE MASTER.
Sun Apr 11 14:24:54 2021 - [info] Slave started.
Sun Apr 11 14:24:54 2021 - [info] gtid_wait(4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6) completed on 172.25.21.4(172.25.21.4:3306). Executed 0 events.
Sun Apr 11 14:24:55 2021 - [info] End of log messages from 172.25.21.4.
Sun Apr 11 14:24:55 2021 - [info] – Slave on host 172.25.21.4(172.25.21.4:3306) started.
Sun Apr 11 14:24:55 2021 - [info] All new slave servers recovered successfully.
Sun Apr 11 14:24:55 2021 - [info]
Sun Apr 11 14:24:55 2021 - [info] * Phase 5: New master cleanup phase…
Sun Apr 11 14:24:55 2021 - [info]
Sun Apr 11 14:24:55 2021 - [info] Resetting slave info on the new master…
Sun Apr 11 14:24:55 2021 - [info] 172.25.21.2: Resetting slave info succeeded.
Sun Apr 11 14:24:55 2021 - [info] Master failover to 172.25.21.2(172.25.21.2:3306) completed successfully.
Sun Apr 11 14:24:55 2021 - [info]

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

app: MySQL Master failover 172.25.21.3(172.25.21.3:3306) to 172.25.21.2(172.25.21.2:3306) succeeded

Master 172.25.21.3(172.25.21.3:3306) is down!

Check MHA Manager logs at server4 for details.

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

会在app1生成相应的锁定文件app。failover。complete

[root@server4 ~]# cd /etc/masterha/app1/
[root@server4 app1]# ls
app.failover.complete
[root@server4 app1]# ll app.failover.complete
-rw-r–r-- 1 root root 0 Apr 11 14:24 app.failover.complete

在123主机进行show

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;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1905
Relay_Log_File: server3-relay-bin.000002
Relay_Log_Pos: 397
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1905
Relay_Log_Space: 606
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 82c65121-9a76-11eb-9fcc-5254005fb835
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
5b1b1aee-9a78-11eb-a07e-5254003540d0:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

2：（手动恢复数据）
start
change
start slave；
show

[root@server2 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@server2 ~]# mysql -pwestos
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.31-log Source distribution

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.21.2’, master_user=‘repl’,master_password=‘westos’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 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 event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1905
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 397
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1905
Relay_Log_Space: 606
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 82c65121-9a76-11eb-9fcc-5254005fb835
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

自动切换

4：检测ssh repl

[root@server4 app1]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Sun Apr 11 14:31:31 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 14:31:31 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:31:31 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:31:31 2021 - [info] Starting SSH connection tests…
Sun Apr 11 14:31:32 2021 - [debug]
Sun Apr 11 14:31:31 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.3(172.25.21.3:22)…
Sun Apr 11 14:31:31 2021 - [debug] ok.
Sun Apr 11 14:31:31 2021 - [debug] Connecting via SSH from root@172.25.21.2(172.25.21.2:22) to root@172.25.21.4(172.25.21.4:22)…
Sun Apr 11 14:31:32 2021 - [debug] ok.
Sun Apr 11 14:31:33 2021 - [debug]
Sun Apr 11 14:31:32 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.2(172.25.21.2:22)…
Sun Apr 11 14:31:32 2021 - [debug] ok.
Sun Apr 11 14:31:32 2021 - [debug] Connecting via SSH from root@172.25.21.3(172.25.21.3:22) to root@172.25.21.4(172.25.21.4:22)…
Sun Apr 11 14:31:32 2021 - [debug] ok.
Sun Apr 11 14:31:34 2021 - [debug]
Sun Apr 11 14:31:32 2021 - [debug] Connecting via SSH from root@172.25.21.4(172.25.21.4:22) to root@172.25.21.2(172.25.21.2:22)…
Sun Apr 11 14:31:32 2021 - [debug] ok.
Sun Apr 11 14:31:32 2021 - [debug] Connecting via SSH from root@172.25.21.4(172.25.21.4:22) to root@172.25.21.3(172.25.21.3:22)…
Sun Apr 11 14:31:33 2021 - [debug] ok.
Sun Apr 11 14:31:34 2021 - [info] All SSH connection tests passed successfully.
[root@server4 app1]# masterha_check_repl --conf=/etc/masterha/app.cnf
Sun Apr 11 14:31:45 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 14:31:45 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:31:45 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:31:45 2021 - [info] MHA::MasterMonitor version 0.58.
Sun Apr 11 14:31:46 2021 - [info] GTID failover mode = 1
Sun Apr 11 14:31:46 2021 - [info] Dead Servers:
Sun Apr 11 14:31:46 2021 - [info] Alive Servers:
Sun Apr 11 14:31:46 2021 - [info] 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:31:46 2021 - [info] 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:31:46 2021 - [info] 172.25.21.4(172.25.21.4:3306)
Sun Apr 11 14:31:46 2021 - [info] Alive Slaves:
Sun Apr 11 14:31:46 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:31:46 2021 - [info] GTID ON
Sun Apr 11 14:31:46 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:31:46 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Apr 11 14:31:46 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:31:46 2021 - [info] GTID ON
Sun Apr 11 14:31:46 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:31:46 2021 - [info] Not candidate for the new Master (no_master is set)
Sun Apr 11 14:31:46 2021 - [info] Current Alive Master: 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 14:31:46 2021 - [info] Checking slave configurations…
Sun Apr 11 14:31:46 2021 - [info] read_only=1 is not set on slave 172.25.21.3(172.25.21.3:3306).
Sun Apr 11 14:31:46 2021 - [info] read_only=1 is not set on slave 172.25.21.4(172.25.21.4:3306).
Sun Apr 11 14:31:46 2021 - [info] Checking replication filtering settings…
Sun Apr 11 14:31:46 2021 - [info] binlog_do_db= , binlog_ignore_db=
Sun Apr 11 14:31:46 2021 - [info] Replication filtering check ok.
Sun Apr 11 14:31:46 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sun Apr 11 14:31:46 2021 - [info] Checking SSH publickey authentication settings on the current master…
Sun Apr 11 14:31:46 2021 - [info] HealthCheck: SSH to 172.25.21.2 is reachable.
Sun Apr 11 14:31:46 2021 - [info]
172.25.21.2(172.25.21.2:3306) (current master)
±-172.25.21.3(172.25.21.3:3306)
±-172.25.21.4(172.25.21.4:3306)

Sun Apr 11 14:31:46 2021 - [info] Checking replication health on 172.25.21.3…
Sun Apr 11 14:31:46 2021 - [info] ok.
Sun Apr 11 14:31:46 2021 - [info] Checking replication health on 172.25.21.4…
Sun Apr 11 14:31:46 2021 - [info] ok.
Sun Apr 11 14:31:46 2021 - [warning] master_ip_failover_script is not defined.
Sun Apr 11 14:31:46 2021 - [warning] shutdown_script is not defined.
Sun Apr 11 14:31:46 2021 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

[root@server4 app1]# masterha_manager --conf=/etc/masterha/app.cnf &
[1] 14451
[root@server4 app1]# Sun Apr 11 14:33:20 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 14:33:20 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:33:20 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…

[root@server4 app1]# ps ax
14451 pts/0 S 0:00 perl /usr/bin/masterha_manager --conf=/etc/masterha/app.cnf

rm -f app。failover。complete（不删除，会阻止切换）

[root@server4 app1]# rm -f app.failover.complete
[root@server4 app1]# ls
app.master_status.health manager.log

1：stop

查看1挂了，2是否是新的master

[root@server1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

[root@server4 app1]# Sun Apr 11 14:36:05 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 14:36:05 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:36:05 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
^C
[1]+ Done masterha_manager --conf=/etc/masterha/app.cnf

23：查看情况

[root@server2 ~]# mysql -pwestos
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 14
Server version: 5.7.31-log Source distribution

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

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;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 234
Relay_Log_File: server3-relay-bin.000002
Relay_Log_Pos: 353
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 234
Relay_Log_Space: 554
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 4992f82e-9a77-11eb-b4d8-525400e26f27
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
5b1b1aee-9a78-11eb-a07e-5254003540d0:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

1：手动恢复
change master to
start slave；
show

[root@server1 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@server1 ~]# mysql -pwestos
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.31-log Source distribution

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.21.3’, master_user=‘repl’,master_password=‘westos’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 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 event
Master_Host: 172.25.21.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 234
Relay_Log_File: server1-relay-bin.000002
Relay_Log_Pos: 353
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 234
Relay_Log_Space: 554
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 4992f82e-9a77-11eb-b4d8-525400e26f27
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

加入脚本

[root@foundation21 ~]# scp master_ip_* server4:/usr/local/bin
root@server4’s password:
master_ip_failover 100% 2156 3.2MB/s 00:00
master_ip_online_change 100% 3813 8.9MB/s 00:00

4:
加点脚本
power：电源管理
report：发送邮件

[root@server4 app1]# cd /usr/local/bin/
[root@server4 bin]# ls
master_ip_failover master_ip_online_change

+x权限

[root@server4 bin]# ll
total 8
-rw-r–r-- 1 root root 2156 Apr 11 14:44 master_ip_failover
-rw-r–r-- 1 root root 3813 Apr 11 14:44 master_ip_online_change
[root@server4 bin]# chmod +x master_ip_failover
[root@server4 bin]# chmod +x master_ip_online_change
[root@server4 bin]# ll
total 8
-rwxr-xr-x 1 root root 2156 Apr 11 14:44 master_ip_failover
-rwxr-xr-x 1 root root 3813 Apr 11 14:44 master_ip_online_change

切换VIP
vim 改ip
（2个脚本：手动自动）

[root@server4 bin]# vim master_ip_failover
my $vip = ‘172.25.21.100/24’;
[root@server4 bin]# vim master_ip_online_change
my $vip = ‘172.25.21.100/24’;

vim app.cnf
改了2出

[root@server4 bin]# vim /etc/masterha/app.cnf
server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir= /usr/local/mysql/data
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.21.2 -s 172.25.21.3
ping_interval=3
master_ip_failover_script= /usr/local/bin/master_ip_failover

shutdown_script= /script/masterha/power_manager

report_script= /script/masterha/send_report

master_ip_online_change_script= /usr/local/bin/master_ip_online_change
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log

[server1]
hostname=172.25.21.2

[server2]
hostname=172.25.21.3
candidate_master=1

[server3]
hostname=172.25.21.4
no_master=1

测试：
master现在是2
[root@server1 ~]# mysql -pwestos
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31-log Source distribution

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;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 234
Relay_Log_File: server1-relay-bin.000002
Relay_Log_Pos: 353
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 234
Relay_Log_Space: 554
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 4992f82e-9a77-11eb-b4d8-525400e26f27
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

4：

2：添加ip addr

[root@server2 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0
BOOTPROTO=static
DEVICE=eth0
ONBOOT=yes
IPADDR1=172.25.21.3
PREFIX=24
GATEWAY=172.25.21.250
DNS1=114.114.114.114
IPADDR2=172.25.21.100
[root@server2 ~]# systemctl restart network

21：hup
可以访问mysql

[root@foundation21 ~]# mysql -h 172.25.21.100 -u root -pwestos
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 17
Server version: 5.7.31-log Source distribution

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.

（记得县删除那个日志）
[root@server4 app1]# rm -fr app.failover.complete

4：1——>到2
YES
YES
结束

[root@server4 app1]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.21.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Sun Apr 11 14:59:18 2021 - [info] MHA::MasterRotate version 0.58.
Sun Apr 11 14:59:18 2021 - [info] Starting online master switch…
Sun Apr 11 14:59:18 2021 - [info]
Sun Apr 11 14:59:18 2021 - [info] * Phase 1: Configuration Check Phase…
Sun Apr 11 14:59:18 2021 - [info]
Sun Apr 11 14:59:18 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 14:59:18 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:59:18 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 14:59:19 2021 - [info] GTID failover mode = 1
Sun Apr 11 14:59:19 2021 - [info] Current Alive Master: 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:59:19 2021 - [info] Alive Slaves:
Sun Apr 11 14:59:19 2021 - [info] 172.25.21.2(172.25.21.2:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:59:19 2021 - [info] GTID ON
Sun Apr 11 14:59:19 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:59:19 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 14:59:19 2021 - [info] GTID ON
Sun Apr 11 14:59:19 2021 - [info] Replicating from 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 14:59:19 2021 - [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.21.3(172.25.21.3:3306)? (YES/no): YES
Sun Apr 11 14:59:22 2021 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time…
Sun Apr 11 14:59:22 2021 - [info] ok.
Sun Apr 11 14:59:22 2021 - [info] Checking MHA is not monitoring or doing failover…
Sun Apr 11 14:59:22 2021 - [info] Checking replication health on 172.25.21.2…
Sun Apr 11 14:59:22 2021 - [info] ok.
Sun Apr 11 14:59:22 2021 - [info] Checking replication health on 172.25.21.4…
Sun Apr 11 14:59:22 2021 - [info] ok.
Sun Apr 11 14:59:22 2021 - [info] 172.25.21.2 can be new master.
Sun Apr 11 14:59:22 2021 - [info]
From:
172.25.21.3(172.25.21.3:3306) (current master)
±-172.25.21.2(172.25.21.2:3306)
±-172.25.21.4(172.25.21.4:3306)

To:
172.25.21.2(172.25.21.2:3306) (new master)
±-172.25.21.4(172.25.21.4:3306)
±-172.25.21.3(172.25.21.3:3306)

Starting master switch from 172.25.21.3(172.25.21.3:3306) to 172.25.21.2(172.25.21.2:3306)? (yes/NO): YES
Sun Apr 11 14:59:32 2021 - [info] Checking whether 172.25.21.2(172.25.21.2:3306) is ok for the new master…
Sun Apr 11 14:59:32 2021 - [info] ok.
Sun Apr 11 14:59:32 2021 - [info] 172.25.21.3(172.25.21.3:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Sun Apr 11 14:59:32 2021 - [info] 172.25.21.3(172.25.21.3:3306): Resetting slave pointing to the dummy host.
Sun Apr 11 14:59:32 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Apr 11 14:59:32 2021 - [info]
Sun Apr 11 14:59:32 2021 - [info] * Phase 2: Rejecting updates Phase…
Sun Apr 11 14:59:32 2021 - [info]
Sun Apr 11 14:59:32 2021 - [info] Executing master ip online change script to disable write on the current master:
Sun Apr 11 14:59:32 2021 - [info] /usr/local/bin/master_ip_online_change --command=stop --orig_master_host=172.25.21.3 --orig_master_ip=172.25.21.3 --orig_master_port=3306 --orig_master_user=‘root’ --new_master_host=172.25.21.2 --new_master_ip=172.25.21.2 --new_master_port=3306 --new_master_user=‘root’ --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx

Disabling the VIP - 172.25.21.100/24 on old master: 172.25.21.3

RTNETLINK answers: Cannot assign requested address
Sun Apr 11 14:59:33 2021 - [info] ok.
Sun Apr 11 14:59:33 2021 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Sun Apr 11 14:59:33 2021 - [info] Executing FLUSH TABLES WITH READ LOCK…
Sun Apr 11 14:59:33 2021 - [info] ok.
Sun Apr 11 14:59:33 2021 - [info] Orig master binlog:pos is binlog.000003:234.
Sun Apr 11 14:59:33 2021 - [info] Waiting to execute all relay logs on 172.25.21.2(172.25.21.2:3306)…
Sun Apr 11 14:59:33 2021 - [info] master_pos_wait(binlog.000003:234) completed on 172.25.21.2(172.25.21.2:3306). Executed 0 events.
Sun Apr 11 14:59:33 2021 - [info] done.
Sun Apr 11 14:59:33 2021 - [info] Getting new master’s binlog name and position…
Sun Apr 11 14:59:33 2021 - [info] binlog.000003:230
Sun Apr 11 14:59:33 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=‘172.25.21.2’, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=‘repl’, MASTER_PASSWORD=‘xxx’;
Sun Apr 11 14:59:33 2021 - [info] Executing master ip online change script to allow write on the new master:
Sun Apr 11 14:59:33 2021 - [info] /usr/local/bin/master_ip_online_change --command=start --orig_master_host=172.25.21.3 --orig_master_ip=172.25.21.3 --orig_master_port=3306 --orig_master_user=‘root’ --new_master_host=172.25.21.2 --new_master_ip=172.25.21.2 --new_master_port=3306 --new_master_user=‘root’ --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx

Enabling the VIP - 172.25.21.100/24 on new master: 172.25.21.2

Sun Apr 11 14:59:33 2021 - [info] ok.
Sun Apr 11 14:59:33 2021 - [info]
Sun Apr 11 14:59:33 2021 - [info] * Switching slaves in parallel…
Sun Apr 11 14:59:33 2021 - [info]
Sun Apr 11 14:59:33 2021 - [info] – Slave switch on host 172.25.21.4(172.25.21.4:3306) started, pid: 14647
Sun Apr 11 14:59:33 2021 - [info]
Sun Apr 11 14:59:34 2021 - [info] Log messages from 172.25.21.4 …
Sun Apr 11 14:59:34 2021 - [info]
Sun Apr 11 14:59:33 2021 - [info] Waiting to execute all relay logs on 172.25.21.4(172.25.21.4:3306)…
Sun Apr 11 14:59:33 2021 - [info] master_pos_wait(binlog.000003:234) completed on 172.25.21.4(172.25.21.4:3306). Executed 0 events.
Sun Apr 11 14:59:33 2021 - [info] done.
Sun Apr 11 14:59:33 2021 - [info] Resetting slave 172.25.21.4(172.25.21.4:3306) and starting replication from the new master 172.25.21.2(172.25.21.2:3306)…
Sun Apr 11 14:59:33 2021 - [info] Executed CHANGE MASTER.
Sun Apr 11 14:59:33 2021 - [info] Slave started.
Sun Apr 11 14:59:34 2021 - [info] End of log messages from 172.25.21.4 …
Sun Apr 11 14:59:34 2021 - [info]
Sun Apr 11 14:59:34 2021 - [info] – Slave switch on host 172.25.21.4(172.25.21.4:3306) succeeded.
Sun Apr 11 14:59:34 2021 - [info] Unlocking all tables on the orig master:
Sun Apr 11 14:59:34 2021 - [info] Executing UNLOCK TABLES…
Sun Apr 11 14:59:34 2021 - [info] ok.
Sun Apr 11 14:59:34 2021 - [info] Starting orig master as a new slave…
Sun Apr 11 14:59:34 2021 - [info] Resetting slave 172.25.21.3(172.25.21.3:3306) and starting replication from the new master 172.25.21.2(172.25.21.2:3306)…
Sun Apr 11 14:59:34 2021 - [info] Executed CHANGE MASTER.
Sun Apr 11 14:59:34 2021 - [info] Slave started.
Sun Apr 11 14:59:34 2021 - [info] All new slave servers switched successfully.
Sun Apr 11 14:59:34 2021 - [info]
Sun Apr 11 14:59:34 2021 - [info] * Phase 5: New master cleanup phase…
Sun Apr 11 14:59:34 2021 - [info]
Sun Apr 11 14:59:34 2021 - [info] 172.25.21.2: Resetting slave info succeeded.
Sun Apr 11 14:59:34 2021 - [info] Switching master to 172.25.21.2(172.25.21.2:3306) completed successfully.

[root@server1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:00:5f:b8:35 brd ff:ff:ff:ff:ff:ff
inet 172.25.21.2/24 brd 172.25.21.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.25.21.100/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::5054:ff:fe5f:b835/64 scope link
valid_lft forever preferred_lft forever

21：mysql pu

4：检测repl

[root@server4 app1]# masterha_check_repl --conf=/etc/masterha/app.cnf
Sun Apr 11 15:05:50 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 15:05:50 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 15:05:50 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 15:05:50 2021 - [info] MHA::MasterMonitor version 0.58.
Sun Apr 11 15:05:51 2021 - [info] GTID failover mode = 1
Sun Apr 11 15:05:51 2021 - [info] Dead Servers:
Sun Apr 11 15:05:51 2021 - [info] Alive Servers:
Sun Apr 11 15:05:51 2021 - [info] 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 15:05:51 2021 - [info] 172.25.21.3(172.25.21.3:3306)
Sun Apr 11 15:05:51 2021 - [info] 172.25.21.4(172.25.21.4:3306)
Sun Apr 11 15:05:51 2021 - [info] Alive Slaves:
Sun Apr 11 15:05:51 2021 - [info] 172.25.21.3(172.25.21.3:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 15:05:51 2021 - [info] GTID ON
Sun Apr 11 15:05:51 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 15:05:51 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Apr 11 15:05:51 2021 - [info] 172.25.21.4(172.25.21.4:3306) Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Sun Apr 11 15:05:51 2021 - [info] GTID ON
Sun Apr 11 15:05:51 2021 - [info] Replicating from 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 15:05:51 2021 - [info] Not candidate for the new Master (no_master is set)
Sun Apr 11 15:05:51 2021 - [info] Current Alive Master: 172.25.21.2(172.25.21.2:3306)
Sun Apr 11 15:05:51 2021 - [info] Checking slave configurations…
Sun Apr 11 15:05:51 2021 - [info] read_only=1 is not set on slave 172.25.21.4(172.25.21.4:3306).
Sun Apr 11 15:05:51 2021 - [info] Checking replication filtering settings…
Sun Apr 11 15:05:51 2021 - [info] binlog_do_db= , binlog_ignore_db=
Sun Apr 11 15:05:51 2021 - [info] Replication filtering check ok.
Sun Apr 11 15:05:51 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sun Apr 11 15:05:51 2021 - [info] Checking SSH publickey authentication settings on the current master…
Sun Apr 11 15:05:51 2021 - [info] HealthCheck: SSH to 172.25.21.2 is reachable.
Sun Apr 11 15:05:51 2021 - [info]
172.25.21.2(172.25.21.2:3306) (current master)
±-172.25.21.3(172.25.21.3:3306)
±-172.25.21.4(172.25.21.4:3306)

Sun Apr 11 15:05:51 2021 - [info] Checking replication health on 172.25.21.3…
Sun Apr 11 15:05:51 2021 - [info] ok.
Sun Apr 11 15:05:51 2021 - [info] Checking replication health on 172.25.21.4…
Sun Apr 11 15:05:51 2021 - [info] ok.
Sun Apr 11 15:05:51 2021 - [info] Checking master_ip_failover_script status:
Sun Apr 11 15:05:51 2021 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.25.21.2 --orig_master_ip=172.25.21.2 --orig_master_port=3306

IN SCRIPT TEST====/sbin/ip addr del 172.25.21.100/24 dev eth0==/sbin/ip addr add 172.25.21.100/24 dev eth0===

Checking the Status of the script… OK
Sun Apr 11 15:05:51 2021 - [info] OK.
Sun Apr 11 15:05:51 2021 - [warning] shutdown_script is not defined.
Sun Apr 11 15:05:51 2021 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

4：
1现在是master
自动切换&
后期防止重复切换，会自动退回

[root@server4 app1]# masterha_manager --conf=/etc/masterha/app.cnf &
[1] 14675
[root@server4 app1]# Sun Apr 11 15:08:15 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 15:08:15 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 15:08:15 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…

1： stop
[root@server1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

2：查看ip

1： start
change
start slave
show

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.21.3’, master_user=‘repl’,master_password=‘westos’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.25.21.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: server1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 150
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master ‘repl@172.25.21.3:3306’ - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 210411 15:19:25
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> stop slave;
Query OK, 0 rows affected (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 event
Master_Host: 172.25.21.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 234
Relay_Log_File: server1-relay-bin.000002
Relay_Log_Pos: 353
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 234
Relay_Log_Space: 554
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 4992f82e-9a77-11eb-b4d8-525400e26f27
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 4992f82e-9a77-11eb-b4d8-525400e26f27:1-3,
82c65121-9a76-11eb-9fcc-5254005fb835:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

4：会生成日志

[root@server4 app1]# ls
app.failover.complete manager.log

[root@server4 app1]# Sun Apr 11 15:08:15 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 15:08:15 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 15:08:15 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
Sun Apr 11 15:10:11 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 11 15:10:11 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf…
Sun Apr 11 15:10:11 2021 - [info] Reading server configuration from /etc/masterha/app.cnf…
^C
[1]+ Done masterha_manager --conf=/etc/masterha/app.cnf

redis

有了makefile
make
u/。make intsall
cp
改名字
systemctl

[root@server1 ~]# tar zxf redis-6.2.1.tar.gz
[root@server1 ~]# cd redis-6.2.1/
[root@server1 redis-6.2.1]# ls
00-RELEASENOTES CONTRIBUTING INSTALL README.md runtest-cluster sentinel.conf TLS.md
BUGS COPYING Makefile redis.conf runtest-moduleapi src utils
CONDUCT deps MANIFESTO runtest runtest-sentinel tests
[root@server1 redis-6.2.1]# make
[root@server1 redis-6.2.1]# ls
00-RELEASENOTES CONTRIBUTING INSTALL README.md runtest-cluster sentinel.conf TLS.md
BUGS COPYING Makefile redis.conf runtest-moduleapi src utils
CONDUCT deps MANIFESTO runtest runtest-sentinel tests
[root@server1 redis-6.2.1]# cd utils/
[root@server1 utils]# ./install_server.sh
Welcome to the redis service installer
This script will help you easily set up a running redis server

This systems seems to use systemd.
Please take a look at the provided example service unit files in this directory, and adapt and install them. Sorry!

。[root@server1 utils]# ls
build-static-symbols.tcl hyperloglog speed-regression.tcl
cluster_fail_time.tcl install_server.sh srandmember
corrupt_rdb.c lru systemd-redis_multiple_servers@.service
create-cluster redis-copy.rb systemd-redis_server.service
generate-command-help.rb redis_init_script tracking_collisions.c
gen-test-certs.sh redis_init_script.tpl whatisdoing.sh
graphs redis-sha1.rb
hashtable releasetools
[root@server1 utils]# cp systemd-redis_server.service /usr/lib/systemd/system/redis_server.service

[root@server1 utils]# vim systemd-redis_multiple_servers@.service

[root@server1 utils]# mkdir /etc/redis

[root@server1 redis-6.2.1]# cp redis.conf /etc/redis/
[root@server1 redis-6.2.1]#

[root@server1 redis-6.2.1]# cd /usr/lib/systemd/system
[root@server1 system]# vim redis_server.service
ExecStart=/usr/local/bin/redis-server /etc/redis/redis.conf

[root@server1 system]# cd /root/redis-6.2.1/utils/
[root@server1 utils]# vim redis_init_script

[root@server1 utils]# cd /usr/lib/systemd/system
[root@server1 system]# vim redis_server.service

#ExecStart=/usr/local/bin/redis-server --supervised systemd --daemonize no

Alternatively, have redis-server load a configuration file:

ExecStart=/usr/local/bin/redis-server /etc/redis/redis.conf --daemonize yes

[root@server1 system]# vim /etc/redis/redis.conf
daemonize yes
bind 0.0.0.0
dir /var/lib/redis

[root@server1 system]# vim redis_server.service

WorkingDirectory=/var/lib/redis

[root@server1 system]# cd /root/redis-6.2.1/utils/
[root@server1 utils]# cp redis_init_script /etc/init.d/redis_6379
[root@server1 utils]# cd /etc/redis/
[root@server1 redis]# ls
redis.conf
[root@server1 redis]# mv redis.conf 6379.conf

[root@server1 redis]# /etc/init.d/redis_6379 start
Starting Redis server…
/etc/init.d/redis_6379: line 28: /usr/local/bin/redis-server: No such file or directory

[root@server1 redis-6.2.1]# make install
cd src && make install
make[1]: Entering directory /root/redis-6.2.1/src' CC Makefile.dep make[1]: Leaving directory/root/redis-6.2.1/src’
make[1]: Entering directory `/root/redis-6.2.1/src’

Hint: It’s a good idea to run ‘make test’ 😉

INSTALL install
INSTALL install
INSTALL install

make[1]: Leaving directory `/root/redis-6.2.1/src’
[root@server1 redis-6.2.1]# /etc/init.d/redis_6379 start
Starting Redis server…
2455:C 11 Apr 2021 17:43:31.932 # Can’t chdir to ‘/var/lib/redis’: No such file or directory
[root@server1 redis-6.2.1]# mkdir /var/lib/redis
[root@server1 redis-6.2.1]# /etc/init.d/redis_6379 start
Starting Redis server…

[root@server1 redis-6.2.1]# netstat -antlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:6379 0.0.0.0:* LISTEN 2467/redis-server 0
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 3264/nginx: master
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 3219/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 3332/master
tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 3264/nginx: master
tcp 0 0 172.25.21.2:40220 172.25.21.3:3306 ESTABLISHED 22841/mysqld
tcp 0 0 172.25.21.2:22 172.25.21.250:33594 ESTABLISHED 3543/sshd: root@pts
tcp6 0 0 :::3306 ::😗 LISTEN 22841/mysqld
tcp6 0 0 :::22 ::😗 LISTEN 3219/sshd
tcp6 0 0 ::1:25 ::😗 LISTEN 3332/master

。。

1：
vim
systemctl

start

安装gcc
tar
make
make intsall

1：
scp 拷贝启动脚本gie2

2：
mkdir varliibredis

vim
stop
start

（（（以上是主从）））））

redis和mysql：
redis的从删除自己的数据，只从主master拷贝数据

1:客户端

redis 服务

异步复制：无法保证slave是否接受到写命令

还会有认证auth

sentinel

客户端和master连接，向其中写入数据
当master和网络隔离时，salve们自己选出一个和master
网络恢复之后，会将之前的master变成slave，并刷新掉之前的所有数据
如何避免刷掉所有的数据？
可以让master故障时，让用户不要写入

config
默认10s，0个slave

1：
vim etc redis。cnf
vim
systemctl
vim conf
1

2：
vim

Outlook(^_^)

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2.7 lnmp架构_MySQL3

慢查询set：超过多长时间认为超时慢查询默认没开开数据库startdata里灭月slow文件生产环境最好激活在全局开启模拟sleep（10）；可以在slow日志里查询到mysqldumpslow备份慢查询mysql路由器一主两从：一个负责写，2个负责读server1（第一个节点：作为引导节点）ONstrartOFF[root@server1 ~]# /etc/init.d/mysqld startStarting MySQL SUCCESS![root@server1
复制链接

扫一扫