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