Skip to content

Protect Your Website: A Simple PHP Script to Periodically Backup Your MySQL Database

Losing your website can be a nightmare for any website owner. Unfortunately, I experienced this firsthand when I accidentally executed the rm -rf command on my public_html folder while working on my WordPress site. In a split second, my website disappeared into thin air, leaving me with a sinking feeling in my stomach.

After trying every trick in the book to recover my website, I realized that I had lost everything, including my precious database. That is when I decided to set up a backup system to prevent such disasters from happening again.

I researched various backup solutions but decided to write a PHP script to backup my MySQL database periodically. Here is why I chose this approach:

  1. Easy to set up: The PHP script I wrote was straightforward to set up, even for a non-technical person like me. All I had to do was edit the configuration file with my database credentials and set up a cron job to run the script at regular intervals.
  2. Efficient: The script only backs up the necessary tables in my database, which saves me a lot of time and disk space. I don’t have to worry about backing up unnecessary data, making the process much more efficient.
  3. Cost-effective: Writing my own PHP script was free, which meant I didn’t have to spend any money on a backup solution. This was particularly important for me as a small business owner who wanted to minimize expenses.
  4. Peace of mind: Knowing that I have a backup of my database gives me peace of mind. I no longer have to worry about losing my website or data, which allows me to focus on growing my business.

Losing my website was a painful experience, but it taught me a valuable lesson about the importance of backups. By writing a simple PHP script to periodically backup my MySQL database, I can now rest easy knowing that my website and data are secure. If you haven’t already done so, I highly recommend that you set up a backup system for your website. It may just save you from a nightmare like mine.

Show me the code!

// MySQL database credentials
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'your_database';

// Backup filename
$date = date('Y-m-d_H-i-s');
$filename = $database . '_' . $date . '.sql';

// MySQL dump command
$command = "mysqldump --host=$host --user=$username --password=$password $database > $filename";

// Execute command

How do I use it?

To use this script, replace the database credentials with your own and save it as a PHP file (e.g., backup.php) on your server. Then, set up a cron job to run the script at your desired intervals (e.g., once a day or once a week).

Note that this script only backs up the database and not the files on your server. You may want to consider backing up your files as well, either manually or through a separate backup solution.

How to schedule it to run 4 AM every morning?

Here’s an example cron job that will run the PHP script every day at 4 AM Pakistan Standard Time:

0 4 * * * /usr/bin/php /path/to/backup.php >/dev/null 2>&1

In this example above, 0 4 * * * specifies the time and date the cron job will run, which translates to “run at 4:00 AM every day”. /usr/bin/php is the path to the PHP binary, and /path/to/backup.php is the path to your PHP backup script. The >/dev/null 2>&1 part is used to redirect the script’s output to /dev/null, which discards it, so you don’t receive any emails with output from the cron job.

Make sure to replace /path/to/backup.php with the actual path to your PHP script. You can add this cron job by editing your crontab file using the crontab -e command.

Read more about crontab here.

GitHub repo

Leave a Reply

Your email address will not be published. Required fields are marked *