mysql备份与恢复test_MySQL数据备份与恢复

1.数据库备份与恢复的概述

数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。

数据库恢复则是当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。数据库的恢复是以备份为基础的，它是与备份相对应的系统维护和管理操作。

注意；使用 show variables like '%secure%'; 查看备份路径，如果secure_file_priv 值为空，需要在my.ini文件中配置路径

修改mysql的配置文件：

C:\Program Files\MySQL\MySQL Server 5.5找到my.ini

在文档结尾加上：

secure_file_priv="F:/sql"

其中：F:/sql为导入导出sql数据的指定目录，根据自己的需求设置；

【应会部分】：

1.使用mysql语句备份和恢复表数据

导出备份语句

语法：SELECT  *  INTO ...OUTFILE‘文件名’【CHARACTER SET charset_name】export_option| INTO  DUMPFILE‘文件名’

其中：export_option格式为：

[FIELDS

[TERMINATED BY ‘string’]

[[OPTIONALLY]ENCLOSED BY ‘char’]

[ESCAPED BY ‘char’]

]

[LINES  TERMINATED BY ‘string’]

该语句的作用是将表中select语句选中的所有行写入到一个文件中,文件名默认在服务器主机上创建，并且文件名不能是已存在的，否则会报错文件已存在。如果要将文件写入到一个特定的位置，则在文件前加上特定的位置。

TERMINATED BY ‘string’：用来指定两个字段之间的标志

OPTIONALLY]ENCLOSED BY ‘char’：指定包裹文件中字符值的符号，加上optionally表示所有的值都放在双引号之间

ESCAPED BY ‘char’：用来指定转义字符

LINES  TERMINATED BY ‘string’：指定一个数据行结束的标志

INTO DUMPFILE:表示导出文件，文件中的内容之间没有任何标记

例题：

备份数据库db_school中表tb_student的全部数据到D盘下的test目录下的t1.txt文件中，要求字段值如果是字符用双引号标注，字段值之间用逗号隔开，每行以问号作为结束标志

Select * from db_school.tb_student into outfile ‘D:/test/t1.txt’

Fields terminatedby‘,’

Optionally enclosedby‘”’

Lines terminatedby ‘?’;

2.LOAD DATA...INFILE

其语法格式为：

LOAD DATA [LOW_PRIORITY|CONCURRENT][LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY 'string]

[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY ‘char’ ]]

[LINES [TERMINATED BY 'string']

[STARTING BY ‘string’]]

[IGNORE number LINES]

[(col_name_or_user_var,...)]

[set col_name =expr,...]

语法说明：

LOW_PRIORITY|CONCURRENT:若指定LOW_PRIORITY，则延迟该语句的执行，若指定CONCURRENT，则当load

data正在执行的时候其他线程可以同时使用该表的数据。

LOCAL:若指定了，则文件会被客户主机上的客户端读取，并被发送到服务器上。

REPLACE | IGNORE：如果指定了REPLACE，则当导入的文件中出现与数据库中原有行相同的唯一关键字值时，输入行会替换原有行，如果指定INGNORE，则把与原有行有相同的唯一关键字值的输入行跳过。

STARTING BY ‘string’：指定一个前缀，导入数据行时，忽略数据行中的该前缀和前缀之前的内容。

IGNORE number LINES：忽略文件的前几行。

col_name_or_user_var：需要载入一个表的部分列，或者文件中字段值顺序与原表中列的顺序不同时，就必须指定一个列清单，其中可以包含列名或用户变量。

set col_name =expr：可以在导入数据时修改表中列的值。

例题：将备份数据库导入数据库db_school中一个和tb_student表结构相同的空表tb_copy中。

Load data infile ‘‘D:/test/t1.txt’ into tabledb_school.tb_copy

Fields terminatedby‘,’

Optionally enclosedby‘”’

Lines terminatedby ‘?’;

3、使用mysqldump程序备份数据

3.1备份数据表

Mysqldump [option] database [tables] >filename

例如：使用mysqldump备份数据库db_school中的表tb_student。

Mysqldump -h localhost -u root -p123 db_school tb_student >d:\test\t.sql

3.2备份数据库

Mysqldump [option] --databases [options] DBI [DB2 DB3...]>filename

例如：备份数据库db_school到d盘test目录下

Mysqldump -u root -p123 --databases db_school >d:\test\data.sql;

3.3备份整个数据库系统

Mysqldump [option] --all -databases [options] > filename

例如：备份mysql服务器上的所有数据库

Mysqldump -u root -p123 --all-databases >d:\test\t1.txt

4、使用mysql命令恢复数据

例如：假如数据库db_school遭遇破坏，试用该数据库的备份文件data.sql将其恢复

Mysql -u root -p123 db_school

5、使用mysqlimpor程序恢复数据

Mysqllimport [option] database textfile

option常用选项：-d、--delete:导入文本之前清空表中所有数据行

-l、--lock-tables:在处理任何文本前锁定多有的表，以保证在所有的表都在服务器上同步，但对于innodb类型的表则不必进行锁定

--low-priority、--local、--replace、--ignore:分别对应load data...infile 语句中的low_priority、local、replace、ignore

Textfile:存储备份文件的文件名

例如：使用备份数据文件t1.txt恢复数据库tb_student中的数据

Mysqlimport -u root -p123 --low-priority --replace db_school d:\test\tb_student.txt