Mydumper and mysqldump test backup and restore

Conditions:

One CentOS server, 2 cores 4G

mysql5.5 version

Same Backup data

1. Restore ceshi20141030.gz to the database after decompression

Mysql-u root-pqwer123

Mysql>use Ceshi;

Mysql>source webgame20141030

Result: Take 66 minutes to import, then compress it again with mysqldump backup for 17 minutes

Mysqldump-r--skip-tz-utc--default-character-set=utf8 ceshi-u root-pqwer123 | Gzip >/data/ceshi20141030.gz

2. Using Mysqldumper for backup

Mydumper-u root-p ' qwer123 '-B ceshi-0/data/0923/

Zip-r 0923.zip 0923

RM-RF 0923

Result: Use Mydumper back up 5 minutes, but need to compress, time takes 6 minutes, add up takes 11 minutes, backup is faster than mysqldump time more than 1/3,

Test decompression: After decompression size than mysqldump export compressed 2M, not compressed large 0.2G

3.mysqldumper for recovery

Delete the original database, and then create a new

myloader-d/home/0923/-o-b ceshi-u root-p ' qwer123 '

Result: Recovery data takes 42 minutes, 1/3 faster than mysqldump

Mydumper is support for multi-threaded work, the speed of the CPU is related to the use of high CPU consumption

Mysqldump only supports single-threaded operation and is less efficient, so

Mydumper parameter Introduction

-B,--database the library that needs to be backed up

-T,--tables-list the tables that need to be backed up, separated by

-O,--outputdir output directory

-S,--statement-size attempted size of INSERT statement in bytes, default 1000000

-R,--rows tries to split into a lot of block tables.

-C,--compress compressed output file

-e--build-empty-files generate an empty file even if the table has no data

-X,--regex supports regular expressions

-I,--ignore-engines ignored by the storage engine, separated by

-M,--no-schemas do not export table structure

-K,--no-locks does not perform a temporary shared read lock warning: This results in inconsistent backups

-L,--long-query-guard long query, default 60s

--kill-long-queries Kill long executed queries (instead of aborting)

-B,--binlogs export Binlog

-D,--daemon enable daemon mode

-I,--snapshot-interval dump snapshot interval, default 60s, required in daemon mode

-L,--logfile log file

-H,--host

-U,--user

-P,--password

-P,--port

-S,--socket

-T, number of threads used by--threads, default 4

-C,--compress-protocol use compression on MySQL connection

-V,--version

-V,--verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Myloader parameter Description:

-D,--directory import backup directory

-Q,--queries-per-transaction the number of queries executed per execution, default 1000

-O,--overwrite-tables if table exists delete table

-B,--database the library that needs to be restored

-E,--enable-binlog enable binary recovery data

-H,--host

-U,--user

-P,--password

-P,--port

-S,--socket

-T, number of threads used by--threads, default 4

-C, using compression on--compress-protocol connections

-V,--version

-V,--verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Mydumper Output File:

Metadata: Metadata records backup start and end times, and Binlog log file locations.

Table data: one file per table

Table schemas: Tables Structure files

Binary logs: Binary files are stored in the Binlog_snapshot directory when the--binlogs option is enabled

Daemon mode: In this model, there are five directories 0,1,binlogs,binlog_snapshot,last_dump.

Backup directory is 0 and 1, interval backup, if mydumper for some reason failed and still have a good snapshot,

When the snapshot is complete, last_dump points to the backup.

This article is from the "Operations" blog, please be sure to keep this source http://yangxiongchun.blog.51cto.com/1353054/1569799

Mydumper and mysqldump test backup and restore