Method for backing up and restoring a web site and its SQL database

Reboot a website in less than an hour.

Clavier lumineux Macbook Pro

Backing up your website and SQL database (Part 1)

As with your computer, keeping a backup copy of your website is imperative.

1- Prepare the backup of the website:

Always have a copy of your website on your computer. If you have installed a blog, forum, cms, e-commerce, keep an operational copy with the config, htaccess, plug-ins, additional modules, themes, templates and your modifications.

If you update your web software, do the same with your backup.
If your site contains an upload folder and logs, go get them regularly. To simplify this last task, the script below will make a ZIP backup of a log file. Instead of downloading a hundred small text files, everything will be grouped in a ZIP file.

Code PHP 
<?php
/* Make sure that the backup destination folder is created. It can be either in the www folder or elsewhere. Here we put it somewhere else*/
/* Modify your settings */
$uploads = "/home/loginftp/www/uploads"; /* absolute path of the uploads folder to save without the / final */
$repsauvegarde = "/home/loginftp/sauvegarde/";  /* absolute path to the backup directory */
$zipuploads = "mesuploads"; /* name of the zip file of the backup without putting the .zip at the end */

/* We are going to put 2 different folders in one zip file. */
/* Modify your settings */
$logs1 = "/home/loginftp/www/telechargement/logs"; /* absolute path to the logs of downloads directory to be saved without the / final */
$logs2 = "/home/loginftp/www/session/logs"; /* absolute path to the session logs folder to be saved without the / final */
$repsauvegarde = "/home/loginftp/sauvegarde/";  /* absolute path to the backup directory */
$ziplogs = "meslogs"; /* name of the zip file of the backup without putting the .zip at the end */
/* That is it. Place this file by FTP somewhere on your web server, in a discreet location. */
/* Then open it with your web browser and follow the instructions. */

echo "<html><body>";
echo "1. This script automatically creates a backup of the folder $uploads \n<br>\n<br>";
echo "The folder is being backed up.......\n<br>";
if (system("zip -qr -5 $repsauvegarde$zipuploads $uploads"));
echo "C'est fait. \n<br>";
echo "\n<br>";
echo "2. Backup of folders $logs1 and $logs2 \n<br>\n<br>";
echo "Folders are being backed up.......\n<br>";
if (system("zip -qr -5 $repsauvegarde$ziplogs $logs1"));
if (system("zip -qr -5 $repsauvegarde$ziplogs $logs2"));
echo "\n<br>";
echo "It is over. You can fetch the FTP backup in the folder $repsauvegarde .\n<br>Don't forget to delete these files from your server via FTP as they may contain passwords or personal information.\n<br>\n<br></body></html>";
?>

Use Tar, GZIP or BZ2 instead of ZIP. There is a difference between tar and zip: there is a limit to combining several different folders in the same archive. With tar, you can only compress one directory with its sub-folders or combine several tar archives. So, for my example backup script we can replace line 20:

Code PHP 
if (system("zip -qr -5 $repsauvegarde$zipuploads $uploads"));

by this for the Tar Gzip:

Code PHP 
if (system("tar -czf $repsauvegarde$zipuploads.tar.gz $uploads"));

or this for the Tar Bzip2:

Code PHP 
if (system("tar -cjf $repsauvegarde$zipuploads.tar.bz2 $uploads"));

Note that tar bzip2 compresses better than tar gzip, which is also often more effective than Zip.

ATTENTION: don't try to make a ZIP file of all your hosting, it might not work. There are 2 reasons for this: the execution time of a script is often limited from 30 to 120 seconds and the RAM memory allocated to processing the script and handling the files is limited depending on your hosting offer (from 32 MB to 256 MB, which is insufficient for a backup of your website). To know these limits, create a file info.php with the following code

Code PHP 
<?php
phpinfo();
?>

Send this file by FTP, open it with your web browser, and look for the lines max_execution_time (script execution time), memory_limit and post_max_size to know the maximum size of the file that can be processed.

2- Preparing the MySQL database backup

Backing up the MySQL database is called a “dump”. It can be done from your phpMyadmin interface, or from a php script.

a) Backup with PHP Script

The advantage of backing up by PHP script is its speed and the ability to control some options that can't be managed with phpMyadmin, in particular the problem of encoding accented characters (called character set). This is why I prefer this solution.

Code PHP 
<?php
/* Modify your MySQL settings */
$db_server   = "ServeurMySQL";
$db_name     = "NomDeLaBaseSQL"; 
$db_username = "IdentifiantSQL";
$db_password = "MotDePasseSQL";
$db_charset = "utf8"; /* put utf8 or latin1 */
/* That is it. Place this file by FTP somewhere on your web server, in a discreet location. */
/* Then open it with your web browser and follow the instructions. */

echo "<html><body>This script creates a backup of the database with the character set encoding $db_charset .  \n<br>\n<br>The backup file is in the same place as this script.  \n<br>\n<br>";
echo "Your database is being backed up.......\n<br>";
if (system("mysqldump --host=$db_server --user=$db_username --password=$db_password -C -Q -e --default-character-set=$db_charset $db_name | gzip -c > $db_name-$db_charset.sql.gz"));
echo "\n<br>";
echo "It is over. You can fetch the backup file. It is called: <a href=\"$db_name-$db_charset.sql.gz\">$db_name-$db_charset.sql.gz</a> (right-click, and save as... or save the link target as...) \n<br>\n<br>Don't forget to delete this file from your server via FTP as it contains passwords.\n<br>\n<br></body></html>";
?>

b) Backup by phpMyadmin

To make a backup of your current MySQL database connect to your phpMyadmin interface. Enter the name of the SQL database and its password, then at the top of the left column, click on the name of the database. At the top of the central page, click on the “Export” tab. The Export page displays a simplified version that should be sufficient most of the time. Choose to export in SQL format and click on the Run button. To select specific tables, choose the custom method and check the boxes of the tables you want to export. Save the output to a file, with a character set that should be UTF-8 by default, and if the database is large, you can compress it to GZIP format. The other default checkboxes should be acceptable, unless you know what you are doing, and then click the Run button. And don't forget to log out when you are done by clicking on the Log Out icon in the top left corner.

After the backup, the recovery…

Restoring your website and SQL database (Part 2)

3- Restoring the website

This is easy. First of all, if you have changed the file permissions to 404 or 444 and folder to 505 or 555 as explained in the article, you will not be able to delete or replace them. Redo the commands by giving permission 604 or 644 to files and 705 or 755 to folders. Then completely erase your website. You don't know what traces and hidden files the hacker has left, you have to rebuild on a sound basis. Then, by FTP send your backup to your site. Then change the file and folder permission as mentioned above.

4- Restoring the SQL database

If your backup is up to date, you have peace of mind. If your site is very active and too much time has passed between your last backup and the hacking, make a backup of the week-old database as explained above. However, you will not be sure that this database is healthy.

a) Restoring by phpMyadmin

Unlike backup, restoring via phpMyadmin has a limit on the maximum upload file size. This size varies depending on your hosting plan from 16 to 64 MB most of the time. To find out this limit, create a file info.php with the following code

Code PHP 
<?php
phpinfo();
?>

Send this file by FTP, open it with your web browser, and look for the line upload_max_filesize to know the maximum size. If your MySQL backup is below this limit, you can do it.

A tip if your “dump” or backup was done with phpMyadmin, you can restore in the same way, otherwise, go to the PHP scripting method explained in the next section.

Deleting the SQL database
Login to your phpMyadmin interface, enter the name of the SQL database and its password, then at the top of the left column, click on the name of the database. Then in the main window, at the bottom of the page, under the checkboxes column, click on “Check all” and in the menu that follows, choose “Delete table”. Confirm the deletion of “Tables” by clicking on “Yes”.

Restoring the base
You will import the new database. Click on the “Import” tab. Click on “Browse” to select the backup on your computer. WARNING: if the backup has just been made with phpMyadmin, i.e., having followed the procedure described above, select the character set “utf8” (there is a good chance that this character set is the right one, otherwise take “latin1”). If the backup was made otherwise, select the corresponding character set. Otherwise, accented characters will not display well on the website. Click Run. And don't forget to log out once you are done by clicking on the Log Out icon.

b) Recovery by PHP script

If you have exceeded the limit because your SQL database is too big, an excellent script can help you, it is called BigDump of which you can download the latest version 0.36b here. The interface is in English. You just have to enter the parameters of your SQL database and choose the right character set “utf8” or “latin1”. If you used the above backup script, you know which character set you used, whereas you are less sure with phpMyadmin.

Deleting the SQL database
Login to your phpMyadmin interface, enter the name of the SQL database and its password, then at the top of the left column, click on the name of the database. Then in the main window, at the bottom of the page, under the checkboxes column, click on “Check all” and in the menu that follows, choose “Delete table”. Confirm the deletion of “Tables” by clicking on “Yes”. And don't forget to log out when you are done by clicking on the Log Out icon.

Restoring the base
By FTP, send bigdump.php and your SQL backup in the same folder. With your web browser, open bigdump.php, it should list your database. Then click on Start Import. Usually, everything should go well and a window should tell you that the database has been restored. Otherwise, you will have to make a backup table by table of your SQL database to reduce the size of the files. Just do it with phpMyadmin as explained above. If this is not enough, check the forums, there are many tips. Then by FTP, delete bigdump.php and the SQL backup. Never keep bigdump, a hacker could use it very easily to control your website.
A tip, check how accented characters are displayed on your website after restoration. Because of some inconsistencies between latin1 and utf8, we can have bad surprises. For example, I have a blog where everything is in utf-8 (text, html charset, SQL interclass in utf-8, etc.), everything is consistent since its creation. If I make a dump (backup) of the database in utf8 with my script, then a restore with bigdump.php in utf8, the website doesn't display accented characters well. I had to do a dump in latin1 then restore with bigdump.php in utf8 to get back to normal. Think about it.

5- Summary. You have on your computer

a) your complete website (HTML files, images, and other files),
b) all updated files of your blog, forum, cms, e-commerce with config, htaccess, plugins, modules, themes, templates and your modifications,
c) the upload folders, logs, etc. that you regularly update,
d) a recent backup of your MySQL database.

6- If you are doing the restoration after a hack

Change your FTP and MySQL passwords. Then, don't forget to edit the config.inc.php or equivalent files that need your new SQL password.

“If-” you'll be a Man…

Re-discover Rudyard Kipling's famous poem “If- you'll be a Man, my son” which has inspired so many generations. It celebrates the courage to overcome hardship.

I've decided to succeed

I have put together 10 tried and tested tips for finding the will to successfully achieve your ambitions and overcome obstacles in your personal and professional life.

Gourmet treats

Exquisite recipes for mini-cakes (madeleines, financiers, biscuits, cakes, muffins) and other delicacies (croissants, brioche, traditional cakes…).