AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL 备份恢复

2023-05-25 161
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： MySQL 备份恢复

数据库也可以进行恢复，但是要结合上上一篇，日志服务，通过binlog 去截取对应的sql语句，进行恢复。

关于备份恢复方面的职责

（1）备份、恢复策略的设计。
  备份周期、备份工具、备份方式、恢复方式全部流程化
（2）日常备份检查
     日志、备份内容   
（3）定期的恢复演练
（4）数据故障时，利用现有的资源，快速恢复
（5）数据迁移、升级。

备份工具介绍

逻辑备份
mysqldump  / source   *****
mysqlbinlog  /source 
mydumper / myloader
select into outfile / load data infile 
binlog2sql
myflashback
物理备份
Percona Xtrabackup (PXB,XBK) *****
迁移表空间 
Mysql Enterpise backup（MEB，企业版）
8.0 clone plugin (8.0.17)

如何选择

100G 以内：逻辑 
100G 以上：物理 
超大型： 逻辑

mysqldump 工具使用

mdp数据逻辑备份工具。（Create database\   create  table \ insert）
MySQL 自带的客户端命令。可以实现远程和本地备份。
连接参数 
-u  
-p 
-S 
-h 
-P
-A 全备
mysqldump -uroot -p123  -A >/data/backup/full.sql
-B 单库或多库
mysqldump -uroot -p123  -B world gtdb test >/data/backup/db.sql
备份单表或多表
mysqldump -uroot -p123  world t1 country >/data/backup/tab.sql
--master-data=2
功能：1.自动记录备份时的binlog信息（注释）
       2.自动锁定所有表，自动解锁（global read lock）。最好配合--single-transaction 参数，减少锁表时间。
mysqldump -uroot -p123  -A  --master-data=2  >/data/backup/full.sql
--single-transaction
对于InnoDB表，开启独立事务，通过快照备份表数据，不锁表备份，可以理解为热备。
mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  >/data/backup/full.sql
--max_allowed_packet=64M 最大允许的数据包大小
mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  >/data/backup/full.sql
-R -E --triggers 备份特殊对象使用
mysqldump -uroot -p123  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full.sql
格式化
备份文件
mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_p

故障恢复演练(mdp+binlog,每天全备)

模拟环境  
mysql> create database mdp charset utf8mb4;
mysql> use mdp
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
模拟 周一23:00 的全备
mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full_`date +%F`.sql
查看 GTID相关信息 ：GTID截取起点
SET @@GLOBAL.GTID_PURGED='202628e9-9265-11ea-b4a0-000c29248f69:1-35';
查看pos号，备份开始时binlog位置点信息
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=158999;
模拟周二白天数据变化 
mysql> use mdp;
mysql> create table t2 (id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
周二下午2点，误删除了mdp核心库
mysql> drop database mdp;

故障恢复

思路：
(1) 恢复全备到周一晚上23:00
检查全备：
vim /data/backup/full_2020-05-11.sql 
# 查看 GTID相关信息 ：GTID截取起点。
SET @@GLOBAL.GTID_PURGED='202628e9-9265-11ea-b4a0-000c29248f69:1-35';
# 查看pos号，备份开始时binlog位置点信息。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=158999;
(2) 截取日日志：
# 起点：
mysql-bin.000023 202628e9-9265-11ea-b4a0-000c29248f69:36 或者 mysql-bin.000023 pos=158999
#终点：drop
[root@db01 backup]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000023'"|grep -B 1 "drop database mdp"
mysql-bin.000023  159421  Gtid  6  159486  SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:38'
mysql-bin.000023  159486  Query  6  159575  drop database mdp
[root@db01 backup]# 
# 截取日志 
[root@db01 backup]# mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:36-37' /data/3306/logs/mysql-bin.000023 >/data/backup/bin.sql
(3) 恢复
mysql> set sql_log_bin=0;
mysql> source /data/backup/full_2020-05-11.sql 
mysql> use mdp
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;
(4) 检查数据
mysql> show tables;
+---------------+
| Tables_in_mdp |
+---------------+
| t1            |
| t2            |
+---------------+
2 rows in set (0.01 sec)
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysqldump多种备份策略和恢复策略介绍

1.mdp full+ binlog 增量备份
恢复单表数据思路：
（1） 提取full全备中的故障表数据 ，恢复数据
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q'  full.sql>createtable.sql
# grep -i 'INSERT INTO `t1`'  full.sql >data.sql 
（2） binlog中截取全备到误删除t1之间对于这张表的修改
2. 单库单表备份+binlog增量  
恢复单表数据思路：
 （1）恢复单表的备份
 （2）binlog中截取备份到误删除t1之间对于这张表的修改

故障模拟

# 模拟原始数据
 create database oldboy charset utf8mb4;
 use oldboy;
 create table oldguo (id int);
 insert into oldguo values(1),(2),(3);
 commit;
# 周一晚上全库备份
mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/data/backup/full.sql
# 模拟周二白天的数据变化 
use oldboy ;
insert into oldguo values(11),(22),(33);
commit;
create table  oldli(id int);
insert into oldli values(1),(2),(3);
commit;
insert into oldguo values(111),(222),(333);
commit;
# 模拟周二下午2点，误删除oldguo表
drop table  oldguo;
3.4.4 恢复过程  
# 1. 处理全备
[root@db01 ~]# sed -n '/CREATE TABLE `oldguo` /,/\;/p' /data/backup/full.sql >/data/backup/create.sql
[root@db01 ~]# grep -i 'INSERT INTO `oldguo`'  /data/backup/full.sql >/data/backup/insert.sql
# 2.  binlog 的截取
范围：
  起点：通过备份。
    SET @@GLOBAL.GTID_PURGED='202628e9-9265-11ea-b4a0-000c29248f69:1-47';
  终点：通过 
  [root@db01 ~]#  mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000023'" |grep -B 1 'DROP TABLE\ `oldguo`'
  mysql-bin.000023  163044  Gtid  6  163109  SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:54'
  mysql-bin.000023  163109  Query  6  163232  use `oldboy`; DROP TABLE `oldguo` /* generated by server */
[root@db01 ~]# mysqlbinlog --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:48-53' /data/3306/logs/mysql-bin.000023 |grep -B 8  '`oldboy`.`oldguo`'|grep 'GTID_NEXT'
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:49'/*!*/;
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:50'/*!*/;
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:53'/*!*/;
截取：
方法1：
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:48-53' --exclude-gtids='202628e9-9265-11ea-b4a0-000c29248f69:51-52' /data/3306/logs/mysql-bin.000023 >/data/backup/bin.sql
方法2：
 mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:48-50','202628e9-9265-11ea-b4a0-000c29248f69:53' /data/3306/logs/mysql-bin.000023 >/data/backup/bin1.sql
# 3. 恢复数据  
use oldboy;
set sql_log_bin=0;
source /data/backup/create.sql
source /data/backup/insert.sql
commit;
source /data/backup/bin.sql
set sql_log_bin=1;
3.4.5  实现单库单表备份
shell# mkdir -p /data/backup/single_bak
mysql> select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/data/backup/single_bak/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema') into outfile '/tmp/single_bak.sh';
shell# sh /tmp/single_bak.sh &>/tmp/bak.log

Percona Xtrabackup（PXB\XBK）

介绍

xtrabackup --> C  C++

innobackupex  --> perl

物理备份工具，类似于cp文件。支持：全备和增量备份

安装

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

下载软件并安装

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

全备

拷贝，/data/3306/data/下的数据文件。
InnoDB  ：热备。拷贝ibdataN,UNDO00N ,ibtmpN ,ibd 。通过截取变化redo。
非InnoDB：FTWRL，全局锁。拷贝非INNODB的文件frm\myi\myd\...
只能本地备份。

需要指定socket文件

$ vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock

全备实现


$ innobackupex --user=root --password=123 /data/backup/test
说明：备份完成后，自动生成基于时间戳的目录
（1）xtrabackup_binlog_info  
  #  记录binlog位置点， 截取binlog起点位置。
（2）xtrabackup_checkpoints  
  #  LSN号码信息
from_lsn = 0         # 一般增量备份会关注，一般上次备份的to_lsn的位置
to_lsn = 180881595   # CKPT-LSN
last_lsn = 180881604 # xtrabackup_logfile LSN
（3）xtrabackup_info      
  # 备份总览信息
（4）xtrabackup_logfile  
  # 备份期间产生的redo变化
自定义目录备份：
$  innobackupex --user=root --password=123 --no-timestamp /data/backup/xbk/full_`date +%F`

全备恢复应用

# 故障模拟 
$ pkill mysqld 
$ rm -rf /data/3306/data/*
# 使用全备恢复数据 
（1）prepare 准备备份阶段
  重用了CR ：自动故障恢复。DWB+redo前滚和undo回滚。
  $ innobackupex --apply-log /data/backup/xbk/full

copy-back 恢复

方法一：
  [root@db01 full]# cp -a /data/backup/xbk/full/*  /data/3306/data/
  或者：mv
  [root@db01 full]# chown -R mysql.mysql /data/*
  [root@db01 full]# /etc/init.d/mysqld start
  Starting MySQL.... SUCCESS! 
 方法二：
  [root@db01 full]# innobackupex --copy-back /data/backup/xbk/full
  [root@db01 full]# innobackupex --move-back /data/backup/xbk/full
 方法三：
  直接指定数据路径为：
  vim /etc/my.cnf
  datadir=/data/backup/xbk/full
  chown -R mysql. /data/* 
  /etc/init.d/mysqld start

增量备份（incremental）功能

自带的功能。

每次增量一般是将最近一次备份作为参照物。

自动读取参照物cat xtrabackup_checkpoints中to_lsn值，与当前CKPT的LSN对比，备份变化过page。备份期间新的数据变化，通过redo自动备份。

恢复数据时，需要把所有需要的增量合并到FULL中。无法通过增量单独恢复数据，依赖与全备

增量备份演练（FULL(周日)+inc1(周一)+inc2(周二)+inc3(周三)）

mkdir -p /data/backup/

备份前数据准备

create database xbk charset utf8mb4;
use xbk
create table full (id int);
insert into full values(1),(2),(3);
commit;

模拟周日 23:00全备

innobackupex --user=root  --no-timestamp /data/backup/full_`date +%F`

模拟周一白天数据变化

use xbk
create table inc1 (id int);
insert into  inc1 values(1),(2),(3);
commit;

模拟周一23:00增量备份

innobackupex --user=root  --no-timestamp   --incremental --incremental-basedir=/data/backup/full_2020-05-12  /data/backup/inc1_`date +%F`

模拟周二白天数据变化

use xbk
create table inc2 (id int);
insert into  inc2 values(1),(2),(3);
commit;

模拟周二23:00增量备份

innobackupex --user=root   --no-timestamp   --incremental --incremental-basedir=/data/backup/inc1_2020-05-12  /data/backup/inc2_`date +%F`

模拟周三白天数据变化

use xbk
create table inc3(id int);
insert into  inc3 values(1),(2),(3);
commit;

模拟周三23:00增量备份

innobackupex --user=root   --no-timestamp   --incremental --incremental-basedir=/data/backup/inc2_2020-05-12  /data/backup/inc3_`date +%F`

模拟周四白天的数据变化

use xbk
create table inc4(id int);
insert into  inc4 values(1),(2),(3);
commit;

周四下午出现数据损坏。如何恢复？

pkill mysqldrm -rf /data/backup/xbk/full/*

恢复思路？

我们有什么？

备份：full+inc1+inc2+inc3 binlog:full以来全量的binlog

处理备份

需要将inc1\inc2\inc3按顺序依次合并到全备，并进行prepare。
innobackupex --apply-log --redo-only  /data/backup/full_2020-05-12

恢复备份

到这步，数据已经恢复到周三晚上备份结束后的状态

innobackupex --apply-log  --incremental-dir=/data/backup/inc1_2020-05-12 /data/backup/full_2020-05-12
innobackupex --apply-log  --incremental-dir=/data/backup/inc2_2020-05-12 /data/backup/full_2020-05-12
innobackupex --apply-log  /data/backup/full_2020-05-12

查看二进制日志

[root@db01 /data/3306/logs ]# ls
mysql-bin.000001  mysql-bin.000006  mysql-bin.000011  mysql-bin.000016  mysql.log
mysql-bin.000002  mysql-bin.000007  mysql-bin.000012  mysql-bin.000017  slow.log
mysql-bin.000003  mysql-bin.000008  mysql-bin.000013  mysql-bin.000018
mysql-bin.000004  mysql-bin.000009  mysql-bin.000014  mysql-bin.000019
mysql-bin.000005  mysql-bin.000010  mysql-bin.000015  mysql-bin.index

查看pos号

1838就是对应pos号

[root@db01 ~ ]# cat /data/backup/inc2_2020-05-12/xtrabackup_binlog_info 
mysql-bin.000019  1838  2f304d5f-93f8-11ea-a410-000c295e9bd3:1-9,
b246e775-9017-11ea-aa20-000c295e9bd3:1-36,
c4457be3-9427-11ea-8321-000c295e9bd3:1-8

截取周三增量备份后 --> 故障之前所有binlog日志，并进行恢复

mysqlbinlog --skip-gtids --start-position=1838  /data/3306/logs/mysql-bin.000019 >/root/1.sql

登录数据库

set sql_log_bin=0;
source /root/1.sql
set sql_log_bin=0;
mysql> use xbk;
Database changed
mysql> show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| full          |
| inc1          |
| inc2          |
| inc3          |
| inc4          |
+---------------+
5 rows in set (0.00 sec)
以上原数据就以完全恢复

总结

基础备份策略： 
MDP 备份 + binlog   ===> 全备完整恢复、部分数据损坏恢复
XBK full+inc+binlog ===>  全备完整恢复、部分数据损坏恢复
XBK full+binlog     ===>  全备完整恢复、部分数据损坏恢复


文章标签：
云数据库 RDS MySQL 版
日志服务
C++
Perl
SQL
关系型数据库
监控
MySQL
数据库
关键词：
云数据库 RDS MySQL 版备份恢复
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
轩公子谈技术
目录
相关文章
hh真是个慢性子
|
3月前
|
SQL 存储 关系型数据库
MySQL备份：mydumper 备份恢复工具生产实战
MySQL备份：mydumper 备份恢复工具生产实战
hh真是个慢性子
111 1
hh真是个慢性子
|
3月前
|
关系型数据库 MySQL 机器人
【MySQL】两个脚本自动化搞定 MySQL 备份恢复--XtraBackup
【MySQL】两个脚本自动化搞定 MySQL 备份恢复--XtraBackup
hh真是个慢性子
76 0
土木林森
|
3月前
|
运维 关系型数据库 MySQL
"MySQL运维精髓：深入解析数据库及表的高效创建、管理、优化与备份恢复策略"
【8月更文挑战第9天】MySQL是最流行的开源数据库之一，其运维对数据安全与性能至关重要。本文通过最佳实践介绍数据库及表的创建、管理与优化，包括示例代码。涵盖创建/删除数据库、表结构定义/调整、索引优化和查询分析，以及数据备份与恢复等关键操作，助您高效管理MySQL，确保数据完整性和系统稳定运行。
土木林森
369 0
王同学睡醒了
|
11月前
|
关系型数据库 MySQL 数据库
零基础带你学习MySQL—备份恢复数据库（三）
零基础带你学习MySQL—备份恢复数据库（三）
王同学睡醒了
66 0
开化者
|
关系型数据库 MySQL 测试技术
MySQL备份恢复-2
MySQL备份恢复-2
开化者
95 0
开化者
|
存储 安全 关系型数据库
MySQL备份恢复
MySQL备份恢复
开化者
91 0
柴米油盐酱醋
|
关系型数据库 MySQL 数据库
RDS for MySQL8.0物理备份恢复到本地自建数据库
此文章是centos7下的恢复流程。 1、安装MySQL8.0（采用yum方式安装）：wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm yum localinstall mysql80-community-release-el7-1.noarch.rpm yum -y install yum-utils 默认安装的就是8.0版本yum install mysql-community-server 安装好了不要启动数据库。
柴米油盐酱醋
3429 0
极客飞兔
|
SQL 存储 关系型数据库
mysql 中的备份恢复、分区分表、主从复制、读写分离
数据库 mysql 的灾难恢复，高并发必备知识
极客飞兔
365 3
mysql 中的备份恢复、分区分表、主从复制、读写分离
阿里云服务支持
|
SQL 安全 关系型数据库
RDS SQL Server 备份恢复到本地报错：Cannot find server certificate with thumbprint
RDS SQL Server 备份恢复到本地报错：Cannot find server certificate with thumbprint
阿里云服务支持
470 0
南葵思暖
|
存储 SQL 关系型数据库
MySQL备份恢复-3
MySQL备份恢复-3
南葵思暖
103 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 4
    Linux操作系统上的mysql安装
  • 5
    MySQl复制---基于行的复制
  • 6
    mysql中char与varchar的区别分析
  • 7
    阿里云RDS for MySQL 发布三节点企业版
  • 8
    icinga2 check_mysql_health插件
  • 9
    mysql常用操作
  • 10
    mysql查询拼接
  • 1
    中间件MySQL性能瓶颈
    57
  • 2
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
    292
  • 3
    MySQL上亿数据查询优化：实践与技巧
    291
  • 4
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    50
  • 5
    MySQL：union all与union区别详解
    89
  • 6
    学习mysql中使用inner join，left join 等
    50
  • 7
    【最佳实践】MySQL数据库迁移到PXC集群
    92
  • 8
    MySQL PXC集群配置IPv6
    49
  • 9
    【实操记录】MySQL主从配置
    140
  • 10
    【实操记录】MySQL二进制安装包部署
    192

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    阿里云OSS设置跨域访问