Mysql Database backup script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
chenci
Forum Newbie
Posts: 5
Joined: Sun Jul 26, 2009 4:50 pm

Mysql Database backup script

Post 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
DaiLaughing
Forum Commoner
Posts: 76
Joined: Thu Jul 16, 2009 8:03 am

Re: Mysql Database backup script

Post 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.
chenci
Forum Newbie
Posts: 5
Joined: Sun Jul 26, 2009 4:50 pm

Re: Mysql Database backup script

Post 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
Post Reply