MySQL多线程备份工具mydumper

MySQL多线程备份工具mydumper目录 一 mydumper 简介 二 mydumper 安装 1 yum 安装 mydumper 2 源码安装 mydumper 三 mydumper 参数介绍 四 myloader 参数介绍 五 使用例子 一 mydumper 简介 mydumper 是一款社区开源的逻辑备份工具 该工具主要由 C 语言编写 目前由 MySQL

大家好,我是讯享网,很高兴认识大家。

目录

一、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备份的文件位置

小讯
上一篇 2025-01-15 23:56
下一篇 2025-03-17 21:48

相关推荐

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