金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 基于Xtrabackup备份集来恢复某个误删除的表,恢复

基于Xtrabackup备份集来恢复某个误删除的表,恢复

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-09-22 12:40

 

 

 

 

Preface

Preface

Preface

Preface

 

 

 

 

    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.

    Today I'm gonna test how to rescue a dropped table from binlog server based on a full Xtrabackup backup set.

    Today,I'm gonna implement a PXC,Let's see the procedure.

    Yesterday,I've demonstratated how to rescue a droped and a truncated table based on mysqldump backup.Let's see what's different in rescuing them based on a backup set made by Xtrabackup.Binlog is also indispensable,if we want to rerieve all the incremental data of them.

 

 

 

 

Procedure

Framework

Framework

Framework

 

 

 

 

1. All the operations on master zlm1.

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm1 192.168.56.100/3306 master CentOS 7.0 5.7.21 on row
zlm2 192.168.56.101/3306 slave CentOS 7.0 5.7.21 on row
zlm3 192.168.56.102/3306 binlog server CentOS 7.0 5.7.21 on row
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)
Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row
  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

 

 

 

 

Precedure

Procedure

Precedure

**2. Operations on slave zlm2.**

 

 

 

  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 statusG
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 statusG
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)

Step 1: Create binlog server.

    The official website of PXC:

Step 1: Destroy the table.

 

 

    

 

3. The supplemented experiment.

Check the position on master 

 

Generate a backup with Xtrabackup first.

  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 statusG
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 statusG
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 statusG
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)
1 zlm@192.168.56.100:3306 [sysbench]>show master status;
2 +------------------+----------+--------------+------------------+-------------------------------------------------+
3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
4 +------------------+----------+--------------+------------------+-------------------------------------------------+
5 | mysql-bin.000098 |      363 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
6 +------------------+----------+--------------+------------------+-------------------------------------------------+
7 1 row in set (0.00 sec)

    Choose the Linux-Generic software.There're three version relevant with openssl(100,101,102),I choose the ssl102 version.

 1 [root@zlm2 07:57:40 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 4 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 5 180727 07:57:43 innobackupex: Starting the backup operation
 6 
 7 IMPORTANT: Please check that the backup run completes successfully.
 8            At the end of a successful backup run innobackupex
 9            prints "completed OK!".
10 
11 ... //Omitted the procedure.
12 
13 180727 07:57:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
14 xtrabackup: The latest check point (for incremental): '10141351562'
15 xtrabackup: Stopping log copying thread.
16 .180727 07:57:56 >> log scanned up to (10141351571)
17 
18 180727 07:57:56 Executing UNLOCK TABLES
19 180727 07:57:56 All tables unlocked
20 180727 07:57:56 [00] Copying ib_buffer_pool to /data/backup/2018-07-27_07-57-43/ib_buffer_pool
21 180727 07:57:56 [00]        ...done
22 180727 07:57:56 Backup created in directory '/data/backup/2018-07-27_07-57-43/'
23 MySQL binlog position: filename 'mysql-bin.000035', position '954887', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220'
24 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/backup-my.cnf
25 180727 07:57:56 [00]        ...done
26 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/xtrabackup_info
27 180727 07:57:56 [00]        ...done
28 xtrabackup: Transaction log of lsn (10141351562) to (10141351571) was copied.
29 180727 07:57:56 completed OK!

 

 

 

 

金沙棋牌app手机下载,**    Ultimately,the incremental data comes back gain.The experiment was finished successfully.**

**Make binlog server begin to receive binlog.**

Install Percona-XtraDB-Cluster software on three nodes.

**Continue to execute dml operation.**

 

 1 [root@zlm3 16:25:01 /data]
 2 #mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098 &
 3 [1] 4375
 4 mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
 5 
 6 [root@zlm3 16:26:24 /data]
 7 #ls -l
 8 total 4
 9 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
10 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
11 -rw-r----- 1 root  root  363 Jul 29 16:26 mysql-bin.000098
 1 [root@zlm2 07:56:03 ~]
 2 #mkdir -p /opt/mysql
 3 
 4 [root@zlm2 07:56:51 ~]
 5 #ls -l|grep Percona
 6 -rwxr-xr-x   1 root root 252395830 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz
 7 
 8 [root@zlm2 07:57:01 ~]
 9 #gzip -d Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz 
10 
11 [root@zlm2 07:57:59 ~]
12 #ls -l|grep Percona
13 -rwxr-xr-x   1 root root 1035939840 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar
14 
15 [root@zlm2 07:59:36 ~]
16 #tar xf Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar -C /opt/mysql
17 
18 [root@zlm2 08:02:29 ~]
19 #cd /usr/local
20 
21 [root@zlm2 08:02:22 /usr/local]
22 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
23 
24 [root@zlm2 08:02:28 /usr/local]
25 #ls -l
26 total 4
27 drwxr-xr-x. 2 root   root    105 May  7 10:44 bin
28 drwxr-xr-x. 2 root   root      6 Jun 10  2014 etc
29 drwxr-xr-x. 2 root   root      6 Jun 10  2014 games
30 drwxr-xr-x. 2 root   root      6 Jun 10  2014 include
31 drwxr-xr-x. 2 root   root      6 Jun 10  2014 lib
32 drwxr-xr-x. 3 root   root     18 May  7 09:37 lib64
33 drwxr-xr-x. 2 root   root      6 Jun 10  2014 libexec
34 lrwxrwxrwx  1 root   root     36 Apr 28 14:30 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64/
35 drwxr-xr-x  9 root   root   4096 Aug  7 10:00 mysql-5.7.21-linux-glibc2.12-x86_64
36 drwxrwxr-x  5 root   root     94 May  7 09:28 mysql_performance_monitor-agent
37 drwxr-xr-x. 2 root   root     18 Apr 28 16:06 sbin
38 drwxr-xr-x. 6 root   root     58 May  7 09:37 share
39 drwxr-xr-x. 2 root   root      6 Jun 10  2014 src
40 drwxr-xr-x  8 zabbix zabbix   70 Apr 28 16:45 zabbix
41 
42 [root@zlm2 08:02:32 /usr/local]
43 #rm -f mysql
44 
45 [root@zlm2 08:02:37 /usr/local]
46 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
47 
48 [root@zlm2 08:03:04 /usr/local]
49 #ls -l
50 total 0
51 drwxr-xr-x. 2 root root  21 Jul  4 03:21 bin
52 drwxr-xr-x. 2 root root   6 Jun 10  2014 etc
53 drwxr-xr-x. 2 root root   6 Jun 10  2014 games
54 drwxr-xr-x. 2 root root   6 Jun 10  2014 include
55 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib
56 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib64
57 drwxr-xr-x. 2 root root   6 Jun 10  2014 libexec
58 lrwxrwxrwx  1 root root  75 Aug  7 08:23 mysql -> /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/
59 drwxr-xr-x  9 root root 120 Aug  7 08:22 mysql-5.7.21-linux-glibc2.12-x86_64
60 drwxr-xr-x. 2 root root   6 Jun 10  2014 sbin
61 drwxr-xr-x. 7 root root  71 Jul  4 03:21 share
62 drwxr-xr-x. 2 root root   6 Jun 10  2014 src
63 
64 [root@zlm2 08:32:56 /usr/local]
65 #ldd mysql/bin/mysqld
66     linux-vdso.so.1 =>  (0x00007fff96f3f000)
67     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1119136000)
68     libaio.so.1 => /lib64/libaio.so.1 (0x00007f1118f34000)
69     libnuma.so.1 => /lib64/libnuma.so.1 (0x00007f1118d27000)
70     libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f1118af0000)
71     libssl.so.10 => /lib64/libssl.so.10 (0x00007f1118883000)
72     libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f111849b000)
73     libdl.so.2 => /lib64/libdl.so.2 (0x00007f1118297000)
74     libz.so.1 => /lib64/libz.so.1 (0x00007f1118081000)
75     librt.so.1 => /lib64/librt.so.1 (0x00007f1117e78000)
76     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f1117b71000)
77     libm.so.6 => /lib64/libm.so.6 (0x00007f111786f000)
78     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f1117658000)
79     libc.so.6 => /lib64/libc.so.6 (0x00007f1117297000)
80     /lib64/ld-linux-x86-64.so.2 (0x00007f1119359000)
81     libfreebl3.so => /lib64/libfreebl3.so (0x00007f1117094000)
82     libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f1116e47000)
83     libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f1116b64000)
84     libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f1116960000)
85     libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f111672d000)
86     libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f111651e000)
87     libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f1116319000)
88     libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f11160ff000)
89     libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f1115eda000)
90     libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f1115c78000)
91     liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f1115a53000)
92     
93 ... //Omitted the procedure of installation on the other nodes.
 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11 | sbtest7            |
12 +--------------------+
13 7 rows in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
16 +----------+
17 | count(*) |
18 +----------+
19 |    10000 |
20 +----------+
21 1 row in set (0.00 sec)
22 
23 (zlm@192.168.1.101 3306)[sysbench]>show master status;
24 +------------------+----------+--------------+------------------+------------------------------------------------+
25 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 | mysql-bin.000035 |   954887 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220 |
28 +------------------+----------+--------------+------------------+------------------------------------------------+
29 1 row in set (0.00 sec)
30 
31 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
32 Query OK, 0 rows affected (0.02 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest7 limit 10;
35 Query OK, 10 rows affected (0.00 sec)
36 
37 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
38 +----------+
39 | count(*) |
40 +----------+
41 |     9990 |
42 +----------+
43 1 row in set (0.00 sec)
44 
45 (zlm@192.168.1.101 3306)[sysbench]>show master status;
46 +------------------+----------+--------------+------------------+------------------------------------------------+
47 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
48 +------------------+----------+--------------+------------------+------------------------------------------------+
49 | mysql-bin.000036 |     2338 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
50 +------------------+----------+--------------+------------------+------------------------------------------------+
51 1 row in set (0.00 sec)

 

 

 

 

**Flush two logs on master.**

**Initialized a PXC node with proper pxc relevant parameters below.**

**Drop table "sbtest7" in "sysbench" database on master in a certain time(our miss operation point).**

 1 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 2 Query OK, 0 rows affected (0.06 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 5 Query OK, 0 rows affected (0.01 sec)
 6 
 7 zlm@192.168.56.100:3306 [sysbench]>show master status;
 8 +------------------+----------+--------------+------------------+-------------------------------------------------+
 9 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
10 +------------------+----------+--------------+------------------+-------------------------------------------------+
11 | mysql-bin.000100 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
12 +------------------+----------+--------------+------------------+-------------------------------------------------+
13 1 row in set (0.00 sec)
 1 [root@zlm2 08:51:49 ~]
 2 #mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
 3 
 4 [root@zlm2 08:52:44 ~]
 5 #chown -R mysql.mysql /data
 6 
 7 [root@zlm2 08:53:27 ~]
 8 #cp /vagrant/my3308.cnf /data/mysql/mysql3308
 9 
10 [root@zlm2 08:54:09 ~]
11 #cd /data/mysql/mysql3308
12 
13 [root@zlm2 08:54:17 /data/mysql/mysql3308]
14 #ls -l
15 total 8
16 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 data
17 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 logs
18 -rwxr-xr-x 1 root  root  4522 Aug  7 08:54 my3308.cnf
19 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 tmp
20 
21 [root@zlm2 08:54:30 /data/mysql/mysql3308]
22 #tail -20 my3308.cnf 
23 ...
24 #pxc
25 innodb_locks_unsafe_for_binlog=1
26 innodb_autoinc_lock_mode=2
27 loose-wsrep_cluster_name=pxc_wubx 
28 loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.103
29 loose-wsrep_node_address=192.168.1.101
30 loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
31 loose-wsrep_sst_method=xtrabackup-v2
32 #loose-wsrep_sst_method=rsync
33 loose-wsrep_sst_auth=sst:zlmzlm
34 #loose-wsrep_debug=on
35 #loose-wsrep_provider_options="debug=on"
36 [root@zlm2 08:54:38 /data/mysql/mysql3308]
37 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --initialize
38 
39 [root@zlm2 09:00:56 /data/mysql/mysql3308]
40 #tail ./data/error.log 
41 2018-08-07T09:00:47.560965+01:00 0 [Warning] CA certificate ca.pem is self signed.
42 2018-08-07T09:00:47.603271+01:00 1 [Note] A temporary password is generated for root@localhost: bY2jmV=k)ltM //This is the password we need when first login.
43 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
44 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
45 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
46 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
47 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
48 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
49 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
50 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
51 
52 [root@zlm2 09:02:45 /data/mysql/mysql3308]
53 #cd data
54 
55 [root@zlm2 09:02:57 /data/mysql/mysql3308/data]
56 #ls -l
57 total 409676
58 -rw-r----- 1 mysql mysql        56 Aug  7 09:00 auto.cnf
59 -rw------- 1 mysql mysql      1680 Aug  7 09:00 ca-key.pem
60 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 ca.pem
61 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 client-cert.pem
62 -rw------- 1 mysql mysql      1680 Aug  7 09:00 client-key.pem
63 -rw-r----- 1 mysql mysql      2354 Aug  7 09:00 error.log
64 -rw-r----- 1 mysql mysql       423 Aug  7 09:00 ib_buffer_pool
65 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ibdata1
66 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile0
67 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile1
68 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile2
69 drwxr-x--- 2 mysql mysql      4096 Aug  7 09:00 mysql
70 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 performance_schema
71 -rw------- 1 mysql mysql      1676 Aug  7 09:00 private_key.pem
72 -rw-r--r-- 1 mysql mysql       452 Aug  7 09:00 public_key.pem
73 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 server-cert.pem
74 -rw------- 1 mysql mysql      1676 Aug  7 09:00 server-key.pem
75 -rw-r----- 1 mysql mysql       227 Aug  7 09:00 slow.log
76 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 sys
77 
78 //Omitted the operations on the other nodes.Notice that there's no need to initialize the other node.
 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest7;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 5 +--------------------+
 6 | Tables_in_sysbench |
 7 +--------------------+
 8 | sbtest1            |
 9 | sbtest2            |
10 | sbtest3            |
11 | sbtest4            |
12 | sbtest5            |
13 | sbtest6            |
14 +--------------------+
15 6 rows in set (0.00 sec)
16 
17 (zlm@192.168.1.101 3306)[sysbench]>show master status;
18 +------------------+----------+--------------+------------------+------------------------------------------------+
19 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
20 +------------------+----------+--------------+------------------+------------------------------------------------+
21 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 |
22 +------------------+----------+--------------+------------------+------------------------------------------------+
23 1 row in set (0.00 sec)

 

 

 

**Check whether the newly generated binlogs are successfully transmited to binlog server.**

**Start mysqld of PXC on zlm2 as the first node in PXC.**

Step 2: Begin to restore backup set.

1 [root@zlm3 16:26:27 /data]
2 #ls -l
3 total 12
4 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
5 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
6 -rw-r----- 1 root  root  410 Jul 29 16:27 mysql-bin.000098
7 -rw-r----- 1 root  root  241 Jul 29 16:27 mysql-bin.000099
8 -rw-r----- 1 root  root  194 Jul 29 16:27 mysql-bin.000100
  1 [root@zlm2 09:21:46 /data/mysql/mysql3308/data]
  2 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
  3 [1] 6251
  4 
  5 [root@zlm2 09:22:57 /data/mysql/mysql3308/data]
  6 #tail ../data/error.log |grep ERROR
  7 2018-08-07T09:22:57.471628+01:00 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/local/mysql/lib/libgalera_smm.so: symbol SSL_COMP_free_compression_methods, version libssl.so.10 not defined in file libssl.so.10 with link time reference
  8 2018-08-07T09:22:57.471657+01:00 0 [ERROR] WSREP: wsrep_load() failed to load the provider('/usr/local/mysql/lib/libgalera_smm.so'): Invalid argument (22). Need to abort.
  9 2018-08-07T09:22:57.471661+01:00 0 [ERROR] Aborting
 10 [1]+  Exit 1                  mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 11 
 12 [root@zlm2 09:23:19 /data/mysql/mysql3308/data]
 13 #cd /usr/local/mysql/lib
 14 
 15 [root@zlm2 09:26:28 /usr/local/mysql/lib]
 16 #ls -l|grep libgalera_smm.so
 17 -rwxr-xr-x 1 root root 35086192 Jun 29 05:44 libgalera_smm.so
 18 
 19 [root@zlm2 09:26:38 /usr/local/mysql/lib]
 20 #rpm -qa|grep openssl
 21 openssl-libs-1.0.1e-42.el7.9.x86_64
 22 openssl-devel-1.0.1e-42.el7.9.x86_64
 23 openssl-1.0.1e-42.el7.9.x86_64
 24 
 25 [root@zlm2 09:32:25 /usr/local/mysql/lib]
 26 #cd /usr/lib64
 27 
 28 [root@zlm2 09:34:03 /usr/lib64]
 29 #ls -l|grep libssl
 30 -rwxr-xr-x.  1 root root  255888 Mar  6  2015 libssl3.so
 31 lrwxrwxrwx   1 root root      16 Jul 16  2015 libssl.so -> libssl.so.1.0.1e
 32 lrwxrwxrwx   1 root root      16 Jul 16  2015 libssl.so.10 -> libssl.so.1.0.1e
 33 -rwxr-xr-x   1 root root  449864 Jun 29  2015 libssl.so.1.0.1e
 34 
 35 [root@zlm2 09:41:02 /usr/lib64]
 36 #yum install openssl
 37 ... //Omitted.
 38 
 39 Updated:
 40   openssl.x86_64 1:1.0.2k-12.el7                                                                                                                     
 41 
 42 Dependency Updated:
 43   openssl-devel.x86_64 1:1.0.2k-12.el7                                      openssl-libs.x86_64 1:1.0.2k-12.el7                                     
 44 
 45 Complete!
 46 
 47 [root@zlm2 09:44:39 /usr/lib64]
 48 #tail /data/mysql/mysql3308/data/error.log |grep ERROR
 49 
 50 [root@zlm2 09:45:15 /usr/lib64]
 51 #ps aux|grep mysqld
 52 mysql     6345  2.9 20.1 1556468 204812 pts/0  Sl   09:44   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
 53 root      6390  0.0  0.0 112640   960 pts/0    R+   09:45   0:00 grep --color=auto mysqld
 54 
 55 [root@zlm2 09:46:00 /usr/lib64]
 56 #cat /data/mysql/mysql3308/data/error.log |grep password
 57 2018-08-07T09:00:47.603271+01:00 1 [Note] A temporary password is generated for root@localhost: bY2jmV=k)ltM
 58 
 59 [root@zlm2 09:47:28 /usr/lib64]
 60 #mysql -uroot -p -S /tmp/mysql3308.sock
 61 Enter password: 
 62 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.101' (111)
 63 
 64 [root@zlm2 09:48:11 /usr/lib64]
 65 #ps aux|grep mysqld
 66 mysql     6345  0.6 20.1 1556468 204812 pts/0  Sl   09:44   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
 67 root      6433  0.0  0.0 112640   960 pts/0    R+   09:48   0:00 grep --color=auto mysqld
 68 
 69 [root@zlm2 09:57:21 /usr/lib64]
 70 #mysql -uroot -p -hlocalhost -S /tmp/mysql3308.sock
 71 Enter password: 
 72 Welcome to the MySQL monitor.  Commands end with ; or g.
 73 Your MySQL connection id is 4
 74 Server version: 5.7.22-22-29.26-log
 75 
 76 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 77 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 78 
 79 Oracle is a registered trademark of Oracle Corporation and/or its
 80 affiliates. Other names may be trademarks of their respective
 81 owners.
 82 
 83 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 84 
 85 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to zlm@'192.168.1.%';
 86 ERROR 1133 (42000): Can't find any matching row in the user table
 87 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to zlm@'192.168.1.%' identified by 'zlmzlm';
 88 Query OK, 0 rows affected, 1 warning (0.34 sec)
 89 
 90 (root@localhost mysql3308.sock)[(none)]>grant reload,lock tables,replication client on *.* to sst@localhost identified by 'zlmzlm';
 91 Query OK, 0 rows affected, 2 warnings (0.03 sec)
 92 
 93 (root@localhost mysql3308.sock)[(none)]>select user,host from mysql.user;
 94 +---------------+-------------+
 95 | user          | host        |
 96 +---------------+-------------+
 97 | zlm           | 192.168.1.% |
 98 | mysql.session | localhost   |
 99 | mysql.sys     | localhost   |
100 | root          | localhost   |
101 | sst           | localhost   |
102 +---------------+-------------+
103 5 rows in set (0.00 sec)
104 
105 (root@localhost mysql3308.sock)[(none)]>show global status like '%wsrep%';
106 +----------------------------------+--------------------------------------+
107 | Variable_name                    | Value                                |
108 +----------------------------------+--------------------------------------+
109 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7 |
110 | wsrep_protocol_version           | 8                                    |
111 | wsrep_last_applied               | 2                                    |
112 | wsrep_last_committed             | 2                                    |
113 | wsrep_replicated                 | 2                                    |
114 | wsrep_replicated_bytes           | 432                                  |
115 | wsrep_repl_keys                  | 2                                    |
116 | wsrep_repl_keys_bytes            | 64                                   |
117 | wsrep_repl_data_bytes            | 232                                  |
118 | wsrep_repl_other_bytes           | 0                                    |
119 | wsrep_received                   | 2                                    |
120 | wsrep_received_bytes             | 141                                  |
121 | wsrep_local_commits              | 0                                    |
122 | wsrep_local_cert_failures        | 0                                    |
123 | wsrep_local_replays              | 0                                    |
124 | wsrep_local_send_queue           | 0                                    |
125 | wsrep_local_send_queue_max       | 1                                    |
126 | wsrep_local_send_queue_min       | 0                                    |
127 | wsrep_local_send_queue_avg       | 0.000000                             |
128 | wsrep_local_recv_queue           | 0                                    |
129 | wsrep_local_recv_queue_max       | 2                                    |
130 | wsrep_local_recv_queue_min       | 0                                    |
131 | wsrep_local_recv_queue_avg       | 0.500000                             |
132 | wsrep_local_cached_downto        | 1                                    |
133 | wsrep_flow_control_paused_ns     | 0                                    |
134 | wsrep_flow_control_paused        | 0.000000                             |
135 | wsrep_flow_control_sent          | 0                                    |
136 | wsrep_flow_control_recv          | 0                                    |
137 | wsrep_flow_control_interval      | [ 100, 100 ]                         |
138 | wsrep_flow_control_interval_low  | 100                                  |
139 | wsrep_flow_control_interval_high | 100                                  |
140 | wsrep_flow_control_status        | OFF                                  |
141 | wsrep_cert_deps_distance         | 1.000000                             |
142 | wsrep_apply_oooe                 | 0.000000                             |
143 | wsrep_apply_oool                 | 0.000000                             |
144 | wsrep_apply_window               | 1.000000                             |
145 | wsrep_commit_oooe                | 0.000000                             |
146 | wsrep_commit_oool                | 0.000000                             |
147 | wsrep_commit_window              | 1.000000                             |
148 | wsrep_local_state                | 4                                    |
149 | wsrep_local_state_comment        | Synced                               |
150 | wsrep_cert_index_size            | 1                                    |
151 | wsrep_cert_bucket_count          | 22                                   |
152 | wsrep_gcache_pool_size           | 1832                                 |
153 | wsrep_causal_reads               | 0                                    |
154 | wsrep_cert_interval              | 0.000000                             |
155 | wsrep_ist_receive_status         |                                      |
156 | wsrep_ist_receive_seqno_start    | 0                                    |
157 | wsrep_ist_receive_seqno_current  | 0                                    |
158 | wsrep_ist_receive_seqno_end      | 0                                    |
159 | wsrep_incoming_addresses         | 192.168.1.101:3308                   |
160 | wsrep_desync_count               | 0                                    |
161 | wsrep_evs_delayed                |                                      |
162 | wsrep_evs_evict_list             |                                      |
163 | wsrep_evs_repl_latency           | 0/0/0/0/0                            |
164 | wsrep_evs_state                  | OPERATIONAL                          |
165 | wsrep_gcomm_uuid                 | bd54497d-9a15-11e8-b386-2759969fcd01 |
166 | wsrep_cluster_conf_id            | 1                                    |
167 | wsrep_cluster_size               | 1                                    |
168 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7 |
169 | wsrep_cluster_status             | Primary                              |
170 | wsrep_connected                  | ON                                   |
171 | wsrep_local_bf_aborts            | 0                                    |
172 | wsrep_local_index                | 0                                    |
173 | wsrep_provider_name              | Galera                               |
174 | wsrep_provider_vendor            | Codership Oy <info@codership.com>    |
175 | wsrep_provider_version           | 3.26(r)                              |
176 | wsrep_ready                      | ON                                   |
177 +----------------------------------+--------------------------------------+
178 68 rows in set (0.00 sec)
179 
180 //Check the below global status of PXC.
181 | wsrep_local_state                | 4                                    |
182 | wsrep_local_state_comment        | Synced                               |
183 | wsrep_cluster_status             | Primary                              |
184 | wsrep_connected                  | ON                                   |
185 
186 //It means that the PXC has been worked normally on node zlm2.
187 
188 //The partial error log of PXC which shows the success of starting up of it.
189 2018-08-07T09:44:40.631123+01:00 0 [Note] Event Scheduler: Loaded 0 events
190 2018-08-07T09:44:40.631550+01:00 0 [Note] mysqld: ready for connections.
191 Version: '5.7.22-22-29.26-log'  socket: '/tmp/mysql3308.sock'  port: 3308  Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
192 2018-08-07T09:44:40.635469+01:00 2 [Note] WSREP: Initialized wsrep sidno 2
193 2018-08-07T09:44:40.635501+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 1) (Increment: 1 -> 1)
194 2018-08-07T09:44:40.635510+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
195 2018-08-07T09:44:40.635525+01:00 2 [Note] WSREP: Assign initial position for certification: 0, protocol version: 3
196 2018-08-07T09:44:40.635540+01:00 0 [Note] WSREP: Service thread queue flushed.
197 2018-08-07T09:44:40.635577+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:0
198 2018-08-07T09:44:40.637360+01:00 2 [Note] WSREP: Synchronized with group, ready for connections 
199 2018-08-07T09:44:40.637369+01:00 2 [Note] WSREP: Setting wsrep_ready to true
200 2018-08-07T09:44:40.637372+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
201 
202 //Check the files in datadir.
203 [root@zlm2 10:38:25 /data/mysql/mysql3308/data]
204 #ls -l
205 total 556916
206 -rw-r----- 1 mysql mysql        56 Aug  7 09:00 auto.cnf
207 -rw------- 1 mysql mysql      1680 Aug  7 09:00 ca-key.pem
208 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 ca.pem
209 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 client-cert.pem
210 -rw------- 1 mysql mysql      1680 Aug  7 09:00 client-key.pem
211 -rw-r----- 1 mysql mysql     24663 Aug  7 10:30 error.log
212 -rw-r----- 1 mysql mysql 134219048 Aug  7 10:30 galera.cache
213 -rw-r----- 1 mysql mysql       113 Aug  7 10:30 grastate.dat
214 -rw-r----- 1 mysql mysql       170 Aug  7 10:30 gvwstate.dat
215 -rw-r----- 1 mysql mysql       423 Aug  7 09:00 ib_buffer_pool
216 -rw-r----- 1 mysql mysql 104857600 Aug  7 10:16 ibdata1
217 -rw-r----- 1 mysql mysql 104857600 Aug  7 10:16 ib_logfile0
218 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile1
219 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile2
220 -rw-r----- 1 mysql mysql  12582912 Aug  7 09:44 ibtmp1
221 drwxr-x--- 2 mysql mysql      4096 Aug  7 09:00 mysql
222 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 performance_schema
223 -rw------- 1 mysql mysql      1676 Aug  7 09:00 private_key.pem
224 -rw-r--r-- 1 mysql mysql       452 Aug  7 09:00 public_key.pem
225 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 server-cert.pem
226 -rw------- 1 mysql mysql      1676 Aug  7 09:00 server-key.pem
227 -rw-r----- 1 mysql mysql       454 Aug  7 09:44 slow.log
228 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 sys
229 -rw-r----- 1 mysql mysql   3932160 Aug  7 10:16 xb_doublewrite
230 -rw-r----- 1 mysql mysql         5 Aug  7 09:44 zlm2.pid
231 
232 [root@zlm2 10:38:27 /data/mysql/mysql3308/data]
233 #cat grastate.dat
234 # GALERA saved state
235 version: 2.1
236 uuid:    bd5525ab-9a15-11e8-aa0f-4b830c783fc7
237 seqno:   -1
238 safe_to_bootstrap: 1 //This means the node is the first one who starts the cluster.

 

 

 

Copy the backup set to another server(mine is zlm3) with newly initialized instance of MySQL.

Step 2: Destroy the table.

**Start mysqld of PXC of the remaining nodes.**

 1 [root@zlm2 08:03:12 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 6 root root 4096 Jul 27 07:57 2018-07-27_07-57-43
 5 
 6 [root@zlm2 08:03:15 /data/backup]
 7 #tar cf innobkex_full.tar 2018-07-27_07-57-43/
 8 
 9 [root@zlm2 08:03:42 /data/backup]
10 #ls -l
11 total 187384
12 drwxr-x--- 6 root root      4096 Jul 27 07:57 2018-07-27_07-57-43
13 -rw-r--r-- 1 root root 191877120 Jul 27 08:03 innobkex_full.tar
14 
15 [root@zlm2 08:03:48 /data/backup]
16 #scp innobkex_full.tar zlm3:/data/backup
17 root@zlm3's password: 
18 innobkex_full.tar                                                                                                  100%  183MB  91.5MB/s   00:02    
19 
20 [root@zlm2 08:04:39 /data/backup]
21 #

 

  1 //Let's start the PXC on node zlm3.
  2 [root@zlm3 10:00:12 /data/mysql/mysql3308/data]
  3 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
  4 [1] 4181
  5 
  6 [root@zlm3 10:30:43 /data/mysql/mysql3308/data]
  7 #ps aux|grep mysqld
  8 mysql     4181 20.0 16.9 756016 172580 pts/0   Sl   10:30   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
  9 root      4596  0.0  0.0 112640   960 pts/0    R+   10:30   0:00 grep --color=auto mysqld
 10 
 11 [root@zlm3 10:31:10 /data/mysql/mysql3308/data]
 12 #tail -1000 error.log 
 13 2018-08-07T09:54:08.825870+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 14 2018-08-07T09:54:08.825960+01:00 0 [Warning] WSREP: Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks
 15 2018-08-07T09:54:08.836945+01:00 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
 16  100
 17  100
 18  100
 19  100
 20 2018-08-07T09:54:27.784997+01:00 0 [Warning] InnoDB: New log files created, LSN=45790
 21 2018-08-07T09:54:28.565405+01:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 22 2018-08-07T09:54:29.043051+01:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1ca6f9ae-9a17-11e8-9a7c-080027de0e0e.
 23 2018-08-07T09:54:29.054082+01:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 24 2018-08-07T09:54:29.471412+01:00 0 [Warning] CA certificate ca.pem is self signed.
 25 2018-08-07T09:54:29.536187+01:00 1 [Note] A temporary password is generated for root@localhost: ,M3=why-ok&d
 26 2018-08-07T09:55:01.556746+01:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
 27 2018-08-07T09:55:01.556802+01:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
 28 2018-08-07T09:55:01.556812+01:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
 29 2018-08-07T09:55:01.556830+01:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
 30 2018-08-07T09:55:01.556837+01:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
 31 2018-08-07T09:55:01.556847+01:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
 32 2018-08-07T09:55:01.556920+01:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
 33 2018-08-07T09:55:01.556931+01:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
 34 
 35 //The starting mysqld operation begins here.
 36 2018-08-07T10:30:43.794296+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 37 2018-08-07T10:30:43.801975+01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
 38 2018-08-07T10:30:43.802040+01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 4181 ...
 39 2018-08-07T10:30:43.805420+01:00 0 [Note] WSREP: Setting wsrep_ready to false //Not ready.
 40 2018-08-07T10:30:43.805476+01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
 41 2018-08-07T10:30:43.805486+01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
 42 2018-08-07T10:30:43.829026+01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
 43 2018-08-07T10:30:43.829070+01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
 44 2018-08-07T10:30:43.829385+01:00 0 [Warning] WSREP: Could not open state file for reading: '/data/mysql/mysql3308/data//grastate.dat'
 45 2018-08-07T10:30:43.829395+01:00 0 [Warning] WSREP: No persistent state found. Bootstraping with default state
 46 2018-08-07T10:30:43.829432+01:00 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1
 47 2018-08-07T10:30:43.837823+01:00 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/mysql3308/data/; base_host = 192.168.1.102; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /data/mysql/mysql3308/data/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/mysql3308/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 8; socket.checksum = 2; socket.recv_buf_size = 212992; 
 48 2018-08-07T10:30:43.861834+01:00 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1
 49 2018-08-07T10:30:43.866886+01:00 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
 50 2018-08-07T10:30:43.866919+01:00 0 [Note] WSREP: Preparing to initiate SST/IST //Begin to SST or IST.
 51 2018-08-07T10:30:43.866922+01:00 0 [Note] WSREP: Starting replication
 52 2018-08-07T10:30:43.866936+01:00 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
 53 2018-08-07T10:30:43.867102+01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
 54 2018-08-07T10:30:43.867166+01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
 55 2018-08-07T10:30:43.869814+01:00 0 [Warning] WSREP: Fail to access the file (/data/mysql/mysql3308/data//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
 56 2018-08-07T10:30:43.869842+01:00 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
 57 2018-08-07T10:30:43.870450+01:00 0 [Note] WSREP: GMCast version 0
 58 2018-08-07T10:30:43.870721+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
 59 2018-08-07T10:30:43.870729+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
 60 2018-08-07T10:30:43.871017+01:00 0 [Note] WSREP: EVS version 0
 61 2018-08-07T10:30:43.871191+01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.103:'
 62 2018-08-07T10:30:43.873170+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection established to 2cf33746 tcp://192.168.1.102:4567
 63 2018-08-07T10:30:43.873199+01:00 0 [Warning] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.102:4567' points to own listening address, blacklisting
 64 2018-08-07T10:30:43.873754+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection established to bd54497d tcp://192.168.1.101:4567
 65 2018-08-07T10:30:43.873806+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 66 2018-08-07T10:30:44.373051+01:00 0 [Note] WSREP: declaring bd54497d at tcp://192.168.1.101:4567 stable
 67 2018-08-07T10:30:44.373698+01:00 0 [Note] WSREP: Node bd54497d state primary
 68 2018-08-07T10:30:44.374205+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 69 view (view_id(PRIM,2cf33746,2)
 70 memb {
 71     2cf33746,0 //This is node zlm2,first one started the cluster.
 72     bd54497d,0 //This is node zlm3.
 73     }
 74 joined {
 75     }
 76 left {
 77     }
 78 partitioned {
 79     }
 80 )
 81 2018-08-07T10:30:44.374227+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 82 2018-08-07T10:30:44.374354+01:00 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.0.103:4567
 83 2018-08-07T10:30:44.374361+01:00 0 [Note] WSREP: discarding pending addr proto entry 0x378db50
 84 2018-08-07T10:30:44.872036+01:00 0 [Note] WSREP: gcomm: connected
 85 2018-08-07T10:30:44.872107+01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
 86 2018-08-07T10:30:44.872173+01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
 87 2018-08-07T10:30:44.872327+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2
 88 2018-08-07T10:30:44.872840+01:00 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5
 89 2018-08-07T10:30:44.873577+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5
 90 2018-08-07T10:30:44.874140+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5 from 0 (zlm3)
 91 2018-08-07T10:30:44.874158+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5 from 1 (zlm2)
 92 2018-08-07T10:30:44.874169+01:00 0 [Note] WSREP: Quorum results:
 93     version    = 4,
 94     component  = PRIMARY,
 95     conf_id    = 1,
 96     members    = 1/2 (primary/total),
 97     act_id     = 18,
 98     last_appl. = -1,
 99     protocols  = 0/8/3 (gcs/repl/appl),
100     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
101 2018-08-07T10:30:44.874173+01:00 0 [Note] WSREP: Flow-control interval: [141, 141] //The scope of fc interval.
102 2018-08-07T10:30:44.874176+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
103 2018-08-07T10:30:44.874179+01:00 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 18)
104 2018-08-07T10:30:44.874224+01:00 2 [Note] WSREP: State transfer required: 
105     Group state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18
106     Local state: 00000000-0000-0000-0000-000000000000:-1
107 2018-08-07T10:30:44.874231+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
108 2018-08-07T10:30:44.874237+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18, view# 2: Primary, number of nodes: 2, my index: 0, protocol version 3
109 2018-08-07T10:30:44.874241+01:00 2 [Note] WSREP: Setting wsrep_ready to true
110 2018-08-07T10:30:44.874243+01:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
111 2018-08-07T10:30:44.874245+01:00 2 [Note] WSREP: Setting wsrep_ready to false
112 2018-08-07T10:30:44.874315+01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
113 which: no socat in (/usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin)
114     2018-08-07T08:30:45.523175Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
115     2018-08-07T08:30:45.524742Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
116     2018-08-07T08:30:45.525848Z WSREP_SST: [ERROR] ****************************************************** 
117 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
118     Read: '(null)'
119 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
120 2018-08-07T10:30:45.526201+01:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
121 2018-08-07T10:30:45.526208+01:00 2 [ERROR] Aborting
122 
123 2018-08-07T10:30:45.526211+01:00 2 [Note] Giving 2 client threads a chance to die gracefully
124 2018-08-07T10:30:47.373430+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting off
125 2018-08-07T10:30:47.526391+01:00 2 [Note] WSREP: Closing send monitor...
126 2018-08-07T10:30:47.526431+01:00 2 [Note] WSREP: Closed send monitor.
127 2018-08-07T10:30:47.526455+01:00 2 [Note] WSREP: gcomm: terminating thread
128 2018-08-07T10:30:47.526465+01:00 2 [Note] WSREP: gcomm: joining thread
129 2018-08-07T10:30:47.526479+01:00 1 [Note] WSREP: rollbacker thread exiting
130 2018-08-07T10:30:47.526675+01:00 2 [Note] WSREP: gcomm: closing backend
131 2018-08-07T10:30:50.874663+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection to peer bd54497d with addr tcp://192.168.1.101:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
132 2018-08-07T10:30:50.874839+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.101:4567 
133 2018-08-07T10:30:51.877925+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') reconnecting to bd54497d (tcp://192.168.1.101:4567), attempt 0
134 2018-08-07T10:30:53.026867+01:00 2 [Note] WSREP: declaring node with index 1 suspected, timeout PT5S (evs.suspect_timeout)
135 2018-08-07T10:30:53.027032+01:00 2 [Note] WSREP: evs::proto(2cf33746, LEAVING, view_id(REG,2cf33746,2)) suspecting node: bd54497d
136 2018-08-07T10:30:53.027042+01:00 2 [Note] WSREP: evs::proto(2cf33746, LEAVING, view_id(REG,2cf33746,2)) suspected node without join message, declaring inactive
137 2018-08-07T10:30:53.027063+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
138 view (view_id(NON_PRIM,2cf33746,2)
139 memb {
140     2cf33746,0 //Only remains the first node zlm2.
141     }
142 joined {
143     }
144 left {
145     }
146 partitioned {
147     bd54497d,0 //Node zlm3 became a suspected node and be moved here.
148     }
149 )
150 2018-08-07T10:30:53.027092+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
151 view ((empty))
152 2018-08-07T10:30:53.027370+01:00 2 [Note] WSREP: gcomm: closed
153 2018-08-07T10:30:53.027421+01:00 0 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
154 2018-08-07T10:30:53.027435+01:00 0 [Note] WSREP: Flow-control interval: [100, 100]
155 2018-08-07T10:30:53.027438+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
156 2018-08-07T10:30:53.027443+01:00 0 [Note] WSREP: Received NON-PRIMARY.
157 2018-08-07T10:30:53.027447+01:00 0 [Note] WSREP: Shifting PRIMARY -> OPEN (TO: 18)
158 2018-08-07T10:30:53.027458+01:00 0 [Note] WSREP: Received self-leave message.
159 2018-08-07T10:30:53.027463+01:00 0 [Note] WSREP: Flow-control interval: [0, 0]
160 2018-08-07T10:30:53.027466+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
161 2018-08-07T10:30:53.027469+01:00 0 [Note] WSREP: Received SELF-LEAVE. Closing connection.
162 2018-08-07T10:30:53.027472+01:00 0 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 18)
163 2018-08-07T10:30:53.027477+01:00 0 [Note] WSREP: RECV thread exiting 0: Success
164 2018-08-07T10:30:53.027719+01:00 2 [Note] WSREP: recv_thread() joined.
165 2018-08-07T10:30:53.027732+01:00 2 [Note] WSREP: Closing replication queue.
166 2018-08-07T10:30:53.027738+01:00 2 [Note] WSREP: Closing slave action queue.
167 2018-08-07T10:30:53.027796+01:00 0 [Note] WSREP: Waiting for active wsrep applier to exit
168 2018-08-07T10:30:53.027804+01:00 0 [Note] WSREP: Service disconnected.
169 2018-08-07T10:30:53.027807+01:00 0 [Note] WSREP: Waiting to close threads......
170 2018-08-07T10:30:58.028048+01:00 0 [Note] WSREP: Some threads may fail to exit.
171 2018-08-07T10:30:58.028088+01:00 0 [Note] Binlog end
172 2018-08-07T10:30:58.028341+01:00 0 [Note] mysqld: Shutdown complete
173 
174 [root@zlm3 10:35:04 /data/mysql/mysql3308/data]
175 #ps aux|grep mysqld
176 root      4672  0.0  0.0 112640   960 pts/0    R+   10:35   0:00 grep --color=auto mysqld
177 
178 //Let's see the detail of ERROR message.
179 [root@zlm3 10:35:06 /data/mysql/mysql3308/data]
180 #cat error.log |grep ERROR
181     2018-08-07T08:30:45.523175Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
182     2018-08-07T08:30:45.524742Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
183     2018-08-07T08:30:45.525848Z WSREP_SST: [ERROR] ****************************************************** 
184 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
185 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
186 2018-08-07T10:30:45.526201+01:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
187 2018-08-07T10:30:45.526208+01:00 2 [ERROR] Aborting
188 
189 //It says that we are absent of "socat" package.
190 
191 [root@zlm3 10:43:15 /data/mysql/mysql3308/data]
192 #yum install socat
193 ... //Omitted.
194 
195 Warning: RPMDB altered outside of yum.
196   Installing : socat-1.7.3.2-2.el7.x86_64                                                                                                        1/1 
197   Verifying  : socat-1.7.3.2-2.el7.x86_64                                                                                                        1/1 
198 
199 Installed:
200   socat.x86_64 0:1.7.3.2-2.el7                                                                                                                       
201 
202 Complete!
203 
204 //Start the PXC on zlm2 again.
205 [root@zlm3 10:35:58 /data/mysql/mysql3308/data]
206 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
207 [1] 4725
208 
209 [root@zlm3 10:53:02 /data/mysql/mysql3308/data]
210 #tail -f error.log 
211 2018-08-07T10:53:15.116919+01:00 0 [Note] WSREP: Member 0.0 (zlm3) requested state transfer from '*any*'. Selected 1.0 (zlm2)(SYNCED) as donor.
212 2018-08-07T10:53:15.117004+01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 18)
213 2018-08-07T10:53:15.117035+01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 1
214 2018-08-07T10:53:15.117051+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18
215 2018-08-07T10:53:15.696885+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -2 (No such file or directory)
216 2018-08-07T10:53:15.696918+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
217 2018-08-07T10:53:15.696940+01:00 0 [Note] WSREP: gcomm: terminating thread
218 2018-08-07T10:53:15.696949+01:00 0 [Note] WSREP: gcomm: joining thread
219 2018-08-07T10:53:15.697076+01:00 0 [Note] WSREP: gcomm: closing backend
220 2018-08-07T10:53:17.026257+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') turning message relay requesting off
221 2018-08-07T10:53:19.026327+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') connection to peer bd54497d with addr tcp://192.168.1.101:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
222 2018-08-07T10:53:19.026394+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.101:4567 
223 2018-08-07T10:53:20.086590+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') reconnecting to bd54497d (tcp://192.168.1.101:4567), attempt 0
224 2018-08-07T10:53:20.786605+01:00 0 [Note] WSREP: declaring node with index 1 suspected, timeout PT5S (evs.suspect_timeout)
225 2018-08-07T10:53:20.786778+01:00 0 [Note] WSREP: evs::proto(5167e9c7, LEAVING, view_id(REG,5167e9c7,4)) suspecting node: bd54497d
226 2018-08-07T10:53:20.786826+01:00 0 [Note] WSREP: evs::proto(5167e9c7, LEAVING, view_id(REG,5167e9c7,4)) suspected node without join message, declaring inactive
227 2018-08-07T10:53:20.786941+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
228 view (view_id(NON_PRIM,5167e9c7,4)
229 memb {
230     5167e9c7,0
231     }
232 joined {
233     }
234 left {
235     }
236 partitioned {
237     bd54497d,0
238     }
239 )
240 2018-08-07T10:53:20.787061+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
241 view ((empty))
242 2018-08-07T10:53:20.787324+01:00 0 [Note] WSREP: gcomm: closed
243 2018-08-07T10:53:20.787362+01:00 0 [Note] WSREP: mysqld: Terminated.
244 ^C
245 
246 [root@zlm3 10:53:48 /data/mysql/mysql3308/data]
247 #cat error.log |grep ERROR
248 2018-08-07T10:53:15.696918+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
249     
250 [root@zlm3 11:13:14 /data/mysql/mysql3308/data]
251 #ps aux|grep mysqld
252 root      6785  0.0  0.0 112640   960 pts/0    R+   11:20   0:00 grep --color=auto mysqld
253 [1]+  Aborted                 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
254 
255 [root@zlm3 11:20:16 /data/mysql/mysql3308/data]
256 #cat error.log |grep ERROR
257 2018-08-07T11:13:15.619315+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
258     2018-08-07T09:13:45.246252Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
259     2018-08-07T09:13:45.247507Z WSREP_SST: [ERROR] Error while getting data from donor node:  exit codes: 255 0
260     2018-08-07T09:13:45.254423Z WSREP_SST: [ERROR] ****************************************************** 
261     2018-08-07T09:13:45.258735Z WSREP_SST: [ERROR] Cleanup after exit with status:32
262     2018-08-07T09:14:10.137993Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
263     2018-08-07T09:14:10.139033Z WSREP_SST: [ERROR] Possible timeout in receving first data from donor in gtid/keyring stage
264     2018-08-07T09:14:10.140912Z WSREP_SST: [ERROR] ****************************************************** 
265     2018-08-07T09:14:10.141951Z WSREP_SST: [ERROR] Cleanup after exit with status:32
266     
267 //There's still one error which cause the mysqld process cannot be started.

 

Check target table on master.

 

Prepare to restore the table "sbtest7".

 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 | sbtest5            |
10 | sbtest6            |
11 +--------------------+
12 6 rows in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest6;
15 +----------+
16 | count(*) |
17 +----------+
18 |        0 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(1,1,'a','b');
23 Query OK, 1 row affected (0.00 sec)
24 
25 zlm@192.168.56.100:3306 [sysbench]>select * from sbtest6;
26 +----+---+---+-----+
27 | id | k | c | pad |
28 +----+---+---+-----+
29 |  1 | 1 | a | b   |
30 +----+---+---+-----+
31 1 row in set (0.00 sec)

Start mysqld of PXC on node zlm4.

 1 [root@zlm3 08:05:13 /data/backup]
 2 #ls -l
 3 total 187380
 4 -rw-r--r-- 1 root root 191877120 Jul 27 08:04 innobkex_full.tar
 5 
 6 [root@zlm3 08:05:15 /data/backup]
 7 #tar xf innobkex_full.tar 
 8 
 9 [root@zlm3 08:05:20 /data/backup]
10 #ls -l
11 total 187384
12 drwxr-x--- 6 root root      4096 Jul 27 07:57 2018-07-27_07-57-43
13 -rw-r--r-- 1 root root 191877120 Jul 27 08:04 innobkex_full.tar
14 
15 [root@zlm3 08:06:06 /data/backup]
16 #cd 2018-07-27_07-57-43/
17 
18 [root@zlm3 08:06:22 /data/backup/2018-07-27_07-57-43]
19 #ls -l
20 total 102464
21 -rw-r----- 1 root root       495 Jul 27 07:57 backup-my.cnf
22 -rw-r----- 1 root root      8988 Jul 27 07:57 ib_buffer_pool
23 -rw-r----- 1 root root 104857600 Jul 27 07:57 ibdata1
24 drwxr-x--- 2 root root      4096 Jul 27 07:57 mysql
25 drwxr-x--- 2 root root      8192 Jul 27 07:57 performance_schema
26 drwxr-x--- 2 root root      8192 Jul 27 07:57 sys
27 drwxr-x--- 2 root root      4096 Jul 27 07:57 sysbench
28 -rw-r----- 1 root root        71 Jul 27 07:57 xtrabackup_binlog_info
29 -rw-r----- 1 root root       121 Jul 27 07:57 xtrabackup_checkpoints
30 -rw-r----- 1 root root       587 Jul 27 07:57 xtrabackup_info
31 -rw-r----- 1 root root      2560 Jul 27 07:57 xtrabackup_logfile
32 
33 [root@zlm3 08:06:23 /data/backup/2018-07-27_07-57-43]
34 #cd sysbench/
35 
36 [root@zlm3 08:06:25 /data/backup/2018-07-27_07-57-43/sysbench]
37 #ls -l
38 total 71768
39 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
40 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest1.frm
41 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest1.ibd
42 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest2.frm
43 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest2.ibd
44 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest3.frm
45 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest3.ibd
46 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest4.frm
47 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest4.ibd
48 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest5.frm
49 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest5.ibd
50 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest6.frm
51 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest6.ibd
52 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest7.frm
53 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest7.ibd
54 
55 [root@zlm3 08:06:27 /data/backup/2018-07-27_07-57-43/sysbench]
56 #mv sbtest7* ../
57 
58 [root@zlm3 08:06:38 /data/backup/2018-07-27_07-57-43/sysbench]
59 #ls -l
60 total 61516
61 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
62 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest1.frm
63 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest1.ibd
64 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest2.frm
65 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest2.ibd
66 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest3.frm
67 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest3.ibd
68 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest4.frm
69 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest4.ibd
70 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest5.frm
71 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest5.ibd
72 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest6.frm
73 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest6.ibd
74 
75 [root@zlm3 08:06:39 /data/backup/2018-07-27_07-57-43/sysbench]
76 #rm -f sbtest* //Delete all those irrelevant tables which I'm not going to rescue.It can extremly reduce the content I need.
77 
78 [root@zlm3 08:06:45 /data/backup/2018-07-27_07-57-43/sysbench]
79 #mv ../sbtest* .
80 
81 [root@zlm3 08:06:57 /data/backup/2018-07-27_07-57-43/sysbench]
82 #ls -l
83 total 10256
84 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
85 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest7.frm
86 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest7.ibd

 

 1 [root@zlm4 11:27:18 /data/mysql/mysql3308/data]
 2 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf initialize
 3 
 4 [root@zlm4 11:27:37 /data/mysql/mysql3308/data]
 5 #ls -l
 6 total 131092
 7 -rw-r----- 1 mysql mysql     13275 Aug  7 11:27 error.log
 8 -rw-r----- 1 mysql mysql 134219048 Aug  7 11:27 galera.cache
 9 -rw-r----- 1 mysql mysql         0 Aug  7 11:27 grastate.dat
10 
11 [root@zlm4 11:27:48 /data/mysql/mysql3308/data]
12 #cat error.log |grep ERROR
13     2018-08-07T09:27:24.770212Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
14     2018-08-07T09:27:24.771275Z WSREP_SST: [ERROR] xtrabackup not in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/root/perl5/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
15     2018-08-07T09:27:24.771951Z WSREP_SST: [ERROR] ****************************************************** 
16 2018-08-07T11:27:24.772254+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: 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 '4363' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
17 2018-08-07T11:27:24.772279+01:00 0 [ERROR] WSREP: Process completed with error: 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 '4363' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
18 2018-08-07T11:27:24.772317+01:00 1 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
19 2018-08-07T11:27:24.772322+01:00 1 [ERROR] Aborting
20 
21 [root@zlm4 11:28:39 /data/mysql/mysql3308/data]
22 #innobackupex --help
23 -bash: innobackupex: command not found
24 
25 //It's due to absence of Xtrabackup tools on node zlm4.

 

Generate Xtrabackup backup set.

 

Apply the redo logfile.

 1 [root@zlm1 16:32:14 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd /data/backup
 3 180729 16:32:20 innobackupex: Error: extra argument found -pPassw0rd
 4 180729 16:32:20 innobackupex: Error: extra argument found /data/backup
 5 
 6 [root@zlm1 16:32:20 ~]
 7 #innobackupex -v
 8 innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
 9 
10 [root@zlm1 16:32:26 ~]
11 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
12 180729 16:32:33 innobackupex: Starting the backup operation
13 ...
14 
15 180729 16:32:53 Backup created in directory '/data/backup/2018-07-29_16-32-33'
16 MySQL binlog position: filename 'mysql-bin.000100', position '476', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694'
17 180729 16:32:53 [00] Writing backup-my.cnf
18 180729 16:32:53 [00]        ...done
19 180729 16:32:53 [00] Writing xtrabackup_info
20 180729 16:32:53 [00]        ...done
21 xtrabackup: Transaction log of lsn (1719676169) to (1719676178) was copied.
22 180729 16:32:53 completed OK!

 

 1 [root@zlm3 08:06:58 /data/backup/2018-07-27_07-57-43/sysbench]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-27_07-57-43/
 3 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 4 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 5 180727 08:08:41 innobackupex: Starting the apply-log operation
 6 
 7 IMPORTANT: Please check that the apply-log run completes successfully.
 8            At the end of a successful apply-log run innobackupex
 9            prints "completed OK!".
10 
11 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
12 xtrabackup: cd to /data/backup/2018-07-27_07-57-43/
13 
14 ... //Omitted.
15 
16 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
17 InnoDB: New log files created, LSN=10141352841
18 InnoDB: Highest supported file format is Barracuda.
19 InnoDB: Log scan progressed past the checkpoint lsn 10141352972
20 InnoDB: Doing recovery: scanned up to log sequence number 10141352981 (0%)
21 InnoDB: Database was not shutdown normally!
22 InnoDB: Starting crash recovery.
23 InnoDB: xtrabackup: Last MySQL binlog file position 954219, file name mysql-bin.000035
24 InnoDB: Removed temporary tablespace data file: "ibtmp1"
25 InnoDB: Creating shared tablespace for temporary tables
26 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
27 InnoDB: File './ibtmp1' size is now 12 MB.
28 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
29 InnoDB: 32 non-redo rollback segment(s) are active.
30 InnoDB: Waiting for purge to start
31 InnoDB: 5.7.19 started; log sequence number 10141352981
32 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
33 InnoDB: page_cleaner: 1000ms intended loop took 18267ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
34 InnoDB: FTS optimize thread exiting.
35 InnoDB: Starting shutdown...
36 InnoDB: Shutdown completed; log sequence number 10141353000
37 180727 08:09:03 completed OK!
38 
39 [root@zlm3 08:09:03 /data/backup/2018-07-27_07-57-43/sysbench]
40 #cd ..
41 
42 [root@zlm3 08:11:14 /data/backup/2018-07-27_07-57-43]
43 #ls -l
44 total 430144
45 -rw-r----- 1 root root       495 Jul 27 07:57 backup-my.cnf
46 -rw-r----- 1 root root      8988 Jul 27 07:57 ib_buffer_pool
47 -rw-r----- 1 root root 104857600 Jul 27 08:09 ibdata1
48 -rw-r----- 1 root root 104857600 Jul 27 08:09 ib_logfile0
49 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile1
50 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile2
51 -rw-r----- 1 root root  12582912 Jul 27 08:09 ibtmp1
52 drwxr-x--- 2 root root      4096 Jul 27 07:57 mysql
53 drwxr-x--- 2 root root      8192 Jul 27 07:57 performance_schema
54 drwxr-x--- 2 root root      8192 Jul 27 07:57 sys
55 drwxr-x--- 2 root root        55 Jul 27 08:06 sysbench
56 -rw-r----- 1 root root        71 Jul 27 07:57 xtrabackup_binlog_info
57 -rw-r--r-- 1 root root        24 Jul 27 08:08 xtrabackup_binlog_pos_innodb
58 -rw-r----- 1 root root       121 Jul 27 08:08 xtrabackup_checkpoints
59 -rw-r----- 1 root root       587 Jul 27 07:57 xtrabackup_info
60 -rw-r----- 1 root root   8388608 Jul 27 08:08 xtrabackup_logfile
61 -rw-r--r-- 1 root root         1 Jul 27 08:08 xtrabackup_master_key_id
62 
63 [root@zlm3 08:11:15 /data/backup/2018-07-27_07-57-43]
64 #cat xtrabackup_binlog_info
65 mysql-bin.000035    954887    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220
66 
67 [root@zlm3 08:11:21 /data/backup/2018-07-27_07-57-43]
68 #cat xtrabackup_binlog_pos_innodb
69 mysql-bin.000035    954219
70 
71 //The result of "show master status;" on zlm2.
72 (zlm@192.168.1.101 3306)[(none)]>show master status;
73 +------------------+----------+--------------+------------------+------------------------------------------------+
74 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
75 +------------------+----------+--------------+------------------+------------------------------------------------+
76 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 | //The newest GTID is "uuid:1-3730222"
77 +------------------+----------+--------------+------------------+------------------------------------------------+
78 1 row in set (0.00 sec)

 

 

Insert one record of "sbtest6" and drop it.

Shutdown the instance on zlm3 and copy back the datafiles

 1 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(2,2,'c','d');
 2 Query OK, 1 row affected (0.00 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>show master status;
 5 +------------------+----------+--------------+------------------+-------------------------------------------------+
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 7 +------------------+----------+--------------+------------------+-------------------------------------------------+
 8 | mysql-bin.000100 |      758 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695 |
 9 +------------------+----------+--------------+------------------+-------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>drop tables sbtest6;
13 Query OK, 0 rows affected (0.02 sec)
14 
15 zlm@192.168.56.100:3306 [sysbench]>show master status;
16 +------------------+----------+--------------+------------------+-------------------------------------------------+
17 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
18 +------------------+----------+--------------+------------------+-------------------------------------------------+
19 | mysql-bin.000100 |      951 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
20 +------------------+----------+--------------+------------------+-------------------------------------------------+
21 1 row in set (0.00 sec)
 1 [root@zlm3 08:11:28 /data/backup/2018-07-27_07-57-43]
 2 #ps aux|grep mysqld
 3 mysql     5079  0.0 20.2 1110576 205684 pts/1  Sl   07:35   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 4 root      5341  0.0  0.0 112640   956 pts/2    R+   08:16   0:00 grep --color=auto mysqld
 5 
 6 [root@zlm3 08:16:38 /data/backup/2018-07-27_07-57-43]
 7 #mysqladmin shutdown
 8 
 9 [root@zlm3 08:16:43 /data/backup/2018-07-27_07-57-43]
10 #ps aux|grep mysqld
11 root      5352  0.0  0.0 112640   960 pts/2    R+   08:16   0:00 grep --color=auto mysqld
12 
13 [root@zlm3 08:16:45 /data/backup/2018-07-27_07-57-43]
14 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
15 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
16 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
17 180727 08:17:34 innobackupex: Starting the copy-back operation
18 
19 IMPORTANT: Please check that the copy-back run completes successfully.
20            At the end of a successful copy-back run innobackupex
21            prints "completed OK!".
22 
23 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
24 Original data directory /data/mysql/mysql3306/data is not empty! //The data directory need to be cleaned.
25 
26 [root@zlm3 08:17:34 /data/backup/2018-07-27_07-57-43]
27 #cd /data/mysql/mysql3306/data
28 
29 [root@zlm3 08:18:09 /data/mysql/mysql3306/data]
30 #ls -l
31 total 409700
32 -rw-r----- 1 mysql mysql        56 Jul 27 07:35 auto.cnf
33 -rw-r----- 1 mysql mysql     27980 Jul 27 08:16 error.log
34 -rw-r----- 1 mysql mysql       904 Jul 27 08:16 ib_buffer_pool
35 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ibdata1
36 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ib_logfile0
37 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile1
38 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile2
39 drwxr-x--- 2 mysql mysql      4096 Jul 27 07:34 mysql
40 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 performance_schema
41 -rw-r----- 1 mysql mysql       276 Jul 27 07:59 relay-bin.000004
42 -rw-r----- 1 mysql mysql      2562 Jul 27 08:16 relay-bin.000005
43 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_applier.000001
44 -rw-r----- 1 mysql mysql        45 Jul 27 07:35 relay-bin-group_replication_applier.index
45 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_recovery.000001
46 -rw-r----- 1 mysql mysql        46 Jul 27 07:35 relay-bin-group_replication_recovery.index
47 -rw-r----- 1 mysql mysql        38 Jul 27 07:59 relay-bin.index
48 -rw-r----- 1 mysql mysql       167 Jul 27 07:35 slow.log
49 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 sys
50 drwxr-x--- 2 mysql mysql        55 Jul 27 07:34 sysbench
51 -rw-r----- 1 mysql mysql       584 Jul 27 07:34 xtrabackup_info
52 
53 [root@zlm3 08:18:18 /data/mysql/mysql3306/data]
54 #rm -rf *
55 
56 [root@zlm3 08:18:19 /data/mysql/mysql3306/data]
57 #ls -l
58 total 0
59 
60 [root@zlm3 08:18:22 /data/mysql/mysql3306/data]
61 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
62 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
63 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
64 180727 08:18:53 innobackupex: Starting the copy-back operation
65 
66 IMPORTANT: Please check that the copy-back run completes successfully.
67            At the end of a successful copy-back run innobackupex
68            prints "completed OK!".
69 
70 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
71 180727 08:18:53 [01] Copying ib_logfile0 to /data/mysql/mysql3306/data/ib_logfile0
72 180727 08:18:54 [01]        ...done
73 180727 08:18:55 [01] Copying ib_logfile1 to /data/mysql/mysql3306/data/ib_logfile1
74 180727 08:18:55 [01]        ...done
75 180727 08:18:59 [01] Copying ib_logfile2 to /data/mysql/mysql3306/data/ib_logfile2
76 180727 08:19:00 [01]        ...done
77 180727 08:19:04 [01] Copying ibdata1 to /data/mysql/mysql3306/data/ibdata1
78 180727 08:19:06 [01]        ...done
79 
80 ... //Omitted.
81 
82 180727 08:19:13 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
83 180727 08:19:13 [01]        ...done
84 180727 08:19:13 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
85 180727 08:19:13 [01]        ...done
86 180727 08:19:13 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
87 180727 08:19:13 [01]        ...done
88 180727 08:19:13 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
89 180727 08:19:13 [01]        ...done
90 180727 08:19:13 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
91 180727 08:19:13 [01]        ...done
92 180727 08:19:14 completed OK!

 

 

Shutdown the mysqld on master(mimic the MySQL instance down).

Start the instance and check the table "sbtest7".

 1 [root@zlm1 16:56:54 /data/backup]
 2 #mysqladmin shutdown
 3 
 4 [root@zlm1 16:57:05 /data/backup]
 5 #ps aux|grep mysqld
 6 mysql     3804  0.0 20.7 1072396 210620 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
 7 root      4512  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm1 16:57:10 /data/backup]
10 #ps aux|grep mysqld
11 mysql     3804  0.0 20.1 1038828 204780 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
12 root      4518  0.0  0.0 112640   956 pts/2    R+   16:57   0:00 grep --color=auto mysqld
13 
14 [root@zlm1 16:57:15 /data/backup]
15 #mysqladmin shutdown
16 mysqladmin: connect to server at '192.168.56.100' failed
17 error: 'Can't connect to MySQL server on '192.168.56.100' (111)'
18 Check that mysqld is running on 192.168.56.100 and that the port is 3306.
19 You can check this by doing 'telnet 192.168.56.100 3306'
20 
21 [root@zlm1 16:57:23 /data/backup]
22 #ps aux|grep mysqld
23 root      4529  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
 1 [root@zlm3 08:21:05 /data/mysql/mysql3306/data]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 08:21:22 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 root      5416  0.0  0.0 112640   960 pts/2    R+   08:21   0:00 grep --color=auto mysqld
 7 
 8 [root@zlm3 08:21:26 /data/mysql/mysql3306/data]
 9 #tail -f error.log 
10 2018-07-27T08:21:22.902146+01:00 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
11 2018-07-27T08:21:22.902161+01:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
12 2018-07-27T08:21:23.503758+01:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
13 2018-07-27T08:21:23.503784+01:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
14 2018-07-27T08:21:23.503792+01:00 0 [ERROR] Failed to initialize builtin plugins.
15 2018-07-27T08:21:23.503796+01:00 0 [ERROR] Aborting
16 
17 2018-07-27T08:21:23.503801+01:00 0 [Note] Binlog end
18 2018-07-27T08:21:23.504149+01:00 0 [Note] mysqld: Shutdown complete
19 
20 ^C
21 
22 [root@zlm3 08:21:33 /data/mysql/mysql3306/data]
23 #chown -R mysql.mysql *
24 
25 [root@zlm3 08:22:05 /data/mysql/mysql3306/data]
26 #ls -l
27 total 421944
28 -rw-r----- 1 mysql mysql      1910 Jul 27 08:21 error.log
29 -rw-r----- 1 mysql mysql      8988 Jul 27 08:19 ib_buffer_pool
30 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:19 ibdata1
31 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:18 ib_logfile0
32 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:18 ib_logfile1
33 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:19 ib_logfile2
34 -rw-r----- 1 mysql mysql  12582912 Jul 27 08:19 ibtmp1
35 -rw-r----- 1 mysql mysql         0 Jul 27 08:21 innodb_status.5398
36 drwxr-x--- 2 mysql mysql      4096 Jul 27 08:19 mysql
37 drwxr-x--- 2 mysql mysql      8192 Jul 27 08:19 performance_schema
38 drwxr-x--- 2 mysql mysql      8192 Jul 27 08:19 sys
39 drwxr-x--- 2 mysql mysql        55 Jul 27 08:19 sysbench
40 -rw-r----- 1 mysql mysql        24 Jul 27 08:19 xtrabackup_binlog_pos_innodb
41 -rw-r----- 1 mysql mysql       587 Jul 27 08:19 xtrabackup_info
42 -rw-r----- 1 mysql mysql         1 Jul 27 08:19 xtrabackup_master_key_id
43 
44 [root@zlm3 08:22:12 /data/mysql/mysql3306/data]
45 #sh /root/mysqld.sh
46 
47 [root@zlm3 08:22:25 /data/mysql/mysql3306/data]
48 #ps aux|grep mysqld
49 mysql     5437  3.0 17.7 1110004 180944 pts/2  Sl   08:22   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
50 root      5470  0.0  0.0 112640   956 pts/2    R+   08:22   0:00 grep --color=auto mysqld
51 
52 (zlm@192.168.1.102 3306)[(none)]>show databases;
53 +--------------------+
54 | Database           |
55 +--------------------+
56 | information_schema |
57 | mysql              |
58 | performance_schema |
59 | sys                |
60 | sysbench           |
61 +--------------------+
62 5 rows in set (0.01 sec)
63 
64 (zlm@192.168.1.102 3306)[(none)]>use sysbench
65 Reading table information for completion of table and column names
66 You can turn off this feature to get a quicker startup with -A
67 
68 Database changed
69 (zlm@192.168.1.102 3306)[sysbench]>show tables;
70 +--------------------+
71 | Tables_in_sysbench |
72 +--------------------+
73 | sbtest7            |
74 +--------------------+
75 1 row in set (0.00 sec)
76 
77 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
78 +----------+
79 | count(*) |
80 +----------+
81 |    10000 |
82 +----------+
83 1 row in set (0.00 sec)
84 
85 //Compare the output of server zlm2.
86 (zlm@192.168.1.101 3306)[sysbench]>show tables;
87 +--------------------+
88 | Tables_in_sysbench |
89 +--------------------+
90 | sbtest1            |
91 | sbtest2            |
92 | sbtest3            |
93 | sbtest4            |
94 | sbtest5            |
95 | sbtest6            |
96 +--------------------+
97 7 rows in set (0.00 sec)

 

 

Copy backup set to zlm2.

Step 3: Implement a slave with replication filter option.

 1 [root@zlm1 16:32:53 ~]
 2 #cd /data/backup/
 3 
 4 [root@zlm1 16:37:19 /data/backup]
 5 #ls -l
 6 total 4
 7 drwxr-x--- 7 root root 4096 Jul 29 16:32 2018-07-29_16-32-33
 8 
 9 [root@zlm1 16:37:24 /data/backup]
10 #scp -r 2018-07-29_16-32-33 zlm2:/data/backup
11 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
12 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02
13 ... //Omitted.

 

 

Execute a "change master to ... " on zlm3.

*Step 3: Rescue data.**

 1 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 2 Empty set (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show master status;
 5 +------------------+----------+--------------+------------------+------------------------------------------------+
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
 7 +------------------+----------+--------------+------------------+------------------------------------------------+
 8 | mysql-bin.000003 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
 9 +------------------+----------+--------------+------------------+------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[sysbench]>change master to 
13     -> master_host='192.168.1.101',
14     -> master_port=3306,
15     -> master_user='repl',
16     -> master_password='repl4slave',
17     -> master_auto_position=1;
18 Query OK, 0 rows affected, 2 warnings (0.05 sec)

 

 

Start slave IO_Thread.

Prepare to restore the backup with "--apply-log" on zlm2.

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave io_thread;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000001
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: 
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
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: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 776
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: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222 //The newest transaction has been retrieved.
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
 1 [root@zlm2 16:38:09 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 7 root root 4096 Jul 29 16:37 2018-07-29_16-32-33
 5 
 6 [root@zlm2 16:38:12 /data/backup]
 7 #innobackupex -v
 8 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
 9 innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
10 
11 [root@zlm2 16:39:13 /data/backup]
12 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-29_16-32-33/
13 ... //Omitted.
14 
15 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
16 InnoDB: page_cleaner: 1000ms intended loop took 8812ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
17 InnoDB: FTS optimize thread exiting.
18 InnoDB: Starting shutdown...
19 InnoDB: Shutdown completed; log sequence number 1719676456
20 180729 16:39:39 completed OK!

 

 

Change replication filter "replicate_do_table" option.

Shutdown instance and copy back.

 1 (zlm@192.168.1.102 3306)[sysbench]>change replication filter replicate_do_table=(sysbench.sbteset7);
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000001
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: 
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: sysbench.sbteset7
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 776
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: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
 1 [root@zlm2 16:39:39 /data/backup]
 2 #ps aux|grep mysqld
 3 mysql     3771  0.0 18.7 1071816 190784 pts/0  Sl   15:52   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
 4 root      4451  0.0  0.0 112640   956 pts/1    R+   16:43   0:00 grep --color=auto mysqld
 5 
 6 [root@zlm2 16:43:10 /data/backup]
 7 #mysqladmin shutdown
 8 
 9 [root@zlm2 16:43:15 /data/backup]
10 #ps aux|grep mysqld
11 root      4463  0.0  0.0 112640   956 pts/1    R+   16:43   0:00 grep --color=auto mysqld
12 
13 [root@zlm2 16:43:18 /data/backup]
14 #cd /data/mysql/mysql3306/data/
15 
16 [root@zlm2 16:43:36 /data/mysql/mysql3306/data]
17 #rm -rf *
18 
19 [root@zlm2 16:43:39 /data/mysql/mysql3306/data]
20 #cd ../logs
21 
22 [root@zlm2 16:43:50 /data/mysql/mysql3306/logs]
23 #rm -rf *
24 
25 [root@zlm2 16:43:53 /data/mysql/mysql3306/logs]
26 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-29_16-32-33/
27 ... //Omitted.

 

 

Analyze the binlog file on zlm2 to get the GTID before dropping operation.

Restart instance and check table.

 1 [root@zlm2 08:35:06 ~]
 2 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.
 3 mysql-bin.000011  mysql-bin.000015  mysql-bin.000019  mysql-bin.000023  mysql-bin.000027  mysql-bin.000031  mysql-bin.000035  
 4 mysql-bin.000012  mysql-bin.000016  mysql-bin.000020  mysql-bin.000024  mysql-bin.000028  mysql-bin.000032  mysql-bin.000036  
 5 mysql-bin.000013  mysql-bin.000017  mysql-bin.000021  mysql-bin.000025  mysql-bin.000029  mysql-bin.000033  mysql-bin.index   
 6 mysql-bin.000014  mysql-bin.000018  mysql-bin.000022  mysql-bin.000026  mysql-bin.000030  mysql-bin.000034  
 7 
 8 [root@zlm2 08:35:06 ~]
 9 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000036 > 36.log
10 
11 [root@zlm2 08:35:43 ~]
12 #tail -20 36.log
13 ###   @2=5039
14 ###   @3='32005756334-91141508143-50354766020-63389724388-71947328293-08845712532-56754824208-47077542818-55826128113-56514840054'
15 ###   @4='22255110541-96175917050-58709546750-68026622026-27581577227'
16 # at 2311
17 #180727  7:59:57 server id 1013306  end_log_pos 2338     Xid = 232
18 COMMIT/*!*/;
19 # at 2338
20 #180727  8:01:50 server id 1013306  end_log_pos 2399     GTID    last_committed=1    sequence_number=2    rbr_only=no
21 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222'/*!*/; //Here's the GTID we need.
22 # at 2399
23 #180727  8:01:50 server id 1013306  end_log_pos 2523     Query    thread_id=21    exec_time=0    error_code=0
24 use `sysbench`/*!*/;
25 SET TIMESTAMP=1532671310/*!*/;
26 DROP TABLE `sbtest7` /* generated by server */
27 /*!*/;
28 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
29 DELIMITER ;
30 # End of log file
31 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
32 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 1 [root@zlm2 16:44:41 /data/mysql/mysql3306/logs]
 2 #cd ../data
 3 
 4 [root@zlm2 16:47:40 /data/mysql/mysql3306/data]
 5 #chown -R mysql.mysql *
 6 
 7 [root@zlm2 16:47:49 /data/mysql/mysql3306/data]
 8 #sh /root/mysqld.sh
 9 
10 [root@zlm2 16:47:56 /data/mysql/mysql3306/data]
11 #ps aux|grep mysqld
12 mysql     4514  3.2 17.9 1071804 182316 pts/1  Sl   16:47   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
13 root      4547  0.0  0.0 112640   960 pts/1    R+   16:48   0:00 grep --color=auto mysqld
14 
15 [root@zlm2 16:48:03 /data/mysql/mysql3306/data]
16 #mysql
17 Welcome to the MySQL monitor.  Commands end with ; or g.
18 Your MySQL connection id is 2
19 Server version: 5.7.21-log MySQL Community Server (GPL)
20 
21 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
22 
23 Oracle is a registered trademark of Oracle Corporation and/or its
24 affiliates. Other names may be trademarks of their respective
25 owners.
26 
27 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
28 
29 zlm@192.168.56.101:3306 [(none)]>use sysbench
30 Reading table information for completion of table and column names
31 You can turn off this feature to get a quicker startup with -A
32 
33 Database changed
34 zlm@192.168.56.101:3306 [sysbench]>show tables;
35 +--------------------+
36 | Tables_in_sysbench |
37 +--------------------+
38 | sbtest1            |
39 | sbtest2            |
40 | sbtest3            |
41 | sbtest4            |
42 | sbtest5            |
43 | sbtest6            |
44 +--------------------+
45 6 rows in set (0.00 sec)
46 
47 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
48 +----+---+---+-----+
49 | id | k | c | pad |
50 +----+---+---+-----+
51 |  1 | 1 | a | b   |
52 +----+---+---+-----+
53 1 row in set (0.00 sec)

 

 

Start slave SQL_Thread with until_option clause.

*Step 4: **Implement a slave with binlog server.***

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave sql_thread until sql_before_gtids='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000002
14                 Relay_Log_Pos: 398
15         Relay_Master_Log_File: mysql-bin.000036
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: sysbench.sbteset7
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 2338
28               Relay_Log_Space: 776
29               Until_Condition: SQL_BEFORE_GTIDS
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: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
64 
65 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
66 +----------+
67 | count(*) |
68 +----------+
69 |    10000 |
70 +----------+
71 1 row in set (0.00 sec)
72 
73 //Why the amount of record isn't 9990?

 

 

Start mysqld on binlog server.

Try to stop and start the slave again.

 1 [root@zlm3 16:50:00 /data/mysql/mysql3306/logs]
 2 #ps aux|grep mysqld
 3 root      4405  0.0  0.0 112640   960 pts/0    R+   16:50   0:00 grep --color=auto mysqld
 4 
 5 [root@zlm3 16:50:08 /data/mysql/mysql3306/logs]
 6 #sh /root/mysqld.sh
 7 
 8 [root@zlm3 16:50:13 /data/mysql/mysql3306/logs]
 9 #ps aux|grep mysqld
10 mysql     4411 16.5 15.6 498232 159408 pts/0   Rl   16:50   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
11 root      4429  0.0  0.0 112640   960 pts/0    R+   16:50   0:00 grep --color=auto mysqld
12 
13 [root@zlm3 16:51:03 /data/mysql/mysql3306/logs]
14 #ls -l
15 total 11216
16 -rw-r----- 1 root  root      4128 Jul 29 08:42 mysql-bin.000095
17 -rw-r----- 1 root  root       241 Jul 29 08:42 mysql-bin.000096
18 -rw-r----- 1 root  root  11461562 Jul 29 09:07 mysql-bin.000097
19 -rw-r----- 1 mysql mysql      154 Jul 29 16:50 mysql-bin.000098
20 -rw-r----- 1 mysql mysql       44 Jul 29 16:50 mysql-bin.index
21 
22 [root@zlm3 16:51:04 /data/mysql/mysql3306/logs]
23 #cat mysql-bin.index
24 /data/mysql/mysql3306/logs/mysql-bin.000098
 1 (zlm@192.168.1.102 3306)[sysbench]>stop slave;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>start slave;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 8 *************************** 1. row ***************************
 9                Slave_IO_State: Waiting for master to send event
10                   Master_Host: 192.168.1.101
11                   Master_User: repl
12                   Master_Port: 3306
13                 Connect_Retry: 60
14               Master_Log_File: mysql-bin.000036
15           Read_Master_Log_Pos: 2523
16                Relay_Log_File: relay-bin.000003
17                 Relay_Log_Pos: 438
18         Relay_Master_Log_File: mysql-bin.000036
19              Slave_IO_Running: Yes
20             Slave_SQL_Running: Yes
21               Replicate_Do_DB: 
22           Replicate_Ignore_DB: 
23            Replicate_Do_Table: sysbench.sbteset7
24        Replicate_Ignore_Table: 
25       Replicate_Wild_Do_Table: 
26   Replicate_Wild_Ignore_Table: 
27                    Last_Errno: 0
28                    Last_Error: 
29                  Skip_Counter: 0
30           Exec_Master_Log_Pos: 2523
31               Relay_Log_Space: 1064
32               Until_Condition: None
33                Until_Log_File: 
34                 Until_Log_Pos: 0
35            Master_SSL_Allowed: No
36            Master_SSL_CA_File: 
37            Master_SSL_CA_Path: 
38               Master_SSL_Cert: 
39             Master_SSL_Cipher: 
40                Master_SSL_Key: 
41         Seconds_Behind_Master: 0
42 Master_SSL_Verify_Server_Cert: No
43                 Last_IO_Errno: 0
44                 Last_IO_Error: 
45                Last_SQL_Errno: 0
46                Last_SQL_Error: 
47   Replicate_Ignore_Server_Ids: 
48              Master_Server_Id: 1013306
49                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
50              Master_Info_File: mysql.slave_master_info
51                     SQL_Delay: 0
52           SQL_Remaining_Delay: NULL
53       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
54            Master_Retry_Count: 86400
55                   Master_Bind: 
56       Last_IO_Error_Timestamp: 
57      Last_SQL_Error_Timestamp: 
58                Master_SSL_Crl: 
59            Master_SSL_Crlpath: 
60            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
61             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222
62                 Auto_Position: 1
63          Replicate_Rewrite_DB: 
64                  Channel_Name: 
65            Master_TLS_Version: 
66 1 row in set (0.00 sec)
67 
68 (zlm@192.168.1.102 3306)[sysbench]>show tables;
69 +--------------------+
70 | Tables_in_sysbench |
71 +--------------------+
72 | sbtest7            |
73 +--------------------+
74 1 row in set (0.00 sec)
75 
76 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
77 +----------+
78 | count(*) |
79 +----------+
80 |    10000 |
81 +----------+
82 1 row in set (0.01 sec)
83 
84 //Even though it has executed the newest GTID "uuid:3730222",the amount of record is unchanged.

 

 

Disguise the binlog server is a fake master.

Try to analyze the binlog on slave zlm3.

 1 [root@zlm3 16:52:59 /data/mysql/mysql3306/logs]
 2 #rm -f mysql-bin.00009*
 3 [1]+  Done                    mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098  (wd: /data)
 4 (wd now: /data/mysql/mysql3306/logs)
 5 
 6 [root@zlm3 17:00:06 /data/mysql/mysql3306/logs]
 7 #ls -l
 8 total 4
 9 -rw-r----- 1 mysql mysql 44 Jul 29 16:50 mysql-bin.index
10 
11 [root@zlm3 17:00:27 /data/mysql/mysql3306/logs]
12 #ps aux|grep mysqlbinlog
13 root      4475  0.0  0.0 112640   960 pts/0    R+   17:00   0:00 grep --color=auto mysqlbinlog
14 
15 [root@zlm3 17:00:38 /data/mysql/mysql3306/logs]
16 #cp /data/mysql-bin* .
17 
18 [root@zlm3 17:02:48 /data/mysql/mysql3306/logs]
19 #ls -l
20 -rw-r----- 1 root  root  410 Jul 29 17:01 mysql-bin.000098
21 -rw-r----- 1 root  root  241 Jul 29 17:01 mysql-bin.000099
22 -rw-r----- 1 root  root  951 Jul 29 17:01 mysql-bin.000100
23 -rw-r----- 1 root  root  177 Jul 29 17:03 mysql-bin.index
24 
25 [root@zlm3 17:02:54 /data/mysql/mysql3306/logs]
26 #ls -1 | awk '{print i$0}' i=`pwd`'/'|grep mysql-bin.000 > mysql-bin.index
27 
28 [root@zlm3 17:03:06 /data/mysql/mysql3306/logs]
29 #cat mysql-bin.index 
30 /data/mysql/mysql3306/logs/mysql-bin.000098
31 /data/mysql/mysql3306/logs/mysql-bin.000099
32 /data/mysql/mysql3306/logs/mysql-bin.000100
33 
34 [root@zlm3 17:16:14 /data/mysql/mysql3306/logs]
35 #chown mysql.mysql *
36 
37 [root@zlm3 17:17:23 /data/mysql/mysql3306/logs]
38 #ls -l
39 total 16
40 -rw-r----- 1 mysql mysql 410 Jul 29 17:01 mysql-bin.000098
41 -rw-r----- 1 mysql mysql 241 Jul 29 17:01 mysql-bin.000099
42 -rw-r----- 1 mysql mysql 951 Jul 29 17:01 mysql-bin.000100
43 -rw-r----- 1 mysql mysql 177 Jul 29 17:03 mysql-bin.index
 1 [root@zlm3 09:06:44 /data/mysql/mysql3306/data]
 2 #cd ../logs
 3 
 4 [root@zlm3 09:06:46 /data/mysql/mysql3306/logs]
 5 #ls -l
 6 total 16
 7 -rw-r----- 1 mysql mysql  169 Jul 27 07:32 mysql-bin.000001
 8 -rw-r----- 1 mysql mysql 1473 Jul 27 08:16 mysql-bin.000002
 9 -rw-r----- 1 mysql mysql  396 Jul 27 09:02 mysql-bin.000003
10 -rw-r----- 1 mysql mysql  132 Jul 27 08:22 mysql-bin.index
11 
12 [root@zlm3 09:06:47 /data/mysql/mysql3306/logs]
13 #mysqlbinlog -v --base64-output=decode-rows mysql-bin.000003 > 3.log
14 
15 [root@zlm3 09:07:26 /data/mysql/mysql3306/logs]
16 #cat 3.log
17 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
18 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
19 DELIMITER /*!*/;
20 # at 4
21 #180727  8:22:26 server id 1023306  end_log_pos 123     Start: binlog v 4, server v 5.7.21-log created 180727  8:22:26 at startup
22 # Warning: this binlog is either in use or was not closed properly.
23 ROLLBACK/*!*/; //It shows that transactions in this binlog file have been rolled back!!!
24 # at 123
25 #180727  8:22:26 server id 1023306  end_log_pos 190     Previous-GTIDs
26 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730215-3730221 //The rolled back GTID is from 3730215 to 3730221.
27 # at 190
28 #180727  8:01:50 server id 1013306  end_log_pos 251     GTID    last_committed=0    sequence_number=1    rbr_only=no
29 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222'/*!*/;
30 # at 251
31 #180727  8:01:50 server id 1013306  end_log_pos 323     Query    thread_id=21    exec_time=3665    error_code=0
32 SET TIMESTAMP=1532671310/*!*/;
33 SET @@session.pseudo_thread_id=21/*!*/;
34 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
35 SET @@session.sql_mode=1436549152/*!*/;
36 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
37 /*!C utf8 *//*!*/;
38 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
39 SET @@session.lc_time_names=0/*!*/;
40 SET @@session.collation_database=DEFAULT/*!*/;
41 BEGIN
42 /*!*/;
43 # at 323
44 #180727  8:01:50 server id 1013306  end_log_pos 396     Query    thread_id=21    exec_time=3665    error_code=0
45 SET TIMESTAMP=1532671310/*!*/;
46 COMMIT
47 /*!*/;
48 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
49 DELIMITER ;
50 # End of log file
51 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
52 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

 

Change the server-uuid of binlog server to be equal with master.

    Okay,I didn't get my supposed consequences in the above test this time.In my opinion,the miss dropped table "sbtest7" should have been recovered to the state of "9990" instead of "10000" what really astonished me.

 1 [root@zlm3 18:01:50 /data/mysql/mysql3306]
 2 #cd ../data
 3 
 4 [root@zlm3 18:01:53 /data/mysql/mysql3306/data]
 5 #cat auto.cnf
 6 [auto]
 7 server-uuid=e00ef9f5-6c4b-11e8-8389-080027de0e0e
 8 
 9 [root@zlm3 18:04:11 /data/mysql/mysql3306/data]
10 #vim auto.cnf
11 
12 [root@zlm3 18:04:26 /data/mysql/mysql3306/data]
13 #cat auto.cnf
14 [auto]
15 server-uuid=2a4b3562-2ab6-11e8-be7a-080027de0e0e

    The transaction of deleting 10 records befor dropping "sbtest7" ,together with those other transactions in mysql-bin.000035,was rolled back.Even if the slave had executed the newest GTID "uuid:3720222",the incremtental modifications on the table couldn't be rescued.

 

    I guess the result is due to that the redo log files in backup set didn't contain the transactions information,thus,it chose to roll back the transactions.

Execute "change master to ... " on zlm2.

 

1 zlm@192.168.56.101:3306 [sysbench]>change master to 
2     -> master_host='192.168.56.102',
3     -> master_port=3306,
4     -> master_user='repl',
5     -> master_password='repl4slave',
6     -> master_auto_position=1;
7 Query OK, 0 rows affected, 2 warnings (0.01 sec)

 

Start IO_Thread on zlm2.

 1 [root@zlm2 17:12:39 /data/backup/2018-07-29_16-32-33]
 2 #cat xtrabackup_binlog_info
 3 mysql-bin.000100    476    2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 4 
 5 zlm@192.168.56.101:3306 [sysbench]>reset master;
 6 Query OK, 0 rows affected (0.00 sec)
 7 
 8 zlm@192.168.56.101:3306 [sysbench]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
 9 Query OK, 0 rows affected (0.00 sec)
10 
11 zlm@192.168.56.101:3306 [sysbench]>start slave io_thread;
12 Query OK, 0 rows affected (0.00 sec)
13 
14 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
15 *************************** 1. row ***************************
16                Slave_IO_State: 
17                   Master_Host: 192.168.56.102
18                   Master_User: repl
19                   Master_Port: 3306
20                 Connect_Retry: 60
21               Master_Log_File: 
22           Read_Master_Log_Pos: 4
23                Relay_Log_File: relay-bin.000001
24                 Relay_Log_Pos: 4
25         Relay_Master_Log_File: 
26              Slave_IO_Running: No
27             Slave_SQL_Running: No
28               Replicate_Do_DB: 
29           Replicate_Ignore_DB: 
30            Replicate_Do_Table: 
31        Replicate_Ignore_Table: 
32       Replicate_Wild_Do_Table: 
33   Replicate_Wild_Ignore_Table: 
34                    Last_Errno: 0
35                    Last_Error: 
36                  Skip_Counter: 0
37           Exec_Master_Log_Pos: 0
38               Relay_Log_Space: 154
39               Until_Condition: None
40                Until_Log_File: 
41                 Until_Log_Pos: 0
42            Master_SSL_Allowed: No
43            Master_SSL_CA_File: 
44            Master_SSL_CA_Path: 
45               Master_SSL_Cert: 
46             Master_SSL_Cipher: 
47                Master_SSL_Key: 
48         Seconds_Behind_Master: NULL
49 Master_SSL_Verify_Server_Cert: No
50                 Last_IO_Errno: 1236
51                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
52                Last_SQL_Errno: 0
53                Last_SQL_Error: 
54   Replicate_Ignore_Server_Ids: 
55              Master_Server_Id: 1023306
56                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
57              Master_Info_File: /data/mysql/mysql3306/data/master.info
58                     SQL_Delay: 0
59           SQL_Remaining_Delay: NULL
60       Slave_SQL_Running_State: 
61            Master_Retry_Count: 86400
62                   Master_Bind: 
63       Last_IO_Error_Timestamp: 180729 18:00:08
64      Last_SQL_Error_Timestamp: 
65                Master_SSL_Crl: 
66            Master_SSL_Crlpath: 
67            Retrieved_Gtid_Set: 
68             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
69                 Auto_Position: 1
70          Replicate_Rewrite_DB: 
71                  Channel_Name: 
72            Master_TLS_Version: 
73 1 row in set (0.00 sec)

 

    I was stucked here.Slave IO_Thread could not be started with 1236,although I had replaced the server-uuid and set the variable of gtid_purged.Only if the slave get binlog which contains the incremental data and start the slave SQL_Thread.We can get back our dropped table together with the modification on it.

 

Supplemented on July 31:

 

    The previous fault of 1236 was due to the wrong configuration of variable "gtid_purged".There were two individual gtids generated by the other masters with different uuid as bellow:

 

金沙棋牌app手机下载 1

 

 

    Therefore,the configuration command should be executed like this:

 

1 set @@global.gtid_purged='27af30ca-6800-11e8-ad7e-080027de0e0e:1,
2 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694,
3 e00ef9f5-6c4b-11e8-8389-080027de0e0e:1';

 

    not merely set it like this:

 

1 set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';

 

    Eventually,In order to make the gtid more clear,I executed "reset master" on binlog server(zlm3) and then copyed those necessary binlogs which were transfered from original master.Further more,I changed back the uuid of zlm3.Because I found that there's no need to change it at all.

    The new procedure of demonstration was shown below:

 

  1 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
  2 Query OK, 0 rows affected (0.00 sec)
  3 
  4 zlm@192.168.56.101:3306 [(none)]>change master to master_host='192.168.56.102',master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1;
  5 Query OK, 0 rows affected, 2 warnings (0.01 sec)
  6 
  7 zlm@192.168.56.101:3306 [(none)]>start slave io_thread;
  8 Query OK, 0 rows affected (0.00 sec)
  9 
 10 zlm@192.168.56.101:3306 [(none)]>show slave statusG
 11 *************************** 1. row ***************************
 12                Slave_IO_State: Waiting for master to send event
 13                   Master_Host: 192.168.56.102
 14                   Master_User: repl
 15                   Master_Port: 3306
 16                 Connect_Retry: 60
 17               Master_Log_File: mysql-bin.000105
 18           Read_Master_Log_Pos: 194
 19                Relay_Log_File: relay-bin.000001
 20                 Relay_Log_Pos: 4
 21         Relay_Master_Log_File: 
 22              Slave_IO_Running: Yes
 23             Slave_SQL_Running: No
 24               Replicate_Do_DB: 
 25           Replicate_Ignore_DB: 
 26            Replicate_Do_Table: 
 27        Replicate_Ignore_Table: 
 28       Replicate_Wild_Do_Table: 
 29   Replicate_Wild_Ignore_Table: 
 30                    Last_Errno: 0
 31                    Last_Error: 
 32                  Skip_Counter: 0
 33           Exec_Master_Log_Pos: 0
 34               Relay_Log_Space: 3360
 35               Until_Condition: None
 36                Until_Log_File: 
 37                 Until_Log_Pos: 0
 38            Master_SSL_Allowed: No
 39            Master_SSL_CA_File: 
 40            Master_SSL_CA_Path: 
 41               Master_SSL_Cert: 
 42             Master_SSL_Cipher: 
 43                Master_SSL_Key: 
 44         Seconds_Behind_Master: NULL
 45 Master_SSL_Verify_Server_Cert: No
 46                 Last_IO_Errno: 0
 47                 Last_IO_Error: 
 48                Last_SQL_Errno: 0
 49                Last_SQL_Error: 
 50   Replicate_Ignore_Server_Ids: 
 51              Master_Server_Id: 1023306
 52                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e //I changed back the uuid of zlm3 to its previous value.
 53              Master_Info_File: /data/mysql/mysql3306/data/master.info
 54                     SQL_Delay: 0
 55           SQL_Remaining_Delay: NULL
 56       Slave_SQL_Running_State: 
 57            Master_Retry_Count: 86400
 58                   Master_Bind: 
 59       Last_IO_Error_Timestamp: 
 60      Last_SQL_Error_Timestamp: 
 61                Master_SSL_Crl: 
 62            Master_SSL_Crlpath: 
 63            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696 //New gtid was received.
 64             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 65                 Auto_Position: 1
 66          Replicate_Rewrite_DB: 
 67                  Channel_Name: 
 68            Master_TLS_Version: 
 69 1 row in set (0.00 sec)
 70 
 71 zlm@192.168.56.101:3306 [sysbench]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715696';
 72 Query OK, 0 rows affected (0.00 sec)
 73 
 74 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
 75 *************************** 1. row ***************************
 76                Slave_IO_State: Waiting for master to send event
 77                   Master_Host: 192.168.56.102
 78                   Master_User: repl
 79                   Master_Port: 3306
 80                 Connect_Retry: 60
 81               Master_Log_File: mysql-bin.000105
 82           Read_Master_Log_Pos: 194
 83                Relay_Log_File: relay-bin.000002
 84                 Relay_Log_Pos: 696
 85         Relay_Master_Log_File: mysql-bin.000100
 86              Slave_IO_Running: Yes
 87             Slave_SQL_Running: No
 88               Replicate_Do_DB: 
 89           Replicate_Ignore_DB: 
 90            Replicate_Do_Table: 
 91        Replicate_Ignore_Table: 
 92       Replicate_Wild_Do_Table: 
 93   Replicate_Wild_Ignore_Table: 
 94                    Last_Errno: 0
 95                    Last_Error: 
 96                  Skip_Counter: 0
 97           Exec_Master_Log_Pos: 758
 98               Relay_Log_Space: 3360
 99               Until_Condition: SQL_BEFORE_GTIDS
100                Until_Log_File: 
101                 Until_Log_Pos: 0
102            Master_SSL_Allowed: No
103            Master_SSL_CA_File: 
104            Master_SSL_CA_Path: 
105               Master_SSL_Cert: 
106             Master_SSL_Cipher: 
107                Master_SSL_Key: 
108         Seconds_Behind_Master: NULL
109 Master_SSL_Verify_Server_Cert: No
110                 Last_IO_Errno: 0
111                 Last_IO_Error: 
112                Last_SQL_Errno: 0
113                Last_SQL_Error: 
114   Replicate_Ignore_Server_Ids: 
115              Master_Server_Id: 1023306
116                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
117              Master_Info_File: /data/mysql/mysql3306/data/master.info
118                     SQL_Delay: 0
119           SQL_Remaining_Delay: NULL
120       Slave_SQL_Running_State: 
121            Master_Retry_Count: 86400
122                   Master_Bind: 
123       Last_IO_Error_Timestamp: 
124      Last_SQL_Error_Timestamp: 
125                Master_SSL_Crl: 
126            Master_SSL_Crlpath: 
127            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696
128             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695
129                 Auto_Position: 1
130          Replicate_Rewrite_DB: 
131                  Channel_Name: 
132            Master_TLS_Version: 
133 1 row in set (0.00 sec)
134 
135 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
136 +----+---+---+-----+
137 | id | k | c | pad |
138 +----+---+---+-----+
139 |  1 | 1 | a | b   |
140 |  2 | 2 | c | d   |
141 +----+---+---+-----+
142 2 rows in set (0.00 sec)
143 
144 //The incremental data in "sbtest6" had been come again.It was okay this time.

  

Summary

  • Binlog server act as a master in this experiment.It can prevent data loss such as miss opeartion of dropping tables whenever the master crashes.
  • We can implement as many binlog servers as possible to continuously acquire the binlogs on different masters.
  • If the only purpose is to get back the dropping data,replication filter of "replicate_do_table" is not necessary.
  • The modification of "sever-uuid" is also neglectable operation.Each gtid contains a differten uuid is completely acceptable.It won't influence our replication except for a little bit complex in output.

 

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:基于Xtrabackup备份集来恢复某个误删除的表,恢复

关键词:

上一篇:没有了

下一篇:没有了