MySQL——使用mysqldump备份与恢复数据

刺骨的言语ヽ痛彻心扉 2023-10-16 17:46 15阅读 0赞

**目录**

1.mysqldump简介

2.mysqldump备份数据

2.1 备份所有数据库

2.2 备份一个/多个数据库

2.3 备份指定库中的指定表

3.mysqldump恢复数据

3.1 恢复数据库

3.2 恢复数据表

--------------------

> **前言**：`mysqldump``和``binlog``都可以作为MySQL数据库备份的方式：`
> 
>  *  `mysqldump`用于将整个或部分数据库导出为可执行的SQL文件，也可以用于导入或还原数据库，它可以创建一个数据库的逻辑备份，包括表结构和数据
>  *  `binlog`（二进制日志）是一种事务日志，记录了对数据库进行的每个更改操作，如插入、更新、删除等。它用于增量备份和数据恢复，可以重放这些更改以还原到之前的状态
> 
> `mysqldump``和``binlog``的主要区别如下：`
> 
> 1.  数据格式：`mysqldump`生成的备份文件是以文本形式保存的SQL语句，可以读取和修改。`binlog`则是以二进制格式保存的事务日志，不能直接查看或修改，需要专门的工具（如mysqlbinlog）进行解析和分析。
> 2.  备份范围：`mysqldump`可以备份整个数据库或特定的表，以及备份时可以选择备份的数据内容（例如只备份表结构、只备份数据等）。而`binlog`会记录所有的更改操作，包括对表结构和数据的更改，因此可以用来恢复到任何一个时间点之前的状态。
> 3.  使用场景：`mysqldump`适用于定期完整备份数据库或移植数据库，以便将数据导入到不同的MySQL服务器。`binlog`则适用于增量备份和恢复，可以用于故障恢复、数据同步、主从复制等场景。
> 
> **通过binlog恢复数据传送门：[通过binlog恢复数据![icon-default.png?t=N7T8][icon-default.png_t_N7T8]https://blog.csdn.net/DreamEhome/article/details/130010601][binlog_icon-default.png_t_N7T8_icon-default.png_t_N7T8_https_blog.csdn.net_DreamEhome_article_details_130010601]**

## 1.mysqldump简介 ##

mysqldump命令可以将数据库中指定或所有的库、表导出为SQL脚本。表的结构和表中的数据将存储在生成的SQL脚本中。

**mysqldump备份恢复原理**：通过先查出需要备份的库及表的结构，在SQL脚本中生成CREATE语句。然后将表中的所有记录转换成[INSERT语句][INSERT]并写入SQL脚本中。这些CREATE语句和INSERT语句都是还原时使用的：还原数据时可使用其中的CREATE语句来创建表，使用INSERT语句还原数据。

--------------------

**环境准备**：如下图所示，共准备了database\_test1和database\_test2两个数据库，每个数据库中都有两个表table\_test1和table\_test2(未演示database\_test2，同database\_test1)，每个表中各有一条数据。

![bcc9c9c7ec1842e2b82cb0871911c774.png][]

--------------------

## 2.mysqldump备份数据 ##

**备份的形式可以分为以下4种**：

1.  备份所有数据库（包含库中所有表及数据）
2.  备份一个/多个数据库（包含库中所有表及数据）
3.  备份指定库中的指定表（指定表及其数据）

下面将分别介绍如何实现这3种形式的数据备份。

### 2.1 备份所有数据库 ###

语法：mysqldump -u\[用户名\] -p\[密码\] --all-databases > /备份路径/备份文件名.sql

#备份全部数据库
    mysqldump -uroot -p123456 --all-databases > backup_all_databases.sql

> mysqldump
> 
> \-uroot -p123456 登录MySQL的用户名/密码
> 
> \--all-databases >
> 
> backup\_all\_databases.sql 备份文件的名称，可加保存路径

![0bce7500c9d2464f8896d46c988ae037.png][]

### 2.2 备份一个/多个数据库 ###

语法：mysqldump -u\[用户名\] -p\[密码\] --databases DB1 \[DB2 DB3...\] > /备份路径/备份文件名.sql

#备份一个数据库
    mysqldump -uroot -p123456 --databases database_test1 > backup_database_test1.sql
    
    #备份多个数据库
    mysqldump -uroot -p123456 --databases database_test1 database_test2 > backup_database_test1_test2.sql

> mysqldump
> 
> \-uroot -p123456 登录MySQL的用户名/密码
> 
> \--databases
> 
> database\_test1 database\_test2 要备份的数据库，多个以空格间隔
> 
> >
> 
> backup\_\*.sql 备份文件的名称，可加保存路径

![eb8f0aca8d2a40ba92ea132c7b461d68.png][]

### 2.3 备份指定库中的指定表 ###

语法：mysqldump -u\[用户名\] -p\[密码\] \[database\] \[table1\] \[table2\] > /备份路径/备份文件名.sql

#备份库中的部分表
    mysqldump -uroot -p123456 database_test1 table_test1 table_test2 > backup_tables.sql

> mysqldump
> 
> \-uroot -p123456 登录MySQL的用户名/密码
> 
> database\_test1 要备份表所在的库名
> 
> table\_test1 > 要备份的表名，可以有多个，以空格分割
> 
> backup\_tables.sql 备份文件的名称，可加保存路径

![b026d9bbc1d64e3f898c91f2dde8c30a.png][]

--------------------

**查看备份文件中的内容**：

#查看其中一个备份文件
    cat backup_tables.sql | grep -v "^--" |grep -v "^/" |grep -v "^$"

> cat
> 
> backup\_database\_test1.sql 要查看的文件
> 
> | grep -v "^--" |grep -v "^/" |grep -v "^$" 过滤不显示的信息(grep为搜索，-v表示忽略)

![b4e4d6e10ca1454ebc76a725582f25fd.png][]

--------------------

## 3.mysqldump恢复数据 ##

--------------------

**环境准备**：登录mysql数据库后删除database\_test1和 database\_test2

#登录mysql后删除database
    drop database database_test1;
    drop database database_test2;

![1e0c2c9d5e8c4476b7e51647954515d0.png][]

--------------------

### 3.1 恢复数据库 ###

> 备份所有数据库和备份指定库的恢复逻辑相同

语法：mysql -u\[用户名\] -p\[密码\] < /备份文件路径/备份文件名.sql

#还原数据库
    mysql -uroot -p123456 < backup_database_test1_test2.sql

![74719a5c240c40bab5254e68deba9a0c.png][]

--------------------

**环境准备**：登录mysql数据库后删除database\_test1库中的table\_test1和table\_test2(只删除一个或删除任意数据都可以)

#进入database_test1库
    use database_test1;
    #删除表
    drop table table_test1;
    drop table table_test2;

![ffb905d302324ef894afc59aa583b743.png][]

--------------------

### 3.2 恢复数据表 ###

语法：mysqldump -u\[用户名\] -p\[密码\] \[database\] < /备份文件路径/备份文件名.sql

恢复表的前提是表所在的库必须存在，且可任意指定库进行恢复操作

mysql -u root -p123456 database_test1 < backup_tables.sql

> mysql
> 
> \-uroot -p123456 数据库用户名/密码
> 
> database\_test1 要恢复的数据库
> 
> <
> 
> backup\_tables.sql 指定的数据库备份文件

![df960976b3574adea5ed9837efbe8702.png][]

[icon-default.png_t_N7T8]: https://csdnimg.cn/release/blog_editor_html/release2.3.6/ckeditor/plugins/CsdnLink/icons/icon-default.png?t=N7T8
[binlog_icon-default.png_t_N7T8_icon-default.png_t_N7T8_https_blog.csdn.net_DreamEhome_article_details_130010601]: https://blog.csdn.net/DreamEhome/article/details/130010601
[INSERT]: https://so.csdn.net/so/search?q=INSERT%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020
[bcc9c9c7ec1842e2b82cb0871911c774.png]: https://img-blog.csdnimg.cn/bcc9c9c7ec1842e2b82cb0871911c774.png
[0bce7500c9d2464f8896d46c988ae037.png]: https://img-blog.csdnimg.cn/0bce7500c9d2464f8896d46c988ae037.png
[eb8f0aca8d2a40ba92ea132c7b461d68.png]: https://img-blog.csdnimg.cn/eb8f0aca8d2a40ba92ea132c7b461d68.png
[b026d9bbc1d64e3f898c91f2dde8c30a.png]: https://img-blog.csdnimg.cn/b026d9bbc1d64e3f898c91f2dde8c30a.png
[b4e4d6e10ca1454ebc76a725582f25fd.png]: https://img-blog.csdnimg.cn/b4e4d6e10ca1454ebc76a725582f25fd.png
[1e0c2c9d5e8c4476b7e51647954515d0.png]: https://img-blog.csdnimg.cn/1e0c2c9d5e8c4476b7e51647954515d0.png
[74719a5c240c40bab5254e68deba9a0c.png]: https://img-blog.csdnimg.cn/74719a5c240c40bab5254e68deba9a0c.png
[ffb905d302324ef894afc59aa583b743.png]: https://img-blog.csdnimg.cn/ffb905d302324ef894afc59aa583b743.png
[df960976b3574adea5ed9837efbe8702.png]: https://img-blog.csdnimg.cn/df960976b3574adea5ed9837efbe8702.png