MySQL8.0 数据备份与恢复

尽管采取了一些管理措施来保证数据库的安全，但是不确定的意外情况总是有可能造成数据的损失，例如意外的停电、管理员不小心的操作失误都可能会造成数据的丢失。保证数据安全最重要的一个措施是确保对数据进行定期备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复，这样就尽可能地降低了意外原因导致的损失。将介绍数据备份、数据恢复、数据迁移和数据导入导出的相关知识。PART1. 数据备份数据备份是

S_zhangmin

8239人浏览 · 2022-02-14 23:08:53

S_zhangmin · 2022-02-14 23:08:53 发布

PART1. 数据备份

数据备份是数据管理员非常重要的工作之一。系统意外崩溃或者硬件的损坏都可能导致数据库的丢失，因此MySQL管理员应该定期地备份数据库，使得在意外情况发生时，尽可能减少损失。数据备份的3种方法。

1. 使用MySQLdump命令备份

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

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数据库和各个表，并插入数据记录。

注意：这里要保证C盘下backup文件夹存在，否则将提示错误信息：系统找不到指定的路径。

可以看到，备份文件包含了一些信息，文件开头首先表明了备份文件使用的MySQLdump工具的版本号；然后是备份账户的名称和主机信息，以及备份的数据库的名称，最后是MySQL服务器的版本号。

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

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

该SET语句将当前系统变量character_set_client 的值赋给用户定义变量@old_character_set_client。

备份文件的最后几行MySQL使用SET语句恢复服务器系统变量原来的值：

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

该语句将用户定义的变量@old_character_set_client 中保存的值赋给实际的系统变量character_set_client。

备份文件中“--”字符开头的行为注释语句；以“/*！“开头、”*/”结尾的语句为可执行的MySQL注释，这些语句可以被MySQL执行，但在其他数据库管理系统中将被作为注释忽略，以提高数据库的可移植性。

另外，备份文件开始的一些语句以数字开头，代表的是MySQL版本号，这些语句只有在指定的MySQL版本或者比该版本高的情况下才能被执行。

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

在前面MySQLdump语法中介绍过，MySQLdump还可以备份数据中的某个表：

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

tbname 表示数据库中的表名，多个表名之间用空格隔开。

备份表和备份数据库中所有表的语句中不同的地方在于，要在数据库名称dbname之后指定需要备份的表名称。

3. 使用MySQLdump备份多个数据库

如果要使用MySQLdump备份多个数据库，就需要使用 -- database 参数：

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

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

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

mysqldump  -u user -h host -p --all-database > filename.sql

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

该语句创建名称为alldbinMySQL.sql的备份文件，文件中包含了对系统中所有数据库的备份信息。

注意：如果在服务器上进行备份，并且表均为MyISAM表，就应该考虑使用MySQLhotcopy，因为可以更快地进行备份和恢复。

2. 直接复制整个数据库目录

因为MySQL表保存为文件方式，所以可以直接复制MySQL数据库的存储目录及文件进行备份。MySQL的数据库目录不一定相同，在windows平台上，MySQL8.0存放数据库的目录通常默认为

“..\MySQL Server 8.0\data”或者其他用户定义目录；在Linux平台下，数据库目录位置通常为/var/lib/MySQL/，不同Linux版本下目录会有所不同，读者应在自己使用的平台下查找该目录。

这是一种简单、快速、有效的备份方式。要想保持备份的一致性，备份前需要对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES。这样当复制数据库目录中的文件时，允许其他客户继续查询表。需要FLUSH TABLES语句来确保开始备份前所有激活的索引页写入硬盘。当然，也可以停止MySQL服务再进行备份操作。

这种方式虽然简单，但不是最好的方式。因为这种方式对InnoDB存储引擎的表不适用。使用这种方式备份的数据最好恢复到相同版本的服务器中，不同的版本可能不兼容。

注意：在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同。

3. 使用MySQLhotcopy工具快速备份

MySQLhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。MySQLhotcopy在UNIX系统中运行。

MySQLhotcopy 命令语法：

mysqlhotcopy db_name_1, ... db_name_n /path/to/new_directory

db_name_1, ...,db_name_n 分别为需要备份的数据库的名称；/path/to/new_directory 指定备份文件目录。

要想执行MySQLhotcopy，必须可以访问备份的表文件，具有那些表的SELECT 权限、RELOAD 权限（以便能够执行FLUSH TABLES）和LOCK TABLES权限。

注意：MySQLhotcopy只是将表所在的目录复制到另一个位置，只能用于备份MyISAM 和 ARCHIVE表。备份InnoDB 类型的数据表时会出现错误信息。由于它复制本地格式的文件，因此也不能移植到其他硬件或操作系统下。

PART2. 数据恢复

管理人员操作的失误、计算机故障以及其他意外情况，都会导致数据的丢失和破坏。当数据丢失或意外破坏时，可以通过恢复已经备份的数据尽量减少数据丢失和破坏造成的损失。

1. 使用MySQL命令恢复

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

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

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

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

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

如果已经登录MySQL服务器，还可以使用source命令导入sql文件。

source filename

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

2. 直接复制到数据库目录

如果数据库通过复制数据库文件备份，可以直接复制备份的文件到MySQL数据目录下实现恢复。通过这种方式恢复时，保存备份数据的数据库和待恢复的数据库服务器的主版本号必须相同。而且这种方式只对MyISAM引擎的表有效，对于InnoDB引擎的表不可用。

执行恢复以前关闭MySQL服务，将备份的文件或目录覆盖MySQL的data目录，启动MySQL服务。对于Linux/UNIX 操作系统来说，复制完文件需要将文件的用户和组更改为MySQL运行的用户和组，通常用户是MySQL，组也是MySQL。

3. MySQLhotcopy快速恢复

MySQLhotcopy备份后的文件也可以用来恢复数据库，在MySQL服务器停止运行时，将备份的数据库文件复制到MySQL存放数据的位置（MySQL的data文件夹），重新启动MySQL服务即可。如果以根用户执行该操作，必须指定数据库文件的所有者，输入语句如下：

chown -R mysql.mysql /var/lib/mysql/dbname

注意：如果需要回复的数据库已经存在，则在使用DROP语句删除已经存在的数据库之后，恢复才能成功。另外，MySQL不同版本之间必须兼容，恢复之后的数据才可以使用。

PART3. 数据库迁移

数据库迁移就是数据从一个系统移动到另一个系统上。数据库迁移有以下原因：

1）需要安装新的数据库服务器

2）MySQL版本更新

3）数据库管理系统的变更（如从Microsoft SQL Server 迁移到 MySQL）。

1. 相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动。迁移过程其实就是在源数据库备份和目标数据库恢复过程的组合。

关于数据库备份和恢复时，已经知道最简单的方式是通过复制数据库文件目录，但是此种方法只适用于MyISAM引擎的表。而对于InnoDB表，不能用直接复制文件的方式备份数据库，因此最常用和最安全的方式是使用MySQLdump命令导出数据，然后在目标数据库服务器使用MySQL命令导入。

例：将www.abc.com 主机上的MySQL数据库全部迁移到www.bcd.com 主机上。在www.abc.com 主机上执行的命令如下：

mysqldump -h www.bac.com -uroot -ppassword dbname |
mysql -h www.bcd.com -uroot -ppassword

MySQLdump 导入的数据直接通过管道符“|”传到MySQL 命令导入到主句 www.bcd.com 数据库中，dbname为需要迁移的数据库名称，如果要迁移全部的数据库，可使用参数--all-databases。

2. 不同版本的MySQL数据库之间的迁移

因为数据库升级等原因，需要将较旧版本MySQL数据库中的数据迁移到较新版本的数据库中。MySQL服务器升级时，需要先停止服务，然后卸载旧版本，并安装新版的MySQL，这种更新方法很简单，如果想保留旧版本中的用户访问控制信息，就需要备份MySQL中的MySQL数据库，在新版本MySQL安装完成之后，重新读入MySQL备份文件中的信息。

旧版本与新版本的MySQL可能使用不同的默认字符集，例如MySQL8.0 版本之前，默认字符集为latin1，而MySQL8.0 版本默认字符集为utf8mb4。数据库中有中文数据的，迁移过程中需要对默认字符集进行修改，不然可能无法正常显示结果。

新版本会对旧版本有一定兼容性。从旧版本的MySQL向新版本的MySQL迁移时，对于MyISAM引擎的表，可以直接复制数据库文件，也可以使用MySQLdump将数据导出。然后使用MySQL命令导入到目标服务器上。从新版中向旧版本MySQL迁移数据时要特别小心，最好使用MySQLdump命令导出，然后导入目标数据库中。

3. 不同数据库之间的迁移

不同类型的数据库之间的迁移，是指把MySQL的数据库转移到其他类型的数据库，例如从MySQL迁移到Oracle，从Oracle迁移到MySQL，从MySQL迁移到SQL Server等。

迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会不同。例如，MySQL中日期字段分为DATE和TIME两种，而Oracle日期字段只有DATE。另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的SQL语句有差别。例如，MySQL几乎完全支持标准SQL语言，而Microsoft SQL Server使用的是T-SQL语言，T-SQL中有一些非标准的SQL语句，因此在迁移时必须对这些语句进行语句映射处理。

数据库迁移可以使用一些工具，例如在Windows系统下，可以使用MyODBC实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit 也可以在不同数据库间进行数据迁移。

PART4. 表的导出和导入

有时会需要将MySQL数据库中的数据导出到外部存储文件中，MySQL数据库中的数据可以导出成sql文本文件、xml文件或者html文件。同样，这些导出文件也可以导入到MySQL数据库中。

1. 使用SELECT ... INTO OUTFILE导入文本文件

MySQL数据库导出数据时，允许使用包含导出定义的SELECT语句进行数据的到处操作。该文件被创建到服务器主机上，因此必须拥有文件写入权限（FILE 权限）才能使用此语法。“SELECT ...INTO OUTFILE 'filename'” 形式的SELECT 语句可以把被选择的行写入一个文件中，并且filename不能是一个已经存在的文件。SELECT...INTO OUTFILE语句的基本格式如下：

SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]
-- OPTIONS 选项
FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARING BY 'value'
LINES TERMINATED BY 'value'

可以看到SELECT columnlist FROM table WHERE condition 为一个查询语句，查询结果返回满足指定条件的一条或多条记录；INTO OUTFILE 语句的作用就是把前面SELECT 语句查询出来的结果导出名称为“filename”的外部文件。[OPTIONS]为可选参数选项，OPTIONS部分的语法包括FIELDS和LINES子句，其可能的取值有：

1）FIELDS TERMINATED BY 'value'：设置字段之间的分隔字符，可以为单个或多个字符默认情况下为制表符‘\t'。

2）FIELDS [OPTIONALLY] ENCLOSED BY 'value'：设置字段的包围字符，只能为单个字符，若使用了OPTIONALLY则只有CHAR 和VARCHAR等字段数据字段被包括。

3）FIELDS ESCAPED BY 'value'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为'\'。

4）LINES STARTING BY 'value'：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。

5）LINES TERMINATED BY 'value'：设置每行数据结尾的字符，可以为单个或多个字符，默认值为'\n'。

FIELDS 和 LINES两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。

SELECT...INTO OUTFILE语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件，不能使用SELECT ...INTO OUTFILE。这种情况下，应该在客户主机上使用比如"MySQL-e"SELECT ...">file_name"的命令来生成文件。

SELECT ...INTO OUTFILE是LOAD DATA INFILE的补语。用于语句的OPTIONS部分的语法包括部分FIELDS和LINES子句，这些子句与LOAD DATA INFILE语句同时使用。

这是因为MySQL默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。

查询指定目录的命令如下：

show global variables like '%secure%';

因为secure_file_priv 配置的关系，所以必须导出到D:\07tools\MySQL\ProgramData\MySQL Server 8.0\Uploads\目录下。如果想自定义导出路径，需要修改my.ini配置文件。打开路径D:\07tools\MySQL\ProgramData\MySQL Server 8.0，用记事本打开my.ini 然后搜索到以下代码：

secure-file-priv

在上述代码前添加#，然后添加以下内容。

secure-file-priv="D:/"

默认情况下，MySQL使用制表符"\t" 分隔不同的字段，字段没有被其他字段括起来。另外，默认情况下，如果遇到NULL值，将会返回"\N"，代表空值，其中的反斜线"\"表示转义字符，如果使用ESCAPED BY选项，则N前面为指定的转义字符。

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

MySQLdump创建一个包含创建表的CREATE TABLE 语句的tablename.sql文件和一个包含其数据的tablename.txt文件。

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选项使用。

1）--fields-terminated-by=value：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符“\t”。

2）--fields-enclosed-by=value：设置字段的包围字符。

3）--fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，只能包括CHAR和VARCHAR等字符数据字段。

4）--fields-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为反斜线"\"

5) --lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为"\n"。

注意：与SELECT ... INTO OUTFILE 语句中的OPTIONS各个参数设置不同，这里OPTIONS各个选项等号后面的value值不要用引号括起来。

3. 使用MySQL命令导出文本文件

MySQL是一个功能丰富的工具命令，使用MySQL还可以在命令行模式下执行SQL指令，将查询结果导入到文本文件中。相比MySQLdump，MySQL工具导出的结果可读性更强。

如果MySQL服务器是单独的机器，用户是在一个client上进行操作，用户要把数据结果导入到client机器上。可以使用MySQL -e语句。

mysql -uroot -p --execute= "SELECT语句" dbname > filename.txt

该命令使用--execute选项，表示执行该选项后面的语句并退出，后面的语句必须用双引号括起来，dbname为要导出的数据库名称；导出的文件中不同列之间使用制表符分隔，第一行包含了各个字段名。

（这种方法尝试没有反应，建议使用SELECT ... INTO OUTFILE）

4. 使用LOAD DATA INFILE方式导入文本文件

MySQL允许将数据导出到外部文件，也可以从外部文件导入数据。MySQL提供了一些导入数据的工具，包括LOAD DATA语句、source命令和MySQL命令。LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为文字字符串。

LOAD DATA语句的基本格式如下：

LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
-- OPTIONS 选项
    FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value'

可以看到LOAD DATA语句中，关键字INFILE后面的filename文件为导入数据的来源；tablename表示待导入的数据库名称；[OPTIONS]为可选参数选项，OPTIONS部分的语法包括FIELDS和LINES子句，其可能的取值有：

1）FIELDS TERMINATED BY ‘value'：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符”\t“。

2）FIELDS [OPTIONALLY] ENCLOSED BY 'value’：设置字段的包围字符，只能为单个字符。如果使用了OPTIONALLY，则只有CHAR和VARCHAR等字符数据字段被包括。

3）FIELDS ESCAPED BY ‘value’：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为”\“。

4）LINES STARTING BY ‘value'：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。

5）LINES TERMINATED BY ’value‘：设置每行数据结尾的字符，可以为单个或多个字符，默认值”\n“。

IGNORE number LINES 选项表示忽略文件开始处的行数，number表示忽略的行数。执行LOAD DATA语句需要FILE权限。

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

使用MySQLimport可以导入文本文件，并且不需要登录MySQL客户端。MySQLimport命令提供许多与LOAD DATA INFILE 语句相同的功能，大多数选项直接对应LOAD DATA INFILE 子句。使用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]为可选参数选项，常见的取值有：

1）--fields-terminated-by=’value‘：设置字段之间的分隔字符，可以为单个或多个字符，默认值为”\t“。

2）--fields-enclosed-by=’value'：设置字段的包围字符。

3）--fields-optionally-enclosed-by=‘value’：设置字段的包围字符，只能为单个字符，包括CHAR和VARCHAR等字符数据字段。

4）--fields-escaped-by=‘value’：控制如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为反斜线”\“。

5）--lines-terminated-by=‘value'：设置每行数据结尾的字符，可以为单个或多个字符，默认值为"\n"。

6）--ignore-lines=n：忽视数据文件的前n行。