ℹ️大家好，我是练小杰，今天周二，明天就是元宵节了呀！！😆
俗话说“众里寻他千百度。蓦然回首，那人却在，灯火阑珊处。”
本文主要对数据库备份与还原的知识点例题学习~~
前情回顾：👉【MySQL数据备份与还原详解】
数据库专栏：👉【数据库专栏】【Mysql练习题】
了解更多，点击主页： 【练小杰的CSDN】
文章目录
- 俗话说“众里寻他千百度。蓦然回首，那人却在，灯火阑珊处。”
- 前言
- 实验准备
- 创建数据库及定义表命令
- 执行完毕后，验证表是否存在
- 备份数据库
- 备份所有表数据
- 备份单个表数据
- 备份多个数据库
- 备份服务器所有数据库
- 备份数据库到指定目录
- 还原(恢复)数据库
- 把sql脚本直接导入数据库
- souce命令还原
- 直接复制备份文件
- 迁移数据库
- 相同版本的MySQL数据库的迁移
- 数据表的导出
- SELECT...INTO OUTFILE 导出文本文件
- mysqldump 导出文本文件
- mysql 命令导出文本文件
- 数据表导入
- LOAD DATA命令导入
- mysqlimport 命令导入
前言
先复习一下【前期回顾】 的博客，再来学习本篇内容吧！！！🙏
为了更好的认识并理解数据备份与还原，首先在数据库中创建并插入相关数据，再进行数据备份与还原的练习！！！
实验准备
创建数据库
booksDB，并使用该数据库。在该库中定义表books、authors和authorbook，并插入相关表数据。
创建数据库及定义表命令
CREATE DATABASE booksDB;
user booksDB;CREATE TABLE books
(bk_id INT NOT NULL PRIMARY KEY,bk_title VARCHAR(50) NOT NULL,copyright YEAR NOT NULL
);
INSERT INTO books
VALUES (11078, 'Learning MySQL', 2025),
(11033, 'Study Html', 2024),
(11035, 'How to use php', 2024),
(11072, 'Teach youself javascript', 2005),
(11028, 'Learing C++', 2005),
(11069, 'MySQL professional', 2009),
(11026, 'Guide to MySQL 5.5', 2008),
(11041, 'Inside VC++', 2011);CREATE TABLE authors
(auth_id INT NOT NULL PRIMARY KEY,auth_name VARCHAR(20),auth_gender CHAR(1)
);
INSERT INTO authors
VALUES (1001, 'WriterX' ,'f'),
(1002, 'WriterA' ,'f'),
(1003, 'WriterB' ,'m'),
(1004, 'WriterC' ,'f'),
(1011, 'WriterD' ,'f'),
(1012, 'WriterE' ,'m'),
(1013, 'WriterF' ,'m'),
(1014, 'WriterG' ,'f'),
(1015, 'WriterH' ,'f');CREATE TABLE authorbook
(auth_id INT NOT NULL,bk_id INT NOT NULL,PRIMARY KEY (auth_id, bk_id),FOREIGN KEY (auth_id) REFERENCES authors (auth_id),FOREIGN KEY (bk_id) REFERENCES books (bk_id)
);INSERT INTO authorbook
VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
执行完毕后，验证表是否存在
SHOW DATABASES;
USE booksDB;
SHOW TABLES;
备份数据库
备份所有表数据
- 使用
mysqldump命令备份数据库中的所有表
mysqldump -u root -p booksdb > C:/backup/booksdb_20250211.sql
执行完毕，将会在C盘的
backup目录下生成一个名为booksdb_20250211.sql的SQL脚本文件。
- 脚本文件的主要内容如下：
CREATE DATABASE IF NOT EXISTS `booksdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `booksdb`;CREATE TABLE `books` (`bk_id` int NOT NULL,`bk_title` varchar(50) NOT NULL,`copyright` year NOT NULL,PRIMARY KEY (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE TABLE `authors` (`auth_id` int NOT NULL,`auth_name` varchar(20) DEFAULT NULL,`auth_gender` char(1) DEFAULT NULL,PRIMARY KEY (`auth_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE TABLE `authorbook` (`auth_id` int NOT NULL,`bk_id` int NOT NULL,PRIMARY KEY (`auth_id`,`bk_id`),KEY `fk_authorbook_books` (`bk_id`),CONSTRAINT `fk_authorbook_authors` FOREIGN KEY (`auth_id`) REFERENCES `authors` (`auth_id`),CONSTRAINT `fk_authorbook_books` FOREIGN KEY (`bk_id`) REFERENCES `books` (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `books` (`bk_id`, `bk_title`, `copyright`) VALUES
(11078, 'Learning MySQL', 2025),
(11033, 'Study Html', 2024),
(11035, 'How to use php', 2024),
(11072, 'Teach youself javascript', 2005),
(11028, 'Learning C++', 2005),
(11069, 'MySQL professional', 2009),
(11026, 'Guide to MySQL 5.5', 2008),
(11041, 'Inside VC++', 2011);INSERT INTO `authors` (`auth_id`, `auth_name`, `auth_gender`) VALUES
(1001, 'WriterX', 'f'),
(1002, 'WriterA', 'f'),
(1003, 'WriterB', 'm'),
(1004, 'WriterC', 'f'),
(1011, 'WriterD', 'f'),
(1012, 'WriterE', 'm'),
(1013, 'WriterF', 'm'),
(1014, 'WriterG', 'f'),
(1015, 'WriterH', 'f');INSERT INTO `authorbook` (`auth_id`, `bk_id`) VALUES
(1001, 11033),
(1002, 11035),
(1003, 11072),
(1004, 11028),
(1011, 11078),
(1012, 11026),
(1012, 11041),
(1014, 11069);/*!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 */;
观察脚本文件可以发现，里面保存了数据库和数据表创建详细信息，包括主键，外键约束，存储引擎，字符集等相关信息！！！
备份单个表数据
- 备份
booksDB数据库中的books表
mysqldump -u root -p booksDB books > C:/backup/books_20250210.sql
生成一个名为 books_20250210.sql 的SQL脚本文件，该文件仅包含 booksDB 数据库中
books表的结构和数据
- 脚本
books_20250210.sql主要内容：
-- MySQL dump 10.13 Distrib 8.0.33, for Linux (x86_64)
--
-- Host: localhost Database: booksDB
-- ------------------------------------------------------
-- Server version 8.0.33/*!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 */;--
-- Database: booksDB
----
-- Table structure for table `books`
--DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `books` (`bk_id` int NOT NULL,`bk_title` varchar(50) NOT NULL,`copyright` year NOT NULL,PRIMARY KEY (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `books`
--LOCK TABLES `books` WRITE;
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES
(11078,'Learning MySQL',2025),
(11033,'Study Html',2024),
(11035,'How to use php',2024),
(11072,'Teach youself javascript',2005),
(11028,'Learning C++',2005),
(11069,'MySQL professional',2009),
(11026,'Guide to MySQL 5.5',2008),
(11041,'Inside VC++',2011);
/*!40000 ALTER TABLE `books` 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 2025-02-10 00:00:00
- 文件内容说明：
- 文件头部: 包含了一些关于导出时间和MySQL版本的注释信息。
- 表结构: books 表的结构被完整地导出，包括主键约束和字符集设置。
- 数据插入: books 表中的所有数据都被导出，使用
INSERT INTO语句。- 锁表: 在导出数据时，对 books 表进行了写锁，以确保数据的一致性。
- 文件尾部: 重置了一些MySQL会话变量，并记录了导出完成的时间。
备份多个数据库
- 使用
mysqldump备份booksDB和test数据库
mysqldump -u root -p --databases booksDB test> C:\backup\books_testDB_20250201.sql
脚本文件的内容与上面类似，这里就不过多重复了。
备份服务器所有数据库
- 使用
mysqldump备份服务器中的所有数据库
mysqldump -u root -p --all-databases > C:/backup/alldbinMySQL.sql
- 执行完毕，将生成一个名为
alldbinMySQL.sql的SQL脚本文件，该文件包含MySQL服务器上所有数据库的结构和数据。- 当你的数据库中包含视图、存储过程、函数、触发器等，文件会包含相应的
CREATE VIEW、CREATE PROCEDURE、CREATE FUNCTION、CREATE TRIGGER等语句。- 因此，生成的脚本文件内容很多，想要了解的可以自行尝试，这里就不过多赘述了！！
备份数据库到指定目录
- 使用
mysqlhotcopy备份test数据库到/usr/backup目录下
mysqlhotcopy -u root -p test /usr/backup
- 命令执行成功： 目标目录
/usr/backup中会包含 test 数据库的物理文件副本。- 执行错误时，会出现以下几种情况：
（1） 权限错误：mysqlhotcopy: ERROR: Cannot lock tables in the 'test' database
（2） 目标目录不存在:mysqlhotcopy: ERROR: Cannot create directory '/usr/backup'
（3） 数据库test不存在：mysqlhotcopy: ERROR: Database 'test' does not exist
- mysqlhotcopy命令的详细说明：
mysqlhotcopy:
这是一个用于MySQL数据库的热备份工具。它允许在不锁定表的情况下复制数据库文件，从而实现在线备份。-u root: 指定连接MySQL服务器的用户名为 root。
-p: 提示输入密码
test: 指要备份的数据库名称
/usr/backup: 备份文件的目标目录。
还原(恢复)数据库
把sql脚本直接导入数据库
- 使用mysql命令将C:\backup\booksdb_20250210.sql文件中的备份导入到数据库中
mysql -u root-p booksDB < C:/backup/booksdb_20250210.sql
执行命令后，将备份文件
booksdb_20250210.sql导入到booksDB数据库中。
MySQL客户端会读取C:/backup/booksdb_20250210.sql文件中的SQL语句，并按顺序执行这些语句。
souce命令还原
- 使用
root用户登录到服务器，然后使用souce导入本地的备份文件booksdb_20250210.sql
use booksdb;
source C:/backup/booksdb_20250210.sql
直接复制备份文件
- 从mysqlhotcopy拷贝的备份恢复数据库
cp –R /usr/backup/test /usr/local/mysql/data
迁移数据库
相同版本的MySQL数据库的迁移
- 将
www.lxj.com主机上的MySQL数据库全部迁移到www.lian.com主机上。在www.lxj.com主机上执行的命令如下：
mysqldump -h www.lian.com -uroot -ppassword dbname |
mysql -hwww.lian.com -uroot -ppassword
数据表的导出
SELECT…INTO OUTFILE 导出文本文件
- 例1：将
test数据库中的person表中的记录导出到文本文件
SELECT * FROM test.person INTO OUTFILE "C:/lxj.txt";
- 例二：把
test数据库中的person表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号‘,’间隔，所有字段值用双引号括起来，定义转义字符定义为单引号‘\’’
SELECT * FROM test.person INTO OUTFILE "C:/lxj1.txt"FIELDS TERMINATED BY ','ENCLOSED BY '\"'ESCAPED BY '\''LINES TERMINATED BY '\r\n';
- 例3：将test数据库中的person表中的记录导出到文本文件，使用
LINES选项，要求每行记录以字符串“>”开始，以“<end>”字符串结尾
SELECT * FROM test.person INTO OUTFILE "C:/lxj2.txt"LINES STARTING BY '> 'TERMINATED BY '<end>';SELECT * FROM test.person INTO OUTFILE "C:/person2.txt"LINES STARTING BY '> 'TERMINATED BY '<end>\r\n';
mysqldump 导出文本文件
mysqldump 工具将
test数据库中的person表导出为文本文件格式。
这种导出方式会将表结构和数据分别导出为.sql文件和纯数据文件（例如.txt或.csv文件）。
- 例1： 将
test数据库中的person表中的记录导出到文本文件
mysqldump -T C:/ test person -u root -p
- 例2：将test数据库中的
person表中的记录导出到文本文件，使用FIELDS选项，要求字段之间使用逗号‘,’间隔，所有字符类型字段值用双引号括起来，定义转义字符定义为问号‘?’，每行记录以回车换行符“\r\n”结尾
mysqldump -T C:\backup test person -u root -p --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n
mysql 命令导出文本文件
- 导出test数据库中person表中的记录到文本文件
mysql -u root -p --execute="SELECT * FROM person;" test > C:\lxj3.txt
是从 test 数据库中的 person 表中查询所有数据，并将结果输出到
C:\lxj3.txt文件中。
- 导出test数据库中person表中的记录到文本文件，使用
--vertical参数显示结果
mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\lxj4.txt
- 导出test数据库中person表中的记录到
html文件
mysql -u root -p --html --execute="SELECT * FROM person;" test > C:\lxj5.html
- 导出test数据库中person表中的记录到
xml文件
mysql -u root -p --xml --execute="SELECT * FROM person;" test > C:\lxj6.xml
数据表导入
LOAD DATA命令导入
- 将
C:\lxj.txt文件中的数据导入到test数据库中的person表
LOAD DATA INFILE 'C:/lxj.txt' INTO TABLE test.person;
- 把
C:\lxj1.txt文件中的数据导入到test数据库中的person表，使用FIELDS选项和LINES选项，要求字段之间使用逗号‘,’间隔，所有字段值用双引号括起来，定义转义字符定义为单引号‘\’’
LOAD DATA INFILE 'C:/lxj1.txt' INTO TABLE test.personFIELDS TERMINATED BY ','ENCLOSED BY '\"'ESCAPED BY '\''LINES TERMINATED BY '\r\n';
mysqlimport 命令导入
- 使用
mysqlimport命令将C:\backup目录下的lxj.txt文件内容导入到test数据库
要求：字段之间使用逗号‘,’间隔，字符类型字段值用双引号括起来，定义转义字符定义为问号‘?’，每行记录以回车换行符“\r\n”结尾
mysqlimport -u root -p test C:/backup/lxj.txt--fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n
今天的Mysql例题内容到这里就结束了，明天再见啦👋
主页：【练小杰的CSDN】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！
