mydumper使用参考

mydumper导出和导入有诸多优点，特别是在并行方面，网上文章很多，不做过多解释，不过随着mysqlpump的发布和增强，mydumper在并行方面优势将不复存在，
以下为mydumper的日常使用，仅记录做参考：
mydumper下载地址，直接下载rpm包进行安装：
https://github.com/maxbube/mydumper/releases

以下下载地址已经不推荐：
https://launchpad.net/mydumper

rpm -ivh mydumper-0.9.5-2.el7.x86_64.rpm
查看安装后的文件路径：
[root@db1 soft]# rpm -ql mydumper
/usr/bin/mydumper
/usr/bin/myloader

导出指定的库：
mydumper -u root -h localhost -p MYsql#123 -e -B app –threads=18 -o /root/backup1/ –logfile=mydumper20180108.log
恢复导入：
myloader -u root -p MYsql#123 -h localhost -P 3306 -B app -d /root/backup/ –threads=8

导出指定的表：
mydumper -u root -h localhost -p MYsql#123 -e -B app -T t_bz_sys_users,t_bz_sys_gallery -o /root/backup1/

mydumper导出的文件分为如下几部分：
database_name-schema-create.sql 创建数据库脚本；
database_name.table_name-schema.sql 创建表的脚本
database_name.table_name.sql 具体的数据插入sql语句
metadata SHOW MASTER STATUS信息，相当于mysqldump加上–single-transaction –master-data

mydumper和myloader详细的使用参数请加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

相关推荐: 【MySQL】【安全】探讨MySQL备份所需最小权限

1.背景：2.解决开发云主机域名思路：3.操作：备注：相关推荐: 什么是SpringBoot事务什么是SpringBoot事务？很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 重要概…