Mydumper principle and mydumper Principle

Mydumper principle and mydumper Principle
Installation of mydumper: http://www.cnblogs.com/lizhi221/p/7010174.htmlMydumper Introduction MySQL's mysqldump tool supports single-threaded operations. Multiple tables are exported one by one without a parallel machine, which makes it unable to quickly back up data. Mydumper is a utility that supports multithreading. It can read data from tables in parallel and write data to different files at the same time, this makes it faster than the traditional mysqldump in terms of processing speed. One of its features is that the list needs to be locked during processing. Therefore, if we need to perform backup during working hours, it will cause DML blocking. However, MySQL usually has master and slave databases, and most of the backups are performed from the master database. Therefore, you do not need to consider the lock issue. In this way, mydumper can better complete the backup task.Mydumper features① Multi-thread backup ② because it is a multi-thread logical backup, multiple backup files will be generated after the backup ③ FTWRL (flush tables with read lock) is applied to the MyISAM table during the backup ), blocking DML statements ④ ensure consistency of backup data ⑤ support File compression ⑥ support exporting binlog 7 Support multi-thread recovery prepare support working in daemon mode, scheduled snapshot and continuous binary log shard support chunking backup filesMydumper Parameters 

$ Mydumper -- helpUsage: mydumper [OPTION...] multi-threaded MySQL dumpingHelp Options :-?, -- Help Show help optionsApplication Options:-B, -- database to be backed up. If this parameter is not specified, all databases-T and -- tables-list must be backed up, names are separated by commas (,). The directory output by the backup file outputdir is-s, and the number of insert statements generated by the statement-size is 1000000-r by default, -- rows Try to split tables into chunks of this rule rows. this option turns off -- chunk-filesize-F, -- chunk-filesize Split tables into chunks of this output file size. this value is in MB-c, -- compress Compress output files compress the output file-e, -- build-empty-files if the table data is empty, still generate an empty file (by default, there is only a table structure file)-x, -- regex Regular expression for 'db. table 'matching uses regular expressions to match 'db. table '-I, -- ignore-engines Comma delimited list of storage engines ignored by ignore, separated by commas (,)-m, -- no-schemas Do not dump table schemas with the data Do not back up the table structure, only back up data-d, -- no-data Do not dump table data backup table structure, Do not back up data-G, -- triggers Dump triggers backup trigger-E, -- events Dump events-R, -- routines Dump stored procedures and functions backup stored procedures and functions-k, -- no-locks do not use temporary shared read-only locks. Using this option will cause data inconsistency -- less-locking Minimize locking time on InnoDB tables. reduce the apply Time of the InnoDB table lock-l, -- long-query-guard sets the long query timeout time for blocking backup, in seconds, the default value is 60 seconds (mydumper will exit after timeout)-K, -- kill-long-queries Kill long running queries (instead of aborting) to kill long queries (do not Exit) -D, -- daemon Enable daemon mode. The daemon mode continuously performs backup-I and -- snapshot-interval dump snapshots on the database at a certain interval, the default value is 60 s. In daemon mode,-L, -- the log file name used by logfile (the log generated by mydumper ), standard output is used by default -- 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 servers with different time zones, defaults to on use -- skip-tz-utc to disable. -- skip-tz-utc -- use-savepoints uses savepoints to reduce the lock time caused by metadata collection, SUPER permission required -- success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist -- lock-all-tables Use lock table for all, instead of FTWRL-U, -- updated-since Use Update_time to dump only tables updated in the last U days -- trx-consistency-only Transactional consistency only-h, -- host connection host name-u, -- user is used to back up the user name-p, -- password user password-P, -- port connection port-S, -- socket: specifies the number of backup threads enabled by the socket file t and threads during socket communication. The default value is 4-C. -- compress-protocol compresses the data-V for mysql communication, -- version: displays the version number-v, -- verbose output information mode, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 2 by default

  Mydumper Main Process Overview1. The main thread flush tables with read lock applies a global READ-only LOCK to prevent DML statement writing, ensure data consistency 2. Read the binary log file name and log write location at the current time point and record it in the metadata file, for example, three or N threads (number of threads can be specified, and the default value is 4) are used even if the point is restored. The dump thread starts transaction with consistent snapshot; start read-consistent transactions 4. dump non-InnoDB tables. First, export the non-transaction engine table 5. After the master thread unlock tables backup is complete, release global read-only locks 6. dump InnoDB tables, export InnoDB table 7 based on transactions, and end mydumper's less locking mode: mydumper uses -- less-locking to reduce lock wait time, at this time, the execution mechanism of mydumper is roughly 1. The main thread flush tables with read lock (Global LOCK) 2. The Dump thread start transaction with consistent snapshot; 3. LL Dump thread lock tables non-InnoDB (internal thread LOCK) 4. main thread unlock tables 5. LL Dump thread dump non-InnoDB tables 6. LL DUmp thread UNLOCK non-InnoDB 7. Dump thread dump InnoDB tables Mydumper backup Flowchart