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

This article mainly shows you "how to use mysqldumper in mysql", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to use mysqldumper in mysql" this article.

Parallel processing:

The limitation of using the mydumper command is that it is a single-threaded process. But open source mydumper is a good alternative.

Mydumper (http://www.mysqldumper.org/) is a high-performance MySQL backup and recovery toolset released under the GNU GPLv3 license. Mydumper is multithreaded, and it is much faster to create a mysql backup than the mysqldump tool released with mysql. Mydumper also has the ability to recover binary logs from the source server.

Advantages of mydumper:

Multithreading, which can be much faster to dump data.

The output of mydumper has been managed and analyzed because his tables and metadata are separate files.

All threads maintain a continuous snapshot, and this provides an accurate master-slave location.

Mydumper supports Perl regular expressions, which can either include a pattern match between the database name and enrollment, or configure such a match.

The mydumper toolset can also restore data from mydumper backups through a multithreaded tool called myloader.

Mydumper must be edited on the source code. This requires a system with a C++ editor. The following components are also required:

Cmake, Glib2 with development packages, PCRE with development packages, client libraries and development tools for mysql.

The installation steps are as follows:

Dependency package: Fedora, RedHat and CentOS: yum install glib2-devel* mysql-devel* zlib-devel* pcre-devel* openssl-devel*-y

Tar-zxvf mydumper-0.2.3.tar.gz

Cd mydumper-0.2.0.3/

Cmake.

Make

. / mydumper-help

Sudo cp mydumper / usr/local/bin

Simple usage:

Mkdir / mysql/bakcup/mydumper

Cd / mysql/backup/mydumper

Time mydumper

[root@d4jtarmsvurd01 mydumper_bak] # pwd

/ mysql/mydumper_bak

[root@d4jtarmsvurd01 mydumper_bak] # ls ex*

Metadata mysql.proc-schema.sql

Mysql-schema-create.sql mysql.procs_priv-schema.sql

Mysql.columns_priv-schema.sql mysql.servers-schema.sql

Mysql.db-schema.sql mysql.tables_priv-schema.sql

Mysql.db.sql mysql.time_zone-schema.sql

Mysql.event-schema.sql mysql.time_zone_leap_second-schema.sql

Mysql.func-schema.sql mysql.time_zone_name-schema.sql

Mysql.help_category-schema.sql mysql.time_zone_transition-schema.sql

Mysql.help_category.sql mysql.time_zone_transition_type-schema.sql

.

When running in verbose mode, additional output is generated, and the complete output directory is not included in the information provided by the order:

[root@d4jtarmsvurd01 mydumper_bak] # mydumper-v 3

* * Message: Connected to a MySQL server

* * Message: Started dump at: 2016-07-05 15:16:56

* * Message: Written master status

* * Message: Thread 1 connected using MySQL connection ID 1367

* * Message: Thread 2 connected using MySQL connection ID 1368

* * Message: Thread 3 connected using MySQL connection ID 1369

* * Message: Thread 4 connected using MySQL connection ID 1370

* * Message: Thread 2 dumping data for `mysql`.`db`

* * Message: Thread 1 dumping data for `mysql`.`columns _ priv`

* * Message: Thread 3 dumping data for `mysql`.`event`

* * Message: Empty table mysql.event

* * Message: Empty table mysql.columns_priv

* * Message: Thread 2 dumping data for `mysql`.`func`

* * Message: Thread 1 dumping data for `mysql`.`help _ roomy`

* * Message: Thread 3 dumping data for `mysql`.`help _ keyword`

* * Message: Thread 1 dumping data for `mysql`.`help _ room`

* * Message: Empty table mysql.func

* * Message: Thread 2 dumping data for `mysql`.`help _ topic`

* * Message: Thread 3 dumping data for `mysql`.`host`

* * Message: Thread 1 dumping data for `mysql`.`ndb _ binlog_ index`

* * Message: Empty table mysql.ndb_binlog_index

* * Message: Thread 1 dumping data for `mysql`.`plugin`

* * Message: Empty table mysql.plugin

* * Message: Thread 1 dumping data for `mysql`.`proc`

* * Message: Empty table mysql.proc

* * Message: Thread 1 dumping data for `mysql`.`procs _ priv`

* * Message: Empty table mysql.host

* * Message: Thread 3 dumping data for `mysql`.`servers`

* * Message: Empty table mysql.servers

* * Message: Thread 3 dumping data for `mysql`.`tables _ priv`

* * Message: Empty table mysql.procs_priv

* * Message: Thread 1 dumping data for `mysql`.`time _ zone`

* * Message: Empty table mysql.time_zone

* * Message: Thread 1 dumping data for `mysql`.`time _ zone_leap_ second`

* * Message: Empty table mysql.time_zone_leap_second

* * Message: Thread 1 dumping data for `mysql`.`time _ zone_ name`

* * Message: Empty table mysql.time_zone_name

* * Message: Thread 1 dumping data for `mysql`.`time _ zone_ accountion`

* * Message: Empty table mysql.tables_priv

* * Message: Thread 3 dumping data for `mysql`.`time _ zone_transition_ type`

* * Message: Empty table mysql.time_zone_transition

* * Message: Thread 1 dumping data for `mysql`.`user`

* * Message: Thread 1 dumping data for `sanxing`.`sanxing`

* * Message: Empty table mysql.time_zone_transition_type

* * Message: Thread 3 dumping data for `test`.`Guijian`

* * Message: Thread 3 dumping schema for `mysql`.`columns _ priv`

* * Message: Thread 1 dumping schema for `mysql`.`db`

* * Message: Thread 1 dumping schema for `mysql`.`event`

* * Message: Thread 1 dumping schema for `mysql`.`func`

* * Message: Thread 1 dumping schema for `mysql`.`help _ roomy`

* * Message: Thread 1 dumping schema for `mysql`.`help _ keyword`

* * Message: Thread 3 dumping schema for `mysql`.`help _ room`

* * Message: Thread 1 dumping schema for `mysql`.`help _ topic`

* * Message: Thread 3 dumping schema for `mysql`.`host`

* * Message: Thread 1 dumping schema for `mysql`.`ndb _ binlog_ index`

* * Message: Thread 1 dumping schema for `mysql`.`plugin`

* * Message: Thread 3 dumping schema for `mysql`.`proc`

* * Message: Thread 1 dumping schema for `mysql`.`procs _ priv`

* * Message: Thread 1 dumping schema for `mysql`.`servers`

* * Message: Thread 1 dumping schema for `mysql`.`tables _ priv`

* * Message: Thread 3 dumping schema for `mysql`.`time _ zone`

* * Message: Thread 1 dumping schema for `mysql`.`time _ zone_leap_ second`

* * Message: Thread 3 dumping schema for `mysql`.`time _ zone_ name`

* * Message: Thread 1 dumping schema for `mysql`.`time _ zone_ accountion`

* * Message: Thread 3 dumping schema for `mysql`.`time _ zone_transition_ type`

* * Message: Thread 3 dumping schema for `mysql`.`user`

* * Message: Thread 3 dumping schema for `sanxing`.`sanxing`

* * Message: Thread 3 dumping schema for `test`.`Guijian`

* * Message: Non-InnoDB dump complete, unlocking tables

* * Message: Thread 3 shutting down

* * Message: Thread 1 shutting down

* * Message: Thread 4 shutting down

* * Message: Thread 2 shutting down

* * Message: Finished dump at: 2016-07-05 15:16:56

[root@d4jtarmsvurd01 mydumper_bak] # ls

Export-20160705-151255 export-20160705-151656

[root@d4jtarmsvurd01 mydumper_bak] #

Usage:

[root@d4jtarmsvurd01 mydumper_bak] # 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,-- 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

-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

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

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

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

[root@d4jtarmsvurd01 mydumper_bak] #

Exclude mysql and tested pattern objects through the regular expression option:

Mydumper-- user root-- regex'^ (mysql | test))'

Compression: by default, all files everywhere are not compressed. But by using the-c option, all files can be compressed.

[root@d4jtarmsvurd01 mydumper_bak] # mydumper-c

[root@d4jtarmsvurd01 mydumper_bak] # ls-lrt

Total 12

Drwx- 2 root root 4096 Jul 5 15:12 export-20160705-151255

Drwx- 2 root root 4096 Jul 5 15:16 export-20160705-151656

Drwx- 2 root root 4096 Jul 5 15:20 export-20160705-152048

[root@d4jtarmsvurd01 mydumper_bak] # cd export-20160705-152048 /

[root@d4jtarmsvurd01 export-20160705-152048] # ls

Metadata mysql.proc-schema.sql.gz

Mysql-schema-create.sql.gz mysql.procs_priv-schema.sql.gz

Mysql.columns_priv-schema.sql.gz mysql.servers-schema.sql.gz

Mysql.db-schema.sql.gz mysql.tables_priv-schema.sql.gz

Mysql.db.sql.gz mysql.time_zone-schema.sql.gz

Mysql.event-schema.sql.gz mysql.time_zone_leap_second-schema.sql.gz

.

Mydumper produces multiple files related to metadata, table data, table schemas, and binary logs.

The .metadata file holds the start and end times of the rollover, as well as the location of the main binary log. When a rollover is performed, a .metadata file edge is created into the output directory:

Back up the files in the directory:

[root@d4jtarmsvurd01 export-20160705-151656] # ls

Metadata mysql.proc-schema.sql

Mysql-schema-create.sql mysql.procs_priv-schema.sql

Mysql.columns_priv-schema.sql mysql.servers-schema.sql

Mysql.db-schema.sql mysql.tables_priv-schema.sql

Mysql.db.sql mysql.time_zone-schema.sql

Mysql.event-schema.sql mysql.time_zone_leap_second-schema.sql

Mysql.func-schema.sql mysql.time_zone_name-schema.sql

Mysql.help_category-schema.sql mysql.time_zone_transition-schema.sql

Mysql.help_category.sql mysql.time_zone_transition_type-schema.sql

Mysql.help_keyword-schema.sql mysql.user-schema.sql

Mysql.help_keyword.sql mysql.user.sql

Mysql.help_relation-schema.sql sanxing-schema-create.sql

Mysql.help_relation.sql sanxing.sanxing-schema.sql

Mysql.help_topic-schema.sql sanxing.sanxing.sql

Mysql.help_topic.sql test-schema-create.sql

Mysql.host-schema.sql test.guijian-schema.sql

Mysql.ndb_binlog_index-schema.sql test.guijian.sql

Mysql.plugin-schema.sql

[root@d4jtarmsvurd01 export-20160705-151656] # more metadata

Started dump at: 2016-07-05 15:16:56

SHOW MASTER STATUS:

Log: mysql-bin.000002

Pos: 106

GTID: (null)

Finished dump at: 2016-07-05 15:16:56

[root@d4jtarmsvurd01 export-20160705-151656] # pwd

/ mysql/mydumper_bak/export-20160705-151656

[root@d4jtarmsvurd01 export-20160705-151656] #

Threads can be monitored through show processlist when using mydumper.

Table data can be stored in two different ways: one file for all table data villages or multiple files for data blocks of a table, and if the-- row option is not specified, a file is created for each table, with command rules similar to database.table.sql.

The types of mydumper generation files are as follows:

Db_name.table_name-schema_name.sql-table structure file

Db_name.table_name.sql-Table data file

Db_name-schema-create.sql-Database creation script

The restore tool for mydumper is myloader, and the instructions are as follows:

[root@d4jtarmsvurd01 mydumper_bak] # 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 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,-- 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

Restore Test:

1. Back up the database:

[root@d4jtarmsvurd01 mydumper_bak] # mydumper-u root-p root123-v 3

2. Delete one of the databases:

Mysql > drop database sanxing

Query OK, 2 rows affected (0.06 sec)

3. Start restoring one of the databases:

[root@d4jtarmsvurd01 mydumper_bak] # myloader-d / mysql/mydumper_bak/export-20170224-151158-o-B sanxing-u root-p 'root123'

4. Check the recovery:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | guijian |

| | mysql |

| | sanxing |

| | test |

+-+

5 rows in set (0.00 sec)

Note that for a database with two tables, the directory of all database backups is specified during the restore, and all database tables are restored to the deleted database. (therefore, pay attention to using a backup of a single database during recovery. That is, what kind of backup can restore what kind of database?

Mysql > use sanxing

Database changed

Mysql > show tables

+-+

| | Tables_in_sanxing |

+-+

| | columns_priv |

| | db |

| | event |

| | func |

| | guijian |

| | guijian01 |

| | help_category |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | jiehun |

| | ndb_binlog_index |

| | plugin |

| | proc |

| | procs_priv |

| | sanxing |

| | servers |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

25 rows in set (0.00 sec)

Mysql >

Test the recovery separately:

Mysql > use guijian

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_guijian |

+-+

| | test |

| | test01 |

+-+

2 rows in set (0.00 sec)

Mysql > exit

Bye

[root@d4jtarmsvurd01 mydumper_bak] # mydumper-u root-p root123-B guijian-v 3

* * Message: Connected to a MySQL server

* * Message: Started dump at: 2017-02-24 15:28:18

* * Message: Written master status

* * Message: Thread 1 connected using MySQL connection ID 2807

* * Message: Thread 2 connected using MySQL connection ID 2808

* * Message: Thread 3 connected using MySQL connection ID 2809

* * Message: Thread 4 connected using MySQL connection ID 2810

* * Message: Thread 1 dumping data for `Guijian`.`test`

* * Message: Thread 3 dumping schema for `Guijian`.`test`

* * Message: Thread 2 dumping data for `Guijian`.`test01`

* * Message: Thread 4 dumping schema for `Guijian`.`test01`

* * Message: Non-InnoDB dump complete, unlocking tables

* * Message: Thread 4 shutting down

* * Message: Thread 1 shutting down

* * Message: Thread 3 shutting down

* * Message: Thread 2 shutting down

* * Message: Finished dump at: 2017-02-24 15:28:18

[root@d4jtarmsvurd01 mydumper_bak] # ls-lrt

Total dosage 4

Drwx- 2 root root 4096 February 24 15:28 export-20170224-152818

[root@d4jtarmsvurd01 mydumper_bak] # cd export-20170224-152818 /

[root@d4jtarmsvurd01 export-20170224-152818] # ls-lrt

Total dosage 24

-rw-r--r-- 1 root root 68 February 24 15:28 guijian-schema-create.sql

-rw-r--r-- 1 root root 1110 February 24 15:28 guijian.test.sql

-rw-r--r-- 1 root root 2817 February 24 15:28 guijian.test-schema.sql

-rw-r--r-- 1 root root 1112 February 24 15:28 guijian.test01.sql

-rw-r--r-- 1 root root 2819 February 24 15:28 guijian.test01-schema.sql

-rw-r--r-- 1 root root 143 February 24 15:28 metadata

[root@d4jtarmsvurd01 export-20170224-152818] #

Mysql > drop database guijian

Query OK, 2 rows affected (0.01sec)

Mysql > exit

[root@d4jtarmsvurd01 mydumper_bak] # myloader-d / mysql/mydumper_bak/export-20170224-152818-o-B guijian-u root-p 'root123'

[root@d4jtarmsvurd01 mydumper_bak] #

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show tables

ERROR 1046 (3D000): No database selected

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | guijian |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > use guijian

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_guijian |

+-+

| | test |

| | test01 |

+-+

2 rows in set (0.00 sec)

The above is all the contents of the article "how to use mysqldumper in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.