首页 > 编程笔记 > MySQL笔记阅读：53

mysqldump备份数据库的用法（非常详细，附带实例）

在一家企业中，数据库属于重中之重，为什么这么说呢？企业中的数据库就好比行军打仗中的粮仓和武器库，没有了粮仓和武器库，这仗肯定是打不下去的，所以像数据库这种“军事重地”可得好好保护起来。

有一段时间频频曝出企业内部人员删库跑路的新闻，这种操作使得企业损失惨重，甚至有些企业因为数据被删而倒闭。因此，对数据库通过一些技术手段保护起来变得越来越重要，这样就算出现了被删库的情况，也能在最短的时间内及时恢复数据。

能够损坏数据库的可不仅仅是人为因素，意外断电、硬盘突然损坏、不小心的误操作、黑客入侵等，都可能会造成数据的丢失。所以为了保护数据库及数据，必须未雨绸缪，及早对数据库采取一些技术保护手段。

对数据库进行保护的技术手段有很多，备份就是其中的一种，也是目前最行之有效的方案之一。我们为了数据安全会提前对数据库进行备份，这样即便某一天真的出现了数据丢失的情况，我们也能够及时将数据完好无损地恢复。

几种主流的数据库备份工具如下：

mysqldump：MySQL/MariaDB 自带的逻辑备份工具，支持所有的存储引擎，可进行温备份、完全备份、部分备份等，特别是对 InnoDB 存储引擎能够进行热备份；
cp：Linux操作系统的拷贝命令，也可以充当物理备份工具，备份过程是直接对数据库的数据文件进行复制，从而达到备份的目的；
xtrabackup：物理热备份工具，能够实现增量备份。只能备份 InnoDB 和 XtraDB 两种存储引擎的表数据，对 MyISAM 存储引擎不支持；
ibbackup：商业工具，备份速度快，支持热备份，但价格非常昂贵；

这里主要介绍 mysqldump，它是 MySQL/MariaDB 自带的逻辑备份工具，不需要额外安装其他辅助工具。

mysqldump 的备份原理是通过协议连接到 MySQL/MariaDB 数据库，再将需要备份的数据查询出来，接着将查询出来的数据转换成对应的 insert 语句，这些 insert 语句会集中存储在一个 SQL 文件中，这样就完成了数据的备份。当我们需要还原这些数据时，只要将这个 SQL 文件再导入数据库中即可，这样就完成了数据的还原。

mysqldump 备份时的语法格式如下：
单个库：

mysqldump [选项] 数据库名 [表名] >文件名.sql

多个库：

mysqldump--databases数据库名数据库名数据库名>文件名.sql

所有库：

mysqldump [选项] --all-databases > 文件名.sql

各字段的含义如下：

[选项]：辅助选项；
数据库名：要备份的数据库名称；
[表名]：可选项，表示要备份数据库中的哪些表，可以指定多个数据表。若不写该参数，则表示备份整个数据库；
右箭头 >：表示将要备份的数据库和表写入指定的备份文件中；
文件名.sql：备份文件的名称，文件的位置可以用相对路径也可以用绝对路径。文件的后缀名要用“.sql”，这样可以起到见名知意的作用。

常用的选项如下：

--host/-h：指定要连接的数据库 IP 地址。
--port/-p：指定要连接的数据库端口号，默认使用 3306。
--user/-u：指定登录数据库时要使用的用户名。
--password/-p：指定用户的密码。
--databases：指定要备份的数据库，多个数据库之间要用空格进行分隔。
--all-databases：备份所有数据库。
--lock-tables：备份前锁定所有数据表。
--no-create-db：禁止生成创建数据库语句。
--force：当出现报错时仍然继续备份操作。
--compatible：导出的数据将和其他数据库或旧版本的 MySQL 相兼容。

【实例】备份单个数据库。

[root@clinux ~]# mysql
----省略开头的介绍内容-----
MariaDB [(none)]>
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.108 sec)

MariaDB [(none)]>
use test01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test01]>
show tables;
+-----------------+
| Tables_in_test01|
+-----------------+
| stu_score       |
+-----------------+
1 row in set (0.001 sec)

MariaDB [test01]>
quit;
Bye

##知道了有哪些数据库和表，接下来就开始备份
##一定要注意，-p 选项后面不要跟密码，留空即可，否则会提示不要输入明文密码
##这里 -u 表示生成的 SQL 文件在当前目录下，使用的是相对路径，也可以使用绝对路径
[root@clinux ~]# mysqldump -uroot -p test01 > ./test01.sql    ##备份 test01 数据库
Enter password:    ##在这里输入数据库密码
[root@clinux ~]# ll
-rw-r--r-- 1 root root 2134 2月 26 22:32 test01.sql
[root@clinux ~]# cat test01.sql    ##查看数据库备份文件
-- MariaDB dump 10.19 Distrib 10.6.5-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: test01
-- Server version: 10.6.5-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `stu_score`
--

DROP TABLE IF EXISTS `stu_score`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stu_score` (
  `stu_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `english` char(3) NOT NULL,
  `mathematics` char(3) DEFAULT NULL,
  `geography` char(3) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stu_score`
--

LOCK TABLES `stu_score` WRITE;
/*!40000 ALTER TABLE `stu_score` DISABLE KEYS */;
INSERT INTO `stu_score` VALUES (2,'小 刘','60','58','74'),(1,'小 孙','60','44','96'),(3,'小 崔','60','99','48'),(4,'小 狗','60','39','69'),(5,'小 猫','60',NULL,NULL);
/*!40000 ALTER TABLE `stu_score` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-02-26 22:32:25

备份文件的开头记录了数据库的名称、版本和主机地址，在整个文件中以“--”开头的都表示注释说明，以“/*！”和“*/”开头的内容在其他数据库中也会被视为注释，从而忽略，这样可以提高数据库的可移植性。

【实例】备份多个数据库。

##备份多个数据库
[root@clinux ~]# mysqldump -uroot -p --databases test01 mysql > ./many.sql
[root@clinux ~]# ll
-rw-r--r-- 1 root root 1788108 2月 26 22:55 many.sql

【实例】备份所有数据库。

##备份所有的数据库
[root@clinux ~]# mysqldump -uroot -p --all-databases > ./all.sql
Enter password:
[root@clinux ~]# ll
-rw-r--r-- 1 root root 1788102 2月 26 22:57 all.sql
[root@clinux ~]#

仔细观察备份文件就会发现，备份多个数据库和备份所有数据库这两种方式所生成的备份文件中都存在创建数据库的 SQL 语句。而备份单个数据库所生成的备份文件中没有与创建数据库相关的 SQL 语句，这就表示通过备份单个数据库生成的备份文件若想要恢复数据，必须恢复到一个已存在的数据库中，可以先通过 CREATE DATABASE 语句创建一个空数据库，再将备份文件恢复到这个空数据库中。

在 MySQL/MariaDB 数据库中，可以通过 mysql 命令恢复备份的数据。mysql 命令可以依次完整地执行备份文件中的所有 SQL 语句，这样就能够将备份文件中所备份的内容（数据库、表、数据等）完全恢复到指定的数据库中。

用来恢复数据的 mysql 命令格式为：

mysql -u 用户名 -p [数据库名] < 文件名.sql

各字段的含义如下：

-u：指定用户名。
-p：指定用户密码。
[数据库名]：可选配置项，表示要将数据恢复到哪个数据库中。若备份文件中包含创建数据库的 SQL 语句（备份多个、所有数据库），则可以不写。
左箭头<：表示将备份文件恢复到数据库中。
文件名.sql：备份文件的名称。

【实例】将备份文件恢复到数据库中（在恢复之前需要先将目前已备份的数据库删除）。

[root@clinux ~]# mysql
----省略开头的介绍内容-----
##删除原先备份的 test01 数据库
MariaDB [(none)]>
drop database test01;
Query OK, 1 row affected (0.094 sec)

##成功删除 test01 数据库
MariaDB [(none)]>
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

##创建一个空数据库用于恢复数据，新创建的数据库可以任意命名
MariaDB [(none)]>
create database test01;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]>
quit
Bye
[root@clinux ~]# ll
-rw-r--r-- 1 root root 2134 2月 26 22:32 test01.sql
##将 test01.sql 备份文件恢复到 test01 数据库中
[root@clinux ~]# mysql -uroot -p test01 < test01.sql
Enter password:
[root@clinux ~]# mysql
----省略开头的介绍内容-----
##查看数据库，已经恢复成功了
MariaDB [(none)]>
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]>
use test01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test01]>
show tables;
+-----------------+
| Tables_in_test01|
+-----------------+
| stu_score       |
+-----------------+
1 row in set (0.000 sec)

##再看看表中的数据，数据都在，完好无损
MariaDB [test01]>
select * from stu_score;
+--------+--------+---------+-------------+------------+
| stu_id | name   | english | mathematics | geography  |
+--------+--------+---------+-------------+------------+
| 2      | 小刘   | 60      | 58          | 74         |
| 1      | 小孙   | 60      | 44          | 96         |
| 3      | 小崔   | 60      | 99          | 48         |
| 4      | 小狗   | 60      | 39          | 69         |
| 5      | 小猫   | 60      | NULL        | NULL       |
+--------+--------+---------+-------------+------------+
5 rows in set (0.000 sec)
MariaDB [test01]>

需要注意的是，如果要恢复带有创建数据库 SQL 语句的备份文件，例如多个数据库备份和所有数据库备份，就要使用以下命令格式：

mysql -uroot -p <all.sql

可以看到在这个语法格式中，已经不需要指定数据库名称了，这是因为在数据恢复的过程中会根据备份文件中的 CREATE DATABASE 语句创建数据库，所以指定数据库这一步可以省略。

mysqldump备份数据库的用法（非常详细，附带实例）

相关文章