|
0 1 2 3 4 5 6 7 8 |
[root@DB1 ~]# yum install perl-DBD-MySQL ncftp perl-DBI mysql> grant all privileges on *.* to mha@'1.1.%.%' identified by 'admin'; [root@DB1 ~]# /usr/local/mysql/bin/mysqldump --single-transaction --triggers --routines --all-databases --events --set-gtid-purged=OFF -uroot -p''>> all.sql Enter password: [root@DB1 ~]# rm -f /tmp/all.sql [root@DB1 ~]# scp all.sql root@1.1.1.133:/tmp/ [root@DB1 ~]# scp all.sql root@1.1.1.132:/tmp/ [root@DB3 ~]# /usr/local/mysql/bin/mysql -uroot -p </tmp/all.sql [root@DB3 ~]# yum install perl-Config-Tiny perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch-Perl perl-DBD-MySQL ncftp |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
mysql> change master to master_host='1.1.1.131', -> MASTER_PORT=3306, -> master_user='repl', -> master_password='admin', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 1.1.1.131 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 154 Relay_Log_Space: 568 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: 0 Master_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: 131 Master_UUID: a2396dd7-0c06-11e7-b5c1-000c290f01e1 Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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: 264053d1-0b78-11e7-8d3b-000c2913ac14:1-262 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) |
|
0 1 2 3 4 5 6 7 |
[root@DB1 ~]# ssh-keygen [root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.132 [root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.133 [root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.131 [root@DB1 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm [root@DB2 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm [root@DB3 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm [root@DB3 ~]# rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
[root@DB3 ~]# masterha_check_ssh --conf=/etc/mha/mysql_mha.conf Sun Mar 19 02:18:59 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Mar 19 02:18:59 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 02:18:59 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 02:18:59 2017 - [info] Starting SSH connection tests.. Sun Mar 19 02:19:02 2017 - [debug] Sun Mar 19 02:18:59 2017 - [debug] Connecting via SSH from root@1.1.1.131(1.1.1.131:22) to root@1.1.1.132(1.1.1.132:22).. Sun Mar 19 02:19:00 2017 - [debug] ok. Sun Mar 19 02:19:00 2017 - [debug] Connecting via SSH from root@1.1.1.131(1.1.1.131:22) to root@1.1.1.133(1.1.1.133:22).. Sun Mar 19 02:19:01 2017 - [debug] ok. Sun Mar 19 02:19:02 2017 - [debug] Sun Mar 19 02:18:59 2017 - [debug] Connecting via SSH from root@1.1.1.132(1.1.1.132:22) to root@1.1.1.131(1.1.1.131:22).. Sun Mar 19 02:19:01 2017 - [debug] ok. Sun Mar 19 02:19:01 2017 - [debug] Connecting via SSH from root@1.1.1.132(1.1.1.132:22) to root@1.1.1.133(1.1.1.133:22).. Sun Mar 19 02:19:02 2017 - [debug] ok. Sun Mar 19 02:19:03 2017 - [debug] Sun Mar 19 02:19:00 2017 - [debug] Connecting via SSH from root@1.1.1.133(1.1.1.133:22) to root@1.1.1.131(1.1.1.131:22).. Sun Mar 19 02:19:01 2017 - [debug] ok. Sun Mar 19 02:19:01 2017 - [debug] Connecting via SSH from root@1.1.1.133(1.1.1.133:22) to root@1.1.1.132(1.1.1.132:22).. Sun Mar 19 02:19:03 2017 - [debug] ok. Sun Mar 19 02:19:03 2017 - [info] All SSH connection tests passed successfully. [root@DB3 ~]# chmod +x /usr/bin/master_ip_failover [root@DB3 ~]# /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100=== Checking the Status of the script.. OK [root@DB3 ~]# masterha_check_repl --conf=/etc/mha/mysql_mha.conf Sun Mar 19 02:46:37 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Mar 19 02:46:37 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 02:46:37 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 02:46:37 2017 - [info] MHA::MasterMonitor version 0.57. Sun Mar 19 02:46:37 2017 - [info] GTID failover mode = 1 Sun Mar 19 02:46:37 2017 - [info] Dead Servers: Sun Mar 19 02:46:37 2017 - [info] Alive Servers: Sun Mar 19 02:46:37 2017 - [info] 1.1.1.131(1.1.1.131:3306) Sun Mar 19 02:46:37 2017 - [info] 1.1.1.132(1.1.1.132:3306) Sun Mar 19 02:46:37 2017 - [info] 1.1.1.133(1.1.1.133:3306) Sun Mar 19 02:46:37 2017 - [info] Alive Slaves: Sun Mar 19 02:46:37 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 02:46:37 2017 - [info] GTID ON Sun Mar 19 02:46:37 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 02:46:37 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 02:46:37 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 02:46:37 2017 - [info] GTID ON Sun Mar 19 02:46:37 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 02:46:37 2017 - [info] Not candidate for the new Master (no_master is set) Sun Mar 19 02:46:37 2017 - [info] Current Alive Master: 1.1.1.131(1.1.1.131:3306) Sun Mar 19 02:46:37 2017 - [info] Checking slave configurations.. Sun Mar 19 02:46:37 2017 - [info] Checking replication filtering settings.. Sun Mar 19 02:46:37 2017 - [info] binlog_do_db= , binlog_ignore_db= Sun Mar 19 02:46:37 2017 - [info] Replication filtering check ok. Sun Mar 19 02:46:37 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Sun Mar 19 02:46:37 2017 - [info] Checking SSH publickey authentication settings on the current master.. Sun Mar 19 02:46:37 2017 - [info] HealthCheck: SSH to 1.1.1.131 is reachable. Sun Mar 19 02:46:37 2017 - [info] 1.1.1.131(1.1.1.131:3306) (current master) +--1.1.1.132(1.1.1.132:3306) +--1.1.1.133(1.1.1.133:3306) Sun Mar 19 02:46:37 2017 - [info] Checking replication health on 1.1.1.132.. Sun Mar 19 02:46:37 2017 - [info] ok. Sun Mar 19 02:46:37 2017 - [info] Checking replication health on 1.1.1.133.. Sun Mar 19 02:46:37 2017 - [info] ok. Sun Mar 19 02:46:37 2017 - [info] Checking master_ip_failover_script status: Sun Mar 19 02:46:37 2017 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100=== Checking the Status of the script.. OK Sun Mar 19 02:46:37 2017 - [info] OK. Sun Mar 19 02:46:37 2017 - [warning] shutdown_script is not defined. Sun Mar 19 02:46:37 2017 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. [root@DB3 ~]# masterha_manager --conf=/etc/mha/mysql_mha.conf & [root@DB1 ~]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:0f:01:e1 brd ff:ff:ff:ff:ff:ff inet 1.1.1.131/24 brd 1.1.1.255 scope global eth0 inet 1.1.1.100/24 brd 1.1.1.255 scope global secondary eth0:1 inet6 fe80::20c:29ff:fe0f:1e1/64 scope link valid_lft forever preferred_lft forever |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 1.1.1.131 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 638 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes 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: 1396 Last_Error: Error 'Operation DROP USER failed for 'mha'@'1.1.%.%'' on query. Default database: ''. Query: 'drop user mha@'1.1.%.%'' Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 1513 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1396 Last_SQL_Error: Error 'Operation DROP USER failed for 'mha'@'1.1.%.%'' on query. Default database: ''. Query: 'drop user mha@'1.1.%.%'' Replicate_Ignore_Server_Ids: Master_Server_Id: 131 Master_UUID: a2396dd7-0c06-11e7-b5c1-000c290f01e1 Master_Info_File: mysql.slave_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: 170319 03:47:25 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:2-3 Executed_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:1, d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000003 Position: 234 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:1, d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262 1 row in set (0.00 sec) mysql> SET SESSION GTID_NEXT='d4e4c35f-0b70-11e7-be1d-000c299824ca:1-263'; ERROR 1774 (HY000): Malformed GTID specification 'd4e4c35f-0b70-11e7-be1d-000c299824ca:1-263'. mysql> SET SESSION GTID_NEXT='d4e4c35f-0b70-11e7-be1d-000c299824ca:263'; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> set gtid_next='AUTOMATIC'; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
[root@DB3 ~]# masterha_stop --conf=/etc/mha/mysql_mha.conf Stopped mysql_mha successfully. [root@DB3 ~]# masterha_stop --conf=/etc/mha/mysql_mha.conf Stopped mysql_mha successfully. [root@DB3 ~]# masterha_master_switch --conf=/etc/mha/mysql_mha.conf --master_state=alive --new_master_host=1.1.1.132 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000 Sun Mar 19 06:03:28 2017 - [info] MHA::MasterRotate version 0.57. Sun Mar 19 06:03:28 2017 - [info] Starting online master switch.. Sun Mar 19 06:03:28 2017 - [info] Sun Mar 19 06:03:28 2017 - [info] * Phase 1: Configuration Check Phase.. Sun Mar 19 06:03:28 2017 - [info] Sun Mar 19 06:03:28 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Mar 19 06:03:28 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 06:03:28 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 06:03:28 2017 - [info] GTID failover mode = 1 Sun Mar 19 06:03:28 2017 - [info] Current Alive Master: 1.1.1.131(1.1.1.131:3306) Sun Mar 19 06:03:28 2017 - [info] Alive Slaves: Sun Mar 19 06:03:28 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 06:03:28 2017 - [info] GTID ON Sun Mar 19 06:03:28 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 06:03:28 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 06:03:28 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 06:03:28 2017 - [info] GTID ON Sun Mar 19 06:03:28 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 06:03:28 2017 - [info] Not candidate for the new Master (no_master is set) It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 1.1.1.131(1.1.1.131:3306)? (YES/no): YES Sun Mar 19 06:03:31 2017 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Sun Mar 19 06:03:31 2017 - [info] ok. Sun Mar 19 06:03:31 2017 - [info] Checking MHA is not monitoring or doing failover.. Sun Mar 19 06:03:31 2017 - [info] Checking replication health on 1.1.1.132.. Sun Mar 19 06:03:31 2017 - [info] ok. Sun Mar 19 06:03:31 2017 - [info] Checking replication health on 1.1.1.133.. Sun Mar 19 06:03:31 2017 - [info] ok. Sun Mar 19 06:03:31 2017 - [info] 1.1.1.132 can be new master. Sun Mar 19 06:03:31 2017 - [info] From: 1.1.1.131(1.1.1.131:3306) (current master) +--1.1.1.132(1.1.1.132:3306) +--1.1.1.133(1.1.1.133:3306) To: 1.1.1.132(1.1.1.132:3306) (new master) +--1.1.1.133(1.1.1.133:3306) +--1.1.1.131(1.1.1.131:3306) Starting master switch from 1.1.1.131(1.1.1.131:3306) to 1.1.1.132(1.1.1.132:3306)? (yes/NO): yes Sun Mar 19 06:03:38 2017 - [info] Checking whether 1.1.1.132(1.1.1.132:3306) is ok for the new master.. Sun Mar 19 06:03:38 2017 - [info] ok. Sun Mar 19 06:03:38 2017 - [info] 1.1.1.131(1.1.1.131:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. Sun Mar 19 06:03:38 2017 - [info] 1.1.1.131(1.1.1.131:3306): Resetting slave pointing to the dummy host. Sun Mar 19 06:03:38 2017 - [info] ** Phase 1: Configuration Check Phase completed. Sun Mar 19 06:03:38 2017 - [info] Sun Mar 19 06:03:38 2017 - [info] * Phase 2: Rejecting updates Phase.. Sun Mar 19 06:03:38 2017 - [info] Sun Mar 19 06:03:38 2017 - [info] Executing master ip online change script to disable write on the current master: Sun Mar 19 06:03:38 2017 - [info] /usr/bin/master_ip_online_change --command=stop --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=1.1.1.132 --new_master_ip=1.1.1.132 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx *************************************************************** Disabling the VIP - 1.1.1.100/24 on old master: 1.1.1.131 *************************************************************** SIOCSIFFLAGS: Cannot assign requested address Sun Mar 19 06:03:38 2017 - [info] ok. Sun Mar 19 06:03:38 2017 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Sun Mar 19 06:03:38 2017 - [info] Executing FLUSH TABLES WITH READ LOCK.. Sun Mar 19 06:03:38 2017 - [info] ok. Sun Mar 19 06:03:38 2017 - [info] Orig master binlog:pos is mysql-bin.000013:194. Sun Mar 19 06:03:38 2017 - [info] Waiting to execute all relay logs on 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 06:03:38 2017 - [info] master_pos_wait(mysql-bin.000013:194) completed on 1.1.1.132(1.1.1.132:3306). Executed 0 events. Sun Mar 19 06:03:38 2017 - [info] done. Sun Mar 19 06:03:38 2017 - [info] Getting new master's binlog name and position.. Sun Mar 19 06:03:38 2017 - [info] mysql-bin.000011:234 Sun Mar 19 06:03:38 2017 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='1.1.1.132', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'; Sun Mar 19 06:03:38 2017 - [info] Executing master ip online change script to allow write on the new master: Sun Mar 19 06:03:38 2017 - [info] /usr/bin/master_ip_online_change --command=start --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=1.1.1.132 --new_master_ip=1.1.1.132 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx *************************************************************** Enabling the VIP - 1.1.1.100/24 on new master: 1.1.1.132 *************************************************************** Sun Mar 19 06:03:39 2017 - [info] ok. Sun Mar 19 06:03:39 2017 - [info] Sun Mar 19 06:03:39 2017 - [info] * Switching slaves in parallel.. Sun Mar 19 06:03:39 2017 - [info] Sun Mar 19 06:03:39 2017 - [info] -- Slave switch on host 1.1.1.133(1.1.1.133:3306) started, pid: 2983 Sun Mar 19 06:03:39 2017 - [info] Sun Mar 19 06:03:40 2017 - [info] Log messages from 1.1.1.133 ... Sun Mar 19 06:03:40 2017 - [info] Sun Mar 19 06:03:39 2017 - [info] Waiting to execute all relay logs on 1.1.1.133(1.1.1.133:3306).. Sun Mar 19 06:03:39 2017 - [info] master_pos_wait(mysql-bin.000013:194) completed on 1.1.1.133(1.1.1.133:3306). Executed 0 events. Sun Mar 19 06:03:39 2017 - [info] done. Sun Mar 19 06:03:39 2017 - [info] Resetting slave 1.1.1.133(1.1.1.133:3306) and starting replication from the new master 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 06:03:39 2017 - [info] Executed CHANGE MASTER. Sun Mar 19 06:03:40 2017 - [info] Slave started. Sun Mar 19 06:03:40 2017 - [info] End of log messages from 1.1.1.133 ... Sun Mar 19 06:03:40 2017 - [info] Sun Mar 19 06:03:40 2017 - [info] -- Slave switch on host 1.1.1.133(1.1.1.133:3306) succeeded. Sun Mar 19 06:03:40 2017 - [info] Unlocking all tables on the orig master: Sun Mar 19 06:03:40 2017 - [info] Executing UNLOCK TABLES.. Sun Mar 19 06:03:40 2017 - [info] ok. Sun Mar 19 06:03:40 2017 - [info] Starting orig master as a new slave.. Sun Mar 19 06:03:40 2017 - [info] Resetting slave 1.1.1.131(1.1.1.131:3306) and starting replication from the new master 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 06:03:40 2017 - [info] Executed CHANGE MASTER. Sun Mar 19 06:03:41 2017 - [info] Slave started. Sun Mar 19 06:03:41 2017 - [info] All new slave servers switched successfully. Sun Mar 19 06:03:41 2017 - [info] Sun Mar 19 06:03:41 2017 - [info] * Phase 5: New master cleanup phase.. Sun Mar 19 06:03:41 2017 - [info] Sun Mar 19 06:03:41 2017 - [info] 1.1.1.132: Resetting slave info succeeded. Sun Mar 19 06:03:41 2017 - [info] Switching master to 1.1.1.132(1.1.1.132:3306) completed successfully. [root@DB3 ~]# |
启动日志：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
[root@DB3 ~]# masterha_check_status --conf=/etc/mha/mysql_mha.conf mysql_mha (pid:2261) is running(0:PING_OK), master:1.1.1.131 Sun Mar 19 07:17:11 2017 - [info] MHA::MasterMonitor version 0.57. Sun Mar 19 07:17:11 2017 - [info] GTID failover mode = 1 Sun Mar 19 07:17:11 2017 - [info] Dead Servers: Sun Mar 19 07:17:11 2017 - [info] Alive Servers: Sun Mar 19 07:17:11 2017 - [info] 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:17:11 2017 - [info] 1.1.1.132(1.1.1.132:3306) Sun Mar 19 07:17:11 2017 - [info] 1.1.1.133(1.1.1.133:3306) Sun Mar 19 07:17:11 2017 - [info] Alive Slaves: Sun Mar 19 07:17:11 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:17:11 2017 - [info] GTID ON Sun Mar 19 07:17:11 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:17:11 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 07:17:11 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:17:11 2017 - [info] GTID ON Sun Mar 19 07:17:11 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:17:11 2017 - [info] Not candidate for the new Master (no_master is set) Sun Mar 19 07:17:11 2017 - [info] Current Alive Master: 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:17:11 2017 - [info] Checking slave configurations.. Sun Mar 19 07:17:11 2017 - [info] Checking replication filtering settings.. Sun Mar 19 07:17:11 2017 - [info] binlog_do_db= , binlog_ignore_db= Sun Mar 19 07:17:11 2017 - [info] Replication filtering check ok. Sun Mar 19 07:17:11 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Sun Mar 19 07:17:11 2017 - [info] Checking SSH publickey authentication settings on the current master.. Sun Mar 19 07:17:12 2017 - [info] HealthCheck: SSH to 1.1.1.131 is reachable. Sun Mar 19 07:17:12 2017 - [info] 1.1.1.131(1.1.1.131:3306) (current master) +--1.1.1.132(1.1.1.132:3306) +--1.1.1.133(1.1.1.133:3306) Sun Mar 19 07:17:12 2017 - [info] Checking master_ip_failover_script status: Sun Mar 19 07:17:12 2017 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100/24=== Checking the Status of the script.. OK Sun Mar 19 07:17:12 2017 - [info] OK. Sun Mar 19 07:17:12 2017 - [warning] shutdown_script is not defined. Sun Mar 19 07:17:12 2017 - [info] Set master ping interval 1 seconds. Sun Mar 19 07:17:12 2017 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s 1.1.1.131 -s 1.1.1.132 -s 1.1.1.133 Sun Mar 19 07:17:12 2017 - [info] Starting ping health check on 1.1.1.131(1.1.1.131:3306).. Sun Mar 19 07:17:12 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. |
切换日志
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 |
ifconfig eth0:1 1.1.1.100/24 Master配置 Sun Mar 19 07:19:58 2017 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away) Sun Mar 19 07:19:58 2017 - [info] Executing SSH check script: exit 0 Sun Mar 19 07:19:58 2017 - [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s 1.1.1.131 -s 1.1.1.132 -s 1.1.1.133 --user=root --master_host=1.1.1.131 --master_ip=1.1.1.131 --master_port=3306 --master_user=mha --master_password=admin --ping_type=SELECT Sun Mar 19 07:19:59 2017 - [info] HealthCheck: SSH to 1.1.1.131 is reachable. Sun Mar 19 07:19:59 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Sun Mar 19 07:19:59 2017 - [warning] Connection failed 2 time(s).. Monitoring server 1.1.1.131 is reachable, Master is not reachable from 1.1.1.131. OK. Sun Mar 19 07:20:00 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Sun Mar 19 07:20:00 2017 - [warning] Connection failed 3 time(s).. Monitoring server 1.1.1.132 is reachable, Master is not reachable from 1.1.1.132. OK. Monitoring server 1.1.1.133 is reachable, Master is not reachable from 1.1.1.133. OK. Sun Mar 19 07:20:01 2017 - [info] Master is not reachable from all other monitoring servers. Failover should start. Sun Mar 19 07:20:01 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Sun Mar 19 07:20:01 2017 - [warning] Connection failed 4 time(s).. Sun Mar 19 07:20:01 2017 - [warning] Master is not reachable from health checker! Sun Mar 19 07:20:01 2017 - [warning] Master 1.1.1.131(1.1.1.131:3306) is not reachable! Sun Mar 19 07:20:01 2017 - [warning] SSH is reachable. Sun Mar 19 07:20:01 2017 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mysql_mha.conf again, and trying to connect to all servers to check server status.. Sun Mar 19 07:20:01 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Mar 19 07:20:01 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 07:20:01 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 07:20:02 2017 - [info] GTID failover mode = 1 Sun Mar 19 07:20:02 2017 - [info] Dead Servers: Sun Mar 19 07:20:02 2017 - [info] 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:02 2017 - [info] Alive Servers: Sun Mar 19 07:20:02 2017 - [info] 1.1.1.132(1.1.1.132:3306) Sun Mar 19 07:20:02 2017 - [info] 1.1.1.133(1.1.1.133:3306) Sun Mar 19 07:20:02 2017 - [info] Alive Slaves: Sun Mar 19 07:20:02 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:02 2017 - [info] GTID ON Sun Mar 19 07:20:02 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:02 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 07:20:02 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:02 2017 - [info] GTID ON Sun Mar 19 07:20:02 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:02 2017 - [info] Not candidate for the new Master (no_master is set) Sun Mar 19 07:20:02 2017 - [info] Checking slave configurations.. Sun Mar 19 07:20:02 2017 - [info] Checking replication filtering settings.. Sun Mar 19 07:20:02 2017 - [info] Replication filtering check ok. Sun Mar 19 07:20:02 2017 - [info] Master is down! Sun Mar 19 07:20:02 2017 - [info] Terminating monitoring script. Sun Mar 19 07:20:02 2017 - [info] Got exit code 20 (Master dead). Sun Mar 19 07:20:02 2017 - [info] MHA::MasterFailover version 0.57. Sun Mar 19 07:20:02 2017 - [info] Starting master failover. Sun Mar 19 07:20:02 2017 - [info] Sun Mar 19 07:20:02 2017 - [info] * Phase 1: Configuration Check Phase.. Sun Mar 19 07:20:02 2017 - [info] Sun Mar 19 07:20:02 2017 - [info] GTID failover mode = 1 Sun Mar 19 07:20:02 2017 - [info] Dead Servers: Sun Mar 19 07:20:02 2017 - [info] 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:02 2017 - [info] Checking master reachability via MySQL(double check)... Sun Mar 19 07:20:02 2017 - [info] ok. Sun Mar 19 07:20:02 2017 - [info] Alive Servers: Sun Mar 19 07:20:02 2017 - [info] 1.1.1.132(1.1.1.132:3306) Sun Mar 19 07:20:02 2017 - [info] 1.1.1.133(1.1.1.133:3306) Sun Mar 19 07:20:02 2017 - [info] Alive Slaves: Sun Mar 19 07:20:02 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:02 2017 - [info] GTID ON Sun Mar 19 07:20:02 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:02 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 07:20:02 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:02 2017 - [info] GTID ON Sun Mar 19 07:20:02 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:02 2017 - [info] Not candidate for the new Master (no_master is set) Sun Mar 19 07:20:02 2017 - [info] Starting GTID based failover. Sun Mar 19 07:20:02 2017 - [info] Sun Mar 19 07:20:02 2017 - [info] ** Phase 1: Configuration Check Phase completed. Sun Mar 19 07:20:02 2017 - [info] Sun Mar 19 07:20:02 2017 - [info] * Phase 2: Dead Master Shutdown Phase.. Sun Mar 19 07:20:02 2017 - [info] Sun Mar 19 07:20:02 2017 - [info] Forcing shutdown so that applications never connect to the current master.. Sun Mar 19 07:20:02 2017 - [info] Executing master IP deactivation script: Sun Mar 19 07:20:02 2017 - [info] /usr/bin/master_ip_failover --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --command=stopssh --ssh_user=root IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100/24=== Disabling the VIP on old master: 1.1.1.131 Sun Mar 19 07:20:03 2017 - [info] done. Sun Mar 19 07:20:03 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Sun Mar 19 07:20:03 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed. Sun Mar 19 07:20:03 2017 - [info] Sun Mar 19 07:20:03 2017 - [info] * Phase 3: Master Recovery Phase.. Sun Mar 19 07:20:03 2017 - [info] Sun Mar 19 07:20:03 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Sun Mar 19 07:20:03 2017 - [info] Sun Mar 19 07:20:03 2017 - [info] The latest binary log file/position on all slaves is mysql-bin.000015:234 Sun Mar 19 07:20:03 2017 - [info] Retrieved Gtid Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:6 Sun Mar 19 07:20:03 2017 - [info] Latest slaves (Slaves that received relay log files to the latest): Sun Mar 19 07:20:03 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:03 2017 - [info] GTID ON Sun Mar 19 07:20:03 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:03 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 07:20:03 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:03 2017 - [info] GTID ON Sun Mar 19 07:20:03 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:03 2017 - [info] Not candidate for the new Master (no_master is set) Sun Mar 19 07:20:03 2017 - [info] The oldest binary log file/position on all slaves is mysql-bin.000015:234 Sun Mar 19 07:20:03 2017 - [info] Retrieved Gtid Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:6 Sun Mar 19 07:20:03 2017 - [info] Oldest slaves: Sun Mar 19 07:20:03 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:03 2017 - [info] GTID ON Sun Mar 19 07:20:03 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:03 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 07:20:03 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:03 2017 - [info] GTID ON Sun Mar 19 07:20:03 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:03 2017 - [info] Not candidate for the new Master (no_master is set) Sun Mar 19 07:20:03 2017 - [info] Sun Mar 19 07:20:03 2017 - [info] * Phase 3.3: Determining New Master Phase.. Sun Mar 19 07:20:03 2017 - [info] Sun Mar 19 07:20:03 2017 - [info] Searching new master from slaves.. Sun Mar 19 07:20:03 2017 - [info] Candidate masters from the configuration file: Sun Mar 19 07:20:03 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:03 2017 - [info] GTID ON Sun Mar 19 07:20:03 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:03 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 07:20:03 2017 - [info] Non-candidate masters: Sun Mar 19 07:20:03 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:20:03 2017 - [info] GTID ON Sun Mar 19 07:20:03 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:20:03 2017 - [info] Not candidate for the new Master (no_master is set) Sun Mar 19 07:20:03 2017 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Sun Mar 19 07:20:03 2017 - [info] New master is 1.1.1.132(1.1.1.132:3306) Sun Mar 19 07:20:03 2017 - [info] Starting master failover.. Sun Mar 19 07:20:03 2017 - [info] From: 1.1.1.131(1.1.1.131:3306) (current master) +--1.1.1.132(1.1.1.132:3306) +--1.1.1.133(1.1.1.133:3306) To: 1.1.1.132(1.1.1.132:3306) (new master) +--1.1.1.133(1.1.1.133:3306) Sun Mar 19 07:20:03 2017 - [info] Sun Mar 19 07:20:03 2017 - [info] * Phase 3.3: New Master Recovery Phase.. Sun Mar 19 07:20:03 2017 - [info] Sun Mar 19 07:20:03 2017 - [info] Waiting all logs to be applied.. Sun Mar 19 07:20:03 2017 - [info] done. Sun Mar 19 07:20:03 2017 - [info] Getting new master's binlog name and position.. Sun Mar 19 07:20:03 2017 - [info] mysql-bin.000014:234 Sun Mar 19 07:20:03 2017 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='1.1.1.132', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'; Sun Mar 19 07:20:03 2017 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000014, 234, a2396dd7-0c06-11e7-b5c1-000c290f01e1:1-6, d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262 Sun Mar 19 07:20:03 2017 - [info] Executing master IP activate script: Sun Mar 19 07:20:03 2017 - [info] /usr/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --new_master_host=1.1.1.132 --new_master_ip=1.1.1.132 --new_master_port=3306 --new_master_user='mha' --new_master_password=xxx Option new_master_user does not take an argument Option new_master_password does not take an argument IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100/24=== Enabling the VIP - 1.1.1.100/24 on the new master - 1.1.1.132 Usage: arping [-fqbDUAV] [-c count] [-w timeout] [-I device] [-s source] destination -f : quit on first reply -q : be quiet -b : keep broadcasting, don't go unicast -D : duplicate address detection mode -U : Unsolicited ARP mode, update your neighbours -A : ARP answer mode, update your neighbours -V : print version and exit -c count : how many packets to send -w timeout : how long to wait for a reply -I device : which ethernet device to use (eth0) -s source : source ip address destination : ask for what ip address Sun Mar 19 07:20:04 2017 - [info] OK. Sun Mar 19 07:20:04 2017 - [info] Setting read_only=0 on 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 07:20:04 2017 - [info] ok. Sun Mar 19 07:20:04 2017 - [info] ** Finished master recovery successfully. Sun Mar 19 07:20:04 2017 - [info] * Phase 3: Master Recovery Phase completed. Sun Mar 19 07:20:04 2017 - [info] Sun Mar 19 07:20:04 2017 - [info] * Phase 4: Slaves Recovery Phase.. Sun Mar 19 07:20:04 2017 - [info] Sun Mar 19 07:20:04 2017 - [info] Sun Mar 19 07:20:04 2017 - [info] * Phase 4.1: Starting Slaves in parallel.. Sun Mar 19 07:20:04 2017 - [info] Sun Mar 19 07:20:04 2017 - [info] -- Slave recovery on host 1.1.1.133(1.1.1.133:3306) started, pid: 2470. Check tmp log /home/mysql_mha/1.1.1.133_3306_20170319072002.log if it takes time.. Sun Mar 19 07:20:05 2017 - [info] Sun Mar 19 07:20:05 2017 - [info] Log messages from 1.1.1.133 ... Sun Mar 19 07:20:05 2017 - [info] Sun Mar 19 07:20:04 2017 - [info] Resetting slave 1.1.1.133(1.1.1.133:3306) and starting replication from the new master 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 07:20:04 2017 - [info] Executed CHANGE MASTER. Sun Mar 19 07:20:05 2017 - [info] Slave started. Sun Mar 19 07:20:05 2017 - [info] gtid_wait(a2396dd7-0c06-11e7-b5c1-000c290f01e1:1-6, d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262) completed on 1.1.1.133(1.1.1.133:3306). Executed 0 events. Sun Mar 19 07:20:05 2017 - [info] End of log messages from 1.1.1.133. Sun Mar 19 07:20:05 2017 - [info] -- Slave on host 1.1.1.133(1.1.1.133:3306) started. Sun Mar 19 07:20:05 2017 - [info] All new slave servers recovered successfully. Sun Mar 19 07:20:05 2017 - [info] Sun Mar 19 07:20:05 2017 - [info] * Phase 5: New master cleanup phase.. Sun Mar 19 07:20:05 2017 - [info] Sun Mar 19 07:20:05 2017 - [info] Resetting slave info on the new master.. Sun Mar 19 07:20:05 2017 - [info] 1.1.1.132: Resetting slave info succeeded. Sun Mar 19 07:20:05 2017 - [info] Master failover to 1.1.1.132(1.1.1.132:3306) completed successfully. Sun Mar 19 07:20:05 2017 - [info] ----- Failover Report ----- mysql_mha: MySQL Master failover 1.1.1.131(1.1.1.131:3306) to 1.1.1.132(1.1.1.132:3306) succeeded Master 1.1.1.131(1.1.1.131:3306) is down! Check MHA Manager logs at DB3.t4x.org:/var/log/mha_manages.log for details. Started automated(non-interactive) failover. Invalidated master IP address on 1.1.1.131(1.1.1.131:3306) Selected 1.1.1.132(1.1.1.132:3306) as a new master. 1.1.1.132(1.1.1.132:3306): OK: Applying all logs succeeded. 1.1.1.132(1.1.1.132:3306): OK: Activated master IP address. 1.1.1.133(1.1.1.133:3306): OK: Slave started, replicating from 1.1.1.132(1.1.1.132:3306) 1.1.1.132(1.1.1.132:3306): Resetting slave info succeeded. Master failover to 1.1.1.132(1.1.1.132:3306) completed successfully. |
|
0 1 2 |
[root@DB3 ~]# masterha_check_status --conf=/etc/mha/mysql_mha.conf [root@DB3 ~]# masterha_check_repl --conf=/etc/mha/mysql_mha.conf [root@DB3 ~]# masterha_manager --conf=/etc/mha/mysql_mha.conf |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
[root@DB3 ~]# masterha_master_switch --conf=/etc/mha/mysql_mha.conf --master_state=alive --new_master_host=1.1.1.132 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000 Sun Mar 19 07:56:12 2017 - [info] MHA::MasterRotate version 0.57. Sun Mar 19 07:56:12 2017 - [info] Starting online master switch.. Sun Mar 19 07:56:12 2017 - [info] Sun Mar 19 07:56:12 2017 - [info] * Phase 1: Configuration Check Phase.. Sun Mar 19 07:56:12 2017 - [info] Sun Mar 19 07:56:12 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Mar 19 07:56:12 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 07:56:12 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf.. Sun Mar 19 07:56:12 2017 - [info] GTID failover mode = 1 Sun Mar 19 07:56:12 2017 - [info] Current Alive Master: 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:56:12 2017 - [info] Alive Slaves: Sun Mar 19 07:56:12 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:56:12 2017 - [info] GTID ON Sun Mar 19 07:56:12 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:56:12 2017 - [info] Primary candidate for the new Master (candidate_master is set) Sun Mar 19 07:56:12 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled Sun Mar 19 07:56:12 2017 - [info] GTID ON Sun Mar 19 07:56:12 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306) Sun Mar 19 07:56:12 2017 - [info] Not candidate for the new Master (no_master is set) It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 1.1.1.131(1.1.1.131:3306)? (YES/no): YES Sun Mar 19 07:56:15 2017 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Sun Mar 19 07:56:15 2017 - [info] ok. Sun Mar 19 07:56:15 2017 - [info] Checking MHA is not monitoring or doing failover.. Sun Mar 19 07:56:15 2017 - [info] Checking replication health on 1.1.1.132.. Sun Mar 19 07:56:15 2017 - [info] ok. Sun Mar 19 07:56:15 2017 - [info] Checking replication health on 1.1.1.133.. Sun Mar 19 07:56:15 2017 - [info] ok. Sun Mar 19 07:56:15 2017 - [info] 1.1.1.132 can be new master. Sun Mar 19 07:56:15 2017 - [info] From: 1.1.1.131(1.1.1.131:3306) (current master) +--1.1.1.132(1.1.1.132:3306) +--1.1.1.133(1.1.1.133:3306) To: 1.1.1.132(1.1.1.132:3306) (new master) +--1.1.1.133(1.1.1.133:3306) +--1.1.1.131(1.1.1.131:3306) Starting master switch from 1.1.1.131(1.1.1.131:3306) to 1.1.1.132(1.1.1.132:3306)? (yes/NO): yes Sun Mar 19 07:56:18 2017 - [info] Checking whether 1.1.1.132(1.1.1.132:3306) is ok for the new master.. Sun Mar 19 07:56:18 2017 - [info] ok. Sun Mar 19 07:56:18 2017 - [info] 1.1.1.131(1.1.1.131:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. Sun Mar 19 07:56:19 2017 - [info] 1.1.1.131(1.1.1.131:3306): Resetting slave pointing to the dummy host. Sun Mar 19 07:56:19 2017 - [info] ** Phase 1: Configuration Check Phase completed. Sun Mar 19 07:56:19 2017 - [info] Sun Mar 19 07:56:19 2017 - [info] * Phase 2: Rejecting updates Phase.. Sun Mar 19 07:56:19 2017 - [info] Sun Mar 19 07:56:19 2017 - [info] Executing master ip online change script to disable write on the current master: Sun Mar 19 07:56:19 2017 - [info] /usr/bin/master_ip_online_change --command=stop --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=1.1.1.132 --new_master_ip=1.1.1.132 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx *************************************************************** Disabling the VIP - 1.1.1.100/24 on old master: 1.1.1.131 *************************************************************** SIOCSIFFLAGS: Cannot assign requested address Sun Mar 19 07:56:19 2017 - [info] ok. Sun Mar 19 07:56:19 2017 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Sun Mar 19 07:56:19 2017 - [info] Executing FLUSH TABLES WITH READ LOCK.. Sun Mar 19 07:56:19 2017 - [info] ok. Sun Mar 19 07:56:19 2017 - [info] Orig master binlog:pos is mysql-bin.000018:234. Sun Mar 19 07:56:19 2017 - [info] Waiting to execute all relay logs on 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 07:56:19 2017 - [info] master_pos_wait(mysql-bin.000018:234) completed on 1.1.1.132(1.1.1.132:3306). Executed 0 events. Sun Mar 19 07:56:19 2017 - [info] done. Sun Mar 19 07:56:19 2017 - [info] Getting new master's binlog name and position.. Sun Mar 19 07:56:19 2017 - [info] mysql-bin.000015:234 Sun Mar 19 07:56:19 2017 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='1.1.1.132', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'; Sun Mar 19 07:56:19 2017 - [info] Executing master ip online change script to allow write on the new master: Sun Mar 19 07:56:19 2017 - [info] /usr/bin/master_ip_online_change --command=start --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=1.1.1.132 --new_master_ip=1.1.1.132 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx *************************************************************** Enabling the VIP - 1.1.1.100/24 on new master: 1.1.1.132 *************************************************************** Sun Mar 19 07:56:20 2017 - [info] ok. Sun Mar 19 07:56:20 2017 - [info] Setting read_only=0 on 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 07:56:20 2017 - [info] ok. Sun Mar 19 07:56:20 2017 - [info] Sun Mar 19 07:56:20 2017 - [info] * Switching slaves in parallel.. Sun Mar 19 07:56:20 2017 - [info] Sun Mar 19 07:56:20 2017 - [info] -- Slave switch on host 1.1.1.133(1.1.1.133:3306) started, pid: 2383 Sun Mar 19 07:56:20 2017 - [info] Sun Mar 19 07:56:21 2017 - [info] Log messages from 1.1.1.133 ... Sun Mar 19 07:56:21 2017 - [info] Sun Mar 19 07:56:20 2017 - [info] Waiting to execute all relay logs on 1.1.1.133(1.1.1.133:3306).. Sun Mar 19 07:56:20 2017 - [info] master_pos_wait(mysql-bin.000018:234) completed on 1.1.1.133(1.1.1.133:3306). Executed 0 events. Sun Mar 19 07:56:20 2017 - [info] done. Sun Mar 19 07:56:20 2017 - [info] Resetting slave 1.1.1.133(1.1.1.133:3306) and starting replication from the new master 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 07:56:20 2017 - [info] Executed CHANGE MASTER. Sun Mar 19 07:56:21 2017 - [info] Slave started. Sun Mar 19 07:56:21 2017 - [info] End of log messages from 1.1.1.133 ... Sun Mar 19 07:56:21 2017 - [info] Sun Mar 19 07:56:21 2017 - [info] -- Slave switch on host 1.1.1.133(1.1.1.133:3306) succeeded. Sun Mar 19 07:56:21 2017 - [info] Unlocking all tables on the orig master: Sun Mar 19 07:56:21 2017 - [info] Executing UNLOCK TABLES.. Sun Mar 19 07:56:21 2017 - [info] ok. Sun Mar 19 07:56:21 2017 - [info] Starting orig master as a new slave.. Sun Mar 19 07:56:21 2017 - [info] Resetting slave 1.1.1.131(1.1.1.131:3306) and starting replication from the new master 1.1.1.132(1.1.1.132:3306).. Sun Mar 19 07:56:21 2017 - [info] Executed CHANGE MASTER. Sun Mar 19 07:56:22 2017 - [info] Slave started. Sun Mar 19 07:56:22 2017 - [info] All new slave servers switched successfully. Sun Mar 19 07:56:22 2017 - [info] Sun Mar 19 07:56:22 2017 - [info] * Phase 5: New master cleanup phase.. Sun Mar 19 07:56:22 2017 - [info] Sun Mar 19 07:56:22 2017 - [info] 1.1.1.132: Resetting slave info succeeded. Sun Mar 19 07:56:22 2017 - [info] Switching master to 1.1.1.132(1.1.1.132:3306) completed successfully. |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[root@DB3 ~]# cat /etc/mha/mysql_mha.conf [server default] user=mha password=admin manager_workdir=/var/log/mha manager_log=/var/log/mha/mha_manages.log remote_workdir=/var/log/mha ssh_user=root repl_user=repl repl_password=admin ping_interval=1 master_binlog_dir=/data/3306/sql_log/ master_ip_failover_script=/usr/bin/master_ip_failover master_ip_online_change_script=/usr/bin/master_ip_online_change secondary_check_script=/usr/bin/masterha_secondary_check -s 1.1.1.131 -s 1.1.1.132 -s 1.1.1.133 [server1] hostname=1.1.1.131 candidate_master=1 check_repl_delay=0 [server2] hostname=1.1.1.132 candidate_master=1 check_repl_delay=0 [server3] hostname=1.1.1.133 ignore_fail=1 no_master=1 |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
[root@DB1 ~]# cat /etc/my.cnf [client] port = 3306 socket = /usr/local/mysql/tmp/mysql.sock [mysqld] user = mysql port = 3306 socket = /usr/local/mysql/tmp/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 10240 back_log = 600 max_connections = 3000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 300 query_cache_size = 64M query_cache_limit = 4M query_cache_min_res_unit = 2k thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 256M max_heap_table_size = 256M long_query_time = 2 log-error = /usr/local/mysql/tmp/error.log pid-file = /usr/local/mysql/tmp/mysql.pid log-bin = /data/3306/sql_log/mysql-bin relay_log = /data/3306/sql_log/relay-bin relay-log-info-file = /data/3306/sql_log/relay-log.info binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 512M expire_logs_days = 7 key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_repair_threads = 1 lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062,1007 #replicate-ignore-db = mysql server-id = 131 gtid_mode = on enforce_gtid_consistency = on #explicit_defaults_for_timestamp = true #ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql_mode=NO_ENGINE_SUBSTITUTION #sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES #sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION log-slave-updates = 1 binlog_format = row #skip_slave_start = 1 log-slave-updates = true enforce-gtid-consistency = true |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
[root@DB3 sql_log]# cat /etc/my.cnf [client] port = 3306 socket = /usr/local/mysql/tmp/mysql.sock [mysqld] user = mysql port = 3306 socket = /usr/local/mysql/tmp/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 10240 back_log = 600 max_connections = 3000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 300 query_cache_size = 64M query_cache_limit = 4M query_cache_min_res_unit = 2k thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 256M max_heap_table_size = 256M long_query_time = 2 log-error = /usr/local/mysql/tmp/error.log pid-file = /usr/local/mysql/tmp/mysql.pid log-bin = /data/3306/sql_log/mysql-bin relay_log = /data/3306/sql_log/relay-bin relay-log-info-file = /data/3306/sql_log/relay-log.info binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 512M expire_logs_days = 7 key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_repair_threads = 1 lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062,1007 #replicate-ignore-db = mysql server-id = 133 #explicit_defaults_for_timestamp = true #ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql_mode=NO_ENGINE_SUBSTITUTION #sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES #sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION log-slave-updates = 1 binlog_format = row #skip_slave_start = 1 log-slave-updates = 1 enforce-gtid-consistency = true gtid_mode = on enforce_gtid_consistency = on read_only = on #master_info_repository = TABLE #relay_log_info_repository = TABLE #super_read_only = on |
|
0 1 2 |
Last_SQL_Errno: 1396 Last_SQL_Error: Error 'Operation CREATE USER failed for 'repl'@'192.168.2.%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'192.168.2.%' IDENTIFIED WITH 'mysql_native_password' AS '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'' mysql> drop user repl@'192.168.2.%'; |
下载地址:https://drive.google.com/drive/folders/0B1lu97m8-haWeHdGWXp0YVVUSlk文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/master-high-availability-mysql/ 文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/master-high-availability-mysql/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
