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

mysql备份与恢复实践案例

2015-11-25 1057
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL Serverless，价值2615元额度，1个月
简介： 恢复误删数据 case：误操作，删除数据忘记带完整条件，执行 delete from user where age > 30 [ and sex - male ] 需求：将被删除数据还原 恢复前提：完整的数据库操作日志（binlog） 演示： 测试表数...

恢复误删数据
case：误操作，删除数据忘记带完整条件，执行 delete from user where age > 30 [ and sex - male ]
需求：将被删除数据还原
恢复前提：完整的数据库操作日志（binlog）
演示：
测试表数据

mysql> select * from user;
+----+------+------+------+---------------------+
| id | name | age  | sex  | create_time         |
+----+------+------+------+---------------------+
|  1 | M1   |   19 | F    | 2015-11-25 13:38:18 |
|  2 | M2   |   50 | M    | 2015-11-25 13:39:48 |
|  3 | M3   |   40 | F    | 2015-11-25 13:39:48 |
|  4 | M4   |   52 | M    | 2015-11-25 13:39:48 |
|  5 | M5   |   23 | F    | 2015-11-25 13:39:48 |
|  6 | M6   |   34 | M    | 2015-11-25 13:39:48 |
+----+------+------+------+---------------------+
6 rows in set (0.00 sec)

模拟误删数据


mysql> delete from user where sex='F';
Query OK, 3 rows affected (0.13 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+------+------+---------------------+
| id | name | age  | sex  | create_time         |
+----+------+------+------+---------------------+
|  2 | M2   |   50 | M    | 2015-11-25 13:39:48 |
|  4 | M4   |   52 | M    | 2015-11-25 13:39:48 |
|  6 | M6   |   34 | M    | 2015-11-25 13:39:48 |
+----+------+------+------+---------------------+
3 rows in set (0.00 sec)

查看binlog日志

[root@miles mysql]# mysqlbinlog -vv binlog.000002
...
### DELETE FROM `db1`.`user`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='M1' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3=19 /* INT meta=0 nullable=1 is_null=0 */
###   @4='F' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
###   @5=1448444542 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### DELETE FROM `db1`.`user`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='M3' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3=40 /* INT meta=0 nullable=1 is_null=0 */
###   @4='F' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
###   @5=1448444550 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### DELETE FROM `db1`.`user`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='M5' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3=23 /* INT meta=0 nullable=1 is_null=0 */
###   @4='F' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
###   @5=1448444550 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
...

根据日志生成反转sql语句

mysql> desc user;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(10)     | NO   | PRI | NULL              | auto_increment              |
| name        | varchar(30) | NO   |     | NULL              |                             |
| age         | int(3)      | YES  |     | NULL              |                             |
| sex         | varchar(6)  | YES  |     | NULL              |                             |
| create_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.01 sec)


mysql> insert into user values (1,'M1',19,'F',from_unixtime(1448444542));
Query OK, 1 row affected (0.01 sec)
...

恢复误删表、库
case：业务被黑，表被删除了drop table used
需求：将表恢复
前提：备份＋备份以来完整binlog
演示：

测试表数据


mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | M1   |
|    2 | M2   |
|    3 | M3   |
|    4 | M4   |
|    5 | M5   |
+------+------+
5 rows in set (0.00 sec)

备份数据库

[root@miles ~]# innobackupex --user=root --password=beijing /home/mysql/backup/
...
151127 19:32:14 Executing UNLOCK TABLES
151127 19:32:14 All tables unlocked
151127 19:32:14 Backup created in directory '/home/mysql/backup//2015-11-27_19-32-07'
MySQL binlog position: filename 'binlog.000003', position '961'
151127 19:32:14 [00] Writing backup-my.cnf
151127 19:32:14 [00]        ...done
151127 19:32:15 [00] Writing xtrabackup_info
151127 19:32:15 [00]        ...done
xtrabackup: Transaction log of lsn (449989787) to (449989787) was copied.
151127 19:32:15 completed OK!

模拟数据操作

mysql> insert into test values (6,'M6'),(7,'M7'),(8,'M8'),(9,'M9'),(10,'M10');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

还原数据备份

[root@miles ~]# innobackupex --apply-log  /home/mysql/backup/2015-11-27_19-32-07

利用还原出来的数据重新启动一个实例，然后在新启动的实例里应用之前的日志

[root@miles backup]# mv 2015-11-27_19-32-07 testEnv3309

[root@miles backup]# cp /etc/my.cnf testEnv3309/

[root@miles backup]# cd testEnv3309/

[root@miles testEnv3309]# mv my.cnf my3309.cnf

[root@miles testEnv3309]# vi my3309.cnf 
port=3309
全局替换文件目录为/home/mysql/backup/testEnv3309/
:%s#/data/mysql#/home/mysql/backup/testEnv3309#g

启动新实例

/usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/backup/testEnv3309/my3309.cnf  &

[root@miles ~]# ps -ef |grep 3309
root      12145  12083  0 10:40 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/backup/testEnv3309/my3309.cnf
mysql     12429  12145  0 10:40 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/backup/testEnv3309/my3309.cnf --basedir=/usr/local/mysql --datadir=/home/mysql/backup/testEnv3309 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/home/mysql/backup/testEnv3309/log/error.log --pid-file=/home/mysql/backup/testEnv3309/mysqld.pid --socket=/home/mysql/backup/testEnv3309/mysql.sock

查看备份信息

[root@miles testEnv3309]# more xtrabackup_binlog_info
binlog.000003   961

查看binlo信息

[root@miles mysql]# mysqlbinlog -vv binlog.000003
...
# at 1187
#151127 19:34:51 server id 1  end_log_pos 1292  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1448624091/*!*/;
DROP TABLE `test` /* generated by server */

基于日志恢复

[root@miles mysql]# mysqlbinlog -vv --start-position=961 --stop-position=1187 binlog.000003|mysql -uroot -pbeijing --socket=/home/mysql/backup/testEnv3309/mysql.sock

查看数据库恢复情况

[root@miles testEnv3309]# mysql -uroot -p --socket=/home/mysql/backup/testEnv3309/mysql.sock db1

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

导出test表并在正式库恢复

[root@miles mysql]# mysqldump --defaults-file=/home/mysql/backup/testEnv3309/my3309.cnf -uroot -pbeijing  --single-transaction --socket=/home/mysql/backup/testEnv3309/mysql.sock db1 test > /home/mysql/backup/db1_test.sql 
--在正式库恢复表test
mysql> source /home/mysql/backup/db1_test.sql

或者

[root@miles log]# mysqldump -uroot -pbeijing --socket=/home/mysql/backup/testEnv3309/mysql.sock --single-transaction db1 test |mysql -uroot -p --socket=/data/mysql/mysql.sock db1

将数据库恢复到指定时间点
case：游戏bug，导致很多玩家利用bug刷金币
需求：游戏回滚，数据库也需要回滚
前提：有效备份+完整的数据库操作日志（binlog）
演示：

全量备份

[root@miles log]# innobackupex --user=root --password=beijing /home/mysql/backup/
...151129 15:53:36 Executing UNLOCK TABLES
151129 15:53:36 All tables unlocked
151129 15:53:36 Backup created in directory '/home/mysql/backup//2015-11-29_15-53-30'
MySQL binlog position: filename 'binlog.000005', position '887'
151129 15:53:36 [00] Writing backup-my.cnf
151129 15:53:36 [00]        ...done
151129 15:53:36 [00] Writing xtrabackup_info
151129 15:53:36 [00]        ...done
xtrabackup: Transaction log of lsn (449994896) to (449994896) was copied.
151129 15:53:36 completed OK!

模拟备份后发生的操作

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | M1   |
|    2 | M2   |
|    3 | M3   |
|    4 | M4   |
|    5 | M5   |
|    6 | M6   |
|    7 | M7   |
|    8 | M8   |
|    9 | M9   |
|   10 | M10  |
+------+------+
10 rows in set (0.00 sec)

mysql> insert into test values(11,'M11'),(12,'M12'),(13,'M13');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> drop database db1;
Query OK, 2 rows affected (0.02 sec)

还原备份

[root@miles log]# innobackupex --apply-log /home/mysql/backup/2015-11-29_15-53-30

利用还原数据库重新启动一个实例，并进行恢复

[root@miles backup]# mv 2015-11-29_15-53-30 testEnv3310
[root@miles backup]# cp testEnv3309/my3309.cnf   testEnv3310/my3310.cnf
[root@miles backup]# vi testEnv3310/my3310.cnf
:%s/3309/3310/g

查看备份信息

[root@miles testEnv3310]# more xtrabackup_binlog_info
binlog.000005   887

[root@miles testEnv3310]# mysqlbinlog -vv --start-position=887 binlog.000005
# at 954
#151129 15:55:54 server id 1  end_log_pos 1071  Query   thread_id=2     exec_time=0     error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1448783754/*!*/;
insert into test values(11,'M11'),(12,'M12'),(13,'M13')
/*!*/;
# at 1071
#151129 15:55:54 server id 1  end_log_pos 1098  Xid = 94
COMMIT/*!*/;
# at 1098
#151129 15:56:17 server id 1  end_log_pos 1177  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1448783777/*!*/;
drop database db1

恢复到指定时间点

[root@miles testEnv3310]# mysqlbinlog --start-position=887 --stop-datetime="2015-11-29 15:56:17"  binlog.000005 |  mysql -uroot -pbeijing --socket=/home/mysql/backup/testEnv3310/mysql.sock

查看恢复数据

[root@miles mysql]# mysql -uroot -pbeijing --socket=/home/mysql/backup/testEnv3310/mysql.sock db1

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|       13 |
+----------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | M1   |
|    2 | M2   |
|    3 | M3   |
|    4 | M4   |
|    5 | M5   |
|    6 | M6   |
|    7 | M7   |
|    8 | M8   |
|    9 | M9   |
|   10 | M10  |
|   11 | M11  |
|   12 | M12  |
|   13 | M13  |
+------+------+
13 rows in set (0.00 sec)
文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
MySQL
数据库
监控
关键词：
云数据库 RDS MySQL 版备份
云数据库 RDS MySQL 版案例
云数据库 RDS MySQL 版实践
云数据库 RDS MySQL 版备份案例
云数据库 RDS MySQL 版恢复
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
miles_wong
目录
相关文章
魏红斌
|
12天前
|
关系型数据库 MySQL Shell
备份 MySQL 的 shell 脚本(mysqldump版本)
【4月更文挑战第28天】
魏红斌
23 0
神秘泣男子
|
4天前
|
关系型数据库 MySQL 数据管理
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
神秘泣男子
14 0
神秘泣男子
|
4天前
|
存储 安全 关系型数据库
MySQL中使用percona-xtrabackup工具 三种备份及恢复 （超详细教程）
MySQL中使用percona-xtrabackup工具 三种备份及恢复 （超详细教程）
神秘泣男子
21 1
你的小笔记本YY
|
9天前
|
SQL 数据可视化 关系型数据库
【MySQL-11】多表查询全解-【多表关系/内外自连接/子查询/多表查询案例链接】（可cv代码＆案例演示）
【MySQL-11】多表查询全解-【多表关系/内外自连接/子查询/多表查询案例链接】（可cv代码＆案例演示）
你的小笔记本YY
17 6
你的小笔记本YY
|
9天前
|
关系型数据库 MySQL Linux
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
你的小笔记本YY
379 4
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
你的小笔记本YY
|
9天前
|
关系型数据库 MySQL 数据库
【MySQL-10】DCL-数据控制语言-【管理用户＆权限控制】 （语法语句＆案例演示＆可cv案例代码）
【MySQL-10】DCL-数据控制语言-【管理用户＆权限控制】 （语法语句＆案例演示＆可cv案例代码）
你的小笔记本YY
18 5
【MySQL-10】DCL-数据控制语言-【管理用户＆权限控制】 （语法语句＆案例演示＆可cv案例代码）
你的小笔记本YY
|
9天前
|
关系型数据库 MySQL Linux
【MySQL-9】一文带你搞定 外键约束＆其【更新/删除行为】（可cv代码＆案例演示）
【MySQL-9】一文带你搞定 外键约束＆其【更新/删除行为】（可cv代码＆案例演示）
你的小笔记本YY
32 5
你的小笔记本YY
|
9天前
|
SQL 关系型数据库 MySQL
【MySQL】DQL-案例练习-DQL基本介绍＆语法＆执行顺序（代码演示）
【MySQL】DQL-案例练习-DQL基本介绍＆语法＆执行顺序（代码演示）
你的小笔记本YY
18 7
你的小笔记本YY
|
9天前
|
SQL 关系型数据库 MySQL
【MySQL-8】DQL-查询语句全解 [ 基础/条件/分组/排序/分页查询 ]（附带代码演示＆案例练习）
【MySQL-8】DQL-查询语句全解 [ 基础/条件/分组/排序/分页查询 ]（附带代码演示＆案例练习）
你的小笔记本YY
19 6
你的小笔记本YY
|
9天前
|
SQL 关系型数据库 MySQL
【MySQL】DQL-条件查询语句全解（附带代码演示＆案例练习）
【MySQL】DQL-条件查询语句全解（附带代码演示＆案例练习）
你的小笔记本YY
27 4

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    [MySQL]SQL优化之索引的使用规则
    41
  • 2
    【mysql】—— 用户管理
    35
  • 3
    【mysql】—— 视图
    34
  • 4
    【mysql】—— 表的增删改查
    42
  • 5
    【mysql】—— 表的约束
    38
  • 6
    【MYSQL】—— MySQL 在 Centos 7环境安装
    66
  • 7
    MySQL模糊查询二三事
    27
  • 8
    MySQL的JOIN到底是怎么玩的
    109
  • 9
    MySQL怎样删除重复数据，只保留一条？
    39
  • 10
    【怒怼大厂面试官】你先说说知道哪些MySQL的高级特性
    53

    • 相关课程

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

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

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

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）