MySQL mysqldumper installation, use and parameters of the detailed

Mysqldumper This backup tool is a good point is obvious, that is, can be concurrent backup, faster than Mysqldump, another is the backup table is a separate file, restore a single table will be very convenient (table structure and data files are also separate a file).

First, download the installation

Download the latest Mysqldumper

Mysqldumper Download Address: https://launchpadlibrarian.net/185032423/mydumper-0.6.2.tar.gz

[root@mysql.dmc.com] $tar-zxvf mydumper-0.6.2.tar.gz
[Root@mysql.dmc.com bin] $yum install pcre-devel.x86_64
[root@mysql.dmc.com ~] $CD mydumper-0.6.2
[root@mysql.dmc.com mydumper-0.6.2] $mkdir bin
[root@mysql.dmc.com mydumper-0.6.2] $CD bin
[Root@mysql.dmc.com bin] $cmake. /
[Root@mysql.dmc.com bin] $make
[Root@mysql.dmc.com bin] $ll-rth mydumper myloader
-rwxr-xr-x. 1 root 149K 2 16:39 mydumper
-rwxr-xr-x. 1 root 51K 2 16:39 myloader

Second, mysqldumper parameter detailed

[Root@mysql.dmc.com bin]$./mydumper--help |more
Usage:
Mydumper [OPTION ...] multi-threaded MySQL dumping

Help Options:
-?,--Help Show Help options

Application Options:
-B,--database backup Database
-T,--tables-list backup table list, multiple tables separated by "," comma
-O,--outputdir specifies the backup directory
-S,--statement-size insert length, Unit is byte, default 1000000, this parameter cannot be too small, otherwise will report Row bigger than Statement_size for Tools.t_ser Verinfo
-R,--rows split table, how many rows are recorded as a chunk. The default is off.
-F,--chunk-filesize split table, backed up in multiple files, in MB
-C,--compress compressed backup
-E,--build-empty-files if the table has no data, or creates the file
-X,--regex matching database, table, such as Db.table1
-I,--ignore-engines ignores the table of the specified engine
-M,--no-schemas unprepared table structure
-K,--no-locks does not add temporary share read lock, this may cause data inconsistency
--less-locking InnoDB table, minimum time lock
-L,--long-query-guard set maximum query time, default is 60s
K,--kill-long-queries to kill the long query
-D,--daemon open daemon mode
-I,--snapshot-interval snapshot time, default is 60s
-L,--logfile log file, default is standard output
--TZ-UTC sets the time zone, which is used only when the backup is applied to different time zones, and--SKIP-TZ-UTC is closed by default.
--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
-H,--host host
-U,--user user
-P,--password password
-P,--port port
-S,--socket socket file
-T,--threads number of concurrent threads, default is 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

Iii. Methods of Use

The SQL file that is backed up, and the file begins with the database. Table name [-schema].sql, schema-created statement for table structure, no INSERT statement for table data.

[Root@mysql.dmc.com bin]$./mydumper--database=tools--outputdir=/root/mydumper-0.6.2/test/
[Root@mysql.dmc.com bin] $cd ot/mydumper-0.6.2/test/
[root@mysql.dmc.com Test] $ll-rth
Total 112K
-rw-r--r--. 1 root root 972 2 18:09 tools.django_session.sql
-rw-r--r--. 1 root 1.1K 2 18:09 tools.t_guid.sql
-rw-r--r--. 1 root root 423 2 18:09 tools.t_ftpserver-schema.sql
-rw-r--r--. 1 root root 325 2 18:09 tools.django_session-schema.sql
-rw-r--r--. 1 root 1.6K 2 18:09 tools.t_serverinfo-schema.sql
-rw-r--r--. 1 root 2 18:09 metadata

Metadata This document is recorded in the Binlog file and POS, you can use this information to build slave.

[root@mysql.dmc.com test] $cat metadata
Started dump at:2015-01-02 18:09:40
Show MASTER STATUS:
log:mysql-bin.000006
pos:4196075

Finished dump at:2015-01-02 18:09:40

Back up T_task and T_guid tables only

[root@mysql.dmc.com bin]$./mydumper--database=tools--outputdir=/root/mydumper-0.6.2/test/--tables-list=t_task,t _guid

Back up only the tables opened in T_server

[root@mysql.dmc.com bin]$./mydumper--database=tools--outputdir=/root/mydumper-0.6.2/test/--regex= "Tools.t_ Server* "

-b,–database can only specify one library backup, if you need to back up multiple databases at the same time:

[root@mysql.dmc.com bin]$./mydumper--outputdir=/root/mydumper-0.6.2/test/--regex= "beebol.*|tools.*"

–threads Concurrency Degree

[root@mysql.dmc.com bin]$./mydumper --outputdir=/root/mydumper-0.6.2/test/--regex= beebol.*|tools.* "--threads=8