php mysql dump: recreate db from dump file through php

[jonas-e]

I want to make a script that transfers my local database to my remote web host - I have come as far as making a dump file of my local db and upload it using ftp to the web host:

$d="C:/xampp/mysql/bin/mysqldump --skip-opt --insert-ignore --add-drop-table --host=*** --user=*** --password=*** db_name > dump.sql";
exec($d);
// set up basic connection
$ftp_server="ftp.***.no";
$conn_id = ftp_connect($ftp_server);
// login with username and password
$ftp_user_name="***";
$login_result = ftp_login($conn_id, $ftp_user_name, '***');
// turn passive mode on
ftp_pasv($conn_id, true);
// check connection
if ((!$conn_id) || (!$login_result)) {
        echo "FTP connection has failed!<br />";
        echo "Attempted to connect to $ftp_server for user $ftp_user_name<br />";
        exit;
    } else {
        echo "Connected to $ftp_server, for user $ftp_user_name<br />";
    }
$destination_file="dump.sql";
$source_file="dump.sql";
// upload the file
$upload = ftp_put($conn_id, $destination_file, $source_file, FTP_BINARY);
// check upload status
if (!$upload) {
        echo "FTP upload has failed!<br />";
    } else {
        echo "Uploaded $source_file to $ftp_server as $destination_file<br />";
    }
// close the FTP stream
ftp_close($conn_id); 

That works great!
Then I try to execute the dump file on the remote web host like this:

$myFile = "dump.sql";
$fh = fopen($myFile, 'r');
$theData = fread($fh, filesize($myFile));
fclose($fh);
if (!mysql_query($theData)) {
    echo mysql_error();
} else {
    echo "SUCCES!<br />";
} 

And I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101' at line 7

If I paste the dump.sql file (located here http://skred-svalbard.no/dump.sql) into a mysql command or phpadmin - it works just fine! But when I pass it through the mysql_query, I get error messages.

Can anyone see what the error is?
Am I perhaps just using an entirely wrong php command "mysql_query"?

cheers, j

[jonas-e]

Someone suggested to explode() the dump text by ";" into an array and run each command separately with mysql_query(). That nearly works, except that ";" also occurs in the database entries, e.g.:

INSERT IGNORE INTO 'table' VALUES (1,2,'this is a text; it has a semi-colon in it',3,4);

I found a work-around which works:

$file = "dump.sql";
$fh = fopen($file, 'r');
$text = fread($fh, filesize($file));
fclose($fh);

// insert seperator before each command
$sep=" |SEP| ";
$text=ereg_replace("DROP"," $sep DROP",$text); 
$text=ereg_replace("INSERT"," $sep INSERT",$text); 
$text=ereg_replace("CREATE"," $sep CREATE",$text); 
$text=ereg_replace("--"," $sep --",$text); 
$text = eregi_replace(
    '/\*([^\\[]+)\*/',
    '', $text); // remove what is btw /* and */

// create array from seperator and run
// queries one-by-one
$sqls=explode(' |SEP| ',$text);
foreach ($sqls as $sql) {
    echo '<div style=\'border:solid 1px grey;margin:0 auto 8px auto;width:500px;\'><p>'
        .$sql.'</p>';
    if (!mysql_query($sql)) {
        echo '<p style=\'color:red;\'>ERROR: '.mysql_error().'</p>';
    } else {echo "<p style='color:red;'>SUCCES!</p>";
    }
    echo "</div>";
} 

- as long as someone doesn't write content in the database with the words INSERT, CREATE or DROP in capital letters - or content which is /*whithin these things*/. So it's not entirely fool-proof.

There must be some way of running the full dump in one go ...

[dazines]

All you have to do is remove the comments before you do the replacements.

Try this:

$matches = array();
$pattern = "(/\*[\w|\W][^\*/]*\*/)"; //Get Rid of comment lines
$text= preg_replace($pattern,'',$text);
$sep=" |SEP| ";
$text =ereg_replace("DROP"," $sep DROP",$text);
$text =ereg_replace("INSERT"," $sep INSERT",$text);
$text =ereg_replace("CREATE"," $sep CREATE",$text);
$text =ereg_replace("--"," $sep --",$text);
$sqls = explode($sep,$text);

[jonas-e]

Thanks!
Amazing that this thread is still alive!

[silverfh]

Guys.. I think better try this regX

(#[^\r\n]+|/\*.*?\*/|//[^\r\n]+)

so to remove all the multi-line and single-line comment

e.g.
/* some
comments please *.
# phpMyadmin use this
// and this is another way of comment

[silverfh]

infect.. this is what I did..

// remove comments
$pattern = "%(#[^\r\n]+|/\*.*?\*/|//[^\r\n]+)%sim";
$query = preg_replace($pattern, '', $query);

// break on ; at the end of line
preg_match_all('%(.*?);$%sim', $query, $result, PREG_PATTERN_ORDER);

in some cases this might not work .. try this
preg_match_all('%(.*?);%sim', $query, $result, PREG_PATTERN_ORDER);


pure regX solution..
hope this might help someone

[jonas-e]

Thanks for you help! I do not work on that application anymore, though - it was in my previous job which I had until Dec 2007.

Surprisingly, the application is still alive and kicking:
http://skred-svalbard.no/