Example of mydumper backing up mysql database and mydumpermysql

Example of mydumper backing up mysql database and mydumpermysql

Mydumper is a lightweight third-party open-source tool for mysql database backup. The backup mode is logical backup. It supports multithreading, And the backup speed is much higher than the original mysqldump and many outstanding features. Therefore, this tool is the best choice for DBAs. This document describes how to use the tool and provides an example.

For mydumper Installation Reference: mydumper installation and installation fault Summary

1. Features of mydumper
A. Multi-thread logical backup: multiple backup files produced
B. Similar to mysqldump, applying FTWRL (FLUSH TABLES WITH READ LOCK) to the MyISAM table during Backup will block the DML statement
C. Ensure consistency of backup data
D. Supports file compression, binlog export, multi-thread recovery, and block backup files.
E. Support Working in daemon mode, timed snapshots, and continuous binary logs

2. mydumper syntax
Mydumper-u [USER]-p [PASSWORD]-h [HOST]-P [PORT]-t [THREADS]-B-c-B [DB]-o [directory]
Parameter description
-B, -- database to be backed up
-T, -- tables-list tables to be backed up, separated by commas
-O, -- outputdir directory of the output file
-S, -- statement-size: the number of bytes of the insert statement. The default value is 1000000.
-R, -- rows split into many row block tables
-C, -- compress compressed output file
-E, -- build-empty-files: even if the table has no data, an empty file is generated.
-X, -- regex Regular Expression: 'db. table'
-I, -- storage engine ignored by ignore-engines, separated by commas
-M, -- no-schemas does not export the table structure
-K, -- no-locks do not execute the shared read lock warning: this will cause inconsistent backups
-L, -- long-query-guard: Set the long query time. The default value is 60 seconds.
-- Kill-long-queries kill the query that has been executed for a long time
-B, -- binlogs export binlog
-D, -- daemon enable daemon mode
-I, -- snapshot-interval dump snapshot interval. The default value is 60 s. It must be in daemon mode.
-L, -- logfile Log File
-H, -- host The host to connect
-U, -- user Username with privileges to run the dump
-P, -- password User password
-P, -- port TCP/IP port to connect
-S, -- socket UNIX domain socket file to use for connection
-T, -- the number of threads used by threads. The default value is 4.
-C, -- compress-protocol use the compression protocol on the mysql connection
-V, -- version Show the program version and exit
-V, -- verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

3. mydumper's Working Process
A. connect to the target database;
B. Use show processlist to determine whether a long query exists. The long query or kill-long-queries parameter is used to exit or kill the long query;
C. lock the myisam table and flush tables with read lock; start transaction for innodb table;
D. Create worker subthreads. The default value is 4;
E. Determine candidate tables. Insert innodb_table, non_innodb_table, and table_schemas linked lists (table structure) based on categories );
F. Add the candidate table to the task queue through g_async_queue_push (the last element of the queue is thread shutdown). The worker subthread reads table information from the queue and executes data export.
G. Execute unlock tables and unlock the myisam table immediately after processing to reduce the lock time;
H. Wait for the worker to exit;

4. Backup File Information
A. All backup files are in the same directory. If not specified, they are in the current directory and the backup date and time folder is automatically generated, such as export-20150703-145806.
B. The directory contains a metadata file, which records the binary log file name at the backup time point and the log write location.
C. If the backup is performed from the slave database, it also records the binary log files synchronized to the master database and the write location.
D. Each table has two backup files: database. table-schema. SQL table structure file, database. table. SQL table data file
E. If you partition a table file, multiple backup data files will be generated. You can specify the number of rows or the size of the partitions.

5. Common backup examples:
### Back up a single database
# Mydumper-u leshami-p pwd-B sakila-o/tmp/bak

### Back up all databases. All databases except information_schema and cece_schema during full database backup will be backed up
# Mydumper-u leshami-p pwd-o/tmp/bak

### Back up a single table
# Mydumper-u leshami-p pwd-B sakila-T actor-o/tmp/bak

### Back up multiple tables
# Mydumper-u leshami-p pwd-B sakila-T actor, city-o/tmp/bak

### The current directory automatically generates the backup date and time folder. If the-o parameter and value are not specified, for example, the folder is export-20150703-145806
Mydumper-u leshami-p pwd-B sakila-T actor

### Backup table without table structure
# Mydumper-u leshami-p pwd-B sakila-T actor-m

### Compression protocols for backup and connection (for non-local backup)
# Mydumper-u leshami-p pwd-B sakila-o/tmp/bak-c-C

### Back up a specific table
# Mydumper-u leshami-p pwd-B sakila -- regex = actor *-o/tmp/bak

### Filter out specific databases, such as not backing up mysql and test Databases
# Mydumper-u leshami-p pwd-B sakila -- regex '^ (?! (Mysql | test) '-o/tmp/bak

### Generate table structure files based on empty tables
# Mydumper-u leshami-p pwd-B sakila-T actor-e-o/tmp/bak

# Set the maximum number of long queries. If a query is longer than this limit, exit mydumper. You can also set the limit to kill this long query.
# Mydumper-u leshami-p pwd-B sakila -- long-query-guard 200 -- kill-long-queries

### Output detailed logs during Backup
# Mydumper-u leshami-p pwd-B sakila-T actor-v 3-o/tmp/bak

### Use the-B parameter to export the binlog_snapshot folder and binlog will be automatically generated in the export directory
# Mydumper-u leshami-p pwd-P 3306-B-o/tmp/bak

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.