Papermashup

Subscribe


Tweets


"RT @bethgordon: .@Tesco Please rethink archaic 'Approved by Mums'. Pretty sure it's not just mothers who make childcare decisions. https://…"

@ashleyford 4 days ago

"Is there ever a time that @DFS doesn't have a sale on?"

@ashleyford 1 week ago

Designer and web developer, Co-founder and Technical Director at Harkable.com. Previously I worked at Spotify, MySpace and InMobi. Contact me - ashley[at]papermashup.com

Papermashup

Creating Automatic MySQL Database backups

Choose a specific table to backup or the whole database

AshleyAshley

Every database needs to be backed up and if you’re currently doing it manually you can stop because there’s a simple solution using PHP for small scale sites. With this script you can either pass a specific table to backup or choose to backup the whole database.

How to implement

The best way to use the script below is to copy the code and put it in a folder called ‘database_backups’ as shown below. Then whenever you call the backup.php file it will create a database backup which is stored on your server. I’d recommend downloading these database backups periodically so you have a copy stored safely elsewhere incase you lose your hosting.

Using CRON

Cron is a time-based job scheduler which allows you to run certain scripts at specific times. For example I have my database backups running at a time when traffic levels are low. You could setup a CRON job if your web host permits by running the following command. Change the link to point to the backup file in your server.


wget -O /dev/null http://your-site.com/database_backup/backup.php

The Code



backup_database_tables('HOST','USERNAME','PASSWORD','DATABASE', '*');

// backup the db function
function backup_database_tables($host,$user,$pass,$name,$tables)
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}

//cycle through each table and format the data
foreach($tables as $table)
{
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);

$return.= 'DROP TABLE '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "nn".$row2[1].";nn";

for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("n","\n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");n";
}
}
$return.="nnn";
}

//save the file
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}

Designer and web developer, Co-founder and Technical Director at Harkable.com. Previously I worked at Spotify, MySpace and InMobi. Contact me - ashley[at]papermashup.com

Comments 16
  • Enamul Hoq
    Posted on

    Enamul Hoq Enamul Hoq

    Reply Author

    Wow ! very great post.. I was looking for a way to backup my MySQL databases, but it’s many of the methods are too difficult . Then i hope that it’s very helpful so thanks a lot for sharing this with us


  • Dmitry Goryachev
    Posted on

    Dmitry Goryachev Dmitry Goryachev

    Reply Author

    After line:

    mysql_select_db($name,$link);

    need add this lines:

    $r = mysql_query(‘SHOW VARIABLES LIKE “character_set_database”;’);
    $variable = mysql_fetch_array($r);
    $charset = $variable[‘Value’];
    mysql_query(“SET NAMES ‘$charset’;”);

    It is to be unloaded at the correct encoding


  • Vipin
    Posted on

    Vipin Vipin

    Reply Author

    Nice…


  • Wojega Allan
    Posted on

    Wojega Allan Wojega Allan

    Reply Author

    Thanks a million


    • luoly
      Posted on

      luoly luoly

      Reply Author

      Thanks for your greate blog. I was looking for a way to backup my MySQL databases, but many of the methods are too difficult or too trouble. Now I’m using a good software “Automatic Backup Scheduler for MySQL”(http://www.databasethink.com), I feel it easy-to-use and it’s a powerful software.


  • Tony
    Posted on

    Tony Tony

    Reply Author

    Is there a way to modify this code so it allows for more than one database? So essentially I can configure it so it downloads more than one separate database.


    • Gabe
      Posted on

      Gabe Gabe

      Reply Author

      Just call this from inside a loop.

      Something like this: (I use the DB name as the key instead of a meaningless integer)
      $dbs = array(‘my-db1’=>array(‘HOST’=>’my-host’, ‘USER’=>’user’, ‘PASSWORD’=>’password123’), …);

      foreach ($dbs AS $db=>$loginData) {
      backup_database_tables($loginData[‘HOST’], $loginData[‘USER’], $loginData[‘PASSWORD’], $db, ‘*’);
      }

      PS. Ashley, I think there is a bug with one of your popups. When attempting to reply I kept getting sent to a different page mid-reply. The page was one that showed up in a little popup in the lower right. I’m using Google Chrome on Win 7 Pro.


  • pablo
    Posted on

    pablo pablo

    Reply Author

    realmente bueno y muy útil.


  • Edmilson
    Posted on

    Edmilson Edmilson

    Reply Author

    Muito útil, para os que tiveram problemas é so alterar:

    44 $row[$j] = ereg_replace(“n”,”\n”,$row[$j]);

    por

    44 $row[$j] = preg_replace(“/n/”,”\n”,$row[$j]);


  • Alejandro Arauz
    Posted on

    Alejandro Arauz Alejandro Arauz

    Reply Author

    Nice script but as others pointed out you could end up with a corrupted backup file. If you are managing a small site you could try using a tool like MySQLBackupFTP (http://mysqlbackupftp.com) to schedule your backups.
    The good thing about this tool is that it connects to MySQL through phpMyAdmin and the free version allows you to schedule up to two databases. The tool also send the compressed backup file to a remote FTP server, you can achieve the same thing adding a few lines to your script but maybe it would be easier with the tool.

    Aafrin, there are several functions that are deprecated on php 5.3 and “ereg_replace” is one of them. Here is more info about some possible replacements http://php.net/manual/en/function.ereg-replace.php


  • Suneel Pervaiz
    Posted on

    Suneel Pervaiz Suneel Pervaiz

    Reply Author

    very nice posting


  • Aafrin
    Posted on

    Aafrin Aafrin

    Reply Author

    I am getting
    Deprecated: Function ereg_replace() is deprecated error when running the script on php 5.3

    Please advice further on this


  • Alexander
    Posted on

    Alexander Alexander

    Reply Author

    I agree too with Luis


  • Olivier
    Posted on

    Olivier Olivier

    Reply Author

    I agree with Luis, mysqldump is more powerfull. Your solution can be used for small DBs, but as you don’t lock anything, it can not be used for e-commerce projects for instance. Risk of DB / table corruption.
    Hope this helps too :)


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      Hey Olivier, Luis,

      Great point! mysqldump is a far better method, especially when scale is involved. I’m just showing how this can be done using PHP for a simple small site.

      Ashley


  • Luis
    Posted on

    Luis Luis

    Reply Author

    # Set a cron with a simple command
    # Dump much more efficient , PHP is really slow doing that
    # Export in Gzip
    # A lot of possible options in mysqldump …

    mysqldump -u root -ppassword –databases mydatabase -c –tables agx_psms_sales | gzip > /$(date +%Y-%m-%d).sql.gz

    I hope this can help. :)