Advanced Search

Results 1 to 7 of 7

Thread: php mysql dump: recreate db from dump file through php

  1. #1
    Join Date
    Feb 2007
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default php mysql dump: recreate db from dump file through php

    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:
    PHP Code:
    $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_idtrue);
    // 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_fileFTP_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:
    PHP Code:
    $myFile "dump.sql";
    $fh fopen($myFile'r');
    $theData fread($fhfilesize($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

  2. #2
    Join Date
    Feb 2007
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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:
    PHP Code:
    $file "dump.sql";
    $fh fopen($file'r');
    $text fread($fhfilesize($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 ...

  3. #3
    Join Date
    Dec 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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);

  4. #4
    Join Date
    Feb 2007
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Thanks!
    Amazing that this thread is still alive!

  5. #5
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default regX improvement

    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

  6. #6
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default anther update.. :) pure regX

    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

  7. #7
    Join Date
    Feb 2007
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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/

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •