Analysis of the working principle of mydumper

1. Introduction

Logical backup Gun-mydumper is a high-performance multithreaded backup tool for MySQL. The tool is developed by developers from MySQL, Facebook and skysql. Is developed by the lightweight C language; the execution speed is said to be 10 times faster than mysqldump; support transaction and non-transaction table consistent backup; also support export binlog; support multithreaded backup; support to work in the form of daemons, scheduled backup; and is open source, its protocol is GPLv3.

2. Usage

Root@dd:~/mydumper-0.6.2#. / mydumper--help Usage: mydumper [OPTION...] Multi-threaded MySQL dumping Help Options: -?,-- help Show help options Application Options:-B,-- the library that database needs to back up-T,-- the table that tables-list needs to back up Separate the directory of-o,-- outputdir output file-s,-- statement-size to generate the number of bytes of the insert statement with a comma. Default is 1000000. This parameter cannot be too small. Otherwise, Row bigger than statement_size for tools.t_serverinfo-r,-- rows will be split into many rows. Block table-- c,-- compress compressed output file-- e,-- build-empty-files even if the table has no data. Or generate an empty file-x,-- regex regular expression: 'db.table'-I,-- storage engine ignored by ignore-engines Separated by commas-m,-- no-schemas does not export table structure-k,-- no-locks does not perform shared read lock warning: this will result in inconsistent backups-l,-- long-query-guard set long query time, default 60 seconds If you exceed this time, you will get an error: There are queries in PROCESSLIST running longer than 60s, aborting dump-- kill-long-queries kill drops the long-executed query-b,-- binlogs exports binlog-D,-- daemon enables daemon mode-- I,-- snapshot-interval dump snapshot interval. Default is 60s. Need to be in daemon mode-L,-- logfile log file-h,-- host The host to connect to-u,-- user Username with privileges to run the dump-p,-- password User password-P,-- port TCP/IP port to connect to-S -- socket UNIX domain socket file to use for connection-t,-- number of threads used by threads Default 4-C,-- compress-protocol uses compression protocol-V,-- version Show the program version and exit-v,-- verbose more output on mysql connections, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

3. Principle analysis

1) the tool can be launched as a daemon in-- daemo mode. By default, a backup is performed every 60s. The interval is controlled by-- snapshot-interval.

2) A connection to the MySQL service will be created first

3) then execute show processlist on MySQL and decide to exit or kill the long query according to the parameters long-query-guard and kill-long-queries

4) according to the availability of-- lock-all-tables, lock the table: LOCK TABLE tn READ or FLUSH TABLES WITH READ LOCK;, and then execute START TRANSACTION

5) create 4 child threads for backup tables

6) after creating a work thread, g_async_queue_pop (conf.ready). If the conf.ready is empty, you need to wait for sleep.

7) work thread execution: connect to mysql; to set the isolation level RR;start transaction;g_async_queue_push (conf- > ready,GINT_TO_POINTER (1)); then the g_async_queue_pop of the main function can wake up and continue to create threads or go down.

8) the work thread then executes: enter the endless loop and pop the task job= (struct job *) g_async_queue_pop (conf- > queue) from the queue; dump according to the task type. The parallelism here is based on table parallelism. Back up the non-transactional table first, then the innodb table

9) finally, wake up g_async_queue_pop (conf.unlock_tables) after all work threads have finished backing up the non-transactional table, and perform UNLOCK TABLES unlocking

10) end of transaction