Mydumper backup principle and use Method-Linuxnote__linux

Time 2014-07-01 09:50:31 Blog Park essence AreaOriginal http://www.cnblogs.com/linuxnote/p/3817698.html Theme Mydumper

MySQL's own mysqldump tool supports single-threaded work, exporting multiple tables in turn, without a parallel machine, which makes it impossible to quickly back up data.

Mydumper, as a utility, can support multithreaded work well, and can read data from tables in parallel and write to different files simultaneously, which makes it faster than traditional mysqldump in processing speed. One of the features is that the list needs to be locked during processing, so if we need to perform backup work during the working hours, it can cause a DML blockage. But generally now MySQL has the principal and subordinate, the backup is mostly in the from, so the lock problem can not be considered. In this way, Mydumper can better complete the backup task.

Mydumper feature multithreading backup because it is a multithreaded logical backup, the MyISAM table is FTWRL (FLUSH tables with READ LOCK) when multiple backup files are generated after backup, blocking DML statements to ensure consistent support for file compression for backup data Indented support export Binlog support for multithreaded recovery support to work in daemon mode, timed snapshots and continuous binary log support to dice backup files

Installation of Mydumper

Mydumper using C language, using GLIBC library

Mydumper installation relies on packages, glibc, zlib, Pcre, Pcre-devel, GCC, gcc-c++, cmake, make, MySQL client library file installation dependency package, add MySQL client library file path to/et C/ld.so.conf, such as/usr/local/mysql/lib unpack the package into the directory, CMake. Make && make install

Two binaries generated after installation Mydumper and Myloader are located in the/usr/local/bin directory

Mydumper Backup mechanism

Mydumper Work Flow chart

The main steps summarize the main thread FLUSH TABLES with read lock, apply a global read-only lock to prevent DML statement writes, and ensure data consistency read the binary log file name and log write location of the current point-in-time and record it in the metadata file for even point restore use N (number of threads can be specified, default is 4) dump thread START TRANSACTION with consistent SNAPSHOT; Open read the same thing dump NON-INNODB tables, first export the main table of the non-things engine UNLOCK tables and things engine backup, release the global read-only lock dump InnoDB tables, based on things to export InnoDB table things knot Beam

Backup generated files All of the backup files are in one directory, and the directory can specify its own directory containing a metadata file

Records the binary log file name of the backup database at the point of Backup, the write location of the log,

If you are backing up from a library, you also record the binary log files and write locations for synchronization to the main library at the time of the backup, with two backup files per table:

DATABASE.TABLE-SCHEMA.SQL Table Structure File

Database.table.sql Table Data File

If you slice a table file, multiple backup data files are generated, and you can specify the number of rows or a specified size fragment

Use of Mydumper and Myloader

Mydumper parameters

-B,--database the database to be backed up, does not specify to back up all libraries-T,--tables-list the tables that need to be backed up, the names are separated by commas-O,--outputdir the backup file is lost The catalog-S, the number of bytes generated by the INSERT statement, the default 1000000-r,--rows the number of block rows specified when the table is--statement-size, specifying that this option closes--chunk-fi Lesize-f,--chunk-filesize blocks the table by size, the specified block size, unit is MB-C,--compress compressed output file-E,--build-empty-files as 
Fruit table data is empty, or produces an empty file (the default is countless only table structure file)-X,--regex is the same regular expression matching ' db.table '-I,--ignore-engines ignored storage engine, with all thick split -M,--no-schemas not to the table structure-K,--no-locks do not use temporary shared read-only locks, using this option will result in inconsistent data--less-locking reduce to I         Nnodb table Lock-imposed time (the mechanism of this pattern is detailed below)-L,--long-query-guard sets the long query timeout for blocking backups, in seconds, by default of 60 seconds (default Mydumper will exit after timeout)--kill-long-queries Kill the long query (do not exit)-B,--binlogs export binlog-d,--daemon enable daemon mode, daemon mode back up the database at a certain interval-I,- Snapshot-interval dump snapshot interval, default 60s, required in daemon mode-L,--logfile used log file name (Mydumper generated log), default use of standard output--tz-ut C Cross time Zone isThe option used does not explain the--SKIP-TZ-UTC ibid--use-savepoints use savepoints to reduce the lock time caused by acquisition metadata, the need for SUPER permissions--succe                  ss-on-1146 not increment error count and Warning instead of Critical in case of table doesn ' t exist-h,--host
Connected hostname-U,--user Backup User-P,--password password-p,--port port -S,--socket socket file when using socket-T,--threads number of backup threads opened, default is 4-c,--compress-protocol compression and  MySQL Communication data-V,--version display version number-V,--verbose output information mode, 0 = silent, 1 = errors, 2 = warnings, 3 = info, defaults to 2

Myloader Use parameters

-D,--directory                   backup Files folder-
Q,--queries-per-transaction the     number of queries executed each time, the default is 1000-
o,--overwrite-tables            If the table you want to restore exists, drop the table first, and use this parameter to back up the table structure-
B,--database                    the database you want to restore-
E,--enable-binlog               Enable binary log of restore data-
h,--host                        host-
u,--user                        restored user-
p,--password                    password-
p,--port                        port -
S,--socket                      socket file-
t,--threads                     Restore the number of threads used, default is 4
-C,--compress-protocol           compression protocol -
V,--version                     display version
-V,--verbose                     output mode, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default to 2

Use case backup game Library to/backup/01 folder and compress backup files

Mydumper-u root-p ###-H localhost-b game-c-o/backup/01

Back up all databases and back up the binary log files and back up to the/backup/02 folder

Mydumper-u root-p ###-H localhost-o/backup/02

Back up the Game.tb_player table and do not back up the table structure, back up to the/backup/03 folder

Mydumper-u root-p ###-H localhost-t tb_player-m-o/backup/03

Restores

Mysqlload-u root-p ###-H localhost-b game-d/backup/02

Less locking model of mydumper

Mydumper uses--less-locking to reduce lock latency, at which point the Mydumper execution mechanism is largely the main thread FLUSH TABLES with READ Lock (Global Lock) Dump thread START TRANSACTION with consistent SNAPSHOT; LL Dump thread lock tables NON-INNODB (thread internal lock) main thread UNLOCK tables ll dump thread dump NON-INNODB tables ll dump thread UNLOCK Non-innodb Dump thread dump InnoDB tables