MySQL5.7中MHA怎么添加或删除slave节点

这篇文章主要讲解了"MySQL5.7中MHA怎么添加或删除slave节点"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"MySQL5.7中MHA怎么添加或删除slave节点"吧！

数据库架构：一主两从

master：192.168.8.57

slave1：192.168.8.58

slave2：192.168.8.59

manager：192.168.8.60

MHA工具包：

mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz

一、添加节点

1.安装OS，修改主机名，hosts

2.配置SSH无密码验证

master

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61

slave1

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61

slave2

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61

manager

ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61

slave3

ssh-keygen -t rsassh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.57ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.58ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.59ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.60

3.安装MySQL5.7，配置复制

change master tomaster_host='192.168.8.57',master_port=3306,master_user='repl',master_password='mysql',master_auto_position=1;

4.安装mha-node

tar xf mha4mysql-node-0.58.tar.gzcd mha4mysql-node-0.58perl Makefile.PLmake && make install

5.修改mha配置文件app1.cnf

[server4]hostname=192.168.8.61port=3306

6.互信测试

Mon Oct 29 20:01:59 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Mon Oct 29 20:01:59 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..Mon Oct 29 20:01:59 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..Mon Oct 29 20:01:59 2018 - [info] Starting SSH connection tests..Mon Oct 29 20:02:01 2018 - [debug] Mon Oct 29 20:01:59 2018 - [debug]  Connecting via SSH from root@192.168.8.57(192.168.8.57:22) to root@192.168.8.58(192.168.8.58:22)..Mon Oct 29 20:02:00 2018 - [debug]   ok.Mon Oct 29 20:02:00 2018 - [debug]  Connecting via SSH from root@192.168.8.57(192.168.8.57:22) to root@192.168.8.59(192.168.8.59:22)..Mon Oct 29 20:02:00 2018 - [debug]   ok.Mon Oct 29 20:02:00 2018 - [debug]  Connecting via SSH from root@192.168.8.57(192.168.8.57:22) to root@192.168.8.61(192.168.8.61:22)..Mon Oct 29 20:02:01 2018 - [debug]   ok.Mon Oct 29 20:02:02 2018 - [debug] Mon Oct 29 20:02:00 2018 - [debug]  Connecting via SSH from root@192.168.8.58(192.168.8.58:22) to root@192.168.8.57(192.168.8.57:22)..Mon Oct 29 20:02:00 2018 - [debug]   ok.Mon Oct 29 20:02:00 2018 - [debug]  Connecting via SSH from root@192.168.8.58(192.168.8.58:22) to root@192.168.8.59(192.168.8.59:22)..Mon Oct 29 20:02:01 2018 - [debug]   ok.Mon Oct 29 20:02:01 2018 - [debug]  Connecting via SSH from root@192.168.8.58(192.168.8.58:22) to root@192.168.8.61(192.168.8.61:22)..Mon Oct 29 20:02:01 2018 - [debug]   ok.Mon Oct 29 20:02:02 2018 - [debug] Mon Oct 29 20:02:00 2018 - [debug]  Connecting via SSH from root@192.168.8.59(192.168.8.59:22) to root@192.168.8.57(192.168.8.57:22)..Mon Oct 29 20:02:01 2018 - [debug]   ok.Mon Oct 29 20:02:01 2018 - [debug]  Connecting via SSH from root@192.168.8.59(192.168.8.59:22) to root@192.168.8.58(192.168.8.58:22)..Mon Oct 29 20:02:01 2018 - [debug]   ok.Mon Oct 29 20:02:01 2018 - [debug]  Connecting via SSH from root@192.168.8.59(192.168.8.59:22) to root@192.168.8.61(192.168.8.61:22)..Mon Oct 29 20:02:02 2018 - [debug]   ok.Mon Oct 29 20:02:03 2018 - [debug] Mon Oct 29 20:02:01 2018 - [debug]  Connecting via SSH from root@192.168.8.61(192.168.8.61:22) to root@192.168.8.57(192.168.8.57:22)..Mon Oct 29 20:02:01 2018 - [debug]   ok.Mon Oct 29 20:02:01 2018 - [debug]  Connecting via SSH from root@192.168.8.61(192.168.8.61:22) to root@192.168.8.58(192.168.8.58:22)..Mon Oct 29 20:02:02 2018 - [debug]   ok.Mon Oct 29 20:02:02 2018 - [debug]  Connecting via SSH from root@192.168.8.61(192.168.8.61:22) to root@192.168.8.59(192.168.8.59:22)..Mon Oct 29 20:02:02 2018 - [debug]   ok.Mon Oct 29 20:02:03 2018 - [info] All SSH connection tests passed successfully.

7.复制状况检查

Mon Oct 29 20:02:15 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Mon Oct 29 20:02:15 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..Mon Oct 29 20:02:15 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..Mon Oct 29 20:02:15 2018 - [info] MHA::MasterMonitor version 0.58.Mon Oct 29 20:02:17 2018 - [info] GTID failover mode = 1Mon Oct 29 20:02:17 2018 - [info] Dead Servers:Mon Oct 29 20:02:17 2018 - [info] Alive Servers:Mon Oct 29 20:02:17 2018 - [info]   192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:02:17 2018 - [info]   192.168.8.58(192.168.8.58:3306)Mon Oct 29 20:02:17 2018 - [info]   192.168.8.59(192.168.8.59:3306)Mon Oct 29 20:02:17 2018 - [info]   192.168.8.61(192.168.8.61:3306)Mon Oct 29 20:02:17 2018 - [info] Alive Slaves:Mon Oct 29 20:02:17 2018 - [info]   192.168.8.58(192.168.8.58:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabledMon Oct 29 20:02:17 2018 - [info]     GTID ONMon Oct 29 20:02:17 2018 - [info]     Replicating from 192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:02:17 2018 - [info]     Primary candidate for the new Master (candidate_master is set)Mon Oct 29 20:02:17 2018 - [info]   192.168.8.59(192.168.8.59:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabledMon Oct 29 20:02:17 2018 - [info]     GTID ONMon Oct 29 20:02:17 2018 - [info]     Replicating from 192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:02:17 2018 - [info]   192.168.8.61(192.168.8.61:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabledMon Oct 29 20:02:17 2018 - [info]     GTID ONMon Oct 29 20:02:17 2018 - [info]     Replicating from 192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:02:17 2018 - [info] Current Alive Master: 192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:02:17 2018 - [info] Checking slave configurations..Mon Oct 29 20:02:17 2018 - [info]  read_only=1 is not set on slave 192.168.8.58(192.168.8.58:3306).Mon Oct 29 20:02:17 2018 - [info]  read_only=1 is not set on slave 192.168.8.59(192.168.8.59:3306).Mon Oct 29 20:02:17 2018 - [info]  read_only=1 is not set on slave 192.168.8.61(192.168.8.61:3306).Mon Oct 29 20:02:17 2018 - [info] Checking replication filtering settings..Mon Oct 29 20:02:17 2018 - [info]  binlog_do_db= , binlog_ignore_db= Mon Oct 29 20:02:17 2018 - [info]  Replication filtering check ok.Mon Oct 29 20:02:17 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.Mon Oct 29 20:02:17 2018 - [info] Checking SSH publickey authentication settings on the current master..Mon Oct 29 20:02:17 2018 - [info] HealthCheck: SSH to 192.168.8.57 is reachable.Mon Oct 29 20:02:17 2018 - [info] 192.168.8.57(192.168.8.57:3306) (current master) +--192.168.8.58(192.168.8.58:3306) +--192.168.8.59(192.168.8.59:3306) +--192.168.8.61(192.168.8.61:3306)Mon Oct 29 20:02:17 2018 - [info] Checking replication health on 192.168.8.58..Mon Oct 29 20:02:17 2018 - [info]  ok.Mon Oct 29 20:02:17 2018 - [info] Checking replication health on 192.168.8.59..Mon Oct 29 20:02:17 2018 - [info]  ok.Mon Oct 29 20:02:17 2018 - [info] Checking replication health on 192.168.8.61..Mon Oct 29 20:02:17 2018 - [info]  ok.Mon Oct 29 20:02:17 2018 - [info] Checking master_ip_failover_script status:Mon Oct 29 20:02:17 2018 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.8.57 --orig_master_ip=192.168.8.57 --orig_master_port=3306 Mon Oct 29 20:02:17 2018 - [info]  OK.Mon Oct 29 20:02:17 2018 - [warning] shutdown_script is not defined.Mon Oct 29 20:02:17 2018 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.

8.复制测试

master

mysql> create table t12 (id int(6));Query OK, 0 rows affected (0.09 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1             || t10            || t11            || t12            || t2             || t3             || t4             || t5             || t6             || t7             || t8             || t9             |+----------------+

slave1，slave2，slave3

mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1             || t10            || t11            || t12            || t2             || t3             || t4             || t5             || t6             || t7             || t8             || t9             |+----------------+

三个slave节点数据一致，表t12创建成功。

二、删除节点

1.关闭slave3复制

mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> reset slave;Query OK, 0 rows affected (0.04 sec)mysql> show slave status \G*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 192.168.8.57                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File:           Read_Master_Log_Pos: 4               Relay_Log_File: localhost-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File:              Slave_IO_Running: No            Slave_SQL_Running: No              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 0              Relay_Log_Space: 217              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: NULLMaster_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: 57                  Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450d             Master_Info_File: /mysql/data/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State:            Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450b:1-4,a92f70a4-d5ea-11e8-af28-080027c0450d:1-15,a92f70a4-d5ea-11e8-af28-080027c0450f:1-8                Auto_Position: 1         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)

2.关闭slave3节点MySQL进程

mysqladmin -uroot -pmysql shutdown

3.修改mha配置文件，删除以下配置

[server4]hostname=192.168.8.61port=3306

4.重启manager监控进程

5.复制环境检查

Mon Oct 29 20:17:37 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Mon Oct 29 20:17:37 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..Mon Oct 29 20:17:37 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..Mon Oct 29 20:17:37 2018 - [info] MHA::MasterMonitor version 0.58.Mon Oct 29 20:17:38 2018 - [info] GTID failover mode = 1Mon Oct 29 20:17:38 2018 - [info] Dead Servers:Mon Oct 29 20:17:38 2018 - [info] Alive Servers:Mon Oct 29 20:17:38 2018 - [info]   192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:17:38 2018 - [info]   192.168.8.58(192.168.8.58:3306)Mon Oct 29 20:17:38 2018 - [info]   192.168.8.59(192.168.8.59:3306)Mon Oct 29 20:17:38 2018 - [info] Alive Slaves:Mon Oct 29 20:17:38 2018 - [info]   192.168.8.58(192.168.8.58:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabledMon Oct 29 20:17:38 2018 - [info]     GTID ONMon Oct 29 20:17:38 2018 - [info]     Replicating from 192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:17:38 2018 - [info]     Primary candidate for the new Master (candidate_master is set)Mon Oct 29 20:17:38 2018 - [info]   192.168.8.59(192.168.8.59:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabledMon Oct 29 20:17:38 2018 - [info]     GTID ONMon Oct 29 20:17:38 2018 - [info]     Replicating from 192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:17:38 2018 - [info] Current Alive Master: 192.168.8.57(192.168.8.57:3306)Mon Oct 29 20:17:38 2018 - [info] Checking slave configurations..Mon Oct 29 20:17:38 2018 - [info]  read_only=1 is not set on slave 192.168.8.58(192.168.8.58:3306).Mon Oct 29 20:17:38 2018 - [info]  read_only=1 is not set on slave 192.168.8.59(192.168.8.59:3306).Mon Oct 29 20:17:38 2018 - [info] Checking replication filtering settings..Mon Oct 29 20:17:38 2018 - [info]  binlog_do_db= , binlog_ignore_db= Mon Oct 29 20:17:38 2018 - [info]  Replication filtering check ok.Mon Oct 29 20:17:38 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.Mon Oct 29 20:17:38 2018 - [info] Checking SSH publickey authentication settings on the current master..Mon Oct 29 20:17:38 2018 - [info] HealthCheck: SSH to 192.168.8.57 is reachable.Mon Oct 29 20:17:38 2018 - [info] 192.168.8.57(192.168.8.57:3306) (current master) +--192.168.8.58(192.168.8.58:3306) +--192.168.8.59(192.168.8.59:3306)Mon Oct 29 20:17:38 2018 - [info] Checking replication health on 192.168.8.58..Mon Oct 29 20:17:38 2018 - [info]  ok.Mon Oct 29 20:17:38 2018 - [info] Checking replication health on 192.168.8.59..Mon Oct 29 20:17:38 2018 - [info]  ok.Mon Oct 29 20:17:38 2018 - [info] Checking master_ip_failover_script status:Mon Oct 29 20:17:38 2018 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.8.57 --orig_master_ip=192.168.8.57 --orig_master_port=3306 Mon Oct 29 20:17:39 2018 - [info]  OK.Mon Oct 29 20:17:39 2018 - [warning] shutdown_script is not defined.Mon Oct 29 20:17:39 2018 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.

感谢各位的阅读，以上就是"MySQL5.7中MHA怎么添加或删除slave节点"的内容了，经过本文的学习后，相信大家对MySQL5.7中MHA怎么添加或删除slave节点这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！