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

Mydumper export and import have many advantages, especially in the aspect of parallelism, there are many articles online and do not explain too much, but with the release and enhancement of mysqlpump, the parallel advantage of mydumper will no longer exist.

The following is the daily use of mydumper, recorded for reference only:

Download the mydumper address and download the rpm package directly for installation:

Https://github.com/maxbube/mydumper/releases

The following download address is no longer recommended:

Https://launchpad.net/mydumper

Rpm-ivh mydumper-0.9.5-2.el7.x86_64.rpm

View the file path after installation:

[root@db1 soft] # rpm-ql mydumper

/ usr/bin/mydumper

/ usr/bin/myloader

Export the specified library:

Mydumper-u root-h localhost-p MYsql#123-e-B app-- threads=18-o / root/backup1/-- logfile=mydumper20180108.log

Resume the import:

Myloader-u root-p MYsql#123-h localhost-P 3306-B app-d / root/backup/-- threads=8

Export the specified table:

Mydumper-u root-h localhost-p MYsql#123-e-B app-T tasking bzies, syscards, users, rewards, bzstores, syscrafts, games-o / root/backup1/

The files exported by mydumper are divided into the following sections:

Database_name-schema-create.sql create database script

Script for database_name.table_name-schema.sql to create a table

Database_name.table_name.sql specific data insert sql statement

Metadata SHOW MASTER STATUS information, equivalent to mysqldump plus-- single-transaction-- master-data

For more detailed parameters of mydumper and myloader, please add help:

[root@db2 backup] # mydumper-- help

Usage:

Mydumper [OPTION?] Multi-threaded MySQL dumping

Help Options:

-help Show help options.

Application Options:

-B,-- database Database to dump

-T,-- tables-list Comma delimited table list to dump (does not exclude regex option)

-O,-- omit-from-file File containing a list of database.table entries to skip, one per line (skips before applying regex option)

-o,-- outputdir Directory to output files to

-s,-- statement-size Attempted size of INSERT statement in bytes, default 1000000

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

-F,-- chunk-filesize Split tables into chunks of this output file size. This value is in MB

-c,-- compress Compress output files

-e-- build-empty-files Build dump files even if no data available from table

-x,-- regex Regular expression for 'db.table' matching

-I-- ignore-engines Comma delimited list of storage engines to ignore

-N,-- insert-ignore Dump rows with INSERT IGNORE

-m,-- no-schemas Do not dump table schemas with the data

-d,-- no-data Do not dump table data

-G,-- triggers Dump triggers

-E-- events Dump events

-R,-- routines Dump stored procedures and functions

-W,-- no-views Do not dump VIEWs

-k,-- no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups

-- no-backup-locks Do not use Percona backup locks

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

-l,-- long-query-guard Set long query timer in seconds, default 60

-K-- kill-long-queries Kill long running queries (instead of aborting)

-D,-- daemon Enable daemon mode

-I,-- snapshot-interval Interval between each dump snapshot (in minutes), requires-- daemon, default 60

-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't exist

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

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

-- trx-consistency-only Transactional consistency only

-- complete-insert Use complete INSERT statements that include column names

-h,-- host The host to connect to

-u,-- user Username with the necessary privileges

-p,-- password User password

-a,-- ask-password Prompt For User password

-P,-- port TCP/IP port to connect to

-S,-- socket UNIX domain socket file to use for connection

-t,-- threads Number of threads to use, default 4

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

-V-- version Show the program version and exit

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

-- defaults-file Use a specific defaults file

-- ssl Connect using SSL

-- key The path name to the key file

-- cert The path name to the certificate file

-- ca The path name to the certificate authority file

-- capath The path name to a directory that contains trusted SSL CA certificates in PEM format

-- cipher A list of permissible ciphers to use for SSL encryption

[root@db2 backup] # myloader-- help

Usage:

Myloader [OPTION?] Multi-threaded MySQL loader

Help Options:

-help Show help options.

Application Options:

-d,-- directory Directory of the dump to import

-Q,-- queries-per-transaction Number of queries per transaction, default 1000

-o,-- overwrite-tables Drop tables if they already exist

-B,-- database An alternative database to restore into

-s-- source-db Database to restore

-e-- enable-binlog Enable binary logging of the restore data

-h,-- host The host to connect to

-u,-- user Username with the necessary privileges

-p,-- password User password

-a,-- ask-password Prompt For User password

-P,-- port TCP/IP port to connect to

-S,-- socket UNIX domain socket file to use for connection

-t,-- threads Number of threads to use, default 4

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

-V-- version Show the program version and exit

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

-- defaults-file Use a specific defaults file

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.