Do a MySQL backup from a PHP script

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.

FOSSwire

Do a MySQL backup from a PHP script

Home » Articles »