Mydumper of MySQL multithreaded logical Backup tool

Download Installation

Yum-y Install CMake glib2 pcre pcre-devel mysql-devel

wget https://launchpad.net/mydumper/0.6/0.6.2/+download/mydumper-0.6.2.tar.gz

Tar XF mydumper-0.6.2.tar.gz

CD mydumper-0.6.2

CMake.

Make && make install

Note: If only help can be compiled this way make doc_html

Two binary command/usr/local/bin/mydumper (Backup) and/usr/local/bin/myloader (recovery) generated after installation

Mydumper Characteristics

• Multi-threaded backup to generate multiple backup files

• Read lock on MyISAM table, blocking DML statements

• Ensure backup Data consistency

• Support for file compression and export Binlog

• Support Multi-threaded recovery

• Supports daemon mode, timed snapshots and continuous binary logs

• Support for regular matching

• Restore support whether to enable Binlog and specify the size of things (how many inserts)

• Cannot back up views and triggers

• Backup stored procedures and functions are available when backing up the entire library, and cannot be backed up by a single-library backup

mydumper backup mechanism

http://www.mysqldumper.net/

Summary of main steps

2. The main thread, FLUSH TABLES with read lock, applies a global read-only lock to prevent DML statement writes, ensuring data consistency

4. Reads the binary log file name and log write location of the current point in time and logs it in the metadata file for even point recovery

6. N (number of threads can be specified, default is 4)dump thread START TRANSACTION with consistent SNAPSHOT; start reading the same thing

8. Dump Non-innodb tables, first export the non-Object engine table

10. Main thread UNLOCK TABLES when the non-object engine is finished backing up, release the global read-only lock

12. Dump InnoDB tables, export InnoDB table based on things

14. The end of Things

backing up the generated files

1. All backup files in one directory, directories can be specified by themselves

2. The directory contains 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 the library, you will also record the backup synchronization to the binary log file from the library and the write location

3. There are two backup files per table:

DATABASE.TABLE-SCHEMA.SQL Table Structure File
Database.table.sql Table Data File
If the table file is fragmented, multiple backup data files will be generated, either specifying the number of rows or specifying the size of the Shard

mydumper Parameter Explanation###############################################

-B,--database the database to be backed up, not specifying to back up all libraries
-T,--tables-list a table that needs to be backed up, with a comma separated name
-O,--outputdir directory for backup file output
-S,--statement-size the number of bytes generated by the INSERT statement, default 1000000
-R,--rows specifies the number of block rows when the table is divided by row, specifying this option turns off--chunk-filesize
-F,--chunk-filesize specifies the block size, in megabytes, when the table is divided by size
-C,--compress compressed output file
-E,--build-empty-files if the table data is empty or generates an empty file (the default is no more than the table structure file)
-X,--regex is matched with regular expression ' db.table '
-I,--ignore-engines ignored storage engine, separated by commas
-M,--no-schemas no table structure
-K,--no-locks does not use temporary shared read-only locks, and using this option can result in inconsistent data
--less-locking reduces the time to apply lock on the InnoDB table (this mode is explained in detail below)
-L,--long-query-guard set long query timeout for blocked backups, in seconds, default is 60 seconds (default Mydumper will exit after timeout)
--kill-long-queries Kill long Query (do not exit)
-B,--binlogs export Binlog
-D,--daemon enable daemon mode, daemon mode backs up the database at some interval
-I,--snapshot-interval dump snapshot interval, default 60s, required in daemon mode
-L, log file name used by--logfile (log generated by mydumper), standard output is used by default
--TZ-UTC Cross-zone is the option that is used and does not explain
--SKIP-TZ-UTC Ibid.
--use-savepoints uses savepoints to reduce the lock time caused by acquisition metadata and requires SUPER privilege
--success-on-1146 not increment error count and Warning instead of Critical in case of table doesn ' t exist
-H,--host the host name of the connection
-U,--user the user used for the backup
-P,--password password
-P,--port port
-S,--socket socket file when using socket communication
-T,--threads number of backup threads opened, default is 4
-C,--compress-protocol compressing data with MySQL communication
-V,--version display version number
-V,--verbose output information mode, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default = 2

#################################################################

Myloader parameter explanation

-D,--directory directory for backup files
-Q,--queries-per-transaction the number of queries executed per object, default is 1000
-O,--overwrite-tables if the table you want to restore exists, drop the table first, use that parameter, and back up the table structure when you need to back up
-B,--database the database that needs to be restored
-E,--enable-binlog enable binary logging of restore data
-H,--host host
-U,--user restored user
-P,--password password
-P,--port port
-S,--socket socket file
-T,--threads the number of threads used by the restore, 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 = 2

Use case

1. Export the Table1 and TABLE2 table structures and data below the Test1 library
   Mydumper-h 10.1.1.1-uroot-proot-b test1-t table1,table2-t 8-r 100000-c--less-locking-v Per.log-o/tmp/test_table1_table2/
   Increase-M parameter if only export data

2. Import the exported data into the Test2 Library of the 10.1.1.2 server
   Myloader-h 10.1.1.2-uroot-proot-b test2-e-T 8-d/tmp/test_table1_table2/--overwrite-tables-v 3
   

Note : Background records the time of export import

Enter the screen command
Then (time myloader-h 10.1.1.2-u user-p ' ap '-B mnew_gz-e-T 8-d/tmp/m_members/--overwrite-tables-v 3) 2>/tmp/ Im.log
Last Ctrl+a+d

Mydumper of MySQL multithreaded logical Backup tool