第8章 MySQL数据库备份与恢复

8.2 常用的备份恢复方法
2.导入表数据
LOAD DATA …INFILE格式： LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '文件名.txt' [REPLACE | IGNORE] INTO TABLE 表名 [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(列名或用户变量, ...)] [SET 列名 = 表达式, ...)]
8.3 日志文件
8.3.1 启用日志
二进制日志可以在启动服务器的时候启用，这需要修改C:\Program Files\My SQL\MySQL Server 5.6 文件夹中的my-default.ini 选项文件。打开该文件，找到 [mysqld]所在行，在该行后面加上以下格式的一行： log-bin[=filename]
8.2.2 使用客户端工具：备份数据库
使用客户端程序的方法如下。 打开命令行，进入bin目录： cd C:\Program Files\MySQL\MySQL Server 5.6\bin 后面介绍的客户端命令都在此处输入，如图8.2所示。
此处输入客 户端命令
8.2.2 使用客户端工具：备份数据库
使用它决定向哪个表导入文件的内容。例如，“patient.txt”、“patient.sql”和
“patient”都会被导入名为patient的表中。所以备份的文件名应根据需要恢复表 命名。
【例8.8】恢复xscj数据库中表xs的数据，保存数据的文件为xs.txt，命令如下：
mysqlimport -uroot –p19830925 --low-priority --replace xscj xs.txt
8.2.2 使用客户端工具：备份数据库
MySQL还能备份整个数据库系统，即系统中的所有数据库。 【例8.4】备份MySQL服务器上的所有数据库。使用如下命令： mysqldump -uroot –p19830925 --all-databases>all.sql 【例8.5】将xscj数据库中所有表的表结构和数据都分别备份到D盘file文件夹下。 命令如下： mysqldump -uroot -p19830925 --tab=D:/file/ xscj 其效果是在file文件夹生成xscj数据库中每个表所对应的.sql文件和.txt文件。
8.2 常用的备份恢复方法
说明： LOW_PRIORITY | CONCURRENT：若指定LOW_PRIORITY，则延迟语句的 执行。 LOCAL：若指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送 到服务器。 文件名.txt：该文件中保存了待存入数据库的数据行，它由SELECT INTO…OUTFILE命令导出产生。 表名：该表在数据库中必须存在，表结构必须与导入文件的数据行一致。 REPLACE | IGNORE：如果指定了REPLACE，则当文件中出现与原有行相同 的唯一关键字值时，输入行会替换原有行。 FIELDS子句：和SELECT..INTO OUTFILE语句中类似。 LINES子句：TERMINATED BY亚子句用来指定一行结束的标志。 IGNORE number LINES：这个选项可以用于忽略文件的前几行。 列名或用户变量：如果需要载入一个表的部分列或文件中字段值顺序与表中列 的顺序不同，就必须指定一个列清单。 SET子句：SET子句可以在导入数据时修改表中列的值。
由于二进制数据可能非常庞大，无法在屏幕上延伸，可以保存到文本文件中：
mysqlbinlog bin_log.000001>D:/file/lbin-log000001.txt 使用日志恢复数据的命令格式如下： mysqlbinlog [options] log-files… | mysql [options]
8.1 基本概念
因此，拥有能够恢复的数据对于一个数据库系统来说是非常重要的。MySQL 有三种保证数据安全的方法。
（1）数据库备份：通过导出数据或者表文件的拷贝来保护数据。
（2）二进制日志文件：保存更新数据的所有语句。 （3）数据库复制：MySQL内部复制功能建立在两个或两个以上服务器之间， 通过设定它们之间的主从关系来实现的。其中一个作为主服务器，其他的作为从服 务器。
8.2 常用的备份恢复方法
导出成功后可以查看D盘file文件夹下的myfile1.txt文件，文件内容如图8.1所 示。
8.2 常用的备份恢复方法
文件备份完后可以将文件中的数据导入到course表中，使用以下命令： load data infile 'd:/file/myfile1.txt' into table course fields terminated by ',' optionally enclosed by '"' lines terminated by '?';
8.2.2 使用客户端工具：备份数据库
【例8.7】假设xs表结构损坏，备份文件在D盘file目录下，现将包含xs表结构 的.sql文件恢复到服务器中。命令如下： mysql -uroot -p19830925 xscj<D:/file/xs.sql 如果只恢复表中的数据，就要使用mysqlimport客户端。
8.2.2 使用客户端工具：备份数据库
（2）备份数据库 mysqldump程序还可以将一个或多个数据库备份到一个文件中。 命令格式： mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > filename 【例8.3】 备份xscj数据库和test数据库到D盘file文件夹下。 命令如下： mysqldump -uroot -p19830925 --databases xscj test>D:/file/data.sql
8.2 常用的备份恢复方法
说明： （1）这个语句的作用是将表中SELECT语句选中的行写入到一个文件中。文
件默认在服务器主机上创建，并且文件存在原文件将被覆盖。
（2）使用OUTFILE时，可以在export_options中加入以下两个自选的子句， 它们的作用是决定数据行在文件中存放的格式。 （3） FIELDS子句：在FIELDS子句中有三个亚子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。 （4）LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标 志，如“LINES TERMINATED BY '?'”表示一行以“?”作为结束标志。
8.2 常用的备份恢复方法
【例8.1】备份xscj数据库中的kc表中数据到D盘file目录中，要求字段值如果是 字符就用双引号标注，字段值之间用逗号隔开，每行以“？”为结束标志。最后将 备份后的数据导入到一个和kc表结构一样的空表course表中。 首先导出数据（操作前先创建D:\file目录）： use xscj; select * from kc into outfile 'd:/file/myfile1.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '?';
8.2.2 使用客户端工具：备份数据库
2．使用mysqlimport恢复数据 mysqlimport客户端可以用来恢复表中的数据，它提供了LOAD DATA INFILE 语句的一个命令行接口，发送一个LOAD DATA INFILE命令到服务器来运作。它 大多数选项直接对应LOAD DATA INFILE语句。 mysqlimport命令格式为： mysqlimport [options] db_name filename ... 对于在命令行上命名的每个文本文件，mysqlimport剥去文件名的扩展名，并
8.3.2 用mysqlbinlog处理日志
使用mysqlbinlog实用工具可以检查二进制日志文件，命令格式为： mysqlbinlog [options] log-files... 例如，运行以下命令可以查看bin_log.000001的内容： mysqlbinlog bin_log.000001
1．使用mysqldump备份数据 mysqldump客户端也可用于备份数据，它比SQL语句多做的工作是可以在导出
的文件中包含表结构的SQL语句，因此可以备份数据库表的结构，而且可以备份一
个数据库，甚至整个数据库系统。 （1）备份表 命令格式： mysqldump [OPTIONS] db_name [tables] > filename 同其他客户端程序一样，备份数据时需要使用一个用户账号连接到服务器，这 需要用户手工提供参数或在选项文件中修改有关值。参数格式为： -h[hostname] -u[username] -p[password] 【例8.2】使用mysqldump备份xs表和kc表。具体命令如下： mysqldump -h localhost -u root -p19830925 xscj xs kc > twotables.sql
8.2.2 使用客户端工具：备份数据库
（3）恢复数据库 mysqldump程序备份的文件中存储的是SQL语句的集合，用户可以将这些语
句还原到服务器中以恢复一个损坏的数据库。
【例8.6】假设xscj数据库损坏，用备份文件将其恢复。 备份xscj数据库的命令为： mysqldump -uroot –p19830925 xscj>xscj.sql 恢复命令为： mysql -uroot –p19830925 xscj<xscj.sql 如果表的结构损坏，也可以恢复，但是表中原有的数据将全部被清空。
第8章 MySQL数据库备份与恢复
基本概念 常用的备份恢复方法 日志文件
8.1 8.2 8.3
Baidu Nhomakorabea
8.1 基本概念
数据库中的数据丢失或被破坏可能是由以下原因造成： （1）计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会
出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。
（2）软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会 误操作数据引起数据破坏。 （3）病毒。破坏性病毒会破坏系统软件、硬件和数据。 （4）误操作。如用户误使用了诸如DELETE、UPDATE等命令而引起数据丢 失或破坏。 （5）自然灾害。如火灾、洪水或地震等，它们会造成极大的破坏，会毁坏计 算机系统及其数据。 （6）盗窃。一些重要数据可能会遭窃。
8.2.3 直接复制
根据本章前两节的介绍，由于MySQL的数据库和表是直接通过目录和表文件 实现的，因此可以通过直接复制文件的方法来备份数据库。不过，直接复制文件 不能够移植到其他机器上，除非要复制的表使用MyISAM 存储格式。 如果要把MyISAM类型的表直接复制到另一个服务器使用，首先要求两个服务 器必须使用相同的MySQL版本，而且硬件结构必须相同或相似。
8.3.1 启用日志
假设这里filename取名为bin_log。若不指定目录，则在MySQL的data目录下 自动创建二进制日志文件。由于下面使用mysqlbinlog工具处理日志时，日志必须
处于bin目录下，所以日志的路径就指定为bin目录，添加的行改为以下一行：
log-bin=C:/Program Files/MySQL/MySQL Server 5.6/bin/bin_log 保存，重启服务器。 重启服务器的方法可以是： 先关闭服务器，在如图8.2所示窗口中输入以下命令： net stop mysql 再启动服务器： net start mysql 此时，MySQL安装目录的bin目录下多出两个文件：bin_log.000001和 bin_log.index。