MYSQL学习与数据库综合实验（十）——语句备份与恢复实验（基于Ubuntu18.04中Docker安装的MySQL8实验）
10.SQL语句备份与恢复实验
数据库的主要作用就是对数据进行保存和维护，所以备份数据是数据库管理中最常用的操作。为了防止数据库意外崩溃或硬件损伤而导致的数据丢失，数据库系统提供了备份和恢复策略。保证数据安全的最重要的一个措施就是定期的对数据库进行备份。这样即使发生了意外，也会把损失降到最低。
数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态
10.1MySQL 导出数据
10.1.1语句基本格式如下:
SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件' [OPTIONS]该语句用 SELECT 来查询所需要的数据，用 INTO OUTFILE 来导出数据。其中，目标文件用来指定将查询的记录导出到哪个文件。这里需要注意的是，目标文件不能是一个已经存在的文件。
[OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，其常用的取值有：
- FIELDS TERMINATED BY ‘字符串’：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。
- FIELDS [OPTIONALLY] ENCLOSED BY ‘字符’：设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
- FIELDS ESCAPED BY ‘字符’：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
- LINES STARTING BY ‘字符串’：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
- LINES TERMINATED BY ‘字符串’：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。
注意：FIELDS 和 LINES 两个子句都是自选的，但是如果两个都被指定了，FIELDS 必须位于 LINES的前面。
10.1.2开放MySQL的导入导出文件权限
在进行对数据库数据备份时如果不开放MySQL的导入导出文件权限，那么导入导出时将遇到提示
Mysql 导入文件提示 --secure-file-priv option 问题首先使用命令进行查看
show variables like "%secure%"
（1）NULL，表示禁止。
（2）如果value值有文件夹目录，则表示只允许该目录下文件（PS：测试子目录也不行）。
（3）如果为空，则表示不限制目录。
10.1.3解决方案
（1）方案一：
把导入文件放入secure-file-priv目前的value值对应路径即可。
（2）方案二：
把secure-file-priv的value值修改为准备导入文件的放置路径。
（3）方案三：修改配置
去掉导入的目录限制。可修改mysql配置文件（Windows下为my.ini, Linux下的my.cnf），在[mysqld]下面，查看是否有：
secure_file_priv =
如上这样一行内容，如果没有，则手动添加。如果存在如下行：
secure_file_priv = "/home"这样一行内容，表示限制为/home文件夹。而如下行：
secure_file_priv =这样一行内容，表示不限制目录，等号一定要有，否则mysql无法启动。
修改完配置文件后，重启mysql生效。
重启后：
关闭：service mysqld stop
启动：service mysqld start
比如在Linux中修改配置文件（安装目录下 etc/mysql/my.cnf）使用 ：
vim etc/mysql/my.cnf
这里secure_file_priv = “” 表示将默认的目录设置为Linux中MySQL安装目录下的data文件夹中。在导出数据时默认导出到data目录下该数据库对应文件夹中。事实上，MySQL安装目录下的data存放的就是用户新建数据库以及新建表这些数据的地方。比如我是一个用户在MySQL客户端连接数据库后新建了一个dbms数据库，那么在 /mysql/data下会有个dbms文件夹，也就是 /mysql/data/dbms，然后在数据库dbms中新建一个employee表，那么在这个dbms文件夹中会有相应的临时文件。
10.2MySQL之mysqldump的使用
10.2.1mysqldump 简介
mysqldump是MySQL自带的逻辑备份工具。它的备份原理是通过协议连接到MySQL数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。
10.2.2备份命令
命令格式
mysqldump [选项] 数据库名 [表名] > 脚本名或
mysqldump [选项] --数据库名 [选项 表名] > 脚本名或
mysqldump [选项] --all-databases [选项] > 脚本名mysqldump只导出整个数据库结构（不包含数据）
mysqldump -h localhost –u root -p -d database > xxx.sql
mysqldump只导出单个数据表结构（不包含数据）
mysqldump -h localhost –u root -p -d database table > xxx.sql
mysqldump只导出整个数据库的数据（不包含结构）
mysqldump -h localhost –u root -p -t database > xxx.sql
mysqldump只导出单个数据表的数据（不包含结构）
mysqldump -h localhost –u root -p -t database table > xxx.sql选项说明
参数名 | 缩写 | 含义 |
–host | -h | 服务器IP地址 |
–port | -P | 服务器端口号 |
–user | -u | MySQL 用户名 |
–pasword | -p | MySQL 密码 |
–databases | 指定要备份的数据库 | |
–all-databases | 备份mysql服务器上的所有数据库 | |
–compact | 压缩模式，产生更少的输出 | |
–comments | 添加注释信息 | |
–complete-insert | 输出完成的插入语句 | |
–lock-tables | 备份前，锁定所有数据库表 | |
–no-create-db/–no-create-info | 禁止生成创建数据库语句 | |
–force | 当出现错误时仍然继续备份操作 | |
–default-character-set | 指定默认字符集 | |
–add-locks | 备份数据库表时锁定数据库表 |
10.2.3 实例（如果备份不成功是因为ip原因请将localhost改为服务器机具体的ip即可。本次实例使用Ubuntu 18.4中Docker安装的MySQL8进行实验）
备份所有数据库：
mysqldump -h localhost -P 3306 -uroot -p --all-databases > /backup/back/all.db备份指定数据库：
mysqldump -h localhost -P 3306 -uroot -p dbms > /mysql/data/dbms/back/dbms.db备份指定数据库指定表(多个表以空格间隔,这里备份的是数据库dbms下的employee ，salary和 department这三个表)
mysqldump -h localhost -P 3306 -uroot -p dbms employee salary department > /mysql/data/dbms/back/emp-salary-dep.db备份指定数据库排除某些表（备份除了employee表外的dbms数据库中的表。）
mysqldump -h localhost -P 3306 -uroot -p dbms --ignore-table=dbms.employee > /mysql/data/dbms/back/salary-dep.db
10.2.4还原命令
10.2.4.1 系统行命令
mysqladmin -h localhost -P 3306 -uroot -p create db_name
mysql -uroot -p db_name < /mysql/data/dbms/back/db_name.db注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。
10.2.4.2soure 方法
mysql > use db_name
mysql > source /mysql/data/dbms/back/db_name.db10.3导入数据
10.3.1mysql 命令导入
使用 mysql 命令导入语法格式为：
mysql -h localhost -P 3306 -u 用户名 -p 密码 < 要导入的数据库数据实例：
mysql -h localhost -P 3306 -u root -p dbms < /mysql/data/dbms/back/salary.sql;以上命令将备份的整个数据库 salary.sql 导入。
10.3.2 source 命令导入
source 命令导入数据库需要先登录到数库终端：
mysql> create database dbms; # 创建数据库
mysql> use dbms; # 使用已创建的数据库
mysql> set names utf8; # 设置编码
mysql> source /home/mysql/data/dbms/back/salary.sql # 导入备份数据库10.3.3使用 LOAD DATA 导入数据
基本语法：
load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
fields
[terminated by'\t']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines terminated by'\n']
[ignore number lines]
[(col_name)]说明：
load data infile语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前，mysqld进程（服务）必须已经在运行。由于安全原因，当读取位于服务器上的文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用load data infile，在服务器主机上必须有file的权限。
1、如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把数据插入。可以使用如下的命令：
load data low_priority infile “/home/mark/data sql” into table Orders;2、如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上。
3、replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace，新行将代替有相同的唯一键值的现有行。如果你指定ignore，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。例如：
load data low_priority infile “/home/mark/data sql” replace into table Orders;4、分隔符
（1） fields关键字指定了文件字段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：
terminated by分隔符：意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符
terminated by描述字段的分隔符，默认情况下是tab字符（\t）
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）
例如：load data infile “/home/mark/Orders txt” replace into table Orders fields terminated by’,’ enclosed by ‘"’;（2）lines 关键字指定了每条记录的分隔符默认为’\n’即为换行符
如果两个字段都指定了，那fields必须在lines之前。如果不指定fields关键字，缺省值与这样写相同： fields terminated by’\t’ enclosed by ’ ‘’ ‘ escaped by’\’
如果你不指定一个lines子句，缺省值与这样写的相同： lines terminated by’\n’
例如：load data infile “/jiaoben/load.txt” replace into table test fields terminated by ‘,’ lines terminated by ‘\n’;5、 load data infile 可以按指定的列把文件导入到数据库中。 当我们要把数据的一部分内容导入的时候，，需要加入一些栏目（列/字段/field）到MySQL数据库中，以适应一些额外的需要。比如，我们要从Oracle数据库升级到MySQL数据库的时候，
下面的例子显示了如何向指定的栏目(field)中导入数据：
load data infile “/home/Order txt” into table Orders(Order_Number, Order_Date, Customer_ID);6.IGNORE number LINES: 忽略文件前几行。
7.当在服务器主机上寻找文件时，服务器使用下列规则：
（1）如果给出一个绝对路径名，服务器使用该路径名。
（2）如果给出一个有一个或多个前置部件的相对路径名，服务器相对服务器的数据目录搜索文件。
（3）如果给出一个没有前置部件的一个文件名，服务器在当前数据库的数据库目录寻找文件。
例如： /myfile txt”给出的文件是从服务器的数据目录读取，而作为“myfile txt”给出的一个文件是从当前数据库的数据库目录下读取。注意：字段中的空值用\N表示
10.3.4使用 mysqlimport 导入数据
mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。
从文件 salary.txt 中将数据导入到 dbms数据库中的salary表中, 可以使用以下命令：
$ mysqlimport -u root -p --local dbms salary.txt
password *****mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：
$ mysqlimport -u root -p --local --fields-terminated-by=":"
--lines-terminated-by="\r\n" dbms salary.txt
password *****mysqlimport 语句中使用 --columns 选项来设置列的顺序：
$ mysqlimport -u root -p --local --columns=b,c,a
dbms salary.txt
password *****使用mysqlimport -?命令，可以查看mysqlimport的具体参数及详细说明。下表是一些常见的选项：
-c, --columns=name | Use only these columns to import the data to. Give the column names in a comma separated list. This is same as giving columns to LOAD DATA INFILE. | 该选项采用用逗号分隔的列名作为其值。列名的顺序指示如何匹配数据文件列和表列。 |
-C, --compress | Use compression in server/client protocol. | 压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩） |
-d, --delete | First delete all rows from table. | 新数据导入数据表中之前删除数据数据表中的所有信息 |
–fields-terminated-by=name | Fields in the textfile are terminated by … | 指定数据之间的分隔符。默认的分隔符是跳格符（Tab） |
–fields-enclosed-by=name | Fields in the importfile are enclosed by … | 指定文本文件中数据的记录是以什么括起的， 很多情况下数据以双引号括起。 默认的情况下数据是没有被字符括起的 |
–fields-optionally-enclosed-by=name | Fields in the i.file are opt. enclosed by … | 字段包括符，只用在CHAR和VERCHAR字段上 |
–fields-escaped-by=name | Fields in the i.file are escaped by … | 转义字符 |
-f, --force | Continue even if we get an sql-error. | 不管是否遇到错误，MySQLimport将强制继续插入数据 |
-?, --help | Displays this help and exits. | 显示帮助消息并退出 |
-h, --host=name | Connect to host. | 将数据导入给定主机上的MySQL服务器。默认主机是localhost |
-i, --ignore | If duplicate unique key was found, keep old row. | 跳过或者忽略那些有相同唯一关键字的行， 导入文件中的数据将被忽略 |
–ignore-lines=# | Ignore first n lines of data infile. | 忽视数据文件的前n行 |
–lines-terminated-by=name | Lines in the i.file are terminated by … | 行记录分隔符。 默认的情况下MySQLimport以newline为行分隔符 |
-L, --local | Read all files through the client. | 从本地客户端读入输入文件 |
-l, --lock-tables | Lock all tables for write (this disables threads). | 数据被插入之前锁住表，防止在更新数据库时，用户的查询和更新受到影响 |
–low-priority | Use LOW_PRIORITY when updating the table. | 低优先级 |
-p, --password[=name] | Password to use when connecting to server. If password is not given it’s asked from the tty. | 提示输入密码 |
-W, --pipe | Use named pipes to connect to server. | 使用命名管道连接服务器 |
-P, --port=# | Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). | 用于连接的TCP/IP端口号 |
–protocol=name | The protocol of connection (tcp,socket,pipe,memory). | 连接使用的协议 |
-r, --replace | If duplicate unique key was found, replace old row. | 与－i选项的作用相反；此选项将替代表中有相同唯一关键字的记录 |
–shared-memory-base-name=name | Base name of shared memory. | 共享内存连接名。该选项只用于Windows |
-s, --silent | Be more silent. | 沉默模式。只有出现错误时才输出 |
-S, --socket=name | Socket file to use for connection. | 当连接localhost时使用的套接字文件(为默认主机) |
–use-threads=# | Load files in parallel. The argument is the number of threads to use for loading data. | 并行多线程导入个数 |
-u, --user=name | User for login if not current user. | 连接服务器时MySQL使用的用户名 |
-v, --verbose | Print info about the various stages. | 冗长模式。打印出程序操作的详细信息 |
-V, --version | Output version information and exit. | 显示版本（version） |
。
10.3.5测试用例（这里使用Ubuntu18.04服务器中使用Docker安装的MySQL8进行测试）
10.3.5.1将用不同的存放格式（自由设计）备份DBEM数据库中的employee, salary两个基本表，其中employee表要求只备份employeeID, name, education等三个字段。
select employeeID,name,education from employee into outfile "employee.csv" character set utf8 FIELDS TERMINATED BY '\t' ;
select* from salary into outfile "salary.txt" character set utf8 FIELDS TERMINATED BY '\t' ;
select* from department into outfile "department.docx" character set utf8 FIELDS TERMINATED BY '\t' ;
然后在备份中目录（secure_file_priv 的value中目录。笔者导出时直接outfile “salary.txt”这里会默认将数据导出到mysql/data下中 dbms数据库对应文件夹名dbms的目录下）：
10.3.5.2根据上述任务所保存的文件，将相关数据恢复到基本表中，其中要求employee表在恢复之前事先随机删除几条记录，SQL语句中要求指定replace功能
可以发现，使用replace命令会首先删除表中和所插入数据中键相同的数据然后再导入数据。并且除了employeeId，name,education这三项外都是NULL。
10.4整理笔记不易，看完如果对你有帮助请给个一键三连哦。
10.5详细的实验资料以及指导PPT请访问笔者GitHub地址：https://github.com/LJF2402901363/database_experiment.git
10.6本博客已同步到个人博客，如有需要请移步：
http://moyisuiying.com/index.php/experiment/mysqlexperiment/435.html
