In today’s FOSSwire programming special, I’m going to be showing you how to build a script in PHP that will allow you to back up your MySQL database remotely, using just a web browser.
Why might you need such a script? Well, remote backup through a web browser could be very useful, as you will be able to do it from any machine with a web browser, where you might not have the ability to or want to log in via SSH.
First of all, I need to point out an important point. This example will have no authentication in it. That means anyone that can find this script can perform a backup of your MySQL databases should they know a valid password, or could try knocking at the door to see if they can get in. I strongly advise you restrict access to the script we’re about to write using some form of authentication, be it through .htpasswd or similar, or by building a custom login system on top of this.
A few more notes:
- This script is basic. It’s not designed to be super-duper, and it’s probably best as a starting point for your own script. Take it with a pinch of salt.
- It needs a directory writable to the web server to store its files. This should most definitely be outside of the web root - i.e. impossible for anyone to download your raw database dumps in their browser.
- Any errors that happen in the mysqldump process will get thrown right back onto the browser. Making that cleaner and better is outside the scope of this script.
- It’s ugly, but making it pretty isn’t my job, or my expertise.
- It is designed for servers where MySQL runs on localhost (i.e. the same box as the web server). It can be easily adapted though, just by changing the mysqldump command string.
- The mysqldump program must be in the PATH, or else you must supply the full path to it in $command.
<?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.
Like this article? Subscribe to FOSSwire now (
Backup MySQL via PHP « 0ddn1x: tricks with *nix wrote:
[...] Backup MySQL via PHP Filed under: Technology — 0ddn1x @ 2008-01-24 20:33:45 +0000 http://fosswire.com/2008/01/21/do-a-mysql-backup-from-a-php-script/ [...]
# Posted on 24-Jan-08 at 1:33 pm
MySQL Backup From a PHP script | David Bisset: Web Designer, Coder, Wordpress Guru wrote:
[...] bumped into PHP scripts like this before, but haven’t bookmarked them until now. Tags: MySQL, [...]
# Posted on 08-Feb-08 at 8:47 am