MySQL database backup and restore using PHP

<!-- MySQL database backup and restore using PHP -->

<!DOCTYPE html>
if(isset($_POST['username']) && isset($_POST['password']) && isset($_POST['databasename'])) {
$username = trim($_POST['username']);
$password = trim($_POST['password']);
$databasename = trim($_POST['databasename']);
$backupRestore = $_POST['backupRestore'];

if ($backupRestore == 'backup'){
$data = $_POST['data'];
$now = str_replace(":", "", date("Y-m-d H:i:s"));
$outputfilename = $databasename . '-' . $now . '.db';
$outputfilename = str_replace(" ", "-", $outputfilename);

//Dump the MySQL database
if ($data == "wd"){
//With data
exec('mysqldump -u '. $username .' -p'. $password .' '. $databasename .' > '. $outputfilename);
//Without data
exec('mysqldump --no-data -u '. $username .' -p'. $password .' '. $databasename .' > '. $outputfilename);

//Download the database file
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($outputfilename));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($outputfilename));

//After download remove the file from server
exec('rm ' . $outputfilename);

else{//Restore the database

$target_path = getcwd();
$databasefilename = $_FILES["databasefile"]["name"];

//Upload the database file to current working directory
move_uploaded_file($_FILES["databasefile"]["tmp_name"], $target_path . '/' . $databasefilename);

//Restore the database
exec('mysql -u '. $username .' -p'. $password .' '. $databasename .' < '. $databasefilename);

//Remove the file from server
exec('rm ' . $databasefilename);
<title>MySQL database backup</title>

$(document).ready(function() {
function checkParameters(){
var username = $.trim($("#username").val());
var password = $.trim($("#password").val());
var databasename = $.trim($("#databasename").val());
if (username == ""){
alert("Plsease enter mysql username.");return false;
else if (password == ""){
alert("Plsease enter mysql password.");return false;
else if (databasename == ""){
alert("Plsease enter mysql database name.");return false;
else if($("#restore").is(':checked')){
var filename = $(".restoreFile").val();
if(filename == ""){
alert("Please choose a file.");return false;
var valid_extensions = /(\.db|\.sql)$/;
if (!valid_extensions.test(filename)){
alert('Invalid file format.');return false;
return true;
function showHide(id){
if (id == "backup"){

<form method="post" action="<?php echo $_SERVER['PHP_SELF'];?>" onsubmit="return checkParameters();" enctype="multipart/form-data">
<table border="0" bgcolor="#C0C0C0" align="center" width="40%">
<td colspan="3" align="center"><b>Please enter the following parameters</b></td>
<td>MySQL username*</td>
<td><input type="text" id="username" name="username" /></td>
<td>MySQL password*</td>
<td><input type="text" id="password" name="password" /></td>
<td>MySQL database name*</td>
<td><input type="text" id="databasename" name="databasename" /></td>
<td colspan="2">Backup <input type="radio" name="backupRestore" id="backup" value="backup" checked="true" onclick="showHide(;" /></td>

With data
With out data
<td>Restore <input type="radio" name="backupRestore" id="restore" value="restore" onclick="showHide(;" /></td>
<td><input class="restoreFile" type="file" name="databasefile" /></td>
<td colspan="3" align="center"><input type="submit" value="Submit"/></td>

Injection Prevention – mysql_real_escape_string()

this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(‘) a user might enter with a MySQL-safe substitute, an escaped quote \’.

Lets try out this function on our two previous injection attacks and see how it works.

Continue reading

Creating a MySQL Index

Creating a MySQL Index – New Table

If you are creating a new MySQL table you can specify a column to index by using the INDEX term as we have below. We have created two fields: name and employeeID (index).

MySQL Code:

CREATE TABLE employee_records (
	name VARCHAR(50), 
	employeeID INT, INDEX (employeeID)

Creating a MySQL Index – Existing Table

You can also add an index to an older table that you think would benefit from some indexing. The syntax is very similar to creating an index in a new table. First, let’s create the table.

MySQL Code:

CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT)

With our newly created table we are going to update the “employee_records2” table to include an index.

MySQL Code:

CREATE INDEX id_index ON employee_records2(employeeID)