本次使用的MySQL版本为8.0.20
目录
案例：备份booksDB数据库中的books表，输入语句如下：
案例：使用MySQLdump备份booksDB和test_db数据库，输入语句如下：
案例：使用MySQL命令将C:\backup\booksdb_20190301.sql文件中的备份导入到数据库中，输入语句如下：
案例：使用root用户登录到服务器，然后使用source导入本地的备份文件bookdb_20221016.sql，输入语句如下：
案例：使用MySQLdump将test_db数据库person表中的记录导出到文本文件，执行的命令如下：
一、数据备份
1.使用 MySQLdump 命令备份
MySQLdump 是 MySQL 提供的一个非常有用的数据库备份工具。MySQLdump 命令执行时，可以将数据库备份成一个文本文件，该文件中实际包含了多个 CREATE 和 INSERT 语句，使用这些语句可以重新创建表和插入数据。
MySQLdump 备份数据库语句的基本语法格式如下：
mysqldump -u user -h host -ppassword dbname[tbname, [tbname...]] > filename.sqluser表示用户名称；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.sqltbname表示数据库中的表名，多个表名之间用空格隔开。备份表和备份数据库中所有表的语句中不同的地方在于，要在数据库名称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.sqluser是执行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中的数据导入到数据库。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/74592.html
