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

 

 

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

Preface

Preface

max_binlog_cache_size设置的参考标准

 

 

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

    I’ve stuck twice in my previous
experiments in backing up dropped tables.I am still not sure that why I
got failure yesterday.Therefore,I decide to do that again with the same
steps on another environment.Let’s see the details.

    When we add a new node into PXC
structure,it will estimate the mothed(IST/SST) to transfer data to the
new node.It’s no wonder that sst will be automatically choosed.What if a
dropped node in PXC by accident want to rejoin the cluster?Especially it
has been a long period of time after it was dropped out.We should avoid
starting up the node directly.As the SST(either xtrabackup-v2 or rsync
mode) will be choosed to resynchronize the dropped node instead of
IST.Generally speaking,SST may cause to decline the perormance of the
whole cluster.It’s recommend to use another way to deal with that kind
of issue.That is,slave replication.

【故障情景】

 

 

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

Procedure

Framework

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

 

 

【故障排查】

1. All the operations on master
zlm1.

Hostname IP Port OS Version MySQL Version Xtrabackup version
zlm2 192.168.1.101 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm3 192.168.1.102 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm4 192.168.1.103 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)

 
查看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 //Check data at first.
  2 zlm@192.168.56.100:3306 [sysbench]>show tables;
  3 +--------------------+
  4 | Tables_in_sysbench |
  5 +--------------------+
  6 | sbtest1            |
  7 | sbtest2            |
  8 | sbtest3            |
  9 | sbtest4            |
 10 | sbtest5            |
 11 +--------------------+
 12 5 rows in set (0.00 sec)
 13 
 14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest5;
 15 +----------+
 16 | count(*) |
 17 +----------+
 18 |    10000 |
 19 +----------+
 20 1 row in set (0.00 sec)
 21 
 22 zlm@192.168.56.100:3306 [sysbench]>show binary logs;
 23 +------------------+-----------+
 24 | Log_name         | File_size |
 25 +------------------+-----------+
 26 | mysql-bin.000091 |      1288 |
 27 | mysql-bin.000092 |       217 |
 28 | mysql-bin.000093 |       241 |
 29 | mysql-bin.000094 |       217 |
 30 | mysql-bin.000095 |      4128 |
 31 | mysql-bin.000096 |       241 |
 32 | mysql-bin.000097 |  11461585 |
 33 | mysql-bin.000098 |       410 |
 34 | mysql-bin.000099 |       241 |
 35 | mysql-bin.000100 |       974 |
 36 | mysql-bin.000101 |       217 |
 37 | mysql-bin.000102 |       217 |
 38 | mysql-bin.000103 |       194 |
 39 +------------------+-----------+
 40 13 rows in set (0.01 sec)
 41 
 42 zlm@192.168.56.100:3306 [sysbench]>show master status;
 43 +------------------+----------+--------------+------------------+-------------------------------------------------+
 44 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 45 +------------------+----------+--------------+------------------+-------------------------------------------------+
 46 | mysql-bin.000103 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
 47 +------------------+----------+--------------+------------------+-------------------------------------------------+
 48 1 row in set (0.00 sec)
 49 
 50 //Generate a Xtrabackup then send it to the slave zlm2.
 51 [root@zlm1 16:27:18 /data/backup]
 52 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
 53 
 54 [root@zlm1 16:34:09 /data/backup]
 55 #scp -r 2018-07-31_16-31-46/ zlm2:/data/backup/
 56 
 57 //Continue to do some operations(DML).
 58 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 2000;
 59 Query OK, 2000 rows affected (0.19 sec)
 60 
 61 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 3000;
 62 Query OK, 3000 rows affected (0.04 sec)
 63 
 64 //Drop the table.
 65 zlm@192.168.56.100:3306 [sysbench]>drop table sbtest5;
 66 Query OK, 0 rows affected (0.04 sec)
 67 
 68 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 69 Query OK, 0 rows affected (0.05 sec)
 70 
 71 zlm@192.168.56.100:3306 [sysbench]>show master status;
 72 +------------------+----------+--------------+------------------+-------------------------------------------------+
 73 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 74 +------------------+----------+--------------+------------------+-------------------------------------------------+
 75 | mysql-bin.000104 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715699 |
 76 +------------------+----------+--------------+------------------+-------------------------------------------------+
 77 1 row in set (0.00 sec)
 78 
 79 //Shutdown the master.
 80 zlm@192.168.56.100:3306 [sysbench]>exit
 81 Bye
 82 
 83 [root@zlm1 16:36:01 ~]
 84 #mysqladmin shutdown
 85 
 86 //Send the binlogs which contain the operations above to slave zlm2 either.
 87 [root@zlm1 16:36:10 ~]
 88 #cd /data/mysql/mysql3306/logs
 89 
 90 [root@zlm1 16:41:45 /data/mysql/mysql3306/logs]
 91 #ls -l
 92 total 12188
 93 -rw-r----- 1 mysql mysql     1288 Jul 22 11:27 mysql-bin.000091
 94 -rw-r----- 1 mysql mysql      217 Jul 22 11:28 mysql-bin.000092
 95 -rw-r----- 1 mysql mysql      241 Jul 25 19:18 mysql-bin.000093
 96 -rw-r----- 1 mysql mysql      217 Jul 25 19:18 mysql-bin.000094
 97 -rw-r----- 1 mysql mysql     4128 Jul 29 08:42 mysql-bin.000095
 98 -rw-r----- 1 mysql mysql      241 Jul 29 08:42 mysql-bin.000096
 99 -rw-r----- 1 mysql mysql 11461585 Jul 29 09:52 mysql-bin.000097
100 -rw-r----- 1 mysql mysql      410 Jul 29 16:27 mysql-bin.000098
101 -rw-r----- 1 mysql mysql      241 Jul 29 16:27 mysql-bin.000099
102 -rw-r----- 1 mysql mysql      974 Jul 29 16:57 mysql-bin.000100
103 -rw-r----- 1 mysql mysql      217 Jul 30 17:01 mysql-bin.000101
104 -rw-r----- 1 mysql mysql      217 Jul 30 18:12 mysql-bin.000102
105 -rw-r----- 1 mysql mysql   954995 Jul 31 16:35 mysql-bin.000103
106 -rw-r----- 1 mysql mysql      217 Jul 31 16:36 mysql-bin.000104
107 -rw-r----- 1 mysql mysql      616 Jul 31 16:35 mysql-bin.index
108 
109 [root@zlm1 16:42:01 /data/mysql/mysql3306/logs]
110 #scp mysql-bin.00010{3,4} zlm2:/data/backup
111 mysql-bin.000103                                                                                                   100%  933KB 932.6KB/s   00:00    
112 mysql-bin.000104                                                                                                   100%  217     0.2KB/s   00:00

 

 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)

 

Procedure

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

**2.
Operations on slave zlm2.**

 

 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 //Prepare to restore the backup of Xtrabackup.
  2 [root@zlm2 16:37:11 /data/backup]
  3 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-31_16-31-46/
  4 
  5 [root@zlm2 16:36:42 /data/mysql/mysql3306/data]
  6 #ls -l
  7 total 409732
  8 -rw-r----- 1 mysql mysql        56 Jul 30 17:25 auto.cnf
  9 -rw-r----- 1 mysql mysql     31332 Jul 30 21:08 error.log
 10 -rw-r----- 1 mysql mysql       871 Jul 30 21:08 ib_buffer_pool
 11 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ibdata1
 12 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ib_logfile0
 13 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile1
 14 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile2
 15 -rw-r----- 1 mysql mysql       131 Jul 30 21:08 master.info
 16 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:22 mrbs
 17 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:22 mysql
 18 drwxr-x--- 2 mysql mysql      8192 Jul 30 17:23 performance_schema
 19 -rw-r----- 1 mysql mysql       201 Jul 30 20:08 relay-bin.000001
 20 -rw-r----- 1 mysql mysql       936 Jul 30 20:08 relay-bin.000002
 21 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000003
 22 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000004
 23 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000005
 24 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000006
 25 -rw-r----- 1 mysql mysql       430 Jul 30 21:08 relay-bin.000007
 26 -rw-r----- 1 mysql mysql       133 Jul 30 20:11 relay-bin.index
 27 -rw-r----- 1 mysql mysql        53 Jul 30 21:08 relay-log.info
 28 -rw-r----- 1 mysql mysql       346 Jul 30 18:17 slow.log
 29 drwxr-x--- 2 mysql mysql      8192 Jul 30 17:22 sys
 30 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:23 sysbench
 31 -rw-r----- 1 mysql mysql        21 Jul 30 17:23 xtrabackup_binlog_pos_innodb
 32 -rw-r----- 1 mysql mysql       595 Jul 30 17:23 xtrabackup_info
 33 -rw-r----- 1 mysql mysql         1 Jul 30 17:23 xtrabackup_master_key_id
 34 
 35 [root@zlm2 16:36:41 /data/mysql/mysql3306/data]
 36 #ps aux|grep mysqld
 37 root      3900  0.0  0.0 112640   960 pts/1    R+   16:38   0:00 grep --color=auto mysqld
 38 
 39 [root@zlm2 16:36:44 /data/mysql/mysql3306/data]
 40 #rm -rf *
 41 
 42 //Copy back the datafiles.
 43 [root@zlm2 16:38:31 /data/backup]
 44 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/
 45 
 46 [root@zlm2 16:38:48 /data/mysql/mysql3306/data]
 47 #ls -l
 48 total 421940
 49 -rw-r----- 1 root root       784 Jul 31 16:39 ib_buffer_pool
 50 -rw-r----- 1 root root 104857600 Jul 31 16:39 ibdata1
 51 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile0
 52 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile1
 53 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile2
 54 -rw-r----- 1 root root  12582912 Jul 31 16:39 ibtmp1
 55 drwxr-x--- 2 root root      4096 Jul 31 16:39 mrbs
 56 drwxr-x--- 2 root root      4096 Jul 31 16:39 mysql
 57 drwxr-x--- 2 root root      8192 Jul 31 16:39 performance_schema
 58 drwxr-x--- 2 root root      8192 Jul 31 16:39 sys
 59 drwxr-x--- 2 root root      4096 Jul 31 16:39 sysbench
 60 -rw-r----- 1 root root        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
 61 -rw-r----- 1 root root       595 Jul 31 16:39 xtrabackup_info
 62 -rw-r----- 1 root root         1 Jul 31 16:39 xtrabackup_master_key_id
 63 
 64 [root@zlm2 16:40:26 /data/mysql/mysql3306/data]
 65 #sh /root/mysqld.sh
 66 
 67 [root@zlm2 16:40:33 /data/mysql/mysql3306/data]
 68 #mysql
 69 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.101' (111)
 70 
 71 [root@zlm2 16:40:37 /data/mysql/mysql3306/data]
 72 #chown -R mysql.mysql *
 73 
 74 [root@zlm2 16:41:00 /data/mysql/mysql3306/data]
 75 #sh /root/mysqld.sh
 76 
 77 [root@zlm2 16:41:02 /data/mysql/mysql3306/data]
 78 #mysql
 79 Welcome to the MySQL monitor.  Commands end with ; or \g.
 80 Your MySQL connection id is 2
 81 Server version: 5.7.21-log MySQL Community Server (GPL)
 82 
 83 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 84 
 85 Oracle is a registered trademark of Oracle Corporation and/or its
 86 affiliates. Other names may be trademarks of their respective
 87 owners.
 88 
 89 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 90 
 91 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
 92 +----------+
 93 | count(*) |
 94 +----------+
 95 |    10000 |
 96 +----------+
 97 1 row in set (0.03 sec)
 98 
 99 //Continue to restore the incremental data with sql_thread.
100 [root@zlm2 16:39:55 /data/backup]
101 #ls -l
102 total 944
103 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
104 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
105 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
106 
107 [root@zlm2 16:43:19 /data/backup]
108 #for i in $(ls mysql-bin.0*)
109 > do
110 >     ext=$(echo $i | cut -d'.' -f2);
111 >     cp $i relay-bin.$ext;
112 > done
113 
114 [root@zlm2 16:45:20 /data/backup]
115 #ls -l
116 total 1884
117 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
118 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
119 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
120 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103
121 -rw-r----- 1 root root    217 Jul 31 16:45 relay-bin.000104
122 
123 [root@zlm2 16:45:23 /data/backup]
124 #ls ./relay-bin.0* > relay-bin.index
125 
126 [root@zlm2 16:45:31 /data/backup]
127 #ls -l
128 total 1888
129 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
130 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
131 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
132 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103
133 -rw-r----- 1 root root    217 Jul 31 16:45 relay-bin.000104
134 -rw-r--r-- 1 root root     38 Jul 31 16:45 relay-bin.index
135 
136 [root@zlm2 16:45:33 /data/backup]
137 #cat relay-bin.index 
138 ./relay-bin.000103
139 ./relay-bin.000104
140 
141 [root@zlm2 16:45:37 /data/backup]
142 #chown mysql.mysql relay*
143 
144 [root@zlm2 16:45:51 /data/backup]
145 #ls -l
146 total 1888
147 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
148 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
149 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
150 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
151 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
152 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
153 
154 [root@zlm2 16:45:52 /data/backup]
155 #cp relay* /data/mysql/mysql3306/data
156 
157 [root@zlm2 16:46:08 /data/backup]
158 #cd /data/mysql/mysql3306/data
159 
160 [root@zlm2 16:46:25 /data/mysql/mysql3306/data]
161 #ls -l
162 total 422908
163 -rw-r----- 1 mysql mysql        56 Jul 31 16:41 auto.cnf
164 -rw-r----- 1 mysql mysql      5989 Jul 31 16:46 error.log
165 -rw-r----- 1 mysql mysql       784 Jul 31 16:39 ib_buffer_pool
166 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1
167 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0
168 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1
169 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2
170 -rw-r----- 1 mysql mysql  12582912 Jul 31 16:41 ibtmp1
171 -rw-r----- 1 mysql mysql         0 Jul 31 16:40 innodb_status.3949
172 -rw-r----- 1 mysql mysql      3835 Jul 31 16:46 innodb_status.3979
173 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mrbs
174 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mysql
175 -rw-r----- 1 mysql mysql         5 Jul 31 16:41 mysql.pid
176 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 performance_schema
177 -rw-r----- 1 root  root     954995 Jul 31 16:46 relay-bin.000103
178 -rw-r----- 1 root  root        217 Jul 31 16:46 relay-bin.000104
179 -rw-r--r-- 1 root  root         38 Jul 31 16:46 relay-bin.index
180 -rw-r----- 1 mysql mysql       173 Jul 31 16:41 slow.log
181 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 sys
182 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 sysbench
183 -rw-r----- 1 mysql mysql        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
184 -rw-r----- 1 mysql mysql       595 Jul 31 16:39 xtrabackup_info
185 -rw-r----- 1 mysql mysql         1 Jul 31 16:39 xtrabackup_master_key_id
186 
187 [root@zlm2 16:46:26 /data/mysql/mysql3306/data]
188 #chown mysql.mysql relay*
189 
190 [root@zlm2 16:46:37 /data/mysql/mysql3306/data]
191 #ls -l
192 total 422908
193 -rw-r----- 1 mysql mysql        56 Jul 31 16:41 auto.cnf
194 -rw-r----- 1 mysql mysql      6137 Jul 31 16:52 error.log
195 -rw-r----- 1 mysql mysql       784 Jul 31 16:39 ib_buffer_pool
196 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1
197 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0
198 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1
199 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2
200 -rw-r----- 1 mysql mysql  12582912 Jul 31 16:41 ibtmp1
201 -rw-r----- 1 mysql mysql         0 Jul 31 16:40 innodb_status.3949
202 -rw-r----- 1 mysql mysql      3835 Jul 31 16:53 innodb_status.3979
203 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mrbs
204 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mysql
205 -rw-r----- 1 mysql mysql         5 Jul 31 16:41 mysql.pid
206 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 performance_schema
207 -rw-r----- 1 mysql mysql    954995 Jul 31 16:46 relay-bin.000103
208 -rw-r----- 1 mysql mysql       217 Jul 31 16:46 relay-bin.000104
209 -rw-r--r-- 1 mysql mysql        38 Jul 31 16:46 relay-bin.index
210 -rw-r----- 1 mysql mysql       173 Jul 31 16:41 slow.log
211 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 sys
212 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 sysbench
213 -rw-r----- 1 mysql mysql        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
214 -rw-r----- 1 mysql mysql       595 Jul 31 16:39 xtrabackup_info
215 -rw-r----- 1 mysql mysql         1 Jul 31 16:39 xtrabackup_master_key_id
216 
217 zlm@192.168.56.101:3306 [(none)]>show master status;
218 +------------------+----------+--------------+------------------+-------------------------------------------------+
219 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
220 +------------------+----------+--------------+------------------+-------------------------------------------------+
221 | mysql-bin.000002 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
222 +------------------+----------+--------------+------------------+-------------------------------------------------+
223 1 row in set (0.00 sec)
224 
225 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx';
226 Query OK, 0 rows affected (0.01 sec)
227 
228 zlm@192.168.56.101:3306 [(none)]>show slave status\G
229 *************************** 1. row ***************************
230                Slave_IO_State: 
231                   Master_Host: xxx
232                   Master_User: 
233                   Master_Port: 3306
234                 Connect_Retry: 60
235               Master_Log_File: 
236           Read_Master_Log_Pos: 4
237                Relay_Log_File: relay-bin.000103
238                 Relay_Log_Pos: 194
239         Relay_Master_Log_File: 
240              Slave_IO_Running: No
241             Slave_SQL_Running: No
242               Replicate_Do_DB: 
243           Replicate_Ignore_DB: 
244            Replicate_Do_Table: 
245        Replicate_Ignore_Table: 
246       Replicate_Wild_Do_Table: 
247   Replicate_Wild_Ignore_Table: 
248                    Last_Errno: 0
249                    Last_Error: 
250                  Skip_Counter: 0
251           Exec_Master_Log_Pos: 0 //No data here yet.
252               Relay_Log_Space: 955366
253               Until_Condition: None
254                Until_Log_File: 
255                 Until_Log_Pos: 0
256            Master_SSL_Allowed: No
257            Master_SSL_CA_File: 
258            Master_SSL_CA_Path: 
259               Master_SSL_Cert: 
260             Master_SSL_Cipher: 
261                Master_SSL_Key: 
262         Seconds_Behind_Master: NULL
263 Master_SSL_Verify_Server_Cert: No
264                 Last_IO_Errno: 0
265                 Last_IO_Error: 
266                Last_SQL_Errno: 0
267                Last_SQL_Error: 
268   Replicate_Ignore_Server_Ids: 
269              Master_Server_Id: 0
270                   Master_UUID: 
271              Master_Info_File: /data/mysql/mysql3306/data/master.info
272                     SQL_Delay: 0
273           SQL_Remaining_Delay: NULL
274       Slave_SQL_Running_State: 
275            Master_Retry_Count: 86400
276                   Master_Bind: 
277       Last_IO_Error_Timestamp: 
278      Last_SQL_Error_Timestamp: 
279                Master_SSL_Crl: 
280            Master_SSL_Crlpath: 
281            Retrieved_Gtid_Set: //No data here.
282             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696
283                 Auto_Position: 0
284          Replicate_Rewrite_DB: 
285                  Channel_Name: 
286            Master_TLS_Version: 
287 1 row in set (0.00 sec)
288 
289 [root@zlm2 16:59:42 /data/backup]
290 #ls -l
291 total 1888
292 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
293 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
294 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
295 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
296 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
297 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
298 
299 //Find out the end position to set util clause.
300 [root@zlm2 16:59:43 /data/backup]
301 #mysqlbinlog --base64-output=decode-rows relay-bin.000103 > 103.log
302 
303 [root@zlm2 17:01:22 /data/backup]
304 #tail -20 103.log
305 #180731 16:35:17 server id 1003306  end_log_pos 954724 CRC32 0xe3e63622     Delete_rows: table id 114 flags: STMT_END_F
306 # at 954724
307 #180731 16:35:17 server id 1003306  end_log_pos 954755 CRC32 0x84cf5136     Xid = 31
308 COMMIT/*!*/;
309 # at 954755 //This position is which I need to set until to.
310 #180731 16:35:29 server id 1003306  end_log_pos 954820 CRC32 0xef03ef84     GTID    last_committed=2    sequence_number=3    rbr_only=no
311 SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699'/*!*/;
312 # at 954820
313 #180731 16:35:29 server id 1003306  end_log_pos 954948 CRC32 0x0309b10f     Query    thread_id=2    exec_time=0    error_code=0
314 use `sysbench`/*!*/;
315 SET TIMESTAMP=1533047729/*!*/;
316 DROP TABLE `sbtest5` /* generated by server */
317 /*!*/;
318 # at 954948
319 #180731 16:35:37 server id 1003306  end_log_pos 954995 CRC32 0xc1ca182a     Rotate to mysql-bin.000104  pos: 4
320 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
321 DELIMITER ;
322 # End of log file
323 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
324 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
325 
326 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until relay_log_file='relay-bin.000103',relay_log_pos=954755;
327 Query OK, 0 rows affected (0.00 sec)
328 
329 zlm@192.168.56.101:3306 [(none)]>show slave status\G
330 *************************** 1. row ***************************
331                Slave_IO_State: 
332                   Master_Host: xxx
333                   Master_User: 
334                   Master_Port: 3306
335                 Connect_Retry: 60
336               Master_Log_File: 
337           Read_Master_Log_Pos: 4
338                Relay_Log_File: relay-bin.000103
339                 Relay_Log_Pos: 954755
340         Relay_Master_Log_File: 
341              Slave_IO_Running: No
342             Slave_SQL_Running: No
343               Replicate_Do_DB: 
344           Replicate_Ignore_DB: 
345            Replicate_Do_Table: 
346        Replicate_Ignore_Table: 
347       Replicate_Wild_Do_Table: 
348   Replicate_Wild_Ignore_Table: 
349                    Last_Errno: 0
350                    Last_Error: 
351                  Skip_Counter: 0
352           Exec_Master_Log_Pos: 954755 //This is the terminal relay log position I set just now.
353               Relay_Log_Space: 955366
354               Until_Condition: Relay
355                Until_Log_File: relay-bin.000103
356                 Until_Log_Pos: 954755
357            Master_SSL_Allowed: No
358            Master_SSL_CA_File: 
359            Master_SSL_CA_Path: 
360               Master_SSL_Cert: 
361             Master_SSL_Cipher: 
362                Master_SSL_Key: 
363         Seconds_Behind_Master: NULL
364 Master_SSL_Verify_Server_Cert: No
365                 Last_IO_Errno: 0
366                 Last_IO_Error: 
367                Last_SQL_Errno: 0
368                Last_SQL_Error: 
369   Replicate_Ignore_Server_Ids: 
370              Master_Server_Id: 0
371                   Master_UUID: 
372              Master_Info_File: /data/mysql/mysql3306/data/master.info
373                     SQL_Delay: 0
374           SQL_Remaining_Delay: NULL
375       Slave_SQL_Running_State: 
376            Master_Retry_Count: 86400
377                   Master_Bind: 
378       Last_IO_Error_Timestamp: 
379      Last_SQL_Error_Timestamp: 
380                Master_SSL_Crl: 
381            Master_SSL_Crlpath: 
382            Retrieved_Gtid_Set: 
383             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698
384                 Auto_Position: 0
385          Replicate_Rewrite_DB: 
386                  Channel_Name: 
387            Master_TLS_Version: 
388 1 row in set (0.00 sec)
389 
390 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
391 +----------+
392 | count(*) |
393 +----------+
394 |     5000 |
395 +----------+
396 1 row in set (0.00 sec)

what’s show in error log of
initiating SST/IST transfer operation on a new added node.

【故障总结】

 

 1 2018-08-09T07:23:32.568794+01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4433' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
 2 2018-08-09T07:23:33.225673+01:00 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.1.103:4444/xtrabackup_sst//1
 3 2018-08-09T07:23:33.225697+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 3) (Increment: 1 -> 3)
 4 2018-08-09T07:23:33.225704+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 5 2018-08-09T07:23:33.225721+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
 6 2018-08-09T07:23:33.225760+01:00 0 [Note] WSREP: Service thread queue flushed.
 7 2018-08-09T07:23:33.226619+01:00 2 [Note] WSREP: Check if state gap can be serviced using IST
 8 2018-08-09T07:23:33.226638+01:00 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 9 2018-08-09T07:23:33.226677+01:00 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
10 2018-08-09T07:23:33.226683+01:00 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (bd5525ab-9a15-11e8-aa0f-4b830c783fc7): 1 (Operation not permitted)
11      at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.
12 2018-08-09T07:23:33.228003+01:00 0 [Note] WSREP: Member 2.0 (zlm4) requested state transfer from '*any*'. Selected 0.0 (zlm2)(SYNCED) as donor.
13 2018-08-09T07:23:33.228029+01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 22)
14 2018-08-09T07:23:33.228088+01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 0
15 2018-08-09T07:23:33.228108+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
16     2018-08-09T05:23:33.781389Z WSREP_SST: [INFO] Proceeding with SST.........
17     2018-08-09T05:23:33.808866Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!

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

3. The supplemented
experiment.

 

  1 //Shudown the instance.
  2 [root@zlm2 18:27:23 /data/mysql/mysql3306/data]
  3 #ps aux|grep mysqld
  4 mysql     3979  0.0 20.0 1072196 204196 pts/1  Sl   16:41   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
  5 root      4265  0.0  0.0 112640   960 pts/0    R+   18:27   0:00 grep --color=auto mysqld
  6 
  7 [root@zlm2 18:27:28 /data/mysql/mysql3306/data]
  8 #mysqladmin shutdown
  9 
 10 [root@zlm2 18:27:42 /data/mysql/mysql3306/data]
 11 
 12 //Copy back the data one more time(no need to executed with option "--apply-log" first this time).
 13 [root@zlm2 18:28:13 /data/backup]
 14 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/
 15 
 16 //Copy the relay logs to datadir directory again.
 17 [root@zlm2 18:29:22 /data/backup]
 18 #ls -l
 19 total 1904
 20 -rw-r--r-- 1 root  root   16082 Jul 31 17:01 103.log
 21 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
 22 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
 23 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
 24 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
 25 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
 26 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
 27 
 28 [root@zlm2 18:29:45 /data/backup]
 29 #cp relay* /data/mysql/mysql3306/data
 30 
 31 [root@zlm2 18:30:25 /data/mysql/mysql3306/data]
 32 #ls -l
 33 total 422884
 34 -rw-r----- 1 root root       784 Jul 31 18:29 ib_buffer_pool
 35 -rw-r----- 1 root root 104857600 Jul 31 18:29 ibdata1
 36 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile0
 37 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile1
 38 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile2
 39 -rw-r----- 1 root root  12582912 Jul 31 18:29 ibtmp1
 40 drwxr-x--- 2 root root      4096 Jul 31 18:29 mrbs
 41 drwxr-x--- 2 root root      4096 Jul 31 18:29 mysql
 42 drwxr-x--- 2 root root      8192 Jul 31 18:29 performance_schema
 43 -rw-r----- 1 root root    954995 Jul 31 18:30 relay-bin.000103
 44 -rw-r----- 1 root root       217 Jul 31 18:30 relay-bin.000104
 45 -rw-r--r-- 1 root root        38 Jul 31 18:30 relay-bin.index
 46 drwxr-x--- 2 root root      8192 Jul 31 18:29 sys
 47 drwxr-x--- 2 root root      4096 Jul 31 18:29 sysbench
 48 -rw-r----- 1 root root        21 Jul 31 18:29 xtrabackup_binlog_pos_innodb
 49 -rw-r----- 1 root root       595 Jul 31 18:29 xtrabackup_info
 50 -rw-r----- 1 root root         1 Jul 31 18:29 xtrabackup_master_key_id
 51 
 52 [root@zlm2 18:31:11 /data/mysql/mysql3306/data]
 53 #chown -R mysql.mysql *
 54 
 55 [root@zlm2 18:31:19 /data/mysql/mysql3306/data]
 56 #sh /root/mysqld.sh
 57 
 58 [root@zlm2 18:31:23 /data/mysql/mysql3306/data]
 59 #mysql
 60 Welcome to the MySQL monitor.  Commands end with ; or \g.
 61 Your MySQL connection id is 2
 62 Server version: 5.7.21-log MySQL Community Server (GPL)
 63 
 64 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 65 
 66 Oracle is a registered trademark of Oracle Corporation and/or its
 67 affiliates. Other names may be trademarks of their respective
 68 owners.
 69 
 70 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 71 
 72 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
 73 +----------+
 74 | count(*) |
 75 +----------+
 76 |    10000 |
 77 +----------+
 78 1 row in set (0.04 sec)
 79 
 80 zlm@192.168.56.101:3306 [(none)]>show master status;
 81 +------------------+----------+--------------+------------------+-------------------------------------------------+
 82 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 83 +------------------+----------+--------------+------------------+-------------------------------------------------+
 84 | mysql-bin.000003 |      210 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698 |
 85 +------------------+----------+--------------+------------------+-------------------------------------------------+
 86 1 row in set (0.00 sec)
 87 
 88 zlm@192.168.56.101:3306 [(none)]>reset master;
 89 Query OK, 0 rows affected (0.03 sec)
 90 
 91 zlm@192.168.56.101:3306 [(none)]>show master status;
 92 +------------------+----------+--------------+------------------+-------------------+
 93 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 94 +------------------+----------+--------------+------------------+-------------------+
 95 | mysql-bin.000001 |      154 |              |                  |                   |
 96 +------------------+----------+--------------+------------------+-------------------+
 97 1 row in set (0.00 sec)
 98 
 99 zlm@192.168.56.101:3306 [(none)]>show slave status\G
100 Empty set (0.00 sec)
101 
102 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696';
103 Query OK, 0 rows affected (0.00 sec)
104 
105 zlm@192.168.56.101:3306 [(none)]>show master status;
106 +------------------+----------+--------------+------------------+-------------------------------------------------+
107 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
108 +------------------+----------+--------------+------------------+-------------------------------------------------+
109 | mysql-bin.000001 |      154 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
110 +------------------+----------+--------------+------------------+-------------------------------------------------+
111 1 row in set (0.00 sec)
112 
113 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx';
114 Query OK, 0 rows affected (0.01 sec)
115 
116 zlm@192.168.56.101:3306 [(none)]>show slave status\G
117 *************************** 1. row ***************************
118                Slave_IO_State: 
119                   Master_Host: xxx
120                   Master_User: 
121                   Master_Port: 3306
122                 Connect_Retry: 60
123               Master_Log_File: 
124           Read_Master_Log_Pos: 4
125                Relay_Log_File: relay-bin.000103
126                 Relay_Log_Pos: 194
127         Relay_Master_Log_File: 
128              Slave_IO_Running: No
129             Slave_SQL_Running: No
130               Replicate_Do_DB: 
131           Replicate_Ignore_DB: 
132            Replicate_Do_Table: 
133        Replicate_Ignore_Table: 
134       Replicate_Wild_Do_Table: 
135   Replicate_Wild_Ignore_Table: 
136                    Last_Errno: 0
137                    Last_Error: 
138                  Skip_Counter: 0
139           Exec_Master_Log_Pos: 0
140               Relay_Log_Space: 955366
141               Until_Condition: None
142                Until_Log_File: 
143                 Until_Log_Pos: 0
144            Master_SSL_Allowed: No
145            Master_SSL_CA_File: 
146            Master_SSL_CA_Path: 
147               Master_SSL_Cert: 
148             Master_SSL_Cipher: 
149                Master_SSL_Key: 
150         Seconds_Behind_Master: NULL
151 Master_SSL_Verify_Server_Cert: No
152                 Last_IO_Errno: 0
153                 Last_IO_Error: 
154                Last_SQL_Errno: 0
155                Last_SQL_Error: 
156   Replicate_Ignore_Server_Ids: 
157              Master_Server_Id: 0
158                   Master_UUID: 
159              Master_Info_File: /data/mysql/mysql3306/data/master.info
160                     SQL_Delay: 0
161           SQL_Remaining_Delay: NULL
162       Slave_SQL_Running_State: 
163            Master_Retry_Count: 86400
164                   Master_Bind: 
165       Last_IO_Error_Timestamp: 
166      Last_SQL_Error_Timestamp: 
167                Master_SSL_Crl: 
168            Master_SSL_Crlpath: 
169            Retrieved_Gtid_Set: 
170             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696
171                 Auto_Position: 0
172          Replicate_Rewrite_DB: 
173                  Channel_Name: 
174            Master_TLS_Version: 
175 1 row in set (0.00 sec)
176 
177 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699';
178 Query OK, 0 rows affected (0.51 sec)
179 
180 zlm@192.168.56.101:3306 [(none)]>show slave status\G
181 *************************** 1. row ***************************
182                Slave_IO_State: 
183                   Master_Host: xxx
184                   Master_User: 
185                   Master_Port: 3306
186                 Connect_Retry: 60
187               Master_Log_File: 
188           Read_Master_Log_Pos: 4
189                Relay_Log_File: relay-bin.000103
190                 Relay_Log_Pos: 954755
191         Relay_Master_Log_File: 
192              Slave_IO_Running: No
193             Slave_SQL_Running: No
194               Replicate_Do_DB: 
195           Replicate_Ignore_DB: 
196            Replicate_Do_Table: 
197        Replicate_Ignore_Table: 
198       Replicate_Wild_Do_Table: 
199   Replicate_Wild_Ignore_Table: 
200                    Last_Errno: 0
201                    Last_Error: 
202                  Skip_Counter: 0
203           Exec_Master_Log_Pos: 954755
204               Relay_Log_Space: 955366
205               Until_Condition: SQL_BEFORE_GTIDS //This time we use this option to start slave sql_thread.
206                Until_Log_File: 
207                 Until_Log_Pos: 0
208            Master_SSL_Allowed: No
209            Master_SSL_CA_File: 
210            Master_SSL_CA_Path: 
211               Master_SSL_Cert: 
212             Master_SSL_Cipher: 
213                Master_SSL_Key: 
214         Seconds_Behind_Master: NULL
215 Master_SSL_Verify_Server_Cert: No
216                 Last_IO_Errno: 0
217                 Last_IO_Error: 
218                Last_SQL_Errno: 0
219                Last_SQL_Error: 
220   Replicate_Ignore_Server_Ids: 
221              Master_Server_Id: 0
222                   Master_UUID: 
223              Master_Info_File: /data/mysql/mysql3306/data/master.info
224                     SQL_Delay: 0
225           SQL_Remaining_Delay: NULL
226       Slave_SQL_Running_State: 
227            Master_Retry_Count: 86400
228                   Master_Bind: 
229       Last_IO_Error_Timestamp: 
230      Last_SQL_Error_Timestamp: 
231                Master_SSL_Crl: 
232            Master_SSL_Crlpath: 
233            Retrieved_Gtid_Set: 
234             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698
235                 Auto_Position: 0
236          Replicate_Rewrite_DB: 
237                  Channel_Name: 
238            Master_TLS_Version: 
239 1 row in set (0.00 sec)
240 
241 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
242 +----------+
243 | count(*) |
244 +----------+
245 |     5000 |
246 +----------+
247 1 row in set (0.00 sec)

**Check the PXC status at
first.**

 

 1 (zlm@192.168.1.101 3308)[(none)]>show global status like '%wsrep%';
 2 +----------------------------------+----------------------------------------------------------+
 3 | Variable_name                    | Value                                                    |
 4 +----------------------------------+----------------------------------------------------------+
 5 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
 6 | wsrep_protocol_version           | 8                                                        |
 7 | wsrep_last_applied               | 22                                                       |
 8 | wsrep_last_committed             | 22                                                       |
 9 | wsrep_replicated                 | 0                                                        |
10 | wsrep_replicated_bytes           | 0                                                        |
11 | wsrep_repl_keys                  | 0                                                        |
12 | wsrep_repl_keys_bytes            | 0                                                        |
13 | wsrep_repl_data_bytes            | 0                                                        |
14 | wsrep_repl_other_bytes           | 0                                                        |
15 | wsrep_received                   | 21                                                       |
16 | wsrep_received_bytes             | 2733                                                     |
17 | wsrep_local_commits              | 0                                                        |
18 | wsrep_local_cert_failures        | 0                                                        |
19 | wsrep_local_replays              | 0                                                        |
20 | wsrep_local_send_queue           | 0                                                        |
21 | wsrep_local_send_queue_max       | 1                                                        |
22 | wsrep_local_send_queue_min       | 0                                                        |
23 | wsrep_local_send_queue_avg       | 0.000000                                                 |
24 | wsrep_local_recv_queue           | 0                                                        |
25 | wsrep_local_recv_queue_max       | 2                                                        |
26 | wsrep_local_recv_queue_min       | 0                                                        |
27 | wsrep_local_recv_queue_avg       | 0.047619                                                 |
28 | wsrep_local_cached_downto        | 0                                                        |
29 | wsrep_flow_control_paused_ns     | 0                                                        |
30 | wsrep_flow_control_paused        | 0.000000                                                 |
31 | wsrep_flow_control_sent          | 0                                                        |
32 | wsrep_flow_control_recv          | 0                                                        |
33 | wsrep_flow_control_interval      | [ 173, 173 ]                                             |
34 | wsrep_flow_control_interval_low  | 173                                                      |
35 | wsrep_flow_control_interval_high | 173                                                      |
36 | wsrep_flow_control_status        | OFF                                                      |
37 | wsrep_cert_deps_distance         | 0.000000                                                 |
38 | wsrep_apply_oooe                 | 0.000000                                                 |
39 | wsrep_apply_oool                 | 0.000000                                                 |
40 | wsrep_apply_window               | 0.000000                                                 |
41 | wsrep_commit_oooe                | 0.000000                                                 |
42 | wsrep_commit_oool                | 0.000000                                                 |
43 | wsrep_commit_window              | 0.000000                                                 |
44 | wsrep_local_state                | 4                                                        |
45 | wsrep_local_state_comment        | Synced                                                   |
46 | wsrep_cert_index_size            | 0                                                        |
47 | wsrep_cert_bucket_count          | 22                                                       |
48 | wsrep_gcache_pool_size           | 1712                                                     |
49 | wsrep_causal_reads               | 0                                                        |
50 | wsrep_cert_interval              | 0.000000                                                 |
51 | wsrep_ist_receive_status         |                                                          |
52 | wsrep_ist_receive_seqno_start    | 0                                                        |
53 | wsrep_ist_receive_seqno_current  | 0                                                        |
54 | wsrep_ist_receive_seqno_end      | 0                                                        |
55 | wsrep_incoming_addresses         | 192.168.1.101:3308,192.168.1.102:3308,192.168.1.103:3308 |
56 | wsrep_desync_count               | 0                                                        |
57 | wsrep_evs_delayed                |                                                          |
58 | wsrep_evs_evict_list             |                                                          |
59 | wsrep_evs_repl_latency           | 0/0/0/0/0                                                |
60 | wsrep_evs_state                  | OPERATIONAL                                              |
61 | wsrep_gcomm_uuid                 | 13eae368-9b79-11e8-9053-338307f4c6cc                     |
62 | wsrep_cluster_conf_id            | 11                                                       |
63 | wsrep_cluster_size               | 3                                                        | //It means I've got three nodes in PXC.
64 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
65 | wsrep_cluster_status             | Primary                                                  |
66 | wsrep_connected                  | ON                                                       |
67 | wsrep_local_bf_aborts            | 0                                                        |
68 | wsrep_local_index                | 0                                                        |
69 | wsrep_provider_name              | Galera                                                   |
70 | wsrep_provider_vendor            | Codership Oy <info@codership.com>                        |
71 | wsrep_provider_version           | 3.26(r)                                                  |
72 | wsrep_ready                      | ON                                                       |
73 +----------------------------------+----------------------------------------------------------+
74 68 rows in set (0.00 sec)

**   
Ultimately,the incremental data comes back gain.The experiment was
finished successfully.**

 

 

Shutdown
MySQL instance on zlm4.

 

 1 [root@zlm4 09:02:18 /data/mysql/mysql3308]
 2 #!ps
 3 ps aux|grep mysqld
 4 mysql     5367  0.2 21.9 1574708 223476 pts/0  Sl   08:00   0:07 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 5 root      5809  0.0  0.0 112640   960 pts/0    R+   09:02   0:00 grep --color=auto mysqld
 6 
 7 [root@zlm4 09:02:22 /data/mysql/mysql3308]
 8 #pkill mysqld
 9 
10 [root@zlm4 09:02:43 /data/mysql/mysql3308]
11 #!ps
12 ps aux|grep mysqld
13 root      5827  0.0  0.0 112640   960 pts/0    R+   09:02   0:00 grep --color=auto mysqld
14 [1]+  Done                    mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf

 

Check
the error log on node zlm3.

 1 [root@zlm3 09:01:42 /data/mysql/mysql3308/data]
 2 #tail -f error.log 
 3 2018-08-09T09:02:44.880772+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.103:4567 
 4 2018-08-09T09:02:44.880866+01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 5 2018-08-09T09:02:44.880877+01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
 6 2018-08-09T09:02:44.880899+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting off
 7 2018-08-09T09:02:44.884451+01:00 0 [Note] WSREP: Node 13eae368 state primary
 8 2018-08-09T09:02:44.887086+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 9 view (view_id(PRIM,13eae368,12)
10 memb {
11     13eae368,0
12     23fb8f7a,0
13     }
14 joined {
15     }
16 left {
17     }
18 partitioned {
19     74f3db69,0
20     }
21 )
22 2018-08-09T09:02:44.887205+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
23 2018-08-09T09:02:44.887402+01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
24 2018-08-09T09:02:44.887688+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
25 2018-08-09T09:02:44.887705+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
26 2018-08-09T09:02:44.888444+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe
27 2018-08-09T09:02:44.889150+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 0 (zlm2)
28 2018-08-09T09:02:44.889187+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 1 (zlm3)
29 2018-08-09T09:02:44.889198+01:00 0 [Note] WSREP: Quorum results:
30     version    = 4,
31     component  = PRIMARY,
32     conf_id    = 11,
33     members    = 2/2 (primary/total),
34     act_id     = 22,
35     last_appl. = 0,
36     protocols  = 0/8/3 (gcs/repl/appl),
37     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
38 2018-08-09T09:02:44.889206+01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
39 2018-08-09T09:02:44.889210+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
40 2018-08-09T09:02:44.889304+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
41 2018-08-09T09:02:44.889315+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 12: Primary, number of nodes: 2, my index: 1, protocol version 3
42 2018-08-09T09:02:44.889319+01:00 2 [Note] WSREP: Setting wsrep_ready to true
43 2018-08-09T09:02:44.889324+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 2 -> 2) (Increment: 3 -> 2)
44 2018-08-09T09:02:44.889328+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
45 2018-08-09T09:02:44.889336+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
46 2018-08-09T09:02:44.889355+01:00 0 [Note] WSREP: Service thread queue flushed.
47 2018-08-09T09:02:50.000210+01:00 0 [Note] WSREP:  cleaning up 74f3db69 (tcp://192.168.1.103:4567)

 

**Do some DML operations on node
zlm3.**

发表评论

电子邮件地址不会被公开。 必填项已用*标注