MySQL 逻辑备份工具 MyDumper

1、依赖安装

dnf install -y cmake gcc gcc-c++ git make

2、mydumper下载和安装

# 下载
wget https://github.com/mydumper/mydumper/releases/download/v0.14.1-1/mydumper-0.14.1-1.el9.x86_64.rpm

# 安装
dnf install mydumper-0.14.1-1.el9.x86_64.rpm -y

3、mydumper参数

# mydumper --help
Usage:
  mydumper [OPTION…] multi-threaded MySQL dumping

Connection Options
  -h, --host                       The host to connect to
  -u, --user                       Username with the necessary privileges
  -p, --password                   User password
  -a, --ask-password               Prompt For User password
  -P, --port                       TCP/IP port to connect to
  -S, --socket                     UNIX domain socket file to use for connection
  -C, --compress-protocol          Use compression on the MySQL connection
  --ssl                            Connect using SSL
  --ssl-mode                       Desired security state of the connection to the server: DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY
  --key                            The path name to the key file
  --cert                           The path name to the certificate file
  --ca                             The path name to the certificate authority file
  --capath                         The path name to a directory that contains trusted SSL CA certificates in PEM format
  --cipher                         A list of permissible ciphers to use for SSL encryption
  --tls-version                    Which protocols the server permits for encrypted connections

Filter Options
  -x, --regex                      Regular expression for 'db.table' matching
  -B, --database                   Database to dump
  -i, --ignore-engines             Comma delimited list of storage engines to ignore
  --where                          Dump only selected records.
  -U, --updated-since              Use Update_time to dump only tables updated in the last U days
  -O, --omit-from-file             File containing a list of database.table entries to skip, one per line (skips before applying regex option)
  -T, --tables-list                Comma delimited table list to dump (does not exclude regex option). Table name must include database name. For instance: test.t1,test.t2

Lock Options
  -z, --tidb-snapshot              Snapshot to use for TiDB
  -k, --no-locks                   Do not execute the temporary shared read lock.  WARNING: This will cause inconsistent backups
  --use-savepoints                 Use savepoints to reduce metadata locking issues, needs SUPER privilege
  --no-backup-locks                Do not use Percona backup locks
  --lock-all-tables                Use LOCK TABLE for all, instead of FTWRL
  --less-locking                   Minimize locking time on InnoDB tables.
  --trx-consistency-only           Transactional consistency only

PMM Options
  --pmm-path                       which default value will be /usr/local/percona/pmm2/collectors/textfile-collector/high-resolution
  --pmm-resolution                 which default will be high

Exec Options
  --exec-threads                   Amount of threads to use with --exec
  --exec                           Command to execute using the file as parameter
  --exec-per-thread                Set the command that will receive by STDIN and write in the STDOUT into the output file
  --exec-per-thread-extension      Set the extension for the STDOUT file when --exec-per-thread is used

If long query running found:
  --long-query-retries             Retry checking for long queries, default 0 (do not retry)
  --long-query-retry-interval      Time to wait before retrying the long query check in seconds, default 60
  -l, --long-query-guard           Set long query timer in seconds, default 60
  -K, --kill-long-queries          Kill long running queries (instead of aborting)

Job Options
  --max-rows                       Limit the number of rows per block after the table is estimated, default 1000000
  --char-deep                      
  --char-chunk                     
  -r, --rows                       Try to split tables into chunks of this many rows.
  --split-partitions               Dump partitions into separate files. This options overrides the --rows option for partitioned tables.

Checksum Options
  -M, --checksum-all               Dump checksums for all elements
  --data-checksums                 Dump table checksums with the data
  --schema-checksums               Dump schema table and view creation checksums
  --routine-checksums              Dump triggers, functions and routines checksums

Objects Options
  -m, --no-schemas                 Do not dump table schemas with the data and triggers
  -Y, --all-tablespaces            Dump all the tablespaces.
  -d, --no-data                    Do not dump table data
  -G, --triggers                   Dump triggers. By default, it do not dump triggers
  -E, --events                     Dump events. By default, it do not dump events
  -R, --routines                   Dump stored procedures and functions. By default, it do not dump stored procedures nor functions
  --views-as-tables                Export VIEWs as they were tables
  -W, --no-views                   Do not dump VIEWs

Statement Options
  --load-data                      
  --csv                            Automatically enables --load-data and set variables to export in CSV format.
  --fields-terminated-by           
  --fields-enclosed-by             
  --fields-escaped-by              Single character that is going to be used to escape characters in theLOAD DATA stament, default: '\' 
  --lines-starting-by              Adds the string at the begining of each row. When --load-data is usedit is added to the LOAD DATA statement. Its affects INSERT INTO statementsalso when it is used.
  --lines-terminated-by            Adds the string at the end of each row. When --load-data is used it isadded to the LOAD DATA statement. Its affects INSERT INTO statementsalso when it is used.
  --statement-terminated-by        This might never be used, unless you know what are you doing
  -N, --insert-ignore              Dump rows with INSERT IGNORE
  --replace                        Dump rows with REPLACE
  --complete-insert                Use complete INSERT statements that include column names
  --hex-blob                       Dump binary columns using hexadecimal notation
  --skip-definer                   Removes DEFINER from the CREATE statement. By default, statements are not modified
  -s, --statement-size             Attempted size of INSERT statement in bytes, default 1000000
  --tz-utc                         SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
  --skip-tz-utc                    
  --set-names                      Sets the names, use it at your own risk, default binary

Extra Options
  -F, --chunk-filesize             Split tables into chunks of this output file size. This value is in MB
  --exit-if-broken-table-found     Exits if a broken table has been found
  --success-on-1146                Not increment error count and Warning instead of Critical in case of table doesn't exist
  -e, --build-empty-files          Build dump files even if no data available from table
  --no-check-generated-fields      Queries related to generated fields are not going to be executed.It will lead to restoration issues if you have generated columns
  --order-by-primary               Sort the data by Primary Key or Unique key if no primary key exists
  -c, --compress                   Compress output files

Daemon Options
  -D, --daemon                     Enable daemon mode
  -I, --snapshot-interval          Interval between each dump snapshot (in minutes), requires --daemon, default 60
  -X, --snapshot-count             number of snapshots, default 2

Application Options:
  -?, --help                       Show help options
  -o, --outputdir                  Directory to output files to
  --stream                         It will stream over STDOUT once the files has been written. Since v0.12.7-1, accepts NO_DELETE, NO_STREAM_AND_NO_DELETE and TRADITIONAL which is the default value and used if no parameter is given
  -L, --logfile                    Log file name to use, by default stdout is used
  --disk-limits                    Set the limit to pause and resume if determines there is no enough disk space.Accepts values like: '<resume>:<pause>' in MB.For instance: 100:500 will pause when there is only 100MB free and willresume if 500MB are available
  -t, --threads                    Number of threads to use, default 4
  -V, --version                    Show the program version and exit
  --identifier-quote-character     This set the identifier quote character that is used to INSERT statements onlyon mydumper and to split statement on myloader. Use SQL_MODE to change theCREATE TABLE statementsPosible values are: BACKTICK and DOUBLE_QUOTE. Default: BACKTICK
  -v, --verbose                    Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  --defaults-file                  Use a specific defaults file. Default: /etc/mydumper.cnf

4、myloader参数

# myloader --help
Usage:
  myloader [OPTION…] multi-threaded MySQL loader

Connection Options
  -h, --host                           The host to connect to
  -u, --user                           Username with the necessary privileges
  -p, --password                       User password
  -a, --ask-password                   Prompt For User password
  -P, --port                           TCP/IP port to connect to
  -S, --socket                         UNIX domain socket file to use for connection
  -C, --compress-protocol              Use compression on the MySQL connection
  --ssl                                Connect using SSL
  --ssl-mode                           Desired security state of the connection to the server: DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY
  --key                                The path name to the key file
  --cert                               The path name to the certificate file
  --ca                                 The path name to the certificate authority file
  --capath                             The path name to a directory that contains trusted SSL CA certificates in PEM format
  --cipher                             A list of permissible ciphers to use for SSL encryption
  --tls-version                        Which protocols the server permits for encrypted connections

Filter Options
  -x, --regex                          Regular expression for 'db.table' matching
  -s, --source-db                      Database to restore
  --skip-triggers                      Do not import triggers. By default, it imports triggers
  --skip-post                          Do not import events, stored procedures and functions. By default, it imports events, stored procedures nor functions
  --no-data                            Do not dump or import table data
  -O, --omit-from-file                 File containing a list of database.table entries to skip, one per line (skips before applying regex option)
  -T, --tables-list                    Comma delimited table list to dump (does not exclude regex option). Table name must include database name. For instance: test.t1,test.t2

PMM Options
  --pmm-path                           which default value will be /usr/local/percona/pmm2/collectors/textfile-collector/high-resolution
  --pmm-resolution                     which default will be high

Execution Options
  -e, --enable-binlog                  Enable binary logging of the restore data
  --innodb-optimize-keys               Creates the table without the indexes and it adds them at the end. Options: AFTER_IMPORT_PER_TABLE and AFTER_IMPORT_ALL_TABLES. Default: AFTER_IMPORT_PER_TABLE
  --purge-mode                         This specify the truncate mode which can be: NONE, DROP, TRUNCATE and DELETE
  --disable-redo-log                   Disables the REDO_LOG and enables it after, doesn't check initial status
  -o, --overwrite-tables               Drop tables if they already exist
  --serialized-table-creation          Table recreation will be executed in series, one thread at a time
  --stream                             It will receive the stream from STDIN and creates the file in the disk before start processing. Since v0.12.7-1, accepts NO_DELETE, NO_STREAM_AND_NO_DELETE and TRADITIONAL which is the default value and used if no parameter is given

Threads Options
  --max-threads-per-table              Maximum number of threads per table to use, default 4
  --max-threads-for-index-creation     Maximum number of threads for index creation, default 4

Statement Options
  -r, --rows                           Split the INSERT statement into this many rows.
  -q, --queries-per-transaction        Number of queries per transaction, default 1000
  --append-if-not-exist                Appends IF NOT EXISTS to the create table statements. This will be removed when https://bugs.mysql.com/bug.php?id=103791 has been implemented
  --set-names                          Sets the names, use it at your own risk, default binary
  --skip-definer                       Removes DEFINER from the CREATE statement. By default, statements are not modified

Application Options:
  -?, --help                           Show help options
  -d, --directory                      Directory of the dump to import
  -L, --logfile                        Log file name to use, by default stdout is used
  -B, --database                       An alternative database to restore into
  --resume                             Expect to find resume file in backup dir and will only process those files
  -t, --threads                        Number of threads to use, default 4
  -V, --version                        Show the program version and exit
  --identifier-quote-character         This set the identifier quote character that is used to INSERT statements onlyon mydumper and to split statement on myloader. Use SQL_MODE to change theCREATE TABLE statementsPosible values are: BACKTICK and DOUBLE_QUOTE. Default: BACKTICK
  -v, --verbose                        Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
  --defaults-file                      Use a specific defaults file. Default: /etc/mydumper.cnf

5、mydumper使用

# 备份全部数据库 
mydumper -u root -p rootroot -o /data/backup/all_db/

# 备份全部数据库 包含触发器、事件、存储过程及函数
mydumper -u root -p rootroot -G -R -E -o /data/backup/all/

# 备份指定库
mydumper -u root -p rootroot -G -R -E -B demo -o /data/backup/demo/

# 使用正则 排除系统库
mydumper -u root -p rootroot -G -R -E --regex '^(?!(mysql|sys))' -o /data/backup/db

# 备份指定表
mydumper -u root -p rootroot -B demo -T table1,table2 -o /data/backup/table/

# 只备份表结构
mydumper -u root -p rootroot -d -B demo -o /data/backup/nodata/

# 只备份表数据
mydumper -u root -p rootroot -m -B demo -o /data/backup/noschema/

# 压缩备份某个表
mydumper -u root -p rootroot -B demo -T table1 -c -o /data/backup/compress/

6、myload使用

# 恢复全部备份文件（若表已存在则先删除）
myloader -u root -p rootroot -o -d /data/backup/all

# 从全备中恢复指定库
myloader -u root -p rootroot -s demo -o -d /data/backup/demo

# 将某个数据库备份还原到另一个数据库中（目标库不存在则会新建）
myloader -u root -p rootroot -B recover_demo -s demo -o -d /data/backup/demo
myloader -u root -p rootroot -B recover_demo -o -d /data/backup/demo

# 恢复时开启binlog（有备库的时候需要开启）
myloader -u root -p rootroot -e -o -d /data/backup/demo

# 无法还原单表，只能进入命令行执行还原
source demo.table1-schema.sql	#还原表结构
source demo.table1.sql	#还原表数据

参考：https://github.com/mydumper/mydumper