This morning I woke up and found that my site was gone. I'm not just talking about the server being down here - no, the site (or better, my blog's contents) was gone. I was getting database errors left and right (perhaps that means I need to do some better error catching in xBlog, eh?), so I logged into phpMyAdmin. Or I tried anyway. I wasn't able to log in, so I logged into cPanel for this domain. I went to the MySQL database section, and much to my dismay, none of my databases showed up. At this point, I officially freaked out. I immediately went to the support center for my hosting, and saw that someone else was having the same problems. There was hope, at least it wasn't just my databases. One of the server administrators came on, and was simply able to restart the SQL server to fix the problem. Thankfully, all my data was still intact.
I did, however, learn a most important lesson (one that I should have already known). Backup you data. Since I didn't want to have to manually go into phpMyAdmin and backup the database for xBlog each day, I started looking at what I'd have to do to write a PHP script to do this. A bit of research showed me that the easiest way would be to use mysqldump, with a shell command.
So, I rolled up my sleeves and wrote the following PHP function to make automatically backing up MySQL databases an easy thing.
<?php
// add your authentication etc. here
// where to store backups? must be writable by the web server user and out
// of the web root so people can't download your DB dumps in their browser
define('BACKUPDIR', '/var/www/privatedata/');
// for making links to this page (form action etc.)
define('THISPAGE', $_SERVER['PHP_SELF']);
/**** SOME FUNCTIONS ****/
function doHeader($title) {
// makes a very basic page header
?><html><head><title><?php echo $title;?></title></head><body><?php
}
function doFooter() {
// makes a very basic page footer
?></body></html><?php
}
// if the filename variable in POST is set, the form has been submitted
if (!empty($_POST['filename'])) {
// we're now going to go through and validate and verify the inputs
// so we know what we're getting and to abort if something is wrong
$errors = array();
$n = 0;
/* we'll put any errors inside this error array, and at the end we'll
list them all for the user to see everything's that's wrong so they
can fix it */
if (empty($_POST['filename'])) { // no filename
$errors[$n] = "You must enter a filename.";
$n++;
}
if (empty($_POST['mysqluser'])) { // no MySQL username
$errors[$n] = "You must enter a MySQL username.";
$n++;
}
if (empty($_POST['mysqlpass'])) { // no MySQL password
$errors[$n] = "You must enter a MySQL password.";
$n++;
}
if ($_POST['backupall'] == 'false' AND empty($_POST['backupwhichdb'])) { // they select to back up a specific DB, but don't say which one
$errors[$n] = "You selected to backup a specific database, but did not specify which one.";
$n++;
}
if ($n > 0) { // if there were errors in the validation stage...
// display an error page
doHeader('Remote Database Backup');
?><h1>Remote Database Backup</h1>
<h2>The backup could not be completed.</h2>
<ul>
<?php foreach ($errors as $err) { // loop through each error
?><li><?php echo $err; // and display its text about it ?></li><?php
}
?>
</ul>
<a href="<?php echo THISPAGE;?>">Return to backup form</a>
<?php
doFooter();
die(); // quit script, don't keep running into the next bit
}
// if we're here, the validation must have been fine, so let's get on with the processing
// escape shell arguments to mitigate command line injection
// please note that this is only basic security, more layers would be added for serious production use
$_POST['filename'] = escapeshellcmd($_POST['filename']);
$_POST['mysqluser'] = escapeshellarg($_POST['mysqluser']);
$_POST['mysqlpass'] = escapeshellcmd($_POST['mysqlpass']);
$_POST['backupwhichdb'] = escapeshellarg($_POST['backupwhichdb']);
// do we want to back up all databases?
$backupall = ($_POST['backupall'] == 'false') ? false : true;
// if we want to back up all databases, set this to -A in the command (backs up all), if not, set it to the name of the database to back up
$dbarg = $backupall ? '-A' : $_POST['backupwhichdb'];
// form our command to execute
$command = "mysqldump ".$dbarg." -u ".$_POST['mysqluser']." -p".$_POST['mysqlpass']." -r \"".BACKUPDIR.$_POST['filename']."\" 2>&1";
// make a header and show some progress to the user, could take a long time
doHeader('Remote Database Backup');
?><h1>Running backup, please wait...</h1><?php
// execute the command we just set up
system($command);
// if they opted to bzip the backup, then do so
if ($_POST['bzip'] == 'true') {
system('bzip2 "'.BACKUPDIR.$_POST['filename'].'"');
}
// OK, we're done. Tell the user what happened. If any errors occurred, they get displayed at the system() call.
?><h2>Command executed. If any errors occurred, they will be displayed above.</h2>
<a href="<?php echo THISPAGE;?>">Return to backup form</a><?php
// pretty footer
doFooter();
// and quit, we're done here!
die();
}
// if the form wasn't submitted, then display the form to the user for the first time
// with a pretty header
doHeader('Remote Database Backup');
?><h1>Remote Database Backup</h1>
<p><em><strong>Please note:</strong> once you hit Create, the backup may take up to 15 seconds or so to create. The page will not load immediately, so be patient.</em></p>
<form name="dbbackup" method="post" action="<?php echo THISPAGE;?>">
Backup file name: <strong><?php echo BACKUPDIR;?></strong><input type="text" name="filename" value="<?php echo date('dMY_H.i.s').'.sql';?>" /><br />
<input type="checkbox" name="bzip" value="true" id="bzipTick" /><label for="bzipTick">Compress backup file with Bzip2 compression</label><br /><br />
MySQL username: <input type="text" name="mysqluser" value="" /><br />
MySQL password: <input type="password" name="mysqlpass" value="" /><br /><br />
Backup what?<br />
<input type="radio" name="backupall" value="true" id="backupallTrue" /><label for="backupallTrue">Backup all databases</label><br />
<input type="radio" name="backupall" value="false" id="backupallFalse" /><label for="backupallFalse">Backup specific database</label> <input type="text" name="backupwhichdb" value="" /><br />
<br /><br />
<input type="submit" value="Create" />
</form>
<?php
// and a pretty footer
doFooter();
?>
And that is it. Properly in place, that script is a very simple, down and dirty interface to remotely back up your MySQL databases from a web browser.
0 Responses to Back Up Your MySQL with PHP
Your feedback is always appreciated. We will try to reply to your queries as soon as time allows.
Note:
1. To add HTML CODE in comments then please use our HTML Encoder.
2. If you are including a link, Please include it using html tags. However irrelevant links are not tolerated.
3. Please do not spam, Spam comments will be deleted immediately after our review.
Regards,
Rupesh.