mysql以文件形式导入导出整个数据库

mysql以文件形式导入导出整个数据库

1. //使用时一定要注意是否是处于导入状态，如果是导入操作，一定要先备份数据库，否则数据会被清空。。
2. /*
3. 导出
4. SELECT * INTO OUTFILE 'c:/name.txt'
5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
6. LINES TERMINATED BY '\n'
7. FROM zones;
9. 导入
10. LOAD DATA INFILE 'c:/name.txt' INTO TABLE zones
11. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
12. LINES TERMINATED BY '\n';
13. */
14. set_time_limit(0);
15. define(HOST,'127.0.0.1');
16. define(USER,'root');
17. define(PWD,'');
18. define(DBNAME,'test');
19. define(DIR,'E:/www/mysql_data/');
20. //0为导入 1为导出 2为修复
21. define(OPERATION,1);
23. mysql_connect(HOST,USER,PWD) or
24. die("Could not connect: " . mysql_error());
25. mysql_select_db(DBNAME) or
26. die("Could not select db: " . mysql_error());
27. $result = mysql_query("show tables");
29. if(!is_dir(DIR)){
30. die('Folder does not exist');
31. }
32. if(OPERATION==0){
33. //前提：需要先前导入表结构
34. //导出命令：mysqldump -u root -p -d test>test.sql
35. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
36. $fileName=DIR.$row[0].".txt";
37. if(file_exists($fileName)){
38. //delete truncate
39. $querySql='TRUNCATE TABLE `'.$row[0].'`';
40. mysql_query($querySql) or
41. die("Could not truncate table: ". $querySql . mysql_error());
43. $querySql = "LOAD DATA INFILE '".$fileName."'
44. INTO TABLE `".$row[0]."`
45. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
46. LINES TERMINATED BY '\n'";
47. mysql_query($querySql) or
48. die("Could not insert into table: ". $querySql . mysql_error());
49. echo 'insert into table '.$row[0].' success.
    ';
50. }else{
51. echo 'Not insert into table '.$row[0].'
    ';
52. }
53. }
54. echo "The task is finished";
55. }else if(OPERATION==1){
56. if (is_writable(DIR)) {
57. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
58. $fileName=DIR.$row[0].".txt";
59. if(file_exists($fileName)){
60. unlink($fileName);
61. }
62. $querySql = "SELECT * INTO OUTFILE '".$fileName."'
63. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
64. LINES TERMINATED BY '\n'
65. FROM `".$row[0]."`";
66. mysql_query($querySql) or
67. die("Could not dump table: ". $querySql . mysql_error());
68. echo 'dump table '.$row[0].' success.
    ';
69. }
70. echo "The task is finished";
71. }else{
72. echo 'Folder '.DIR.' can not writeable';
73. }
74. }else if(OPERATION==2){
75. while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
76. $fileName=DIR.$row[0].".txt";
77. if(file_exists($fileName)){
78. unlink($fileName);
79. }
80. $querySql = "REPAIR TABLE `$row[0]`";
81. mysql_query($querySql) or
82. die("Could not repair table: ". $querySql . mysql_error());
83. echo 'repair table '.$row[0].' success.
    ';
84. }
85. }
87. mysql_free_result($result);
88. mysql_close();
89. ?>

复制代码