Mydumper-MySQL Database Backup Tool
Mydumper is a MySQL database server backup tool, which is much faster than mysqldump. It also provides the ability to obtain remote server binary log files while dumping.
Advantages of Mydumper
- Parallel capabilities (so high speed) and performance (efficient code avoids the character set conversion process that consumes CPU processing capability)
- It is easier to manage output (each table corresponds to an independent file, dumping metadata, etc., to facilitate viewing/parsing data)
- Consistency: maintains snapshots across threads and provides precise Master/Slave log location.
- Manageability: supports the use of pcre to include/exclude specified databases and tables (LCTT: PCRE, Perl Compatible Regular Expression, Perl Compatible Regular Expression)
Install mydumper on Ubuntu
Open the terminal and run the following command
sudo apt-get install mydumper
Use Mydumper syntax
mydumper [options]
Application options:
- -B, -- database to be dumped
- -T, -- tables-list comma-separated dump table list (not excluded by regular expressions)
- -O, -- outputdir: directory for saving the output file
- -S, -- statement-size: the size of the inserted statement. The default value is 1000000 bytes.
- -R, -- rows cut the table by the number of rows
- -C, -- compress compressed output file
- -E, -- build-empty-files empty table also outputs files
- -X, -- regex matches the Regular Expression of 'db. Table '.
- -I, -- ignore-engines list of ignored storage engines separated by commas
- -M, -- no-schemas do not dump the table Architecture
- -K, -- no-locks do not execute temporary shared read locks. Warning: this will cause inconsistency of backups.
- -L, -- long-query-guard: sets the timer seconds for long queries. The default value is 60 seconds.
- -- Kill-long-queries: kill the long query (instead of exiting the Program)
- -B, -- binlogs obtains the binary log file snapshot and dumps the data.
- -D, -- daemon: Enable the daemon mode.
- -I, -- snapshot-interval: interval (minutes) between each dump snapshot. You must enable -- daemon. The default value is 60 minutes.
- -L, -- Name of the logfile. The default value is stdout.
- -H, -- host the host to be connected
- -U, -- the user name with the permission to dump
- -P, -- password User password
- -P, -- port connection TCP/IP port
- -S, -- socket: Unix socket file used for connection
- -T, -- the number of threads used by threads. The default value is 4.
- -C, -- compress-protocol use compression on MySQL connections
- -V, -- version: view the program version.
- -V, -- verbose output level, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 2 by default
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
Description of Mydumper output data
Mydumper does not directly specify the output file, but outputs the file to the folder. The -- outputdir option specifies the name of the directory to be used.
The output is divided into two parts:
Table Structure
CREATE a file containing the create table statement for each TABLE in the database. File Name:
Dbname.tablename-schema. SQL .gz
Data
Each table name is followed by the number of chunks by the -- rows parameter. The name of the created file is:
Dbname.tablename.w.n. 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
This article permanently updates the link address:
