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

基于mysqldump备份mysql（MairaDB）

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

一、简介

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等

   它可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建你的数据库所需要的SQL命令。这个命令取得所有的模式并且将其转换成DDL语法（CREATE语句，即数据库定义语句），取得所有的数据，并且从这些数据中创建INSERT语句。这个工具将你的数据库中所有的设计倒转。因为所有的东西都被包含到了一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中。这个工具令人难以置信地简单而快速。决不会有半点让人头疼的地方。

   mysqldump是一个逻辑备份工具，它支持InnoDB的热备，MyISAM温备（需锁表），很难实现差异备份和增量备份，如果非要这么做的话，需结合binlog日志文件；它是一个单线程的备份工具，所以备份和恢复过程较慢，如果你的数据量大于10G的话，最好还是使用别的工具吧

二、mysqldump的备份方式及选项

1、备份类型

注意：备份前要加锁

（1）备份单个库或库中的指定表

1
# mysqldump [options] db_name [tb1][tb2]...

单个库备份例子：

1
#mysqldump -uroot -hlocalhost -p hellodb > /backups/hdb.sql

当备份完成后，如果数据小的话，可以使用vim打开查看；如果数据量大的话，千万别这么玩儿

恢复时：如果目标不存在，需事先手动创建

（2）备份多个数据库

1
#mysqldump [options] --databases[options] DB1 [DB2 DB3...] > /backups/bak.sql

哪怕只有一个库时，最好也加上--databases，真正的备份方式应该是这样的：

1
#mysqldump --databases hellodb --lock-all-tables > /backups/hdb.sql

（3）备份所有数据库

1
#mysqldump [options] --all-databases [options] > /backups/all.sql

2、选项[options]

（1）--lock-all-tables

   请求锁定所有表之后再备份，对MyISAM、InnoDB、Aria做温备

   但是这种方式并不理想，因为它锁定了所有表

（2）--single-transaction

   启用一个单一的大事务，基于多版本并发控制完成对InnoDB存储的热备

   能够对InnoDB存储引擎实现热备

   如果使用此选项，就不要使用--lock-all-tables了，因为它会自动加锁

（3）备份代码

   ①、--events：备份事件调度器代码

   ②、--routines：备份存储过程和存储函数

   ③、--triggers备份解发器

（4）备份时滚动日志

   --flush-logs：备份前，请求到锁之后滚动日志

（5）复制时的同步位置标记

   --master-data=[0|1|2]

   0：不记录

   1：记录为CHANGE MASTER语句

   2：记录为注释的CHANGE MASTER语句

三、建议及备份策略

1、使用mysqldump备份

   请求锁：--lock-all-tables或使用--single-transaction进行innodb热备

   滚动日志：--flush-logs

   选定要备份的库：--databases

   记录二进制日志文件及位置：--master-data=

2、恢复

   关闭二进制日志（恢复过程没有必要记录二进制日志），关闭其它用户连接

3、备份策略（基于mysqldump）

   备份：

       mysqldump+二进制日志文件

   恢复：

       完全备份+各二进制日志文件中至此刻的事件

   对于MySQL配置文件，以及与MySQL相关的OS配置文件在每次修改后都应该直接进行备份

四、备份及恢复

1、准备工作

（1）数据库前提说明

   数据目录在/mydata/data下，配置文件在/etc/my.cnf，二进制日志在/mydata/binlogs

（2）创建备份存储目录

1
[root@shuishui ~] # mkdir /backups/

（3）导入数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@shuishui ~] # mysql < hellodb.sql
[root@shuishui ~] # mysql
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| bynr               |
| hellodb            |
| hhht               |
| hlbrc              |
| information_schema |
| makingware         |
| mysql              |
| performance_schema |
|  test                |
| ultrax             |
+--------------------+
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> 
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

2、单库备份（温备）及恢复

（1）查看hellodb数据库当前所使用的二进制日志及位置

1
2
3
4
5
6
MariaDB [hellodb]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000019 |     8951 |              |                  |
+-------------------+----------+--------------+------------------+

（2）查看hello的数据库引擎，然后选用备份方式（申请锁表还是单事务）

wKiom1NgTznAIpVLAACBYwLI4YQ689.png

查看表状态信息得知，hellodb中的所有表都是MyISAM存储引擎的，那就可以申请全局锁来备份了

（3）备份操作

1
2
3
4
5
6
[root@shuishui ~] # mysqldump -uroot -p -hlocalhost --databases hellodb --flush-logs --master-data=2 > /backups/hellodb.bak.sql
Enter password:
[root@shuishui ~] # ll /backups/hellodb.bak.sql
-rw-r--r-- 1 root root 7953 Apr 29 15:29  /backups/hellodb .bak.sql
[root@shuishui ~] # file /backups/hellodb.bak.sql
/backups/hellodb .bak.sql: ASCII text, with very long lines

（4）恢复

   ①、在备份后，对hellodb又做了修改操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [hellodb]>
MariaDB [hellodb]> create table nmg (
     ->  id  tinyint auto_increment not null primary key,
     -> city varchar(20)
     -> );
Query OK, 0 rows affected (0.07 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| nmg               |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

   ②、手误，删除hellodb数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [hellodb]> drop database hellodb;
Query OK, 8 rows affected (0.09 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| bynr               |
| hhht               |
| hlbrc              |
| information_schema |
| makingware         |
| mysql              |
| performance_schema |
|  test                |
| ultrax             |
+--------------------+

   ③、恢复

   第一步：导出二进制日志，以备即时点恢复使用

1
2
3
4
5
6
7
8
[root@shuishui ~] # cd /mydata/binlogs/
[root@shuishui binlogs] # ls
master-bin.000001  master-bin.000007  master-bin.000013  master-bin.000019
master-bin.000002  master-bin.000008  master-bin.000014  master-bin.000020
master-bin.000003  master-bin.000009  master-bin.000015  master-bin.index
master-bin.000004  master-bin.000010  master-bin.000016
master-bin.000005  master-bin.000011  master-bin.000017
master-bin.000006  master-bin.000012  master-bin.000018

   因为我们刚才在备份的时候做了滚动日志的操作（--flush-logs），所以在备份之后的所有操作都应该记录到了第20个二进制日志文件中，现在我们导出来看看：

1
2
[root@shuishui binlogs] # mysqlbinlog master-bin.000020 > mysqldump.sql
[root@shuishui binlogs] # vim mysqldump.sql

wKiom1Nga6-TAr3yAAAnCpf27Wo293.png


从上图可以看出来，我们在第20个二进制日志文件中的第593个点执行了"drop database hellodb"，那我们在恢复时就以593这个点为stop-position

1
2
3
[root@shuishui binlogs] # pwd
/mydata/binlogs
[root@shuishui binlogs] # mysqlbinlog --stop-position=593 master-bin.000020 > /backups/binlogbakup.sql


  第二步：恢复完全备份 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [(none)]>  set  session sql_log_bin=0;    #恢复过程不需记录二进制日志，切记，在恢复完成后一定要开启二进制日志，否则后续操作都不会被记录到二进制日志
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>  source  /backups/hellodb .bak.sql
MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| bynr               |
| hellodb            |
| hhht               |
| hlbrc              |
| information_schema |
| makingware         |
| mysql              |
| performance_schema |
|  test                |
| ultrax             |
+--------------------+


1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

查看当前hellodb数据库中的表，是没有我们在备份这后创建的那个nmg表，所以接下来的操作我们应该做即时点恢复

   第三步：即时点恢复

   这里做即时点恢复，我们只需要导入刚才准备好的那个/backups/binlogbakup.sql就可以了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [hellodb]>  source  /backups/binlogbakup .sql
MariaDB [hellodb]>
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| nmg               |     #做即时点恢复表nmg成功
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

   ④、开启二进制日志记录

1
2
3
MariaDB [hellodb]>  set  session sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
#千万别忘了，否则后续操作就不会被记录到二进制日志中

五、结语

   到这里，关于mysqldump的演示就算完成了，虽然只做了单库演示，但多库备份恢复也是一样的道理，就不再演示了

   还有一个要说的就是：这里备份的是MyISAM存储引擎的表，如果是InnoDB引擎的表，应该使用下面这样的命令

1
[root@MariaDB ~] # mysqldump -uroot -hlocalhost -p --database hellodb --single-transaction --flush-logs > /backups/abc.sql









本文转自 nmshuishui 51CTO博客，原文链接：http://blog.51cto.com/nmshuishui/1404963，如需转载请自行联系原作者
文章标签：
云数据库 RDS MySQL 版
日志服务
SQL
关系型数据库
监控
数据库
存储
关键词：
云数据库 RDS MySQL 版备份
云数据库 RDS MySQL 版mysqldump
mysqldump云数据库 RDS MySQL 版
云数据库 RDS MySQL 版mairadb
mysqldump备份云数据库 RDS MySQL 版
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
余二五
目录
相关文章
源码星辰
|
6月前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
源码星辰
253 4
潇洒洒
|
4月前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下，实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
潇洒洒
331 3
最好zzz
|
4月前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下，MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务，我们可以每天自动执行数据库备份，从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
最好zzz
186 3
sunrr
|
5月前
|
安全 关系型数据库 MySQL
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
sunrr
132 3
sunrr
|
5月前
|
存储 关系型数据库 MySQL
MySQL如何备份？
【10月更文挑战第2天】MySQL如何备份？
sunrr
425 3
蓝易云
|
4天前
|
关系型数据库 MySQL 数据库连接
docker拉取MySQL后数据库连接失败解决方案
通过以上方法，可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限，以及检查主机防火墙设置等。通过逐步排查，可以快速定位并解决连接问题，确保MySQL服务的正常使用。
蓝易云
105 82
游客r2tgrg3ez7yzc
|
2月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示：“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后，连接时提示不允许连接问题解决
游客r2tgrg3ez7yzc
240 69
技术自由圈/原疯狂创客圈
|
7天前
|
消息中间件 缓存 NoSQL
缓存与数据库的一致性方案，Redis与Mysql一致性方案，大厂P8的终极方案（图解+秒懂+史上最全）
缓存与数据库的一致性方案，Redis与Mysql一致性方案，大厂P8的终极方案（图解+秒懂+史上最全）
技术自由圈/原疯狂创客圈
61 1
蓝易云
|
1月前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤，希望对您有所帮助。
蓝易云
194 42
游客kufrkwrbkmpsa
|
12天前
|
SQL 关系型数据库 MySQL
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案，最后总结了迁移体验，包括工具部署和操作特点，也指出功能有优化空间及暂不支持的部分，期待其不断优化。
游客kufrkwrbkmpsa
28 1

热门文章

最新文章

  • 1
    Canal是怎么伪装成 MySQL slave？
  • 2
    基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
  • 3
    ELK实现nginx、mysql、http的日志可视化实验
  • 4
    无缝集成 MySQL，解锁秒级 OLAP 分析性能极限，完成任务可领取三合一数据线！
  • 5
    如何解决 MySQL 数据库服务器 CPU 飙升的情况
  • 6
    docker拉取MySQL后数据库连接失败解决方案
  • 7
    seatunnel配置mysql2hive
  • 8
    MySQL和SQLSugar百万条数据查询分页优化
  • 9
    100%兼容MySQL！手把手教你基于PolarDB搭建RAG系统
  • 10
    基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
  • 1
    从 MySQL 到时序数据库 TDengine：Zendure 如何实现高效储能数据管理？
    14
  • 2
    MySQL索引有哪些类型？
    23
  • 3
    Redis和Mysql如何保证数据⼀致?
    18
  • 4
    MYSQL支持的存储引擎有哪些, 有什么区别
    13
  • 5
    Mysql的索引
    24
  • 6
    Canal是怎么伪装成 MySQL slave？
    9777
  • 7
    mysql 之order by工作流程
    11
  • 8
    如何在MySQL中创建定时任务？
    31
  • 9
    docker拉取MySQL后数据库连接失败解决方案
    105
  • 10
    【YashanDB知识库】MySQL迁移至崖山char类型数据自动补空格问题
    36

    • 相关课程

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

    • 相关电子书

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

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    阿里云oss简介和如何对接使用