mydumper是开源的MySQL数据库逻辑备份工具,相比于mysqldump,它能够提供快速、高并发的数据导出与导入功能。
帮助文档:
下载地址:
安装(CentOS 6.5,mydumper 0.2.3):
yum install mydumper
常用方法:
1.导出数据
mydumper -B db_name -T tb_name1,tb_name2 -r 1000000 -t 8 -h host_name -u user --password='1234' -P 3306 -o dump_data_dir_name
2.导入数据
myloader -d dump_data_dir_name -B db_name -h host_name -u user -p "1234" -P 3306 -t 8 -e
注意:
mydumper 可以通过设置 -r 参数,配合 -t 参数让单表并发导出和导入。
myloader --enable-binlog 或者 -e 参数,默认是关闭的,这里有点坑,如果没有这个参数,导入的数据将不会写入binlog中,导致主从数据不一致。
mydumer参数:
--help
Show help text
--host, -h
Hostname of MySQL server to connect to (default localhost)
--user, -u
MySQL username with the correct privileges to execute the dump
--password, -p
The corresponding password for the MySQL user
--port, -P
The port for the MySQL connection.
--socket, -S
The UNIX domain socket file to use for the connection
--database, -B
Database to dump
--table-list, -T
A comma separated list of tables to dump
--threads, -t
The number of threads to use for dumping data, default is 4
--outputdir, -o
Output directory name, default is export-YYYYMMDD-HHMMSS
--statement-size, -s
The maximum size for an insert statement before breaking into a new statement, default 1,000,000 bytes
--rows, -r
Split table into chunks of this many rows, default unlimited
--compress, -c
Compress the output files
--compress-input, -C
Use client protocol compression for connections to the MySQL server
--build-empty-files, -e
Create empty dump files if there is no data to dump
--regex, -x
A regular expression to match against database and table
--ignore-engines, -i
Comma separated list of storage engines to ignore
--no-schemas, -m
Do not dump schemas with the data
--long-query-guard, -l
Timeout for long query execution in seconds, default 60
--kill-long-queries, -k
Kill long running queries instead of aborting the dump
--version, -V
Show the program version and exit
--verbose, -v
The verbosity of messages. 0 = silent, 1 = errors, 2 = warnings, 3 = info. Default is 2.
--binlogs, -b
Get the binlogs from the server as well as the dump files
--binlog-outdir, -d
The directory to output the binlog files into, default 'binlogs' in the export directory.
myloader参数:
--help
Show help text
--host, -h
Hostname of MySQL server to connect to (default localhost)
--user, -u
MySQL username with the correct privileges to execute the restoration
--password, -p
The corresponding password for the MySQL user
--port, -P
The port for the MySQL connection.
--socket, -S
The UNIX domain socket file to use for the connection
--threads, -t
The number of threads to use for restoring data, default is 4
--version, -V
Show the program version and exit
--compress-protocol, -C
Use client protocol compression for connections to the MySQL server
--directory, -d
The directory of the mydumper backup to restore
--database, -B
An alternative database to load the dump into
--queries-per-transaction, -q
Number of INSERT queries to execute per transaction during restore, default is 1000.
--overwrite-tables, -o
Drop any existing tables when restoring schemas
--enable-binlog, -e
Log the data loading in the MySQL binary log if enabled (off by default)
--verbose, -v
The verbosity of messages. 0 = silent, 1 = errors, 2 = warnings, 3 = info. Default is 2.