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

mysql备份恢复实验

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

内容大纲：

一 mysqldump备份

二 sql语句备份

三 mysqlbinlog备份恢复



一 mysqldump数据文件备份恢复

mysql> use cw_d;

Database changed

mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

|  4 | cw4  |      4 |

|  5 | cw5  |      5 |

|  6 | cw6  |      6 |

|  7 | cw7  |      7 |

|  8 | cw8  |      8 |

|  9 | cw9  |      9 |

+----+------+--------+

9 rows in set (0.00 sec)


mysql>


备份数据库cw_d

[root@69 ~]# mysqldump -u root -p cw_d>cw.sql

Enter password:

[root@69 ~]#


删除数据库cw_d，然后查询记录


mysql> drop table cw;

Query OK, 0 rows affected (0.02 sec)


mysql> select * from cw;

ERROR 1146 (42S02): Table 'cw_d.cw' doesn't exist


执行恢复：

[root@69 ~]# mysql -u root -p cw_d <cw.sql

Enter password:

[root@69 ~]#

验证语句，如果能查询到结果，说明已经成功了。

mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

|  4 | cw4  |      4 |

|  5 | cw5  |      5 |

|  6 | cw6  |      6 |

|  7 | cw7  |      7 |

|  8 | cw8  |      8 |

|  9 | cw9  |      9 |

+----+------+--------+

9 rows in set (0.00 sec)


mysql>


二 SQL语句备份恢复

mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

|  4 | cw4  |      4 |

|  5 | cw5  |      5 |

|  6 | cw6  |      6 |

|  7 | cw7  |      7 |

|  8 | cw8  |      8 |

|  9 | cw9  |      9 |

+----+------+--------+

9 rows in set (0.00 sec)


mysql> select * into outfile '/root/cw_sql.sql' from cw;

ERROR 1 (HY000): Can't create/write to file '/root/cw_sql.sql' (Errcode: 13)

mysql> select * into outfile '/root/cw.sql' from cw;

ERROR 1 (HY000): Can't create/write to file '/root/cw.sql' (Errcode: 13)   //权限不够，需要mysql能够写入的目录。

mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

|  4 | cw4  |      4 |

|  5 | cw5  |      5 |

|  6 | cw6  |      6 |

|  7 | cw7  |      7 |

|  8 | cw8  |      8 |

|  9 | cw9  |      9 |

+----+------+--------+

9 rows in set (0.00 sec)


mysql> select * into outfile '/tmp/cw.sql' from cw;    //备份语句

Query OK, 9 rows affected (0.00 sec)


mysql> delete from cw;

Query OK, 9 rows affected (0.00 sec)


mysql> select * from cw;

Empty set (0.00 sec)



mysql> load data infile '/tmp/cw.sql' into table cw;    //恢复语句

Query OK, 9 rows affected (0.01 sec)

Records: 9  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from cw;     //验证成功，能够查询到语句，说明恢复成功。

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

|  4 | cw4  |      4 |

|  5 | cw5  |      5 |

|  6 | cw6  |      6 |

|  7 | cw7  |      7 |

|  8 | cw8  |      8 |

|  9 | cw9  |      9 |

+----+------+--------+

9 rows in set (0.00 sec)


mysql>


三 mysqlbinlog备份恢复

1.清理下二进制文件

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)


mysql> show master logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     21817 |

| mysql-bin.000002 |    752122 |

| mysql-bin.000003 |  45418387 |

| mysql-bin.000004 |       126 |

| mysql-bin.000005 |   6615020 |

| mysql-bin.000006 |   6708657 |

| mysql-bin.000007 |  29665204 |

| mysql-bin.000008 |       753 |

| mysql-bin.000009 |    307620 |

| mysql-bin.000010 |       107 |

+------------------+-----------+

10 rows in set (0.00 sec)


mysql>

现在已经是已mysql-bin.000010 开始了。

2.

mysql> use cw_d;

Database changed

mysql> create table cw( id int auto_increment not null primary key, name varchar(20), number int(12));

Query OK, 0 rows affected (0.04 sec)


mysql>




mysql> select * from cw;

Empty set (0.00 sec)


mysql> insert into cw(name,number) values('cw1',0001);

Query OK, 1 row affected (0.00 sec)


mysql> insert into cw(name,number) values('cw2',0002);

Query OK, 1 row affected (0.01 sec)


mysql> insert into cw(name,number) values('cw3',0003);

Query OK, 1 row affected (0.01 sec)


mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

+----+------+--------+

3 rows in set (0.00 sec)


mysql> show master logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     21817 |

| mysql-bin.000002 |    752122 |

| mysql-bin.000003 |  45418387 |

| mysql-bin.000004 |       126 |

| mysql-bin.000005 |   6615020 |

| mysql-bin.000006 |   6708657 |

| mysql-bin.000007 |  29665204 |

| mysql-bin.000008 |       753 |

| mysql-bin.000009 |    307620 |

| mysql-bin.000010 |      7676 |

+------------------+-----------+

10 rows in set (0.00 sec)


mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)


mysql> show master logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     21817 |

| mysql-bin.000002 |    752122 |

| mysql-bin.000003 |  45418387 |

| mysql-bin.000004 |       126 |

| mysql-bin.000005 |   6615020 |

| mysql-bin.000006 |   6708657 |

| mysql-bin.000007 |  29665204 |

| mysql-bin.000008 |       753 |

| mysql-bin.000009 |    307620 |

| mysql-bin.000010 |      7719 |

| mysql-bin.000011 |       710 |

+------------------+-----------+

11 rows in set (0.00 sec)


mysql>




mysql> insert into cw(name,number) values('cw4',0004);

Query OK, 1 row affected (0.01 sec)


mysql> insert into cw(name,number) values('cw5',0005);

Query OK, 1 row affected (0.00 sec)


mysql> insert into cw(name,number) values('cw6',0006);

Query OK, 1 row affected (0.01 sec)


mysql> insert into cw(name,number) values('cw7',0007);

Query OK, 1 row affected (0.01 sec)


mysql> delete from cw where number=0005;

Query OK, 1 row affected (0.00 sec)


mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

|  4 | cw4  |      4 |

|  6 | cw6  |      6 |

|  7 | cw7  |      7 |

+----+------+--------+

6 rows in set (0.00 sec)


mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)


mysql> show master logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     21817 |

| mysql-bin.000002 |    752122 |

| mysql-bin.000003 |  45418387 |

| mysql-bin.000004 |       126 |

| mysql-bin.000005 |   6615020 |

| mysql-bin.000006 |   6708657 |

| mysql-bin.000007 |  29665204 |

| mysql-bin.000008 |       753 |

| mysql-bin.000009 |    307620 |

| mysql-bin.000010 |      7719 |

| mysql-bin.000011 |      4560 |

| mysql-bin.000012 |       107 |

+------------------+-----------+

12 rows in set (0.00 sec)


mysql>




mysql> insert into cw(name,number) values('cw8',0008);

Query OK, 1 row affected (0.01 sec)


mysql> insert into cw(name,number) values('cw9',0009);

Query OK, 1 row affected (0.01 sec)


mysql> drop table cw;

Query OK, 0 rows affected (0.04 sec)


mysql> select * from cw;

ERROR 1146 (42S02): Table 'cw_d.cw' doesn't exist

mysql>



二 恢复过程


由于有3个日志，且每个日志都有其它数据库写入，所以需要手工先分析日志，具体分析过程如下：

mysqlbinlog ./mysql-bin.000010


1.分析日志：

create database cw_d

/*!*/;

# at 4411

#140324  2:19:06 server id 1  end_log_pos 4568  Query   thread_id=32914 exec_time=0     error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395641946/*!*/;

create table cw( id int auto_increment not null primary key, name varchar(20), number int(12))

/*!*/;

# at 4568


--stop-position=4568  --start-position=4411


恢复表：

mysqlbinlog --stop-position=4568  --start-position=4411 ./mysql-bin.000010


恢复数据1：

mysqlbinlog --stop-position=7073  --start-position=6473 ./mysql-bin.000010



执行恢复表后：

[root@69 mysql]# mysqlbinlog --stop-position=4568  --start-position=4411 ./mysql-bin.000010 | mysql -u root -p1807099411


mysql> select * from cw;

ERROR 1146 (42S02): Table 'cw_d.cw' doesn't exist

mysql> select * from cw;         //检查可以查询到表了，说明恢复成功了。

Empty set (0.00 sec)


mysql>



2.分析日志：

mysqlbinlog ./mysql-bin.000011

#140324  2:18:59 server id 1  end_log_pos 4411  Query   thread_id=32914 exec_time=0     error_code=0

SET TIMESTAMP=1395641939/*!*/;

SET @@session.sql_mode=0/*!*/;

create database cw_d

/*!*/;

# at 4411

#140324  2:19:06 server id 1  end_log_pos 4568  Query   thread_id=32914 exec_time=0     error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395641946/*!*/;

create table cw( id int auto_increment not null primary key, name varchar(20), number int(12))

/*!*/;

# at 4568




执行恢复表数据后

[root@69 mysql]# mysqlbinlog --stop-position=7073  --start-position=6473 ./mysql-bin.000010 |mysql -u root -p1807099411

mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

+----+------+--------+

3 rows in set (0.00 sec)


mysql>



分析另外2个二进制文件：

mysqlbinlog ./mysql-bin.000011

# at 1381

#140324  2:21:20 server id 1  end_log_pos 1409  Intvar

SET INSERT_ID=4/*!*/;

# at 1409

#140324  2:21:20 server id 1  end_log_pos 1518  Query   thread_id=32914 exec_time=0     error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395642080/*!*/;

insert into cw(name,number) values('cw4',0004)

/*!*/;

# at 1518

#140324  2:21:20 server id 1  end_log_pos 1545  Xid = 5378105

COMMIT/*!*/;

# at 1545

#140324  2:21:28 server id 1  end_log_pos 1613  Query   thread_id=32914 exec_time=0     error_code=0

SET TIMESTAMP=1395642088/*!*/;

BEGIN

/*!*/;

# at 1613

#140324  2:21:28 server id 1  end_log_pos 1641  Intvar

SET INSERT_ID=5/*!*/;

# at 1641

#140324  2:21:28 server id 1  end_log_pos 1750  Query   thread_id=32914 exec_time=0     error_code=0

SET TIMESTAMP=1395642088/*!*/;

insert into cw(name,number) values('cw5',0005)

/*!*/;

# at 1750

#140324  2:21:28 server id 1  end_log_pos 1777  Xid = 5378131

COMMIT/*!*/;

# at 1777

#140324  2:21:34 server id 1  end_log_pos 1845  Query   thread_id=32914 exec_time=0     error_code=0

SET TIMESTAMP=1395642094/*!*/;

BEGIN

/*!*/;

# at 1845

#140324  2:21:34 server id 1  end_log_pos 1873  Intvar

SET INSERT_ID=6/*!*/;

# at 1873

#140324  2:21:34 server id 1  end_log_pos 1982  Query   thread_id=32914 exec_time=0     error_code=0

SET TIMESTAMP=1395642094/*!*/;

insert into cw(name,number) values('cw6',0006)

/*!*/;

# at 1982




# at 2612

#140324  2:21:49 server id 1  end_log_pos 2680  Query   thread_id=32914 exec_time=0     error_code=0

SET TIMESTAMP=1395642109/*!*/;

SET @@session.sql_mode=0/*!*/;

BEGIN

/*!*/;

# at 2680

#140324  2:21:49 server id 1  end_log_pos 2708  Intvar

SET INSERT_ID=7/*!*/;

# at 2708

#140324  2:21:49 server id 1  end_log_pos 2817  Query   thread_id=32914 exec_time=0     error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395642109/*!*/;

insert into cw(name,number) values('cw7',0007)

/*!*/;

# at 2817

#140324  2:21:49 server id 1  end_log_pos 2844  Xid = 5378286

COMMIT/*!*/;

# at 2844


根据上面的语句生成日志：

[root@69 mysql]# mysqlbinlog --stop-position=2077  --start-position=1982 ./mysql-bin.000011 |mysql -u root -p1807099411


[root@69 mysql]# mysqlbinlog --stop-position=2844  --start-position=2612 ./mysql-bin.000011 |mysql -u root -p1807099411



恢复日志


mysqlbinlog --stop-position=1777  --start-position=1381 ./mysql-bin.000012 |mysql -u root -p1807099411



mysql> select * from cw;

+----+------+--------+

| id | name | number |

+----+------+--------+

|  1 | cw1  |      1 |

|  2 | cw2  |      2 |

|  3 | cw3  |      3 |

|  4 | cw4  |      4 |

|  5 | cw5  |      5 |

|  6 | cw6  |      6 |

|  7 | cw7  |      7 |

|  8 | cw8  |      8 |

|  9 | cw9  |      9 |

+----+------+--------+

9 rows in set (0.00 sec)


mysql>



本文转自 woshiwei201 51CTO博客，原文链接:http://blog.51cto.com/chenwei/1382718



文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
MySQL
数据库
SQL
关键词：
云数据库 RDS MySQL 版实验
云数据库 RDS MySQL 版备份恢复
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
科技小能手
目录
相关文章
Meteor.792
|
存储 关系型数据库 MySQL
MySQL数据库实验九 MySQL应用系统开发
MySQL数据库实验九 MySQL应用系统开发
Meteor.792
298 0
java冯坚持
|
24天前
|
关系型数据库 MySQL Java
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库，创建模型实例，并自动或手动创建数据库表，以及对这些表进行操作的详细教程。
java冯坚持
55 0
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
hh真是个慢性子
|
3月前
|
SQL 存储 关系型数据库
MySQL备份：mydumper 备份恢复工具生产实战
MySQL备份：mydumper 备份恢复工具生产实战
hh真是个慢性子
102 1
hh真是个慢性子
|
3月前
|
关系型数据库 MySQL 机器人
【MySQL】两个脚本自动化搞定 MySQL 备份恢复--XtraBackup
【MySQL】两个脚本自动化搞定 MySQL 备份恢复--XtraBackup
hh真是个慢性子
68 0
土木林森
|
3月前
|
运维 关系型数据库 MySQL
"MySQL运维精髓：深入解析数据库及表的高效创建、管理、优化与备份恢复策略"
【8月更文挑战第9天】MySQL是最流行的开源数据库之一，其运维对数据安全与性能至关重要。本文通过最佳实践介绍数据库及表的创建、管理与优化，包括示例代码。涵盖创建/删除数据库、表结构定义/调整、索引优化和查询分析，以及数据备份与恢复等关键操作，助您高效管理MySQL，确保数据完整性和系统稳定运行。
土木林森
337 0
王同学睡醒了
|
11月前
|
关系型数据库 MySQL 数据库
零基础带你学习MySQL—备份恢复数据库（三）
零基础带你学习MySQL—备份恢复数据库（三）
王同学睡醒了
66 0
骚戴
|
6月前
|
存储 Java 关系型数据库
实验设备管理系统【GUI/Swing+MySQL】（Java课设）
实验设备管理系统【GUI/Swing+MySQL】（Java课设）
骚戴
28 0
Meteor.792
|
安全 关系型数据库 MySQL
MySQL数据库实验七 MySQL安全管理
MySQL数据库实验七 MySQL安全管理
Meteor.792
226 0
Meteor.792
|
存储 关系型数据库 MySQL
MySQL数据库实验六 MySQL并发事务与锁机制
MySQL数据库实验六 MySQL并发事务与锁机制
Meteor.792
92 0
Meteor.792
|
关系型数据库 MySQL 数据库
MySQL数据库实验三 MySQL查询
MySQL数据库实验三 MySQL查询
Meteor.792
398 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    MySQL性能优化（硬件，系统配置，表结构，SQL语句）
  • 3
    MySQL集群自动安装脚本
  • 4
    mysql数据库主从不同步的解决方法
  • 5
    mysql左关联不走索引的一个问题的排查过程及思考
  • 6
    应用MySQL MGR
  • 7
    一台mysql管理多个实例
  • 8
    如何修改WAMP中mysql默认空密码
  • 9
    mysql的总结6--视图的学习
  • 10
    删除MySQL重复数据
  • 1
    MySQL 计算时间差分钟
    88
  • 2
    MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
    119
  • 3
    MySQL是怎样存储数据的？
    61
  • 4
    MySQL高可用架构探秘：主从复制剖析、切换策略、延迟优化与架构选型
    96
  • 5
    “时光机”与“多维视界”⭐️MySQL中原子性与隔离性的科幻大片
    60
  • 6
    MySQL持久化不为人知的一面⭐️卡顿现象的根源与对策
    65
  • 7
    MySQL怎样处理排序⭐️如何优化需要排序的查询？
    44
  • 8
    完蛋！😱 我被MySQL索引失效包围了！
    52
  • 9
    5分钟搞懂MySQL半连接优化⭐️多种半连接的优化策略
    64
  • 10
    MySQL的3种索引合并优化⭐️or到底能不能用索引?
    67

    • 相关课程

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

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 快照备份与恢复
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    DataWorks智能数据建模全面公测开始啦！