MySQL备份恢复之mydumper

 

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.

 

Introduce

 

    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:https://launchpad.net/mydumper/+milestone/0.9.3

 

  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

 

Summary

• 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.