mysql数据备份和还原

备份结构

1.备份表结构

mysqldump -u root -p -d dbname table1 table2 ... > a.sql

2.备份数据库的所有表结构

mysqldumo -u root -p -d dbname > b.sql

3.备份多个数据库的所有表结构

mysqldump -u root -p -d --databases db1 db2... > c.sql

4.备份所有数据库的表结构

mysqldump -u root -p -d --all-databases > d.sql

备份结构和数据(相当于在备份结构的语法上去掉-d选项)

1.备份表结构和数据

mysqldump -u root -p dbname table1 table2 ... > a.sql

2.备份数据库的所有表结构和数据

mysqldump -u root -p dbname  > b.sql

3.备份多个数据库的表结构和数据

mysqldump -u root -p --databases db1 db2  > c.sql

4.备份所有数据库的表结构和数据

mysqldump -u root -p --all-databases > d.sql

 备份表数据

select ... into outfile

select [列名] from table [where 语句] into outfile '目标文件' [options]

目标文件必须是字符串形式

options是设置导出文件的一些格式；

fields terminated by '字符串':多个字段值之间的分隔符，默认是'\t'

fields enclosed by '字符串':用给定的字符来包围字段值,默认不需要

fields optionally enclosed by '字符串':用给定的字符包围文本类型的字段值，默认不需要

fields escaped by '字符串':设置转义字符，默认为"\"

lines starting by '字符串':每条记录的开始字符,默认没有

lines terminated by '字符串'：每条记录的结束符，默认是换行符

 

select * from pmx.score2 where 1 into outfile '/Users/pmx/Sites/a.txt' fields terminated by '|' enclosed by '"' optionally enclosed by '@' lines starting by '^' terminated by '$' ;

结果:

　　^1|900|@计算机@|90$^2|901|@英语@|80$^3|902|@计算机@|70$^4|903|@中文@|30$

1.只需要一个fields关键字或者一个lines关键字

2.各选项间以空格分隔

3.重复选项以最后一个为准

select * from pmx.score2 where 1 into outfile '/Users/pmx/Sites/a.txt' fields terminated by 'o' terminated by 'p';

结果：

1p900p计算机p90
2p901p英语p80
3p902p计算机p70
4p903p中文p30

select * from pmx.score2 where 1 into outfile '/Users/pmx/Sites/a.txt' fields optionally enclosed by '|' enclosed by '"';

结果

1 900 "计算机" 90
2 901 "英语" 80
3 902 "计算机" 70
4 903 "中文" 30

相当于optionally enclosed by ' " ';

4.optionally enclosed by 会覆盖enclosed by

select * from pmx.score2 where 1 into outfile '/Users/pmx/Sites/a.txt' fields enclosed by '[' optionally enclosed by '|';

结果：

1 900 |计算机| 90
2 901 |英语| 80
3 902 |计算机| 70
4 903 |中文| 30

当enclosed by 后面又设置optionally enclosed by时，则前面的enclosed by 无效

5.fileds选项必须在lines前面

select * from pmx.score2 where 1 into outfile '/Users/pmx/Sites/a.txt' lines starting by '^' fields terminated by '|';

直接报错了

mysqldump命令

mysqldump -u root -p -T 目标目录 dbname tablename [options]

参数是目录路径不是文件路径，参数可以加引号也可以不加

options参数含义和select into outfile一致

 

mysqldump -u root -p -T /Users/pmx/Sites pmx score2;

mysqldump命令默认会以score2为文件名导出两份文件，一份是txt文件，保存表的数据，一份是sql文件，保存表的结构

 

mysqldump -u root -p -T /Users/pmx/Sites pmx score2  score;

mysqldump还能导出多个表内容，每个表内容分别保存在对应表名的文本文件中

 

mysqldump -u root -p -T /Users/pmx/Sites pmx;

没有表名，则默认会导出数据库的所有表

 

mysqldump -u root -p -T /Users/pmx/Sites  pmx score "--fields-terminated-by=|" "--fields-enclosed-by=\"" "--lines-terminated-by=$";

各选项以字符串形式表示，选项间以空格分隔。选项值是字符串类型，但不需要加引号。选项值是"时用转义符转义。

 

mysqldump -u root -p -T /Users/pmx/Sites  pmx score "--lines-terminated-by=$" "--fields-terminated-by=|";

lines选项可以在fields选项前面

 

mysqldump -u root -p -T /Users/pmx/Sites  pmx score "--fields-enclosed-by=\"" "--fields-optionally-enclosed-by=|";

--fields-enclosed-by和--fields-optionally-enclosed-by只能出现一个，否则报错

 

mysqldump -u root -p -T /Users/pmx/Sites  pmx score "--lines-starting-by=^";

 没有--lines-starting-by选项

 

mysqldump -u root -p --xml  pmx score > a.xml;

导出为xml文件，不能有options参数。

mysql命令

mysql -u root -p -e "selec 语句" dbname > 目标文件名

目标文件名可以加引号或者不加

当select语句中明确数据库时，dbname要省略

mysql命令中没有options参数

mysql -u root -p -e "select * from pmx.score" > 'a.txt'

结果为：

id stu_id c_name grade
1 900 计算机 90
2 901 英语 80
3 902 计算机 70
4 903 中文 30

导出文件中有字段名

 

mysql -u root -p -X  -e "select * from pmx.score" > a.xml

mysql命令可以导出为xml文件

 

mysql -u root -p -H  -e "select * from pmx.score" > a.html

mysql命令可以导出为html文件

还原表结构和数据

mysql -u root -p [dbname] < 目标文件

如果目标文件中有创建数据库的语句，则dbname省略

还原表数据

load data infile命令

load data [local] infile file into table table_name [options]

options选项和select into outfile选项规则一致，但是多出3个特殊选项

导入文件a.txt的内容

"1"|"900"|"计算机"|"90"
"2"|"901"|"英语"|"80"
"3"|"902"|"计算机"|"70"
"4"|"903"|"中文"|"30"

load data local infile '/Users/pmx/Sites/a.txt' into table pmx.score fields terminated by '|' enclosed by '"' ignore 1 lines (id,grade,c_name,stu_id) set grade = grade + 10; 

ignore n lines:忽略文件的前n行记录

(id,grade,c_name,stu_id):每条记录的每个字段值加载到设置的字段上，也就是原来900对应的是stu_id值，现在900被加载到grade字段上

set column = exp:在加载记录前，先将column列的值用exp表达式处理下

3个特殊选项的顺序是定死的

如果mysql导入的文件中，第一行不是真实数据，要加上ignore n lines忽略掉。

mysqlimport命令

mysqlimport -u root -p [--local] dbname file [option]

 

导入文件score.txt的内容：

select * from pmx.score2 into outfile '/Users/pmx/Sites/score.txt' fields terminated by '---' lines starting by '^' terminated by '$';

^1---900---计算机---90$^2---901---英语---80$^3---902---计算机---70$^4---903---中文---30$

 

mysqlimport -u root -p --local pmx '/Users/pmx/Sites/score.txt' "--fields-terminated-by=---" "--lines-terminated-by=$" "--ignore-lines=1";

mysqlimport命令将导入文件名作为表名，将数据导入到该表中。

mysqlimport命令和mysqldump一样没有lines starting by选项

这里每行开头的^没有被保存，因为id字段是整型，"^1"在保存之前将^忽略掉了

如果score.txt每行的起始符是一个数字

21---900---计算机---90$22---901---英语---80$23---902---计算机---70$24---903---中文---30$

那么在保存的时候"21"会被保存，然而实际应该保存的是"1"。所以要设置line starting by选项，但是mysqlimport是没有--line-starting-by选项的，所以这种形式的文件应该用load data infile命令保存

 

在load data infile命令中的选项options最多:

　　fields termindate by

　　fields [optionally] enclosed by

　　fields escaped by

　　lines starting by

　　lines terminated by

　　ignore n lines

　　(字段列表)

　　set column=exp

在书写时，必须按照fields>lines>ignore>(字段列表)>set column的优先级。每个选项的子选项间的优先级相同

以上的形式是在登录mysql后的书写形式。在没有登录进mysql,仅仅是在控制台的话，则应该以"--fields-terminated-by=|"形式书写。特别的，这种形式是没有"--lines-starting-by="选项的