xml地图|网站地图|网站标签 [设为首页] [加入收藏]
MySQL高可用之MHA切换测试,ibdata1文件损坏时恢复
分类:数据库

 

1197多语句事务要求更大的max_binlog_cache_size报错

 

Preface

  binlog_cache_size:为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点。

Preface

 

max_binlog_cache_size设置的参考标准

 

    I've installed MasterHA yesterday,Now let's test the master-slave switch and failover feature.

  Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数;Binlog_cache_use 表示用binlog_cache_size缓存的次数,当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

    ibdata1 file is a shared system tablespace of innodb engine.Although we always set variable of "innodb_file_per_table" to "on" which means put the data of table into their individual ibd files.The ibdata file will still store undo log of our innodb tables.As the ibdata1 file is inevitable to make the MySQL database running properly.Today,I'm gonna demonstrate a way to rescue the table in an instance whose ibdata file is destroied by a certain way such as write several unmeaningful characters into it.

 

【故障情景】

 

Framework

  通过脚本以load的方式导入数据时,出现多行事务需要的max_binlog_cache_size空间不足。该数据文件HAOHUAN.txt只包含以逗号分隔的500万行左右的数据,每行四列,文件大小为270M。

Porcedure

 

1 [root@172-16-3-190 shells]# bash +x load_data_into.sh 
2                 文件的总数为:1 
3                 文件名为:/tmp/load/HAOHUAN.txt 
4 当前正在处理的文件是:/tmp/load/HAOHUAN.txt
5 load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by 'n' (merchant_no,bank_code,bank_card,protocol_no)
6 Warning: Using a password on the command line interface can be insecure.
7 ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

 

Hostname IP Port Identity OS Version MySQL Version
zlm2 192.168.1.101 3306 master CentOS 7.0 5.7.21
zlm3 192.168.1.102 3306 slave/mha-manager CentOS 7.0 5.7.21
null 192.168.1.200 null vip null null

【故障排查】

Backup the instance using Xtrabackup first.

Procedure

  查看max_binlog_cache_size的大小,发现数据文件的大小确实较max_binlog_cache_size的值要小,如果max_binlog_cache_size的大小不足以存放事务的binlog,那么会临时使用磁盘临时文件来存放binlog,通过查看Binlog_cache_disk_use发现使用临时文件存放的次数为1。因此增大max_binlog_cache_size的值到300M,再次执行脚本发现还是报相同的错误。且使用临时文件的次数为2,使用临时文件的存放binlog的总次数也相应由15增加到了16次。

 1 [root@zlm1 13:46:27 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
 3 180805 13:46:40 innobackupex: Starting the backup operation
 4 ... //Omitted.
 5 
 6 [root@zlm1 13:47:14 /data/backup]
 7 #ls -l
 8 total 4
 9 drwxr-x--- 7 root root 4096 Aug  5 13:47 2018-08-05_13-46-40
10 
11 [root@zlm1 13:47:17 /data/backup]

 

 1 mysql> show global variables like '%binlog_cache%';
 2 +-----------------------+-----------+
 3 | Variable_name | Value |
 4 +-----------------------+-----------+
 5 | binlog_cache_size | 16777216 |
 6 | max_binlog_cache_size | 268435456 |
 7 +-----------------------+-----------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> show global status like '%binlog_cache%';
11 +-----------------------+-------+
12 | Variable_name | Value |
13 +-----------------------+-------+
14 | Binlog_cache_disk_use | 1 |
15 | Binlog_cache_use | 15 |
16 +-----------------------+-------+
17 2 rows in set (0.00 sec)
18 
19 mysql> set @@global.max_binlog_cache_size=300000000;
20 Query OK, 0 rows affected, 1 warning (0.00 sec)
21 
22 [root@172-16-3-190 shells]# bash +x load_data_into.sh          
23                 文件的总数为:1 
24                 文件名为:/tmp/load/HAOHUAN.txt 
25 当前正在处理的文件是:/tmp/load/HAOHUAN.txt
26 load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by 'n' (merchant_no,bank_code,bank_card,protocol_no)
27 Warning: Using a password on the command line interface can be insecure.
28 ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
29 
30 mysql> show global status like '%binlog_cache%';         
31 +-----------------------+-------+
32 | Variable_name | Value |
33 +-----------------------+-------+
34 | Binlog_cache_disk_use | 2 |
35 | Binlog_cache_use | 16 |
36 +-----------------------+-------+
37 2 rows in set (0.00 sec)

 

Test 1:Manual master switchover

无奈直接增加max_binlog_cache_size的值到500M时问题才解决(后经test实际给到400M也可以load成功),但是slave上的值没有及时改动,因而SQL同步线程报错,stop同步线程,同master一样的更改后,同步才算正常

Check the target table which we want to rescue in plan.

 

 1 mysql> set @@global.max_binlog_cache_size=500000000;
 2 Query OK, 0 rows affected, 1 warning (0.00 sec)
 3 
 4 mysql> show slave status G;
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 172.16.3.190
 8                   Master_User: repl
 9                   Master_Port: 3309
10                 Connect_Retry: 30
11               Master_Log_File: binlog.000018
12           Read_Master_Log_Pos: 120
13                Relay_Log_File: relay_bin.000006
14                 Relay_Log_Pos: 6973
15         Relay_Master_Log_File: binlog.000017
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: Yes
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 1197
25                    Last_Error: Could not execute Write_rows event on table practice.temp_baofoo_unbind; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log binlog.000017, end_log_pos 268602107
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 11408
28               Relay_Log_Space: 333526981
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: 208
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 1197
43                Last_SQL_Error: Could not execute Write_rows event on table practice.temp_baofoo_unbind; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log binlog.000017, end_log_pos 268602107
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1903309
46                   Master_UUID: 1b589d80-f450-11e7-9150-525400f4ecb2
47              Master_Info_File: /opt/app/mysql_3309/logs/master.info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: Reading event from the relay log
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 180803 17:39:08
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 
59                 Auto_Position: 0
60 1 row in set (0.00 sec)
61 
62 mysql> stop slave;
63 Query OK, 0 rows affected (1 min 10.64 sec)
 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
13 +----------+
14 | count(*) |
15 +----------+
16 |    10000 |
17 +----------+
18 1 row in set (0.05 sec)
19 
20 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest4 limit 1000;
21 Query OK, 1000 rows affected (0.17 sec)
22 
23 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
24 +----------+
25 | count(*) |
26 +----------+
27 |     9000 |
28 +----------+
29 1 row in set (0.00 sec)

Check state of MHA-manager on zlm3.

【故障总结】

 

 1 [root@zlm3 07:35:00 ~]
 2 #masterha_check_ssh --conf=/etc/masterha/app1.conf
 3 Fri Aug  3 07:37:13 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
 4 Fri Aug  3 07:37:13 2018 - [info] Reading application default configuration from /etc/masterha/app1.conf..
 5 Fri Aug  3 07:37:13 2018 - [info] Reading server configuration from /etc/masterha/app1.conf..
 6 Fri Aug  3 07:37:13 2018 - [info] Starting SSH connection tests..
 7 Fri Aug  3 07:37:13 2018 - [debug] 
 8 Fri Aug  3 07:37:13 2018 - [debug]  Connecting via SSH from root@192.168.1.101(192.168.1.101:22) to root@192.168.1.102(192.168.1.102:22)..
 9 Fri Aug  3 07:37:13 2018 - [debug]   ok.
10 Fri Aug  3 07:37:14 2018 - [debug] 
11 Fri Aug  3 07:37:13 2018 - [debug]  Connecting via SSH from root@192.168.1.102(192.168.1.102:22) to root@192.168.1.101(192.168.1.101:22)..
12 Fri Aug  3 07:37:13 2018 - [debug]   ok.
13 Fri Aug  3 07:37:14 2018 - [info] All SSH connection tests passed successfully.
14 
15 [root@zlm3 07:37:14 ~]
16 #masterha_check_repl --conf=/etc/masterha/app1.conf --global_conf=/etc/masterha/masterha_default.conf 
17 Fri Aug  3 07:37:37 2018 - [info] Reading default configuration from /etc/masterha/masterha_default.conf..
18 Fri Aug  3 07:37:37 2018 - [info] Reading application default configuration from /etc/masterha/app1.conf..
19 Fri Aug  3 07:37:37 2018 - [info] Reading server configuration from /etc/masterha/app1.conf..
20 Fri Aug  3 07:37:37 2018 - [info] MHA::MasterMonitor version 0.56.
21 Fri Aug  3 07:37:38 2018 - [info] GTID failover mode = 1
22 Fri Aug  3 07:37:38 2018 - [info] Dead Servers:
23 Fri Aug  3 07:37:38 2018 - [info] Alive Servers:
24 Fri Aug  3 07:37:38 2018 - [info]   192.168.1.101(192.168.1.101:3306)
25 Fri Aug  3 07:37:38 2018 - [info]   192.168.1.102(192.168.1.102:3306)
26 Fri Aug  3 07:37:38 2018 - [info] Alive Slaves:
27 Fri Aug  3 07:37:38 2018 - [info]   192.168.1.102(192.168.1.102:3306)  Version=5.7.21-log (oldest major version between slaves) log-bin:enabled
28 Fri Aug  3 07:37:38 2018 - [info]     GTID ON
29 Fri Aug  3 07:37:38 2018 - [info]     Replicating from 192.168.1.101(192.168.1.101:3306)
30 Fri Aug  3 07:37:38 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
31 Fri Aug  3 07:37:38 2018 - [info] Current Alive Master: 192.168.1.101(192.168.1.101:3306)
32 Fri Aug  3 07:37:38 2018 - [info] Checking slave configurations..
33 Fri Aug  3 07:37:38 2018 - [info]  read_only=1 is not set on slave 192.168.1.102(192.168.1.102:3306).
34 Fri Aug  3 07:37:38 2018 - [info] Checking replication filtering settings..
35 Fri Aug  3 07:37:38 2018 - [info]  binlog_do_db= , binlog_ignore_db= 
36 Fri Aug  3 07:37:38 2018 - [info]  Replication filtering check ok.
37 Fri Aug  3 07:37:38 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
38 Fri Aug  3 07:37:38 2018 - [info] Checking SSH publickey authentication settings on the current master..
39 ssh_exchange_identification: Connection closed by remote host
40 Fri Aug  3 07:37:38 2018 - [warning] HealthCheck: SSH to 192.168.1.101 is NOT reachable.
41 Fri Aug  3 07:37:38 2018 - [info] 
42 192.168.1.101(192.168.1.101:3306) (current master)
43  +--192.168.1.102(192.168.1.102:3306)
44 
45 Fri Aug  3 07:37:38 2018 - [info] Checking replication health on 192.168.1.102..
46 Fri Aug  3 07:37:38 2018 - [info]  ok.
47 Fri Aug  3 07:37:38 2018 - [info] Checking master_ip_failover_script status:
48 Fri Aug  3 07:37:38 2018 - [info]   /etc/masterha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.1.101 --orig_master_ip=192.168.1.101 --orig_master_port=3306  --orig_master_ssh_port=3306
49 Fri Aug  3 07:37:38 2018 - [info]  OK.
50 Fri Aug  3 07:37:38 2018 - [warning] shutdown_script is not defined.
51 Fri Aug  3 07:37:38 2018 - [info] Got exit code 0 (Not master dead).
52 
53 MySQL Replication Health is OK.
54 
55 [root@zlm3 07:40:03 ~]
56 #Fri Aug  3 07:40:03 2018 - [info] Reading default configuration from /etc/masterha/masterha_default.conf..
57 Fri Aug  3 07:40:03 2018 - [info] Reading application default configuration from /etc/masterha/app1.conf..
58 Fri Aug  3 07:40:03 2018 - [info] Reading server configuration from /etc/masterha/app1.conf..
59 ssh_exchange_identification: Connection closed by remote host
60 ^C
61 
62 [root@zlm3 07:40:11 ~]
63 #masterha_check_status --conf=/etc/masterha/app1.conf
64 app1 (pid:5628) is running(0:PING_OK), master:192.168.1.101

  max_binlog_cache_size参数时动态参数,该值的设置可以参考binlog_cache_use的大小来相应增加。load导入或者delete数据的大小必须要大于max_binlog_cache_size的值,多行事务才能成功执行。该参数值修改后,注意要与配置文件中的值大小一致。

Destroy the ibdata1 file with "dd" command.

 

 1 [root@zlm1 13:57:01 ~]
 2 #cd /data/mysql/mysql3306/data
 3 
 4 [root@zlm1 13:59:35 /data/mysql/mysql3306/data]
 5 #ls -l
 6 total 433892
 7 -rw-r----- 1 mysql mysql        56 Mar 18 15:10 auto.cnf
 8 -rw-r----- 1 mysql mysql     81490 Aug  5 13:50 error.log
 9 -rw-r----- 1 mysql mysql       882 Jul 31 16:36 ib_buffer_pool
10 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ibdata1
11 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile0
12 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile1
13 -rw-r----- 1 mysql mysql 104857600 Jul  1 14:32 ib_logfile2
14 -rw-r----- 1 mysql mysql  12582912 Aug  5 13:45 ibtmp1
15 -rw-r----- 1 mysql mysql      3924 Aug  5 13:53 innodb_status.3799
16 -rw-r----- 1 mysql mysql         0 Jul 14 02:52 innodb_status.3828
17 -rw-r----- 1 mysql mysql      4008 Jun  1 21:38 innodb_status.4131
18 drwxr-x--- 2 mysql mysql      4096 Jul  7 09:57 mrbs
19 drwxr-x--- 2 mysql mysql      4096 Mar 18 15:10 mysql
20 -rw-r----- 1 mysql mysql         5 Aug  5 13:45 mysql.pid
21 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 performance_schema
22 -rw-r----- 1 mysql mysql       177 Jun  4 16:48 relay-bin.000001
23 -rw-r----- 1 mysql mysql        19 Jun  4 16:48 relay-bin.index
24 -rw-r----- 1 mysql mysql    526773 Aug  5 13:45 slow.log
25 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 sys
26 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:35 sysbench
27 -rw-r----- 1 mysql mysql  11610419 Jul 29 09:52 zlm1.log
28 
29 [root@zlm1 13:59:38 /data/mysql/mysql3306/data]
30 #dd if=/dev/zero of=./ibdata1 bs=1024 count=262144
31 262144+0 records in
32 262144+0 records out
33 268435456 bytes (268 MB) copied, 1.61997 s, 166 MB/s
34 
35 [root@zlm1 14:02:34 /data/mysql/mysql3306/data]
36 #ls -l|grep ibdata1
37 -rw-r----- 1 mysql mysql 268435456 Aug  5 14:06 ibdata1 //The ibdata1 turned out to be 256M and filled with zero.

Switch master to slave and make it become a new slave of new master.

 

  1 [root@zlm3 08:21:27 ~]
  2 #masterha_master_switch --conf=/etc/masterha/app1.conf --global_conf=/etc/masterha/masterha_default.conf --master_state=alive --new_master_host=192.168.1.102 --orig_master_is_new_slave --running_updates_limit=60
  3 Fri Aug  3 08:21:29 2018 - [info] MHA::MasterRotate version 0.56.
  4 Fri Aug  3 08:21:29 2018 - [info] Starting online master switch..
  5 Fri Aug  3 08:21:29 2018 - [info] 
  6 Fri Aug  3 08:21:29 2018 - [info] * Phase 1: Configuration Check Phase..
  7 Fri Aug  3 08:21:29 2018 - [info] 
  8 Fri Aug  3 08:21:29 2018 - [info] Reading default configuration from /etc/masterha/masterha_default.conf..
  9 Fri Aug  3 08:21:29 2018 - [info] Reading application default configuration from /etc/masterha/app1.conf..
 10 Fri Aug  3 08:21:29 2018 - [info] Reading server configuration from /etc/masterha/app1.conf..
 11 Fri Aug  3 08:21:30 2018 - [info] GTID failover mode = 1
 12 Fri Aug  3 08:21:30 2018 - [info] Current Alive Master: 192.168.1.101(192.168.1.101:3306)
 13 Fri Aug  3 08:21:30 2018 - [info] Alive Slaves:
 14 Fri Aug  3 08:21:30 2018 - [info]   192.168.1.102(192.168.1.102:3306)  Version=5.7.21-log (oldest major version between slaves) log-bin:enabled
 15 Fri Aug  3 08:21:30 2018 - [info]     GTID ON
 16 Fri Aug  3 08:21:30 2018 - [info]     Replicating from 192.168.1.101(192.168.1.101:3306)
 17 Fri Aug  3 08:21:30 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
 18 
 19 It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.1.101(192.168.1.101:3306)? (YES/no): yes
 20 Fri Aug  3 08:21:33 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
 21 Fri Aug  3 08:21:33 2018 - [info]  ok.
 22 Fri Aug  3 08:21:33 2018 - [info] Checking MHA is not monitoring or doing failover..
 23 Fri Aug  3 08:21:33 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterRotate.pm, ln142] Getting advisory lock failed on the current master. MHA Monitor runs on the current master. Stop MHA Manager/Monitor and try again.
 24 Fri Aug  3 08:21:33 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/bin/masterha_master_switch line 53.
 25 
 26 //It means that we should stop MHA-manager when donging switchover master.
 27 
 28 [root@zlm3 08:21:33 ~]
 29 #masterha_stop --conf=/etc/masterha/app1.conf --global_conf=/etc/masterha/masterha_default.conf
 30 Stopped app1 successfully.
 31 [1]+  Exit 1                  masterha_manager --conf=/etc/masterha/app1.conf --global_conf=/etc/masterha/masterha_default.conf
 32 
 33 [root@zlm3 08:28:07 ~]
 34 #masterha_master_switch --conf=/etc/masterha/app1.conf --global_conf=/etc/masterha/masterha_default.conf --master_state=alive --new_master_host=192.168.1.102 --orig_master_is_new_slave --running_updates_limit=60
 35 Fri Aug  3 08:28:21 2018 - [info] MHA::MasterRotate version 0.56.
 36 Fri Aug  3 08:28:21 2018 - [info] Starting online master switch..
 37 Fri Aug  3 08:28:21 2018 - [info] 
 38 Fri Aug  3 08:28:21 2018 - [info] * Phase 1: Configuration Check Phase..
 39 Fri Aug  3 08:28:21 2018 - [info] 
 40 Fri Aug  3 08:28:21 2018 - [info] Reading default configuration from /etc/masterha/masterha_default.conf..
 41 Fri Aug  3 08:28:21 2018 - [info] Reading application default configuration from /etc/masterha/app1.conf..
 42 Fri Aug  3 08:28:21 2018 - [info] Reading server configuration from /etc/masterha/app1.conf..
 43 Fri Aug  3 08:28:22 2018 - [info] GTID failover mode = 1
 44 Fri Aug  3 08:28:22 2018 - [info] Current Alive Master: 192.168.1.101(192.168.1.101:3306)
 45 Fri Aug  3 08:28:22 2018 - [info] Alive Slaves:
 46 Fri Aug  3 08:28:22 2018 - [info]   192.168.1.102(192.168.1.102:3306)  Version=5.7.21-log (oldest major version between slaves) log-bin:enabled
 47 Fri Aug  3 08:28:22 2018 - [info]     GTID ON
 48 Fri Aug  3 08:28:22 2018 - [info]     Replicating from 192.168.1.101(192.168.1.101:3306)
 49 Fri Aug  3 08:28:22 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
 50 
 51 It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.1.101(192.168.1.101:3306)? (YES/no): yes
 52 Fri Aug  3 08:28:25 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
 53 Fri Aug  3 08:28:25 2018 - [info]  ok.
 54 Fri Aug  3 08:28:25 2018 - [info] Checking MHA is not monitoring or doing failover..
 55 Fri Aug  3 08:28:25 2018 - [info] Checking replication health on 192.168.1.102..
 56 Fri Aug  3 08:28:25 2018 - [info]  ok.
 57 Fri Aug  3 08:28:25 2018 - [info] 192.168.1.102 can be new master.
 58 Fri Aug  3 08:28:25 2018 - [info] 
 59 From:
 60 192.168.1.101(192.168.1.101:3306) (current master)
 61  +--192.168.1.102(192.168.1.102:3306)
 62 
 63 To:
 64 192.168.1.102(192.168.1.102:3306) (new master)
 65  +--192.168.1.101(192.168.1.101:3306)
 66 
 67 Starting master switch from 192.168.1.101(192.168.1.101:3306) to 192.168.1.102(192.168.1.102:3306)? (yes/NO): yes
 68 Fri Aug  3 08:28:31 2018 - [info] Checking whether 192.168.1.102(192.168.1.102:3306) is ok for the new master..
 69 Fri Aug  3 08:28:31 2018 - [info]  ok.
 70 Fri Aug  3 08:28:31 2018 - [info] 192.168.1.101(192.168.1.101:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
 71 Fri Aug  3 08:28:31 2018 - [info] 192.168.1.101(192.168.1.101:3306): Resetting slave pointing to the dummy host.
 72 Fri Aug  3 08:28:31 2018 - [info] ** Phase 1: Configuration Check Phase completed.
 73 Fri Aug  3 08:28:31 2018 - [info] 
 74 Fri Aug  3 08:28:31 2018 - [info] * Phase 2: Rejecting updates Phase..
 75 Fri Aug  3 08:28:31 2018 - [info] 
 76 Fri Aug  3 08:28:31 2018 - [info] Executing master ip online change script to disable write on the current master:
 77 Fri Aug  3 08:28:31 2018 - [info]   /etc/masterha/master_ip_online_change --command=stop --orig_master_host=192.168.1.101 --orig_master_ip=192.168.1.101 --orig_master_port=3306 --orig_master_user='zlm' --orig_master_password='zlmzlm' --new_master_host=192.168.1.102 --new_master_ip=192.168.1.102 --new_master_port=3306 --new_master_user='zlm' --new_master_password='zlmzlm' --orig_master_ssh_user=root --new_master_ssh_user=root  --orig_master_ssh_port=3306  --new_master_ssh_port=3306 --orig_master_is_new_slave
 78 Unknown option: new_master_ssh_port
 79 Fri Aug  3 08:28:32 2018 116409 Set read_only on the new master.. ok.
 80 Fri Aug  3 08:28:32 2018 125643 drop vip 10.33.101.239..
 81 ssh_exchange_identification: Connection closed by remote host
 82 Fri Aug  3 08:28:32 2018 142948 Waiting all running 1 threads are disconnected.. (max 1500 milliseconds)
 83 {'Time' => '13435','db' => undef,'Id' => '21','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'zlm3:40535'}
 84 Fri Aug  3 08:28:32 2018 646769 Waiting all running 1 threads are disconnected.. (max 1000 milliseconds)
 85 {'Time' => '13435','db' => undef,'Id' => '21','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'zlm3:40535'}
 86 Fri Aug  3 08:28:33 2018 149221 Waiting all running 1 threads are disconnected.. (max 500 milliseconds)
 87 {'Time' => '13436','db' => undef,'Id' => '21','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'zlm3:40535'}
 88 Fri Aug  3 08:28:33 2018 650816 Set read_only=1 on the orig master.. ok.
 89 Fri Aug  3 08:28:33 2018 653323 Waiting all running 1 queries are disconnected.. (max 500 milliseconds)
 90 {'Time' => '13436','db' => undef,'Id' => '21','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'zlm3:40535'}
 91 Fri Aug  3 08:28:34 2018 154965 Killing all application threads..
 92 Fri Aug  3 08:28:34 2018 167919 done.
 93 Fri Aug  3 08:28:34 2018 - [info]  ok.
 94 Fri Aug  3 08:28:34 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
 95 Fri Aug  3 08:28:34 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
 96 Fri Aug  3 08:28:34 2018 - [info]  ok.
 97 Fri Aug  3 08:28:34 2018 - [info] Orig master binlog:pos is mysql-bin.000050:2361.
 98 Fri Aug  3 08:28:34 2018 - [info]  Waiting to execute all relay logs on 192.168.1.102(192.168.1.102:3306)..
 99 Fri Aug  3 08:28:34 2018 - [info]  master_pos_wait(mysql-bin.000050:2361) completed on 192.168.1.102(192.168.1.102:3306). Executed 0 events.
100 Fri Aug  3 08:28:34 2018 - [info]   done.
101 Fri Aug  3 08:28:34 2018 - [info] Getting new master's binlog name and position..
102 Fri Aug  3 08:28:34 2018 - [info]  mysql-bin.000003:2321
103 Fri Aug  3 08:28:34 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.102', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
104 Fri Aug  3 08:28:34 2018 - [info] Executing master ip online change script to allow write on the new master:
105 Fri Aug  3 08:28:34 2018 - [info]   /etc/masterha/master_ip_online_change --command=start --orig_master_host=192.168.1.101 --orig_master_ip=192.168.1.101 --orig_master_port=3306 --orig_master_user='zlm' --orig_master_password='zlmzlm' --new_master_host=192.168.1.102 --new_master_ip=192.168.1.102 --new_master_port=3306 --new_master_user='zlm' --new_master_password='zlmzlm' --orig_master_ssh_user=root --new_master_ssh_user=root  --orig_master_ssh_port=3306  --new_master_ssh_port=3306 --orig_master_is_new_slave
106 Unknown option: new_master_ssh_port
107 Fri Aug  3 08:28:34 2018 327146 Set read_only=0 on the new master.
108 Fri Aug  3 08:28:34 2018 328259Add vip 10.33.101.239 on p3p1..
109 ssh_exchange_identification: Connection closed by remote host
110 Fri Aug  3 08:28:34 2018 - [info]  ok.
111 Fri Aug  3 08:28:34 2018 - [info] 
112 Fri Aug  3 08:28:34 2018 - [info] * Switching slaves in parallel..
113 Fri Aug  3 08:28:34 2018 - [info] 
114 Fri Aug  3 08:28:34 2018 - [info] Unlocking all tables on the orig master:
115 Fri Aug  3 08:28:34 2018 - [info] Executing UNLOCK TABLES..
116 Fri Aug  3 08:28:34 2018 - [info]  ok.
117 Fri Aug  3 08:28:34 2018 - [info] Starting orig master as a new slave..
118 Fri Aug  3 08:28:34 2018 - [info]  Resetting slave 192.168.1.101(192.168.1.101:3306) and starting replication from the new master 192.168.1.102(192.168.1.102:3306)..
119 Fri Aug  3 08:28:34 2018 - [info]  Executed CHANGE MASTER.
120 Fri Aug  3 08:28:35 2018 - [info]  Slave started.
121 Fri Aug  3 08:28:35 2018 - [info] All new slave servers switched successfully.
122 Fri Aug  3 08:28:35 2018 - [info] 
123 Fri Aug  3 08:28:35 2018 - [info] * Phase 5: New master cleanup phase..
124 Fri Aug  3 08:28:35 2018 - [info] 
125 Fri Aug  3 08:28:35 2018 - [info]  192.168.1.102: Resetting slave info succeeded.
126 Fri Aug  3 08:28:35 2018 - [info] Switching master to 192.168.1.102(192.168.1.102:3306) completed successfully.
127 
128 [root@zlm3 08:28:35 ~]
129 #

Restart the MySQL instance.

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:MySQL高可用之MHA切换测试,ibdata1文件损坏时恢复

上一篇:MsSQL使用加密连接SSL,mySql常用命令 下一篇:没有了
猜你喜欢
热门排行
精彩图文