MySQL: How to automate backups

Backing up your files will save your day, but think about losing a client’s database table? Or an entire database? This will likely be irreversible, and may even hurt the core of your clients’s business. As you know, SQL injection is real, and so is hardware failure and human error. With that in mind, I’ll show you how to avoid such a catastrophe with a few steps.

Let’s consider you are running an Apache server, with PHP and MySQL.

Step 1.
Create a new directory to hold the backup files. For example: db_backups

Step 2.
Inside this directory, create a php file and name it index.php, code follows:

<?php
function db_dump() {
$db_host   = 'localhost';
$db_user   = 'username';
$db_pass   = 'password';
$databases = array(
                  'database_name',
                  'another_database_name',
                  'one_more_database'
                  );
              
foreach ($databases as $db_name) {      
         $filename = $db_name . '_' . date('Y-m-d-H-i') . '.sql';
         $cmd = 'mysqldump -u ' . $db_user . ' -h ' . $db_host . ' --password=' . $db_pass . ' ' . $db_name . ' > ' . $filename;
         $dump_status = (passthru($cmd) === false) ? 'No' : 'Yes';
         echo 'command executed? ' . $dump_status . '
'; } } db_dump(); ?>

Step 3.
Create a text file and name it .htaccess. Add the following Apache directive.

<Files ~ "\.(sql)$">
 Order deny,allow
 Deny from all
</Files>

The <Files> directive limits the scope of the enclosed directives by filename. In other words, using this directive with a simple regular expression, will prevent hackers and anyone else to access files with .sql extension. As you can see, this is extremely important.

Step 4.
Last but not least, we need to automate this process. We can do so with a simple Cron task.

// option 1
php5 /home/path_to_your/db_backup/index.php

// option 2 ( using cURL )
curl http://domain.com/path_to_your/db_backup/index.php

Your web hosting should provide you with a graphic interface to schedule the frequency and date for the each Cron job. If not, please take note of the interval cheat sheet below. It’s easy to implement.

Interval cheat sheet
* * * * *	every minute
0 0 * * *	every day at midnight
0 5 * * *	every day at five o'clock in the morning
(11 = 11AM, 23 = 11PM)
0 0 * * 0	every Sunday at midnight
(0 = Sunday, 1 = Monday, ...)

MySQL: How to automate backups

Leave a Reply