MySQL-MHA高可用(一)(2)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL-MHA高可用(一)

创建用户并指定主从

master1

       第一条grant命令是创建一个用于主从复制的帐号,在master1和master2的主机上创建即可。 第二条grant命令是创建MHA管理账号,所有mysql服务器上都需要执行。MHA会在配置文件里要求能远程登录到数据库,所以要进行必要的赋权。

1. mysql> grant replication slave on *.* to mharep@'192.168.8.%' identified by '123';
2. Query OK, 0 rows affected, 1 warning (1.00 sec)
3. 
4. mysql> grant all privileges on *.* to manager@'192.168.8.%' identified by '123';
5. Query OK, 0 rows affected, 1 warning (0.00 sec)
6. 
7. mysql> show master status;
8. +------------------+----------+--------------+------------------+-------------------+
9. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
10. +------------------+----------+--------------+------------------+-------------------+
11. | mysql-bin.000005 |      742 |              |                  |                   |
12. +------------------+----------+--------------+------------------+-------------------+
13. 1 row in set (0.00 sec)

master2

1. mysql> grant replication slave on *.* to mharep@'192.168.8.%' identified by '123';
2. Query OK, 0 rows affected, 1 warning (10.01 sec)
3. 
4. mysql> grant all privileges on *.* to manager@'192.168.8.%' identified by '123';
5. Query OK, 0 rows affected, 1 warning (0.00 sec)
6. 
7. mysql> change master to
8.     -> master_host='192.168.8.30',
9.     -> master_port=3306,
10.     -> master_user='mharep',
11.     -> master_password='123',
12.     -> master_log_file='mysql-bin.000005',
13.     -> master_log_pos=742;
14. Query OK, 0 rows affected, 2 warnings (0.01 sec)
15. 
16. mysql> start slave;
17. Query OK, 0 rows affected (0.00 sec)

       查看从(master2)的状态(切记不要搞混,master2也是从),以下两个值必须为yes,代表从服务器能正常连接主服务器 Slave_IO_Running:Yes,Slave_SQL_Running:Yes

slave

1. mysql> grant all privileges on *.* to manager@'192.168.8.%' identified by '123';
2. Query OK, 0 rows affected, 1 warning (0.04 sec)
3. 
4. mysql> change master to
5.     -> master_host='192.168.8.30',
6.     -> master_port=3306,
7.     -> master_user='mharep',
8.     -> master_password='123',
9.     -> master_log_file='mysql-bin.000005',
10.     -> master_log_pos=742;
11. Query OK, 0 rows affected, 2 warnings (0.01 sec)
12. 
13. mysql> start slave;
14. Query OK, 0 rows affected (0.01 sec)

       查看从的状态,以下两个值必须为yes,代表从服务器能正常连接主服务器 Slave_IO_Running:Yes Slave_SQL_Running:Yes

查看master1半同步状态

1. mysql> show status like '%rpl_semi_sync%';
2. +--------------------------------------------+-------+
3. | Variable_name                              | Value |
4. +--------------------------------------------+-------+
5. | Rpl_semi_sync_master_clients               | 2     |
6. | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
7. | Rpl_semi_sync_master_net_wait_time         | 0     |
8. | Rpl_semi_sync_master_net_waits             | 0     |
9. | Rpl_semi_sync_master_no_times              | 1     |
10. | Rpl_semi_sync_master_no_tx                 | 2     |
11. | Rpl_semi_sync_master_status                | ON    |
12. | Rpl_semi_sync_master_timefunc_failures     | 0     |
13. | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
14. | Rpl_semi_sync_master_tx_wait_time          | 0     |
15. | Rpl_semi_sync_master_tx_waits              | 0     |
16. | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
17. | Rpl_semi_sync_master_wait_sessions         | 0     |
18. | Rpl_semi_sync_master_yes_tx                | 0     |
19. | Rpl_semi_sync_slave_status                 | OFF   |
20. +--------------------------------------------+-------+
21. 15 rows in set (0.01 sec)

配置mysql-mha

       manager需要安装node包和manager包,data节点包括原有的MySQL复制结构中的主机,至少3台,即1主2从,当masterfailover后,还能保证主从结构;只需安装node包。manager server:运行监控脚本,负责monitoring 和 auto-failover;

1、 在所有主机上安装mha所依赖的软件包。

[root@manager ~]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles ncftp perl-Params-Validate perl-CPAN perl-Test-Mock-LWP.noarch perl-LWP-Authen-Negotiate.noarch perl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

2、在管理节点(manager)需要两个都安装:mha4mysql-node-0.56.tar.gz和mha4mysql-manager-0.56.tar.gz ,在另外三台数据库节点只要安装MHA的node节点。

软件下载 yoshinorim (Yoshinori Matsunobu) · GitHub

管理节点安装mha4mysql-manager-0.58.tar.gz

1. [root@manager ~]# tar zxf mha4mysql-manager-0.58.tar.gz 
2. [root@manager ~]# cd mha4mysql-manager-0.58/
3. [root@manager mha4mysql-manager-0.58]# perl Makefile.PL 
4. [root@manager mha4mysql-manager-0.58]# make && make install
5. [root@manager mha4mysql-manager-0.58]# mkdir /etc/masterha
6. [root@manager mha4mysql-manager-0.58]# mkdir -p /masterha/app1
7. [root@manager mha4mysql-manager-0.58]# mkdir /scripts
8. [root@manager mha4mysql-manager-0.58]# cp samples/conf/* /etc/masterha/
9. [root@manager mha4mysql-manager-0.58]# cp samples/scripts/* /scripts

注意:在所有数据库(包括管理节点的四台服务器)上安装mha4mysql-node-0.56.tar.gz

1. [root@manager ~]# tar zxf mha4mysql-node-0.58.tar.gz 
2. [root@manager ~]# cd mha4mysql-node-0.58/
3. [root@manager mha4mysql-node-0.58]# perl Makefile.PL
4. [root@manager mha4mysql-node-0.58]# make && make install

配置mha

       与绝大多数Linux应用程序类似,MHA的正确使用依赖于合理的配置文件。MHA的配置文件与mysql的my.cnf文件配置相似,采取的是param=value的方式来配置,配置文件位于管理节点,通常包括每一个mysql server的主机名,mysql用户名,密码,工作目录等等。 编辑/etc/masterha/app1.conf,内容如下:

1. [root@manager ~]# vim /etc/masterha/app1.cnf 
2. [server default]
3. manager_workdir=/masterha/app1
4. manager_log=/masterha/app1/manager.log
5. user=manager
6. password=123
7. ssh_user=root
8. repl_user=mharep
9. repl_password=123
10. ping_interval=1
11. 
12. [server1]
13. hostname=192.168.8.30
14. port=3306
15. master_binlog_dir=/usr/local/mysql/data
16. candidate_master=1
17. 
18. [server2]
19. hostname=192.168.8.40
20. port=3306
21. master_binlog_dir=/usr/local/mysql/data
22. candidate_master=1
23. 
24. [server3]
25. hostname=192.168.8.50
26. port=3306
27. master_binlog_dir=/usr/local/mysql/data
28. no_master=1

配关配置项的解释如下:

manager_workdir=/masterha/app1 #设置manager的工作目录

manager_log=/masterha/app1/manager.log #设置manager的日志

user=manager #设置监控用户manager

password=123 #监控用户manager的密码

ssh_user=root #ssh连接用户

repl_user=mharep #主从复制用户

repl_password=123 #主从复制用户密码

ping_interval=1 #设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行

railover master_binlog_dir=/usr/local/mysql/data #设置master 保存binlog的位置,以便MHA可以找到master的日志,我这里的也就是mysql的数据目录

candidate_master=1 #设置为候选master,如果设置该参数以后,发生主从切换以后将会将此从库提升为主库

验证

SSH 有效性验证

1. [root@manager ~]# masterha_check_ssh --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.cnf
2. Tue Apr  4 13:44:58 2023 - [info] Reading default configuration from /etc/masterha/masterha_default.cnf..
3. Tue Apr  4 13:44:58 2023 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
4. Tue Apr  4 13:44:58 2023 - [info] Reading server configuration from /etc/masterha/app1.cnf..
5. Tue Apr  4 13:44:58 2023 - [info] Starting SSH connection tests..
6. Tue Apr  4 13:45:01 2023 - [debug] 
7. Tue Apr  4 13:44:59 2023 - [debug]  Connecting via SSH from root@192.168.8.50(192.168.8.50:22) to root@192.168.8.30(192.168.8.30:22)..
8. Tue Apr  4 13:45:00 2023 - [debug]   ok.
9. Tue Apr  4 13:45:00 2023 - [debug]  Connecting via SSH from root@192.168.8.50(192.168.8.50:22) to root@192.168.8.40(192.168.8.40:22)..
10. Tue Apr  4 13:45:00 2023 - [debug]   ok.
11. Tue Apr  4 13:45:01 2023 - [debug] 
12. Tue Apr  4 13:44:59 2023 - [debug]  Connecting via SSH from root@192.168.8.40(192.168.8.40:22) to root@192.168.8.30(192.168.8.30:22)..
13. Tue Apr  4 13:44:59 2023 - [debug]   ok.
14. Tue Apr  4 13:44:59 2023 - [debug]  Connecting via SSH from root@192.168.8.40(192.168.8.40:22) to root@192.168.8.50(192.168.8.50:22)..
15. Tue Apr  4 13:45:00 2023 - [debug]   ok.
16. Tue Apr  4 13:45:01 2023 - [debug] 
17. Tue Apr  4 13:44:58 2023 - [debug]  Connecting via SSH from root@192.168.8.30(192.168.8.30:22) to root@192.168.8.40(192.168.8.40:22)..
18. Tue Apr  4 13:44:59 2023 - [debug]   ok.
19. Tue Apr  4 13:44:59 2023 - [debug]  Connecting via SSH from root@192.168.8.30(192.168.8.30:22) to root@192.168.8.50(192.168.8.50:22)..
20. Tue Apr  4 13:45:00 2023 - [debug]   ok.
21. Tue Apr  4 13:45:01 2023 - [info] All SSH connection tests passed successfully.

集群复制的有效性验证

       所有mysql必须都启动,验证成功的话会自动识别出所有服务器和主从状况 注:验证成功的话会自动识别出所有服务器和主从状况 在验证时,若遇到这个错误:Can't exec "mysqlbinlog" ...... 解决方法是在所有服务器上执行:

ln -s /usr/local/mysql/bin/* /usr/local/bin/
1. [root@manager ~]# masterha_check_repl --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.cnf
2. Tue Apr  4 13:48:03 2023 - [info] Reading default configuration from /etc/masterha/masterha_default.cnf..
3. Tue Apr  4 13:48:03 2023 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
4. Tue Apr  4 13:48:03 2023 - [info] Reading server configuration from /etc/masterha/app1.cnf..
5. Tue Apr  4 13:48:03 2023 - [info] MHA::MasterMonitor version 0.58.
6. Tue Apr  4 13:48:04 2023 - [info] GTID failover mode = 0
7. Tue Apr  4 13:48:04 2023 - [info] Dead Servers:
8. Tue Apr  4 13:48:04 2023 - [info] Alive Servers:
9. Tue Apr  4 13:48:04 2023 - [info]   192.168.8.30(192.168.8.30:3306)
10. Tue Apr  4 13:48:04 2023 - [info]   192.168.8.40(192.168.8.40:3306)
11. Tue Apr  4 13:48:04 2023 - [info]   192.168.8.50(192.168.8.50:3306)
12. Tue Apr  4 13:48:04 2023 - [info] Alive Slaves:
13. Tue Apr  4 13:48:04 2023 - [info]   192.168.8.40(192.168.8.40:3306)  Version=5.7.40-log (oldest major version between slaves) log-bin:enabled
14. Tue Apr  4 13:48:04 2023 - [info]     Replicating from 192.168.8.30(192.168.8.30:3306)
15. Tue Apr  4 13:48:04 2023 - [info]     Primary candidate for the new Master (candidate_master is set)
16. Tue Apr  4 13:48:04 2023 - [info]   192.168.8.50(192.168.8.50:3306)  Version=5.7.40-log (oldest major version between slaves) log-bin:enabled
17. Tue Apr  4 13:48:04 2023 - [info]     Replicating from 192.168.8.30(192.168.8.30:3306)
18. Tue Apr  4 13:48:04 2023 - [info]     Not candidate for the new Master (no_master is set)
19. Tue Apr  4 13:48:04 2023 - [info] Current Alive Master: 192.168.8.30(192.168.8.30:3306)
20. Tue Apr  4 13:48:04 2023 - [info] Checking slave configurations..
21. Tue Apr  4 13:48:04 2023 - [info]  read_only=1 is not set on slave 192.168.8.40(192.168.8.40:3306).
22. Tue Apr  4 13:48:04 2023 - [warning]  relay_log_purge=0 is not set on slave 192.168.8.50(192.168.8.50:3306).
23. Tue Apr  4 13:48:04 2023 - [info] Checking replication filtering settings..
24. Tue Apr  4 13:48:04 2023 - [info]  binlog_do_db= , binlog_ignore_db= 
25. Tue Apr  4 13:48:04 2023 - [info]  Replication filtering check ok.
26. Tue Apr  4 13:48:04 2023 - [info] GTID (with auto-pos) is not supported
27. Tue Apr  4 13:48:04 2023 - [info] Starting SSH connection tests..
28. Tue Apr  4 13:48:07 2023 - [info] All SSH connection tests passed successfully.
29. Tue Apr  4 13:48:07 2023 - [info] Checking MHA Node version..
30. Tue Apr  4 13:48:07 2023 - [info]  Version check ok.
31. Tue Apr  4 13:48:07 2023 - [info] Checking SSH publickey authentication settings on the current master..
32. Tue Apr  4 13:48:08 2023 - [info] HealthCheck: SSH to 192.168.8.30 is reachable.
33. Tue Apr  4 13:48:08 2023 - [info] Master MHA Node version is 0.58.
34. Tue Apr  4 13:48:08 2023 - [info] Checking recovery script configurations on 192.168.8.30(192.168.8.30:3306)..
35. Tue Apr  4 13:48:08 2023 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/data/log/masterha/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000005 
36. Tue Apr  4 13:48:08 2023 - [info]   Connecting to root@192.168.8.30(192.168.8.30:22).. 
37.   Creating /data/log/masterha if not exists..    ok.
38.   Checking output directory is accessible or not..
39.    ok.
40.   Binlog found at /usr/local/mysql/data, up to mysql-bin.000005
41. Tue Apr  4 13:48:08 2023 - [info] Binlog setting check done.
42. Tue Apr  4 13:48:08 2023 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
43. Tue Apr  4 13:48:08 2023 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='manager' --slave_host=192.168.8.40 --slave_ip=192.168.8.40 --slave_port=3306 --workdir=/data/log/masterha --target_version=5.7.40-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
44. Tue Apr  4 13:48:08 2023 - [info]   Connecting to root@192.168.8.40(192.168.8.40:22).. 
45. Creating directory /data/log/masterha.. done.
46.   Checking slave recovery environment settings..
47.     Opening /usr/local/mysql/data/relay-log.info ... ok.
48.     Relay log found at /usr/local/mysql/data, up to relay-bin.000002
49.     Temporary relay log file is /usr/local/mysql/data/relay-bin.000002
50.     Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
51.     Testing mysql connection and privileges..
52. mysql: [Warning] Using a password on the command line interface can be insecure.
53. done.
54.     Testing mysqlbinlog output.. done.
55.     Cleaning up test file(s).. done.
56. Tue Apr  4 13:48:09 2023 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='manager' --slave_host=192.168.8.50 --slave_ip=192.168.8.50 --slave_port=3306 --workdir=/data/log/masterha --target_version=5.7.40-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
57. Tue Apr  4 13:48:09 2023 - [info]   Connecting to root@192.168.8.50(192.168.8.50:22).. 
58. Creating directory /data/log/masterha.. done.
59.   Checking slave recovery environment settings..
60.     Opening /usr/local/mysql/data/relay-log.info ... ok.
61.     Relay log found at /usr/local/mysql/data, up to relay-bin.000002
62.     Temporary relay log file is /usr/local/mysql/data/relay-bin.000002
63.     Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
64.     Testing mysql connection and privileges..
65. mysql: [Warning] Using a password on the command line interface can be insecure.
66. done.
67.     Testing mysqlbinlog output.. done.
68.     Cleaning up test file(s).. done.
69. Tue Apr  4 13:48:09 2023 - [info] Slaves settings check done.
70. Tue Apr  4 13:48:09 2023 - [info] 
71. 192.168.8.30(192.168.8.30:3306) (current master)
72.  +--192.168.8.40(192.168.8.40:3306)
73.  +--192.168.8.50(192.168.8.50:3306)
74. 
75. Tue Apr  4 13:48:09 2023 - [info] Checking replication health on 192.168.8.40..
76. Tue Apr  4 13:48:09 2023 - [info]  ok.
77. Tue Apr  4 13:48:09 2023 - [info] Checking replication health on 192.168.8.50..
78. Tue Apr  4 13:48:09 2023 - [info]  ok.
79. Tue Apr  4 13:48:09 2023 - [warning] master_ip_failover_script is not defined.
80. Tue Apr  4 13:48:09 2023 - [warning] shutdown_script is not defined.
81. Tue Apr  4 13:48:09 2023 - [info] Got exit code 0 (Not master dead).
82. 
83. MySQL Replication Health is OK.

启动manager

       注:在应用Unix/Linux时,我们一般想让某个程序在后台运行,于是我们将常会用 & 在程序结尾来让程序自动运行。比如我们要运行mysql在后台: /usr/local/mysql/bin/mysqld_safe –user=mysql &。可是有很多程序并不想mysqld一样,这样我们就需要nohup命令。

1. [root@manager mha4mysql-manager-0.58]# cd /masterha/
2. [root@manager masterha]# nohup masterha_manager --conf/etc/masterha/app1.cnf &> /tmp/mha_manager.log &
3. [2] 99442

检查状态

1. [root@manager masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
2. app1 (pid:99277) is running(0:PING_OK), master:192.168.8.30

故障转移验证

       (自动failover) master dead后,MHA当时已经开启,master2(Slave)会自动failover为Master. 验证的方式是先停掉 master1,因为之前的配置文件中,把master2作为了候选人,那么就到 slave 上查看 master 的 IP 是否变为了master2 的 IP

1、停掉 master 在master1上把 mysql 停掉

[root@master1 ~]# systemctl stop mysqld

2、查看 MHA 日志 上面的配置文件中指定了日志位置为/masterha/app1/manager.log

1. [root@manager ~]# cat /masterha/app1/manager.log 
2. ----- Failover Report -----
3. 
4. app1: MySQL Master failover 192.168.8.30(192.168.8.30:3306) to 192.168.8.40(192.168.8.40:3306) succeeded
5. 
6. Master 192.168.8.30(192.168.8.30:3306) is down!
7. 
8. Check MHA Manager logs at manager:/masterha/app1/manager.log for details.
9. 
10. Started automated(non-interactive) failover.
11. The latest slave 192.168.8.40(192.168.8.40:3306) has all relay logs for recovery.
12. Selected 192.168.8.40(192.168.8.40:3306) as a new master.
13. 192.168.8.40(192.168.8.40:3306): OK: Applying all logs succeeded.
14. 192.168.8.50(192.168.8.50:3306): This host has the latest relay log events.
15. Generating relay diff files from the latest slave succeeded.
16. 192.168.8.50(192.168.8.50:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.8.40(192.168.8.40:3306)
17. 192.168.8.40(192.168.8.40:3306): Resetting slave info succeeded.
18. Master failover to 192.168.8.40(192.168.8.40:3306) completed successfully.

       从日志信息中可以看到 master failover 已经成功了,并可以看出故障转移的大体流程

3、检查 slave2 的复制 登录 slave(192.168.8.50) 的Mysql,查看 slave 状态

        可以看到 master 的 IP 现在为 192.168.8.40, 已经切换到和192.168.8.40同步了,本来是和192.168.8.30同步的,说明 MHA 已经把master2提升为了新的 master,IO线程和SQL线程也正确运行,MHA搭建成功。

MHA Manager 端日常操作

1、检查是否有下列文件,有则删除。 发生主从切换后,MHAmanager服务会自动停掉,且在manager_workdir(/masterha/app1)目录下面生成文件app1.failover.complete,若要启动MHA,必须先确保无此文件。

       主从转移后,需要删除下面两个文件。

1. ll /masterha/app1/app1.failover.complete
2. ll /masterha/app1/app1.failover.error

2、检查MHA复制检查:(需要把master设置成candicatade的从服务器)

1. mysql> change master to
2.     -> master_host='192.168.8.40',
3.     -> master_port=3306,
4.     -> master_user='mharep',
5.     -> master_password='123',
6.     -> master_log_file='mysql-bin.000007',
7.     -> master_log_pos=154;
8. Query OK, 0 rows affected, 2 warnings (0.03 sec)
9. 
10. mysql> start slave;
11. Query OK, 0 rows affected (0.01 sec)

3、登录manager

1. [root@manager ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
2. #省略部分内容
3. Thu Apr  6 19:58:27 2023 - [info] Checking replication health on 192.168.8.30..
4. Thu Apr  6 19:58:27 2023 - [info]  ok.
5. Thu Apr  6 19:58:27 2023 - [info] Checking replication health on 192.168.8.50..
6. Thu Apr  6 19:58:27 2023 - [info]  ok.
7. Thu Apr  6 19:58:27 2023 - [warning] master_ip_failover_script is not defined.
8. Thu Apr  6 19:58:27 2023 - [warning] shutdown_script is not defined.
9. Thu Apr  6 19:58:27 2023 - [info] Got exit code 0 (Not master dead).
10. 
11. MySQL Replication Health is OK.

4、停止MHA:

1. [root@manager ~]# masterha_stop --conf=/etc/masterha/app1.cnf
2. Stopped app1 successfully.

5、启动MHA:

1. [root@manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &>/tmp/mha_manager.log & 
2. [1] 101196

       当有slave 节点宕掉时,默认是启动不了的,加上 --ignore_fail_on_start 即使有节点宕掉也能启动MHA,如下:

nohup masterha_manager --conf=/etc/masterha/app1.cnf --ignore_fail_on_start &>/tmp/mha_manager.log &

6、检查状态:

1. [root@manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf 
2. app1 (pid:101303) is running(0:PING_OK), master:192.168.8.40

7、检查日志:

[root@manager ~]# tail -f /masterha/app1/manager.log

8、主从切换后续工作

       重构: 重构就是你的主挂了,切换到Candicate master上,Candicate master变成了主,因此重构的一种方案原主库修复成一个新的slave 主库切换后,把原主库修复成新从库,然后重新执行以上5步。

登录master1主机

1. mysql> show slave status\G
2. *************************** 1. row ***************************
3.                Slave_IO_State: Waiting for master to send event
4.                   Master_Host: 192.168.8.40
5.                   Master_User: mharep
6.                   Master_Port: 3306
7.                 Connect_Retry: 60
8.               Master_Log_File: mysql-bin.000007
9.           Read_Master_Log_Pos: 154
10.                Relay_Log_File: relay-bin.000002
11.                 Relay_Log_Pos: 320
12.         Relay_Master_Log_File: mysql-bin.000007
13.              Slave_IO_Running: Yes
14.             Slave_SQL_Running: Yes

启动manager

1. [root@manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /tmp/mha_manager.log &
2. [root@manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf 
3. app1 (pid:101303) is running(0:PING_OK), master:192.168.8.40

       注意:如果正常,会显示"PING_OK",否则会显示"NOT_RUNNING",这代表MHA监控没有开启。定期删除中继日志 在配置主从复制中,slave上设置了参数relay_log_purge=0,所以slave节点需要定期删除中继日志,建议每个slave节点删除中继日志的时间错开。

1. [root@manager ~]# crontab -e
2. 0 5 * * * /usr/local/bin/purge_relay_logs - -user=root --password=123 --port=3306 --disable_relay_log_purge >> /var/log/purge_relay.log 2>&1
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 关系型数据库 MySQL
Mysql高可用|索引|事务 | 调优
Mysql高可用|索引|事务 | 调优
|
2月前
|
SQL 容灾 关系型数据库
rds容灾与高可用
rds容灾与高可用
18 4
|
2月前
|
关系型数据库 MySQL
电子好书发您分享《MySQL MGR 8.0高可用实战》
电子好书发您分享《MySQL MGR 8.0高可用实战》 电子好书发您分享《MySQL MGR 8.0高可用实战》
23 1
|
28天前
|
监控 容灾 关系型数据库
rds容灾与高可用
rds容灾与高可用
27 6
|
2月前
|
SQL 关系型数据库 MySQL
Mysql高可用,索引,事务与调优:提高数据库性能的关键技术
在当今互联网时代,高可用性、稳定性和性能是数据库的三大关键要素。本文将深入探讨Mysql高可用、索引、事务和调优等方面的技术,为读者提供实用的解决方案和经验。
14 0
|
2月前
|
监控 关系型数据库 MySQL
MySQL高可用与性能优化综述
MySQL作为一种常用的关系型数据库管理系统,高可用性、索引优化、事务处理和性能调优一直是开发人员和运维人员关注的重点。本文将从MySQL高可用性解决方案、索引设计与优化、事务处理最佳实践以及性能调优策略等方面进行探讨,为读者提供全面的MySQL技术知识概览。
|
2月前
|
SQL 缓存 关系型数据库
MySQL高可用与性能优化——从索引到事务
MySQL是一款常用的关系型数据库,但在实际应用中,如何保证其高可用性与性能优化依然是一个值得探讨的话题。本文将从索引的创建和优化、事务的处理以及常见的性能瓶颈入手,为读者提供MySQL高可用与性能优化的实践经验。
|
2月前
|
缓存 固态存储 关系型数据库
从索引到事务,MySQL高可用调优全攻略
MySQL是目前最流行的关系型数据库之一,但是在高并发情况下,它的性能和可靠性会受到很大的挑战。本文将介绍MySQL高可用方案中的索引优化、事务管理以及调优技巧,帮助读者提升MySQL应用的性能和可靠性。
14 0
|
3月前
|
存储 关系型数据库 MySQL
史上最全MySQL剖析:优化+存储+查询+索引+复制+可扩展+高可用
在互联网行业,MySQL数据库毫无疑问已经是最常用的数据库,LAMP (Linux +Apache + MySQL + PHP)甚至已经成为专有名词,也是很多中小网站建站的首选技术架构。
|
3月前
|
运维 监控 关系型数据库
百度搜索:蓝易云【mysql的mmm高可用详解。】
总结: MMM是一种基于MySQL主从复制的高可用性解决方案,通过使用双主复制架构和自动故障检测与切换机制,实现了MySQL数据库的高可用性和数据同步。使用MMM可以提供更高的系统可靠性和可用性,并减少系统停机时间。但需要注意的是,MMM并不是万能的解决方案,仍需要根据实际需求和系统规模来评估是否适合使用。
43 1

热门文章

最新文章