基于mysqldump备份集来恢复某个误操作的表,MySQL备份恢复之mysqldump数据库

 

 

 

Preface

Preface

Preface

 

 

 

    In my previous two blogs,we have known
about the tool of backing up MySQL db.I’m gonna use another tool named
“MySQL Data Dumper” to do some test,here we go.

    The day before yesterday,there’s a
motif about the lock procedure when backing up MySQL using mysqldump or
Xtrabackup in “Ask Ye” which is like a kind of Q&A originated by Mr.
Ye.In my essay today,I’m gonna use
the tool mysqlsump to do some tests then observe procedures of backing
up MySQL database,here we go.

    How to rescue a dropped or truncated
table online?
Dropping or truncating
is ddl operation which cannot be flashed back by the populare flashback
tools like
MyFlash,binlog2mysql,mysqldump_backup,etc.Therefore,the conventional method is
restoring the database to a newly initialized instance on another server
with backup(physical or logical).Whatif the backup set is rather huge
for example the mysqldump backup is more than 200G?It will cost a long
time to rescue the dropped table back.Is there an effective way to
accomplish the issue?Let’s see the tests below.

 

 

 

Introduce

Introduction

Framework

 

 

 

    There’s a third-party tool called mydumper
which is similar with MySQL official backup tool mysqldump and
identically backs up logically.The latest version of  mydumper
is 0.9.3,Here’s the website link:**

    mysqldump is a protogenic logical
backup tool of MySQL.As soon as you’ve installed MySQL server,it can be
used in command line directly.

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

 

    mysqldump logically dumps data in the
target database then gernerate a sql file which can be used to restore
the the very database later.it’s rather convenient to backup a single or
multiple databases online(innodb only) but the side-effect is
that it can hold lock when backing up.

 

  1 ###Download & Install mydumper.###
  2 [root@zlm1 16:10:55 ~]
  3 #wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
  4 --2018-06-17 16:13:27--  https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
  5 Resolving launchpad.net (launchpad.net)... 91.189.89.223, 91.189.89.222
  6 Connecting to launchpad.net (launchpad.net)|91.189.89.223|:443... connected.
  7 HTTP request sent, awaiting response... 303 See Other
  8 Location: https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz [following]
  9 --2018-06-17 16:13:38--  https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz
 10 Resolving launchpadlibrarian.net (launchpadlibrarian.net)... 91.189.89.229, 91.189.89.228
 11 Connecting to launchpadlibrarian.net (launchpadlibrarian.net)|91.189.89.229|:443... connected.
 12 HTTP request sent, awaiting response... 200 OK
 13 Length: 44463 (43K) [application/x-tar]
 14 Saving to: ‘mydumper-0.9.1.tar.gz’
 15 
 16 100%[===========================================================================================================>] 44,463      67.6KB/s   in 0.6s   
 17 
 18 2018-06-17 16:13:50 (67.6 KB/s) - ‘mydumper-0.9.1.tar.gz’ saved [44463/44463]
 19 
 20 
 21 [root@zlm1 16:14:40 ~]
 22 #ls -l
 23 total 99860
 24 -rw-------.  1 root root     1431 Jul 16  2015 anaconda-ks.cfg
 25 -rw-r--r--   1 root root    44463 Nov  6  2015 mydumper-0.9.1.tar.gz
 26 drwxrwxrwx   6 root root     4096 Jun  8 17:31 mysqlbinlog_flashback-master
 27 -rwxr-xr-x   1 root root       58 Jun  4 16:01 mysqld.sh
 28 -rwxr-xr-x   1 root root       40 Jun  1 16:13 mysql.sh
 29 -rw-r--r--   1 root root  7839980 Jul 21  2016 percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
 30 drwxrwxrwx   6 root root     4096 Jun  8 16:59 pip-10.0.1
 31 -rwxr-xr-x   1 root root  4720640 Jun  8 16:57 pip-10.0.1.tar
 32 drwxr-xr-x   6 root root     4096 Jun  8 17:25 PyMySQL-0.8.1
 33 -rwxr-xr-x   1 root root   358400 Jun  8 17:08 PyMySQL-0.8.1.tar
 34 drwxrwxr-x  18 root root     4096 Jun  8 16:38 Python-2.6.9
 35 -rwxr-xr-x   1 root root 59238400 Jun  8 16:28 Python-2.6.9.tar
 36 -rw-r--r--   1 root root      181 Jun  1 20:18 recover.sh
 37 -rw-r--r--   1 root root        0 May 30 20:33 rename_tb.sql
 38 drwxrwxrwx   9 root root     4096 Jun  8 17:22 setuptools-39.2.0
 39 
 40 [root@zlm1 16:15:06 ~]
 41 #gzip -d mydumper-0.9.1.tar.gz 
 42 
 43 [root@zlm1 16:15:26 ~]
 44 #tar -xf mydumper-0.9.1.tar
 45 
 46 [root@zlm1 16:16:41 ~]
 47 #cd mydumper-0.9.1
 48 
 49 [root@zlm1 16:16:47 ~/mydumper-0.9.1]
 50 #ls -l
 51 total 168
 52 -rw-r--r-- 1 root root  8171 Nov  6  2015 binlog.c
 53 -rw-r--r-- 1 root root  1244 Nov  6  2015 binlog.h
 54 drwxr-xr-x 3 root root    20 Jun 17 16:15 cmake
 55 -rw-r--r-- 1 root root  2339 Nov  6  2015 CMakeLists.txt
 56 -rw-r--r-- 1 root root  2081 Nov  6  2015 common.h
 57 -rw-r--r-- 1 root root   101 Nov  6  2015 config.h.in
 58 drwxr-xr-x 4 root root  4096 Jun 17 16:15 docs
 59 -rw-r--r-- 1 root root  3614 Nov  6  2015 g_unix_signal.c
 60 -rw-r--r-- 1 root root   339 Nov  6  2015 g_unix_signal.h
 61 -rw-r--r-- 1 root root 92941 Nov  6  2015 mydumper.c
 62 -rw-r--r-- 1 root root  2169 Nov  6  2015 mydumper.h
 63 -rw-r--r-- 1 root root 16630 Nov  6  2015 myloader.c
 64 -rw-r--r-- 1 root root  1284 Nov  6  2015 myloader.h
 65 -rw-r--r-- 1 root root  2244 Nov  6  2015 README
 66 -rw-r--r-- 1 root root  2021 Nov  6  2015 server_detect.c
 67 -rw-r--r-- 1 root root  1116 Nov  6  2015 server_detect.h
 68 
 69 [root@zlm1 16:17:22 ~/mydumper-0.9.1]
 70 #cmake .
 71 -bash: cmake: command not found -- There's not cmake package in my system,install cmake first.
 72 
 73 [root@zlm1 16:28:50 ~/mydumper-0.9.1]
 74 #yum install cmake
 75 -- Omitted.
 76 
 77 [root@zlm1 16:29:33 ~/mydumper-0.9.1]
 78 #cmake .
 79 -- The C compiler identification is GNU 4.8.3
 80 -- The CXX compiler identification is GNU 4.8.3
 81 -- Check for working C compiler: /usr/bin/cc
 82 -- Check for working C compiler: /usr/bin/cc -- works
 83 -- Detecting C compiler ABI info
 84 -- Detecting C compiler ABI info - done
 85 -- Check for working CXX compiler: /usr/bin/c++
 86 -- Check for working CXX compiler: /usr/bin/c++ -- works
 87 -- Detecting CXX compiler ABI info
 88 -- Detecting CXX compiler ABI info - done
 89 -- Using mysql-config: /usr/local/mysql/bin/mysql_config
 90 -- Found MySQL: /usr/local/mysql/include, /usr/local/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
 91 -- Found ZLIB: /usr/lib64/libz.so (found version "1.2.7") 
 92 -- Found PkgConfig: /usr/bin/pkg-config (found version "0.27.1") 
 93 -- checking for one of the modules 'glib-2.0'
 94 CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake:363 (message):
 95   None of the required 'glib-2.0' found
 96 Call Stack (most recent call first):
 97   cmake/modules/FindGLIB2.cmake:10 (pkg_search_module)
 98   CMakeLists.txt:10 (find_package)
 99 
100 
101 -- checking for one of the modules 'gthread-2.0'
102 CMake Error at /usr/share/cmake/Modules/FindPkgConfig.cmake:363 (message):
103   None of the required 'gthread-2.0' found
104 Call Stack (most recent call first):
105   cmake/modules/FindGLIB2.cmake:11 (pkg_search_module)
106   CMakeLists.txt:10 (find_package)
107 
108 
109 -- checking for module 'libpcre'
110 --   found libpcre, version 8.32
111 -- Found PCRE: /usr/include  
112 
113 CMake Warning at docs/CMakeLists.txt:9 (message):
114   Unable to find Sphinx documentation generator
115 
116 
117 -- ------------------------------------------------
118 -- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
119 -- CMAKE_INSTALL_PREFIX = /usr/local
120 -- BUILD_DOCS = ON
121 -- WITH_BINLOG = OFF
122 -- RUN_CPPCHECK = OFF
123 -- Change a values with: cmake -D<Variable>=<Value>
124 -- ------------------------------------------------
125 -- 
126 CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
127 Please set them or make sure they are set and tested correctly in the CMake files:
128 GLIB2_LIBRARIES (ADVANCED)
129     linked by target "mydumper" in directory /root/mydumper-0.9.1
130     linked by target "myloader" in directory /root/mydumper-0.9.1
131 GTHREAD2_LIBRARIES (ADVANCED)
132     linked by target "mydumper" in directory /root/mydumper-0.9.1
133     linked by target "myloader" in directory /root/mydumper-0.9.1
134 
135 -- Configuring incomplete, errors occurred!
136 See also "/root/mydumper-0.9.1/CMakeFiles/CMakeOutput.log".
137 
138 [root@zlm1 16:41:25 ~/mydumper-0.9.1]
139 #yum install glib2-devel -- Install glib2-devel package to solve the problem above.
140 --Omitted.
141 
142 [root@zlm1 16:49:17 ~/mydumper-0.9.1]
143 #cmake .
144 -- Using mysql-config: /usr/local/mysql/bin/mysql_config
145 -- Found MySQL: /usr/local/mysql/include, /usr/local/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
146 -- checking for one of the modules 'glib-2.0'
147 -- checking for one of the modules 'gthread-2.0'
148 
149 CMake Warning at docs/CMakeLists.txt:9 (message):
150   Unable to find Sphinx documentation generator
151 
152 
153 -- ------------------------------------------------
154 -- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
155 -- CMAKE_INSTALL_PREFIX = /usr/local
156 -- BUILD_DOCS = ON
157 -- WITH_BINLOG = OFF
158 -- RUN_CPPCHECK = OFF
159 -- Change a values with: cmake -D<Variable>=<Value>
160 -- ------------------------------------------------
161 -- 
162 -- Configuring done
163 -- Generating done
164 -- Build files have been written to: /root/mydumper-0.9.1
165 
166 [root@zlm1 16:49:21 ~/mydumper-0.9.1]
167 #make
168 Scanning dependencies of target mydumper
169 [ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
170 [ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
171 [ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
172 Linking C executable mydumper
173 [ 75%] Built target mydumper
174 Scanning dependencies of target myloader
175 [100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
176 Linking C executable myloader
177 [100%] Built target myloader
178 
179 [root@zlm1 16:50:31 ~/mydumper-0.9.1]
180 #ls -l | grep my
181 -rwxr-xr-x 1 root root 241428 Jun 17 16:50 mydumper -- This can be used to backup db.
182 -rw-r--r-- 1 root root  92941 Nov  6  2015 mydumper.c
183 -rw-r--r-- 1 root root   2169 Nov  6  2015 mydumper.h
184 -rwxr-xr-x 1 root root  63401 Jun 17 16:50 myloader -- This can be used to restore db.
185 -rw-r--r-- 1 root root  16630 Nov  6  2015 myloader.c
186 -rw-r--r-- 1 root root   1284 Nov  6  2015 myloader.hss
187 
188 [root@zlm1 16:52:33 ~/mydumper-0.9.1]
189 #mydumper --help
190 -bash: mydumper: command not found
191 
192 ###Copy
193 [root@zlm1 16:57:11 ~/mydumper-0.9.1]
194 #cp mydumper /usr/bin
195 
196 [root@zlm1 16:58:27 ~/mydumper-0.9.1]
197 #cp myloader /usr/bin
198 
199 [root@zlm1 16:58:33 ~/mydumper-0.9.1]
200 #mydumper --help -- There's not option to dump binlogs in new version,you cannot find parameter "-b" anymore.
201 Usage:
202   mydumper [OPTION?] multi-threaded MySQL dumping
203 
204 Help Options:
205   -?, --help                  Show help options
206 
207 Application Options:
208   -B, --database              Database to dump
209   -T, --tables-list           Comma delimited table list to dump (does not exclude regex option)
210   -o, --outputdir             Directory to output files to
211   -s, --statement-size        Attempted size of INSERT statement in bytes, default 1000000
212   -r, --rows                  Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
213   -F, --chunk-filesize        Split tables into chunks of this output file size. This value is in MB
214   -c, --compress              Compress output files
215   -e, --build-empty-files     Build dump files even if no data available from table
216   -x, --regex                 Regular expression for 'db.table' matching
217   -i, --ignore-engines        Comma delimited list of storage engines to ignore
218   -m, --no-schemas            Do not dump table schemas with the data
219   -d, --no-data               Do not dump table data
220   -G, --triggers              Dump triggers
221   -E, --events                Dump events
222   -R, --routines              Dump stored procedures and functions
223   -k, --no-locks              Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
224   --less-locking              Minimize locking time on InnoDB tables.
225   -l, --long-query-guard      Set long query timer in seconds, default 60
226   -K, --kill-long-queries     Kill long running queries (instead of aborting)
227   -D, --daemon                Enable daemon mode
228   -I, --snapshot-interval     Interval between each dump snapshot (in minutes), requires --daemon, default 60
229   -L, --logfile               Log file name to use, by default stdout is used
230   --tz-utc                    SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
231   --skip-tz-utc               
232   --use-savepoints            Use savepoints to reduce metadata locking issues, needs SUPER privilege
233   --success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
234   --lock-all-tables           Use LOCK TABLE for all, instead of FTWRL
235   -U, --updated-since         Use Update_time to dump only tables updated in the last U days
236   --trx-consistency-only      Transactional consistency only
237   -h, --host                  The host to connect to
238   -u, --user                  Username with privileges to run the dump
239   -p, --password              User password
240   -P, --port                  TCP/IP port to connect to
241   -S, --socket                UNIX domain socket file to use for connection
242   -t, --threads               Number of threads to use, default 4
243   -C, --compress-protocol     Use compression on the MySQL connection
244   -V, --version               Show the program version and exit
245   -v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
246 
247 ###create a big MyISAM table.###
248 root@localhost:mysql3306.sock [zlm]06:34:15>create table test_myisam(
249     -> id int primary key
250     -> ) engine=myisam;
251 Query OK, 0 rows affected (0.00 sec)
252 
253 root@localhost:mysql3306.sock [zlm]06:34:53>delimiter $$
254 root@localhost:mysql3306.sock [zlm]06:35:03>create procedure pro_insert (count int)
255     -> begin
256     -> declare i int unsigned default 0;
257     -> start transaction;
258     -> while i < count do
259     -> insert into test_myisam(id) values(i);
260     -> set i=i+1;
261     -> end while;
262     -> commit;
263     -> end;
264     -> $$
265 Query OK, 0 rows affected (0.00 sec)
266 
267 root@localhost:mysql3306.sock [zlm]06:35:03>delimiter ;
268 root@localhost:mysql3306.sock [zlm]06:35:04>call pro_insert(10000000);
269 Query OK, 0 rows affected (11 min 37.72 sec)
270 
271 root@localhost:mysql3306.sock [zlm]06:46:57>select count(*) from test_myisam;
272 +----------+
273 | count(*) |
274 +----------+
275 | 10000000 |
276 +----------+
277 1 row in set (0.03 sec)
278 
279 root@localhost:mysql3306.sock [zlm]06:48:55>show tables;
280 +----------------+
281 | Tables_in_zlm  |
282 +----------------+
283 | semi_sync_test |
284 | t1             |
285 | t2             |
286 | t3             |
287 | test           |
288 | test_flashbk   |
289 | test_myisam    |
290 +----------------+
291 7 rows in set (0.06 sec)
292 
293 root@localhost:mysql3306.sock [zlm]06:51:25>show create table test;
294 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
295 | Table | Create Table                                                                                                                                                                                  |
296 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
297 | test  | CREATE TABLE `test` (
298   `id` bigint(20) NOT NULL AUTO_INCREMENT,
299   `name` varchar(20) NOT NULL DEFAULT '',
300   PRIMARY KEY (`id`)
301 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 |
302 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
303 1 row in set (0.00 sec)
304 
305 ###Generate a backup by 2 threads.###
306 [root@zlm1 18:57:45 ~/mydumper-0.9.1]
307 #mydumper -B zlm -T test,test_myisam -u root -p Passw0rd -h localhost -t 2 -o /data/backup -- -t specify threads(default 4) in all.
308 
309 [root@zlm1 18:58:47 ~/mydumper-0.9.1]
310 
311 ###Check the output file.###
312 [root@zlm1 18:58:57 ~/mydumper-0.9.1]
313 #ls -l /data/backup
314 total 108408
315 drwxr-x--- 7 root root      4096 Jun 16 11:29 2018-06-16_11-28-59
316 -rw-r--r-- 1 root root       188 Jun 17 18:58 metadata -- It contains time,binlog file & position,GTID informations.
317 -rw-r--r-- 1 root root        62 Jun 17 18:58 zlm-schema-create.sql -- It contains database structure.
318 -rw-r--r-- 1 root root       180 Jun 17 18:58 zlm.test_myisam-schema.sql  -- It contains table structure.
319 -rw-r--r-- 1 root root 108892589 Jun 17 18:58 zlm.test_myisam.sql -- It contains data of MyISAM table "test_myisam".
320 -rw-r--r-- 1 root root       258 Jun 17 18:58 zlm.test-schema.sql -- It contains table structure.
321 -rw-r--r-- 1 root root   2089075 Jun 17 18:58 zlm.test.sql -- It contains data of innodb table "test".
322 
323 ###Check the general log for detail of backup.###
324 [root@zlm1 18:58:26 /data/mysql/mysql3306/data]
325 #cat zlm1.log
326 
327 2018-06-17T16:58:40.634569Z       29 Connect    root@localhost on zlm using Socket
328 2018-06-17T16:58:40.634595Z       29 Query    SET SESSION wait_timeout = 2147483
329 2018-06-17T16:58:40.634670Z       29 Query    SET SESSION net_write_timeout = 2147483
330 2018-06-17T16:58:40.634819Z       29 Query    SHOW PROCESSLIST
331 2018-06-17T16:58:40.634902Z       29 Query    FLUSH TABLES WITH READ LOCK -- Begin to generate FTWRL in order to have consistent backup.
332 2018-06-17T16:58:40.634997Z       29 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ -- create consistent snapshot.
333 2018-06-17T16:58:40.635072Z       29 Query    /*!40101 SET NAMES binary*/
334 2018-06-17T16:58:40.635125Z       29 Query    SHOW MASTER STATUS
335 2018-06-17T16:58:40.635205Z       29 Query    SHOW SLAVE STATUS
336 2018-06-17T16:58:40.636261Z       30 Connect    root@localhost on  using Socket -- Create sub thread #1 in backing up.
337 2018-06-17T16:58:40.636392Z       30 Query    SET SESSION wait_timeout = 2147483
338 2018-06-17T16:58:40.636443Z       30 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
339 2018-06-17T16:58:40.636479Z       30 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
340 2018-06-17T16:58:40.636527Z       30 Query    /*!40103 SET TIME_ZONE='+00:00' */
341 2018-06-17T16:58:40.636570Z       30 Query    /*!40101 SET NAMES binary*/
342 2018-06-17T16:58:40.636913Z       31 Connect    root@localhost on  using Socket -- Create sub thread #2 in backing up.
343 2018-06-17T16:58:40.636962Z       31 Query    SET SESSION wait_timeout = 2147483
344 2018-06-17T16:58:40.637005Z       31 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
345 2018-06-17T16:58:40.637039Z       31 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
346 2018-06-17T16:58:40.637084Z       31 Query    /*!40103 SET TIME_ZONE='+00:00' */
347 2018-06-17T16:58:40.637123Z       31 Query    /*!40101 SET NAMES binary*/
348 2018-06-17T16:58:40.637178Z       29 Init DB    zlm
349 2018-06-17T16:58:40.637219Z       29 Query    SHOW TABLE STATUS
350 2018-06-17T16:58:40.772453Z       29 Query    SHOW CREATE DATABASE `zlm`
351 2018-06-17T16:58:40.772650Z       30 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test_myisam`
352 2018-06-17T16:58:40.772927Z       31 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test`
353 2018-06-17T16:58:40.848929Z       31 Query    SHOW CREATE TABLE `zlm`.`test` -- Get the table structure of "test".
354 2018-06-17T16:58:40.961590Z       31 Query    SHOW CREATE TABLE `zlm`.`test_myisam` -- Get the table structure of "test_myisam".
355 2018-06-17T16:58:47.022712Z       29 Query    UNLOCK TABLES /* FTWRL */ -- Release table locks after get structure of tables.
356 2018-06-17T16:58:47.022724Z       31 Quit    
357 2018-06-17T16:58:47.022812Z       29 Quit    
358 2018-06-17T16:58:47.024460Z       30 Quit
359 
360 ###Generate a backup by 4 threads.###
361 [root@zlm1 19:05:10 ~/mydumper-0.9.1]
362 #mydumper -B zlm -T test,test_myisam -u root -p Passw0rd -h localhost -t 4 -o /data/backup
363 
364 [root@zlm1 19:27:06 ~/mydumper-0.9.1]
365 #
366 
367 ###Check the general log for detail of backup.###
368 [root@zlm1 19:26:37 /data/mysql/mysql3306/data]
369 #cat zlm1.log
370 
371 2018-06-17T17:27:01.654622Z       32 Connect    root@localhost on zlm using Socket
372 2018-06-17T17:27:01.654864Z       32 Query    SET SESSION wait_timeout = 2147483
373 2018-06-17T17:27:01.654922Z       32 Query    SET SESSION net_write_timeout = 2147483
374 2018-06-17T17:27:01.655009Z       32 Query    SHOW PROCESSLIST
375 2018-06-17T17:27:01.655072Z       32 Query    FLUSH TABLES WITH READ LOCK
376 2018-06-17T17:27:01.656485Z       32 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
377 2018-06-17T17:27:01.656577Z       32 Query    /*!40101 SET NAMES binary*/
378 2018-06-17T17:27:01.656744Z       32 Query    SHOW MASTER STATUS
379 2018-06-17T17:27:01.656832Z       32 Query    SHOW SLAVE STATUS
380 2018-06-17T17:27:01.657303Z       33 Connect    root@localhost on  using Socket -- Create sub thread #1 in backing up.
381 2018-06-17T17:27:01.657367Z       33 Query    SET SESSION wait_timeout = 2147483
382 2018-06-17T17:27:01.657416Z       33 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
383 2018-06-17T17:27:01.657453Z       33 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
384 2018-06-17T17:27:01.657503Z       33 Query    /*!40103 SET TIME_ZONE='+00:00' */
385 2018-06-17T17:27:01.657547Z       33 Query    /*!40101 SET NAMES binary*/
386 2018-06-17T17:27:01.658353Z       34 Connect    root@localhost on  using Socket -- Create sub thread #2 in backing up.
387 2018-06-17T17:27:01.658438Z       34 Query    SET SESSION wait_timeout = 2147483
388 2018-06-17T17:27:01.658485Z       34 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
389 2018-06-17T17:27:01.658568Z       34 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
390 2018-06-17T17:27:01.658631Z       34 Query    /*!40103 SET TIME_ZONE='+00:00' */
391 2018-06-17T17:27:01.658689Z       34 Query    /*!40101 SET NAMES binary*/
392 2018-06-17T17:27:01.659442Z       35 Connect    root@localhost on  using Socket -- Create sub thread #3 in backing up.
393 2018-06-17T17:27:01.659541Z       35 Query    SET SESSION wait_timeout = 2147483
394 2018-06-17T17:27:01.659580Z       35 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
395 2018-06-17T17:27:01.659581Z       35 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
396 2018-06-17T17:27:01.659581Z       35 Query    /*!40103 SET TIME_ZONE='+00:00' */
397 2018-06-17T17:27:01.659614Z       35 Query    /*!40101 SET NAMES binary*/
398 2018-06-17T17:27:01.659992Z       36 Connect    root@localhost on  using Socket -- Create sub thread #4 in backing up.
399 2018-06-17T17:27:01.660057Z       36 Query    SET SESSION wait_timeout = 2147483
400 2018-06-17T17:27:01.660106Z       36 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
401 2018-06-17T17:27:01.660144Z       36 Query    START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
402 2018-06-17T17:27:01.660193Z       36 Query    /*!40103 SET TIME_ZONE='+00:00' */
403 2018-06-17T17:27:01.660236Z       36 Query    /*!40101 SET NAMES binary*/
404 2018-06-17T17:27:01.660801Z       32 Init DB    zlm
405 2018-06-17T17:27:01.661053Z       32 Query    SHOW TABLE STATUS
406 2018-06-17T17:27:01.662581Z       32 Query    SHOW CREATE DATABASE `zlm`
407 2018-06-17T17:27:01.662906Z       33 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test_myisam`
408 2018-06-17T17:27:01.669514Z       34 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `zlm`.`test`
409 2018-06-17T17:27:01.674024Z       35 Query    SHOW CREATE TABLE `zlm`.`test`
410 2018-06-17T17:27:01.681205Z       35 Query    SHOW CREATE TABLE `zlm`.`test_myisam`
411 2018-06-17T17:27:06.548006Z       32 Query    UNLOCK TABLES /* FTWRL */
412 2018-06-17T17:27:06.548426Z       35 Quit    
413 2018-06-17T17:27:06.548509Z       36 Quit    
414 2018-06-17T17:27:06.548552Z       34 Quit    
415 2018-06-17T17:27:06.548619Z       32 Quit    
416 2018-06-17T17:27:06.549291Z       33 Quit

 

Precedure

 

Procedure

 

Summary

 

**Test1:Rescue a table after
dropping it based on a new mysqldump backup.**

  • mydump
    is a logical backup tool like mysqldump,but more
    efficient.
  • mydump support mutiple thread
    backup,which can short your backup time especially when backing up
    big tables.
  • When backing up operation begins
    there also a FTWRL to make a consistent backup.

    In
order to see the intact procedure of backup,we open the general log by
setting “general_log=on”  in runtime first.

 

 

 

Generate the test data with
sysbench.

 1 ###Open General Log###
 2 (root@localhost mysql3306.sock)[performance_schema]09:29:17>show variables like '%gener%';
 3 +------------------+-------------------------------------+
 4 | Variable_name    | Value                               |
 5 +------------------+-------------------------------------+
 6 | general_log      | OFF                                 |
 7 | general_log_file | /data/mysql/mysql3306/data/zlm2.log |
 8 +------------------+-------------------------------------+
 9 2 rows in set (0.00 sec)
10 
11 (root@localhost mysql3306.sock)[performance_schema]09:29:23>set general_log=on;
12 ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL
13 (root@localhost mysql3306.sock)[performance_schema]09:29:39>set global general_log=on;
14 Query OK, 0 rows affected (0.14 sec)
15 
16 ###Create innodb table and myisam table in database zlm.###
17 (root@localhost mysql3306.sock)[zlm]09:36:56>create table test_innodb(id int primary key) engine=innodb;
18 Query OK, 0 rows affected (0.02 sec)
19 
20 (root@localhost mysql3306.sock)[zlm]09:37:17>create table test_myisam(id int primary key) engine=myisam;
21 Query OK, 0 rows affected (0.01 sec)
22 
23 (root@localhost mysql3306.sock)[zlm]09:37:42>show tables;
24 +---------------+
25 | Tables_in_zlm |
26 +---------------+
27 | test_innodb   |
28 | test_myisam   |
29 +---------------+
30 2 rows in set (0.00 sec)
31 
32 (root@localhost mysql3306.sock)[zlm]09:37:45>
33 
34 ###Gnerate First Backup(disable triggers)###
35 [root@zlm2 09:50:08 ~]
36 #mysqldump --triggers=false -B zlm > /data/backup/first_3306-`date +%Y%m%d`.sql
37 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
38 
39 [root@zlm2 09:53:41 ~]
40 #ls -l /data/backup
41 total 8
42 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
43 
44 ###Check General Log###
45 [root@zlm2 09:52:57 /data/mysql/mysql3306/data]
46 #cat zlm2.log
47 
48 2018-06-15T07:53:41.345850Z       32 Connect    root@localhost on  using Socket
49 2018-06-15T07:53:41.346193Z       32 Query    /*!40100 SET @@SQL_MODE='' */
50 2018-06-15T07:53:41.346252Z       32 Query    /*!40103 SET TIME_ZONE='+00:00' */
51 2018-06-15T07:53:41.346332Z       32 Query    SHOW VARIABLES LIKE 'gtid\_mode'
52 2018-06-15T07:53:41.348422Z       32 Query    SELECT @@GLOBAL.GTID_EXECUTED
53 2018-06-15T07:53:41.350309Z       32 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
54 2018-06-15T07:53:41.351603Z       32 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
55 2018-06-15T07:53:41.352151Z       32 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
56 2018-06-15T07:53:41.354634Z       32 Init DB    zlm
57 2018-06-15T07:53:41.354659Z       32 Query    SHOW CREATE DATABASE IF NOT EXISTS `zlm`
58 2018-06-15T07:53:41.354660Z       32 Query    show tables
59 2018-06-15T07:53:41.354747Z       32 Query    LOCK TABLES `test_innodb` READ /*!32311 LOCAL */,`test_myisam` READ /*!32311 LOCAL */
60 2018-06-15T07:53:41.354815Z       32 Query    show table status like 'test\_innodb'
61 2018-06-15T07:53:41.355067Z       32 Query    SET SQL_QUOTE_SHOW_CREATE=1
62 2018-06-15T07:53:41.355110Z       32 Query    SET SESSION character_set_results = 'binary'
63 2018-06-15T07:53:41.355144Z       32 Query    show create table `test_innodb`
64 2018-06-15T07:53:41.355188Z       32 Query    SET SESSION character_set_results = 'utf8'
65 2018-06-15T07:53:41.355227Z       32 Query    show fields from `test_innodb`
66 2018-06-15T07:53:41.355412Z       32 Query    show fields from `test_innodb`
67 2018-06-15T07:53:41.355631Z       32 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
68 2018-06-15T07:53:41.356449Z       32 Query    show table status like 'test\_myisam'
69 2018-06-15T07:53:41.356723Z       32 Query    SET SQL_QUOTE_SHOW_CREATE=1
70 2018-06-15T07:53:41.356759Z       32 Query    SET SESSION character_set_results = 'binary'
71 2018-06-15T07:53:41.356819Z       32 Query    show create table `test_myisam`
72 2018-06-15T07:53:41.356863Z       32 Query    SET SESSION character_set_results = 'utf8'
73 2018-06-15T07:53:41.356900Z       32 Query    show fields from `test_myisam`
74 2018-06-15T07:53:41.357109Z       32 Query    show fields from `test_myisam`
75 2018-06-15T07:53:41.357349Z       32 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
76 2018-06-15T07:53:41.357420Z       32 Query    UNLOCK TABLES --Release the lock only after all tables have finished backup.
77 2018-06-15T07:53:41.361654Z       32 Quit    
 1 [root@zlm2 07:30:58 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=10 --table-size=10000 --mysql-storage-engine=innodb prepare
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Creating table 'sbtest1'...
 6 Inserting 10000 records into 'sbtest1'
 7 Creating a secondary index on 'sbtest1'...
 8 Creating table 'sbtest2'...
 9 Inserting 10000 records into 'sbtest2'
10 Creating a secondary index on 'sbtest2'...
11 Creating table 'sbtest3'...
12 Inserting 10000 records into 'sbtest3'
13 Creating a secondary index on 'sbtest3'...
14 Creating table 'sbtest4'...
15 Inserting 10000 records into 'sbtest4'
16 Creating a secondary index on 'sbtest4'...
17 Creating table 'sbtest5'...
18 Inserting 10000 records into 'sbtest5'
19 Creating a secondary index on 'sbtest5'...
20 Creating table 'sbtest6'...
21 Inserting 10000 records into 'sbtest6'
22 Creating a secondary index on 'sbtest6'...
23 Creating table 'sbtest7'...
24 Inserting 10000 records into 'sbtest7'
25 Creating a secondary index on 'sbtest7'...
26 Creating table 'sbtest8'...
27 Inserting 10000 records into 'sbtest8'
28 Creating a secondary index on 'sbtest8'...
29 Creating table 'sbtest9'...
30 Inserting 10000 records into 'sbtest9'
31 Creating a secondary index on 'sbtest9'...
32 Creating table 'sbtest10'...
33 Inserting 10000 records into 'sbtest10'
34 Creating a secondary index on 'sbtest10'...
35 
36 (zlm@192.168.1.101 3306)[sysbench]>show tables;
37 +--------------------+
38 | Tables_in_sysbench |
39 +--------------------+
40 | sbtest1            |
41 | sbtest10           |
42 | sbtest2            |
43 | sbtest3            |
44 | sbtest4            |
45 | sbtest5            |
46 | sbtest6            |
47 | sbtest7            |
48 | sbtest8            |
49 | sbtest9            |
50 +--------------------+
51 10 rows in set (0.00 sec)

 

 

   
I’ve make the output be simplest by disable triggers’ backup.We can see
from general log is that “LOCK TABLES test_innodb READ” then the
“UNLOCK TABLES” statment.Let’s see the difference with parameter
“single-transaction”.

**Backup the database sysbench
with mysqldump.**

 

 1 [root@zlm2 07:32:01 ~]
 2 #mysqldump --single-transaction --master-data=2 -A > db3306_`date +%Y%m%d`.sql
 3 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 4 
 5 [root@zlm2 07:32:09 ~]
 6 #ls -l
 7 total 34744
 8 drwxr-xr-x   2 root root     4096 Jul 23 10:10 20180723
 9 -rw-------.  1 root root     1431 Jul 16  2015 anaconda-ks.cfg
10 -rw-r--r--   1 root root 20390934 Jul 26 07:32 db3306_20180726.sql
11 -rw-r--r--   1 root root  7333548 Jul 24 02:48 db.sql
12 -rwxr-xr-x   1 root root       54 Jun 13 04:16 mysqld.sh
13 -rwxr-xr-x   1 root root  7829340 Jul 24 10:02 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
14 drwxr-xr-x  13 root root     4096 Jul  4 03:21 sysbench-1.0
15 
16 [root@zlm2 07:32:11 ~]
17 #scp db3306_20180726.sql zlm3:/data/backup
18 root@zlm3's password: 
19 db3306_20180726.sql                                                                                                100%   19MB  19.5MB/s   00:00    
20 
21 [root@zlm2 07:33:35 ~]
 1 ###Gnerate Sceond Backup(enable transaction consistent backup)###
 2 [root@zlm2 10:00:41 ~]
 3 #mysqldump --triggers=false --single-transaction -B zlm > /data/backup/second_3306-`date +%Y%m%d`.sql
 4 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 5 
 6 [root@zlm2 10:02:16 ~]
 7 #ls -l /data/backup
 8 total 8
 9 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
10 -rw-r--r-- 1 root root 2822 Jun 15 10:00 second_3306-20180615.sql
11 
12 ###Check General Log###
13 [root@zlm2 10:00:56 /data/mysql/mysql3306/data]
14 #cat zlm2.log
15 
16 2018-06-15T08:00:52.911566Z       34 Connect    root@localhost on  using Socket
17 2018-06-15T08:00:52.911733Z       34 Query    /*!40100 SET @@SQL_MODE='' */
18 2018-06-15T08:00:52.911848Z       34 Query    /*!40103 SET TIME_ZONE='+00:00' */
19 2018-06-15T08:00:52.912749Z       34 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
20 2018-06-15T08:00:52.912839Z       34 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
21 2018-06-15T08:00:52.912919Z       34 Query    SHOW VARIABLES LIKE 'gtid\_mode'
22 2018-06-15T08:00:52.915228Z       34 Query    SELECT @@GLOBAL.GTID_EXECUTED
23 2018-06-15T08:00:52.915371Z       34 Query    UNLOCK TABLES --Release lock here untill end,there're no more locks.
24 2018-06-15T08:00:52.915568Z       34 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
25 2018-06-15T08:00:52.916737Z       34 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
26 2018-06-15T08:00:52.918498Z       34 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
27 2018-06-15T08:00:52.920665Z       34 Init DB    zlm
28 2018-06-15T08:00:52.920742Z       34 Query    SHOW CREATE DATABASE IF NOT EXISTS `zlm`
29 2018-06-15T08:00:52.920787Z       34 Query    SAVEPOINT sp --Notice,there's a save opoint here.
30 2018-06-15T08:00:52.920837Z       34 Query    show tables
31 2018-06-15T08:00:52.921068Z       34 Query    show table status like 'test\_innodb'
32 2018-06-15T08:00:52.921242Z       34 Query    SET SQL_QUOTE_SHOW_CREATE=1
33 2018-06-15T08:00:52.921290Z       34 Query    SET SESSION character_set_results = 'binary'
34 2018-06-15T08:00:52.921334Z       34 Query    show create table `test_innodb`
35 2018-06-15T08:00:52.921397Z       34 Query    SET SESSION character_set_results = 'utf8'
36 2018-06-15T08:00:52.921444Z       34 Query    show fields from `test_innodb`
37 2018-06-15T08:00:52.921833Z       34 Query    show fields from `test_innodb`
38 2018-06-15T08:00:52.922279Z       34 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
39 2018-06-15T08:00:52.922380Z       34 Query    ROLLBACK TO SAVEPOINT sp --Rollback to savepoint sp
40 2018-06-15T08:00:52.922487Z       34 Query    show table status like 'test\_myisam'
41 2018-06-15T08:00:52.922694Z       34 Query    SET SQL_QUOTE_SHOW_CREATE=1
42 2018-06-15T08:00:52.922730Z       34 Query    SET SESSION character_set_results = 'binary'
43 2018-06-15T08:00:52.922763Z       34 Query    show create table `test_myisam`
44 2018-06-15T08:00:52.922872Z       34 Query    SET SESSION character_set_results = 'utf8'
45 2018-06-15T08:00:52.922929Z       34 Query    show fields from `test_myisam`
46 2018-06-15T08:00:52.923140Z       34 Query    show fields from `test_myisam`
47 2018-06-15T08:00:52.923395Z       34 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
48 2018-06-15T08:00:52.923459Z       34 Query    ROLLBACK TO SAVEPOINT sp --Rollback to savepoint sp.
49 2018-06-15T08:00:52.923487Z       34 Query    RELEASE SAVEPOINT sp --Release it where backup finish.
50 2018-06-15T08:00:52.928411Z       34 Quit    

 

 

**Drop one table in database
“sysbench”.**

   
There is only one piece of “UNLOCK TABLES”  which is related with lock
can be found this time.At the very beginning of the general log,it shows
“START TRANSACTION WITH CONSISTENT SNAPSHOT”.As soon as it gets the
gtid_executed variable,then it will realse the lock by execute statment
“UNLOCK TABLES”.Obviously,it’s a very short time.

 

   
After get the statment of creating database,there’s a savepoint
created.The rollback operation of the savepoint hapens after it gets the
full table backup.Let’s see another parameter “master-data” which can
make something different,too.

 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest10; 
 2 Query OK, 0 rows affected (0.01 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 | sbtest7            |
15 | sbtest8            |
16 | sbtest9            |
17 +--------------------+
18 9 rows in set (0.00 sec)
19 
20 (zlm@192.168.1.101 3306)[sysbench]>

 

 

 1 ###Gnerate Third Backup(add replication information)###
 2 [root@zlm2 10:37:55 ~]
 3 #mysqldump --triggers=false --single-transaction --master-data=2 -B zlm > /data/backup/third_3306-`date +%Y%m%d`.sql
 4 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 5 
 6 [root@zlm2 10:37:29 ~]
 7 #ls -l /data/backup
 8 total 12
 9 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
10 -rw-r--r-- 1 root root 2822 Jun 15 10:00 second_3306-20180615.sql
11 -rw-r--r-- 1 root root 2971 Jun 15 10:37 third_3306-20180615.sql
12 
13 ###Check General Log###
14 [root@zlm2 10:37:39 /data/mysql/mysql3306/data]
15 #cat zlm2.log
16 
17 2018-06-15T08:37:29.848849Z       35 Connect    root@localhost on  using Socket
18 2018-06-15T08:37:29.849052Z       35 Query    /*!40100 SET @@SQL_MODE='' */
19 2018-06-15T08:37:29.849153Z       35 Query    /*!40103 SET TIME_ZONE='+00:00' */
20 2018-06-15T08:37:29.849290Z       35 Query    FLUSH /*!40101 LOCAL */ TABLES -- Difference 1.
21 2018-06-15T08:37:29.855139Z       35 Query    FLUSH TABLES WITH READ LOCK -- Difference 2.
22 2018-06-15T08:37:29.855196Z       35 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
23 2018-06-15T08:37:29.855225Z       35 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
24 2018-06-15T08:37:29.855272Z       35 Query    SHOW VARIABLES LIKE 'gtid\_mode'
25 2018-06-15T08:37:29.857074Z       35 Query    SELECT @@GLOBAL.GTID_EXECUTED
26 2018-06-15T08:37:29.857406Z       35 Query    SHOW MASTER STATUS
27 2018-06-15T08:37:29.857498Z       35 Query    UNLOCK TABLES
28 2018-06-15T08:37:29.857622Z       35 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
29 2018-06-15T08:37:29.858662Z       35 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
30 2018-06-15T08:37:29.859309Z       35 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
31 2018-06-15T08:37:29.861396Z       35 Init DB    zlm
32 2018-06-15T08:37:29.862152Z       35 Query    SHOW CREATE DATABASE IF NOT EXISTS `zlm`
33 2018-06-15T08:37:29.862255Z       35 Query    SAVEPOINT sp
34 2018-06-15T08:37:29.862322Z       35 Query    show tables
35 2018-06-15T08:37:29.862485Z       35 Query    show table status like 'test\_innodb'
36 2018-06-15T08:37:29.862665Z       35 Query    SET SQL_QUOTE_SHOW_CREATE=1
37 2018-06-15T08:37:29.862724Z       35 Query    SET SESSION character_set_results = 'binary'
38 2018-06-15T08:37:29.862777Z       35 Query    show create table `test_innodb`
39 2018-06-15T08:37:29.862827Z       35 Query    SET SESSION character_set_results = 'utf8'
40 2018-06-15T08:37:29.862880Z       35 Query    show fields from `test_innodb`
41 2018-06-15T08:37:29.863198Z       35 Query    show fields from `test_innodb`
42 2018-06-15T08:37:29.863476Z       35 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
43 2018-06-15T08:37:29.863597Z       35 Query    ROLLBACK TO SAVEPOINT sp
44 2018-06-15T08:37:29.863668Z       35 Query    show table status like 'test\_myisam'
45 2018-06-15T08:37:29.865590Z       35 Query    SET SQL_QUOTE_SHOW_CREATE=1
46 2018-06-15T08:37:29.865833Z       35 Query    SET SESSION character_set_results = 'binary'
47 2018-06-15T08:37:29.865853Z       35 Query    show create table `test_myisam`
48 2018-06-15T08:37:29.865853Z       35 Query    SET SESSION character_set_results = 'utf8'
49 2018-06-15T08:37:29.865854Z       35 Query    show fields from `test_myisam`
50 2018-06-15T08:37:29.866059Z       35 Query    show fields from `test_myisam`
51 2018-06-15T08:37:29.867277Z       35 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
52 2018-06-15T08:37:29.867367Z       35 Query    ROLLBACK TO SAVEPOINT sp
53 2018-06-15T08:37:29.867398Z       35 Query    RELEASE SAVEPOINT sp
54 2018-06-15T08:37:29.869643Z       35 Quit    

Create a
rescue environment in an initialized instance on zlm3.

 

 1 (zlm@192.168.1.102 3306)[(none)]>show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | performance_schema |
 8 | sys                |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[(none)]>create database sysbench; //Create a same name database.
13 Query OK, 1 row affected (0.00 sec)
14 
15 (zlm@192.168.1.102 3306)[(none)]>show databases;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mysql              |
21 | performance_schema |
22 | sys                |
23 | sysbench           |
24 +--------------------+
25 5 rows in set (0.00 sec)
26 
27 (zlm@192.168.1.102 3306)[(none)]>create user rescue@'192.168.1.%' identified by 'rescue'; //Create a rescue user called "rescue".
28 Query OK, 0 rows affected (0.00 sec)
29 
30 (zlm@192.168.1.102 3306)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges to user ""rescue.
31 ERROR 1142 (42000): GRANT command denied to user 'zlm'@'zlm3' for table 'sbtest10' //It seems current user does not has the privilege to grant.
32 (zlm@192.168.1.102 3306)[(none)]>exit
33 Bye
34 
35 [root@zlm3 07:49:50 ~]
36 #mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3306.sock //Login with root user.
37 mysql: [Warning] Using a password on the command line interface can be insecure.
38 Welcome to the MySQL monitor.  Commands end with ; or \g.
39 Your MySQL connection id is 6
40 Server version: 5.7.21-log MySQL Community Server (GPL)
41 
42 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
43 
44 Oracle is a registered trademark of Oracle Corporation and/or its
45 affiliates. Other names may be trademarks of their respective
46 owners.
47 
48 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
49 
50 (root@localhost mysql3306.sock)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges again.It works.
51 Query OK, 0 rows affected (0.00 sec)

   
There’re two differences above,one is “FLUSH TABLES” another one is
“FLUSH TABLE WITH READ LOCK”.In case of other transactoins hold the
table lock related with the table to be backed up,the backup operation
will be blocked until those transactions release the lock or waiting
until time out(according to value of “lock_wait_timeout”)
occurs.

 

*    As
a matter of fact,we usually use “master-data” to backup our databases,we
shoudn’t make it happen in the case of there’re massive dml operations
continueously executed in rush hour.This will lead to failure of your
backup.

*

**Check
the backup set and import it.**

 

 1 [root@zlm3 07:59:28 /data/backup]
 2 #ls -l|grep db3306
 3 -rw-r--r-- 1 root  root   20390934 Jul 26 07:33 db3306_20180726.sql
 4 
 5 [root@zlm3 07:59:42 /data/backup]
 6 #mysql -urescue -prescue -h192.168.1.102 -P3306 -f < db3306_20180726.sql 
 7 mysql: [Warning] Using a password on the command line interface can be insecure.
 8 ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
 9 ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
10 ERROR 1044 (42000) at line 36: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
11 ERROR 1044 (42000) at line 38: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
12 ERROR 1046 (3D000) at line 44: No database selected
13 
14 //A bundle of "No database seelcted" message has been omitted.
15 
16 ERROR 1046 (3D000) at line 915: No database selected
17 ERROR 1044 (42000) at line 935: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
18 ERROR 1142 (42000) at line 943: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest1'
19 ERROR 1142 (42000) at line 946: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest1'
20 ERROR 1044 (42000) at line 960: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
21 ERROR 1142 (42000) at line 961: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
22 ERROR 1142 (42000) at line 962: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
23 ERROR 1142 (42000) at line 963: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
24 ERROR 1142 (42000) at line 964: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
25 ERROR 1044 (42000) at line 988: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
26 ERROR 1142 (42000) at line 999: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest2'
27 ERROR 1142 (42000) at line 1002: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest2'
28 ERROR 1044 (42000) at line 1016: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
29 ERROR 1142 (42000) at line 1017: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
30 ERROR 1142 (42000) at line 1018: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
31 ERROR 1142 (42000) at line 1019: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
32 ERROR 1142 (42000) at line 1020: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
33 ERROR 1142 (42000) at line 1027: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest3'
34 ERROR 1142 (42000) at line 1030: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest3'
35 ERROR 1044 (42000) at line 1044: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
36 ERROR 1142 (42000) at line 1045: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
37 ERROR 1142 (42000) at line 1046: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
38 ERROR 1142 (42000) at line 1047: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
39 ERROR 1142 (42000) at line 1048: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
40 ERROR 1142 (42000) at line 1055: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest4'
41 ERROR 1142 (42000) at line 1058: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest4'
42 ERROR 1044 (42000) at line 1072: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
43 ERROR 1142 (42000) at line 1073: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
44 ERROR 1142 (42000) at line 1074: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
45 ERROR 1142 (42000) at line 1075: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
46 ERROR 1142 (42000) at line 1076: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
47 ERROR 1142 (42000) at line 1083: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest5'
48 ERROR 1142 (42000) at line 1086: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest5'
49 ERROR 1044 (42000) at line 1100: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
50 ERROR 1142 (42000) at line 1101: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
51 ERROR 1142 (42000) at line 1102: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
52 ERROR 1142 (42000) at line 1103: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
53 ERROR 1142 (42000) at line 1104: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
54 ERROR 1142 (42000) at line 1111: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest6'
55 ERROR 1142 (42000) at line 1114: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest6'
56 ERROR 1044 (42000) at line 1128: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
57 ERROR 1142 (42000) at line 1129: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
58 ERROR 1142 (42000) at line 1130: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
59 ERROR 1142 (42000) at line 1131: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
60 ERROR 1142 (42000) at line 1132: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
61 ERROR 1142 (42000) at line 1139: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest7'
62 ERROR 1142 (42000) at line 1142: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest7'
63 ERROR 1044 (42000) at line 1156: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
64 ERROR 1142 (42000) at line 1157: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
65 ERROR 1142 (42000) at line 1158: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
66 ERROR 1142 (42000) at line 1159: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
67 ERROR 1142 (42000) at line 1160: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
68 ERROR 1142 (42000) at line 1167: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest8'
69 ERROR 1142 (42000) at line 1170: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest8'
70 ERROR 1044 (42000) at line 1184: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
71 ERROR 1142 (42000) at line 1185: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
72 ERROR 1142 (42000) at line 1186: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
73 ERROR 1142 (42000) at line 1187: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
74 ERROR 1142 (42000) at line 1188: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
75 ERROR 1142 (42000) at line 1195: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest9'
76 ERROR 1142 (42000) at line 1198: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest9'
77 ERROR 1044 (42000) at line 1212: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
78 ERROR 1142 (42000) at line 1213: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
79 ERROR 1142 (42000) at line 1214: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
80 ERROR 1142 (42000) at line 1215: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
81 ERROR 1142 (42000) at line 1216: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
82 ERROR 1044 (42000) at line 1223: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
83 ERROR 1044 (42000) at line 1225: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
84 ERROR 1227 (42000) at line 1226: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
85 
86 //The other tables in backup set will be skipped except for table "sbtest10".

Summary

 

  •  
      You’ve been clear about the whole procedure of backup using
    mysqldump now.But,there’s still one thing make me confused is that
    why I used “master-data” in mysqldump but not got the change master
    statement
    .Is it due to my Group Replication environment?**
  •  
      Look out,this tool doesnot support backing up in parallel mode.If
    your database is huge enough,do think twice or use another backup
    tool in stead of it. 

**Check
the rescued table “sbtest10”.**

 

 1 (root@localhost mysql3306.sock)[(none)]>use sysbench
 2 Reading table information for completion of table and column names
 3 You can turn off this feature to get a quicker startup with -A
 4 
 5 Database changed
 6 (root@localhost mysql3306.sock)[sysbench]>show tables;
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest10           |
11 +--------------------+
12 1 row in set (0.00 sec)
13 
14 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest10;
15 +----------+
16 | count(*) |
17 +----------+
18 |    10000 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 //Because the dropping operation is just happened after my backing up with mysqldump.There's no need to backup the incremental data in the dropped table.
23 //Therefore,we can simply copy the table back with transportable tablespace method,which can be referred to my previous blog.

 

Test2:Rescue
a table after truncating it based on a old mysqldump backup plus
binlog.

 

Execute
several normal dml operations in table “sbtest9”.

 1 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
 2 +----------+
 3 | count(*) |
 4 +----------+
 5 |    10000 |
 6 +----------+
 7 1 row in set (0.00 sec)
 8 
 9 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest9 limit 5000;
10 Query OK, 5000 rows affected (0.07 sec)
11 
12 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
13 +----------+
14 | count(*) |
15 +----------+
16 |     5000 |
17 +----------+
18 1 row in set (0.00 sec)
19 
20 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
21 Query OK, 0 rows affected (0.04 sec)
22 
23 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest9 limit 2500;
24 Query OK, 2500 rows affected (0.04 sec)
25 
26 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
27 +----------+
28 | count(*) |
29 +----------+
30 |     2500 |
31 +----------+
32 1 row in set (0.00 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
35 Query OK, 0 rows affected (0.02 sec)

 

**Truncate
the table to mimic the miss operation.**

 1 (zlm@192.168.1.101 3306)[sysbench]>truncate table sbtest9;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
 5 +----------+
 6 | count(*) |
 7 +----------+
 8 |        0 |
 9 +----------+
10 1 row in set (0.00 sec)
11 
12 (zlm@192.168.1.101 3306)[sysbench]>show master status;
13 +------------------+----------+--------------+------------------+------------------------------------------------+
14 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
15 +------------------+----------+--------------+------------------+------------------------------------------------+
16 | mysql-bin.000033 |      340 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730214 |
17 +------------------+----------+--------------+------------------+------------------------------------------------+
18 1 row in set (0.00 sec)

 

**Clear
the environment and grant the right privileges.**

1 (root@localhost mysql3306.sock)[sysbench]>drop table sbtest10;
2 Query OK, 0 rows affected (0.03 sec)
3 
4 (root@localhost mysql3306.sock)[sysbench]>revoke all privileges on sysbench.sbtest10 from rescue@'192.168.1.%';
5 Query OK, 0 rows affected (0.00 sec)
6 
7 (root@localhost mysql3306.sock)[sysbench]>grant all privileges on sysbench.sbtest9 to rescue@'192.168.1.%';
8 Query OK, 0 rows affected (0.00 sec)

 

**Restore
the table “sbtest9” from mysqldump backup.**

 1 [root@zlm3 09:19:39 /data/backup]
 2 #mysql -urescue -prescue -h192.168.1.102 -P3306 -f < db3306_20180726.sql
 3 
 4 ... //Omitted.
 5 
 6 (root@localhost mysql3306.sock)[sysbench]>show tables;
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest9            |
11 +--------------------+
12 1 row in set (0.00 sec)
13 
14 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
15 +----------+
16 | count(*) |
17 +----------+
18 |    10000 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 //On account of restoring from an old mysqldump backup,we cannot rescue the incremental data in the table "sbtest9".
23 //What can we do next step?Those incremental data are all in the binlog,so we need to implement a slave first.

 

**Implement
a slave filter replication on zlm3.**

  1 //Fetch the gtid_purged infomation from mysqldump backup.
  2 [root@zlm3 09:39:19 /data/backup]
  3 #grep "SET @@GLOBAL.GTID_PURGED" db3306_20180726.sql 
  4 SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';
  5 
  6 (root@localhost mysql3306.sock)[sysbench]>reset master;
  7 Query OK, 0 rows affected (0.01 sec)
  8 
  9 (root@localhost mysql3306.sock)[sysbench]>reset slave;
 10 Query OK, 0 rows affected (0.02 sec)
 11 
 12 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
 13 *************************** 1. row ***************************
 14                Slave_IO_State: 
 15                   Master_Host: 192.168.1.101
 16                   Master_User: repl
 17                   Master_Port: 3306
 18                 Connect_Retry: 60
 19               Master_Log_File: 
 20           Read_Master_Log_Pos: 4
 21                Relay_Log_File: relay-bin.000001
 22                 Relay_Log_Pos: 4
 23         Relay_Master_Log_File: 
 24              Slave_IO_Running: No
 25             Slave_SQL_Running: No
 26               Replicate_Do_DB: 
 27           Replicate_Ignore_DB: 
 28            Replicate_Do_Table: 
 29        Replicate_Ignore_Table: 
 30       Replicate_Wild_Do_Table: 
 31   Replicate_Wild_Ignore_Table: 
 32                    Last_Errno: 0
 33                    Last_Error: 
 34                  Skip_Counter: 0
 35           Exec_Master_Log_Pos: 0
 36               Relay_Log_Space: 169
 37               Until_Condition: None
 38                Until_Log_File: 
 39                 Until_Log_Pos: 0
 40            Master_SSL_Allowed: No
 41            Master_SSL_CA_File: 
 42            Master_SSL_CA_Path: 
 43               Master_SSL_Cert: 
 44             Master_SSL_Cipher: 
 45                Master_SSL_Key: 
 46         Seconds_Behind_Master: NULL
 47 Master_SSL_Verify_Server_Cert: No
 48                 Last_IO_Errno: 0
 49                 Last_IO_Error: 
 50                Last_SQL_Errno: 0
 51                Last_SQL_Error: 
 52   Replicate_Ignore_Server_Ids: 
 53              Master_Server_Id: 0
 54                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
 55              Master_Info_File: mysql.slave_master_info
 56                     SQL_Delay: 0
 57           SQL_Remaining_Delay: NULL
 58       Slave_SQL_Running_State: 
 59            Master_Retry_Count: 86400
 60                   Master_Bind: 
 61       Last_IO_Error_Timestamp: 
 62      Last_SQL_Error_Timestamp: 
 63                Master_SSL_Crl: 
 64            Master_SSL_Crlpath: 
 65            Retrieved_Gtid_Set: 
 66             Executed_Gtid_Set: 
 67                 Auto_Position: 1
 68          Replicate_Rewrite_DB: 
 69                  Channel_Name: 
 70            Master_TLS_Version: 
 71 1 row in set (0.00 sec)
 72 
 73 //Set gtid_purged variable.
 74 (root@localhost mysql3306.sock)[sysbench]>SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';
 75 Query OK, 0 rows affected (0.00 sec)
 76 
 77 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
 78 *************************** 1. row ***************************
 79                Slave_IO_State: 
 80                   Master_Host: 192.168.1.101
 81                   Master_User: repl
 82                   Master_Port: 3306
 83                 Connect_Retry: 60
 84               Master_Log_File: 
 85           Read_Master_Log_Pos: 4
 86                Relay_Log_File: relay-bin.000001
 87                 Relay_Log_Pos: 4
 88         Relay_Master_Log_File: 
 89              Slave_IO_Running: No
 90             Slave_SQL_Running: No
 91               Replicate_Do_DB: 
 92           Replicate_Ignore_DB: 
 93            Replicate_Do_Table: 
 94        Replicate_Ignore_Table: 
 95       Replicate_Wild_Do_Table: 
 96   Replicate_Wild_Ignore_Table: 
 97                    Last_Errno: 0
 98                    Last_Error: 
 99                  Skip_Counter: 0
100           Exec_Master_Log_Pos: 0
101               Relay_Log_Space: 169
102               Until_Condition: None
103                Until_Log_File: 
104                 Until_Log_Pos: 0
105            Master_SSL_Allowed: No
106            Master_SSL_CA_File: 
107            Master_SSL_CA_Path: 
108               Master_SSL_Cert: 
109             Master_SSL_Cipher: 
110                Master_SSL_Key: 
111         Seconds_Behind_Master: NULL
112 Master_SSL_Verify_Server_Cert: No
113                 Last_IO_Errno: 0
114                 Last_IO_Error: 
115                Last_SQL_Errno: 0
116                Last_SQL_Error: 
117   Replicate_Ignore_Server_Ids: 
118              Master_Server_Id: 0
119                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
120              Master_Info_File: mysql.slave_master_info
121                     SQL_Delay: 0
122           SQL_Remaining_Delay: NULL
123       Slave_SQL_Running_State: 
124            Master_Retry_Count: 86400
125                   Master_Bind: 
126       Last_IO_Error_Timestamp: 
127      Last_SQL_Error_Timestamp: 
128                Master_SSL_Crl: 
129            Master_SSL_Crlpath: 
130            Retrieved_Gtid_Set: 
131             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210 //After set @@global.gtid_purged operation,Executed_Gitd_Set will contain it.
132                 Auto_Position: 1
133          Replicate_Rewrite_DB: 
134                  Channel_Name: 
135            Master_TLS_Version: 
136 1 row in set (0.00 sec)
137 
138 //Start IO Thread.
139 (root@localhost mysql3306.sock)[sysbench]>start slave io_thread;
140 Query OK, 0 rows affected (0.01 sec)
141 
142 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
143 *************************** 1. row ***************************
144                Slave_IO_State: Waiting for master to send event
145                   Master_Host: 192.168.1.101
146                   Master_User: repl
147                   Master_Port: 3306
148                 Connect_Retry: 60
149               Master_Log_File: mysql-bin.000033 //The newly binlog has been pulled to local server.
150           Read_Master_Log_Pos: 190
151                Relay_Log_File: relay-bin.000001
152                 Relay_Log_Pos: 4
153         Relay_Master_Log_File: 
154              Slave_IO_Running: Yes //The IO Thread working normally.
155             Slave_SQL_Running: No
156               Replicate_Do_DB: 
157           Replicate_Ignore_DB: 
158            Replicate_Do_Table: 
159        Replicate_Ignore_Table: 
160       Replicate_Wild_Do_Table: 
161   Replicate_Wild_Ignore_Table: 
162                    Last_Errno: 0
163                    Last_Error: 
164                  Skip_Counter: 0
165           Exec_Master_Log_Pos: 0
166               Relay_Log_Space: 1433264
167               Until_Condition: None
168                Until_Log_File: 
169                 Until_Log_Pos: 0
170            Master_SSL_Allowed: No
171            Master_SSL_CA_File: 
172            Master_SSL_CA_Path: 
173               Master_SSL_Cert: 
174             Master_SSL_Cipher: 
175                Master_SSL_Key: 
176         Seconds_Behind_Master: NULL
177 Master_SSL_Verify_Server_Cert: No
178                 Last_IO_Errno: 0
179                 Last_IO_Error: 
180                Last_SQL_Errno: 0
181                Last_SQL_Error: 
182   Replicate_Ignore_Server_Ids: 
183              Master_Server_Id: 1013306
184                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
185              Master_Info_File: mysql.slave_master_info
186                     SQL_Delay: 0
187           SQL_Remaining_Delay: NULL
188       Slave_SQL_Running_State: 
189            Master_Retry_Count: 86400
190                   Master_Bind: 
191       Last_IO_Error_Timestamp: 
192      Last_SQL_Error_Timestamp: 
193                Master_SSL_Crl: 
194            Master_SSL_Crlpath: 
195            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214 //The newest gtid information has been got(3730211-3730214).
196             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210
197                 Auto_Position: 1
198          Replicate_Rewrite_DB: 
199                  Channel_Name: 
200            Master_TLS_Version: 
201 1 row in set (0.00 sec)
202 
203 //Specify the replication filter only for table "sbtest9".
204 (root@localhost mysql3306.sock)[sysbench]>CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (sysbench.sbtest9);
205 Query OK, 0 rows affected (0.00 sec)
206 
207 //Analyze the binlog on master to find out the right postion of gtid_set.
208 [root@zlm2 10:20:28 ~]
209 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000033 > 33.log
210 
211 [root@zlm2 10:20:36 ~]
212 #cat 33.log
213 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
214 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
215 DELIMITER /*!*/;
216 # at 4
217 #180726  9:13:04 server id 1013306  end_log_pos 123     Start: binlog v 4, server v 5.7.21-log created 180726  9:13:04
218 # Warning: this binlog is either in use or was not closed properly.
219 # at 123
220 #180726  9:13:04 server id 1013306  end_log_pos 190     Previous-GTIDs
221 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213
222 # at 190
223 #180726 10:11:52 server id 1013306  end_log_pos 251     GTID    last_committed=0    sequence_number=1    rbr_only=no
224 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214'/*!*/;
225 # at 251
226 #180726 10:11:52 server id 1013306  end_log_pos 340     Query    thread_id=16    exec_time=0    error_code=0
227 use `sysbench`/*!*/;
228 SET TIMESTAMP=1532592712/*!*/;
229 SET @@session.pseudo_thread_id=16/*!*/;
230 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
231 SET @@session.sql_mode=1436549152/*!*/;
232 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
233 /*!\C utf8 *//*!*/;
234 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
235 SET @@session.lc_time_names=0/*!*/;
236 SET @@session.collation_database=DEFAULT/*!*/;
237 truncate table sbtest9 //Here's the truncate operation,we are supposed the sql_thread just stop before this operation.
238 /*!*/;
239 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
240 DELIMITER ;
241 # End of log file
242 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
243 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
244 
245 //Start SQL Thread using until clause.
246 (root@localhost mysql3306.sock)[sysbench]>start slave sql_thread until SQL_BEFORE_GTIDS='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214';
247 Query OK, 0 rows affected (0.00 sec)
248 
249 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
250 *************************** 1. row ***************************
251                Slave_IO_State: Waiting for master to send event
252                   Master_Host: 192.168.1.101
253                   Master_User: repl
254                   Master_Port: 3306
255                 Connect_Retry: 60
256               Master_Log_File: mysql-bin.000033
257           Read_Master_Log_Pos: 340
258                Relay_Log_File: relay-bin.000007
259                 Relay_Log_Pos: 395
260         Relay_Master_Log_File: mysql-bin.000033
261              Slave_IO_Running: Yes
262             Slave_SQL_Running: No
263               Replicate_Do_DB: 
264           Replicate_Ignore_DB: 
265            Replicate_Do_Table: sysbench.sbtest9 //Here's the "do table" option of replication filter.
266        Replicate_Ignore_Table: 
267       Replicate_Wild_Do_Table: 
268   Replicate_Wild_Ignore_Table: 
269                    Last_Errno: 0
270                    Last_Error: 
271                  Skip_Counter: 0
272           Exec_Master_Log_Pos: 190
273               Relay_Log_Space: 821
274               Until_Condition: SQL_BEFORE_GTIDS //Here's the option of until condition of start slave clause.
275                Until_Log_File: 
276                 Until_Log_Pos: 0
277            Master_SSL_Allowed: No
278            Master_SSL_CA_File: 
279            Master_SSL_CA_Path: 
280               Master_SSL_Cert: 
281             Master_SSL_Cipher: 
282                Master_SSL_Key: 
283         Seconds_Behind_Master: NULL
284 Master_SSL_Verify_Server_Cert: No
285                 Last_IO_Errno: 0
286                 Last_IO_Error: 
287                Last_SQL_Errno: 0
288                Last_SQL_Error: 
289   Replicate_Ignore_Server_Ids: 
290              Master_Server_Id: 1013306
291                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
292              Master_Info_File: mysql.slave_master_info
293                     SQL_Delay: 0
294           SQL_Remaining_Delay: NULL
295       Slave_SQL_Running_State: 
296            Master_Retry_Count: 86400
297                   Master_Bind: 
298       Last_IO_Error_Timestamp: 
299      Last_SQL_Error_Timestamp: 
300                Master_SSL_Crl: 
301            Master_SSL_Crlpath: 
302            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214
303             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213
304                 Auto_Position: 1
305          Replicate_Rewrite_DB: 
306                  Channel_Name: 
307            Master_TLS_Version: 
308 1 row in set (0.00 sec)
309 
310 //Check the contents of rescued table.
311 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
312 +----------+
313 | count(*) |
314 +----------+
315 |     2500 | //This is the correct number of records before we truncate the table on master.
316 +----------+
317 1 row in set (0.00 sec)
318 
319 //Likewise,we can copy the rescued table back to master in a proper certain time by transportable tablespace tech(I'm not going to demonstrate here).

 

Summary

  • There
    always be some miss operations such as drop,truncate which cannot be
    flashed back easily by tools.We should be careful to avoid
    them.
  • Onlyif
    you have a full database backup(mysqldump or Xtraback) and vital
    binlog,the destroyed table could be rescued.
  • The
    portion of recovering imcremental data also can be used in
    Xtrabackup method when rescuing lost data.
  • It’s
    recommend to rename the rescued table before copying it back to the
    product database with transportable tablespace.