I’ve recently finished and launched my website. Since it is built on WordPress, it took me a while to set it up as I wanted it. Now, I want to back everything up, just in case.

What if you need to change web host or switch your database server? This is probably the time when backups are really the easiest way to do the change.

There are a few excellent and quite cheap solutions you can find on the web, but if you are anything like me you wouldn’t want to rely on 3^rd party solutions to do this job and on the plus side – it’s free!

So let’s start.

What to back-up?

Basically there are two things you need to backup: all your server files and MySQL databases.

You can do this manually. Zip and download all the files from your server and dump your MySQL database(s) and save them to your local disk. But the tricky part (well, not really) is to restore the backup you created manually. You need to copy back all of the files and import MySQL dump files through phpMyAdmin or some other app.

Well, why not have an automatic script to do this for you?

Backup MySQL

When working with MySQL, I always start by setting MySQL DB configurations to a config file. What we need to set are DB host, DB UserName, DB Password, name of MySQL DB and tables we want to backup within selected MySQL DB.

Save these settings to config.php file.

<?php
    // Settings
    $DBhost = 'localhost';
    $DBuser = 'mysql_username';
    $DBpass = 'mysql_password';
    $DBName = 'name_of_mysql_db';
    $table = '*';
?>

Ok, so now we need to create a function that will back-up MySQL DB.

function backup_tables()
{

}

Since this function will do almost everything, we need to pass MySQL config to the function. So we will pass everything that we previously saved to config file.

function backup_tables($host,$user,$pass,$name,$tables)
{

}

When working with MySQL the first thing needed is to connect to MySQL DB.

function backup_tables($host,$user,$pass,$name,$tables)
{
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
}

Once we have established connection to MySQL we can start working with it.
Everything we want to save will be held in a single variable. So, define the var $return and set it’s value to an empty string.

function backup_tables($host,$user,$pass,$name,$tables)
{
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    $return = "";
}

Because we want to provide an option to back-up all the tables within MySQL database or some specific table(s), first we have to check what the user wants to backup.
So if the script detects that all the tables should be backed-up, it will then get the names of tables from MySQL DB. Otherwise, it will just take the provided table names and work with them. Either way, array $tables will contain all the table names with which the script should work. In case that only a single table is to be backed-up, $tables will still hold the name of that table.

function backup_tables($host,$user,$pass,$name,$tables)
{
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    $return = "";

    // Get all of the tables
    if($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        if (is_array($tables)) {
            $tables = explode(',', $tables);
        }
    }
}

We now need to cycle through each table and save the contents of that table. For each table we will create three-part output. This output is what will be saved into a backup file.
First part is to tell the script, which will be restoring this backup, to remove existing table, which we want to restore. Second is to create new table with the same name as the deleted one, and the final part is to insert backed-up values into newly created table.

Another thing I should mention, you can see that after each command we are saving, I’m adding ;<|||||||> set of characters. This is only as a separator, so that the restore script (which we are going to write, in a bit) will now where the commands end. While testing the script on a real-world database, I used semicolon as a separator, but then restoring didn’t go quite well (because inputs already had semicolons in the content). After changing it to current, everything works fine. You can use whatever you like, just as long as it can’t be found in any of the tables you are backing-up.

function backup_tables($host,$user,$pass,$name,$tables)
{
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    $return = "";

    // Get all of the tables
    if($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        if (is_array($tables)) {
            $tables = explode(',', $tables);
        }
    }

    // Cycle through each provided table
    foreach($tables as $table) {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        // First part of the output – remove the table
        $return .= 'DROP TABLE ' . $table . ';<|||||||>';

        // Second part of the output – create table
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return .= "\n\n" . $row2[1] . ";<|||||||>\n\n";

        // Third part of the output – insert values into new table
        for ($i = 0; $i < $num_fields; $i++) {
            while($row = mysql_fetch_row($result)) {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j<($num_fields-1)) {
                        $return.= ',';
                    }
                }
                $return.= ");<|||||||>\n";
            }
        }
    $return.="\n\n\n";
    }
}

We are almost done, we now just need to save the output to a file. I will save the backup file to backup subdirectory. Output is held by $return variable.
We will set the filename to which the output should be saved and then save the backup. The filename is formatted as follows:  dbbackup_date_time.sql
I also want to print that the backup is finished and to provide a link to the backup file. I’m also closing MySQL connection at the end of this function.

function backup_tables($host,$user,$pass,$name,$tables)
{
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    $return = "";

    // Get all of the tables
    if($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        if (is_array($tables)) {
            $tables = explode(',', $tables);
        }
    }

    // Cycle through each provided table
    foreach($tables as $table) {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        // First part of the output – remove the table
        $return .= 'DROP TABLE ' . $table . ';<|||||||>';

        // Second part of the output – create table
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return .= "\n\n" . $row2[1] . ";<|||||||>\n\n";

        // Third part of the output – insert values into new table
        for ($i = 0; $i < $num_fields; $i++) {
            while($row = mysql_fetch_row($result)) {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j<($num_fields-1)) {
                        $return.= ',';
                    }
                }
                $return.= ");<|||||||>\n";
            }
        }
        $return.="\n\n\n";
    }

    // Generate the filename for the sql file
    $filess = 'backup/dbbackup_' . date("d.m.Y_H:i:s") . '.sql';

    // Save the sql file
    $handle = fopen($filess,'w+');
    fwrite($handle,$return);
    fclose($handle);

    // Print the message
    print("The backup has been created successfully. You can get the file <a href='$filess'>here</a>.<br>\n");

    // Close MySQL Connection
    mysql_close();
}

We have our backup function ready. We can now call it. To do this we need to include the settings from the config file and call the function.

// Include settings
include("config.php");

// Call the function
backup_tables($DBhost,$DBuser,$DBpass,$DBName,$table);

Before going and testing this function I wanted to change the filename of the backup a bit. Since our function is checking if the user entered all the tables to be backed-up or just some specific ones I want to add small changes to the filename, depending on the input. So I’m adding the following code between inclusion of the config file and function call. But this also requires that we pass another arg to our function. I named it bckextname.

// Call the backup function for all tables in a DB
if ($table == '*') {
    $extname = 'alltbls';
}else{
    $extname = str_replace(",", "_", $table);
    $extname = str_replace(" ", "_", $extname);
}

What this does is that it checks if the input is ‘*’ (which, if you remember from the code above, tells the function to back-up all the tables), and sets the extended name var $extname to alltbls. Otherwise it sets this var to all the table names that will be backed-up. This means that we will be able to see which tables are in which backup, just by looking at filename of the backup.

So now, after adding another argument, we need to change the $filess variable, near the end of the code. So the whole page now looks like this

<?php
    // Include settings
    include("config.php");

    // Set the suffix of the backup filename
    if ($table == '*') {
        $extname = 'all';
    }else{
        $extname = str_replace(",", "_", $table);
        $extname = str_replace(" ", "_", $extname);
    }

    // Call the backup function for all tables in a DB
    backup_tables($DBhost,$DBuser,$DBpass,$DBName,$table,$extname);

    // Backup the table and save it to a sql file
    function backup_tables($host,$user,$pass,$name,$tables,$bckextname)
    {
        $link = mysql_connect($host,$user,$pass);
        mysql_select_db($name,$link);
        $return = "";

        // Get all of the tables
        if($tables == '*') {
            $tables = array();
            $result = mysql_query('SHOW TABLES');
            while($row = mysql_fetch_row($result)) {
                $tables[] = $row[0];
            }
        } else {
            if (is_array($tables)) {
            $tables = explode(',', $tables);
        }
    }

    // Cycle through each provided table
    foreach($tables as $table) {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        // First part of the output – remove the table
        $return .= 'DROP TABLE ' . $table . ';<|||||||>';

        // Second part of the output – create table
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return .= "\n\n" . $row2[1] . ";<|||||||>\n\n";

        // Third part of the output – insert values into new table
        for ($i = 0; $i < $num_fields; $i++) {
            while($row = mysql_fetch_row($result)) {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j<($num_fields-1)) {
                        $return.= ',';
                    }
                }
                $return.= ");<|||||||>\n";
            }
        }
        $return.="\n\n\n";
    }

    // Generate the filename for the sql file
    $filess = 'backup/dbbackup_' . date("d.m.Y_H:i:s") . '_' . $extname . '.sql';

    // Save the sql file
    $handle = fopen($filess,'w+');
    fwrite($handle,$return);
    fclose($handle);

    // Print the message
    print("The backup has been created successfully. You can get the file <a href='$filess'>here</a>.<br>\n");

    // Close MySQL Connection
    mysql_close();
}
?>

I saved this code to backup_perform.php file.

Restore MySQL Backup

Since we have already saved the backup in the form of MySQL commands, we now just need to import these commands and run them.

Since the backup filenames are generated by time and date and table names, we will need to provide the filename of the backup to the restore script, so the script will know which backup to use.

We will establish MySQL connection, read-in the file and run the commands from the file.

First we will get the provided backup filename and include config file since we will need MySQL settings.

// Get the provided arg
$id=$_GET['id'];

// Check if the file has needed args
if ($id==NULL){
    print("<script type='text/javascript'>window.alert('You have not provided a backup to restore.')</script>");
    print("<script type='text/javascript'>window.location='backup_overview.php'</script>");
    print("You have not provided a backup to restore.<br>Click <a href='backup_overview.php'>here</a> if your browser doesn't automatically redirect you.");
}

// Include settings
include("config.php");

We need to generate the filename of the backup. Remember: backup file is located in the backup dir.

$filename = 'backup/' . $id;

So now, we will restore the backup (basically just run MySQL commands from the file). Remember to change the separator characters if you changed them in previous code (backup_perform.php file). Otherwise, the script won’t work correctly.

// Restore the backup
$con = mysql_connect($DBhost,$DBuser,$DBpass);
if ($con !== false){

    // Load and explode the sql file
    mysql_select_db("$DBName");
    $f = fopen($filename,"r+");
    $sqlFile = fread($f,filesize($filename));
    $sqlArray = explode(';<|||||||>',$sqlFile);

    // Process the sql file by statements
    foreach ($sqlArray as $stmt) {
        if (strlen($stmt)>3){
            $result = mysql_query($stmt);
        }
    }
}

// Close the connection
mysql_close();

I will also add error reporting, so that I will know if anything goes wrong (but it shouldn’t). So the script looks like this. Save it as backup_restore.php

<?php
    // Get the provided arg
    $id=$_GET['id'];

    // Check if the file has needed args
    if ($id==NULL){
        print("<script type='text/javascript'>window.alert('You have not provided a backup to restore.')</script>");
        print("<script type='text/javascript'>window.location='backup_overview.php'</script>");
        print("You have not provided a backup to restore.<br>Click <a href='backup_overview.php'>here</a> if your browser doesn't automatically redirect you.");
    }

    // Include settings
    include("config.php");

    // Generate filename and set error variables
    $filename = 'backup/' . $id;
    $sqlErrorText = '';
    $sqlErrorCode = 0;
    $sqlStmt      = '';

    // Restore the backup
    $con = mysql_connect($DBhost,$DBuser,$DBpass);
    if ($con !== false){

        // Load and explode the sql file
        mysql_select_db("$DBName");
        $f = fopen($filename,"r+");
        $sqlFile = fread($f,filesize($filename));
        $sqlArray = explode(';<|||||||>',$sqlFile);

        // Process the sql file by statements
        foreach ($sqlArray as $stmt) {
            if (strlen($stmt)>3){
                $result = mysql_query($stmt);
            }
        }
    }

    // Print message (error or success)
    if ($sqlErrorCode == 0){
        print("Database restored successfully!<br>\n");
        print("Backup used: " . $filename);
    } else {
        print("An error occurred while restoring backup!<br><br>\n");
        print("Error code: $sqlErrorCode<br>\n");
        print("Error text: $sqlErrorText<br>\n");
        print("Statement:<br/> $sqlStmt<br>");
    }

    // Close the connection
    mysql_close();
?>

Maintenance part of the script

Now, we have written backup script and restore script. I just want to add a management script, so that we wouldn’t need to call these files manually each time.

I will print a simple table that will have the list of all backups, and that will have Restore, Download and Delete links.

This isn’t hard at all. All I need to do is to list the files in the backup dir.

<table>
<tr><th>Filename</th></tr>
<?php
    // List the files
    $dir = opendir ("./backup");
    while (false !== ($file = readdir($dir))) {

        // Print the filenames that have .sql extension
        if (strpos($file,'.sql',1)) {

            // Print the cells
            print("<tr>\n");
            print("  <td>" . $file . "</td>\n");
            print("</tr>\n");
        }
    }
?>
</table>

Now, just add the links to other functions. I have also added the part where the script gets the date and time of backup creation and puts it in the table.

<p>Create new backup by clicking <a href="backup_perform.php">here</a> or use the links below to manage existing backups.</p>
<table>
<tr><th>Filename</th><th>Date and time</th><th>Restore</th><th>Get the file</th><th>Delete</th></tr>
<?php
    // List the files
    $dir = opendir ("./backup");
    while (false !== ($file = readdir($dir))) {

        // Print the filenames that have .sql extension
        if (strpos($file,'.sql',1)) {

            // Get time and date from filename
            $date = substr($file, 9, 10);
            $time = substr($file, 20, 8);

            // Print the cells
            print("<tr>\n");
            print("  <td>" . $file . "</td>\n");
            print("  <td>" . $date . " " . $time . "</td>\n");
            print("  <td><a href='backup_restore.php?id=" . $file . "'>Restore</a></td>\n");
            print("  <td><a href='backup/" . $file . "'>Download</a></td>\n");
            print("  <td><a href='delete_file.php?file=" . $file . "'>Delete</a></td>\n");
            print("</tr>\n");
        }
    }
?>
</table>

I have saved this code as backup_restore.php. We just need to create one more file: delete_file.php

It will allow us to delete existing backup files. We will be using unlink() PHP function. First we are checking if the file to delete has been provided, and then we are checking that it actually is a file. Using unlink() we are removing the selected file and returning to backup_restore.php.

<?php
    // Get the filename to be deleted
    $file=$_GET['file'];

    // Check if the file has needed args
    if ($file==NULL){
        print("<script type='text/javascript'>window.alert('You have not provided a file to delete.')</script>");
        print("<script type='text/javascript'>window.location='backup_overview.php'</script>");
        print("You have not provided a file to delete.<br>Click <a href='backup_overview.php'>here</a> if your browser doesn't automatically redirect you.");
        die();
    }

    // Delete the file
    if (!is_dir("backup/" . $file)) {
        unlink("backup/" . $file);
    }

    // Redirect
    header("Location: backup_overview.php");
?>

That’s it! We have created the first part of our Backup & Restore Script: MySQL Backup & Restore. There is one job left, create Backup & Restore script for the server files and merge these two parts.

Server Files Backup

Now we are going to make a script that will be creating a zip archive. It will be zipping all files and directories from the root dir.

For this I will use PclZip library. It is licensed under GNU/LGPL, so there is no license problem for using it. You can download this library from here (http://www.phpconcept.net/pclzip). I have included it in the download package.

PclZip does not nativly support the addition of directories, so we will have to do the following

<?php
    // Include the PclZip library
    require_once('pclzip.lib.php');

    // Set the arhive filename
    $archive = new PclZip('backup/archive.zip');

    // Set the dir to archive
    $v_dir = dirname(getcwd()); // or dirname(__FILE__);
    $v_remove = $v_dir;

    // Create the archive
    $v_list = $archive->create($v_dir, PCLZIP_OPT_REMOVE_PATH, $v_remove);
    if ($v_list == 0) {
        die("Error : ".$archive->errorInfo(true));
    }
?>

Since this script is not in the root dir on my server but in a subdir, I’m using this dirname(getcwd()) to tell the script where my root dir is compared to current dir (one level back). getcwd() gets my current dir, while dirname() returns dir one level up from the current dir (return value of getcwd()).

Save this as a files_backup.php and upload pclzip.lib.php that you have downloaded from PclZip website. When you run files_backup.php, you will see that it has created new file in the backup dir, named archive.php. If you download this archive you will see that all your files and dirs are in there.

Server Files Restore

To restore a zip archive, we will need first to delete all files and dirs, and then to unpack our zipped backup. So, we will start by recursively deleting everything in the target dir, and then extracting to the target dir.

I have placed pczlib library to be included at the very beginning, so that we could easily delete it afterwards. After including this library, we are calling the rrmdir() function (recursively remove directory) with provided path which is one level up from the working dir. In the previous script we have backed-up the root dir, so now we want to extract to the root dir.

We are limiting the rrmdir() function not to delete current working file (files_restore.php) and our backup archive so that we could finish the extraction, after which we are deleting these two files ourselves.

<?php
    // Include this library so we could delete the file
    include('pclzip.lib.php');

    // Remove the current dir
    rrmdir(dirname(getcwd()));

    // Recursively remove dir
    function rrmdir($dir) {
        if (is_dir($dir)) {
            $objects = scandir($dir);
            foreach ($objects as $object) {
                if ($object != "." &amp;&amp; $object != ".." &amp;&amp; $object != "files_restore.php" &amp;&amp; $object != "archive.zip") {
                    if (filetype($dir."/".$object) == "dir") {
                        rrmdir($dir."/".$object);
                    } else {
                        unlink($dir."/".$object);
                    }
                }
            }
            reset($objects);
        }
    }

    // Extract archive
    $archive = new PclZip('archive.zip');
    if ($archive->extract(PCLZIP_OPT_PATH, "../") == 0) {
        die("Error : ".$archive->errorInfo(true));
    }

    // Remove two left files
    unlink("backup/archive.zip");
    rmdir("backup");
    unlink("files_restore.php");
    rmdir(getcwd());
?>

Our File Restoring script is now working. The last thing is to merge MySQL Backup script with Files Backup script and the Restore scripts, too. We will also change the Maintenance script a bit, so it will reflect both MySQL and Files backups.

Merging and Rewriting Maintenance Script

Copy the content of files_backup.php into backup_perform.php, at the very end, right after the function backup_tables is closed. Move the line where we generate the filename before the backup_tables function and after setting the suffix of the backup filename.

Add a new argument to the function, named $filess, and also add it to the function call, two lines above.

The beginning of the script should look like this

<?php
    // Include settings
    include("config.php");

    // Set the suffix of the backup filename
    if ($table == '*') {
        $extname = 'all';
    }else{
        $extname = str_replace(",", "_", $table);
        $extname = str_replace(" ", "_", $extname);
    }

    // Generate the filename for the backup file
    $filess = 'backup/dbbackup_' . date("d.m.Y_H:i:s") . '_' . $bckextname . '.sql';

    // Call the backup function for all tables in a DB
    backup_tables($DBhost,$DBuser,$DBpass,$DBName,$table,$extname,$filess);

    // Backup the table and save it to a sql file
    function backup_tables($host,$user,$pass,$name,$tables,$bckextname,$filess)
    {
        $link = mysql_connect($host,$user,$pass);
        mysql_select_db($name,$link);
        $return = "";

Remove the  . ‘.sql’ part of the $filess variable.

In the Save the sql file part of the backup_tables function, add . ’.sql’ so that the script will write the MySQL dump into .sql file. So this part should look like this, now.

// Save the sql file
$handle = fopen($filess.'.sql','w+');
fwrite($handle,$return);
fclose($handle);

In the part that we copied from the files_backup.php file, replace “backup/archive.zip” with $filess.’.zip’, so this part should look like this.

require_once('pclzip.lib.php');
$archive = new PclZip($filess.'.zip');
$v_dir = dirname(getcwd()); // or dirname(__FILE__);
$v_remove = $v_dir;
$v_list = $archive->create($v_dir, PCLZIP_OPT_REMOVE_PATH, $v_remove);
if ($v_list == 0) {
    die("Error : ".$archive->errorInfo(true));
}

Move the success message at the end of the file, and change it a bit, so it will display both MySQL dump file and zip archive (both of these files have the same name, just different extensions).

// Print the message
print('The backup has been created successfully. <br />You can get <b>MySQL dump file</b> <a href="' . $filess . '.sql">here</a>.<br>' . "\n");
print('You can get <b>Backed-up files archive</b> <a href="' . $filess . '.zip">here</a>.<br>' . "\n");

Now, the whole script looks like this. Save it as backup.php.

<?php
    // Include settings
    include("config.php");

    // Set the suffix of the backup filename
    if ($table == '*') {
        $extname = 'all';
    }else{
        $extname = str_replace(",", "_", $table);
        $extname = str_replace(" ", "_", $extname);
    }

    // Generate the filename for the backup file
    $filess = 'backup/dbbackup_' . date("d.m.Y_H:i:s") . '_' . $bckextname;

    // Call the backup function for all tables in a DB
    backup_tables($DBhost,$DBuser,$DBpass,$DBName,$table,$extname,$filess);

    // Backup the table and save it to a sql file
    function backup_tables($host,$user,$pass,$name,$tables,$bckextname,$filess)
    {
        $link = mysql_connect($host,$user,$pass);
        mysql_select_db($name,$link);
        $return = "";

        // Get all of the tables
        if($tables == '*') {
            $tables = array();
            $result = mysql_query('SHOW TABLES');
            while($row = mysql_fetch_row($result)) {
                $tables[] = $row[0];
            }
        } else {
            if (is_array($tables)) {
                $tables = explode(',', $tables);
            }
        }

        // Cycle through each provided table
        foreach($tables as $table) {
            $result = mysql_query('SELECT * FROM '.$table);
            $num_fields = mysql_num_fields($result);

            // First part of the output – remove the table
            $return .= 'DROP TABLE ' . $table . ';<|||||||>';

            // Second part of the output – create table
            $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
            $return .= "\n\n" . $row2[1] . ";<|||||||>\n\n";

            // Third part of the output – insert values into new table
            for ($i = 0; $i < $num_fields; $i++) {
                while($row = mysql_fetch_row($result)) {
                    $return.= 'INSERT INTO '.$table.' VALUES(';
                    for($j=0; $j<$num_fields; $j++) {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                        if (isset($row[$j])) {
                            $return .= '"' . $row[$j] . '"';
                        } else {
                            $return .= '""';
                        }
                        if ($j<($num_fields-1)) {
                            $return.= ',';
                        }
                    }
                    $return.= ");<|||||||>\n";
                }
            }
            $return.="\n\n\n";
        }

        // Save the sql file
        $handle = fopen($filess.'.sql','w+');
        fwrite($handle,$return);
        fclose($handle);

        // Close MySQL Connection
        mysql_close();
    }

    require_once('pclzip.lib.php');
    $archive = new PclZip($filess.'.zip');
    $v_dir = dirname(getcwd()); // or dirname(__FILE__);
    $v_remove = $v_dir;
    $v_list = $archive->create($v_dir, PCLZIP_OPT_REMOVE_PATH, $v_remove);
    if ($v_list == 0) {
        die("Error : ".$archive->errorInfo(true));
    }

    // Print the message
    print('The backup has been created successfully. <br />You can get <b>MySQL dump file</b> <a href="' . $filess . '.sql">here</a>.<br>' . "\n");
    print('You can get <b>Backed-up files archive</b> <a href="' . $filess . '.zip">here</a>.<br>' . "\n");
?>

Now we will just merge two restore scripts.

Copy the content of the files_restore.php into backup_restore.php file, just below mysql_close()., replace ‘archive.zip’ with $filename (you should do this in the rrmdir() function, extract archive part and in the last part where we remove two last files),  replace ‘files_restore.php’ with ‘restore.php’ and ‘backup_overview.php’ with ‘manage.php’.

When setting $filename var add . ‘.sql’ after $id.

Between mysql_close() and include(‘pclzip.lib.php’) insert the following code

// Change the filename from sql to zip
$filename = str_replace('.sql', '.zip', $filename);

This code will replace SQL extension with ZIP extension.

At the end of the file add a notice that files have been restored successfully. Your code should look something like this.

<?php
    // Get the provided arg
    $id=$_GET['id'];

    // Check if the file has needed args
    if ($id==NULL){
        print("<script type='text/javascript'>window.alert('You have not provided a backup to restore.')</script>");
        print("<script type='text/javascript'>window.location='manage.php'</script>");
        print("You have not provided a backup to restore.<br>Click <a href='manage.php'>here</a> if your browser doesn't automatically redirect you.");
    }

    // Include settings
    include("config.php");

    // Generate filename and set error variables
    $filename = 'backup/' . $id;
    $sqlErrorText = '';
    $sqlErrorCode = 0;
    $sqlStmt      = '';

    // Restore the backup
    $con = mysql_connect($DBhost,$DBuser,$DBpass);
    if ($con !== false){

        // Load and explode the sql file
        mysql_select_db("$DBName");
        $f = fopen($filename,"r+");
        $sqlFile = fread($f,filesize($filename));
        $sqlArray = explode(';<|||||||>',$sqlFile);

        // Process the sql file by statements
        foreach ($sqlArray as $stmt) {
            if (strlen($stmt)>3){
                $result = mysql_query($stmt);
            }
        }
    }

    // Print message (error or success)
    if ($sqlErrorCode == 0){
        print("Database restored successfully!<br>\n");
        print("Backup used: " . $filename . "<br>\n");
    } else {
        print("An error occurred while restoring backup!<br><br>\n");
        print("Error code: $sqlErrorCode<br>\n");
        print("Error text: $sqlErrorText<br>\n");
        print("Statement:<br/> $sqlStmt<br>");
    }

    // Close the connection
    mysql_close();

    // Change the filename from sql to zip
    $filename = str_replace('.sql', '.zip', $filename);

    // Include this library so we could delete the file
    include('pclzip.lib.php');

    // Remove the current dir
    rrmdir(dirname(getcwd()));

    // Recursively remove dir
    function rrmdir($dir) {
        if (is_dir($dir)) {
            $objects = scandir($dir);
            foreach ($objects as $object) {
                if ($object != "." &amp;&amp; $object != ".." &amp;&amp; $object != "restore.php" &amp;&amp; $object != $filename) {
                    if (filetype($dir."/".$object) == "dir") {
                        rrmdir($dir."/".$object);
                    } else {
                        unlink($dir."/".$object);
                }
            }
        }
        reset($objects);
    }

    // Extract archive
    $archive = new PclZip($filename);
    if ($archive->extract(PCLZIP_OPT_PATH, "../") == 0) {
        die("Error : ".$archive->errorInfo(true));
    }

    // Remove two left files
    unlink($filename);
    rmdir("backup");
    unlink("restore.php");
    rmdir(getcwd());

    // Files restored successfully
    print("Files restored successfully!<br>\n");
    print("Backup used: " . $filename . "<br>\n");
?>

Save this file as restore.php.

Next file to edit is backup_overview.php

In the first line, change from backup_overview.php to manage.php. Add following lines below the line where we set the $date variable. It removes the sql extension from the filename.

// Remove the sql extension part in the filename
$filenameboth = str_replace('.sql', '', $file);

In the Print the cells part, replace $file with $filenameboth. There are 4 occurrences. In the same section, replace ‘backup_restore.php?id=’ with ‘restore.php?id=’ and replace ‘delete_file.php?file=’ with ‘delete.php?file=’.

Replace the line

print("  <td><a href='backup/" . $filenameboth . ".sql'>Download SQL</a></td>\n");

with these two lines

print("  <td><a href='backup/" . $filenameboth . ".sql'>Download SQL</a></td>\n");
print("  <td><a href='backup/" . $filenameboth . ".zip'>Download ZIP</a></td>\n");

Save the file as manage.php. The complete code of this file is

<p>Create new backup by clicking <a href="backup.php">here</a> or use the links below to manage existing backups.</p>
<table>
<tr><th>Filename</th><th>Date and time</th><th>Restore</th><th>Get SQL file</th><th>Get ZIP file</th><th>Delete</th></tr>
<?php
    // List the files
    $dir = opendir ("./backup");
    while (false !== ($file = readdir($dir))) {

        // Print the filenames that have .sql extension
        if (strpos($file,'.sql',1)) {

            // Get time and date from filename
            $date = substr($file, 9, 10);
            $time = substr($file, 20, 8);

            // Remove the sql extension part in the filename
            $filenameboth = str_replace('.sql', '', $file);

            // Print the cells
            print("<tr>\n");
            print("  <td>" . $filenameboth . "</td>\n");
            print("  <td>" . $date . " " . $time . "</td>\n");
            print("  <td><a href='restore.php?id=" . $filenameboth . "'>Restore</a></td>\n");
            print("  <td><a href='backup/" . $filenameboth . ".sql'>Download SQL</a></td>\n");
            print("  <td><a href='backup/" . $filenameboth . ".zip'>Download ZIP</a></td>\n");
            print("  <td><a href='delete_file.php?file=" . $filenameboth . "'>Delete</a></td>\n");
            print("</tr>\n");
        }
    }
?>
</table>

As the last file to edit, we have delete_file.php.

Duplicate the Delete the file part, and change it as follows.

// Delete the SQL file
if (!is_dir("backup/" . $file . '.sql')) {
    unlink("backup/" . $file . '.sql');
}

// Delete the ZIP file
if (!is_dir("backup/" . $file . '.zip')) {
    unlink("backup/" . $file . '.zip');
}

Replace ‘backup_overview.php’ with ‘manage.php’ and you are done! Save it as delete.php.

The source code of this page

<?php
    // Get the filename to be deleted
    $file=$_GET['file'];

    // Check if the file has needed args
    if ($file==NULL){
        print("<script type='text/javascript'>window.alert('You have not provided a file to delete.')</script>");
        print("<script type='text/javascript'>window.location='manage.php'</script>");
        print("You have not provided a file to delete.<br>Click <a href='manage.php'>here</a> if your browser doesn't automatically redirect you.");
        die();
    }

    // Delete the SQL file
    if (!is_dir("backup/" . $file . '.sql')) {
        unlink("backup/" . $file . '.sql');
    }

    // Delete the ZIP file
    if (!is_dir("backup/" . $file . '.zip')) {
        unlink("backup/" . $file . '.zip');
    }

    // Redirect
    header("Location: manage.php");
?>

That’s it! You have fully functional (but still ugly though) Backup & Restore script. Remove the unnecessary files from your dir. I’m left with only the following ones: backup.php, config.php, delete.php, manage.php, pclzip.lib.php, restore.php and a single directory backup.

Cron Job

The whole point of this script is to make your (mine) life easier. So I wanted to create a Cron job, but then I realized that if I was only running backup.php, my backup dir will get full. So I’ve created a small script for Cron jobs.

This file will have three parts. The first part is going to delete all previous backups from the backup dir and nothing else. Second part will back-up MySQL DB, and the final part will back-up files from the server.

I also wanted to protect it from unauthorized access. So I’m adding password check at the beginning.

First I will get the password sent by the Cron job. Change the password to whatever you want, but DO change it. You can see that if passwords don’t match we are killing the script (with die()).

$pass = $argv[2];
if ($pass != 'your_own_custom_long_password_here') {
    die('Password is incorrect!');
}

Since this will be run from the command line (Cron Job), we can’t use $_GET[‘’], but instead we need to use $argv. Since I’m calling cron.php with following command in the Cron job settings

php /the/path/toyour/cronjob/file/cron.php -- password_you_changed

password is the third argument (first is file path and second is –), so I’ve set $argv array to element 2, since it starts counting from 0.

Now, I want to remove all previous backup files in the backup dir. So, I’m going to empty this dir.

foreach(glob('backup/' . '*.*') as $v){
    if (is_file($v)) {
        unlink($v);
    }
}

You can see that I’m removing all files (*.*) from the backup/ dir. I’m checking to see if they really are files and not subdirs.

I’ve simply copied backing-up MySQL and Files parts from the backup.php. This is simply because I wanted restore.php script to restore these cron backups, which are the same thing as manually called backups.

Next, I’ve changed a couple of things. I’ve added _cron_ to backup filename. So when I download backups on my local drive I will now, which are regular backups and which are after some big change I did. You don’t have to do this you can leave the filename as is.

I’ve also changed the message at the end of the file. Now it prints only “The backup has been successful!” and nothing else, because I don’t want to download backups or do anything else with these files. I will download manually these Cron backups.

I named this file cron.php, which has following code

<?php
    $pass = $argv[2];
    if ($pass != 'your_own_custom_long_password_here') {
        die('Password is incorrect!');
    }

    foreach(glob('backup/' . '*.*') as $v){
        if (is_file($v)) {
            unlink($v);
        }
    }

    // Include settings
    include("config.php");

    // Set the suffix of the backup filename
    if ($table == '*') {
        $extname = 'all';
    }else{
        $extname = str_replace(",", "_", $table);
        $extname = str_replace(" ", "_", $extname);
    }

    // Generate the filename for the backup file
    $filess = 'backup/dbbackup_' . date("d.m.Y_H:i:s") . '_cron_' . $extname;

    // Call the backup function for all tables in a DB
    backup_tables($DBhost,$DBuser,$DBpass,$DBName,$table,$extname,$filess);

    // Backup the table and save it to a sql file
    function backup_tables($host,$user,$pass,$name,$tables,$bckextname,$filess)
    {
        $link = mysql_connect($host,$user,$pass);
        mysql_select_db($name,$link);
        $return = "";

        // Get all of the tables
        if($tables == '*') {
            $tables = array();
            $result = mysql_query('SHOW TABLES');
            while($row = mysql_fetch_row($result)) {
                $tables[] = $row[0];
            }
        } else {
            if (is_array($tables)) {
                $tables = explode(',', $tables);
            }
        }

    // Cycle through each provided table
    foreach($tables as $table) {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        // First part of the output – remove the table
        $return .= 'DROP TABLE ' . $table . ';<|||||||>';

        // Second part of the output – create table
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return .= "\n\n" . $row2[1] . ";<|||||||>\n\n";

        // Third part of the output – insert values into new table
        for ($i = 0; $i < $num_fields; $i++) {
            while($row = mysql_fetch_row($result)) {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) {
                        $return .= '"' . $row[$j] . '"';
                    } else {
                        $return .= '""';
                    }
                    if ($j<($num_fields-1)) {
                        $return.= ',';
                    }
                }
                $return.= ");<|||||||>\n";
            }
        }
        $return.="\n\n\n";
    }

    // Save the sql file
    $handle = fopen($filess.'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);

    // Close MySQL Connection
    mysql_close();
    }

    require_once('pclzip.lib.php');
    $archive = new PclZip($filess.'.zip');
    $v_dir = dirname(getcwd()); // or dirname(__FILE__);
    $v_remove = $v_dir;
    $v_list = $archive->create($v_dir, PCLZIP_OPT_REMOVE_PATH, $v_remove);
    if ($v_list == 0) {
        die("Error : ".$archive->errorInfo(true));
    }

    // Print the message
    print('The backup has been created successfully!');
?>

We are done! Now, you can Backup & Restore your entire web site, manage your backups, download & remove them and set cron jobs.

Styling the Files

Since this is quite ugly (visually) I wanted to add some styling to the files so that they are nice to work with. The files in the download package are already styled, so you won’t have to bother with it.

Here are a few images of the styled script.

For styling I’ve used Transdmin Light Theme from http://www.transdmin.perspectived.com/.

I haven’t styled cron.php since it is not intended to be run within browser or to be worked with. It will only print a simple message that you will get in your email when executing cron.php as a cron job.

Usage

This script is free! You can download and use it as you wish. You are not even obligated to put a backlink! If you are using it for commercial purposes I kindly ask you to give some credit, but still it’s your free will.

Download

You can download the complete, styled source code, using the link below.

Social Share

   

39 Comments for this entry