数据库架构：一主两从
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
| 1 |
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61
|
slave1
| 1 |
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61
|
slave2
| 1 |
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61
|
manager
| 1 |
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.8.61
|
slave3
| 12345 |
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，配置复制
| 123456 |
change master tomaster_host='192.168.8.57',master_port=3306,master_user='repl',master_password='mysql',master_auto_position=1;
|
4.安装mha-node
| 1234 |
tar xf mha4mysql-node-0.58.tar.gzcd mha4mysql-node-0.58perl Makefile.PLmake && make install
|
5.修改mha配置文件app1.cnf
| 123 |
[server4]hostname=192.168.8.61port=3306
|
6.互信测试
| 123456789101112131415161718192021222324252627282930313233 |
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.复制状况检查
| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950 |
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
| 12345678910111213141516171819 |
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
| 1234567891011121314151617 |
mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1 || t10 || t11 || t12 || t2 || t3 || t4 || t5 || t6 || t7 || t8 || t9 |+----------------+
|
三个slave节点数据一致，表t12创建成功。
二、删除节点
1.关闭slave3复制
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566 |
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进程
| 1 |
mysqladmin -uroot -pmysql shutdown
|
3.修改mha配置文件，删除以下配置
| 123 |
[server4]hostname=192.168.8.61port=3306
|
4.重启manager监控进程
5.复制环境检查
| 123456789101112131415161718192021222324252627282930313233343536373839404142 |
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.
|