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

This article mainly introduces the multithreaded backup tool mydumper how to use, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Installation

Click (here) to collapse or open

Dependent installation

Debian

Apt-get install libglib2.0-dev

Apt-get install libmysqlclient-dev

Apt-get install libghc-zlib-dev

Apt-get install libpcre++-dev

Centos

Yum install glib2-devel mysql-devel zlib-devel pcre-devel

# wget https://launchpad.net/ubuntu/+archive/primary/+files/mydumper_0.9.1.orig.tar.gz

# tar xzvf mydumper_0.9.1.orig.tar.gz

# cd mydumper-0.9.1/

# cmake.

# make & & make

Description

Click (here) to collapse or open

# mydumper-help

Usage:

Mydumper [OPTION...] Multi-threaded MySQL dumping

Help Options:

-help Show help options.

Application Options:

-B,-- database (specify the database to export)

-T,-- tables-list (specify the table to be exported, multiple tables are separated by commas, regular expressions are not supported)

-o,-- outputdir (specify output directory)

-s,-- statement-size (the length of the exported insert statement is specified, in bytes, default 100wziji)

-r,-- rows (that is, horizontal split table for storage, per row, this option invalidates-- chunk-filesize)

-F,-- chunk-filesize (that is, horizontal split table for storage, unit MB)

-c,-- compress (compressed output file)

-e,-- build-empty-files (tables with empty data also generate files)

-x,-- regex (regular expression matches' db.table')

-I,-- ignore-engines (sets the storage engine table to be ignored, multiple separated by commas)

-m,-- no-schemas (do not export tables with data in the schema library)

-d,-- no-data (only export table structure, no data)

-G,-- triggers (export trigger)

-E,-- events (export event)

-R,-- routines (export stored procedure)

-k,-- no-locks (do not temporarily use read locks during data export). WARNING: This will cause inconsistent backups

-- less-locking Minimize locking time on InnoDB tables.

-l,-- long-query-guard (define how long the query is long querY, default is 60, unit s)

-K,-- kill-long-queries (kill minus long query) (instead of aborting)

-D,-- daemon (using daemon mode)

-I,-- snapshot-interval (interval setting between dump snapshots is required with-- daemon parameter start. Default is 60, unit minutes)

-L-- logfile Log file name to use, by default stdout is used

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 Use savepoints to reduce metadata locking issues, needs SUPER privilege

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

-- lock-all-tables Use LOCK TABLE for all, instead of FTWRL (lock all tables)

-U-- updated-since Use Update_time to dump only tables updated in the last U days

-- trx-consistency-only Transactional consistency only (transaction consistency)

-t,-- threads Number of threads to use, default 4 (number of backup threads, default is 4)

-C,-- compress-protocol Use compression on the MySQL connection (connection compression)

-v,-- verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Default connection parameters

-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

test

Click (here) to collapse or open

Mydumper-u root-p xxx-B db_slave-e-o db_slave_dir

The catalog file is as follows:

Each of the documents states

Build database file db_slave-schema-create.sql (common format dbname-schema-create.sql)

Click (here) to collapse or open

# cat db_slave-schema-create.sql

CREATE DATABASE `db_ slave` / *! 40100 DEFAULT CHARACTER SET latin1 * /

Table creation file db_slave.t_check_flag-schema.sql (common format dbname.tbname-schema.sql)

Click (here) to collapse or open

# cat db_slave.t_check_flag-schema.sql

/ *! 40101 SET NAMES binary*/

/ *! 40014 SET FOREIGN_KEY_CHECKS=0*/

CREATE TABLE `tchecking _ checking `(

`db_ ip`char (16) NOT NULL DEFAULT''

`check_ room`tinyint (4) DEFAULT'0' COMMENT'1 monitor 0 not'

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Data file db_slave.t_check_flag.sql (common format dbname.tbname.sql)

Click (here) to collapse or open

# cat db_slave.t_check_flag.sql

/ *! 40101 SET NAMES binary*/

/ *! 40014 SET FOREIGN_KEY_CHECKS=0*/

/ * 40103 SET TIME_ZONE='+00:00' * /

INSERT INTO `tasking checkout _ checking `VALUES

("192.168.111.129", 1)

("192.168.111.130", 1)

(192.168.111.26 ", 1)

Export the information file metadata as a whole (here the start time, end time, and binlog information at the beginning of the export are recorded, which is convenient for building slave libraries)

Click (here) to collapse or open

# cat metadata

Started dump at: 2016-11-19 13:38:56

SHOW MASTER STATUS:

Log: 2104.014976

Pos: 85965870

GTID: (null)

Finished dump at: 2016-11-19 13:38:56

Process description

Open general_log and you can see the following log

Click (here) to collapse or open

148901 Connect root@localhost on db_slave

148901 Query SET SESSION wait_timeout = 2147483

148901 Query SET SESSION net_write_timeout = 2147483

148901 Query SHOW PROCESSLIST

148901 Query FLUSH TABLES WITH READ LOCK

148901 Query SELECT @ @ tokudb_version

148901 Query START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * /

148901 Query / *! 40101 SET NAMES binary*/

148901 Query SHOW MASTER STATUS

148901 Query SELECT @ @ gtid_current_pos

148901 Query SELECT @ @ default_master_connection

148901 Query SHOW SLAVE STATUS

148902 Connect root@localhost on

148902 Query SET SESSION wait_timeout = 2147483

148902 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

148902 Query START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * /

148902 Query / *! 40103 SET TIME_ZONE='+00:00' * /

148902 Query / *! 40101 SET NAMES binary*/

148903 Connect root@localhost on

148903 Query SET SESSION wait_timeout = 2147483

148903 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

148903 Query START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * /

148903 Query / *! 40103 SET TIME_ZONE='+00:00' * /

148903 Query / *! 40101 SET NAMES binary*/

148904 Connect root@localhost on

148904 Query SET SESSION wait_timeout = 2147483

148904 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

148904 Query START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * /

148904 Query / *! 40103 SET TIME_ZONE='+00:00' * /

148904 Query / *! 40101 SET NAMES binary*/

148905 Connect root@localhost on

148905 Query SET SESSION wait_timeout = 2147483

148905 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

148905 Query START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * /

148905 Query / *! 40103 SET TIME_ZONE='+00:00' * /

148905 Query / *! 40101 SET NAMES binary*/

148901 Init DB db_slave

148901 Query SHOW TABLE STATUS

148901 Query SHOW CREATE DATABASE `db_ slave`

148901 Query UNLOCK TABLES / * FTWRL * /

148902 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `db_ slave`.`t _ check_ room`

148903 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `db_ slave`.`t _ master_ instance`

148901 Quit

148904 Query SHOW CREATE TABLE `db_ slave`.`t _ check_ room`

148905 Query SHOW CREATE TABLE `db_ slave`.`t _ master_ instance`

① connects to the database

② determines the relevance of long queries,-- long-query-guard and-- kill-long-queries

③ flush tables with read locks, backup of the mysiam table is completed immediately unlock tables; for the innodb table, mydumper uses single transaction for backup. Therefore, the size and number of mysiam tables affect the backup time of mysiam, and then whether the business is read-only.

④ is created with the number of threads specified by the-t parameter, and works on the worker child thread

⑤ determines the current table to be exported and adds the table to be exported to the queue.

⑥ backup completes and exits (where mysiam completion will unlock tables;)

Summary:

① can use the-t parameter to specify the number of threads, and multithreading can speed up simultaneous backup, but it also depends on the IO performance of the disk.

The ② export process requires a read lock, which is recommended during the business trough.

When ③ backs up from the library, the metadata also records the binlog file and location to which the current slave library has been executed.

④ scheduled task backups can use the-v parameter to output the appropriate level of information as a backup log.

⑤ when the library is too large, it is recommended to use the-c parameter to compress the results.

When the ⑥ single table is too large, you can use-r or-F parameter decomposition to avoid the backup time of the single table is too long (if it is not disassembled, it will be exported by one thread)

Thank you for reading this article carefully. I hope the article "how to use the multithreaded backup tool mydumper" shared by the editor will be helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.