Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to add or delete slave nodes in MySQL5.7". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to add or delete slave nodes in MySQL5.7".

Database architecture: one master and two slaves

Master:192.168.8.57

Slave1:192.168.8.58

Slave2:192.168.8.59

Manager:192.168.8.60

MHA Toolkit:

Mha4mysql-manager-0.58.tar.gz

Mha4mysql-node-0.58.tar.gz

First, add nodes

1. Install OS, modify hostname, hosts

two。 Configure SSH no password authentication

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. Install MySQL5.7 and configure replication

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

4. Install mha-node

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

5. Modify mha configuration file app1.cnf

[server4] hostname=192.168.8.61port=3306

6. Mutual trust test

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) to root@192.168.8.58 (192.168.58) 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.57) to root@192.168.8.59 (192.168.59). 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.57 Connecting via SSH from root@192.168.8.57 (192.168.57) to root@192.168.8.61 (192.168.61). 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) to root@192.168.8.57 (192.168.8.57). 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.58) to root@192.168.8.59 (192.168.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.58 ok.Mon Oct 22) to root@192.168.8.61 (192.168.61ok.Mon Oct 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) to root@192.168.8.57 (192.168.8.57). 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) to root@192.168. 8.58 (192.168.8.58 Mon Oct 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.59 debug 22) to root@192.168.8.61 (192.168.61 ok.Mon Oct 22).. Mon Oct 29 20:02:02 2018-[debug] debug 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.61 to root@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 Mon Oct 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.61) to root@192.168.8.59 (192.168.59).. Mon Oct 29 20:02:02 2018-[debug] Ok.Mon Oct 29 20:02:03 2018-[info] All SSH connection tests passed successfully.

7. Replication status check

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.57 info) Mon Oct 29 20:02:17 2018-[info] 192.168.58 (192.168.58 info) Mon Oct 29 20:02:17 2018-[info] 192.168.59 (192.168.593306) Mon Oct 29 20:02: 17 2018-[info] 192.168.8.61 (192.168.61 Alive Slaves:Mon Oct) Mon Oct 29 20:02:17 2018-[info] Alive Slaves:Mon Oct 29 20:02:17 2018-[info] 192.168.58 (192.168.58 Vera 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.57) 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.59 (192.168.59 Mon Oct 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.57 GTID ONMon Oct 3306) Mon Oct 29 20:02:17 2018-[info] 192.168.8.61 (192.168.61 Vera 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.57 Mon Oct 3306) Mon Oct 29 20:02:17 2018-[info] Current Alive Master: 192.168.57 (192.168.57 Current Alive Master) 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.58 (192.168.58) : 3306). Mon Oct 29 20:02:17 2018-[info] read_only=1 is not set on slave 192.168.8.59 (192.168.59 read_only=1 is not set on slave 3306). Mon Oct 29 20:02:17 2018-[info] read_only=1 is not set on slave 192.168.61 (192.168.61 read_only=1 is not set on slave 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.57 is reachable.Mon Oct 29 20:02:17 2018-[info] 192.168.57 (192.168.57) (current master) +-192.168.58 (192.168.58) + -- 192.168.8.59 (192.168.8.59 ok.Mon Oct 3306) +-- 192.168.8.61 (192.168.61) 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. Copy test

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 | +-+ |

The data of the three slave nodes are consistent, and table T12 is created successfully.

2. Delete nodes

1. Turn off slave3 replication

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\ gateway * 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

two。 Close the slave3 node MySQL process

Mysqladmin-uroot-pmysql shutdown

3. Modify the mha configuration file to delete the following configuration

[server4] hostname=192.168.8.61port=3306

4. Restart the manager monitoring process

5. Replication environment check

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.57 info) Mon Oct 29 20:17:38 2018-[info] 192.168.58 (192.168.58 info) Mon Oct 29 20:17:38 2018-[info] 192.168.59 (192.168.593306) Mon Oct 29 20:17: 38 2018-[info] Alive Slaves:Mon Oct 29 20:17:38 2018-[info] 192.168.8.58 (192.168.58 oldest major version between slaves) 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.57 (192.168.573Suzhou 306) 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.59 info) 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.57 .168.8.57: 3306) Mon Oct 29 20:17:38 2018-[info] Current Alive Master: 192.168.8.57 (192.168.57 Current Alive Master) 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.58 (192.168.583info 306). Mon Oct 29 20:17:38 2018-[info] Read_only=1 is not set on slave 192.168.8.59 (192.168.59 Checking replication filtering settings..Mon Oct). 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.57 is reachable.Mon Oct 29 20:17:38 2018-[info] 192.168.57 (192.168.57) (current master) +-192.168.58 (192.168.58) + -- 192.168.8.59 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. Thank you for your reading, the above is the content of "how to add or remove slave nodes in MySQL5.7". After the study of this article, I believe you have a deeper understanding of the problem of how to add or delete MHA nodes in MySQL5.7, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.