数据库备份与恢复

一、myqldump备份数据库

     1.备份单个数据库的语法

      mysqldump -u 用户名 -h 主机名 -p 密码 数据库名 [表名 [表名…]] >备份文件名.sql

【例7-2】使用mysqldump命令备份数据库students_db中的所有表，存于f:\db_bak文件夹下，文件名为studentdb_bak.sql。

     

      2.备份多个数据库的语法

mysqldump -u 用户名 -h 主机名 -p 密码 --databases 数据库名 数据库名 … >备份文件名.sql

【例7-4】使用mysqldump命令备份数据库students_db和scott，存于f:\db_bak文件夹下，文件名为db_bak.sql。    

        3.备份所有数据库的语法

mysqldump -u 用户名 -h 主机名 -p 密码 --all-databases >备份文件名.sql

【例7-5】使用mysqldump命令备份所有数据库，存于f:\db_bak文件夹下，文件名为all_db_bak.sql。

二、使用mysql命令恢复数据库

mysql -u 用户名 -p 数据库名 < 备份文件名.sql

mysql -u root -p dbtest<d:\db_bak\china.sql//如果dbtest不存在，要先创建dbtest数据库

【说明】执行mysql命令前，必须在MySQL服务器中创建命令中的数据库，如果该数据库不存在，在数据恢复过程中会出错。

【例7-6】使用mysql命令将备份文件studentdb_bak.sql恢复到数据库students中。

（1）在MySQL服务器上创建students数据库。

          CREATE DATABASE students;

（2）在CMD命令提示符窗口中执行如下命令。

三、表数据的导入与导出

1. 使用SELECT …INTO OUTFILE语句导出表数据。

SELECT语句 INTO OUTFILE '文本文件'

[FIELDS [TERMINATED BY '字符']

                [[OPTIONALLY] ENCLOSED BY '字符']

                [ESCAPED BY '字符']

 ]

[LINES   [STARTING BY '字符串']

                [TERMINATED BY '字符串']

]

【说明】

① TERMINATED BY '字符'：字段分隔符，默认是制表符'\t'。

② [OPTIONALLY] ENCLOSED BY '字符'：向字段值两边加上字段包围符。如果使用OPTIONALLY选项，只在CHAR、VARCAHR和TEXT字符串类型的字段值两边添加字段包围符。

③ ESCAPED BY '字符'：设置转义字符，默认值为'\'。

④ STARTING BY '字符串'：设置每行开头的字符，默认情况下无任何字符。

⑤ TERMINATED BY '字符串'：设置每行的结束符，默认值是'\n'。

【注意】

     在使用SELECT …INTO OUTFILE语句时，目标文件的路径只能是MySQL的secure_file_priv参数所指定的位置，可通过以下语句获取。

【例7-7】使用SELECT …INTO OUTFILE语句备份students_db数据库中的student表的数据。要求字段之间用“｜”隔开，字符型数据用双引号括起来。

USE students_db;
SELECT * FROM student
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/table_bak.txt'
FIELDS TERMINATED BY '|'  OPTIONALLY ENCLOSED BY '"'
LINES  TERMINATED BY '\r\n';

2. 使用mysqldump命令导出表数据

     mysqldump -u root -p -T "目标路径" 数据库名 表名

     [--fields-terminated-by=字符]

     [--fields-enclose-by=字符]

     [--fields-optionally-enclosed-by=字符]

     [--fields-escaped-by=字符]

     [--lines-terminated-by=字符串]

【说明】

① 只有指定-T参数，才能导出纯文本文件。

② 导出生成的文件有两个，一个是包含创建表的CREATE TABLE语句的表名.sql文件，一个是包含其数据的表名.txt文件。

③ 目标路径必须是MySQL的secure_file_priv参数所指定的位置。

④ 各选项功能对应“SELECT …INTO OUTFILE”语句中的各项功能。

【例7-8】使用mysqldump命令将students_db数据库中的sc表中的记录导出到文本文件。

导出的文件默认以表的名字命名。

3. 使用LOAD DATA INFILE语句导入表数据

LOAD DATA INFILE '文本文件' INTO TABLE 表名

[FIELDS [TERMINATED BY '字符']

                [[OPTIONALLY] ENCLOSED BY '字符']

               [ESCAPED BY '字符']

]

[LINES  [STARTING BY '字符串']

              [TERMINATED BY '字符串']

]

[IGNORE n LINES]

【说明 】

① FIELDS和LINES选项功能与“SELECT …INTO OUTFILE”语句中选项的功能相同。

② IGNORE n LINES：忽略文本文件中的前n条记录。

③ 使用“SELECT …INTO OUTFILE”语句将数据从一个数据库表导出到一个文本文件，再使用“LOAD DATA INFILE”语句从文本文件中将数据导入到数据库表时，两个命令的选项参数必须匹配，否则“LOAD DATA INFILE”语句无法解析文本文件的内容。

【例7-9】使用LOAD DATA INFILE语句将【例7-7】table_bak.txt文件中的数据导入到students_db数据库的student表。

#（1）先将student表中的数据全部删除。
USE students_db;
SET SQL_SAFE_UPDATES=0;
DELETE FROM student;

#（2）从table_bak.txt文件恢复数据。
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/table_bak.txt'
 INTO TABLE students_db.student
 FIELDS TERMINATED BY '|'  OPTIONALLY ENCLOSED BY '"'
 LINES  TERMINATED BY '\r\n';

4. 使用mysqlimport命令导入表数据

mysqlimport -u root -p 数据库名 文本文件名.txt

[--fields-terminated-by=字符]

[--fields-enclose-by=字符]

[--fields-optionally-enclosed-by=字符]

[--fields-escaped-by=字符]

[--lines-terminated-by=字符串]

[--ignore-lines=n]

      【说明】--ignore-lines=n：忽略文本文件的前n行。

【例7-10】使用mysqlimport命令将【例7-8】sc.txt文件中的数据导入到students_db数据库的sc表。

USE students_db;
SET SQL_SAFE_UPDATES=0;
DELETE FROM sc;

四、二进制日志恢复数据库的综合实例

① 完全备份数据库。

使用mysqldump命令备份所有数据库。

② 在MySQL服务器上执行如下语句。

USE students_db;

SET SQL_SAFE_UPDATES=0;

DELETE FROM sc;

③ 增量备份。

    完全备份整个服务器的数据库后，使用mysqladmin进行增量备份。

④ 恢复fullbackup.sql文件的完全备份。

⑤ 恢复PC-20170706QEJD-bin.000004的增量备份。