Shulou(Shulou.com)06/01 Report--

Background: comparison of three logical backup tools for mysqldump mysqlpump mydumper

Introduction to mysqlpump (transferred): http://www.cnblogs.com/zhoujinyi/p/5684903.html

This article refers to: http://blog.itpub.net/22664653/viewspace-2132759/

1. 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 (see the end of the flow chart) 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

Source: http://blog.itpub.net/22664653/viewspace-2132759/

3 installation and use

1.yum install glib2-devel mysql-devel zlib-devel pcre-devel zlib gcc-c++ gcc cmake-y

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

3.tar xf mydumper-0.9.1.tar.gz

4.cd mydumper-0.9.1/

5.cmake .6.make & & make install

After installation, two files are generated:

/ usr/local/bin/mydumper

/ usr/local/bin/myloader

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 doesnmyloader 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

Comparison between mydumper and mysqldump: mydumper: mydumper-u root-p rootroot-S / data/mysql_3306/mysql.sock-B homework-o.

Restore command # myloader-u root-p rootroot-s / data/mysql_3306/mysql.sock-d / data/10.0.6.11/2017-02-22/homework-B homework

Mysqldump:

/ opt/mysql_3306/bin/mysqldump-- socket=/data/mysql_3306/mysql.sock-uroot-prootroot-T. Homework

Because the file is relatively small and there is no comparison of the results, we will test it later.

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.