Backing up MySQL database using PHP

[trassalg]

Is there a simple script I can use to retrieve a copy of a full MySQL database and download it to the user's computer via a single pushbutton?

[ss32]

no, but you could use a series of DESCRIBEs to get the data you would need, and from that use PHP to regenerate a query of all the data within the table.  it sounds difficult...  and would probably involve some string parsing, but in reality its not really that complicated.  just a bunch of nested foreach loops.

 

$sql_show_tables = "show tables";

foreach ($sql_show_tables result as table) {

  $sql_table_props = "describe " . table;

  foreach($sql_table_props result as table values) {

    //build a query that selects all of the data from the database ($sql_get_table_data)

    //build a table creation query

  }

 

  foreach($sql_get_table_data result as res) {

    //echo an insert query for every result

  }

}

[Barand]

Is there a simple script I can use to retrieve a copy of a full MySQL database and download it to the user's computer via a single pushbutton?

 

here's one I wrote earlier.

 

query string params:

  db=dbname (default is 'test')

    t=tablename (default is all tables in db)

    r=n (default 0; n < total in table -> n random records;  n >= total -> all records.

 

So all tables, all records

 

tables.php?db=dbname&r=2000000

<?php
include 'db.php';        // connect stuff

function showtable($table, $data=0) {
         #
         # if $data is non-zero, list that many random records
         #
    $rc = mysql_result(mysql_query("SELECT COUNT(*) FROM $table"),0);
    $sqlDesc = "DESCRIBE $table ";
    $result = mysql_query($sqlDesc);
    echo "<p style='border: 1pt solid silver; padding: 8px'>";
    echo "<b>$table</b> ($rc records)</p>";
    echo "<pre>\n";
    echo "CREATE TABLE $table (";
    $first = true;
    $pk = array();
    #
    # $f = fieldname, $t = type, $n = NULL,
    # $k = key, $d = default, $e = extra
    #
    while (list($f, $t, $n, $k, $d, $e) = mysql_fetch_row($result)) {
           if (!$first)
                echo ",\n";
           else
               echo "\n";
           echo "\t`$f` $t " . ($n=='YES'? '': ' NOT NULL ') .
           ($d ? "DEFAULT '$d'":"") . $e;
           $first = false;
           if ($k=='PRI') $pk[] = $f;
    }

    if (count($pk)>0) echo ",\n\tPRIMARY KEY(" . join(',',$pk) . ")";
    echo "\n);\n";
    mysql_free_result($result);

    if ($data) {
        $sql = "SELECT * FROM $table";
        if ($rc > $data)
            $sql .= " ORDER BY RAND() LIMIT $data";
        $res = mysql_query($sql);

        while ($row = mysql_fetch_row($res)) {
               foreach($row as $k=>$v) {
                       $row[$k] = addslashes($v);
               }
               echo "\n" . wordwrap("INSERT INTO $table VALUES ('" . join("', '", $row) . "');", 100);
        }
        echo "\n";
        mysql_free_result($res);
    }
    echo "</pre>\n";
}

# Call for whole DB, called 'test' in this example
$recs = 0;
$db = 'test';
if (isset($_GET['r'])) $recs = $_GET['r'];

if (isset($_GET['db'])) {
    $db = $_GET['db'];
    mysql_select_db($db);
}
if (isset($_GET['t'])) {
           showtable ($_GET['t'], $recs);
}
else {
    $res = mysql_list_tables($db);
    while ($row = mysql_fetch_row($res)) {
           showtable ($row[0],$recs);
    }

}
?>

[pnj]

How about

 

system ("mysqldump ...");

 

or would that be considered bad practice?

 

-pnj

[Barand]

Far too easy

[trassalg]

I'm not going to be the one using the site.  It's a database for one user, and he'll be doing the backups himself.  THe problem is he has no knowledge whatsoever beyond how to use a web browser, so I want to make it easy enough for him to point and click.  Which of these options in this thread is the simplest and most foolproof?

[ss32]

as long as all you have to do is press a button, it is pretty much fool proof.

unless of course you give him two buttons... then he may get confused.

[Barand]

Best way is to automate the backups on a daily basis.

 

If you download Mysql Administrator from mysql.com it will enable you to define regular timed backups