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.
版权声明:本文为博主原创文章,如需转载请保留此声明及博客链接,谢谢!
博客地址: http://www.cnblogs.com/aaron8219 & http://blog.csdn.net/aaron8219
博客地址: http://www.cnblogs.com/aaron8219 & http://blog.csdn.net/aaron8219