本文共 109911 字，大约阅读时间需要 366 分钟。
set：超过多长时间认为超时
慢查询默认没开开数据库
startdata里灭月slow文件
生产环境最好激活
在全局开启 模拟sleep（10）； 可以在slow日志里查询到mysqldumpslow备份慢查询
一主两从：一个负责写，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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select * from performance_schema.replication_group_members;
±--------------------------±----------±------------±------------±-------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | ±--------------------------±----------±------------±------------±-------------+ | group_replication_applier | | | NULL | OFFLINE | ±--------------------------±----------±------------±------------±-------------+ 1 row in set (0.00 sec)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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select * from performance_schema.replication_group_members;
±--------------------------±----------±------------±------------±-------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | ±--------------------------±----------±------------±------------±-------------+ | group_replication_applier | | | NULL | OFFLINE | ±--------------------------±----------±------------±------------±-------------+ 1 row in set (0.00 sec)mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (5.78 sec)mysql> select * from performance_schema.replication_group_members;
±--------------------------±-------------------------------------±------------±------------±-------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | ±--------------------------±-------------------------------------±------------±------------±-------------+ | group_replication_applier | 4d406e30-99dc-11eb-b5c2-525400e26f27 | server2 | 3306 | ONLINE | | group_replication_applier | fb85ede0-99d9-11eb-bc3d-5254005fb835 | server1 | 3306 | ONLINE | ±--------------------------±-------------------------------------±------------±------------±-------------+ 2 rows in set (0.00 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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (3.15 sec)mysql> select * from performance_schema.replication_group_members;
±--------------------------±-------------------------------------±------------±------------±-------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | ±--------------------------±-------------------------------------±------------±------------±-------------+ | group_replication_applier | 34b94e9e-99df-11eb-b783-5254003540d0 | server3 | 3306 | ONLINE | | group_replication_applier | 4d406e30-99dc-11eb-b5c2-525400e26f27 | server2 | 3306 | ONLINE | | group_replication_applier | fb85ede0-99d9-11eb-bc3d-5254005fb835 | server1 | 3306 | ONLINE | ±--------------------------±-------------------------------------±------------±------------±-------------+ 3 rows in set (0.00 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/mysqlroutervim 配置文件
写入内容 （如果是一主两从，那么目的地只能写相应的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/mysqlrouter21主机：
查询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 distributionCopyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show 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> show databases;
±-------------------+ | Database | ±-------------------+ | information_schema | | mysql | | performance_schema | | sys | ±-------------------+ 4 rows in set (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> desc westos.linux;
±---------±------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±---------±------------±-----±----±--------±------+ | username | varchar(6) | NO | | NULL | | | password | varchar(30) | NO | | NULL | | ±---------±------------±-----±----±--------±------+ 2 rows in set (0.00 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 distributionCopyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show 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 distributionCopyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases;
±-------------------+ | Database | ±-------------------+ | information_schema | | mysql | | performance_schema | | sys | | westos | ±-------------------+ 5 rows in set (0.01 sec)mysql> desc westos.linux;
±---------±------------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±---------±------------±-----±----±--------±------+ | username | varchar(6) | NO | | NULL | | | password | varchar(30) | NO | | NULL | | ±---------±------------±-----±----±--------±------+ 2 rows in set (0.00 sec)[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 distributionCopyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases;
±-------------------+ | Database | ±-------------------+ | information_schema | | westos | ±-------------------+ 2 rows in set (0.00 sec)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 distributionCopyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use westos;
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase 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/mysqld123停掉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=0server_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_installationSecuring 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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show master status\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 specifiedmysql> 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=0server_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_installationSecuring 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@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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> change master to master_host=‘172.25.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 specified3：
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=0server_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_installationSecuring 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@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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> change master to master_host=‘172.25.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 specified4：
安装 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:/root1：
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.rpm2：
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.cnfvim 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=3manager_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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> 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.
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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show slave status\G;
*************************** 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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show slave status\G;
*************************** 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 specified2：
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
[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 21 Server version: 5.7.31-log Source distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show slave status\G;
*************************** 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 specified2：（手动恢复数据）
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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> change master to master_host=‘172.25.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 specified4：检测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.cnfrm -f app。failover。complete（不删除，会阻止切换）
[root@server4 app1]# rm -f app.failover.complete
[root@server4 app1]# ls app.master_status.health manager.log1：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.cnf23：查看情况
[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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show slave status\G;
Empty set (0.00 sec)ERROR:
No query specified[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 32 Server version: 5.7.31-log Source distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show slave status\G;
*************************** 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 specified1：手动恢复
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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> change master to master_host=‘172.25.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:004:
加点脚本 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_failovermaster_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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show slave status\G;
*************************** 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 specified4：
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 network21：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 distributionCopyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases;
±-------------------+ | Database | ±-------------------+ | information_schema | | mysql | | performance_schema | | sys | ±-------------------+ 4 rows in set (0.00 sec)（记得县删除那个日志）
[root@server4 app1]# rm -fr app.failover.complete4：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=xxxDisabling 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=xxxEnabling 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 forever21：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=3306IN 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[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 distributionCopyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> change master to master_host=‘172.25.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 specifiedmysql> 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 specified4：会生成日志
[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有了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 serverThis 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
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 installINSTALL installINSTALL 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 systemctlstart
安装gcc
tar make make intsall1：
scp 拷贝启动脚本gie22：
mkdir varliibredisvim
stop start（（（以上是主从）））））
redis和mysql：
redis的从删除自己的数据，只从主master拷贝数据1:客户端
客户端和master连接，向其中写入数据
当master和网络隔离时，salve们自己选出一个和master 网络恢复之后，会将之前的master变成slave，并刷新掉之前的所有数据 如何避免刷掉所有的数据？ 可以让master故障时，让用户不要写入config
默认10s，0个slave1：
vim etc redis。cnf vim systemctl vim conf 12：
vim转载地址：http://opsv.baihongyu.com/