导航：首页 > 数据库 >

mysql中mysqldumper怎么用

发表于：2024-10-04 作者：千家信息网编辑

千家信息网最后更新 2024年10月04日，这篇文章主要为大家展示了"mysql中mysqldumper怎么用"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"mysql中mysqldumper怎么用"

千家信息网最后更新 2024年10月04日mysql中mysqldumper怎么用

这篇文章主要为大家展示了"mysql中mysqldumper怎么用"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"mysql中mysqldumper怎么用"这篇文章吧。

并行处理：

使用mydumper命令的局限在与他是一个单线程进程。但开源的mydumper是一个很好的替代。

mydumper（http://www.mysqldumper.org/）是一个在GNU GPLv3许可下发布的高性能MySQL备份和恢复工具集。mydumper是多线程的，他创建一个mysql备份就比随mysql发布的mysqldump工具要快得多。mydumper也有从源端服务器恢复二进制日志的能力。

mydumper的优点：

多线程，可以是转存数据快很多。

mydumper的输出已于管理和分析，因为他的表和元数据是分开的单独文件。

所有线程都维护有一直的快照，这边提供了精准的主从位置。

Mydumper支持Perl正则表达式，这样就既可以包括是数据库名和报名的模式匹配，也可以配置这种匹配。

通过名为myloader的多线程工具，mydumper工具集也可以从mydumper备份中恢复数据。

mydumper必须在源代码上进行编辑。这就需要带有C++编辑器的系统。另外还需要如下组件：

Cmake、带有开发包的Glib2、带有开发包的PCRE、mysql的客户端库和开发工具。

安装步骤如下：

依赖包：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

简单用法：

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

....................

当在冗长模式中运行时，会产生额外的输出，单所提供的信息中并不包括完整的输出目录：

[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_category`

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

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

** 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_transition`

** 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_category`

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

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

** 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_transition`

** 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]#

用法：

[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]#

通过正则表达式选项排除mysql和测试的模式对象：

mydumper --user root --regex '^(?!(mysql|test))'

压缩：默认情况下，所有处处文件都是不压缩的。但是通过使用-c选项，所有文件都可以被压缩。

[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生产多个与元数据、表数据、表模式和二进制日志相关的文件。

.metadata文件中保存着转存的开始和结束时间以及主二进制日志的位置。当执行转存时，一个.metadata文件边被创建到输出目录中：

备份目录中的素有文件：

[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]#

在使用mydumper的时候可以通过show processlist来监控线程。

可以使用两种不同的方式存储表数据：将所有表数据村委一个文件或者将一个表的数据块存为多个文件，如果未指定--row选项，则将为每个表创建一个文件，命令规则类似于database.table.sql。

关于mydumper生成文件的类型如下：

db_name.table_name-schema_name.sql ---表结构文件

db_name.table_name.sql ---表数据文件

db_name-schema-create.sql ---数据库创建脚本

mydumper的还原工具为：myloader，使用说明如下：

[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

恢复测试：

1、备份数据库：

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

2、删除其中的某一个数据库：

mysql> drop database sanxing;

Query OK, 2 rows affected (0.06 sec)

3、开始恢复其中的一个数据库：

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

4、检查恢复情况：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| guijian |

| mysql |

| sanxing |

| test |

+--------------------+

5 rows in set (0.00 sec)

注意原本有两个表的数据库，在恢复时指定了所有数据库备份的目录，此时所有的数据库表都被恢复到了，删除的库，（故此，在恢复的时候注意要使用单个数据库的备份，即什么样的备份能恢复什么样的数据库）

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>

单独测试恢复：

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

总用量 4

drwx------ 2 root root 4096 2月 24 15:28 export-20170224-152818

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

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

总用量 24

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

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

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

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

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

-rw-r--r-- 1 root root 143 2月 24 15:28 metadata

[root@d4jtarmsvurd01 export-20170224-152818]#

mysql> drop database guijian;

Query OK, 2 rows affected (0.01 sec)

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)