备份恢复MySQL数据库的命令

1. 备份与恢复  
2. 备份：  
3. 1、系统运行中，增量备份与整体备份  
4. 例：每周日整体备份一次，周一到周六备份当天  
5. 如果周五出了问题可以用周日的整体备份加周一、二、三、四来恢复  
6. 2、备份工具有第三方收费备份工具，系统也有自带的备份功能mysqldump  
7. mysqldump可以导出表和库  
8. 1）、导出指定表格式：mysqldump -u用户名 -p 库名 表1 表2 表3 >保存地址\\保存备份文件名  
9. 例一、导出mugua库下的account表  
10. mysqldump -uroot -p mugua account >D:\\account.sql  
11. 注：语句结尾不用加分号，用反斜杠需要再加一个用来转义。  
12. 2）、导出库下的所有表格式：mysqldump -u用户名 -p 库名 >保存地址\\保存备份文件名  
13. 3）、导出库格式：mysqldump -u用户名 -p -B 库名1 库名2 库名3 >保存地址\\保存备份文件名  
14. 注：不加-B会把库2和库3当初表名，加了-B后就表示以库为单位备份  
15. 4）、导出所有库格式：mysqldump -u用户名 -p -A >保存地址/保存备份文件名  
16. 恢复：  
17. a、登录到mysql情况下：  
18. 语句格式：source 保存地址/保存备份文件名;  
19. 对于表级备份文件需要在指定的库下才能恢复  
20. b、不登录mysql情况下恢复库、表：  
21. 1、恢复库：mysql -uroot -p <保存地址/保存备份文件名;  
22. 2、恢复库下的表：mysql -uroot -p 库名 <保存地址/保存备份文件名;  
23.   
24.   
25. 备份  
26. 1、mysqldump备份  
27. 1）、备份多个数据库，如：  
28. mysqldump -u username -p --databases dbname1 dbname2...>backupName.sql;//要加--databases选项  
29. 2）、备份所有数据库  
30. mysqldump -u username -p --all-databases > backupName.sql;  
31. 2、mysqlhostcopy工具快速备份--直接复制整个数据库目录  
32. 注：该工具是perl脚本，主要在linux系统下使用。该工具原理是将数据库文件拷贝到目标目录。因此只能备份MyISAM类型的表，不能用来备份InnoDB类型的表。该工具使用lock tables、flush tables和cp进行快速备份。先将需要备份的数据库加上一个读操作锁，如拿回用flush tables将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。   
33.   backipDir参数指出备份到哪个文件夹下。选项：--help：用来查看帮助 --allowold：如果备份目录下存在相同的备份文件，将旧的备份文件名加上_old；--keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧文件更名；--flushlog：本次备份之后，将对数据库的更新记录到日志中；--noindices：只备份数据文件，不备份索引文件；--user=用户名：用来指定用户名，可以用-u代替；--password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p紧挨着。或者只用-p，然后交换方式输入密码。--port=端口号：用来指定访问端口，可以用-P代替；--socket=socket文件：用来指定socket文件，可以用-S代替。  
34. 如：[root@localhost~]#mysqlhostcopy [option] dbname1 dbname2 ... backupDir/  
35.   
36. 数据还原  
37. 直接复制到数据库目录  
38. 1）、可以直接复制到mysql数据库目录下。需要mysql数据库主版本号相同。而且对MyISAM类型的表比较有效。  
39. winsows下数据库目录通常放在C:\mysql\date、C:\documents and settings\all users\application data\mysql\mysql server5.1\data或c:\program files\mysql\mysql server 5.1\data。  
40. linux下通常在/var/lib/mysql、/usr/local/mysql/data或/usr/local/mysql/var。具体位置根据安装时设置位置而定。  
41. 2）、mysqlhostcopy命令也是通过这种方式还原的。在linux下，复制到数据库目录后一定要将数据库的用户和组变成mysql。如：chown -R mysql.mysql dataDir 注：两个mysql分别表示组和用户；"-R"参数可以改变文件夹下的所有子文件的用户和组；"dataDir"参数表示数据库目录。mysql数据库只有root用户和mysql用户组下的mysql用户可以访问。因此，将数据库目录复制到指定文件夹后，一定要用chown命令将文件夹用户组变为mysql，将用户变为mysql。  
42.   
43.   
44. 数据库迁移  
45. 1、相同版本mysql数据库间迁移  
46. 备份和迁移可以同时进行，如host1机器备份出所有数据库迁移到host2机器上。如：  
47. mysqldump -h name1 -u root --password=password1 --all-databases | mysql -h host2 -u root --password=password2  
48. 其中‘|’符号表示管道，作用是将mysqldump备份的文件送给mysql命令；"--password=password1"是name1主机上root用户的密码。同理，password2是name2主机上的root用户的密码。通过这种方式可以直接实现迁移。  
49. 2、不同版本的mysql数据库间迁移也可用mysqldump  
50. 3、不同数据库间迁移  
51. 通常可以用MyODBC来实现Mysql与sql server间迁移。mysql迁移到oracle时，需要用mysqldump导出sql文件，然后手动更改sql文件中的create语句。详情可以看tech.ccidnet.com/zt/qianyi/  
52.   
53. 表的导出导入  
54. mysql中可以将表导出成文本文件、xml文件或html文件。相应文本文件也可以导入mysql中。  
55. 1、用select...into outfile导出文本文件  
56. 如：select [列名] from table [where 语句] into outfile '目标文件'[option];  
57. 注：a、fields terminated by '字符串'：设置字符串为字段的分隔符，默认值是"\t";  
58. b、fields enclosed by '字符'：设置字符来括上字段的值。默认情况下不使用任何符号；  
59. c、fields optionally enclosed by '字符'：设置字符来括上char、varchar和text等字符型字段。默认情况下不使用任何符号；  
60. d、fields escaped by '字符'：设置转义字符，默认值为“\”;  
61. e、lines starting by '字符串'：设置每行开头的字符，默认情况下无任何字符；  
62. f、lines terminated by '字符串'：设置每行的结束符，默认值是"\n"。  
63. 如：select * from test.student into oufile 'C:/student1.txt' fields terminated by '\、' optionally enclosed by '\"' lines starting by '\>' terminated by '\r\n';student1.txt中内容如下：  
64. >901、"张老大"、"男"、1985、"计算机系"、"北京市"注：windows下"\r\n"才是回车换行。  
65. 2、用mysqldump命令导出文本文件  
66. 格式：mysqldump -uroot -ppassword -T 目标目录 dbname table [option];  
67. 注：option表示附件选项：这些选项必须用双引号括起来  
68. --fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是"\t";  
69. --fields-enclosed-by=字符：设置字符来括上字段的值；  
70. --fields-optionally-enclosed-by=字符：设置字符括上char、varchar和text等字符型字段；  
71. --fields-escaped-by=字符：设置转义字符；  
72. --lines-terminated-by=字符串：设置每行的结束符。  
73. 如：mysqldump -uroot -phuang -T C:/test student "--fields-terminated-by=," "--fields-optionally-enclosed-by=""  
74. student.txt中的内容如：  
75. 901,"张老大","男",1985,"计算机系","北京市"  
76. 3、导出xml格式文件：mysqldump -uroot -ppassword [--xml或 -X] dbname table > C:/name.xml;  
77. 注：--xml或-X选项可以导出xml格式文件；  
78. 4、用mysql命令导出文本文件  
79. 如：mysql -uroot -ppassword -e "select 语句" dbname > C:/name.txt;  
80. 如：mysql -uroot -ppassword [--xml|-X] -e "select 语句" dbname > C:/name.xml;//导出xml格式  
81. 如：mysql -uroot -ppassword [--html|-H] -e "select 语句" dbname > C:/name.html;//导出html文件  
82. 5、用load data infile方式导入文本文件  
83. LOAD DATA [LOCAL] INFILE file INTO TABLE table [OPTION];  
84. 其中，"LOCAL"是在本地计算机中查找文本文件时用的；"file"参数指定了文本文件的路径和名称；"table"参数指表的名称；"OPTION"参数是可以有常用的选项如：  
85. fields terminated by '字符串'：设置字符串为字段的分隔符，默认值是"\t";  
86. fields enclosed by '字符'：设置字符来括上字段的值。默认情况下不使用任何符号；  
87. fields optionally enclosed by '字符'：设置字符来括上char、varchar和text等字符型字段。默认情况下不使用任何符号；  
88. fields escaped by '字符':设置转义字符，默认值为"\";  
89. lines starting by '字符'：设置每行开头的字符，默认情况下无任何字符；  
90. lines terminated by '字符串'：设置每行的结束符，默认值是"\n"；  
91. ignore n lines：忽略文件的前n行记录；  
92. （字段列表）：根据字段列表中的字段和顺序来加载记录；  
93. set column=expr：将指定的列column进行相应地转换后再加载，使用expr表达式来进行转换。  
94. 例：LOAD DATA INFILE 'C:/student.txt' into table student FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "";  
95. 6、用mysqlimport命令导入文本文件  
96. 格式如：mysqlimport -u -ppassword [--local] dbname file [OPTION]  
97. 其中，"local"是在本地计算机中查找文本文件时用的；"file"参数指定了文本文件的路径和名称；如：  
98. --fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是"\t";  
99. --fields-enclosed-by=字符：设置字符来括上字段的值。  
100. --fields-optionally-enclosed-by=字符：设置字符括上char、varchar和text等字符型字段。  
101. --fields-escaped-by=字符:设置转义字符;  
102. --lines-terminated-by=字符串：设置每行的结束符；  
103. --ignore-lines=n：忽略文件的前n行；  
104. 如：mysqlimport -uroot -phuang test C:/student.txt "--fields-terminated-by=," "--fields-optionally-enclosed-by=""