Back up your WordPress database - Administration

A number of database backup solutions exist for WordPress. One of the free, secure and efficient ways you can periodically backup your WordPress MySQL database involves using PHP. The PHP script is executed periodically -- either weekly or monthly, depending on your setting -- using your hosting cron.

Once the PHP script executes, it dumps your WordPress MySQL database into a secure location on your server. This is usually situated above the webroot, since this cannot be accessed by a browser or the public. More details about this will be discussed later.

Here are the detailed steps you must take to efficiently back up your WordPress database.

1. Download this WordPress database backup script: http://www.php-developer.org/wordpress-database-backup-php-script/

2. Unzip the package. What you should see is a folder named wordpressmysqlbackup, with two files inside it: wordpressbackup.php and .htaccess.

3. Open the wordpressbackup.php using your favorite PHP editor.

4. Scroll down until you see these lines:

$usernamewordpress = "Your WordPress MySQL database username";
$passwordwordpress = "Your WordPress MySQL database password";
$hostnamewordpress = "Your WordPress MySQL hostname";
$databasewordpress = "Your WordPress MySQL database name";

Replace the above values of the PHP variable with your WordPress MySQL database  credentials.

If you would like to back up another MySQL database in your server, aside from your WordPress database, then uncomment these lines (by removing the "//" before the variable):

//$username1 = "MySQL database username1";
//$password1 = "MySQL database password1";
//$hostname1 = "MySQL database hostname1";
//$database1 = "MySQL database name1";

5. Next, find this line:

$full_serverpath_to_backup= '/this/is/the/full_server_path_to/your/wordpressmysqlbackup/';

You need to define the correct path to your wordpressmysqlbackup folder in your server. This path SHOULD be ABOVE your normal WordPress files and the webroot files.

A correct example path for the wordpressmysqlbackup folder is shown below:

You will notice that the wordpressmysqlbackup folder is located in this path:

/home/wordpressmysqlbackup/

This is because it is under the main "home" directory in your Linux server. So the correct $full_serverpath_to_backup will be:

$full_serverpath_to_backup= '/home/wordpressmysqlbackup/';

Also, based on the website file structure screen shot, the wordpressmysqlbackup folder is placed ABOVE the main webroot files, where your normal WordPress files are located.

In the above example, the main WordPress files are inside the www web root folder.

6. Find this line in the wordpressmysqlbackup.php:

$deleteolddatabase_in_days=6;

This defines after how many days you wish to delete the old database backup in your server. The most recommended rule is that this number is one less than the cron interval settings.

Example: If you set your cron to run weekly (7 days), then you should set:

$deleteolddatabase_in_days =6

So if you set the cron to make database backups every two weeks (14 days), then set the variable to 13 days:

$deleteolddatabase_in_days =13

This method can prevent your server from being clogged with old database backups.

7. Upload the wordpressmysqlbackup folder (containing wordpressmysqlbackup.php and .htaccess) to the path defined by $full_serverpath_to_backup variable. Use your SSH client to upload files.

Set the following file permissions:

• wordpressmysqlbackup folder =755
  
• wordpressmysqlbackup.php =755
  
• .htaccess = 644

You can use an SSH client such as Filezilla to set file permissions (done by right clicking on the file/folder, and then clicking "File permissions").

In the second part of this tutorial, you will learn how to configure your hosting cron to execute this PHP script, and how to test your WordPress backups. You will need the following to run this PHP WordPress database backup script:

1. Apache server
   
   
2. Linux/Unix compatible server
   
   
3. At least PHP 5
   
   
4. At least MySQL database 5
   
   
5. Any WordPress version (latest version recommended for best security)