MySQL高效的逻辑备份工具mydumper & myloader

mydumper是开源的MySQL数据库逻辑备份工具，相比于mysqldump，它能够提供快速、高并发的数据导出与导入功能。

帮助文档：

http://centminmod.com/mydumper.html

下载地址：

https://launchpad.net/mydumper

安装（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.