mydumper 备份工具
此工具会锁定表无法进行DML操作只能进行查询，应该在从库上进行备份
一、mydumper备份
1-1、备份全库
mydumper -u dba -p 'password' -h 127.0.0.1 --triggers --events --routines -o /tmp/full.sql
1-2、备份test数据库
mydumper -u dba -p 'password' -h 127.0.0.1 --triggers --events --routines -B test -o /tmp/test_bak/
1-3、备份多张表(t1、t2)：
mydumper -u dba -p 'password' -h 127.0.0.1 --triggers --events --routines -B test -T t1,t2 -o /tmp/test_bak/
1-4、备份t1表的数据，不备份表结构
mydumper -u dba -p 'password' -h 127.0.0.1 --triggers --events --routines -B test -T t1 -m -o /tmp/test_bak/
1-5、备份t1表的数据，并进行压缩
mydumper -u dba -p 'password' -h 127.0.0.1 --triggers --events --routines -B test -T t1 -c -o /tmp/test_bak/
1-6、实战
nohup mydumper \ --build-empty-files \ --trx-consistency-only \ --threads=8 \ --less-locking \ --verbose=3 \ --triggers \ --events \ --routines \ --compress \ --long-query-guard=120 \ --kill-long-queries \ --logfile=/root/mydumper_$(date +%Y%m%d).log \ --host=10.11.8.206 \ --port=3307 \ --user=dba \ --password='123456' \ --outputdir=/mydata/backup/3307/test_$(date +%Y%m%d_%H%M%S) \ -B test 2>/tmp/myduper.log &
二、myloader还原
2-1、还原test库
nohup myloader \ --threads=32 \ --enable-binlog \ --verbose=3 \ --logfile=/mydata/backup/3309/myloader_$(date +%Y%m%d).log \ --host=172.22.222.80 \ --port=3309 \ --user=test \ --password='123456@!QAZ' \ --source-db=test \ --database=test \ -d /mydata/backup/3309/test_222_249_2022_08_26/ 2>>/mydata/backup/3309/myloader_$(date +%Y%m%d).log & myloader \ --threads=8 \ --enable-binlog \ --host=10.11.8.206 \ --port=3307 \ --user=dba \ --password='123456' \ --source-db=test \ --database=test2 \ -d /mydata/backup/3307/test_20220823
2-2、还原t1表
myloader无法还原单表，需要source进行还原
# 进入备份目录找到需要还原的表，如果进行了压缩需要先解压 cd /mydata/backup/3307/test_20220823 # 解压表结构文件 gunzip test.t1-schema.sql.gz # 解压数据文件 gunzip test.t1.00000.sql.gz # 导入test库表结构 mysql -udba -p'123456' -h10.11.8.206 -P3307 test -e "source test.t1-schema.sql" # 导入test库数据 nohup mysql -udba -p'123456' -h10.11.8.206 -P3307 test -e "source test.t1.00000.sql" 2>/root/source_$(date +%Y%m%d).log & # 查询数据量 mysql> select count(1) from t1; +----------+ | count(1) | +----------+ | 19999 | +----------+
三、安装使用
wget --user=admin --password=123456 http://jvqn.vip:88/mysql/mydumper-0.10.7-2.el7.x86_64.rpm wget https://github.com/mydumper/mydumper/releases/download/v0.12.5-3/mydumper-0.12.5-3.el7.x86_64.rpm yum install mydumper-0.12.5-3.el7.x86_64.rpm -y
3-1、mydumper参数
Application Options: -B, --database 需要备份的数据库，一个数据库一条命令备份，要不就是备份所有数据库，包括mysql。 -T, --tables-list 需要备份的表，用逗号分隔。 -o, --outputdir 备份文件目录 -s, --statement-size 生成插入语句的字节数，默认1000000，这个参数不能太小，不然会报 Row bigger than statement_size for tools.t_serverinfo -r, --rows 试图用行块来分割表，该参数关闭--chunk-filesize -F, --chunk-filesize 行块分割表的文件大小，单位是MB -c, --compress 压缩输出文件 -e, --build-empty-files 即使表没有数据，也产生一个空文件 -x, --regex 正则表达式匹配，如'db.table' -i, --ignore-engines 忽略的存储引擎，用逗号分隔 -m, --no-schemas 不导出表结构 -d, --no-data 不导出表数据 -G, --triggers 导出触发器 -E, --events 导出事件 -R, --routines 导出存储过程 -k, --no-locks 不执行共享读锁 警告：这将导致不一致的备份 --less-locking 减到最小的锁在innodb表上. -l, --long-query-guard 设置长查询时间,默认60秒，超过该时间则会报错：There are queries in PROCESSLIST running longer than 60s, aborting dump -K, --kill-long-queries kill掉长时间执行的查询，备份报错：Lock wait timeout exceeded; try restarting transaction -D, --daemon 启用守护进程模式 -I, --snapshot-interval dump快照间隔时间，默认60s，需要在daemon模式下 -L, --logfile 使用日志文件，默认标准输出到终端 --tz-utc 备份的时候允许备份Timestamp，这样会导致不同时区的备份还原会出问题，默认关闭，参数：--skip-tz-utc to disable. --skip-tz-utc --use-savepoints 使用savepoints来减少采集metadata所造成的锁时间，需要SUPER权限 --success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist --lock-all-tables 锁全表，代替FLUSH TABLE WITH READ LOCK -U, --updated-since 使用Update_time仅转储在过去U天内更新的表 --trx-consistency-only 仅事务一致性 快照备份 必加参数！！！ -h, --host The host to connect to -u, --user Username with privileges to run the dump -p, --password User password -P, --port TCP/IP port to connect to -S, --socket UNIX domain socket file to use for connection -t, --threads 备份执行的线程数,默认4个线程 -C, --compress-protocol 在mysql连接上使用压缩协议 -V, --version Show the program version and exit -v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
3-2、myloader参数
Application Options: -d, --directory 备份文件所在的目录 -q, --queries-per-transaction 每个事务的query数量, 默认1000 -o, --overwrite-tables 如果表存在则先删除，使用该参数，需要备份时候要备份表结构，不然还原会找不到表 -B, --database 指定需要还原的数据库（必选参数）需要还原到哪里去！！！ -s, --source-db 选择需要导入备份中哪个数据库内的表（必选参数）选择导入时导入哪个数据库的表！！！ -e, --enable-binlog 启用二进制日志恢复数据(默认不启用)有从库的情况需要开启此参数 -h, --host The host to connect to -u, --user Username with privileges to run the dump -p, --password User password -P, --port TCP/IP port to connect to -S, --socket UNIX domain socket file to use for connection -t, --threads 使用的线程数量，默认4 -C, --compress-protocol 连接上使用压缩协议 -V, --version Show the program version and exit -v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
3-3、插入测试数据存储过程
drop table t4; CREATE TABLE `t4` ( `id` int(11) primary key not null auto_increment, `name` varchar(12) not null ); drop PROCEDURE insert_testdata; DELIMITER ;; CREATE PROCEDURE insert_testdata ( ) BEGIN DECLARE i INT DEFAULT 1; WHILE i < 20000 DO insert into t4 values(i,"1+i dsdsd"); SET i = i + 1; END WHILE; COMMIT; END ;; DELIMITER ; CALL insert_testdata();
生产备份脚本
vim mydumper.sh #! /bin/bash # Mydumper备份脚本可以指定库备份也可以全备 # # 需要排除的库，没有则不填 //按格式填写 示例：'^(?!(mysql\.|test\.|sys\.))' 排除 Exclude='' # 指定备份库 // Exclude参数和此参数只能选一个 都不填则为全备 Database='' # 备份用户名 User=dba # 备份用户密码 Passwd='123456' Host=10.11.8.206 Port=3307 # 备份文件保存路径 Bak_Dir=/mydata/backup/3307/ Mydumper_Dir=$(which mydumper) Time=$(date +%Y_%m_%d) # 获取主机后两位 if [ ${Host} == 'localhost' ] || [ ${Host} == '127.0.0.1' ]; then Host_info='local' else Host_info=$(echo "${Host}" |awk -F'.' '{print $3"_"$4}') fi # 判断是否指定了库 if [ -n "${Database}" ]; then Schema="--database=${Database}" Dir_Name=${Database} else Schema="" Dir_Name=full fi # 判断是否指定了排除库 if [ -n "${Exclude}" ];then Exclude_1="--regex "${Exclude}"" else Exclude_1='' fi # 创建备份目录 mkdir -p ${Bak_Dir}/${Dir_Name}_${Host_info}_${Time} ${Mydumper_Dir} \ --host=${Host} \ ${Schema} \ ${Exclude_1} \ --port=${Port} \ --user=${User} \ --password=${Passwd} \ --outputdir=${Bak_Dir}/${Dir_Name}_${Host_info}_${Time} \ --rows=5000000 \ --build-empty-files \ --threads=8 \ --trx-consistency-only \ --compress-protocol \ --less-locking \ --verbose=3 \ --logfile=${Bak_Dir}/mydumper_${Time}.log \ --triggers \ --events \ --routines \ --compress \ --long-query-guard=120 \ --kill-long-queries if [ $? -ne 0 ]; then echo "$(/usr/bin/date +%Y-%m-%d_%H:%M:%S) ${Host} 主机 备份失败" >>${Bak_Dir}/mydumper_${Time}.log else echo "$(/usr/bin/date +%Y-%m-%d_%H:%M:%S) ${Host} 主机 备份成功" >>${Bak_Dir}/mydumper_${Time}.log fi if [ ${Bak_Dir} == '/' ] || [ ${Bak_Dir} == '' ];then echo '指定备份地址为 "/" 不予执行清理工作' >>${Bak_Dir}/mydumper_${Time}.log exit else find ${Bak_Dir} -type d -name "${Dir_Name}_${Host_info}*" -mtime +90 -print |xargs rm -rf find ${Bak_Dir} -type f -name "mydumper_*.log" -mtime +90 -print |xargs rm -rf fi
