目录
一 Mysql 在服务器中的部署方法
1.1 在Linux下部署mysql
1.1.1 安装依赖性并解压源码包,源码编译安装mysql:
1.1.2 部署mysql
二 mysql的组从复制
2.1 配置mastesr和salve
测试结果
2.2 当有数据时添加slave2
2.3 延迟复制
2.4 慢查询日志
测试结果
2.5 mysql的并行复制
2.6 原理刨析
2.7 架构缺陷
三 半同步模式
3.1半同步模式原理
3.2 gtid模式
3.3.启用半同步模式
3.4.测试 在master端写入数据
四 mysql高可用之组复制 (MGR)
4.1 组复制流程
4.2 组复制单主和多主模式
4.3.实现mysql组复制
五 mysql-router(mysql路由)
六 mysql高可用之MHA
6.1.MHA概述
6.2 MHA部署实施
6.2.1 搭建主两从架构
6.2.2安装MHA所需要的软件
6.2.3 配置MHA 的管理环境
6.2.3 MHA的故障切换
6.2.3 为MHA添加VIP功能
一 Mysql 在服务器中的部署方法
在企业中90%的服务器操作系统均为Linux
在企业中对于Mysql的安装通常用源码编译的方式来进行
1.1 在Linux下部署mysql
1.1.1 安装依赖性并解压源码包,源码编译安装mysql:
注意:当cmake出错后如果想重新检测,删除 mysql-5.7.44 中 CMakeCache.txt即可
[root@mysql-node2 ~]# tar zxf mysql-boost-5.7.44.tar.gz
[root@mysql-node2 ~]# cd mysql-5.7.44/[root@mysql-node2 mysql-5.7.44]# yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64 -y[root@mysql-node2 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/[root@mysql-node2 mysql-5.7.44]# make -j2
[root@mysql-node2 mysql-5.7.44]# make install
1.1.2 部署mysql
[root@mysql-node1 mysql-5.7.44]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 ~]# cd /usr/local/mysql/
[root@mysql-node1 mysql]# mkdir /data/mysql -p
[root@mysql-node1 mysql]# chown mysql.mysql -R /data/mysql/[root@mysql-node1 mysql]# cd support-files/
[root@mysql-node1 support-files]# cp mysql.server /etc/init.d/mysqld
[root@mysql-node1 support-files]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0[root@mysql-node1 support-files]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin[root@mysql-node1 support-files]# source ~/.bash_profile [root@mysql-node1 support-files]# mysqld --user mysql --initialize
2024-08-22T02:37:04.001552Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-22T02:37:04.805970Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-22T02:37:04.914701Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-22T02:37:04.986356Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6b30c927-602f-11ef-a32a-000c29dbd129.
2024-08-22T02:37:04.987245Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-22T02:37:05.136107Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:37:05.136123Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:37:05.136851Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-22T02:37:05.213971Z 1 [Note] A temporary password is generated for root@localhost: uHbVyl,xw2d?[root@mysql-node1 ~]# vim passwd
[root@mysql-node1 ~]# echo uHbVyl,xw2d? > passwd[root@mysql-node1 ~]# chkconfig mysqld on
[root@mysql-node1 ~]# chkconfig --listNote: This output shows SysV services only and does not include nativesystemd services. SysV configuration data might be overridden by nativesystemd configuration.If you want to list systemd services use 'systemctl list-unit-files'.To see services enabled on particular target use'systemctl list-dependencies [target]'.mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off
network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
rhnsd 0:off 1:off 2:on 3:on 4:on 5:on 6:off[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.exam.com.err'.SUCCESS!
[root@mysql-node1 ~]# mysql_secure_installation
测试:
[root@node10 ~]# mysql -uroot -predhatmysql: [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 8Server version: 5.7.44 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)
二 mysql的组从复制
2.1 配置mastesr和salve
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS! [root@mysql-node1 ~]# mysql -uroot -predhat
mysql> create user repl@'%' identified by 'test';
Query OK, 0 rows affected (0.01 sec)mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 595 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS! [root@mysql-node2 ~]# mysql -predhat
mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='test,',master_log_file='mysql-bin.000001',master_log_pos=595;
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G
测试结果
[root@mysql-node1 ~]# mysql -predhat
mysql> create database test;
Query OK, 1 row affected (0.02 sec)mysql> create table test.userlist (-> username varchar(20) not null,-> password varchar(50) not null-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into test.userlist value ('test','123');
Query OK, 1 row affected (0.03 sec)mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| test | 123 |
+----------+----------+
1 row in set (0.00 sec)[root@mysql-node2 ~]# mysql -predhat
mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| test | 123 |
+----------+----------+
1 row in set (0.00 sec)
2.2 当有数据时添加slave2
完成基础配置
[root@mysql-node3 ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0server-id=30[root@mysql-node3 ~]# /etc/init.d/mysqld restart
生产环境中备份时需要锁表,保证备份前后的数据一致
mysql> FLUSH TABLES WITH READ LOCK;
备份后再解锁
mysql> UNLOCK TABLES;
mysqldump命令备份的数据文件,在还原时先DROP TABLE,需要合并数据时需要删除此语句
[root@mysql-node1 ~]# mysql -uroot -predhat
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)[root@mysql-node1 ~]# mysqldump -uroot -predhat test > test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@mysql-node1 ~]# scp test.sql root@172.25.254.30:/root/
root@172.25.254.30's password:
test.sql 100% 1945 1.7MB/s 00:00 [root@mysql-node1 ~]# mysql -uroot -predhat -e "SHOW MASTER STATUS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1245 | | | |
+------------------+----------+--------------+------------------+-------------------+[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30[root@mysql-node3 ~]# mysql -uroot -predhat
mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='test,',master_log_file='mysql-bin.000001',master_log_pos=1245;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Connecting to masterMaster_Host: 172.25.254.10Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1245Relay_Log_File: mysql-node3-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: ConnectingSlave_SQL_Running: Yes
2.3 延迟复制
延迟复制时用来控制sql线程的,和i/o线程无关
这个延迟复制不是i/o线程过段时间来复制,i/o是正常工作的
是日志已经保存在slave端了,那个sql要等多久进行回放
在master中写入数据后过了延迟时间才能被查询到
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.00 sec)mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;Master_UUID: 6b30c927-602f-11ef-a32a-000c29dbd129Master_Info_File: /data/mysql/master.infoSQL_Delay: 60SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400
2.4 慢查询日志
- 慢查询,顾名思义,执行很慢的查询
- 当执行SQL超过long_query_time参数设定的时间阈值(默认10s)时,就被认为是慢查询,这个 SQL语句就是需要优化的
- 慢查询被记录在慢查询日志里
- 慢查询日志默认是不开启的
- 如果需要优化SQL语句,就可以开启这个功能,它可以让你很容易地知道哪些语句是需要优化的。
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)mysql> set long_query_time=4;
Query OK, 0 rows affected (0.01 sec)mysql> show variables like "long%";
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.01 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.01 sec)[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
测试结果
mysql> slect sleep(10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slect sleep(10)' at line 1
mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.01 sec)[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
# Time: 2024-08-22T08:38:31.989061Z
# User@Host: root[root] @ localhost [] Id: 43
# Query_time: 10.011055 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1724315911;
select sleep(10);
2.5 mysql的并行复制
默认情况下slave中使用的是sql单线程回放
在master中时多用户读写,如果使用sql单线程回放那么会造成组从延迟严重
开启MySQL的多线程回放可以解决上述问题
注意:MySQL 组提交(Group commit)是一个性能优化特性,它允许在一个事务日志同步操作中将多个 事务的日志记录一起写入。这样做可以减少磁盘I/O的次数,从而提高数据库的整体性能。
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON[root@mysql-node2 ~]# /etc/init.d/mysqld restart[root@mysql-node2 ~]# mysql -uroot -predhat
mysql> show processlist;
此时sql线程转化为协调线程,16个worker负责处理sql协调线程发送过来的处理请求
2.6 原理刨析
三个线程
实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中,会基于3 个线程来操作, 一个主库线程,两个从库线程。
- 二进制日志转储线程(Binlog dump thread)是一个主库线程。当从库线程连接的时候, 主库可以 将二进制日志发送给从库,当主库读取事件(Event)的时候,会在 Binlog 上加锁,读取完成之 后,再将锁释放掉。
- 从库 I/O 线程会连接到主库,向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库 的二进制日志转储线程发送的 Binlog 更新部分,并且拷贝到本地的中继日志 (Relay log)。
- 从库 SQL 线程会读取从库中的中继日志,并且执行日志中的事件,将从库中的数据与主库保持同步。
复制三步骤
- 步骤1:Master将写操作记录到二进制日志(binlog)。
- 步骤2:Slave将Master的binary log events拷贝到它的中继日志(relay log);
- 步骤3:Slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化 的,而且重启后从接入点开始复制。
具体操作
1.slaves端中设置了master端的ip,用户,日志,和日志的Position,通过这些信息取得master的认证及信息
2.master端在设定好binlog启动后会开启binlog dump的线程
3.master端的binlog dump把二进制的更新发送到slave端的
4.slave端开启两个线程,一个是I/O线程,一个是sql线程,
- i/o线程用于接收master端的二进制日志,此线程会在本地打开relaylog中继日志,并且保存到本地 磁盘
- sql线程读取本地relog中继日志进行回放
5.什么时候我们需要多个slave?
当读取的而操作远远高与写操作时。我们采用一主多从架构
数据库外层接入负载均衡层并搭配高可用机制
2.7 架构缺陷
- 主从架构采用的是异步机制
- master更新完成后直接发送二进制日志到slave,但是slaves是否真正保存了数据master端不会检测
- master端直接保存二进制日志到磁盘
- 当master端到slave端的网络出现问题时或者master端直接挂掉,二进制日志可能根本没有到达slave
- master出现问题slave端接管master,这个过程中数据就丢失了
- 这样的问题出现就无法达到数据的强一致性,零数据丢失
三 半同步模式
3.1半同步模式原理
- 1.用户线程写入完成后master中的dump会把日志推送到slave端
- 2.slave中的io线程接收后保存到relaylog中继日志
- 3.保存完成后slave向master端返回ack
- 4.在未接受到slave的ack时master端时不做提交的,一直处于等待当收到ack后提交到存储引擎
- 5.在5.6版本中用到的时after_commit模式,after_commit模式时先提交在等待ack返回后输出ok
3.2 gtid模式
[root@mysql-node1 ~]# mysql -uroot -predhat
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)[root@mysql-node1 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240822 21:35:12 server id 10 end_log_pos 123 CRC32 0xf5eb61b6 Start: binlog v 4, server v 5.7.44-log created 240822 21:35:12 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
kD7HZg8KAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACQPsdmEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbZh6/U=
'/*!*/;
# at 123
#240822 21:35:12 server id 10 end_log_pos 154 CRC32 0xcf149075 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node2 ~]# /etc/init.d/mysqld restart[root@mysql-node2 ~]# mysql -uroot -predhat
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='test', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.10Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 154Relay_Log_File: mysql-node2-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: Yes
3.3.启用半同步模式
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 [root@mysql-node1 ~]# mysql -uroot -predhat
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安装半同步插件
Query OK, 0 rows affected (0.00 sec)mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS-> FROM INFORMATION_SCHEMA.PLUGINS-> WHERE PLUGIN_NAME LIKE '%semi%'; #查看插件情况
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; #打开半同步功能
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%'; #查看半同步功能状态
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 [root@mysql-node2 ~]# mysql -uroot -predhat
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.01 sec)mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
3.4.测试 在master端写入数据
[root@mysql-node2 ~]# mysql -uroot -predhat
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| 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 | 710 |
| Rpl_semi_sync_master_tx_wait_time | 710 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
模拟故障:
#在slave端
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)#在master端插入数据
mysql> insert into test.userlist value ('test111','123');
Query OK, 1 row affected (10.00 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 710 |
| Rpl_semi_sync_master_tx_wait_time | 710 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
四 mysql高可用之组复制 (MGR)
MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩 展的解决方案
组复制是 MySQL 5.7.17 版本出现的新特性,它提供了高可用、高扩展、高可靠的
MySQL 集群服务 MySQL 组复制分单主模式和多主模式,传统的mysql复制技术仅解决了数据同步的问题,
MGR 对属于同一组的服务器自动进行协调。对于要提交的事务,组成员必须就全局事务序列中给定事务 的顺序达成一致
提交或回滚事务由每个服务器单独完成,但所有服务器都必须做出相同的决定
如果存在网络分区,导致成员无法达成事先定义的分割策略,则在解决此问题之前系统不会继续进行, 这是一种内置的自动裂脑保护机制
MGR由组通信系统( Group Communication System,GCS ) 协议支持
该系统提供故障检测机制、组成员服务以及安全且有序的消息传递
4.1 组复制流程
首先我们将多个节点共同组成一个复制组,在执行读写(RW)事务的时候,需要通过一致性协议层 (Consensus 层)的同意,也就是读写事务想要进行提交,必须要经过组里“大多数人”(对应 Node 节 点)的同意,大多数指的是同意的节点数量需要大于 (N/2+1),这样才可以进行提交,而不是原发起 方一个说了算。而针对只读(RO)事务则不需要经过组内同意,直接提交即可
注意!!!:节点数量不能超过9台
4.2 组复制单主和多主模式
single-primary mode(单写或单主模式)
单写模式 group 内只有一台节点可写可读,其他节点只可以读。当主服务器失败时,会自动选择新的主服务器,
multi-primary mode(多写或多主模式)
组内的所有机器都是 primary 节点,同时可以进行读写操作,并且数据是最终一致的。
4.3.实现mysql组复制
注意!!!!:为了避免出错,在所有节点中从新生成数据库数据
编辑主配置文件:
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.10:33061"
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1[root@mysql-node1 ~]# rm -rf /data/mysql/*
[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# mysqld --user mysql --initialize
2024-08-24T13:30:49.223356Z 1 [Note] A temporary password is generated for root@localhost: N0Xgap&RI,yl[root@mysql-node1 ~]# /etc/init.d/mysqld restartERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/data/mysql/mysql-node1.exam.com.err'.SUCCESS! [root@mysql-node1 ~]# mysql -uroot -p'N0Xgap&RI,yl'mysql> alter user root@localhost identified by 'redhat';
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> 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 MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (1.71 sec)mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
| group_replication_applier | 135c7cb4-621d-11ef-9ec2-000c29dbd129 | mysql-node1.exam.com | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
1 row in set (0.00 sec)[root@mysql-node3 ~]# rm -rf /data/mysql/* #先清空之前的内容,保证虚拟机的实验环境纯洁
[root@mysql-node3 ~]# vim /etc/my.cnf
[root@mysql-node3 ~]# mysqld --user mysql --initialize
2024-08-24T13:18:38.761556Z 1 [Note] A temporary password is generated for root@localhost: r)wITuyo2_oQ #这是密码[root@mysql-node3 ~]# /etc/init.d/mysqld restartERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/data/mysql/mysql-node3.exam.com.err'.SUCCESS! [root@mysql-node3 ~]# mysql -uroot -p'r)wITuyo2_oQ'mysql> alter user root@localhost identified by 'redhat';
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 'redhat';
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> 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 master to master_user='rpl_user',master_password='redhat' for channel'group_replplication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.28 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
| group_replication_applier | 0aa2051c-61e5-11ef-b163-000c29dbd129 | mysql-node1.exam.com | 3306 | ONLINE |
| group_replication_applier | 280effce-61e8-11ef-a61a-000c2998aed7 | mysql-node2.exam.com | 3306 | ONLINE |
| group_replication_applier | 6003c5e5-621b-11ef-beb0-000c2976158f | mysql-node3.exam.com | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+
五 mysql-router(mysql路由)
MySQL Router
是一个对应用程序透明的InnoDB Cluster连接路由服务,提供负载均衡、应用连接故障转移和客户端路由。
利用路由器的连接路由特性,用户可以编写应用程序来连接到路由器,并令路由器使用相应的路由策略 来处理连接,使其连接到正确的MySQL数据库服务器
Mysql route的部署方式
我们需要在所有的数据库主机之外再开一台主机mysql-router
#安装mysql-router[root@mysql-node1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm#配置mysql-router[root@mysql-router ~]# vim /etc/mysqlrouter/mysqlrouter.conf[routing:ro]bind_address = 0.0.0.0bind_port = 7001destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306routing_strategy = round-robin[routing:rw]bind_address = 0.0.0.0bind_port = 7002destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306routing_strategy = first-available[root@mysql-router ~]# systemctl start mysqlrouter.service
测试:
#建立测试用户
mysql> CREATE USER lee@'%' IDENTIFIED BY 'lee';
mysql> GRANT ALL ON lee.* TO lee@'%';#查看调度效果
[root@mysql-node10 & 20 & 30 ~]# watch -1 lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 9879 mysql 22u IPv6 56697
0t0 TCP *:mysql (LISTEN)[root@mysql-router ~]# mysql -ulee -plee -h 172.25.254.40 -P 7001
注意!!!:mysql router 并不能限制数据库的读写,访问分流
六 mysql高可用之MHA
6.1.MHA概述
为什么要用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支持一主多从架构,最少三台服务,即一主两从
故障切换备选主库的算法
1.一般判断从库的是从(position/GTID)判断优劣,数据有差异,最接近于master的slave,成为备选 主。
2.数据一致的情况下,按照配置文件顺序,选择备选主库。
3.设定有权重(candidate_master=1),按照权重强制指定备选主。
(1)默认情况下如果一个slave落后master 100M的relay logs的话,即使有权重,也会失效。
(2)如果check_repl_delay=0的话,即使落后很多日志,也强制选择其为备选主。
MHA工作原理
- 目前MHA主要支持一主多从的架构,要搭建MHA,要求一个复制集群必须最少有3台数据库服务器, 一主二从,即一台充当Master,台充当备用Master,另一台充当从库。
- MHA Node 运行在每台 MySQL 服务器上
- MHAManager 会定时探测集群中的master 节点
- 当master 出现故障时,它可以自动将最新数据的slave 提升为新的master
- 然后将所有其他的slave 重新指向新的master,VIP自动漂移到新的master。
- 整个故障转移过程对应用程序完全透明。
6.2 MHA部署实施
6.2.1 搭建主两从架构
[root@mysql-node1 local]# mysql -uroot -p'P2GlHy2P*-y9'mysql> alter user root@localhost identified by 'redhat';
Query OK, 0 rows affected (0.01 sec)mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'redhat';
Query OK, 0 rows affected (0.01 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.00 sec)mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)[root@mysql-node2 ~]# rm -rf /data/mysql/*
[root@mysql-node2 ~]# mysqld --user mysql --initialize
2024-08-24T14:36:05.168222Z 1 [Note] A temporary password is generated for root@localhost: N/4sQst+yWP?
[root@mysql-node2 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node2.exam.com.err'.SUCCESS! [root@mysql-node2 ~]# mysql -uroot -p'N/4sQst+yWP?'
mysql> alter user root@localhost identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='redhat', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.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.01 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;
6.2.2安装MHA所需要的软件
[root@mysql-node5 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.10:/root
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 17.6MB/s 00:00
[root@mysql-node5 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.20:/root
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 19.3MB/s 00:00
[root@mysql-node5 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.30:/root
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 6.5MB/s 00:00 [root@mysql-node3 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
在软件中包含的工具包介绍
1.Manager工具包主要包括以下几个工具:
- masterha_check_ssh #检查MHA的SSH配置状况
- masterha_check_repl #检查MySQL复制状况
- masterha_manger #启动MHA
- 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线程)
6.2.3 配置MHA 的管理环境
1.生成配置目录和配置文件
[root@mysql-node5 MHA-7]# masterha_manager --help
Usage:masterha_manager --global_conf=/etc/masterha_default.cnf--conf=/usr/local/masterha/conf/app1.cnfSee online reference(http://code.google.com/p/mysql-master-ha/wiki/masterha_manager) fordetails.
因为我们当前只有一套主从,所以我们只需要写一个配置文件即可
rpm包中没有为我们准备配置文件的模板
可以解压源码包后在samples中找到配置文件的模板文件
#生成配置文件
[root@mysql-node5 MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@mysql-node5 MHA-7]# cd mha4mysql-manager-0.58/[root@mysql-node5 MHA-7]# cd mha4mysql-manager-0.58/samples/conf/
[root@mysql-node5 conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf
[root@mysql-node5 conf]# cd /etc/masterha/
[root@mysql-node5 masterha]# vim /etc/masterha/app1.cnf[server default]
user=root
password=redhat
ssh_user=root
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s 172.25.254.20
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/manager.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@mysql-node5 .ssh]# scp id_rsa root@172.25.254.10:/root/.ssh/
id_rsa 100% 1679 1.6MB/s 00:00
[root@mysql-node5 .ssh]# scp id_rsa root@172.25.254.20:/root/.ssh/
id_rsa 100% 1679 2.1MB/s 00:00
[root@mysql-node5 .ssh]# scp id_rsa root@172.25.254.30:/root/.ssh/
id_rsa 100% 1679 1.6MB/s 00:00
2.检测配置:
[root@mysql-node5 .ssh]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
[root@mysql-node5 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
a)检测网络及ssh免密
b)检测数据主从复制情况
6.2.3 MHA的故障切换
MHA的故障切换过程
共包括以下的步骤:
1.配置文件检查阶段,这个阶段会检查整个集群配置文件配置
2.宕机的master处理,这个阶段包括虚拟ip摘除操作,主机关机操作
3.复制dead master和最新slave相差的relay log,并保存到MHA Manger具体的目录下
4.识别含有最新更新的slave
5.应用从master保存的二进制日志事件(binlog events)
6.提升一个slave为新的master进行复制
7.使其他的slave连接新的master进行复制
切换方式:
master未出现故障手动切换
#切换过程如下:
[root@mysql-node5 ~]# 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#检测
[root@mysql-node5 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf #模拟master故障
[root@mysql-node5 ~]# /etc/init.d/mysqld stop#在MHA-master中做故障切换
[root@mysql-node5 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.20 --dead_master_port=3306 --new_master_host=172.25.254.10 --new_master_port=3306 --ignore_last_failover恢复故障mysql节点
[root@mysql-node20 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@mysql-node20 tmp]# mysql -predhat
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='test', MASTER_AUTO_POSITION=1;mysql> show slave\G#测试一主两从是否正常
[root@mysql-node5 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnfFri Aug 2 20:15:29 2024 - [info] Checking replication health on 172.25.254.20..Fri Aug 2 20:15:29 2024 - [info] ok.Fri Aug 2 20:15:29 2024 - [info] Checking replication health on 172.25.254.30..Fri Aug 2 20:15:29 2024 - [info] ok.Fri Aug 2 20:15:29 2024 - [warning] master_ip_failover_script is not defined.Fri Aug 2 20:15:29 2024 - [warning] shutdown_script is not defined.Fri Aug 2 20:15:29 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.
自动切换
#删掉切换锁文件
[root@mysql-node5 ~]# rm -fr app1.failover.complete#监控程序通过指定配置文件监控master状态,当master出问题后自动切换并退出避免重复做故障切换
[root@mysql-node5 ~]# masterha_manager --conf=/etc/masterha/app1.cnf
[root@mysql-node5 ~]# cat /etc/masterha/manager.log恢复故障节点
[root@mysql-node20 ~]# /etc/init.d/mysqld start
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;清除锁文件
[root@mysql-node5 ~]# rm -rf app1.failover.complete manager.log
6.2.3 为MHA添加VIP功能
[root@mysql-node5 ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@mysql-node5 ~]# chmod +x /usr/local/bin/master_ip_*
[root@mysql-node5 ~]# vim /usr/local/bin/master_ip_failover
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";[root@mysql-node5 ~]# vim /usr/local/bin/master_ip_online_change
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;[root@mysql-node5 ~]# masterha_manager --conf=/etc/masterha/app1.cnf
Sun Aug 25 11:44:23 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 25 11:44:23 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sun Aug 25 11:44:23 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..[root@mysql-node1 ~]# ip a a 172.25.254.100/24 dev eth0
模拟故障
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL........... SUCCESS! [root@mysql-node5 ~]# cd /etc/masterha/
[root@mysql-node5 masterha]# cat manager.log
恢复故障主机
[root@mysql-node1 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@mysql-node1 ~]# mysql -uroot -predhatmysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='redhat',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)[root@mysql-node5 masterha]# rm -rf app1.failover.complete manager.log
手动切换后查看vip变化
[root@mysql-node5 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[root@mysql-node1 ~]# ip a