mysql import and export the entire database as a file
Release: 2016-07-25 09:09:52
Original
926 people have browsed it
mysql import and export the entire database as a file
- //When using it, be sure to pay attention to whether it is in the import state. If it is an import operation, you must back up the database first, otherwise the data will be cleared. .
- /*
- Export
- SELECT * INTO OUTFILE 'c:/name.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n'
- FROM zones;
- Import
- LOAD DATA INFILE ' c:/name.txt' INTO TABLE zones
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n';
- */
- set_time_limit(0);
- define(HOST,'127.0.0.1' );
- define(USER,'root');
- define(PWD,'');
- define(DBNAME,'test');
- define(DIR,'E:/www/mysql_data/');
- // 0 is import, 1 is export, 2 is repair
- define(OPERATION,1);
- mysql_connect(HOST,USER,PWD) or
- die("Could not connect: " . mysql_error());
- mysql_select_db(DBNAME) or
- die("Could not select db: " . mysql_error());
- $result = mysql_query("show tables");
- if(!is_dir(DIR)){
- die('Folder does not exist');
- }
- if(OPERATION==0){
- //Prerequisite: table structure needs to be imported previously
- //Export command: mysqldump -u root -p -d test>test.sql
- while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
- $fileName=DIR.$row[0].".txt";
- if(file_exists($fileName)){
- //delete truncate
- $querySql='TRUNCATE TABLE `'.$row[0 ].''';
- mysql_query($querySql) or
- die("Could not truncate table: ". $querySql . mysql_error());
-
- $querySql = "LOAD DATA INFILE '".$fileName."'
- INTO TABLE `".$row[0]."`
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n'";
- mysql_query($querySql) or
- die("Could not insert into table: ". $querySql . mysql_error());
- echo 'insert into table '.$row[0].' success.
';
- }else{
- echo 'Not insert into table '. $row[0].'
';
- }
- }
- echo "The task is finished";
- }else if(OPERATION==1){
- if (is_writable(DIR)) {
- while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
- $fileName=DIR.$row[0].".txt";
- if(file_exists($fileName)){
- unlink($fileName);
- }
- $querySql = "SELECT * INTO OUTFILE '".$fileName."'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''
- LINES TERMINATED BY 'n'
- FROM `".$row[0]."`" ;
- mysql_query($querySql) or
- die("Could not dump table: ". $querySql . mysql_error());
- echo 'dump table '.$row[0].' success.
' ;
- }
- echo "The task is finished";
- }else{
- echo 'Folder '.DIR.' can not writeable';
- }
- }else if(OPERATION==2){
- while ($row = mysql_fetch_array ($result, MYSQL_NUM)) {
- $fileName=DIR.$row[0].".txt";
- if(file_exists($fileName)){
- unlink($fileName);
- }
- $querySql = "REPAIR TABLE `$row[0]`";
- mysql_query($querySql) or
- die("Could not repair table: ". $querySql . mysql_error());
- echo 'repair table '.$row[0].' success.
';
- }
- }
-
- mysql_free_result($result);
- mysql_close();
- ?>
Copy code
|
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
-
2024-10-22 09:46:29
-
2024-10-13 13:53:41
-
2024-10-12 12:15:51
-
2024-10-11 22:47:31
-
2024-10-11 19:36:51
-
2024-10-11 15:50:41
-
2024-10-11 15:07:41
-
2024-10-11 14:21:21
-
2024-10-11 12:59:11
-
2024-10-11 12:17:31