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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 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


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
27 4
|
1月前
|
canal 消息中间件 关系型数据库
Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
【9月更文挑战第1天】Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
207 4
|
21天前
|
SQL 缓存 关系型数据库
MySQL高级篇——性能分析工具
MySQL的慢查询日志,用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long-query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为 10,意思是运行10秒以上(不含10秒)的语句,认为是超出了我们的最大忍耐时间值。它的主要作用是,帮助我们发现那些执行时间特别长的 SOL 查询,并且有针对性地进行优化,从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候,检查一下慢查询日志,找到那些慢查询,对解决问题很有帮助。
MySQL高级篇——性能分析工具
|
11天前
|
安全 关系型数据库 MySQL
Navicat工具设置MySQL权限的操作指南
通过上述步骤,您可以使用Navicat有效地为MySQL数据库设置和管理用户权限,确保数据库的安全性和高效管理。这个过程简化了数据库权限管理,使其既直观又易于操作。
50 4
|
1月前
|
监控 关系型数据库 MySQL
zabbix agent集成percona监控MySQL的插件实战案例
这篇文章是关于如何使用Percona监控插件集成Zabbix agent来监控MySQL的实战案例。
31 2
zabbix agent集成percona监控MySQL的插件实战案例
|
2月前
|
SQL 关系型数据库 MySQL
干货!python与MySQL数据库的交互实战
干货!python与MySQL数据库的交互实战
|
2月前
|
存储 关系型数据库 MySQL
实战!MySQL主从复制一键搭建脚本分享
实战!MySQL主从复制一键搭建脚本分享
31 2
|
2月前
|
存储 关系型数据库 MySQL
MySQL备份与恢复
MySQL备份与恢复
47 0
|
18天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
20天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤:启动软件后,通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后,选择“数据导出”,确认导出的数据库及格式(推荐SQL格式),设置存储路径,点击“开始导出”。完成后,可在指定路径找到备份文件,建议定期备份并存储于安全位置。
160 11
下一篇
无影云桌面