我要评论前言
mysql 集群（mysql cluster）本质是为了解决单节点 mysql 的 性能瓶颈（高并发）、可用性风险（单点故 障）和 数据可靠性（数据丢失）问题，通过多台服务器协同工作，将数据分散 / 复制存储、请求分散处理，最终实 现：
- 高可用（ha）：单个节点故障不影响整体服务；
- 高扩展（scalability）：可通过增加节点提升处理能力；
- 数据一致性：集群内数据保持同步（不同架构一致性级别不同）
一 mysql 在服务器中的部署方法
在企业中 90%的服务器操作系统均为 linux
mysql 版本使用最多的是 mysql5.7 和 mysql8
在企业中对于 mysql 的安装通常用源码编译的方式来进行
官网：http://www.mysql.com
在 linux 下部署 mysql
#下载安装包
[root@mysql-node1 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.3.0.tar.gz
#源码编译
[root@mysql-node1 ~]# dnf install cmake3 gcc git bison openssl-devel ncurses-devel systemd-devel rpcgen.x86_64 libtirpc-devel-1.3.3-9.el9.x86_64.rpm gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils gcc-toolset-12-annobin-annocheck gcc-toolset-12-annobin-plugin-gcc -y
[root@mysql-node1 ~]# tar zxf mysql-boost-8.3.0.tar.gz
[root@mysql-node1 mysql-8.3.0]# mkdir build
[root@mysql-node1 mysql-8.3.0]# cd build/
[root@mysql-node1 build]# cmake3 .. -dcmake_install_prefix=/usr/local/mysql -dmysql_datadir=/data/mysql -dmysql_unix_addr=/data/mysql/mysql.sock -dwith_innobase_storage_engine=1 -dwith_extra_charsets=all -ddefault_charset=utf8mb4 -ddefault_collation=utf8mb4_unicode_ci -dwith_ssl=system -dwith_debug=off
[root@mysql-node1 build]# make
#部署mysql
[root@mysql-node1 build]# make install
[root@mysql-node1 build]# cd /usr/local/mysql/
[root@mysql-node1 mysql]# vim ~/.bash_profile
# .bash_profile
# get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# user specific environment and startup programs
export path=$path:/usr/local/mysql/bin #设置mysql运行环境的环境变量
[root@mysql-node1 mysql]# source ~/.bash_profile
[root@mysql-node1 mysql]# useradd -r -s /sbin/nologin -m mysql
[root@mysql-node1 mysql]# mkdir -p /data/mysql
[root@mysql-node1 mysql]# chown mysql.mysql /data/mysql/
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
server_id=10 | 20 | 30
datadir=/data/mysql
socket=/data/mysql/mysql.sock
#mysql数据结构初始化
[root@mysql-node1 ~]# mysqld --initialize --user=mysql
启动mysql
#采用自编写systemd启动脚本启动 [root@mysql-node1/2/3 ~]# vim /lib/systemd/system/mysqld.service [unit] description=mysql 8.3 database server documentation=man:mysqld(8) after=network.target syslog.target [service] type=notify user=mysql group=mysql execstart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf execreload=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown timeoutsec=300 privatetmp=true limitnofile=65535 limitnproc=65535 restart=on-failure restartsec=5 [install] wantedby=multi-user.target [root@mysql-node1/2/3 ~]# systemctl daemon-reload [root@mysql-node1/2/3 ~]# systemctl enable --now mysqld.service
mysql的安全初始化
[root@mysql-node1 ~]# mysql_secure_installation securing the mysql server deployment. enter password for user root: the existing password for the user account root has expired. please set a new password. new password: re-enter new password: validate password component 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 component? press y|y for yes, any other key for no: no using existing password for root. change the password for root ? ((press y|y for yes, any other key for no) : no remove anonymous users? (press y|y for yes, any other key for no) : y success. normally, root should only be allowed to connect from 'localhost'. this ensures that someone cannot guess at the root password from the network. disallow root login remotely? (press y|y for yes, any other key for no) : y success. by default, mysql comes with a database named 'test' that anyone can access. this is also intended only for testing, and should be removed before moving into a production environment. remove test database and access to it? (press y|y for yes, any other key for no) : y - dropping test database... success. - removing privileges on test database... success. reloading the privilege tables will ensure that all changes made so far will take effect immediately. reload privilege tables now? (press y|y for yes, any other key for no) : y success. all done!
#验证
[root@mysql-node3 ~]# mysql -uroot -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 11
server version: 8.3.0 source distribution
copyright (c) 2000, 2024, oracle and/or its affiliates.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> select @@server_id
-> ;
+-------------+
| @@server_id |
+-------------+
| 30 |
+-------------+
1 row in set (0.00 sec)
二.mysql集群实战--主从复制
编写my.cnf 主配置文件
[root@mysql-node1 ~]# vim /etc/my.cnf [mysqld] server_id=10 datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 log-bin=mysql-bin [root@mysql-node1 ~]# systemctl restart mysqld.service [root@mysql-node2 ~]# vim /etc/my.cnf [mysqld] server_id=20 datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 log-bin=mysql-bin [root@mysql-node2 ~]# systemctl restart mysqld.service [root@mysql-node3 ~]# vim /etc/my.cnf [mysqld] server_id=30 datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 log-bin=mysql-bin [root@mysql-node3 ~]# systemctl restart mysqld.service
建立同步时需要用到的数据库账号
[root@mysql-node1 ~]# mysql -uroot -plee welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 9 server version: 8.3.0 source distribution copyright (c) 2000, 2024, oracle and/or its affiliates. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql> show variables like 'default_authentication_plugin'; +-------------------------------+-----------------------+ | variable_name | value | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+ 1 row in set (0.00 sec) mysql> create user lee@'%' identified with mysql_native_password by 'lee';#建立用户 query ok, 0 rows affected (0.01 sec) mysql> select user from mysql.user; +------------------+ | user | +------------------+ | lee | | mysql.infoschema | | mysql.session | | mysql.sys | | root | +------------------+ 5 rows in set (0.00 sec) mysql> grant replication slave on *.* to lee@'%'; #给用户授权 query ok, 0 rows affected (0.00 sec) mysql> show grants for lee@'%'; +---------------------------------------------+ | grants for lee@% | +---------------------------------------------+ | grant replication slave on *.* to `lee`@`%` | +---------------------------------------------+ 1 row in set (0.00 sec) mysql> #在其他主机里面测试 [root@mysql-node3 ~]# mysql -ulee -plee -h172.25.254.10 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: 8.3.0 source distribution copyright (c) 2000, 2024, oracle and/or its affiliates. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql>
配置数据库一主一从
#在master中查看日志文件名称及id
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 659 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
#在slave主机中
mysql> change master to master_host='172.25.254.10',master_user='lee',master_password='lee',master_log_file='mysql-bin.000001',master_log_pos=659;
query ok, 0 rows affected, 8 warnings (0.05 sec)
mysql> start slave;
query ok, 0 rows affected, 1 warning (0.02 sec)
mysql> start slave;
query ok, 0 rows affected, 2 warnings (0.00 sec)
mysql> show slave status \g;
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.10
master_user: lee
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 659
relay_log_file: mysql-node2-relay-bin.000002
relay_log_pos: 328
relay_master_log_file: mysql-bin.000001
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: 659
relay_log_space: 545
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: 10
master_uuid: cab1d53d-93cf-11f1-af65-000c29e79365
master_info_file: mysql.slave_master_info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: replica has read all relay log; waiting for more updates
master_retry_count: 10
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set:
executed_gtid_set:
auto_position: 0
replicate_rewrite_db:
channel_name:
master_tls_version:
master_public_key_path:
get_master_public_key: 0
network_namespace:
1 row in set, 1 warning (0.00 sec)
error:
no query specified
mysql>
测试
[root@mysql-node1 ~]# mysql -uroot -lee #在master中建立库 mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> create database xia; query ok, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | xia | +--------------------+ 5 rows in set (0.00 sec) [root@mysql-node2 ~]# mysql -uroot -plee #在slave主机中可以实现数据同步 mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | xia | +--------------------+ 5 rows in set (0.00 sec)
向当前一主一从中加入新的数据库
#模拟一主一从中已经存在数据情况
[root@mysql-node1 ~]# mysql -uroot -plee
mysql> create table xia.userlist (
-> name varchar(10) not null,
-> pass varchar(50) not null
-> );
query ok, 0 rows affected (0.01 sec)
mysql> insert into xia.userlist values ('user1','123');
query ok, 1 row affected (0.00 sec)
mysql> select * from xia.userlist;
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
+-------+------+
1 row in set (0.01 sec)
#加入新从库时需要手动拉平数据
[root@mysql-node1 ~]# mysqldump -uroot -p xia > timinglee.sql
[root@mysql-node1 ~]# scp timinglee.sql root@172.25.254.30:/root/
[root@mysql-node3 ~]# mysql -uroot -plee -e "create database xia;"
[root@mysql-node3 ~]# mysql -uroot -plee xia < timinglee.sql
[root@mysql-node3 ~]# mysql -uroot -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 11
server version: 8.3.0 source distribution
copyright (c) 2000, 2024, oracle and/or its affiliates.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> select * from xia.userlist;
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
+-------+------+
1 row in set (0.00 sec)
将新库加入主从结构中
#在master中查看日志的id
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1368 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
#在mysql-node3里面同步
mysql> change master to master_host='172.25.254.10',master_user='lee',master_password='lee',master_log_file='mysql-bin.000001',master_log_pos=1368;
query ok, 0 rows affected, 8 warnings (0.03 sec)
mysql> start slave;
query ok, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\g;
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.10
master_user: lee
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 1368
relay_log_file: mysql-node3-relay-bin.000002
relay_log_pos: 328
relay_master_log_file: mysql-bin.000001
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: 1368
relay_log_space: 545
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: 10
master_uuid: cab1d53d-93cf-11f1-af65-000c29e79365
master_info_file: mysql.slave_master_info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: replica has read all relay log; waiting for more updates
master_retry_count: 10
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set:
executed_gtid_set:
auto_position: 0
replicate_rewrite_db:
channel_name:
master_tls_version:
master_public_key_path:
get_master_public_key: 0
network_namespace:
1 row in set, 1 warning (0.00 sec)
error:
no query specified
测试一主两从
#在master中建立数据
mysql> insert into timinglee.userlist values ('user2','abc');
#在新加入的slave中查看信息
mysql> select * from timinglee.userlist;
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
| user2 | abc |
+-------+------+
2 rows in set (0.00 sec)三.mysql主从架构中的使用技巧及优化
延迟复制
#在指定需要延迟同步的slave主机中，如果主机中安装数据库的版本是8以上
mysql> stop replica
-> ;
query ok, 0 rows affected (0.01 sec)
mysql> change replication source to source_delay=30;
query ok, 0 rows affected (0.02 sec)
mysql> start replica
-> ;
mysql> exit
bye
[root@mysql-node3 ~]# mysql -uroot -plee -e "show slave status\g;" | grep sql_delay
mysql: [warning] using a password on the command line interface can be insecure.
sql_delay: 30
#在master主机中对数据进行更改
mysql> delete from xia.userlist where name='user2';
mysql> select * from xia.userlist;
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
+-------+------+
1 row in set (0.00 sec)
#在未被延迟的slave数据库中查看是否数据操作动作被同步
mysql> select * from xia.userlist;
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
+-------+------+
1 row in set (0.00 sec)
#在被设定延迟复制的主机中查看动作是否被同步
mysql> select * from xia.userlist;
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
| user2 | abc |
+-------+------+
2 rows in set (0.00 sec)
#等待延迟时间过后再次查看
mysql> select * from xia.userlist;
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
+-------+------+
1 row in set (0.00 sec)慢查询日志
mysql> show variables like "slow%"; +---------------------+----------------------------------+ | variable_name | value | +---------------------+----------------------------------+ | slow_launch_time | 2 | | slow_query_log | off | | slow_query_log_file | /data/mysql/mysql-node1-slow.log | +---------------------+----------------------------------+ 3 rows in set (0.03 sec) #开启慢查询日志 mysql> set global slow_query_log=on; query ok, 0 rows affected (0.03 sec) mysql> show variables like "slow%"; +---------------------+----------------------------------+ | variable_name | value | +---------------------+----------------------------------+ | slow_launch_time | 2 | | slow_query_log | on | | slow_query_log_file | /data/mysql/mysql-node1-slow.log | +---------------------+----------------------------------+ 3 rows in set (0.00 sec) #检测慢查询日志 mysql> show variables like "long%"; #慢查询阈值 +-----------------+-----------+ | variable_name | value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.01 sec) mysql> set long_query_time=4; query ok, 0 rows affected (0.00 sec) mysql> show variables like "long%"; +-----------------+----------+ | variable_name | value | +-----------------+----------+ | long_query_time | 4.000000 | +-----------------+----------+ 1 row in set (0.00 sec) mysql> select sleep (3); #不会生成慢查询日志 +-----------+ | sleep (3) | +-----------+ | 0 | +-----------+ 1 row in set (3.00 sec) mysql> select sleep (5); +-----------+ | sleep (4) | +-----------+ | 0 | +-----------+ 1 row in set (4.00 sec) [root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log /usr/local/mysql/bin/mysqld, version: 8.3.0 (source distribution). started with: tcp port: 3306 unix socket: /data/mysql/mysql.sock time id command argument # time: 2026-02-27t02:04:39.297189z # user@host: root[root] @ localhost [] id: 10 # query_time: 4.000424 lock_time: 0.000000 rows_sent: 1 rows_examined: 1 set timestamp=1772157875; select sleep (5);
gtid模式
#在master和slave中默认gtid模式是未开启的
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| variable_name | value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | on |
| enforce_gtid_consistency | off |
| gtid_executed | |
| gtid_executed_compression_period | 0 |
| gtid_mode | off |
| gtid_next | automatic |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | off |
+----------------------------------+-----------+
9 rows in set (0.01 sec)
#在所有主机中加入参数
[root@mysql-node1~3 ~]# vim /etc/my.cnf
gtid_mode=on
enforce-gtid-consistency=on
[root@mysql-node1~3 ~]# systemctl restart mysqld.service
#在三台主机中分别查看gtid模式是否开启
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| variable_name | value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | on |
| enforce_gtid_consistency | on |
| gtid_executed | |
| gtid_executed_compression_period | 0 |
| gtid_mode | on |
| gtid_next | automatic |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | off |
+----------------------------------+-----------+
9 rows in set (0.00 sec)
#在从库中停止slave功能；
mysql> stop slave;
mysql> change master to master_host='172.25.254.10', master_user='lee', master_password='lee', master_auto_position=1;
mysql> start slave ;
mysql> show slave status \g;
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.10
master_user: lee
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000005
read_master_log_pos: 158
relay_log_file: mysql-node2-relay-bin.000002
relay_log_pos: 375
relay_master_log_file: mysql-bin.000005
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: 158
relay_log_space: 592
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: 10
master_uuid: be64e358-12b4-11f1-88cc-000c29f4a60c
master_info_file: mysql.slave_master_info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: replica has read all relay log; waiting for more updates
master_retry_count: 10
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set:
executed_gtid_set:
auto_position: 1
replicate_rewrite_db:
channel_name:
master_tls_version:
master_public_key_path:
get_master_public_key: 0
network_namespace:
1 row in set, 1 warning (0.00 sec)多线程回放
#在slave主机中默认回方日志时使用单线程回放 #mysql-node3 mysql> show processlist; +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ | id | user | host | db | command | time | state | info | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ | 5 | system user | connecting host | null | connect | 222 | waiting for source to send event | null | | 6 | system user | | null | query | 222 | replica has read all relay log; waiting for more updates | null | | 7 | event_scheduler | localhost | null | daemon | 222 | waiting on empty queue | null | | 8 | system user | | null | connect | 222 | waiting for an event from coordinator | null | | 10 | system user | | null | connect | 222 | waiting for an event from coordinator | null | | 11 | system user | | null | connect | 222 | waiting for an event from coordinator | null | | 12 | system user | | null | connect | 222 | waiting for an event from coordinator | null | | 14 | root | localhost | null | query | 0 | init | show processlist | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ 8 rows in set, 1 warning (0.00 sec) #mysql-node2 mysql> show processlist; +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ | id | user | host | db | command | time | state | info | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ | 8 | event_scheduler | localhost | null | daemon | 229 | waiting on empty queue | null | | 14 | root | localhost | null | query | 0 | init | show processlist | | 15 | system user | connecting host | null | connect | 120 | waiting for source to send event | null | | 16 | system user | | null | query | 120 | replica has read all relay log; waiting for more updates | null | | 17 | system user | | null | connect | 120 | waiting for an event from coordinator | null | | 18 | system user | | null | connect | 120 | waiting for an event from coordinator | null | | 19 | system user | | null | connect | 120 | waiting for an event from coordinator | null | | 20 | system user | | null | connect | 120 | waiting for an event from coordinator | null | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ 8 rows in set, 1 warning (0.00 sec) #开启多线程回放日志（在slave主中） [root@mysql-node2 ~]# vim /etc/my.cnf slave-parallel-type=logical_clock slave-parallel-workers=16 relay_log_recovery=on [root@mysql-node2 ~]# systemctl restart mysqld.service [root@mysql-node2 ~]# mysql -uroot -plee 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 26 server version: 8.3.0 source distribution copyright (c) 2000, 2024, oracle and/or its affiliates. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. #查看更改生效信息 mysql> show processlist; +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ | id | user | host | db | command | time | state | info | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ | 5 | system user | connecting host | null | connect | 36 | waiting for source to send event | null | | 6 | system user | | null | query | 36 | replica has read all relay log; waiting for more updates | null | | 7 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 8 | event_scheduler | localhost | null | daemon | 36 | waiting on empty queue | null | | 10 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 11 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 12 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 14 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 15 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 16 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 17 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 18 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 19 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 20 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 21 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 22 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 23 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 24 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 25 | system user | | null | connect | 36 | waiting for an event from coordinator | null | | 26 | root | localhost | null | query | 0 | init | show processlist | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+ 20 rows in set, 1 warning (0.00 sec)
半同步模式
#在所有主机中操作
[root@mysql-node1~3 ~]# vim /etc/my.cnf
rpl_semi_sync_master_enabled=1
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled = 1;
#在slave主机中
mysql> stop slave io_thread;
mysql> start slave io_thread;
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | active |
+----------------------+---------------+
1 row in set (0.00 sec)
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| variable_name | value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | on |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | on |
| rpl_semi_sync_master_wait_point | after_sync |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
#所有主机重启mysql
[root@mysql-node3 ~]# /etc/init.d/mysqld restart
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| variable_name | value |
+--------------------------------------------+-------+
| rpl_semi_sync_master_clients | 0 |
| rpl_semi_sync_master_net_avg_wait_time | 0 |
| rpl_semi_sync_master_net_wait_time | 0 |
| rpl_semi_sync_master_net_waits | 0 |
| rpl_semi_sync_master_no_times | 0 |
| rpl_semi_sync_master_no_tx | 0 |
| rpl_semi_sync_master_status | on |
| rpl_semi_sync_master_timefunc_failures | 0 |
| rpl_semi_sync_master_tx_avg_wait_time | 0 |
| rpl_semi_sync_master_tx_wait_time | 0 |
| rpl_semi_sync_master_tx_waits | 0 |
| rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| rpl_semi_sync_master_wait_sessions | 0 |
| rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#测试：
#在主库中
mysql> create database timinglee;
query ok, 1 row affected (10.01 sec) #有等待ack时间
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| variable_name | value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | on |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | on |
| rpl_semi_sync_master_wait_point | after_sync |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
#模拟ack故障 在所有slave主机中
mysql> stop slave io_thread;
query ok, 0 rows affected, 1 warning (0.00 sec)
#在主库写入数据
mysql> insert into timinglee.userlist values ('user3','123');
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| variable_name | value |
+--------------------------------------------+-------+
| rpl_semi_sync_master_clients | 0 |
| rpl_semi_sync_master_net_avg_wait_time | 0 |
| rpl_semi_sync_master_net_wait_time | 0 |
| rpl_semi_sync_master_net_waits | 0 |
| rpl_semi_sync_master_no_times | 1 |
| rpl_semi_sync_master_no_tx | 2 |
| rpl_semi_sync_master_status | off |
| rpl_semi_sync_master_timefunc_failures | 0 |
| rpl_semi_sync_master_tx_avg_wait_time | 0 |
| rpl_semi_sync_master_tx_wait_time | 0 |
| rpl_semi_sync_master_tx_waits | 0 |
| rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| rpl_semi_sync_master_wait_sessions | 0 |
| rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#恢复故障 在所有slave主机中
mysql> start slave io_thread;
query ok, 0 rows affected, 1 warning (0.00 sec)
#所有主机重启mysql
[root@mysql-node3 ~]# systemctl restart mysql.server警告：如果之前的环境中开启了半同步模式，那么在做下处实验时会报错
last_sql_error: coordinator stopped because there were error(s) in the worker(s). the most recent failure being: worker 1 failed executing transaction 'd77b3abd-92cb-11f1-abed-000c29f195bc:1' at source log mysql-binlog.000003, end_log_pos 1566. see error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. |
#解决方案，在每台机子做以下
mysql> stop replica; mysql> set gtid_next='d77b3abd-92cb-11f1-abed-000c29f195bc:1'; mysql> begin;commit; mysql> set gtid_next='automatic'; mysql> start replica;
四. mysql 高可用之 mha
mha 概述
github地址：https://github.com/yoshinorim
为什么要用 mha？
master 的单点故障问题
什么是 mha？
- mha（master high availability）是一套优秀的 mysql 高可用环境下故障切换和主从复制的软件。mha 的出现就是解决 mysql 单点的问题。
- mysql 故障切换过程中，mha 能做到 0-30 秒内自动完成故障切换操作。
- mha 能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。
mha 的组成
- mha 由两部分组成: mhamanager (管理节点) mha node (数据库节点), mha manager 可以单独部署在一台 独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节 点上。
- mha manager 会定时探测集群中的 master 节点。
- 当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的 slave 重新 指向新的 master。
mha 的特点
- 自动故障切换过程中，mha 从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失。
- 使用半同步复制，可以大大降低数据丢失的风险，如果只有一个 slave 已经收到了最新的二进制日志，mha 可以将最新的二进制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数据一致性 。
- 目前 mha 支持一主多从架构，最少三台服务，即一主两从。
故障切换备选主库的算法
- 一般判断从库的是从（position/gtid）判断优劣，数据有差异，最接近于 master
- 的 slave，成为备选主。
- 数据一致的情况下，按照配置文件顺序，选择备选主库。
- 设定有权重 （candidate_master = 1），按照权重强制指定备选主。
- 默认情况下如果一个 slave 落后 master 100m 的 relay logs 的话，即使有权重，也会失效。
- 如果 check_repl_delay = 0 的话，即使落后很多日志，也强制选 择其为备选主。
mha 工作原理
- 目前 mha 主要支持一主多从的架构，要搭建 mha, 要求一个复制集群必须最少有 3 台数据库服务器，一主二 从，即一台充当 master，台充当备用 master，另一台充当从库。
- mha node 运行在每台 mysql 服务器上
- mhamanager 会定时探测集群中的 master 节点
- 当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master
- 然后将所有其他的 slave 重新指向新的 master，vip 自动漂移到新的 master。
- 整个故障转移过程对应用程序完全透明。
mysql-mha高可用集群环境配置
准备工作-保证数据一致性（所有的mysql节点）
#在master节点中
[root@mysql-node10 ~]# /etc/init.d/mysqld stop
[root@mysql-node10 ~]# rm -fr /data/mysql/*
[root@mysql-node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=on
log_slave_updates=on
enforce-gtid-consistency=on
symbolic-links=0
[root@mysql-node10 ~]# mysqld --user mysql --initialize
[root@mysql-node10 ~]# /etc/init.d/mysqld start
[root@mysql-node10 ~]# mysql_secure_installation
[root@mysql-node10 ~]# mysql -p
mysql> create user repl@'%' identified with mysql_native_password by 'lee';
query ok, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repl@'%';
query ok, 0 rows affected (0.00 sec)
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
query ok, 0 rows affected (0.02 sec)
mysql> set global rpl_semi_sync_master_enabled = 1;
query ok, 0 rows affected (0.00 sec)
#在slave1和slave2中
[root@mysql-node20 & 30 ~]# /etc/init.d/mysqld stop
[root@mysql-node20 & 30 ~]# rm -fr /data/mysql/*
[root@mysql-node20 & 30 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=on
log_slave_updates=on
enforce-gtid-consistency=on
symbolic-links=0
[root@mysql-node20 & 30 ~]# mysqld --user mysql --initialize
[root@mysql-node20 & 300 ~]# /etc/init.d/mysqld start
[root@mysql-node20 & 30 ~]# mysql_secure_installation
[root@mysql-node20 & 30 ~]# mysql -p
mysql> change master to master_host='172.25.254.10', master_user='repl', master_password='lee', master_auto_position=1;
query ok, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
query ok, 0 rows affected (0.00 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
query ok, 0 rows affected (0.01 sec)
mysql> set global rpl_semi_sync_slave_enabled =1;
query ok, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
query ok, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
query ok, 0 rows affected (0.00 sec)
mysql> show status like 'rpl_semi_sync%';
+----------------------------+-------+
| variable_name | value |
+----------------------------+-------+
| rpl_semi_sync_slave_status | on |
+----------------------------+-------+
1 row in set (0.01 sec)
mysql> show slave status\g;
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.10
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 1329
relay_log_file: mysql-node3-relay-bin.000003
relay_log_pos: 462
relay_master_log_file: mysql-bin.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: 1329
relay_log_space: 2067
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: 10
master_uuid: f5387b14-93dd-11f1-8cb7-000c29e79365
master_info_file: mysql.slave_master_info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: replica has read all relay log; waiting for more updates
master_retry_count: 10
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set: f5387b14-93dd-11f1-8cb7-000c29e79365:1-5
executed_gtid_set: ed268a96-93de-11f1-9b43-000c290682f7:1-3,
f5387b14-93dd-11f1-8cb7-000c29e79365:1-5
auto_position: 1
replicate_rewrite_db:
channel_name:
master_tls_version:
master_public_key_path:
get_master_public_key: 0
network_namespace:
1 row in set, 1 warning (0.00 sec)
error:
no query specified
在所有主机中安装mha相应软件
#在mha中 [root@mysql-mha ~]# unzip mha-7.zip [root@mysql-mha ]# cd mha-7/ [root@mysql-mha 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@mha mha-7]# dnf install perl perl-dbd-mysql perl-cpan -y [root@mha mha-7]# cpan loading internal logger. log::log4perl recommended for better logging cpan.pm requires configuration, but most of it can be done automatically. if you answer 'no' below, you will enter an interactive dialog for each configuration option instead. would you like to configure as much as possible automatically? [yes] yes cpan[1]> install config::tiny cpan[2]> install log::dispatch cpan[3]> install mail::sender specify defaults for mail::sender? (y/n) y default encoding of message bodies (n)one, (q)uoted-printable, (b)ase64: n cpan[4]> install parallel::forkmanager cpan[5]>exit #验证组建是否安装成功 [root@mha ~]# perl -mconfig::tiny -e 'print "ok\n"' ok [root@mha ~]# perl -mlog::dispatch -e 'print "ok\n"' ok [root@mha ~]# perl -mmail::sender -e 'print "ok\n"' mail::sender is deprecated and you should look to email::sender instead at -e line 0. ok [root@mha ~]# perl -mparallel::forkmanager -e 'print "ok\n"' ok #在mha节点 [root@mha mha-7]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm --nodeps #在所有mysql节点 [root@mha mha-7]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm --nodeps
在软件中包含的工具包介绍
1.manager 工具包主要包括以下几个工具：
masterha_check_ssh #检查mha的ssh配置状况
masterha_check_repl #检查mysql复制状况
masterha_manger #启动mha</span>
masterha_check_status #检测当前mha运行状态masterha_master_monitor #检测master是否宕机
masterha_master_switch #控制故障转移（自动或者手动）
masterha_conf_host #添加或删除配置的server信息
2.node 工具包 （通常由 masterha 主机直接调用，无需人为执行）
save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog #去除不必要的rollback事件（mha已不再使用这个工具）
purge_relay_logs #清除中继日志（不会阻塞sql线程）
在slave中安装相应软件
[root@mha mha-7]# for i in 10 20 30 > do > scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.$i:/mnt > ssh -l root 172.25.254.$i "rpm -ivh /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm --nodeps" > done warning: permanently added '172.25.254.10' (ed25519) to the list of known hosts. mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35kb 16.9mb/s 00:00 verifying... ######################################## 准备中... ######################################## 正在升级/安装... mha4mysql-node-0.58-0.el7.centos ######################################## warning: permanently added '172.25.254.20' (ed25519) to the list of known hosts. mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35kb 28.2mb/s 00:00 verifying... ######################################## 准备中... ######################################## 正在升级/安装... mha4mysql-node-0.58-0.el7.centos ######################################## warning: permanently added '172.25.254.30' (ed25519) to the list of known hosts. mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35kb 20.1mb/s 00:00 verifying... ######################################## 准备中... ######################################## 正在升级/安装... mha4mysql-node-0.58-0.el7.centos ########################################
修改mha-manager中的检测代码
[root@mha mha-7]# vim /usr/share/perl5/vendor_perl/mha/nodeutil.pm
199 #sub parse_mysql_major_version($) {
200 # my $str = shift;
201 # my $result = sprintf( '%03d%03d', $str =~ m/(\d+)/g );
202 # return $result;
203 #}
sub parse_mysql_major_version($) {
my $str = shift;
my @nums = $str =~ m/(\d+)/g;
my $result = sprintf( '%03d%03d', $nums[0]//0, $nums[1]//0);
return $result;
}未mha建立远程登录用户
#在master主机中 mysql> create user root@'%' identified with mysql_native_password by 'lee'; query ok, 0 rows affected (0.01 sec) mysql> grant all on *.* to root@'%' ; query ok, 0 rows affected (0.00 sec
生成mha-manager的配置文件模板
[root@mha mha4mysql-manager-0.58]# mkdir /etc/masterha/ -p [root@mha mha-7]# tar zxf mha4mysql-manager-0.58.tar.gz [root@mha mha-7]# cd mha4mysql-manager-0.58 [root@mha mha4mysql-manager-0.58]# mkdir /etc/masterha/ -p [root@mha mha4mysql-manager-0.58]# cat samples/conf/masterha_default.cnf samples/conf/app1.cnf > /etc/masterha/app1.cnf
修改配置文件
[root@mha mha4mysql-manager-0.58]# vim /etc/masterha/app1.cnf [server default] user=root password=lee ssh_user=root repl_user=lee repl_password=lee master_binlog_dir= /data/mysql remote_workdir=/tmp secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s 172.25.254.30 ping_interval=3 # master_ip_failover_script= /script/masterha/master_ip_failover # shutdown_script= /script/masterha/power_manager # report_script= /script/masterha/send_report # master_ip_online_change_script= /script/masterha/master_ip_online_change [server default] manager_workdir=/etc/masterha manager_log=/etc/masterha/mha.log [server1] hostname=172.25.254.10 candidate_master=1 check_repl_delay=0 [server2] hostname=172.25.254.20 candidate_master=1 check_repl_delay=0 [server3] hostname=172.25.254.30 no_master=1
检测环境
[root@mha mha4mysql-manager-0.58]# masterha_check_ssh --conf=/etc/masterha/app1.cnf fri feb 27 16:23:20 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. fri feb 27 16:23:20 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. fri feb 27 16:23:20 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. fri feb 27 16:23:20 2026 - [info] starting ssh connection tests.. fri feb 27 16:23:21 2026 - [debug] fri feb 27 16:23:20 2026 - [debug] connecting via ssh from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.20(172.25.254.20:22).. fri feb 27 16:23:20 2026 - [debug] ok. fri feb 27 16:23:20 2026 - [debug] connecting via ssh from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.30(172.25.254.30:22).. fri feb 27 16:23:20 2026 - [debug] ok. fri feb 27 16:23:21 2026 - [debug] fri feb 27 16:23:20 2026 - [debug] connecting via ssh from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.10(172.25.254.10:22).. fri feb 27 16:23:21 2026 - [debug] ok. fri feb 27 16:23:21 2026 - [debug] connecting via ssh from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.30(172.25.254.30:22).. fri feb 27 16:23:21 2026 - [debug] ok. fri feb 27 16:23:22 2026 - [debug] fri feb 27 16:23:21 2026 - [debug] connecting via ssh from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.10(172.25.254.10:22).. fri feb 27 16:23:21 2026 - [debug] ok. fri feb 27 16:23:21 2026 - [debug] connecting via ssh from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.20(172.25.254.20:22).. fri feb 27 16:23:21 2026 - [debug] ok. fri feb 27 16:23:22 2026 - [info] all ssh connection tests passed successfully. use of uninitialized value in exit at /usr/bin/masterha_check_ssh line 44. [root@mha mha4mysql-manager-0.58]# masterha_check_repl --conf=/etc/masterha/app1.cnf fri feb 27 16:23:50 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. fri feb 27 16:23:50 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. fri feb 27 16:23:50 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. fri feb 27 16:23:50 2026 - [info] mha::mastermonitor version 0.58. fri feb 27 16:23:51 2026 - [info] gtid failover mode = 1 fri feb 27 16:23:51 2026 - [info] dead servers: fri feb 27 16:23:51 2026 - [info] alive servers: fri feb 27 16:23:51 2026 - [info] 172.25.254.10(172.25.254.10:3306) fri feb 27 16:23:51 2026 - [info] 172.25.254.20(172.25.254.20:3306) fri feb 27 16:23:51 2026 - [info] 172.25.254.30(172.25.254.30:3306) fri feb 27 16:23:51 2026 - [info] alive slaves: fri feb 27 16:23:51 2026 - [info] 172.25.254.20(172.25.254.20:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled fri feb 27 16:23:51 2026 - [info] gtid on fri feb 27 16:23:51 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) fri feb 27 16:23:51 2026 - [info] primary candidate for the new master (candidate_master is set) fri feb 27 16:23:51 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled fri feb 27 16:23:51 2026 - [info] gtid on fri feb 27 16:23:51 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) fri feb 27 16:23:51 2026 - [info] not candidate for the new master (no_master is set) fri feb 27 16:23:51 2026 - [info] current alive master: 172.25.254.10(172.25.254.10:3306) fri feb 27 16:23:51 2026 - [info] checking slave configurations.. fri feb 27 16:23:51 2026 - [info] read_only=1 is not set on slave 172.25.254.20(172.25.254.20:3306). fri feb 27 16:23:51 2026 - [info] read_only=1 is not set on slave 172.25.254.30(172.25.254.30:3306). fri feb 27 16:23:51 2026 - [info] checking replication filtering settings.. fri feb 27 16:23:51 2026 - [info] binlog_do_db= , binlog_ignore_db= fri feb 27 16:23:51 2026 - [info] replication filtering check ok. fri feb 27 16:23:51 2026 - [info] gtid (with auto-pos) is supported. skipping all ssh and node package checking. fri feb 27 16:23:51 2026 - [info] checking ssh publickey authentication settings on the current master.. fri feb 27 16:23:51 2026 - [info] healthcheck: ssh to 172.25.254.10 is reachable. fri feb 27 16:23:51 2026 - [info] 172.25.254.10(172.25.254.10:3306) (current master) +--172.25.254.20(172.25.254.20:3306) +--172.25.254.30(172.25.254.30:3306) fri feb 27 16:23:51 2026 - [info] checking replication health on 172.25.254.20.. fri feb 27 16:23:51 2026 - [info] ok. fri feb 27 16:23:51 2026 - [info] checking replication health on 172.25.254.30.. fri feb 27 16:23:51 2026 - [info] ok. fri feb 27 16:23:51 2026 - [warning] master_ip_failover_script is not defined. fri feb 27 16:23:51 2026 - [warning] shutdown_script is not defined. fri feb 27 16:23:51 2026 - [info] got exit code 0 (not master dead). mysql replication health is ok.
在slave中安装mha-node软件依赖
#在所有的数据库主机中安装依赖 [root@mysql-node1~3 ~]# dnf install perl perl-dbd-mysql perl-cpan -y [root@mysql-node1~3 ~]# tar zxf cpan_plugin.tar.gz [root@mysql-node1~3 ~]# cpan loading internal logger. log::log4perl recommended for better logging cpan.pm requires configuration, but most of it can be done automatically. if you answer 'no' below, you will enter an interactive dialog for each configuration option instead. would you like to configure as much as possible automatically? [yes] yes cpan[1]> install config::tiny cpan[2]> install log::dispatch cpan[3]> install mail::sender specify defaults for mail::sender? (y/n) y default encoding of message bodies (n)one, (q)uoted-printable, (b)ase64: n cpan[4]> install parallel::forkmanager cpan[5]>exit #验证组建是否安装成功 [root@mysql-node1~3 ~]# perl -mconfig::tiny -e 'print "ok\n"' ok [root@mysql-node1~3 ~]# perl -mlog::dispatch -e 'print "ok\n"' ok [root@mysql-node1~3 ~]# perl -mmail::sender -e 'print "ok\n"' mail::sender is deprecated and you should look to email::sender instead at -e line 0. ok [root@mysql-node1~3 ~]# perl -mparallel::forkmanager -e 'print "ok\n"' ok
集群切换操作
1.手动切换
#master无故障切换
#默认状态
[root@mysql-node2 ~]# mysql -uroot -plee -e "show slave status\g;" | head -n 10
mysql: [warning] using a password on the command line interface can be insecure .
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.10
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 1829
relay_log_file: mysql-node2-relay-bin.000003
relay_log_pos: 962
[root@mysql-node3 ~]# mysql -uroot -plee -e "show slave status\g;" | head -n 15
mysql: [warning] using a password on the command line interface can be insecure.
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.10
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 1829
relay_log_file: mysql-node3-relay-bin.000003
relay_log_pos: 962
relay_master_log_file: mysql-bin.000002
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
replicate_ignore_db:
#执行切换，把master切换到20
[root@mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.20 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
mon aug 10 15:30:08 2026 - [info] mha::masterrotate version 0.58.
mon aug 10 15:30:08 2026 - [info] starting online master switch..
mon aug 10 15:30:08 2026 - [info]
mon aug 10 15:30:08 2026 - [info] * phase 1: configuration check phase..
mon aug 10 15:30:08 2026 - [info]
mon aug 10 15:30:08 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping.
mon aug 10 15:30:08 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf..
mon aug 10 15:30:08 2026 - [info] reading server configuration from /etc/masterha/app1.cnf..
mon aug 10 15:30:09 2026 - [info] gtid failover mode = 1
mon aug 10 15:30:09 2026 - [info] current alive master: 172.25.254.10(172.25.254.10:3306)
mon aug 10 15:30:09 2026 - [info] alive slaves:
mon aug 10 15:30:09 2026 - [info] 172.25.254.20(172.25.254.20:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled
mon aug 10 15:30:09 2026 - [info] gtid on
mon aug 10 15:30:09 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306)
mon aug 10 15:30:09 2026 - [info] primary candidate for the new master (candidate_master is set)
mon aug 10 15:30:09 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled
mon aug 10 15:30:09 2026 - [info] gtid on
mon aug 10 15:30:09 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306)
mon aug 10 15:30:09 2026 - [info] not candidate for the new master (no_master is set)
it is better to execute flush no_write_to_binlog tables on the master before switching. is it ok to execute on 172.25.254.10(172.25.254.10:3306)? (yes/no): yes
mon aug 10 15:30:11 2026 - [info] executing flush no_write_to_binlog tables. this may take long time..
mon aug 10 15:30:11 2026 - [info] ok.
mon aug 10 15:30:11 2026 - [info] checking mha is not monitoring or doing failover..
mon aug 10 15:30:11 2026 - [info] checking replication health on 172.25.254.20..
mon aug 10 15:30:11 2026 - [info] ok.
mon aug 10 15:30:11 2026 - [info] checking replication health on 172.25.254.30..
mon aug 10 15:30:11 2026 - [info] ok.
mon aug 10 15:30:11 2026 - [info] 172.25.254.20 can be new master.
mon aug 10 15:30:11 2026 - [info]
from:
172.25.254.10(172.25.254.10:3306) (current master)
+--172.25.254.20(172.25.254.20:3306)
+--172.25.254.30(172.25.254.30:3306)
to:
172.25.254.20(172.25.254.20:3306) (new master)
+--172.25.254.30(172.25.254.30:3306)
+--172.25.254.10(172.25.254.10:3306)
starting master switch from 172.25.254.10(172.25.254.10:3306) to 172.25.254.20(172.25.254.20:3306)? (yes/no): yes
mon aug 10 15:30:13 2026 - [info] checking whether 172.25.254.20(172.25.254.20:3306) is ok for the new master..
mon aug 10 15:30:13 2026 - [info] ok.
mon aug 10 15:30:13 2026 - [info] 172.25.254.10(172.25.254.10:3306): show slave status returned empty result. to check replication filtering rules, temporarily executing change master to a dummy host.
mon aug 10 15:30:13 2026 - [info] 172.25.254.10(172.25.254.10:3306): resetting slave pointing to the dummy host.
mon aug 10 15:30:13 2026 - [info] ** phase 1: configuration check phase completed.
mon aug 10 15:30:13 2026 - [info]
mon aug 10 15:30:13 2026 - [info] * phase 2: rejecting updates phase..
mon aug 10 15:30:13 2026 - [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
mon aug 10 15:30:14 2026 - [info] locking all tables on the orig master to reject updates from everybody (including root):
mon aug 10 15:30:14 2026 - [info] executing flush tables with read lock..
mon aug 10 15:30:14 2026 - [info] ok.
mon aug 10 15:30:14 2026 - [info] orig master binlog:pos is mysql-bin.000002:4859.
mon aug 10 15:30:14 2026 - [info] waiting to execute all relay logs on 172.25.254.20(172.25.254.20:3306)..
mon aug 10 15:30:14 2026 - [info] master_pos_wait(mysql-bin.000002:4859) completed on 172.25.254.20(172.25.254.20:3306). executed 0 events.
mon aug 10 15:30:14 2026 - [info] done.
mon aug 10 15:30:14 2026 - [info] getting new master's binlog name and position..
mon aug 10 15:30:14 2026 - [info] mysql-bin.000002:4953
mon aug 10 15:30:14 2026 - [info] all other slaves should start replication from here. statement should be: change master to master_host='172.25.254.20', master_port=3306, master_auto_position=1, master_user='lee', master_password='xxx';
mon aug 10 15:30:14 2026 - [info] setting read_only=0 on 172.25.254.20(172.25.254.20:3306)..
mon aug 10 15:30:14 2026 - [info] ok.
mon aug 10 15:30:14 2026 - [info]
mon aug 10 15:30:14 2026 - [info] * switching slaves in parallel..
mon aug 10 15:30:14 2026 - [info]
mon aug 10 15:30:14 2026 - [info] -- slave switch on host 172.25.254.30(172.25.254.30:3306) started, pid: 40084
mon aug 10 15:30:14 2026 - [info]
mon aug 10 15:30:15 2026 - [info] log messages from 172.25.254.30 ...
mon aug 10 15:30:15 2026 - [info]
mon aug 10 15:30:14 2026 - [info] waiting to execute all relay logs on 172.25.254.30(172.25.254.30:3306)..
mon aug 10 15:30:14 2026 - [info] master_pos_wait(mysql-bin.000002:4859) completed on 172.25.254.30(172.25.254.30:3306). executed 0 events.
mon aug 10 15:30:14 2026 - [info] done.
mon aug 10 15:30:14 2026 - [info] resetting slave 172.25.254.30(172.25.254.30:3306) and starting replication from the new master 172.25.254.20(172.25.254.20:3306)..
mon aug 10 15:30:14 2026 - [info] executed change master.
mon aug 10 15:30:14 2026 - [info] slave started.
mon aug 10 15:30:15 2026 - [info] end of log messages from 172.25.254.30 ...
mon aug 10 15:30:15 2026 - [info]
mon aug 10 15:30:15 2026 - [info] -- slave switch on host 172.25.254.30(172.25.254.30:3306) succeeded.
mon aug 10 15:30:15 2026 - [info] unlocking all tables on the orig master:
mon aug 10 15:30:15 2026 - [info] executing unlock tables..
mon aug 10 15:30:15 2026 - [info] ok.
mon aug 10 15:30:15 2026 - [info] starting orig master as a new slave..
mon aug 10 15:30:15 2026 - [info] resetting slave 172.25.254.10(172.25.254.10:3306) and starting replication from the new master 172.25.254.20(172.25.254.20:3306)..
mon aug 10 15:30:15 2026 - [info] executed change master.
mon aug 10 15:30:15 2026 - [info] slave started.
mon aug 10 15:30:15 2026 - [info] all new slave servers switched successfully.
mon aug 10 15:30:15 2026 - [info]
mon aug 10 15:30:15 2026 - [info] * phase 5: new master cleanup phase..
mon aug 10 15:30:15 2026 - [info]
mon aug 10 15:30:15 2026 - [info] 172.25.254.20: resetting slave info succeeded.
mon aug 10 15:30:15 2026 - [info] switching master to 172.25.254.20(172.25.254.20:3306) completed successfully.
#记得看配置是否正确
[root@mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf mon aug 10 15:35:59 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 15:35:59 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 15:35:59 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 15:35:59 2026 - [info] mha::mastermonitor version 0.58. mon aug 10 15:36:00 2026 - [info] gtid failover mode = 1 mon aug 10 15:36:00 2026 - [info] dead servers: mon aug 10 15:36:00 2026 - [info] alive servers: mon aug 10 15:36:00 2026 - [info] 172.25.254.10(172.25.254.10:3306) mon aug 10 15:36:00 2026 - [info] 172.25.254.20(172.25.254.20:3306) mon aug 10 15:36:00 2026 - [info] 172.25.254.30(172.25.254.30:3306) mon aug 10 15:36:00 2026 - [info] alive slaves: mon aug 10 15:36:00 2026 - [info] 172.25.254.10(172.25.254.10:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:36:00 2026 - [info] gtid on mon aug 10 15:36:00 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306) mon aug 10 15:36:00 2026 - [info] primary candidate for the new master (candidate_master is set) mon aug 10 15:36:00 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:36:00 2026 - [info] gtid on mon aug 10 15:36:00 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306) mon aug 10 15:36:00 2026 - [info] not candidate for the new master (no_master is set) mon aug 10 15:36:00 2026 - [info] current alive master: 172.25.254.20(172.25.254.20:3306) mon aug 10 15:36:00 2026 - [info] checking slave configurations.. mon aug 10 15:36:00 2026 - [info] read_only=1 is not set on slave 172.25.254.30(172.25.254.30:3306). mon aug 10 15:36:00 2026 - [info] checking replication filtering settings.. mon aug 10 15:36:00 2026 - [info] binlog_do_db= , binlog_ignore_db= mon aug 10 15:36:00 2026 - [info] replication filtering check ok. mon aug 10 15:36:00 2026 - [info] gtid (with auto-pos) is supported. skipping all ssh and node package checking. mon aug 10 15:36:00 2026 - [info] checking ssh publickey authentication settings on the current master.. mon aug 10 15:36:00 2026 - [info] healthcheck: ssh to 172.25.254.20 is reachable. mon aug 10 15:36:00 2026 - [info] 172.25.254.20(172.25.254.20:3306) (current master) +--172.25.254.10(172.25.254.10:3306) +--172.25.254.30(172.25.254.30:3306) mon aug 10 15:36:00 2026 - [info] checking replication health on 172.25.254.10.. mon aug 10 15:36:00 2026 - [info] ok. mon aug 10 15:36:00 2026 - [info] checking replication health on 172.25.254.30.. mon aug 10 15:36:00 2026 - [info] ok. mon aug 10 15:36:00 2026 - [warning] master_ip_failover_script is not defined. mon aug 10 15:36:00 2026 - [warning] shutdown_script is not defined. mon aug 10 15:36:00 2026 - [info] got exit code 0 (not master dead). mysql replication health is ok. [root@mha masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf mon aug 10 15:36:05 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 15:36:05 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 15:36:05 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 15:36:05 2026 - [info] starting ssh connection tests.. mon aug 10 15:36:06 2026 - [debug] mon aug 10 15:36:05 2026 - [debug] connecting via ssh from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.20(172.25.254.20:22).. mon aug 10 15:36:06 2026 - [debug] ok. mon aug 10 15:36:06 2026 - [debug] connecting via ssh from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.30(172.25.254.30:22).. mon aug 10 15:36:06 2026 - [debug] ok. mon aug 10 15:36:07 2026 - [debug] mon aug 10 15:36:06 2026 - [debug] connecting via ssh from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.10(172.25.254.10:22).. mon aug 10 15:36:06 2026 - [debug] ok. mon aug 10 15:36:06 2026 - [debug] connecting via ssh from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.30(172.25.254.30:22).. mon aug 10 15:36:07 2026 - [debug] ok. mon aug 10 15:36:08 2026 - [debug] mon aug 10 15:36:06 2026 - [debug] connecting via ssh from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.10(172.25.254.10:22).. mon aug 10 15:36:07 2026 - [debug] ok. mon aug 10 15:36:07 2026 - [debug] connecting via ssh from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.20(172.25.254.20:22).. mon aug 10 15:36:07 2026 - [debug] ok. mon aug 10 15:36:08 2026 - [info] all ssh connection tests passed successfully. use of uninitialized value in exit at /usr/bin/masterha_check_ssh line 44.
#可能会出现一下
#卡住了，后面报错 [root@mha masterha]# masterha_master_switch \ --conf=/etc/masterha/app1.cnf \ --master_state=alive \ --new_master_host=172.25.254.20 \ --new_master_port=3306 \ --orig_master_is_new_slave \ --running_updates_limit=10000 mon aug 10 14:54:28 2026 - [info] mha::masterrotate version 0.58. mon aug 10 14:54:28 2026 - [info] starting online master switch.. mon aug 10 14:54:28 2026 - [info] mon aug 10 14:54:28 2026 - [info] * phase 1: configuration check phase.. mon aug 10 14:54:28 2026 - [info] mon aug 10 14:54:28 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 14:54:28 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 14:54:28 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 14:54:29 2026 - [info] gtid failover mode = 1 mon aug 10 14:54:29 2026 - [info] current alive master: 172.25.254.10(172.25.254.10:3306) mon aug 10 14:54:29 2026 - [info] alive slaves: mon aug 10 14:54:29 2026 - [info] 172.25.254.20(172.25.254.20:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 14:54:29 2026 - [info] gtid on mon aug 10 14:54:29 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 14:54:29 2026 - [info] primary candidate for the new master (candidate_master is set) mon aug 10 14:54:29 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 14:54:29 2026 - [info] gtid on mon aug 10 14:54:29 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 14:54:29 2026 - [info] not candidate for the new master (no_master is set) it is better to execute flush no_write_to_binlog tables on the master before switching. is it ok to execute on 172.25.254.10(172.25.254.10:3306)? (yes/no): yes mon aug 10 14:54:31 2026 - [info] executing flush no_write_to_binlog tables. this may take long time.. mon aug 10 14:54:31 2026 - [info] ok. mon aug 10 14:54:31 2026 - [info] checking mha is not monitoring or doing failover.. mon aug 10 14:54:31 2026 - [info] checking replication health on 172.25.254.20.. mon aug 10 14:54:31 2026 - [info] ok. mon aug 10 14:54:31 2026 - [info] checking replication health on 172.25.254.30.. mon aug 10 14:54:31 2026 - [info] ok. mon aug 10 14:54:31 2026 - [info] 172.25.254.20 can be new master. mon aug 10 14:54:31 2026 - [info] from: 172.25.254.10(172.25.254.10:3306) (current master) +--172.25.254.20(172.25.254.20:3306) +--172.25.254.30(172.25.254.30:3306) to: 172.25.254.20(172.25.254.20:3306) (new master) +--172.25.254.30(172.25.254.30:3306) +--172.25.254.10(172.25.254.10:3306) starting master switch from 172.25.254.10(172.25.254.10:3306) to 172.25.254.20(172.25.254.20:3306)? (yes/no): yes mon aug 10 14:54:33 2026 - [info] checking whether 172.25.254.20(172.25.254.20:3306) is ok for the new master.. mon aug 10 14:54:33 2026 - [info] ok. mon aug 10 14:54:33 2026 - [info] 172.25.254.10(172.25.254.10:3306): show slave status returned empty result. to check replication filtering rules, temporarily executing chnge master to a dummy host. mon aug 10 14:54:33 2026 - [info] 172.25.254.10(172.25.254.10:3306): resetting slave pointing to the dummy host. mon aug 10 14:54:33 2026 - [info] ** phase 1: configuration check phase completed. mon aug 10 14:54:33 2026 - [info] mon aug 10 14:54:33 2026 - [info] * phase 2: rejecting updates phase.. mon aug 10 14:54:33 2026 - [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 proeed? (yes/no): yes mon aug 10 14:54:36 2026 - [info] locking all tables on the orig master to reject updates from everybody (including root): mon aug 10 14:54:36 2026 - [info] executing flush tables with read lock.. mon aug 10 14:54:36 2026 - [info] ok. mon aug 10 14:54:36 2026 - [info] orig master binlog:pos is mysql-bin.000002:1829. mon aug 10 14:54:36 2026 - [info] waiting to execute all relay logs on 172.25.254.20(172.25.254.20:3306).. mon aug 10 14:54:36 2026 - [info] master_pos_wait(mysql-bin.000002:1829) completed on 172.25.254.20(172.25.254.20:3306). executed 0 events. mon aug 10 14:54:36 2026 - [info] done. mon aug 10 14:54:36 2026 - [info] getting new master's binlog name and position.. mon aug 10 14:54:36 2026 - [info] mysql-bin.000002:3245 mon aug 10 14:54:36 2026 - [info] all other slaves should start replication from here. statement should be: change master to master_host='172.25.254.20', master_port=3306,master_auto_position=1, master_user='lee', master_password='xxx'; mon aug 10 14:54:36 2026 - [info] mon aug 10 14:54:36 2026 - [info] * switching slaves in parallel.. mon aug 10 14:54:36 2026 - [info] mon aug 10 14:54:36 2026 - [info] -- slave switch on host 172.25.254.30(172.25.254.30:3306) started, pid: 39924 mon aug 10 14:54:36 2026 - [info] ^cmon aug 10 15:03:51 2026 - [info] killing thread 25 on 172.25.254.10(172.25.254.10:3306).. mon aug 10 15:03:51 2026 - [info] ok. mon aug 10 15:03:51 2026 - [info] killing thread 28 on 172.25.254.20(172.25.254.20:3306).. mon aug 10 15:03:51 2026 - [info] ok. mon aug 10 15:03:51 2026 - [info] killing thread 27 on 172.25.254.30(172.25.254.30:3306).. mon aug 10 15:03:51 2026 - [info] ok. [root@mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf mon aug 10 15:04:31 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 15:04:31 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 15:04:31 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 15:04:31 2026 - [info] mha::mastermonitor version 0.58. mon aug 10 15:04:32 2026 - [warning] sql thread is stopped(no error) on 172.25.254.20(172.25.254.20:3306) mon aug 10 15:04:32 2026 - [error][/usr/share/perl5/vendor_perl/mha/servermanager.pm, ln653] there are 2 non-slave servers! mha manages at most one non-slave server. check onfigurations. mon aug 10 15:04:32 2026 - [error][/usr/share/perl5/vendor_perl/mha/mastermonitor.pm, ln427] error happened on checking configurations. at /usr/share/perl5/vendor_perl/mhamastermonitor.pm line 329. mon aug 10 15:04:32 2026 - [error][/usr/share/perl5/vendor_perl/mha/mastermonitor.pm, ln525] error happened on monitoring servers. mon aug 10 15:04:32 2026 - [info] got exit code 1 (not master dead). mysql replication health is not ok!
#后面我手动切换到了node1上面，重复以上操作
#解决方式
#node1(master) [root@mysql-node1 ~]# mysql -uroot -plee <<eof reset slave all; alter user lee@'%' identified with mysql_native_password by 'lee'; grant replication slave on *.* to lee@'%'; flush privileges; show master status; eof #node2(从机) [root@mysql-node2 ~]# mysql -uroot -plee <<eof stop slave; reset slave all; change master to master_host='172.25.254.10', master_user='lee', master_password='lee', master_auto_position=1; start slave; show slave status\g eof [root@mysql-node3 ~]# mysql -uroot -plee <<eof stop slave; reset slave all; change master to master_host='172.25.254.10', master_user='lee', master_password='lee', master_auto_position=1; start slave; show slave status\g eof #从配置主机
#最后一张图解决方案
把日志里提示冲突的 gtid 替换到下面语句，空提交跳过冲突事务 stop slave; set gtid_next='这里填报错的gtid'; begin;commit; set gtid_next='automatic'; start slave; show slave status\g
#master故障后切换
#把node1停了 [root@mysql-node1 ~]# systemctl stop mysqld.service [root@mha masterha]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.10 --dead_master_port=3306 --new_master_host=172.25.254.20 --new_master_port=3306 --ignore_last_failover --dead_master_ip=<dead_master_ip> is not set. using 172.25.254.10. mon aug 10 15:48:40 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 15:48:40 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 15:48:40 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 15:48:40 2026 - [info] mha::masterfailover version 0.58. mon aug 10 15:48:40 2026 - [info] starting master failover. mon aug 10 15:48:40 2026 - [info] mon aug 10 15:48:40 2026 - [info] * phase 1: configuration check phase.. mon aug 10 15:48:40 2026 - [info] mon aug 10 15:48:41 2026 - [info] gtid failover mode = 1 mon aug 10 15:48:41 2026 - [info] dead servers: mon aug 10 15:48:41 2026 - [info] 172.25.254.10(172.25.254.10:3306) mon aug 10 15:48:41 2026 - [info] checking master reachability via mysql(double check)... mon aug 10 15:48:41 2026 - [info] ok. mon aug 10 15:48:41 2026 - [info] alive servers: mon aug 10 15:48:41 2026 - [info] 172.25.254.20(172.25.254.20:3306) mon aug 10 15:48:41 2026 - [info] 172.25.254.30(172.25.254.30:3306) mon aug 10 15:48:41 2026 - [info] alive slaves: mon aug 10 15:48:41 2026 - [info] 172.25.254.20(172.25.254.20:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:48:41 2026 - [info] gtid on mon aug 10 15:48:41 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 15:48:41 2026 - [info] primary candidate for the new master (candidate_master is set) mon aug 10 15:48:41 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:48:41 2026 - [info] gtid on mon aug 10 15:48:41 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 15:48:41 2026 - [info] not candidate for the new master (no_master is set) master 172.25.254.10(172.25.254.10:3306) is dead. proceed? (yes/no): yes mon aug 10 15:48:44 2026 - [info] starting gtid based failover. mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] ** phase 1: configuration check phase completed. mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] * phase 2: dead master shutdown phase.. mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] healthcheck: ssh to 172.25.254.10 is reachable. mon aug 10 15:48:44 2026 - [info] forcing shutdown so that applications never connect to the current master.. mon aug 10 15:48:44 2026 - [warning] master_ip_failover_script is not set. skipping invalidating dead master ip address. mon aug 10 15:48:44 2026 - [warning] shutdown_script is not set. skipping explicit shutting down of the dead master. mon aug 10 15:48:44 2026 - [info] * phase 2: dead master shutdown phase completed. mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] * phase 3: master recovery phase.. mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] * phase 3.1: getting latest slaves phase.. mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] the latest binary log file/position on all slaves is mysql-bin.000002:4859 mon aug 10 15:48:44 2026 - [info] latest slaves (slaves that received relay log files to the latest): mon aug 10 15:48:44 2026 - [info] 172.25.254.20(172.25.254.20:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:48:44 2026 - [info] gtid on mon aug 10 15:48:44 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 15:48:44 2026 - [info] primary candidate for the new master (candidate_master is set) mon aug 10 15:48:44 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:48:44 2026 - [info] gtid on mon aug 10 15:48:44 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 15:48:44 2026 - [info] not candidate for the new master (no_master is set) mon aug 10 15:48:44 2026 - [info] the oldest binary log file/position on all slaves is mysql-bin.000002:4859 mon aug 10 15:48:44 2026 - [info] oldest slaves: mon aug 10 15:48:44 2026 - [info] 172.25.254.20(172.25.254.20:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:48:44 2026 - [info] gtid on mon aug 10 15:48:44 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 15:48:44 2026 - [info] primary candidate for the new master (candidate_master is set) mon aug 10 15:48:44 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:48:44 2026 - [info] gtid on mon aug 10 15:48:44 2026 - [info] replicating from 172.25.254.10(172.25.254.10:3306) mon aug 10 15:48:44 2026 - [info] not candidate for the new master (no_master is set) mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] * phase 3.3: determining new master phase.. mon aug 10 15:48:44 2026 - [info] mon aug 10 15:48:44 2026 - [info] 172.25.254.20 can be new master. mon aug 10 15:48:44 2026 - [info] new master is 172.25.254.20(172.25.254.20:3306) mon aug 10 15:48:44 2026 - [info] starting master failover.. mon aug 10 15:48:44 2026 - [info] from: 172.25.254.10(172.25.254.10:3306) (current master) +--172.25.254.20(172.25.254.20:3306) +--172.25.254.30(172.25.254.30:3306) to: 172.25.254.20(172.25.254.20:3306) (new master) +--172.25.254.30(172.25.254.30:3306) starting master switch from 172.25.254.10(172.25.254.10:3306) to 172.25.254.20(172.25.254.20:3306)? (yes/no): yes mon aug 10 15:48:46 2026 - [info] new master decided manually is 172.25.254.20(172.25.254.20:3306) mon aug 10 15:48:46 2026 - [info] mon aug 10 15:48:46 2026 - [info] * phase 3.3: new master recovery phase.. mon aug 10 15:48:46 2026 - [info] mon aug 10 15:48:46 2026 - [info] waiting all logs to be applied.. mon aug 10 15:48:46 2026 - [info] done. mon aug 10 15:48:46 2026 - [info] getting new master's binlog name and position.. mon aug 10 15:48:46 2026 - [info] mysql-bin.000002:4953 mon aug 10 15:48:46 2026 - [info] all other slaves should start replication from here. statement should be: change master to master_host='172.25.254.20', master_port=3306, master_auto_position=1, master_user='lee', master_password='xxx'; mon aug 10 15:48:46 2026 - [info] master recovery succeeded. file:pos:exec_gtid_set: mysql-bin.000002, 4953, 43831566-93de-11f1-9d5a-000c29f930af:1-6, f5387b14-93dd-11f1-8cb7-000c29e79365:1-15 mon aug 10 15:48:46 2026 - [warning] master_ip_failover_script is not set. skipping taking over new master ip address. mon aug 10 15:48:46 2026 - [info] setting read_only=0 on 172.25.254.20(172.25.254.20:3306).. mon aug 10 15:48:46 2026 - [info] ok. mon aug 10 15:48:46 2026 - [info] ** finished master recovery successfully. mon aug 10 15:48:46 2026 - [info] * phase 3: master recovery phase completed. mon aug 10 15:48:46 2026 - [info] mon aug 10 15:48:46 2026 - [info] * phase 4: slaves recovery phase.. mon aug 10 15:48:46 2026 - [info] mon aug 10 15:48:46 2026 - [info] mon aug 10 15:48:46 2026 - [info] * phase 4.1: starting slaves in parallel.. mon aug 10 15:48:46 2026 - [info] mon aug 10 15:48:46 2026 - [info] -- slave recovery on host 172.25.254.30(172.25.254.30:3306) started, pid: 40148. check tmp log /etc/masterha/172.25.254.30_3306_20260810154840.log if it takes time.. mon aug 10 15:48:47 2026 - [info] mon aug 10 15:48:47 2026 - [info] log messages from 172.25.254.30 ... mon aug 10 15:48:47 2026 - [info] mon aug 10 15:48:46 2026 - [info] resetting slave 172.25.254.30(172.25.254.30:3306) and starting replication from the new master 172.25.254.20(172.25.254.20:3306).. mon aug 10 15:48:46 2026 - [info] executed change master. mon aug 10 15:48:46 2026 - [info] slave started. mon aug 10 15:48:46 2026 - [error][/usr/share/perl5/vendor_perl/mha/server.pm, ln974] gtid_wait(43831566-93de-11f1-9d5a-000c29f930af:1-6, f5387b14-93dd-11f1-8cb7-000c29e79365:1-15) returned null on 172.25.254.30(172.25.254.30:3306). maybe sql thread was aborted? mon aug 10 15:48:47 2026 - [info] end of log messages from 172.25.254.30. mon aug 10 15:48:47 2026 - [error][/usr/share/perl5/vendor_perl/mha/masterfailover.pm, ln2045] master failover to 172.25.254.20(172.25.254.20:3306) done, but recovery on slave partially failed. mon aug 10 15:48:47 2026 - [info] ----- failover report ----- app1: mysql master failover 172.25.254.10(172.25.254.10:3306) to 172.25.254.20(172.25.254.20:3306) master 172.25.254.10(172.25.254.10:3306) is down! check mha manager logs at mha for details. started manual(interactive) failover. selected 172.25.254.20(172.25.254.20:3306) as a new master. 172.25.254.20(172.25.254.20:3306): ok: applying all logs succeeded. 172.25.254.30(172.25.254.30:3306): error: failed on waiting gtid exec set on master. master failover to 172.25.254.20(172.25.254.20:3306) done, but recovery on slave partially failed. #这里有错误正常，因为我们把node1机子停了
#查看切换信息
[root@mysql-node3 ~]# mysql -uroot -plee -e "show slave status\g;"
mysql: [warning] using a password on the command line interface can be insecure.
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.20
master_user: lee
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 4953
relay_log_file: mysql-node3-relay-bin.000002
relay_log_pos: 422
relay_master_log_file: mysql-bin.000002
slave_io_running: yes
slave_sql_running: yes
#故障恢复
#当出现故障切换后，mha主机中会出现切换锁文件，当文件存在后不能再次执行切换
[root@mha ~]# ls /etc/masterha/
app1.cnf app1.failover.complete mha.log scripts
|
#锁文件
[root@mha ~]# rm -fr /etc/masterha/app1.failover.complete
[root@mysql-node2 ~]# mysql -uroot -plee -e "reset slave;"
#启动node1的机子恢复故障
[root@mysql-node1 ~]# systemctl start mysqld.service
[root@mysql-node1 ~]# mysql -uroot -plee -e "change master to master_host='172.25.254.20', master_user='lee', master_password='lee', master_auto_position=1;"
mysql: [warning] using a password on the command line interface can be insecure.
[root@mysql-node1 ~]# mysql -uroot -plee -e "start slave;"
mysql: [warning] using a password on the command line interface can be insecure.
[root@mysql-node1 ~]# mysql -uroot -plee -e "show slave status\g;" | head -n 15
mysql: [warning] using a password on the command line interface can be insecure.
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.20
master_user: lee
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 4953
relay_log_file: mysql-node1-relay-bin.000002
relay_log_pos: 422
relay_master_log_file: mysql-bin.000002
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
replicate_ignore_db:
#记得查看有问题没有
[root@mha masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf mon aug 10 15:57:48 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 15:57:48 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 15:57:48 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 15:57:48 2026 - [info] starting ssh connection tests.. mon aug 10 15:57:49 2026 - [debug] mon aug 10 15:57:48 2026 - [debug] connecting via ssh from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.20(172.25.254.20:22).. mon aug 10 15:57:48 2026 - [debug] ok. mon aug 10 15:57:48 2026 - [debug] connecting via ssh from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.30(172.25.254.30:22).. mon aug 10 15:57:49 2026 - [debug] ok. mon aug 10 15:57:49 2026 - [debug] mon aug 10 15:57:48 2026 - [debug] connecting via ssh from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.10(172.25.254.10:22).. mon aug 10 15:57:49 2026 - [debug] ok. mon aug 10 15:57:49 2026 - [debug] connecting via ssh from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.30(172.25.254.30:22).. mon aug 10 15:57:49 2026 - [debug] ok. mon aug 10 15:57:50 2026 - [debug] mon aug 10 15:57:49 2026 - [debug] connecting via ssh from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.10(172.25.254.10:22).. mon aug 10 15:57:49 2026 - [debug] ok. mon aug 10 15:57:49 2026 - [debug] connecting via ssh from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.20(172.25.254.20:22).. mon aug 10 15:57:50 2026 - [debug] ok. mon aug 10 15:57:50 2026 - [info] all ssh connection tests passed successfully. use of uninitialized value in exit at /usr/bin/masterha_check_ssh line 44. [root@mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf mon aug 10 15:57:56 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 15:57:56 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 15:57:56 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 15:57:56 2026 - [info] mha::mastermonitor version 0.58. mon aug 10 15:57:57 2026 - [info] gtid failover mode = 1 mon aug 10 15:57:57 2026 - [info] dead servers: mon aug 10 15:57:57 2026 - [info] alive servers: mon aug 10 15:57:57 2026 - [info] 172.25.254.10(172.25.254.10:3306) mon aug 10 15:57:57 2026 - [info] 172.25.254.20(172.25.254.20:3306) mon aug 10 15:57:57 2026 - [info] 172.25.254.30(172.25.254.30:3306) mon aug 10 15:57:57 2026 - [info] alive slaves: mon aug 10 15:57:57 2026 - [info] 172.25.254.10(172.25.254.10:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:57:57 2026 - [info] gtid on mon aug 10 15:57:57 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306) mon aug 10 15:57:57 2026 - [info] primary candidate for the new master (candidate_master is set) mon aug 10 15:57:57 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:57:57 2026 - [info] gtid on mon aug 10 15:57:57 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306) mon aug 10 15:57:57 2026 - [info] not candidate for the new master (no_master is set) mon aug 10 15:57:57 2026 - [info] current alive master: 172.25.254.20(172.25.254.20:3306) mon aug 10 15:57:57 2026 - [info] checking slave configurations.. mon aug 10 15:57:57 2026 - [info] read_only=1 is not set on slave 172.25.254.10(172.25.254.10:3306). mon aug 10 15:57:57 2026 - [info] read_only=1 is not set on slave 172.25.254.30(172.25.254.30:3306). mon aug 10 15:57:57 2026 - [info] checking replication filtering settings.. mon aug 10 15:57:57 2026 - [info] binlog_do_db= , binlog_ignore_db= mon aug 10 15:57:57 2026 - [info] replication filtering check ok. mon aug 10 15:57:57 2026 - [info] gtid (with auto-pos) is supported. skipping all ssh and node package checking. mon aug 10 15:57:57 2026 - [info] checking ssh publickey authentication settings on the current master.. mon aug 10 15:57:57 2026 - [info] healthcheck: ssh to 172.25.254.20 is reachable. mon aug 10 15:57:57 2026 - [info] 172.25.254.20(172.25.254.20:3306) (current master) +--172.25.254.10(172.25.254.10:3306) +--172.25.254.30(172.25.254.30:3306) mon aug 10 15:57:57 2026 - [info] checking replication health on 172.25.254.10.. mon aug 10 15:57:57 2026 - [info] ok. mon aug 10 15:57:57 2026 - [info] checking replication health on 172.25.254.30.. mon aug 10 15:57:57 2026 - [info] ok. mon aug 10 15:57:57 2026 - [warning] master_ip_failover_script is not defined. mon aug 10 15:57:57 2026 - [warning] shutdown_script is not defined. mon aug 10 15:57:57 2026 - [info] got exit code 0 (not master dead). mysql replication health is ok.
#现在也可以手动切换到node1 [root@mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000 mon aug 10 15:58:11 2026 - [info] mha::masterrotate version 0.58. mon aug 10 15:58:11 2026 - [info] starting online master switch.. mon aug 10 15:58:11 2026 - [info] mon aug 10 15:58:11 2026 - [info] * phase 1: configuration check phase.. mon aug 10 15:58:11 2026 - [info] mon aug 10 15:58:11 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 15:58:11 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 15:58:11 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. mon aug 10 15:58:12 2026 - [info] gtid failover mode = 1 mon aug 10 15:58:12 2026 - [info] current alive master: 172.25.254.20(172.25.254.20:3306) mon aug 10 15:58:12 2026 - [info] alive slaves: mon aug 10 15:58:12 2026 - [info] 172.25.254.10(172.25.254.10:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:58:12 2026 - [info] gtid on mon aug 10 15:58:12 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306) mon aug 10 15:58:12 2026 - [info] primary candidate for the new master (candidate_master is set) mon aug 10 15:58:12 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled mon aug 10 15:58:12 2026 - [info] gtid on mon aug 10 15:58:12 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306) mon aug 10 15:58:12 2026 - [info] not candidate for the new master (no_master is set) it is better to execute flush no_write_to_binlog tables on the master before switching. is it ok to execute on 172.25.254.20(172.25.254.20:3306)? (yes/no): yes mon aug 10 15:58:14 2026 - [info] executing flush no_write_to_binlog tables. this may take long time.. mon aug 10 15:58:14 2026 - [info] ok. mon aug 10 15:58:14 2026 - [info] checking mha is not monitoring or doing failover.. mon aug 10 15:58:14 2026 - [info] checking replication health on 172.25.254.10.. mon aug 10 15:58:14 2026 - [info] ok. mon aug 10 15:58:14 2026 - [info] checking replication health on 172.25.254.30.. mon aug 10 15:58:14 2026 - [info] ok. mon aug 10 15:58:14 2026 - [info] 172.25.254.10 can be new master. mon aug 10 15:58:14 2026 - [info] from: 172.25.254.20(172.25.254.20:3306) (current master) +--172.25.254.10(172.25.254.10:3306) +--172.25.254.30(172.25.254.30:3306) to: 172.25.254.10(172.25.254.10:3306) (new master) +--172.25.254.30(172.25.254.30:3306) +--172.25.254.20(172.25.254.20:3306) starting master switch from 172.25.254.20(172.25.254.20:3306) to 172.25.254.10(172.25.254.10:3306)? (yes/no): yes mon aug 10 15:58:16 2026 - [info] checking whether 172.25.254.10(172.25.254.10:3306) is ok for the new master.. mon aug 10 15:58:16 2026 - [info] ok. mon aug 10 15:58:16 2026 - [info] ** phase 1: configuration check phase completed. mon aug 10 15:58:16 2026 - [info] mon aug 10 15:58:16 2026 - [info] * phase 2: rejecting updates phase.. mon aug 10 15:58:16 2026 - [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 mon aug 10 15:58:17 2026 - [info] locking all tables on the orig master to reject updates from everybody (including root): mon aug 10 15:58:17 2026 - [info] executing flush tables with read lock.. mon aug 10 15:58:17 2026 - [info] ok. mon aug 10 15:58:17 2026 - [info] orig master binlog:pos is mysql-bin.000002:4953. mon aug 10 15:58:17 2026 - [info] waiting to execute all relay logs on 172.25.254.10(172.25.254.10:3306).. mon aug 10 15:58:17 2026 - [info] master_pos_wait(mysql-bin.000002:4953) completed on 172.25.254.10(172.25.254.10:3306). executed 0 events. mon aug 10 15:58:17 2026 - [info] done. mon aug 10 15:58:17 2026 - [info] getting new master's binlog name and position.. mon aug 10 15:58:17 2026 - [info] mysql-bin.000004:238 mon aug 10 15:58:17 2026 - [info] all other slaves should start replication from here. statement should be: change master to master_host='172.25.254.10', master_port=3306, master_auto_position=1, master_user='lee', master_password='xxx'; mon aug 10 15:58:17 2026 - [info] mon aug 10 15:58:17 2026 - [info] * switching slaves in parallel.. mon aug 10 15:58:17 2026 - [info] mon aug 10 15:58:17 2026 - [info] -- slave switch on host 172.25.254.30(172.25.254.30:3306) started, pid: 40197 mon aug 10 15:58:17 2026 - [info] mon aug 10 15:58:19 2026 - [info] log messages from 172.25.254.30 ... mon aug 10 15:58:19 2026 - [info] mon aug 10 15:58:17 2026 - [info] waiting to execute all relay logs on 172.25.254.30(172.25.254.30:3306).. mon aug 10 15:58:17 2026 - [info] master_pos_wait(mysql-bin.000002:4953) completed on 172.25.254.30(172.25.254.30:3306). executed 0 events. mon aug 10 15:58:17 2026 - [info] done. mon aug 10 15:58:17 2026 - [info] resetting slave 172.25.254.30(172.25.254.30:3306) and starting replication from the new master 172.25.254.10(172.25.254.10:3306).. mon aug 10 15:58:17 2026 - [info] executed change master. mon aug 10 15:58:18 2026 - [info] slave started. mon aug 10 15:58:19 2026 - [info] end of log messages from 172.25.254.30 ... mon aug 10 15:58:19 2026 - [info] mon aug 10 15:58:19 2026 - [info] -- slave switch on host 172.25.254.30(172.25.254.30:3306) succeeded. mon aug 10 15:58:19 2026 - [info] unlocking all tables on the orig master: mon aug 10 15:58:19 2026 - [info] executing unlock tables.. mon aug 10 15:58:19 2026 - [info] ok. mon aug 10 15:58:19 2026 - [info] starting orig master as a new slave.. mon aug 10 15:58:19 2026 - [info] resetting slave 172.25.254.20(172.25.254.20:3306) and starting replication from the new master 172.25.254.10(172.25.254.10:3306).. mon aug 10 15:58:19 2026 - [info] executed change master. mon aug 10 15:58:20 2026 - [info] slave started. mon aug 10 15:58:20 2026 - [info] all new slave servers switched successfully. mon aug 10 15:58:20 2026 - [info] mon aug 10 15:58:20 2026 - [info] * phase 5: new master cleanup phase.. mon aug 10 15:58:20 2026 - [info] mon aug 10 15:58:20 2026 - [info] 172.25.254.10: resetting slave info succeeded. mon aug 10 15:58:20 2026 - [info] switching master to 172.25.254.10(172.25.254.10:3306) completed successfully.
#如果node2的机子出现以下问题是正常的
[root@mysql-node2 ~]# mysql -uroot -plee -e "show slave status\g"
mysql: [warning] using a password on the command line interface can be insecure.
*************************** 1. row ***************************
slave_io_state:
master_host: 172.25.254.10
master_user: lee
master_port: 3306
connect_retry: 60
master_log_file:
read_master_log_pos: 4
relay_log_file: mysql-node2-relay-bin.000001
relay_log_pos: 4
relay_master_log_file:
slave_io_running: no
slave_sql_running: no
2.自动切换
#为了方便观察建议开启两个shell [root@mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf & [1] 40337 [root@mha masterha]# mon aug 10 16:24:49 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping. mon aug 10 16:24:49 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf.. mon aug 10 16:24:49 2026 - [info] reading server configuration from /etc/masterha/app1.cnf.. ^c [root@mha masterha]# jobs [1]+ 运行中 masterha_manager --conf=/etc/masterha/app1.cnf & #在另一台shell里面打开 [root@mha ~]# watch -n 1 cat /etc/masterha/mha.log #当我把node1机子停了之后，检查三遍，自动切换到20 [root@mysql-node1 ~]# systemctl stop mysqld.service
[root@mysql-node3 ~]# mysql -uroot -plee -e "show slave status\g;"
mysql: [warning] using a password on the command line interface can be insecure.
*************************** 1. row ***************************
slave_io_state: waiting for source to send event
master_host: 172.25.254.20
master_user: lee
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 4953
relay_log_file: mysql-node3-relay-bin.000002
relay_log_pos: 422
relay_master_log_file: mysql-bin.000002
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
vip功能及vip的启动切换
[root@mha ~]# ls -l /etc/masterha/scripts/ 总用量 8 -rw-r--r-- 1 root root 2158 8月 10 16:40 master_ip_failover -rw-r--r-- 1 root root 3813 8月 10 16:40 master_ip_online_change [root@mha ~]# chmod +x /etc/masterha/scripts/master_ip_failover [root@mha ~]# chmod +x /etc/masterha/scripts/master_ip_online_change [root@mha ~]# mkdir /etc/masterha/scripts [root@mha ~]# cp mha-7/master_ip_* /etc/masterha/scripts [root@mha ~]# vim /etc/masterha/app1.cnf master_ip_failover_script= /etc/masterha/scripts/master_ip_failover master_ip_online_change_script= /etc/masterha/scripts/master_ip_online_change [root@mha ~]# vim /etc/masterha/scripts/master_ip_failover my $vip = '172.25.254.200/24'; [root@mha ~]# vim /etc/masterha/scripts/master_ip_online_change my $vip = '172.25.254.200/24';
测试
[root@mysql-node2 ~]# ip a a 172.25.254.200/24 dev eth0
[root@mysql-node2 ~]# ip a
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 mq state up group default qlen 1000
link/ether 00:0c:29:f9:30:af brd ff:ff:ff:ff:ff:ff
altname enp3s0
altname ens160
inet 172.25.254.20/24 brd 172.25.254.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 172.25.254.200/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::38ca:adc5:ce6e:f574/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@mha ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
mon aug 10 17:06:33 2026 - [info] mha::masterrotate version 0.58.
mon aug 10 17:06:33 2026 - [info] starting online master switch..
mon aug 10 17:06:33 2026 - [info]
mon aug 10 17:06:33 2026 - [info] * phase 1: configuration check phase..
mon aug 10 17:06:33 2026 - [info]
mon aug 10 17:06:33 2026 - [warning] global configuration file /etc/masterha_default.cnf not found. skipping.
mon aug 10 17:06:33 2026 - [info] reading application default configuration from /etc/masterha/app1.cnf..
mon aug 10 17:06:33 2026 - [info] reading server configuration from /etc/masterha/app1.cnf..
mon aug 10 17:06:34 2026 - [info] gtid failover mode = 1
mon aug 10 17:06:34 2026 - [info] current alive master: 172.25.254.20(172.25.254.20:3306)
mon aug 10 17:06:34 2026 - [info] alive slaves:
mon aug 10 17:06:34 2026 - [info] 172.25.254.10(172.25.254.10:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled
mon aug 10 17:06:34 2026 - [info] gtid on
mon aug 10 17:06:34 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306)
mon aug 10 17:06:34 2026 - [info] primary candidate for the new master (candidate_master is set)
mon aug 10 17:06:34 2026 - [info] 172.25.254.30(172.25.254.30:3306) version=8.3.0 (oldest major version between slaves) log-bin:enabled
mon aug 10 17:06:34 2026 - [info] gtid on
mon aug 10 17:06:34 2026 - [info] replicating from 172.25.254.20(172.25.254.20:3306)
mon aug 10 17:06:34 2026 - [info] not candidate for the new master (no_master is set)
it is better to execute flush no_write_to_binlog tables on the master before switching. is it ok to execute on 172.25.254.20(172.25.254.20:3306)? (yes/no): yes
mon aug 10 17:06:36 2026 - [info] executing flush no_write_to_binlog tables. this may take long time..
mon aug 10 17:06:36 2026 - [info] ok.
mon aug 10 17:06:36 2026 - [info] checking mha is not monitoring or doing failover..
mon aug 10 17:06:36 2026 - [info] checking replication health on 172.25.254.10..
mon aug 10 17:06:36 2026 - [info] ok.
mon aug 10 17:06:36 2026 - [info] checking replication health on 172.25.254.30..
mon aug 10 17:06:36 2026 - [info] ok.
mon aug 10 17:06:36 2026 - [info] 172.25.254.10 can be new master.
mon aug 10 17:06:36 2026 - [info]
from:
172.25.254.20(172.25.254.20:3306) (current master)
+--172.25.254.10(172.25.254.10:3306)
+--172.25.254.30(172.25.254.30:3306)
to:
172.25.254.10(172.25.254.10:3306) (new master)
+--172.25.254.30(172.25.254.30:3306)
+--172.25.254.20(172.25.254.20:3306)
starting master switch from 172.25.254.20(172.25.254.20:3306) to 172.25.254.10(172.25.254.10:3306)? (yes/no): yes
mon aug 10 17:06:38 2026 - [info] checking whether 172.25.254.10(172.25.254.10:3306) is ok for the new master..
mon aug 10 17:06:38 2026 - [info] ok.
mon aug 10 17:06:38 2026 - [info] ** phase 1: configuration check phase completed.
mon aug 10 17:06:38 2026 - [info]
mon aug 10 17:06:38 2026 - [info] * phase 2: rejecting updates phase..
mon aug 10 17:06:38 2026 - [info]
mon aug 10 17:06:38 2026 - [info] executing master ip online change script to disable write on the current master:
mon aug 10 17:06:38 2026 - [info] /etc/masterha/scripts/master_ip_online_change --command=stop --orig_master_host=172.25.254.20 --orig_master_ip=172.25.254.20 --orig_master_port=3306 --orig_master_user='root' --new_master_host=172.25.254.10 --new_master_ip=172.25.254.10 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
***************************************************************
disabling the vip - 172.25.0.200/24 on old master: 172.25.254.20
***************************************************************
error: ipv4: address not found.
mon aug 10 17:06:38 2026 - [info] ok.
mon aug 10 17:06:38 2026 - [info] locking all tables on the orig master to reject updates from everybody (including root):
mon aug 10 17:06:38 2026 - [info] executing flush tables with read lock..
mon aug 10 17:06:38 2026 - [info] ok.
mon aug 10 17:06:38 2026 - [info] orig master binlog:pos is mysql-bin.000003:238.
mon aug 10 17:06:38 2026 - [info] waiting to execute all relay logs on 172.25.254.10(172.25.254.10:3306)..
mon aug 10 17:06:38 2026 - [info] master_pos_wait(mysql-bin.000003:238) completed on 172.25.254.10(172.25.254.10:3306). executed 0 events.
mon aug 10 17:06:38 2026 - [info] done.
mon aug 10 17:06:38 2026 - [info] getting new master's binlog name and position..
mon aug 10 17:06:38 2026 - [info] mysql-bin.000005:238
mon aug 10 17:06:38 2026 - [info] all other slaves should start replication from here. statement should be: change master to master_host='172.25.254.10', master_port=3306, master_auto_position=1, master_user='lee', master_password='xxx';
mon aug 10 17:06:38 2026 - [info] executing master ip online change script to allow write on the new master:
mon aug 10 17:06:38 2026 - [info] /etc/masterha/scripts/master_ip_online_change --command=start --orig_master_host=172.25.254.20 --orig_master_ip=172.25.254.20 --orig_master_port=3306 --orig_master_user='root' --new_master_host=172.25.254.10 --new_master_ip=172.25.254.10 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
***************************************************************
enabling the vip - 172.25.0.200/24 on new master: 172.25.254.10
***************************************************************
mon aug 10 17:06:38 2026 - [info] ok.
mon aug 10 17:06:38 2026 - [info]
mon aug 10 17:06:38 2026 - [info] * switching slaves in parallel..
mon aug 10 17:06:38 2026 - [info]
mon aug 10 17:06:38 2026 - [info] -- slave switch on host 172.25.254.30(172.25.254.30:3306) started, pid: 45538
mon aug 10 17:06:38 2026 - [info]
mon aug 10 17:06:40 2026 - [info] log messages from 172.25.254.30 ...
mon aug 10 17:06:40 2026 - [info]
mon aug 10 17:06:38 2026 - [info] waiting to execute all relay logs on 172.25.254.30(172.25.254.30:3306)..
mon aug 10 17:06:38 2026 - [info] master_pos_wait(mysql-bin.000003:238) completed on 172.25.254.30(172.25.254.30:3306). executed 0 events.
mon aug 10 17:06:38 2026 - [info] done.
mon aug 10 17:06:38 2026 - [info] resetting slave 172.25.254.30(172.25.254.30:3306) and starting replication from the new master 172.25.254.10(172.25.254.10:3306)..
mon aug 10 17:06:38 2026 - [info] executed change master.
mon aug 10 17:06:39 2026 - [info] slave started.
mon aug 10 17:06:40 2026 - [info] end of log messages from 172.25.254.30 ...
mon aug 10 17:06:40 2026 - [info]
mon aug 10 17:06:40 2026 - [info] -- slave switch on host 172.25.254.30(172.25.254.30:3306) succeeded.
mon aug 10 17:06:40 2026 - [info] unlocking all tables on the orig master:
mon aug 10 17:06:40 2026 - [info] executing unlock tables..
mon aug 10 17:06:40 2026 - [info] ok.
mon aug 10 17:06:40 2026 - [info] starting orig master as a new slave..
mon aug 10 17:06:40 2026 - [info] resetting slave 172.25.254.20(172.25.254.20:3306) and starting replication from the new master 172.25.254.10(172.25.254.10:3306)..
mon aug 10 17:06:40 2026 - [info] executed change master.
mon aug 10 17:06:41 2026 - [info] slave started.
mon aug 10 17:06:41 2026 - [info] all new slave servers switched successfully.
mon aug 10 17:06:41 2026 - [info]
mon aug 10 17:06:41 2026 - [info] * phase 5: new master cleanup phase..
mon aug 10 17:06:41 2026 - [info]
mon aug 10 17:06:41 2026 - [info] 172.25.254.10: resetting slave info succeeded.
mon aug 10 17:06:41 2026 - [info] switching master to 172.25.254.10(172.25.254.10:3306) completed successfully.
[root@mysql-node1 ~]# ip a
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 mq state up group default qlen 1000
link/ether 00:0c:29:e7:93:65 brd ff:ff:ff:ff:ff:ff
altname enp3s0
altname ens160
inet 172.25.254.10/24 brd 172.25.254.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 172.25.0.200/24 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::6d9e:f307:9836:9db1/64 scope link noprefixroute
valid_lft forever preferred_lft forever
五.还原mysql所有节点
利用ansible还原所有节点
#利用ansible还原所有节点
[root@mha ~]# cat > /etc/yum.repos.d/epel.repo <<eof
> [epel]
> name = epel
> baseurl = https://mirrors.aliyun.com/epel-archive/9.6/everything/x86_64/
> gpgcheck = 0
> eof
[root@mha ~]# dnf install ansible -y
[root@mha ~]# ansible --version
ansible [core 2.14.18]
config file = /etc/ansible/ansible.cfg
configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python3.9/site-packages/ansible
ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
executable location = /usr/bin/ansible
python version = 3.9.21 (main, feb 10 2025, 00:00:00) [gcc 11.5.0 20240719 (red hat 11.5.0-5)] (/usr/bin/python3)
jinja version = 3.1.2
libyaml = true
[root@mha ~]# useradd devops
[root@mha ~]# echo lee | passwd --stdin devops
[root@mha ~]# su - devops
[devops@mha ~]$ mkdir ansible
[devops@mha ansible]$ cat >ansible.cfg <<eof
[defaults]
inventory=./inventory
remote_user=root
host_key_checking=false
[privilege_escalation]
become=false
eof
[devops@mha ansible]$ ansible mysql -m user -a 'name=devops'
[devops@mha ansible]$ ansible mysql -m shell -a 'echo devops | passwd --stdin devops'
[devops@mha ansible]$ ansible mysql -m shell -a 'echo "devops all=(all) nopasswd: all" >> /etc/sudoers'
[devops@mha ansible]$ ansible all -m file -a 'path=/home/devops/.ssh owner=devops group=devops mode="0700" state=directory'
[devops@mha ansible]$ ansible all -m copy -a 'src=/home/devops/.ssh/authorized_keys dest=/home/devops/.ssh/authorized_keys owner=devops group=devops mode='0600''
[devops@mha ansible]$ cat >ansible.cfg <<eof
[defaults]
inventory=./inventory
remote_user=devops
host_key_checking=false
[privilege_escalation]
become=true
become_ask_pass=false
become_method=sudo
become_user=root
eof
[devops@mha ansible]$ ansible all -m shell -a 'whoami'
172.25.254.20 | changed | rc=0 >>
root
172.25.254.30 | changed | rc=0 >>
root
172.25.254.10 | changed | rc=0 >>
root
[devops@mha ansible]$ vim clear_mysql.yml
- name: reset mysql
hosts: mysql
tasks:
- name: stop mysql
shell: '/etc/init.d/mysqld stop'
ignore_errors: yes
- name: delete mysql data
file:
path: /data/mysql
state: absent
- name: crate data directroy
file:
path: /data/mysql
state: directory
owner: mysql
group: mysql
- name: initialize mysql
shell: '/usr/local/mysql/bin/mysqld --initialize --user=mysql'
[devops@mha ansible]$ ansible-playbook clear_mysql.yml -vv | grep password手动还原方式
所有节点初始化数据 [root@mysql-node1/2 /3~]# systemctl stop mysqld [root@mysql-node1/2/3 ~]# rm -rf /data/mysql/* [root@mysql-node1/2/3 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock symbolic-links=0 #不同主机server-id一定要根据实际情况做相应改变 server-id=10|20|30 log-bin=mysql-bin gtid_mode=on enforce-gtid-consistency=on default_authentication_plugin=mysql_native_password log_slave_updates=on binlog_format=row binlog_checksum=none disabled_storage_engines="myisam,blackhole,federated,archive,memory" plugin_load_add='group_replication.so' group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" group_replication_start_on_boot=off group_replication_local_address="172.25.254.10|20|30:33061" #其他两台主机一定要根据ip进行修改 group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061" group_replication_bootstrap_group=off group_replication_single_primary_mode=off [root@mysql-node1|2|3 ~]# mysqld --user=mysql --initialize [root@mysql-node1|2|3 ~]# systemctl daemon-reload [root@mysql-node1|2|3 ~]# systemctl start mysqld.service
部署组复制
#配置组复制-在首台主机中 [root@mysql-node1 ~]# mysql -uroot -p' heks5!z3e:xe' mysql> alter user root@localhost identified by 'lee'; query ok, 0 rows affected (0.04 sec) mysql> set sql_log_bin=0; query ok, 0 rows affected (0.00 sec) mysql> create user rpl_user@'%' identified by 'lee'; query ok, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> grant connection_admin on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> grant backup_admin on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> grant group_replication_stream on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> flush privileges; query ok, 0 rows affected (0.00 sec) mysql> set sql_log_bin=1; query ok, 0 rows affected (0.00 sec) mysql> change replication source to source_user='rpl_user', source_password='lee' for channel 'group_replication_recovery'; query ok, 0 rows affected, 2 warnings (0.01 sec) mysql> show plugins; #查看组复制插件是否激活 | group_replication | active | group replication | group_replication.so | gpl | mysql> set global group_replication_bootstrap_group=on; query ok, 0 rows affected (0.00 sec) mysql> start group_replication user='rpl_user', password='lee'; query ok, 0 rows affected (1.10 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 | member_role | member_version | member_communication_stack | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | ac3d6eaf-1a0a-11f1-9efa-000c29f4a60c | mysql-node1 | 3306 | online | primary | 8.3.0 | xcom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 1 row in set (0.00 sec) #配置组复制在其余主机中 [root@mysql-node2/3 ~]# systemctl daemon-reload [root@mysql-node2/3 ~]# systemctl start mysqld.service mysql> alter user root@localhost identified by 'lee'; query ok, 0 rows affected (0.00 sec) mysql> set sql_log_bin=0; query ok, 0 rows affected (0.00 sec) mysql> create user rpl_user@'%' identified by 'lee'; query ok, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> grant connection_admin on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> grant backup_admin on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> grant group_replication_stream on *.* to rpl_user@'%'; query ok, 0 rows affected (0.00 sec) mysql> set sql_log_bin=1; query ok, 0 rows affected (0.00 sec) mysql> change replication source to source_user='rpl_user',source_password='lee' for channel 'group_replication_recovery'; query ok, 0 rows affected, 2 warnings (0.00 sec) mysql> start group_replication user='rpl_user', password='lee'; error 3092 (hy000): the server is not configured properly to be an active member of the group. please see more details on error log. #出现此处报错可以初始化下master mysql> reset master; #用过此命令解决以上报错 query ok, 0 rows affected, 1 warning (0.04 sec) mysql> start group_replication user='rpl_user', password='lee'; query ok, 0 rows affected (7.94 sec) #出现一下代表成功了 mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | channel_name | member_id | member_host | member_port | member_state | member_role | member_version | member_communication_stack | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | ac3d6eaf-1a0a-11f1-9efa-000c29f4a60c | mysql-node1 | 3306 | online | primary | 8.3.0 | xcom | | group_replication_applier | e0b37b20-1a0b-11f1-a62c-000c29e84b64 | mysql-node2 | 3306 | online | primary | 8.3.0 | xcom | | group_replication_applier | e0b37b20-1a0b-11f1-a62c-000c29e84b64 | mysql-node3 | 3306 | online | primary | 8.3.0 | xcom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 2 rows in set (0.00 sec)
测试
测试所有节点是否可以执行读写并数据是否同步
#node1中
mysql> create database xia;
query ok, 1 row affected (0.00 sec)
mysql> create table xia.userlist (
-> username varchar(10) primary key not null,
-> password varchar(50) not null
-> );
query ok, 0 rows affected (0.01 sec)
mysql> insert into xia.userlist values ('first','abc');
query ok, 1 row affected (0.01 sec)
#在node2中查看并插入新的数据
mysql> select * from xia.userlist;
+----------+----------+
| username | password |
+----------+----------+
| first | abc |
+----------+----------+
1 row in set (0.00 sec)
mysql> insert into xia.userlist values ('second','def');
query ok, 1 row affected (0.01 sec)
mysql> select * from xia.userlist;
+----------+----------+
| username | password |
+----------+----------+
| first | abc |
| second | def |
+----------+----------+
2 rows in set (0.01 sec)
#在node3中查看并插入数据
mysql> select * from xia.userlist;
+----------+----------+
| username | password |
+----------+----------+
| first | abc |
| second | def |
+----------+----------+
2 rows in set (0.00 sec)
mysql> delete from xia.userlist where name='first'
query ok, 1 row affected (0.01 sec)
mysql> select * from timinglee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| second | def |
+----------+----------+
1rows in set (0.00 sec)
mysql>
总结
到此这篇关于mysql集群技术的文章就介绍到这了,更多相关mysql集群技术内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
![nginx报错nginx:[emerg] bind() to 0.0.0.0:80 failed(98: Address already in use)问题](https://images1.3wcode.com/3wcode/20260821/s_0_202608211207528181.jpg)
发表评论