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)

# 导出指定的表，多个表之间用逗号分隔。db1.t1,db1,t2,db2,t1,db3,t1

-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

# 指定导出SQL文件中每个insert语句的大小，默认每个insert 1000000value

-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

# 压缩备份，导出SQL文件以sql.gz结尾

-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

# 导出的SQL文件加上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

# 不使用 Percona backup locks

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

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

# 设置执行多长时间SQL将会被KILL

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

# kill长时间执行的SQL

# -D, --daemon Enable daemon mode

以守护进程进行备份，默认每60分钟备份一次

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

# 每个备份之间的时间间隔（以分钟为单位）需要--daemon，默认值为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.

# 备份时时区设置，默认在备份文件开始添加SET TIME_ZONE='+00:00'语句

--skip-tz-utc

# 禁止在备份文件中输出SET TIME_ZONE语句

--use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege

# 使用savepoints来减少元数据锁定问题，需要SUPER权限

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

# 指定mysql my.cnf配置文件

mysql> show global variables like '%sql_mode%';

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

| Variable_name | Value                                                                                                                  |

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

| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

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

mysql> set global sql_mode='';

执行完myloader之后再恢复设置

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

myloader参数介绍：

-d, --directory # 导入备份目录

-q, --queries-per-transaction #每次执行的查询数量, 默认1000

-o, --overwrite-tables #如果表存在删除表

-B, --database  # 需要还原的库

-s, --source-db Database to restore  # 从备份文件把-s 指定的库恢复到数据库中

-e, --enable-binlog # 启用二进制恢复数据。恢复时是否开启binlog，默认导入时关闭binlog（有主从环境注意如果-e 那么从库也会一起恢复，但是速度较慢）

-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  #指定socket file

-t, --threads Number of threads to use, default 4  # 指定线程数，默认为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