目录
一、mydumper简介
mydumper 是一款社区开源的逻辑备份工具。该工具主要由 C 语言编写，目前由 MySQL 、Facebook 等公司人员开发维护。
参考官方介绍，mydumper 主要有以下几点特性：
- 支持多线程导出数据，速度更快。
- 支持一致性备份。
- 支持将导出文件压缩，节约空间。
- 支持多线程恢复。
- 支持以守护进程模式工作，定时快照和连续二进制日志。
- 支持按照指定大小将备份文件切割。
- 数据与建表语句分离。
mydumper 官网：https://launchpad.net/mydumper
mydumper github： https://github.com/mydumper/mydumper
mydumper下载：https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz
wget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz二、mydumper安装
1、yum安装mydumper
## RedHat / Centosrelease=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el7.x86_64.rpmyum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el8.x86_64.rpm2、源码安装mydumper
根据个人使用经验，推荐mydumper-0.9.1版本比较稳定，github上边的比较新，会出现预想不到bug。
# Dependencies for building mydumperyum install -y cmake gcc gcc-c++ git makeyum install -y glib2-devel openssl-devel pcre-devel zlib-devel libzstd-develyum install -y mysql-develyum install -y Percona-Server-devel-57yum install -y mariadb-develwget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gztar -xzvf mydumper-0.9.1.tar.gzcd mydumper-0.9.1 && cmake . && makechmod a+x mydumper myloadercp mydumper myloader /usr/local/bin/三、mydumper参数介绍
mydumper 和 myloader 是相对应的一组可执行程序，二者的作用分别是导出数据与导入数据。
## mydumper --helpUsage: mydumper [OPTION?] multi-threaded MySQL dumpingHelp Options: -?, --help Show help optionsApplication Options: -B, --database Database to dump -T, --tables-list Comma delimited table list to dump (does not exclude regex option) -O, --omit-from-file File containing a list of database.table entries to skip, one per line (skips before applying regex option) -o, --outputdir Directory to output files to -s, --statement-size Attempted size of INSERT statement in bytes, default 1000000 -r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize -F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB -c, --compress Compress output files -e, --build-empty-files Build dump files even if no data available from table -x, --regex Regular expression for 'db.table' matching -i, --ignore-engines Comma delimited list of storage engines to ignore -N, --insert-ignore Dump rows with INSERT IGNORE -m, --no-schemas Do not dump table schemas with the data -d, --no-data Do not dump table data -G, --triggers Dump triggers -E, --events Dump events -R, --routines Dump stored procedures and functions -W, --no-views Do not dump VIEWs -k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups --no-backup-locks Do not use Percona backup locks --less-locking Minimize locking time on InnoDB tables. -l, --long-query-guard Set long query timer in seconds, default 60 -K, --kill-long-queries Kill long running queries (instead of aborting) -D, --daemon Enable daemon mode -I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default 60 -L, --logfile Log file name to use, by default stdout is used --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 --use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege --success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist --lock-all-tables Use LOCK TABLE for all, instead of FTWRL -U, --updated-since Use Update_time to dump only tables updated in the last U days --trx-consistency-only Transactional consistency only --complete-insert Use complete INSERT statements that include column names -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 -t, --threads Number of threads to use, default 4 -C, --compress-protocol Use compression on the MySQL connection -V, --version Show the program version and exit -v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 --defaults-file Use a specific defaults file四、myloader参数介绍
# myloader --helpUsage: myloader [OPTION?] multi-threaded MySQL loaderHelp Options: -?, --help Show help optionsApplication Options: -d, --directory Directory of the dump to import -q, --queries-per-transaction Number of queries per transaction, default 1000 -o, --overwrite-tables Drop tables if they already exist -B, --database An alternative database to restore into -s, --source-db Database to restore -e, --enable-binlog Enable binary logging of the restore data -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 -t, --threads Number of threads to use, default 4 -C, --compress-protocol Use compression on the MySQL connection -V, --version Show the program version and exit -v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 --defaults-file Use a specific defaults file五、使用例子
这里是我根据自己的环境优化的备份和还原常用参数
## cat backupdata.sh#!/bin/bash## 备份源库数据copydata() {mydumper -u 源库用户 -p '源库密码' -h 源库IP -P 3306 --regex '^(?!(mysql|sys|information_schema|performance_schema|test))' -D -E -R -G -C -c -t 4 -F 1 -s 4000000 -v 2 -l 360 --logfile log.log -o data}# 还原到目标库restore() {nohup myloader -u 目标库用户 -p '目标库密码' -h 127.0.0.1 -P 3306 -C -t 4 -o -v 2 --directory data/0 &}# 由于我的mydumper安装在目标库上，所有目标库IP 使用 127.0.0.1 copydatarestore| -D | 守护进程方式运行mydumper |
| -E | 备份事件 |
| -R | 备份存储和函数 |
| -G | 备份触发器 |
| -C | 在MySQL连接上使用压缩 |
| -c | 压缩输出文件 |
| -t | 要使用的线程数，默认值为4 |
| -F | 将表拆分为此输出文件大小的块。此值以MB为单位 |
| -s | INSERT语句的尝试大小（以字节为单位），默认值为1000000 |
| -v | 输出的详细性，0=无提示，1=错误，2=警告，3=信息，默认值2 |
| -l | 以秒为单位设置长查询计时器，默认值为60 |
| --logfile | 要使用的日志文件名，默认情况下使用stdout |
| -o (--outputdir) | mydumper的-o表示：要将文件输出到的目录 |
| -o (--overwrite-tables) | myloader的-o表示：删除表（如果它们已经存在） |
| --directory | 要导入的转储目录,即mydumper备份的文件位置 |
来源地址：https://blog.csdn.net/weixin_44770684/article/details/128037729
