目录
一、mydumper简介
二、mydumper安装
1、yum安装mydumper
2、源码安装mydumper
三、mydumper参数介绍
四、myloader参数介绍
五、使用例子
一、mydumper简介
mydumper 是一款社区开源的逻辑备份工具。该工具主要由 C 语言编写,目前由 MySQL 、Facebook 等公司人员开发维护。
参考官方介绍,mydumper 主要有以下几点特性:
- 支持多线程导出数据,速度更快。
- 支持一致性备份。
- 支持将导出文件压缩,节约空间。
- 支持多线程恢复。
- 支持以守护进程模式工作,定时快照和连续二进制日志。
- 支持按照指定大小将备份文件切割。
- 数据与建表语句分离。
mydumper 官网:https://launchpad.net/mydumper
mydumper github: https://github.com/mydumper/mydumper
mydumper下载:https://launchpadlibrarian.net//mydumper-0.9.1.tar.gz
wget https://launchpadlibrarian.net//mydumper-0.9.1.tar.gz
讯享网
二、mydumper安装
1、yum安装mydumper
讯享网 RedHat / Centos release=$(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.rpm yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el8.x86_64.rpm
2、源码安装mydumper
根据个人使用经验,推荐mydumper-0.9.1版本比较稳定,github上边的比较新,会出现预想不到bug。
# Dependencies for building mydumper yum install -y cmake gcc gcc-c++ git make yum install -y glib2-devel openssl-devel pcre-devel zlib-devel libzstd-devel yum install -y mysql-devel yum install -y Percona-Server-devel-57 yum install -y mariadb-devel
讯享网wget https://launchpadlibrarian.net//mydumper-0.9.1.tar.gz tar -xzvf mydumper-0.9.1.tar.gz cd mydumper-0.9.1 && cmake . && make chmod a+x mydumper myloader cp mydumper myloader /usr/local/bin/
三、mydumper参数介绍
mydumper 和 myloader 是相对应的一组可执行程序,二者的作用分别是导出数据与导入数据。
mydumper --help Usage: mydumper [OPTION?] multi-threaded MySQL dumping Help Options: -?, --help Show help options Application 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 -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 --help Usage: myloader [OPTION?] multi-threaded MySQL loader Help Options: -?, --help Show help options Application 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 -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 copydata restore
| -D | 守护进程方式运行mydumper |
| -E | 备份事件 |
| -R | 备份存储和函数 |
| -G | 备份触发器 |
| -C | 在MySQL连接上使用压缩 |
| -c | 压缩输出文件 |
| -t | 要使用的线程数,默认值为4 |
| -F | 将表拆分为此输出文件大小的块。此值以MB为单位 |
| -s | INSERT语句的尝试大小(以字节为单位),默认值为 |
| -v | 输出的详细性,0=无提示,1=错误,2=警告,3=信息,默认值2 |
| -l | 以秒为单位设置长查询计时器,默认值为60 |
| --logfile | 要使用的日志文件名,默认情况下使用stdout |
| -o (--outputdir) |
mydumper的-o表示:要将文件输出到的目录 |
| -o (--overwrite-tables) |
myloader的-o表示:删除表(如果它们已经存在) |
| --directory | 要导入的转储目录,即mydumper备份的文件位置 |

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/80699.html