MySQL备份：mydumper 备份恢复工具生产实战

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