php MySql database backups

safeastinkywinky

safeastinkywinky

safeastinkywinky

Associate
Joined
25 Aug 2004
Posts
163
Location
Reading
Anyone got any good php scripts, links, sites to backup and restore a Mysql Database through a web page?

I know I can do it easily through phpMyAdmin, but my brother just wants something he can do for his Tibia clan forums to save the information. He doesn't want to do anything other than click a button on his site to backup, and select from a list of backed up archives to restore.

Also, could this be automated through php to backup daily, or would there need to be some kind of Windows script? Running on Windows btw!

I know I can call mysqldump with my db credentials using the system command in php, but I can't seem to find or direct the location of the backup in order to search for the file to restore it. Hope this makes sense.
 
hacked this together for you.
put it in a directory, make a directory under that called 'backups', and give the webserver write permissions, and fill in the mysql info :)
simple and hacky, but it works (for me :))

oh btw, mysqldump writes to stdout, so you just redirect stdout to a file. the normal mysql shell command will parse it, if you redirect stdin from said file, as this script does.

PHP: 
<?php

$config = array();
$config['backups_directory'] = "backups";
$config['mysql_username'] = "YOURUSERNAME";
$config['mysql_password'] = "YOURPASSWORD";
$config['mysql_database_name'] = "YOURDBNAME";

if ($_REQUEST['command'] == 'make_backup') {
        $filename = 'mysql.' . date('Ymd-His') . '.sql';
        $command = 'mysqldump' .
                ' --quote-names --add-drop-table' .
                ' -u ' . $config['mysql_username'] .
                ' -p' . $config['mysql_password'] .
                ' --databases ' . $config['mysql_database_name'] .
                ' > ' . $config['backups_directory'] . '/' .
                $filename;
        $output = system($command, $result);
        if ($result == 0) {
                echo 'backup written sucessfully to : ' .
                        $config['backups_directory'] . '/' .
                        $filename . "<br />\n";
        } else {
                echo 'backup failed (' . $output . ')';
        }
}

if ($_REQUEST['command'] == 'restore') {
        $filename = $_REQUEST['filename'];
        $command = 'mysql' .
                ' -u ' . $config['mysql_username'] .
                ' -p' . $config['mysql_password'] .
                ' < ' . $config['backups_directory'] . '/' .
                $filename;
        $output = system($command, $result);
        if ($result == 0) {
                echo "restored sucessfully<br />\n";
        } else {
                echo 'restore failed [' . $command . '] (' . $output . ')';
        }
}

if ($_REQUEST['command'] == 'delete') {
        $filename = $_REQUEST['filename'];
        $result = unlink($config['backups_directory'] . '/' . $filename);
        if ($result) {
                echo "$filename deleted<br />\n";
        } else {
                echo "couldn't delete $filename<br />\n";
        }
}

if (is_dir($config['backups_directory'])) {
        $backup_dir = dir($config['backups_directory']);
        echo "backups available: ";
        $backup_files = array();
        while (false !== ($backup_file = $backup_dir->read())) {
                if ($backup_file != '.' && $backup_file != '..') {
                        array_push($backup_files, $backup_file);
                }
        }
        reset($backup_files);
        echo "<table>\n";
        while (list($key, $value) = each($backup_files)) {
?>
        <tr><td><?php echo $value; ?></td>
        <td>
        <form style="display:inline;">
        <input type="hidden" name="command" value="restore" />
        <input type="hidden" name="filename" value="<?php echo $value;?>"</input>
        <input type="submit" value="restore"?>
        </form>
        <form style="display:inline;">
        <input type="hidden" name="command" value="delete" />
        <input type="hidden" name="filename" value="<?php echo $value;?>"</input>
        <input type="submit" value="delete"?>
        </form>
        </td></tr>
<?php
        }
        echo "</table>\n";
} else {
        echo "backups directory not found";
}
?>

<form>
<input type="hidden" name="command" value="make_backup" />
<input type="submit" value="Make backup now!" />
</form>
 
Last edited:
matja said:
hacked this together for you.
put it in a directory, make a directory under that called 'backups', and give the webserver write permissions, and fill in the mysql info :)
simple and hacky, but it works (for me :))

oh btw, mysqldump writes to stdout, so you just redirect stdout to a file. the normal mysql shell command will parse it, if you redirect stdin from said file, as this script does.
Click to expand...
Is there a way to modify that to a) gz gip, b) download locally rather than save to server?
 
jdickerson said:
Is there a way to modify that to a) gz gip, b) download locally rather than save to server?
Click to expand...
a) Edit the shell command. Replace
Code: 
        $command = 'mysqldump' .
                ' --quote-names --add-drop-table' .
                ' -u ' . $config['mysql_username'] .
                ' -p' . $config['mysql_password'] .
                ' --databases ' . $config['mysql_database_name'] .
                ' > ' . $config['backups_directory'] . '/' .
                $filename;
with
Code: 
$command = 'mysqldump --quote-names --add-drop-table -u ' . $config['mysql_username'] . ' -p' . $config['mysql_password'] . ' --databases ' . $config['mysql_database_name'] . ' | gzip -c > ' . $config['backups_directory'] . '/' . $_POST['filename'] . '.gz';

That should work, but keep in mind it may take a while. BTW Matja, register_globals is not your friend!

b) Well think about it, to download something locally you need a server copy in the first place. :P
 
exec()? If you're corporately hosted then it's unlikely to work as system() is blocked(?), but it could be down to OS configuration etc. - worth a shot.
 
You must log in or register to reply here.
Back
Top Bottom