MySQL Multi-threaded backup recovery Tool Mydumper,myloder

Mydumper is a multithreaded backup tool that opens 4 threads by default and is faster than mysqldump

mydumper Backup, the data and table structure is separate, such as when the test table is backed up, generates Test.sql files and test.schema.sql files.

Myloader The binary log is not logged by default, even if you are sql_log_bin=on.

To prepare the installation media:

Download mydumper-0.6.2.tar.gz

: Https://launchpad.net/mydumper

To install a dependency package:

# yum Install glib2-devel mysql-devel zlib-devel pcre-devel

Compiling the installation

# tar ZXVF mydumper-0.6.2.tar.gz

# CD mydumper-0.6.2

# mkdir Bin

# CD Bin

# CMake. /

# make

# make Install

After installation, you can see the/usr/local/bin directory under the Mydumper and Myloader two commands

# ll/usr/local/bin/

Total 188

-rwxr-xr-x 1 root root 132868 3 22:51 mydumper

-rwxr-xr-x 1 root root 47491 3 22:51 myloader

#./mydumper--database=test--outputdir=/data/backup/test

./mydumper:error while loading shared libraries:libmysqlclient.so.18:cannot open Shared object file:no such file or di Rectory

Create a soft link to resolve this error:

# ln-s/usr/local/mysql/lib/libmysqlclient.so.18/usr/lib/

# ldconfig-v

Export:

# Mydumper--database=test--outputdir=/data/backup/test

Import:

# myloader-b test-d/data/backup/test

Using backups to build from a library:

Cd/data/backup/test

There is a metadata file, this file is the current Binlog file and POS, you can use this information to build from the library

Mydumper parameter Description:

-B,--database backing up the database

-T,--tables-list backup table list, multiple tables with "," comma separated

-O,--outputdir specify backup directory

-S,--statement-size insert length, Unit is byte, default 1000000, this parameter can not be too small, otherwise will be reported Row bigger than statement_size for Tools.t_ser Verinfo

-R,--rows partition table, how many rows are recorded as a chunk. The default is off

-F,--chunk-filesize partitioned tables, backed up in multiple files, in megabytes

-C,--compress compression backup

-E,--build-empty-files if the table has no data, create the file

-X,--regex matches databases, tables, such as Db.table1

-I,--ignore-engines ignores tables for the specified engine

-M,--no-schemas no table structure

-K,--no-locks does not add temporary share read lock, which may cause inconsistent data

--less-locking InnoDB table, minimum time lock

-L,--long-query-guard set the 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 off 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

This article is from the "Always on the Road" blog, please be sure to keep this source http://chenql.blog.51cto.com/8732050/1681643

MySQL Multi-threaded backup recovery Tool Mydumper,myloder