Mysql Database backup script

[chenci]

I use this db backup script when php4xx was common, and everyone was using it. It work fine back then, but now, it doesn't anymore with php5xx. This is the script

function backup_all()
{
global $file_folder, $gmt;
$dateforbackup = gmdate("Y.m.d-H.i.s", mktime(date("H")+ $gmt, date("i"), date("s"), date("m"), date("d"), date("Y"))); 
if (isset($_POST['backup']))
{
$data = ("
###################################
##Backup de bases de datos Completo
##
##Backupdate: '$dateforbackup'
##Database: ".s('dbname')."
###################################\n
");


$tabeller = mysql_list_tables(s('dbname'));
$tab_navn = array();
for ($i = 0; $i < mysql_num_rows($tabeller); $i++) {
     $tab_navn[] = mysql_tablename($tabeller, $i);
}

foreach ($tab_navn as $tabell) {
$query = mysql_query("SHOW CREATE TABLE $tabell");
$result = mysql_fetch_array($query);

$data .= ("DROP TABLE IF EXISTS $tabell ;\n");
$data .= ("$result[1];\n\n");

    $felt_navn = array();
    $query = mysql_query("SELECT * FROM " .s('prefix'). "$tabell");
    for ($i = 0; $i < mysql_num_fields($query); $i++) {
        $felt_navn[] = mysql_field_name($query, $i);
    }
    
    while($result = mysql_fetch_array($query)) {
        $data .= ("INSERT INTO `$tabell` VALUES (");
        foreach ($felt_navn as $felt_name) {
            $result[$felt_name] = str_replace("'", "\'", $result[$felt_name]);
            $data .= ("'$result[$felt_name]'");
            if(next($felt_navn)) {
                $data .= (", ");
            }
        }   
        $data .= (");\n");
    }
    mysql_free_result($query);
}
$data .= ("\n");

$filnavn = $dateforbackup."_Backup_Completo.inc";

    if (!$action = fopen($file_folder."/".$filnavn, 'w')) {
        print("Can not open $filnavn");
        echo "<h1><a href=\"index.php?action=backup\">". l('back') ."</a></h1>";
    } elseif (fwrite($action, $data) === FALSE) {
        print("Can not write to $filnavn");
        echo "<h1><a href=\"index.php?action=backup\">". l('back') ."</a></h1>";
    } else 
    {
    echo "<h3>".l(back_success)."<i>".s('dbname')."</i>".l(have_success)."</h3>";
    echo "<h1><a href=\"index.php?action=backup\">". l('back') ."</a></h1>";
    ?><meta http-equiv="refresh" content="2; url=index.php?action=backup"><?
    }     
    fclose($action);
}   
}

The problem is in the backup file, it has this:

###################################
##Backup de bases de datos Completo
##
##Backupdate: '2009.07.26-00.24.03'
##Database: killerkaos_news
###################################

DROP TABLE IF EXISTS articles ;
CREATE TABLE `articles` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  `text` text,
  `textlimit` int(5) NOT NULL default '0',
  `date` datetime default NULL,
  `category` int( NOT NULL default '0',
  `position` char(3) default NULL,
  `displaytitle` char(3) NOT NULL default 'YES',
  `displayinfo` char(3) NOT NULL default 'YES',
  `commentable` varchar(5) NOT NULL default 'YES',
  `image` text NOT NULL,
  `swf` varchar(30) default NULL,
  `publishuser` char(3) NOT NULL default 'YES',
  `postedby` int(3) NOT NULL default '0',
  `materia` char(3) NOT NULL default '',
  `materialogo` varchar(30) NOT NULL default '',
  `private` char(3) NOT NULL default 'NO',
  `lastupdate` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INTO `articles` VALUES ('1', 'Apertura', '<p>Nueva apertura de Craken.com.ar</p>', '500', '2007-09-01 21:12:16', '0', '1', 'YES', 'YES', 'YES', '', '', 'YES', '1', 'NO', '', 'NO''2009-02-28 16:38:45');

Note the last part, there is a comma missing so it can't insert it correctly. It happens with all the INSERT INTO of the backup file. And i just can't fix it. It's a shame i can't backup all my db in php5xx. Any sugestions?

Mysql version: 5.0.81

Thanks

[irvieto]

what about a mysql_dump ?

[ldougherty]

It is rather simple to backup your databases using mysqldump

 

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

 

In essence just execute

 

mysqldump -u<user> -p<password> -h<host> <dbname> > <dbname>.sql

 

Then you can get nifty and add this to a cron job so it creates daily backup and take it one step further by renaming the file(s) to include the date and only keep the backups for x amount of time.