• Mysql 数据备份详解

    MySQL 的数据备份有 2 种：

    一种是物理备份，通过把数据文件复制出来，达到备份的目的；

    另外一种是逻辑备份，通过把描述数据库结构和内容的信息保存起来，达到备份的目的。逻辑备份这种方式是免费的，广泛得到使用；而物理备份的方式需要收费，用得比较少。

    如何进行数据备份？

    首先，我们来学习下用于数据备份的工具 mysqldump。它总共有三种模式：

    1. 备份数据库中的表；

    2. 备份整个数据库；

    3. 备份整个数据库服务器。

    接下来，我就来介绍下这 3 种备份的具体方法。

    如何备份数据库中的表?

    mysqldump 备份数据库中的表的语法结构是：

    mysqldump -h 服务器 -u 用户 -p 密码 数据库名称 [表名称 … ] > 备份文件名称

    • 1
    • “-h”后面跟的服务器名称，如果省略，默认是本机“localhost”。
    • “-u”后面跟的是用户名。
    • “-p”后面跟的是密码，如果省略，执行的时候系统会提示录入密码。

    比如：

    H:\>mysqldump -u root -p demo goodsmaster membermaster > test.sql
Enter password: *****

    • 1
    • 2

    这个指令的意思，就是备份本机数据库服务器上 demo 数据库中的商品信息表和会员信息表的所有信息。
    备份文件是以文本格式保存的，我们可以用记事本打开，看一下备份的内容：

    -- MySQL dump 10.13 Distrib 8.0.23, for Win64 (x86_64)
--
-- Host: localhost Database: demo -- 表示从本地进行备份，数据库是demo
-- ------------------------------------------------------
-- Server version 8.0.23
 
/*!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 */;
/*!50503 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 `goodsmaster` -- 商品信息表的结构
--
 
DROP TABLE IF EXISTS `goodsmaster`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `goodsmaster` (
`itemnumber` int NOT NULL,
`barcode` text,
`goodsname` text,
`specification` text,
`unit` text,
`salesprice` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`itemnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `goodsmaster` -- 商品信息表的内容
--
 
LOCK TABLES `goodsmaster` WRITE;
/*!40000 ALTER TABLE `goodsmaster` DISABLE KEYS */;
INSERT INTO `goodsmaster` VALUES (1,'0001','书','16开','本',89.00),(2,'0002','笔','10支装','包',5.00),(3,'0003','橡皮',NULL,'个',3.00);
/*!40000 ALTER TABLE `goodsmaster` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `membermaster` -- 会员表的结构
--
 
DROP TABLE IF EXISTS `membermaster`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `membermaster` (
`id` int NOT NULL AUTO_INCREMENT,
`cardno` char(8) NOT NULL,
`membername` text,
`memberphone` text,
`memberpid` text,
`memberaddress` text,
`sex` text,
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `membermaster` -- 会员表的内容
--
 
LOCK TABLES `membermaster` WRITE;
/*!40000 ALTER TABLE `membermaster` DISABLE KEYS */;
INSERT INTO `membermaster` VALUES ('10000001','张三','13812345678','110123200001017890','北京','男','2000-01-01 00:00:00',1),('10000002','李四','13512345678','123123199001012356','上海','女','1990-01-01 00:00:00',2);
/*!40000 ALTER TABLE `membermaster` 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 2021-04-11 10:43:04

    • 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
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85

    如何备份数据库

    mysqldump 备份数据库的语法结构是：

    mysqldump -h 服务器 -u 用户 -p 密码 --databases 数据库名称 … > 备份文件名

    • 1

    举个小例子，假设我现在需要对本机的数据库服务器中的 2 个数据库 demo 和 demo1 进行备份，就可以用下面的指令：

    H:\>mysqldump -u root -p --databases demo demo1 > test1.sql
Enter password: *****

    • 1
    • 2

    现在，我们来查看一下备份文件的内容：

    -- MySQL dump 10.13 Distrib 8.0.23, for Win64 (x86_64)
--
-- Host: localhost Database: demo
-- ------------------------------------------------------
-- Server version 8.0.23
 
/*!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 */;
/*!50503 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 */;
 
--
-- Current Database: `demo` -- 备份数据库demo
--
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `demo` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
 
USE `demo`; -- 备份数据库中的表
 
--
-- Table structure for table `dailystatistics`
--
 
DROP TABLE IF EXISTS `dailystatistics`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `dailystatistics` (
`id` int NOT NULL AUTO_INCREMENT,
`itemnumber` int DEFAULT NULL,
`quantity` decimal(10,3) DEFAULT NULL,
`actualvalue` decimal(10,2) DEFAULT NULL,
`cost` decimal(10,2) DEFAULT NULL,
`profit` decimal(10,2) DEFAULT NULL,
`profitratio` decimal(10,4) DEFAULT NULL,
`salesdate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_dailystatistic_salesdate` (`salesdate`),
KEY `index_dailystatistic_itemnumber` (`itemnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `dailystatistics`
--
 
LOCK TABLES `dailystatistics` WRITE;
/*!40000 ALTER TABLE `dailystatistics` DISABLE KEYS */;
INSERT INTO `dailystatistics` VALUES (15,1,3.000,267.00,100.50,166.50,0.6236,'2020-12-01 00:00:00'),(16,2,2.000,10.00,7.00,3.00,0.3000,'2020-12-01 00:00:00');
/*!40000 ALTER TABLE `dailystatistics` ENABLE KEYS */;
UNLOCK TABLES;
 
-- 这里省略了其他表的备份语句
--
-- Current Database: `demo1` -- 备份数据库demo1
--
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `demo1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
 
USE `demo1`;
/*!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 2021-04-11 11:02:09

    • 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
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76

    可以看到，这个文件里面包含了创建数据库 demo 和 demo1 的 SQL 语句，以及创建数据库中所有表、插入所有表中原有数据的 SQL 语句。

    如何备份整个数据库服务器？

    mysqldump 备份整个数据库服务器的语法结构是：

    mysqldump -h 服务器 -u 用户 -p 密码 --all-databases > 备份文件名

    • 1

    举个小例子，假设我要把本机上整个 MySQL 服务器备份下来，可以用下面的代码：

    H:\>mysqldump -u root -p --all-databases > test2.sql
Enter password: *****

    • 1
    • 2

    这个指令表示，备份本机上运行的 MySQL 数据库服务器的全部内容，包含系统数据库和用户创建的数据库中的库结构信息、表结构信息和表里的数据。这种备份方式会把系统数据库也全部备份出来，而且消耗的资源也比较多，一般来说没有必要，我就不展开细说了。
    备份文件有了，如何用它进行数据恢复呢？下面我就来给你介绍下具体的方法。

    如何进行数据恢复

    mysqldump 的备份文件包含了创建数据库、数据表，以及插入数据表里原有数据的 SQL 语句，我们可以直接运行这些 SQL 语句，来进行数据恢复。
    数据恢复的方法主要有 2 种：

    • 使用“mysql”命令行客户端工具进行数据恢复；
    • 使用“SOURCE”语句进行数据恢复。
      使用“mysql”命令行客户端工具，进行数据恢复的命令如下：
    H:\>mysql -u root -p demo < test.sql
Enter password: *****

    • 1
    • 2

    我来简单介绍下这个数据恢复命令。

    • mysql 是一个命令行客户端工具，可以与 MySQL 服务器之间进行连接，执行 SQL 语句。
    • “-u”后面跟的是用户。
    • “-p”后面跟的是密码。
      在这个命令里面，我指定了数据库，因为备份文件 test.sql 里面只有数据表的备份信息，需要指定恢复到哪个数据库中。如果使用的备份文件备份的是数据库的信息（比如 test1.sql），或者是整个 MySQL 数据库服务器的信息（比如 test2.sql），则不需要指定数据库。

    第二种数据恢复的方法是，使用“SOURCE”语句恢复数据，语法结构如下：

    SOURCE 备份文件名

    • 1

    举个小例子，刚才我们对商品信息表和会员信息表进行了备份，现在想用备份的文件进行恢复，就可以用下面的语句：

    mysql> USE demo;
Database changed
mysql> SOURCE H:\\test.sql
Query OK, 0 rows affected (0.00 sec)

    • 1
    • 2
    • 3
    • 4

    注意，这里需要先用“USE”语句把当前的数据库变更为 demo，这样商品信息表和会员表才能恢复到正确的数据库里面。否则，可能会恢复错误。
    除此之外，你还可以通过这种方式，用整个数据库的备份文件把数据库恢复回来，甚至是用整个数据库服务器的备份文件，恢复整个 MySQL 服务器。
    到这里，我们就掌握了备份和恢复整个数据库服务器、数据库和数据库中的表的方法。不过，有的时候，我们只关心表里的数据本身，希望能够把表里的数据，按照一定的格式保存下来。这个时候，mysqldump 就不够用了。所以，接下来我再给你介绍下 MySQL 数据导出和导入的方法。

    如何导出和导入表里的数据？

    SELECT 语句导出数据

    使用“SELECT … INTO OUTFILE”语句导出数据表的语法结构是：

    SELECT 字段列表 INTO OUTFILE 文件名称
FIELDS TERMINATED BY 字符
LINES TERMINATED BY 字符
FROM 表名;

    • 1
    • 2
    • 3
    • 4

    我来解释下这段代码。

    • INTO OUTFILE 文件名称，表示查询的结果保存到文件名称指定的文件中；
    • FIELDS TERMINATED BY 字符，表示列之间的分隔符是“字符”；
    • LINES TERMINATED BY 字符，表示行之间的分隔符是“字符”。
      举个小例子，假设我们要把商品信息表导出到文件 H:\goodsmaster.txt 中，该如何实现呢？按照我刚刚介绍的语法结构来尝试一下：
    mysql> SELECT * INTO OUTFILE 'H:\goodsmaster.txt'
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\n'
-> FROM demo.goodsmaster;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

    • 1
    • 2
    • 3
    • 4
    • 5

    结果，系统提示错误。其实，这是因为服务器的“secure-file-priv”参数选项，不允许把文件写入到 H:\goodsmaster.txt 中。那怎么解决这个问题呢？
    这个时候，我们可以通过 MySQL 的配置文件 my.ini，来查看一下“secure-file-priv”参数的设定，并且按照这个参数设定的要求准备导入文件。
    打开 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini，找到“secure-file-priv”参数设定，如下所示：

    # Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

    • 1
    • 2

    这个意思是说，只能把数据导出到“C:/ProgramData/MySQL/MySQL Server 8.0/Uploads”这个文件夹中，所以，如果我们把数据导出到 H:\goodsmaster.txt 中，就违反了系统参数的设定，导致发生错误。
    现在，我们来修改一下数据导出的 SQL 语句，把导出文件的路径改到系统要求的文件目录，看看结果如何：

    mysql> SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/goodsmaster.txt'
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\n'
-> FROM demo.goodsmaster;
Query OK, 3 rows affected (0.00 sec)`

    • 1
    • 2
    • 3
    • 4
    • 5

    结果显示，执行成功了。下面我们来看一下结果文件的内容：

    1,0001,书,16开,本,89.00
2,0002,笔,10支装,包,5.00
3,0003,橡皮,\N,个,3.00

    • 1
    • 2
    • 3

    很显然，这很符合我们希望的导出格式：行与行之间用回车“\n”分隔，列与列之间用逗号“,”分隔。
    到这里，我们就知道怎么把数据表中的数据按照一定的格式导出到文件了。那在实际工作中，我们还经常需要把一定格式的数据从文件中导入到数据表中。
    “LOAD DATA”语句，就是 MySQL 提供的一种快速数据读入的方法，在实际工作中常用于大量数据的导入，效率极高。

    使用“LOAD DATA”语句导入数据

    “LOAD DATA”是与“SELECT … INTO OUTFILE”相对应的数据导入语句。语句结构是：

    LOAD DATA INFILE 文件名
INTO TABLE 表名
FIELDS TERMINATED BY 字符
LINES TERMINATED BY 字符;

    • 1
    • 2
    • 3
    • 4

    我举个小例子来演示一下“LOAD DATA”语句是如何工作的。
    还是以我们刚才导出的那个文件 goodsmaster.txt 为例，现在我们把这个文件内的数据导入到商品信息表（demo.goodsmaster）中去。
    为了演示方便，我会先把 demo.goodsmaster 中的数据先删除，然后使用“LOAD DATA”语句，把刚才的导出文件 goodsmaster.txt 的内容导入进来，再与删除之前的数据进行对比，来验证“LOAD DATA”语句的执行效果。
    首先，我们把商品信息表中的数据删除：

    mysql> DELETE FROM demo.goodsmaster
-> WHERE itemnumber>0;
Query OK, 3 rows affected (0.03 sec) 

    • 1
    • 2
    • 3

    然后，我们尝试把文件 goodsmaster.txt 中的数据导入进来：

    mysql> LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/goodsmaster.txt'
-> INTO TABLE demo.goodsmaster
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\n';
Query OK, 3 rows affected (0.02 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    结果显示，导入成功了。我们再查看一下数据表中的内容：

    mysql> SELECT * FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
| 1 | 0001 | 书 | 16开 | 本 | 89.00 |
| 2 | 0002 | 笔 | 10支装 | 包 | 5.00 |
| 3 | 0003 | 橡皮 | NULL | 个 | 3.00 |
+------------+---------+-----------+---------------+------+------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    结果显示，与我们删除之前的数据完全一致。这说明，“LOAD DATA”语句成功导入了数据文件 goodsmaster.txt 中的数据。

  • 相关阅读:
    Linux之 4 种休眠模式
    Java手写希尔排序和算法案例拓展
    21天学习挑战赛--字符串切割
    通信接口五种主要的类型是什么？RS-232、485、CAN、USB
    【数据挖掘】聚类分析
    通过制作llama_cpp的docker镜像在内网离线部署运行大模型
    ABB MPRC086444-005数字输入模块
    1.关于433MHz按键单片机解码
    ZIP压缩文件的打开密码和自动加密有什么不同？
    若依：用sqlite3随便掰饬掰饬
  • 原文地址：https://blog.csdn.net/fd2025/article/details/125516421