Use PHP to Backup your MySQL Database

If you don’t backup your databases regularly, shame on you. You should!

The code below will allow you to generate a backup as often as you’d like. It only makes sense to run this via a CRON job. This is pretty easy to do and you can set the frequency as you wish.

The backup files will be stored in the same directory as this script.

Please leave feedback and let me know if this works for you!

Here’s the PHP code:

Select Code
backup_tables('localhost','username','password','blog');


// Backup the entire database or just a specific table.  
function backup_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);
    }
    
    //This method is completed for each table
    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.= "\n\n".$row2[1].";\n\n";
        
        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] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j<($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }
    
    //Now, we'll save the file
    $handle = fopen('backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

One comment on “Use PHP to Backup your MySQL Database

  1. Ben Avery says:

    A very good backup method, thanks!

    I’ve been looking for a cross operating system compatible approach to this.

    Any chance of an equally good way to reverse the process to create a “restore” database?

    I have a local MySQL server on a personal computer which runs a very important schedule which gets updated regularly. As it runs on a Windows OS for this project, I’ve been using the php “system” command to backup the database.

    Could a restore similar to the way you execute the backup above be written to provide a similar functionality to a cross operating system setting?

    Ben

Leave a Reply

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


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>