Page 1 of 1

Mysql Database backup script

Posted: Mon Jul 27, 2009 12:17 am
by 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

Code: Select all

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:

Code: Select all

###################################
##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(8) 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', '', [color=#FF0000]'NO''2009-02-28 16:38:45'[/color]);
 
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?
Thanks

Re: Mysql Database backup script

Posted: Mon Jul 27, 2009 12:48 am
by DaiLaughing
While you are trying to fix that script I suggest you backup with mysqldump or PHPMyAdmins Export tab. If you use PHPMyAdmin make sure you check the end of the backed up file to be sure that the PHP script did not time out.

Re: Mysql Database backup script

Posted: Mon Jul 27, 2009 12:51 am
by chenci
don't worry with my backups, i'm using my server which is php4, but i want to move to php5 newer servers. Actually i'm using phpmyadmin for backups in php5 test servers, but you know how slow is phpmyadmin, i just want with one click to have a backup file. :D