MySQL多线程备份工具mydumper

@土豆

已于 2023-04-26 20:15:11 修改

阅读量6.6k

点赞数 10

文章标签： mysql linux 服务器

于 2022-11-25 15:48:25 首次发布

本文链接：https://blog.csdn.net/weixin_44770684/article/details/128037729

版权

一、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 / 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/225370879/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 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 --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 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 

copydata
restore

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