MySQL 数据备份与恢复_mysql数据备份和恢复

本次使用的MySQL版本为8.0.20

目录

一、数据备份

1.使用 MySQLdump 命令备份

（1）使用MySQLdump备份单个数据库中的所有表

案例：完成数据插入后，输入备份命令如下：

（2）使用MySQLdump备份数据库中的某个表

案例：备份booksDB数据库中的books表，输入语句如下：

（3）使用MySQLdump备份多个数据库

案例：使用MySQLdump备份booksDB和test_db数据库，输入语句如下：

二、数据恢复

1.使用MySQL命令恢复

案例：使用MySQL命令将C:\backup\booksdb_20190301.sql文件中的备份导入到数据库中，输入语句如下：

案例：使用root用户登录到服务器，然后使用source导入本地的备份文件bookdb_20221016.sql，输入语句如下：

三、表的导出和导入

1.使用MySQLdump命令导出文本文件

案例：使用MySQLdump将test_db数据库person表中的记录导出到文本文件，执行的命令如下： 

案例：使用MySQLdump命令将test_db数据库person表中的记录导出到文本文件，使用FIELDS选项，要求字段之间使用逗号“,”间隔，所有字符类型字段值用双引号括起来，定义转义字符为问号“?”，每行记录以回车换行符“\r\n”结尾，执行的命令如下： 

2.使用MySQLimport命令导入文本文件

案例：使用MySQLimport命令将D盘目录下的person.txt文件内容导入到test_db数据库中，字段之间使用逗号“,”间隔，字符类型字段值用双引号括起来，将转义字符定义为问号“?”，每行记录以回车换行符“\r\n”结尾，执行的命令如下： 

---

一、数据备份

1.使用 MySQLdump 命令备份

        MySQLdump 是 MySQL 提供的一个非常有用的数据库备份工具。MySQLdump 命令执行时，可以将数据库备份成一个文本文件，该文件中实际包含了多个 CREATE 和 INSERT 语句，使用这些语句可以重新创建表和插入数据。

        MySQLdump 备份数据库语句的基本语法格式如下：

mysqldump -u user -h host -ppassword dbname[tbname, [tbname...]] > filename.sql

        user表示用户名称；host表示登录用户的主机名称；password为登录密码；dbname为需要备 份的数据库名称；tbname为dbname数据库中需要备份的数据表，可以指定多个需要备份的表；右 箭头符号“>”告诉MySQLdump将备份数据表的定义和数据写入备份文件；filename.sql为备份文件的名称。

（1）使用MySQLdump备份单个数据库中的所有表

        为了更好地理解MySQLdump工具是如何工作的，这里给出一个完整的数据库例子。首先登录MySQL，按下面数据库结构创建booksDB数据库和各个表，并插入数据记录。数据库和表定义如下：

[root@client01 home]# mysql -uroot -p123456
 
CREATE DATABASE booksDB;
use 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', 2010),
(11033, 'Study Html', 2011),
(11035, 'How to use php', 2003),
(11072, 'Teach yourself javascript', 2005),
(11028,'Learning C++', 2005),
(11069, 'MySQL professional', 2009),
(11026, 'Guide to MySQL 8.0', 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);
 
exit

• 案例：完成数据插入后，输入备份命令如下：

# 要保证 mysql_back 目录存在才可执行
[root@client01 home]# mysqldump -u root -p booksDB > /home/mysql_back/bookdb_20221016.sql
Enter password: 
 
[root@client01 home]# ls /home/mysql_back/
bookdb_20221016.sql

        输入密码之后，MySQL便对数据库进行了备份，在/home/mysql_back文件夹下面查看刚才备份过的文件，使用VS Code 打开文件可以看到，备份文件包含了一些信息，文件开头首先表明了备份文件使用的MySQLdump工具的版本号；然后是备份账户的名称和主机信息，以及备份的数据库的名称，最后是MySQL服务器的版本号，在这里为8.0.20。

        备份文件接下来的部分是一些SET语句，这些语句将一些系统变量值赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同，例如：

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

        该SET语句将当前系统变量character_set_client的值赋给用户定义变量@old_character_ set_client。其他变量与此类似。

        备份文件中以“--”字符开头的行为注释语句；以“/*!”开头、“*/”结尾的语句为可执行的MySQL注释，这些语句可以被MySQL执行，但在其他数据库管理系统中将被作为注释忽略，以提高数据库的可移植性。另外，备份文件开始的一些语句以数字开头，代表的是MySQL版本号，这些语句只有在指定的MySQL版本或者比该版本高的情况下才能执行。例如，40101，表明这些语句只有在MySQL版本号为4.01.01或者更高的条件下才可以被执行。

（2）使用MySQLdump备份数据库中的某个表

其语法格式为：

mysqldump　–u user –h host –p dbname　[tbname, [tbname...]] > filename.sql

        tbname表示数据库中的表名，多个表名之间用空格隔开。备份表和备份数据库中所有表的语句中不同的地方在于，要在数据库名称dbname之后指定需要备份的表名称。

• 案例：备份booksDB数据库中的books表，输入语句如下：

[root@client01 mysql_back]# mysqldump -u root -p booksDB books > /home/mysql_back/books_20221017.sql
Enter password: 
 
[root@client01 mysql_back]# ls
bookdb_20221016.sql  books_20221017.sql

（3）使用MySQLdump备份多个数据库

        如果要使用MySQLdump备份多个数据库，就需要使用--databases参数。备份多个数据库的语句格式如下：

mysqldump　–u user –h host –p --databases　[dbname, [dbname...]] > filename.sql

        使用--databases参数之后，必须指定至少一个数据库的名称，多个数据库名称之间用空格隔开。

• 案例：使用MySQLdump备份booksDB和test_db数据库，输入语句如下：

mysqldump -u root -p --databases booksDB test_db > /home/mysql_back/booksdb_test_db_20221017

另外，使用--all-databases参数可以备份系统中所有的数据库，语句如下：

mysqldump　-u root -p --all-databases > /home/mysql_back/alldbinMySQL.sql

 使用参数--all-databases时，不需要指定数据库名称。

二、数据恢复

1.使用MySQL命令恢复

        对于已经备份的包含CREATE、INSERT语句的文本文件，可以使用MySQL命令导入到数据库中。本此使用MySQL命令导入sql文件的方法。

        备份的sql文件中包含CREATE、INSERT语句（有时也会有DROP语句）。MySQL命令可以直接执行文件中的这些语句。其语法如下：

mysql –u user –p [dbname] < filename.sql

        user是执行backup.sql中语句的用户名；-p表示输入用户密码；dbname是数据库名。如果filename.sql文件为MySQLdump工具创建的包含创建数据库语句的文件，执行的时候不需要指定数据库名。

• 案例：使用MySQL命令将C:\backup\booksdb_20190301.sql文件中的备份导入到数据库中，输入语句如下：

mysql –u root –p booksDB < /home/mysql_back/bookdb_20221016.sql

        执行该语句前，必须先在MySQL服务器中创建booksDB数据库，如果不存在恢复过程将会出错。命令执行成功之后bookdb_20221016.sql文件中的语句就会在指定的数据库中恢复以前的表。

如果已经登录连接MySQL服务器，还可以使用source命令导入sql文件。source语句语法如下：

source filename

• 案例：使用root用户登录到服务器，然后使用source导入本地的备份文件bookdb_20221016.sql，输入语句如下：

--选择要恢复到的数据库
mysql> use booksDB;
Database changed
 
--使用source命令导入备份文件
mysql> source /home/mysql_back/bookdb_20221016.sql

        命令执行后，会列出备份文件bookdb_20221016.sql中每一条语句的执行结果。source命令执行成功后，bookdb_20221016.sql中的语句会全部导入到现有数据库中。

执行source命令前，必须使用use语句选择数据库。不然，恢复过程中会出现“ERROR 1046 (3D000): No database selected”的错误。

三、表的导出和导入

1.使用MySQLdump命令导出文本文件

        前面介绍了使用MySQLdump备份数据库，该工具不仅可以将数据导出为包含CREATE、INSERT的sql文件，也可以导出为纯文本文件。MySQLdump创建一个包含创建表的CREATE TABLE语句的tablename.sql文件和一个包含其数据的tablename.txt文件。MySQLdump导出文本文件的基本语法格式如下：

mysqldump -T path -u root -p dbname [tables] [OPTIONS]
 
--OPTIONS选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value

        只有指定了-T参数才可以导出纯文本文件；path表示导出数据的目录；tables为指定要导出的表名称，如果不指定，将导出数据库dbname中所有的表；[OPTIONS]为可选参数选项，这些选项需要结合-T选项使用。

使用OPTIONS常见的取值有：

• --fields-terminated-by=value：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符“\t”。
• --fields-enclosed-by=value：设置字段的包围字符。
• --fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，只能包括CHAR和VERCHAR等字符数据字段。
• --fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为反斜线“\”。
• --lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

• 案例：使用MySQLdump将test_db数据库person表中的记录导出到文本文件，执行的命令如下： 

mysqldump -T /home/mysql_back test_db person -u root -p

        语句执行成功，/home/mysql_back/目录下面将会有两个文件，分别为person.sql和person.txt。person.sql包含创建person表的CREATE语句；person.txt包含数据包中的数据，其内容如下：

• 案例：使用MySQLdump命令将test_db数据库person表中的记录导出到文本文件，使用FIELDS选项，要求字段之间使用逗号“,”间隔，所有字符类型字段值用双引号括起来，定义转义字符为问号“?”，每行记录以回车换行符“\r\n”结尾，执行的命令如下： 

mysqldump -T /home/mysql_back test_db person -u root -p --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n
Enter password:******

        上面语句要在一行中输入，语句执行成功，/home/backup/目录下面将会有两个文件，分别为person.sql和person.txt。person.sql包含创建person表的CREATE语句，其内容与前面例子中的相同，person.txt文件的内容与上一个例子不同，显示如下：

　　1,"Green",21,"Lawyer"
　　2,"Suse",22,"dancer"
　　3,"Mary",24,"Musician"
　　4,"Willam",20,"sports man"
　　5,"Laura",25,?N
　　6,"Evans",27,"secretary"
　　7,"Dale",22,"cook"
　　8,"Edison",28,"singer"
　　9,"Harry",21,"magician"
　　10,"Harriet",19,"pianist"

        可以看到，只有字符类型的值被双引号括了起来，而数值类型的值没有；第5行记录中的NULL值表示为“?N”，使用问号“?”替代了系统默认的反斜线转义字符“\”。

2.使用MySQLimport命令导入文本文件

        使用MySQLimport可以导入文本文件，并且不需要登录MySQL客户端。使用MySQLimport语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。MySQLimport命令的基本语法格式如下：

mysqlimport -u root-p dbname filename.txt [OPTIONS]
 
--OPTIONS选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
--ignore-lines=n

        dbname为导入的表所在的数据库名称。注意，MySQLimport命令不指定导入数据库的表名称，数据表的名称由导入文件名称确定，即文件名作为表名，导入数据之前该表必须存在。

[OPTIONS]为可选参数选项，其常见的取值有：

•  --fields-terminated-by= 'value'：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符“\t”。
•  --fields-enclosed-by= 'value'：设置字段的包围字符。
•  --fields-optionally-enclosed-by= 'value'：设置字段的包围字符，只能为单个字符，包括CHAR和VERCHAR等字符数据字段。
•  --fields-escaped-by= 'value'：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为反斜线“\”。
•  --lines-terminated-by= 'value'：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。
•  --ignore-lines=n：忽视数据文件的前n行。

• 案例：使用MySQLimport命令将D盘目录下的person.txt文件内容导入到test_db数据库中，字段之间使用逗号“,”间隔，字符类型字段值用双引号括起来，将转义字符定义为问号“?”，每行记录以回车换行符“\r\n”结尾，执行的命令如下： 

mysqlimport -u root -p test_db /home/mysql_back/person.txt --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n

上面的语句要在一行中输入，语句执行成功，将把person.txt中的数据导入到数据库。