Implementation Tips - MySQL

This section will discuss the implementation details of the previous script, and how you can use it to automatically back up your MySQL database using PHP and Cron Job.

Step 1. Download the complete script as discussed in this tutorial: http://www.php-developer.org/php-script-to-backup-mysql-database-using-hosting-cron/

Step 2. Right click on the zip file and click "Extract here." After unzipping the package, you will see a folder named "backuprestricted," which contains backupmysql.php and .htaccess.

Step 3. Open backupmysql.php and assign appropriate MySQL database connection parameters (username, password, hostname, database name).

Step 4. In backupmysql.php, specify the path to the backup folder. Refer to the guide in the previous section.

Step 5. Save backupmysql.php and upload the folder to your website root directory. Use SSH if possible, because it is more secure.

Step 6. Using an SSH client, set the file permission of the following files and folders:

• .htaccess = 644
  
• backupmysql.php = 755
  
• backuprestricted folder = 755

This is what your files will look like after changing permissions:

Step 7. It is now the time to configure your cron. You will need to consult your hosting provider for detailed cron instructions.

The screen shot below applies to Agilityhoster.com, as well as other hosting providers with similar cron features:

The command is simply a full server path to the PHP script. And the cron is configured to run every first day of the month. Do not forget to enabled email reporting and specify your email address.

If you are using Go Daddy hosting, the cron command will be:

/web/cgi-bin/php5 "$HOME/html/backuprestricted/backupmysql.php"

Of course, it already assumes you are using PHP 5.

Troubleshooting Tips

The best data gathering tool for troubleshooting this application is to enable reporting of cron by email. This means that after cron processing, a report is generated and sent to the webmaster's email address.

Common errors:

Publicly accessible backup folder. To check, open your URL using a web browser; it should give a 403 forbidden error.

Or use this tool: http://gsitecrawler.com/tools/Server-Status.aspx
Enter the address of your PHP script http://www.example.com/backuprestricted/backupmysql.php 

It should give a 403 forbidden response, or 404 in some servers. If it returns 200 OK status, make sure your .htaccess uses this line:

deny from all

Important: Do not remove the .htaccess

No output from the cron. You need to check the cron report sent to your email. If the report tells you  something like "permission denied," then you need to set the folders and script to use a file permission of 755.

If it uses an error code of 127, then the path to your backup script is not correct. Make sure you are using the correct server path.

Normally, you will receive this email content if the script has been successfully executed by the cron (no error):

Content-type: text/html
0

As a troubleshooting tip, you can set the cron first to operate every hour or even every 15 minutes (if your hosting cron allows) so that you can see if the MySQL database backup really works.

Once it is fully working, set a realistic time frame, such as monthly, for your backups.

You can even write a script to delete old MySQL backup files in your backup folder, again using cron. Do not forget to download important MySQL database backup files to your Desktop.