← Back to EasyCron.com

Nov 27, 2011

A simple PHP script to periodically backup mysql database

Periodically backing up database is crucial to data safety for DBAs or webmasters. But manually doing this is often boring and easily forgotten.

Below script works on Linux system to backup mysql database. With an easy-to-use webcron, you can setup periodical backup jobs which will happen automatically.

Steps to take:

1. Create an empty PHP file named with whatever you like (e.g. example.php).

2. Copy the following code into the PHP file.

<?php

/* 
 * This script only works on linux.
 * It keeps only 31 backups of past 31 days, and backups of each 1st day of past months.
 */

define('DB_HOST', 'localhost');
define('DB_NAME', 'your_database_name');
define('DB_USER', 'your_database_username');
define('DB_PASSWORD', 'your_username_password');
define('BACKUP_SAVE_TO', 'backup_storage_path'); // without trailing slash

$time = time();
$day = date('j', $time);
if ($day == 1) {
    $date = date('Y-m-d', $time);
} else {
    $date = $day;
}

$backupFile = BACKUP_SAVE_TO . '/' . DB_NAME . '_' . $date . '.gz';
if (file_exists($backupFile)) {
    unlink($backupFile);
}
$command = 'mysqldump --opt -h ' . DB_HOST . ' -u ' . DB_USER . ' -p\'' . DB_PASSWORD . '\' ' . DB_NAME . ' | gzip > ' . $backupFile;
system($command);

?>

3. Change the parameters (DB_HOST, DB_NAME, DB_USER, DB_PASSWORD, BACKUP_SAVE_TO) in the script to reflect the settings of your environment. You would like to keep the database backups from accessing by web visitor, so you may need to set a private path to BACKUP_SAVE_TO or put a .htaccess file with following content
Order deny,allow
Deny from all
into the backup storage folder.

4. Put the PHP file on your server (If you want to use a webcron to trigger the auto backup, you need to make sure the file is accessible via HTTP). If your backup storage folder is not yet existent, please create it. Your folder should be made writable by PHP.

5. You can both use Linux's Cron or a webcron to trigger to script. If you need a webcron, just go to EasyCron.com and register an account to proceed.

Advantages:
  • Great performance. Because it calls MySQL's native command to perform the backup.
  • Saves your disk space as backups are gzipped.
  • Uses a way to keep both last 30 days' backups and each 1st day's backup of past months. It balances the data importance (through earlier to later time) and disk space cost.

Requirements:
  • Linux (Though the script can be amend to work on other platforms).
  • PHP + MySQL

8 comments:

  1. Why have you remove comments? That does not give confidence

    ReplyDelete
  2. We hope we can keep the comments. But unfortunately all comments are just SPAMs saying "Want PHP training? Access http://xxxxx" :(

    ReplyDelete
  3. Yes, it worked. I run the script manually.
    can you please tell me what command i have to put in cronjob command box?

    ReplyDelete
  4. Pls share the appropriate cronjob command...

    ReplyDelete
  5. Using crontab, you could add this:
    12 0 * * * /usr/bin/php PATH_TO_YOUR_BACKUP_PHP_SCRIPT

    Otherwise, you could just place the backup php script on your web server, and add a URL pointing to it on easycron to trigger it.

    ReplyDelete
  6. Um código que funciona em Windows (A code that works in Windows:

    Tiago França,
    Fonte/Source:http://www.howi.in/2016/12/auto-backup-mysql-database-using-php.html





    select_db($name);
    $mysqli->query("SET NAMES 'utf8'");

    $queryTables = $mysqli->query('SHOW TABLES');
    while($row = $queryTables->fetch_row())
    {
    $target_tables[] = $row[0];
    }
    if($tables !== false)
    {
    $target_tables = array_intersect( $target_tables, $tables);
    }
    foreach($target_tables as $table)
    {
    $result = $mysqli->query('SELECT * FROM '.$table);
    $fields_amount = $result->field_count;
    $rows_num=$mysqli->affected_rows;
    $res = $mysqli->query('SHOW CREATE TABLE '.$table);
    $TableMLine = $res->fetch_row();
    $content = (!isset($content) ? '' : $content) . "\n\n".$TableMLine[1].";\n\n";

    for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0)
    {
    while($row = $result->fetch_row())
    { //when started (and every after 100 command cycle):
    if ($st_counter%100 == 0 || $st_counter == 0 )
    {
    $content .= "\nINSERT INTO ".$table." VALUES";
    }
    $content .= "\n(";
    for($j=0; $j<$fields_amount; $j++)
    {
    $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) );
    if (isset($row[$j]))
    {
    $content .= '"'.$row[$j].'"' ;
    }
    else
    {
    $content .= '""';
    }
    if ($j<($fields_amount-1))
    {
    $content.= ',';
    }
    }
    $content .=")";
    //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
    if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num)
    {
    $content .= ";";
    }
    else
    {
    $content .= ",";
    }
    $st_counter=$st_counter+1;
    }
    } $content .="\n\n\n";
    }
    //$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
    $date = date("Y-m-d_H:i:s");
    $backup_name = $backup_name ? $backup_name : $name.".$date.sql";
    header('Content-Type: application/octet-stream');
    header("Content-Transfer-Encoding: Binary");
    header("Content-disposition: attachment; filename=\"".$backup_name."\"");
    echo $content; exit;
    }
    ?>

    ReplyDelete
  7. This works for me. did some tweak. But everything is fine. Backing up remote mysql db. :)

    ReplyDelete