跳至主要內容

MySQL备份与恢复

棋大约 13 分钟约 3901 字

为了保证数据的安全，需要定时对数据进行备份。备份的方式有很多种，效果也不一样。若数据库中的数据发生了错误，需要使用备份好的数据进行数据还原，以将损失降到最低，过程中还可能涉及到数据库信息的导入和导出。本章将介绍数据备份和还原的方法。

数据备份

备份数据是数据库管理最常用的操作。为了保证数据库中数据的安全，数据管理员还需定期进行数据备份，一旦数据库遭到损坏，即通过备份的文件来还原数据库。可能遭到数据破坏的原因有以下几个：存储介质故障、服务器彻底瘫痪、用户的误操作以及黑客的入侵。因此数据备份是很重要的操作。

使用mysqldump命令备份

使用mysqldump命令可以将数据库的数据备份成一个文本文件。表的结构和表的数据将存储在文本文件中。mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一条CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。在恢复时，CREATE语句和INSERT语句就可以用来重新生成表。

在使用mysqldump命令进行数据备份时，经常分为以下三种格式：备份一个数据库、备份多个数据库、备份所有数据库。下面将分别介绍这三种备份格式。

备份一个数据库

使用mysqldump备份一个数据库的语法如下。

mysqldump -u username -p dbname table1,table2,... > BackupName.sql

其中，username指用户名；dbname指数据库的名称；tableX表示表的名称，没有该参数时将备份整个数据库；BackupName.sql代表目标备份文件的名称，文件名前还可加上一个绝对路径。

目标备份文件的后缀并非都是.sql，只是.sql给人的第一感觉就是它和数据库有关，但如将它命名为.txt的文件也可以。

如我们创建一个如下的数据库和数据表并将它备份至A盘中。

create database db_stu;
use db_stu;
create table tb_stu(
	id int(11) auto_increment,
    name varchar(50) not null comment '姓名',
  	enroll_year year not null comment '入学年份',
    stu_id varchar(4) not null comment '学号',
    birthday date not null comment '生日',
    primary key id (id),
    index id (id),
    unique u_stu_id (stu_id),
    check (stu_id regexp '[0123456789]{4}')
);
insert into tb_stu (name, enroll_year, stu_id, birthday) values
    ('张三', 2020, '1001', '2002-01-01'),
    ('李四', 2020, '1002', '2002-02-01'),
    ('王五', 2022, '2001', '2002-10-01'),
    ('王六', 2022, '2002', '2003-01-01');
select * from tb_stu;
运行结果
运行结果
运行结果
运行结果

执行完以上语句后，使用mysqldump将此表备份到A盘，语法如下。

mysqldump -u root -p db_stu tb_stu > A:\stu.sql
运行结果
运行结果

在退出MySQL命令行之后，回到系统命令行，然后输入上面的命令，再输入密码，就可以在A盘根目录看到此文件了。我们使用Datagrip打开此文件，便可查看此文件的内容。此文件的主要内容及意义如下图。

运行结果
运行结果

文件开头记录的就是MySQL的版本号、备份的主机名、数据库名；以/*!40101开头的内容是只有MySQL版本大于4.1.1才执行的语句，下面的/*!40103、/*!40104也是这样。

该文件中没有创建数据库的语句，因此以上语句必须还原到已经存在的数据库中。

备份多个数据库

使用mysqldump命令备份多个数据库的语法如下。

mysqldump -u root -p --databases database1,database2,... > BackupName.sql

这里要加上子参数--databases，代表选择多个数据库。

备份所有数据库

使用mysqldump命令备份所有数据库的语法如下。

mysqldump -u root -p254456 --all-databases > A:/BackupName.sql

直接复制整个数据库目录

MySQL有一种最简单的备份方法，就是将MySQL的数据库文件直接复制出来，使用此方法前最好先将服务器停止，因为在复制的过程中若服务器改变了数据，再次恢复时会造成数据不一致的情况。即使这样，一般情况下也不建议这样做，因为可能涉及到版本等诸多问题。

采用此方法复制目录前可通过以下语句查看数据库文件的保存位置。

SHOW VARIABLES LIKE '%datadir%';

数据恢复

使用mysql命令还原

此方法是将上一节中使用mysqldump命令保存的文本文件作为数据源导入到数据库中，语法如下。

mysql -u root -p [dbname] < backup.sql

其中``dbname`参数表示数据库名称。该参数是可选参数，选择此参数时，表示还原此数据库下的表，不选此参数则代表还原所有数据库。

如我们还原上面保存的tb_stu表。

先看一下还原之前的数据库。

还原前的数据库
还原前的数据库

我们需要先创建数据库db_stu，然后退出MySQL命令行，然后我们通过下面的命令还原数据表。

mysql -u root -p < A:\stu.sql
运行结果
运行结果

没有报错后我们重新登录数据库，并且查看数据。

运行结果
运行结果

可以看到之前的表结构以及表数据都已经还原回来了。

直接复制到数据库目录

在上面介绍了一种直接复制数据库目录的方法，通过这种方式备份的数据，在还原时可以直接复制到MySQL的数据库目录下，但必须保证两个数据库的版本号是相同的，而且此方法对MyISAM类型的表比较有效，对InnoDB类型的表则不可用，因为InnoDB表的表空间不可直接复制。

数据库迁移

实际应用中可能遇到数据库升级、开发了新的管理系统、换用其他的数据库等情况。数据库迁移指将数据库中的数据从一个数据库转移到另一个数据库。数据库迁移可分为相同版本的数据库迁移和不同数据库的数据库迁移。因为只有MyISAM的表支持直接复制整个数据库目录，而InnoDB表不支持，因此最安全最有效的方法就是使用上文介绍过的mysqldump命令复制整个数据库。语法如下。

mysql -h name1 -u root -password=password1 --all-databases |
mysql -h host2 -u root -password=password2

其中|表示管道，其作用是将mysqldump备份的文件送给mysql命令；-password=password1是name1主机上root用户的密码，后面的也是同理。通过这种方式可以实现迁移。

但要是想在不同的数据库之间进行迁移，就需要根据双方数据库的类型指定方法。如从MySQL迁移到Oracle数据库，需要使用mysqldump命令先导出SQL文件，然后修改文件中的CREATE语句；若要从MySQL迁移到SQL Server，则可以使用MySQL提供的ODBC驱动。

表的导出与导入

MySQL数据库的表可以导出成文本文件、XML文件或HTML文件，相应的文本文件也可以导入到MySQL中。在表的维护中，需要经常执行数据库文件的导入和导出。下面将分别进行介绍。

用SELECT ... INTO OUTFILE语句导出文本文件

语法如下。

SELECT [col_name[,col_name,...]] FROM table [where clause] INTO OUTFILE 'target_file' [OPTION];

该语句分为两个部分，前半部分是一个简单的SELECT语句，通过该语句查询所需要的数据，后半段的作用是导出数据，其中，'target_file'参数指出将查询的数据导入到哪个文件；OPTION参数可选的几个选项如下：

选项含义
FIELDS TERMINATED BY 'str'设置字符串的分隔符，默认值是'\t'，其中str表示字符串
FIELDS ENCLOSED BY 'str'设置字符来括上字段的值，默认情况下不使用任何符号
FIELDS OPTIONALLY ENCLOSED BY 'str'设置字符来括上CHAR、VARCAHR、TEXT等字符型字段，默认情况下不使用任何符号
FIELDS ESCAPED BY 'str'设置转义字符，默认情况下是\
LINES STARTING BY 'str'设置每行开头的字符，默认情况下无任何字符
LINES TERMINATED BY 'str'设置每行的结束符，默认是\n

使用此语句时，指定的目标路径只能是MySQL的secure_file_priv参数所指定的路径，获取此路径的语法如下。

SELECT @@secure_file_priv;

secure_file_priv参数用于限制LOAD DATA，SELECT ... OUTFILE，LOAD_FILE()传到哪个指定目录。

  • secure_file_priv为NULL时，表示限制mysqld不允许导入或导出。
  • secure_file_priv参数为/tmp时，表示限制mysqld只能在/tmp目录下执行导入或导出，其他目录不能执行。
  • secure_file_priv没有值时，表示不限制mysqld在任意目录的导入或导出。
运行结果
运行结果

从上图可看到，该值为NULL，表示限制mysqld导入或导出。此时可以在my.ini文件中添加secure_file_priv=''一行即可。设置完成后重启MySQL并重新登录，再次查看该值，如下图，即可导出数据。

运行结果
运行结果

例：

select * from db_stu.tb_stu into outfile 'A:\stu1.txt'
fields terminated by ',' optionally enclosed by '\"'
lines starting by '\>' terminated by '\r\n';

其中\"、\>都为转义字符，\r\n是Windows系统的分隔符。

运行结果
运行结果
运行结果
运行结果

执行成功后，打开A盘下的stu1.txt文件即可查看保存的文本文件。

使用mysqldump命令导出文本文件

语法如下。

mysqldump -u root -p -T 目标目录 dbname table [option];

其中“目标目录”参数指出文本文件的路径；dbname参数表示数据库的名称；table参数表示表的名称；option表示附件选项，具体如下：

  • --fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是``\t`。
  • --fields-enclosed-by=字符串：设置字符来括上字段的值。
  • --fields-optionally-enclosed-by=字符：设置字符括上CHAR、VARCHAR和TEXT等字符型字段。
  • --fields-escaped-by=字符：设置转义字符。
  • --lines-terminated-by=字符：设置每行结束符

有些时候这些字符必须用双引号括起来，否则MySQL数据库系统将不能识别这几个参数。

mysqldump命令还可以导出为XML文件，语法如下。

mysqldump -u root -p --xml | -X dbname table > E:\name.xml

使用mysql命令导出文本文件

mysql命令可以用来登录MySQL服务器和还原备份文件，除此之外还可以用来导出为文本文件。语法如下。

mysql -u root -p -e "select 语句" db_name > name.txt;

其中，-e选项代表执行SQL语句，"select 语句"代表查询语句。

如我们将tb_stu表存到A盘中，语法如下。

mysql -u root -p -e "select * from tb_stu" db_stu > A:/stu3.txt
运行结果
运行结果
运行结果
运行结果

可以看到，我们成功保存了。

mysql命令还可以导出XML文件或HTML文件，语法如下。

mysql -u root -p --xml | -X -e "select 语句" dbname > name.xml
mysql -u root -p --html | -H -e "select 语句" dbname > name.html

如我们将tb_stu表导出为html文件，语法如下。

mysql -u root -p --html -e "select * from tb_stu" db_stu > A:\stu.html
运行结果
运行结果
运行结果
运行结果

从运行结果可以看到，导出的数据变成了一张数据表。

用LOAD DATA INFILE命令将文本文件导入数据表

语法如下。

LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE file_name INTO TABLE table_name [OPTION];

参数说明如下表。

参数含义
LOW_PRIORITY若指定此参数，则LOAD DATA语句会被延迟，直到没有其他的客户端正在读取表
CONCURRENT若指定此参数，则当LOAD DATA正在执行时，其他线程可以同时使用此表的数据
LOCAL若指定此参数，则文件会被主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定其确切的位置。若给定的是一个相对路径名称，则此名称会被理解成相对于启动客户端时所在的目录。若没有指定此参数，则文件必须位于服务器主机上，并且会被服务器直接读取。使用此参数时速度会略慢。
file_name用来指定导入的文本文件得路径和名称。这个文件可以手动创建，也可以使用其他的程序创建。可以使用绝对路径，也可以不指定路径，直接指定文件名，这时服务器会在默认数据库路径中查找并读取
table_name用来指定需要导入数据的表名，该表在数据库中必须存在，表结构必须与导入文件的数据一致
OPTION用于设置相应的选项，其值可以是下面9个值中的任意一个
值含义
FIELDS TERMINATED BY '字符串'用于指定字段分隔符为此字符串，默认是\t
FILED ENCLOSED BY '字符串'用于设置字符串括上每个字段的值，默认不设置任何字符
FIELDS OPTIONALLY ENCLOSED
BY '字符串'		用于设置括上CHAR、VARCHAR和TEXT类型的字段值的符号，默认不使用任何符号
FILEDS ESCAPED BY '字符串'用来设置转义字符的字符符号，默认是\
LINES STARTING BY 字符串用来设置每行开头的字符串，默认不设置任何字符
LINES TERMINATED BY '字符串'设置每行结束的字符串，默认使用\n
IGNORE n LINES忽略文件的前n条记录
(字段列表)用于实现根据字段列表中的字符和顺序加载记录
SET column=expr用于设置列的转换方式，即所指定的列要经过转换才会被加载

在使用该命令时，必须根据要导入文本文件中字段值的分隔符来指定使用的分隔符；并且如果文本文件中的字段顺序和表的字段顺序不一致时，就需要使用(字段列表)参数来指定加载字段的顺序。

如我们使用LOAD DATA ... INFILE命令导入如下的学生表。

运行结果
运行结果
load data infile 'A:/tb_stu.txt' into table new_tb_stu
	character set 'utf8' -- 若出现乱码，可将读取方式改为utf8
	fields terminated by ',' enclosed by '\"'
	lines starting by '\>' terminated by '\r\n';
运行结果
运行结果
运行结果
运行结果

用mysqlimport命令导入文本文件

在MySQL中，若只是恢复数据，则可以命令行窗口中使用mysqlimport实现。语法如下。

mysqlimport -u root -p database filename [option];

OPTION参数的几个可用值如下表。

值含义
--fields-terminated-by='str'以字符串str作为字段的分隔符，默认为\t
--fields-enclosed-by='str'用字符串str括上每个字段的值，默认不使用任何字符
--fields-optionally-enclosed-by='str'用字符串str括上CHAR、VARCHAR和TEXT类型字段的值，
默认不使用任何字符
--fields-escaped-by='str'设置字符串str为转义字符
--lines-terminated-by='str'设置每行的结束符为str
--ingore-lines=n忽略前n行的数据

在MySQL中，表名和字符串可以使用反引号括起来，但是这不是必须的，但若表名或字符串是MySQL中的某个关键字，那么就必须使用反引号将其括起来。

上次编辑于:
贡献者: QI
2024 © ImQi1