Introduction to the use of MySQL database backup tool Mydumper

Mydumper is a MySQL database server backup tool, which is much faster than mysqldump that comes with MySQL. It also has the ability to obtain remote server binary log files at the same time as the dump.

Advantages of Mydumper

• Parallel capabilities (hence high speed) and performance (efficient code avoids overhead Character set conversion process of CPU processing power)

• Easier to manage output (each table corresponds to an independent file, dump metadata, etc., easy to view/parse the data)

• Consistency: Maintain snapshots across threads, provide accurate master-slave log positioning, etc.

• Manageability: Supports using PCRE to include/exclude specified databases and tables (LCTT Annotation: PCRE, Perl Compatible Regular Expression, Perl Compatible Regular Expression)

Installing mydumper on Ubuntu

Open a terminal and run the following command

sudo apt-get install mydumper

Using Mydumper

Syntax

mydumper [options]

Application options:

• -B, –database Database to dump

• -T, –tables-list Comma-separated list of dump tables (not will be excluded by regular expressions)

• -o, –outputdir directory to save the output file

• -s, –statement-size insert statement The byte size, the default is 1000000 bytes

• -r, –rows cuts the table into blocks according to the number of rows

• -c, –compress compresses the output file

• -e, –build-empty-files also outputs the empty table file

• -x, –regex matches’ Regular expression for db.table'

• -i, –ignore-engines Comma-separated list of ignored storage engines

• - m, –no-schemas Do not dump table schema

• -k, –no-locks Do not perform temporary shared read locks. Warning: This will cause backup inconsistencies

• -l, –long-query-guard Set the timer seconds for long queries, the default is 60 seconds

• –kill-long-queries Kill long queries (instead of exiting the program)

• -b, –binlogs Take a snapshot of the binary log file and dump the data

• -D, –daemon Enable daemon mode

• -I, –snapshot-interval The interval between each dump snapshot (minutes) , you need to enable –daemon, the default is 60 minutes

• -L, –logfile the name of the log file, the default is stdout

• -h, –host The host to connect to

• -u, –user Username with dump permission

• -p, –password User password

• -P, –port TCP/IP port for connection

• -S, –socket Unix socket file used for connection

• -t, –threads The number of threads to use, the default is 4

• ##-C, –compress-protocol Use compression on the MySQL connection

• -V, –version View program version number

• -v, –verbose output information level, 0 = silent, 1 = errors, 2 = warnings, 3 = info, the default is 2

##Mydumper Example

mydumper /
--database=$DB_NAME /
--host=$DB_HOST /
--user=$DB_USER /
--password=$DB_PASS /
--outputdir=$DB_DUMP /
--rows=500000 /
--compress /
--build-empty-files /
--threads=2 /
--compress-protocol

Mydumper Description of output data

Mydumper does not directly specify the output file. Instead, it outputs to a file in the folder. The –outputdir option specifies the directory name to use.

The output is divided into two parts

For each table in the database, create a file containing the CREATE TABLE statement. The file is named:

dbname.tablename-schema.sql.gz

Each table name is followed by the number of slices according to the –rows parameter, and the created file name is:

dbname.tablename.0000n.sql.gz

"n" starts from 0.

You can use Myloader to restore these backups

myloader /
--database=$DB_NAME /
--directory=$DB_DUMP /
--queries-per-transaction=50000 /
--threads=10 /
--compress-protocol /
--verbose=3

The above is the introduction to the use of MySQL database backup tool Mydumper, more related content Please pay attention to the PHP Chinese website (www.php.cn)!