JOAOSANTACRUZ.COM

Programmatically Backup mysql database with PHP

This script will perform a backup from a given database using PHP


						

<?php

backup_tables(`localhost`, `root`, ``, `mydbname`);

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);
        
    }

    
    // cycle through
    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.= " " . $row2[1] . "; ";
        
        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(" ", " ", $row[$j]);
                    
                if (isset($row[$j]))
                {
                    $return.= `"` . $row[$j] . `"`;
                }
                  else
                {
                    $return.= `""`;
                }
                
                if ($j < ($num_fields - 1))
                {
                    $return.= `,`;
                }
                }
                
                $return.= "); ";
            }
        }
        
        $return.= " ";
        
    }

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

}

?>

Go Back