MySQL Dump/Back-up with PHP?

Discussion in 'Web Design & Coding' started by SPeedY_B, Oct 24, 2004.

  1. SPeedY_B

    SPeedY_B I may actually be insane.

    Messages:
    15,800
    Location:
    Midlands, England
    Topic says it all really, basically I want to back-up a MySQL table (the whole thing) to a standardised .tar.gz file which I can re-upload into PHPMyAdmin (or make my own form to do it :D).. and basically haven't a clue how to do so, any pointer in the right direction appreciated :D
     
  2. Kush

    Kush High On Life!

    Messages:
    4,590
    Location:
    Montreal, Quebec
    ok that was in chinese to me, btw thats like the second time i see speedy ask for help ;) wow long time since u did! go speedy go speeedy!
     
  3. SPeedY_B

    SPeedY_B I may actually be insane.

    Messages:
    15,800
    Location:
    Midlands, England
    hehe :p

    I know of mysqldump from the command line and that I could just shell_exec(); it, I just wondered if there was a method from within PHP to do it more 'natively' so to speak.
     
  4. Kush

    Kush High On Life!

    Messages:
    4,590
    Location:
    Montreal, Quebec
    sure do whatever u want i trust u :p
     
  5. Glaanieboy

    Glaanieboy Moderator

    Messages:
    2,626
    Location:
    The Netherlands
    I have built a very simple php mysqldump thingy for a website of a friend of mine. 'll see if I can dig it up and post it up here :)
     
  6. Glaanieboy

    Glaanieboy Moderator

    Messages:
    2,626
    Location:
    The Netherlands
    It's been a while since I played with this, so you have to look for yourself how this works. Also, I didn't create the functions myself, I modified them a bit to fit my needs, but I forgot which website I got it from (badly documented :eek:).

    Code:
    <?php
    include 'sql.php';
        define("dbserver", $server);
        define("dbuser", $user);
        define("dbpass", $pass);
        define("dbname", $db);
    
    function get_structure()
    {
        mysql_connect(dbserver, dbuser, dbpass);
        mysql_select_db(dbname);
        $tables = mysql_list_tables(dbname);
        while ($td = mysql_fetch_array($tables))
        {
            $table =  $td[0];
            $r = mysql_query("SHOW CREATE TABLE `$table`");
            if ($r)
            {
                $insert_sql = "";
                $d = mysql_fetch_array($r);
                $d[1] .= ";";
                $SQL[] = str_replace("\n", "", $d[1]);
                $table_query = mysql_query("SELECT * FROM `$table`");
                $num_fields = mysql_num_fields($table_query);
                while ($fetch_row = mysql_fetch_array($table_query)){
                  $insert_sql .= "INSERT INTO $table VALUES(";
                  for ($n=1;$n<=$num_fields;$n++){
                    $m = $n - 1;
                    $insert_sql .= "'".$fetch_row[$m]."', ";
                  }
                  $insert_sql = substr($insert_sql,0,-2);
                  $insert_sql .= ");\n";
                }
                if ($insert_sql != ""){
                  $SQL[] = $insert_sql;
                }
            }
        }
        return $SQL;
    }
    
    function save_structure($filename)
    {
        $sql = get_structure();
        header('Content-type: plain/text');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        $sql = implode("\r", $sql);
        print $sql;
        exit;
    }
    
    function create_structure ($filename)
    {
        mysql_connect(dbserver, dbuser, dbpass);
        mysql_select_db(dbname);
        $lines = explode("\n", file_get_contents($filename));
        $result = true;
        foreach ($lines as $line)
            mysql_query($line) OR DIE("Error while importing: ".mysql_error());
            //$result = $result && mysql_query($line);
        return $result;
    }
    if ($_GET['action'] == "export"){
      save_structure("backup.sql");
    }elseif ($_GET['action'] == "import"){
      create_structure($_FILES['filename']['tmp_name']);
      print "Import succeeded!<br>";
    }
    ?>
    
    <script type="text/javascript">
    function checkForm(){
      if (document.backupform.filename.value == ""){
        alert('Please select a file first');
        return false;
      }else{
        return true;
      }
    }
    function toggle_display(idname){
      obj = fetch_object(idname);
      if (obj){
        if (obj.style.display == "none"){
          obj.style.display = "";
        }else{
          obj.style.display = "none";
        }
      }
      return false;
    }
    </script>
    <center>
    <div id="export_text" style="display : none;">Export Succeeded!</div>
    <form method="post" action="?action=import" enctype="multipart/form-data" onSubmit="return checkForm();" name="backupform">
     <input type="file" name="filename">
     <input type="submit" value="Import tables">
    </form>
    
    <input type="button" value="Backup tables" onClick="javascript:location.href='backup.php?action=export'"></center>