Backup MySQL DB using PHP script

Hi there,

I like to back up my databases using a php script.

I like to allow the admin of the site to first select the database to be backed up. In doing this, am stuck with pulling off the list of databases from the server first, select then proceed to back it up.

My snippet is below;


<?php

include ('../includes/header_admin.html');

require_once ('../includes/mysqli_connect.php'); // Connect to the db.

echo '<table><tr><td><img src="../imgs/dbbkp.png" border="0" width="25" height="25" alt="Backup database" /></td>
    <td><h4>Backup Database</h4></td></tr>
</table>';

//To use mysqldump() for backing up selected database
//$backupFile = DB_NAME . date("Y-m-d-H-i-s") . '.gz';
//$command = "mysqldump --opt -h DB_HOST -u DB_USER -p DB_PASSWORD DB_NAME | gzip > $backupFile";
//system($command);

$dbname = null;

if(isset($_GET["dbsource"]) && is_numeric($_GET["dbsource"]))
{
    $dbname = $_GET["dbsource"];
}
?>
<script language="JavaScript">

function autoSubmit()
{
    var formObject = document.forms['selDb'];
    formObject.submit();
}

</script>

<form name="selDb" method="GET">
    
Select Database to Backup from List: <select name="dbsource" onChange="autoSubmit();">        
               
        <?php
        $sql = "SHOW DATABASES;";
        $dbname = mysql_query($sql, $dbc);
        
        while($row = mysql_fetch_array($dbname))
        {        
            echo ("<option value=\\"$row[$dbname]\\" " . ($dbname == $row["$dbname"] ? " selected" : "") . ">$row[$dbname]</option>");        
        }
        
        ?>
</select>
<br />
</form>
<button onclick=" ">Backup Selected Database</button>
</form>

Code atleast runs but I get an empty dropdown.

Also I don’t know how to integrate the code area that backs up the database itself when the button is clicked.

Any insight is appreciated!

First of all remove that semicolon at the end of the query string - I don’t think MySQL will handle that nicely.

If that doesn’t work, you need to make sure you HAVE databases in there.

Run this outside of the select:

$Q = mysql_query("SHOW DATABASES");
echo '<p>';
if($Q === false){
    echo 'Query Error: ' . mysql_error();
}else{
    echo mysql_num_rows($Q) . ' Databases found';
}
echo '</p>';

What is the output?

Thanks Jake for reply…

Semicolon removed but problem remains…

I ran the suggested query outside the select and number of databases were returned…

New snippet below:

$dbname = null;

if(isset($_GET["dbsource"]) && is_numeric($_GET["dbsource"]))
{
    $dbname = $_GET["dbsource"];
}
?>
<script language="JavaScript">

function autoSubmit()
{
    var formObject = document.forms['selDb'];
    formObject.submit();
}

</script>

<?php
//Checks if any database actually exists on the server
$Q = mysql_query("SHOW DATABASES");
echo '<p>';
if ($Q === false) {
    echo 'Query Error: ' . mysql_error();
} else {
    echo mysql_num_rows($Q) . ' Databases found';
}
echo '</p>';
?>

<form name="selDb" method="GET">
    
Select Database to Backup from List: <select name="dbsource" onChange="autoSubmit();">        
               
        <?php
        $sql = "SHOW DATABASES";
        $dbname = mysql_query($sql, $dbc);
        
        while($row = mysql_fetch_array($dbname))
        {        
            echo ("<option value=\\"$row[$dbname]\\" " . ($dbname == $row["$dbname"] ? " selected" : "") . ">$row[$dbname]</option>");        
        }
        
        ?>
        
    </select><br />
</form>
<button onclick="">Backup Selected Database</button>

Challenge is now displaying the name of the actual databases in the select then allowing user to backup when button is clicked.

Regards…

This post (http://www.sitepoint.com/forums/4819220-post13.html) has a code snippet which will give you a list of the names of all the databases on the server. Note that the “information_schema” and “mysql” are MySQL’s “internal system” databases which you shouldn’t touch in any way as you risk breaking MySQL by making any changes to them two databases.

If your looking to see if you have any databases of your own on the server then you should check to see if more then two databases are found.

Thanks SpacePhonix…

Code displays database list.

How can I select a database (not system ones) for backup then?

Regards,

When looping through the list of database names you could use an “if-break” structure.


<?php
if ( $db_name === 'information_schema' || $db_name === 'mysql' ) {
break;
}
?>

The "if-break"s would be used in the line right before the line which echos the database name. Change the $db_name to the appropriate varaible name for your script.