Shulou(Shulou.com)05/31 Report--

This article is about how to use the mydumper tool in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

A preface

Mydumper has the following characteristics

1 supports multi-thread data export, which is faster than mysqldump.

2 consistent backup is supported. Using FTWRL (FLUSH TABLES WITH READ LOCK) will block DML statements and ensure the consistency of backup data.

3 support to compress the exported files to save space.

(4) support multi-thread recovery.

5 support for working in daemon mode, scheduled snapshots and continuous binary logs

6 support cutting backup files according to the specified size.

(7) the data is separated from the table statement.

Two principles

Refer to a picture to introduce the working principle of mydumper

The main working steps of mydumper

1 the main thread FLUSH TABLES WITH READ LOCK, which imposes a global read-only lock to prevent the writing of DML statements and ensure the consistency of data

2 read the binary log file name and the location of log writes at the current point in time and record them in the metadata file for even point recovery

3 START TRANSACTION WITH CONSISTENT SNAPSHOT; starts read consistency transaction

4 enable N (number of threads can be specified, default is 4) dump thread export table and table structure

5 back up tables of non-transaction type

6 main thread UNLOCK TABLES, after backing up non-transactional tables, release the global read-only lock

7 dump InnoDB tables, export InnoDB table based on things

8 things come to an end

3 installation and use

3.1 installation

Mydumper is written in c language and needs to be compiled and installed, so the compilation tools need to be installed.

Yum install glib2-devel mysql-devel zlib-devel pcre-devel zlib gcc-c++ gcc cmake-y

Wget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz

Tar xf mydumper-0.9.1.tar.gz

Cd mydumper-0.9.1/

Cmake.

Make & & make install

Generally, you will encounter the problem that you can not find mysql-libraries. You can refer to the answer of stackoverflow. If you can't solve it, it may be the problem of your own MySQL installation directory. For example, my own installation directory is / opt/mysql/.

You need to make a soft connection.

Ln-s / opt/mysql/lib/libperconaserverclient.so / usr/lib64/libperconaserverclient.so

3.2 Parameter description

Common parameters of mydumper

-B,-- dbname to be exported by database

-T,-- the table name that tables-list needs to export. Multiple tables to be exported need to be separated by commas, T1 [, T2, T3.]

-o,-- the directory where the outputdir exported data files are stored, which is automatically created by mydumper.

-s,-- statement-size generates the number of bytes of the insert statement. Default is 1000000 bytes.

-r,-- rows Try to split tables into chunks of this many rows. This option turns off-chunk-filesize

-F,-- the size of the chunk-filesize cutting table file. The default unit is MB, if the table is larger than

-c,-- compress compressed exported files

-e,-- build-empty-files creates files for tables even if they are empty

-x,-- regex uses regular expressions to match db.table

-I,-- Storage engine ignored by ignore-engines, with multiple values separated by commas

-m,-- no-schemas only exports data, not database-building table statements

-d,-- no-data only exports the table structure and creates the statement of db

-G,-- triggers export trigger

-E,-- events export events

-R,-- routines exports stored procedures and functions

-k,-- no-locks does not perform temporary read-only locks, which can lead to inconsistent backups. WARNING: This will cause inconsistent backups

-- less-locking minimizes locking time on innodb tables-- butai

-l,-- long-query-guard sets the time standard for long-time execution of sql

-K,-- sql kill that kill-long-queries will execute for a long time

-D,-- daemon executes as a daemon

-I,-- the interval between snapshot-interval 's creation of exported snapshots, which defaults to 60s, which is useful only when the daemon is executing.

-L,-- logfile specifies the log file of the mydumper output, which uses the console output 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 between servers with different time zones, defaults to on use-- skip-tz-utc to disable.

-- skip-tz-utc

-- use-savepoints uses savepoints to reduce MDL lock events requires SUPER permission

-- success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn

Myloader usage parameters

-d,-- folder for directory backup files

-Q,-- queries-per-transaction the number of queries executed per transaction. Default is 1000.

-o,-- overwrite-tables if the table to be restored exists, drop the table first, use this parameter, and back up the table structure when you need to back up

-B,-- the database that database needs to restore

-e,-- enable-binlog enables binary logging of restored data

-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,-- the number of threads used by threads 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 is 2

Four methods of use

# Export the entire library

Mydumper-u root-S / srv/my3308/run/mysql.sock-B trade_platform-o / data/trade_platform

# only the ddl statement of exporting platform does not contain data to the specified directory / data/platform

Mydumper-u root-S / srv/my3308/run/mysql.sock-B platform-m-o / data/platform

# Files exported in a compressed manner

Mydumper-u root-S / srv/my3308/run/mysql.sock-B trade_platform-c-o / data/trade_platform

Backup files are compressed in the. gz format

# ls

Metadata trade_platform.config.sql.gz trade_platform.trade_order-schema.sql.gz

Trade_platform.config-schema.sql.gz trade_platform-schema-create.sql.gz trade_platform.trade_order.sql.gz

# use regular expressions

Mydumper-u root-S / srv/my3308/run/mysql.sock-regex=' ^ (?! (mysql | test))'- o / data/bk20170120

Where the regular expression can be

-- regex=order.* exports all tables at the beginning of order

Files exported by mydumper

[root@rac4 17:27:02 / data/platform]

# ls

Metadata platform.config.sql platform.order.sql

Files exported by mydumper are divided into

Metadata: contains the binlog bit information at the time of export. If gtid is enabled, the gtid information is recorded.

Started dump at: 2017-01-2017: 26:53

SHOW MASTER STATUS:

Log: mysql-bin.000025

Pos: 505819083

GTID:

Finished dump at: 2017-01-2017: 27:02

Db.table.sql: data files, insert statements

Db.table-schema.sql: contains table statements

Db-schema.sql: contains database building statements

Note that the-- binlogs parameter is removed in version 0.9.1, so there are fewer files related to enabling the binlogs parameter.

Thank you for reading! This is the end of this article on "how to use mydumper tools in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.