xoops forums

wishcraft

Module Developer
Posted on: 2010/2/12 6:16
wishcraft
wishcraft (Show more)
Module Developer
Posts: 3710
Since: 2007/5/18
#1

CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

This code is great for a cron job for your XOOPS System, it will create a backup of your MySQL Database on any Unix machine, I have it running as a cron on my systems. It is only compatible with XOOPS 2.3 and later, you can adjust it for earlier versions.

Place the code in a file called backup.php in your XOOPS_ROOT_PATH/cron as all XOOPS code has to operate within this area and run it going to http://www.yoursite.com/cron/backup.php

<?php

    
// Call File backup.php in your /public_html/cron path

    
include('../mainfile.php');
    
    
define('lnbr'"n");
    
    
set_time_limit(1200);
    
    
$path XOOPS_VAR_PATH.'/backups/'.strtolower(date('l')).'/'.strtolower(date('Y')).'/'.strtolower(date('m')).'/';
    
    if (!
is_dir($path))
        foreach(
explode('/'$path) as $folder) {
            
$tmp .= '/' $folder;
            
mkdir($tmp.'/'0777);
        }
        
    
$filename $path .XOOPS_DB_NAME.'_'.XOOPS_DB_PREFIX.'.sql';
        
    if (
file_exists($filename))
        
unlink ($filename);
        
    if (
$file fopen($filename'w+')) {
        
$dump mysql_dump(XOOPS_DB_NAME$file);
        
fclose($file);
        echo 
'Backup Successful wrote :: '$filename.' - Filesize ' filesize($filename);
    } else 
        echo 
'Backup Failed wrote :: '$filename.' - Filesize ' filesize($filename);

   function 
mysql_dump($database$file) {

      
$query '';

      
$tables = @mysql_list_tables($database);
      while (
$row = @mysql_fetch_row($tables)) { $table_list[] = $row[0]; }

      for (
$i 0$i < @count($table_list); $i++) {

         
$results mysql_query('DESCRIBE ' $database '.' $table_list[$i]);

         
$query 'DROP TABLE IF EXISTS `' $database '.' $table_list[$i] . '`;' lnbr;
             
fwrite($file$querystrlen($query));
         
$query lnbr 'CREATE TABLE `' $database '.' $table_list[$i] . '` (' lnbr;
             
fwrite($file$querystrlen($query));
         
$tmp '';

         while (
$row = @mysql_fetch_assoc($results)) {

            
$query .= '`' $row['Field'] . '` ' $row['Type'];

            if (
$row['Null'] != 'YES') { $query .= ' NOT NULL'; }
            if (
$row['Default'] != '') { $query .= ' DEFAULT '' . $row['Default'] . '''; }
            if (
$row['Extra']) { $query .= ' ' strtoupper($row['Extra']); }
            if (
$row['Key'] == 'PRI') { $tmp 'primary key(' $row['Field'] . ')'; }

            
$query .= ','lnbr;

         }

         
$query .= $tmp lnbr ');' str_repeat(lnbr2);

         
$results mysql_query('SELECT * FROM ' $database '.' $table_list[$i]);

         while (
$row = @mysql_fetch_assoc($results)) {

            
$query .= 'INSERT INTO `' $database '.' $table_list[$i] .'` (';

            
$data = Array();

            while (list(
$key$value) = @each($row)) { $data['keys'][] = $key$data['values'][] = addslashes($value); }

            
$query .= join($data['keys'], ', ') . ')' lnbr 'VALUES ('' . join($data['values'], '', '') . '');' lnbr;

         }

         
$query .= str_repeat(lnbr2);
        
fwrite($file$querystrlen($query));
        
$query '';
      }

      return 
true;

   }
?>

bjuti

Just can't stay away
Posted on: 2010/2/12 10:58
bjuti
bjuti (Show more)
Just can't stay away
Posts: 871
Since: 2009/1/7 2
#2

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

Great, 10x :)

BTW. Any solution for restore? It would be nice in case of emergency :)

BTW2. My database is more than 30mb packed with zip. How to restore the base when my provider give me only 8mb upload limit? :)

wishcraft

Module Developer
Posted on: 2010/2/12 14:04
wishcraft
wishcraft (Show more)
Module Developer
Posts: 3710
Since: 2007/5/18
#3

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

Generally I use SQLYog from Webyog.. Look it up in google. You set SQL Remote Access to yourself ie.. Through cpanel and you have a tunnel with this software for windows between you and your MySQL server.

You can Run large Batch Scripts with tools like this.. There are also these GUI tools available for Ubuntu, Debian and other forms of unix.

btesec

Friend of XOOPS
Posted on: 2010/2/12 14:23
btesec
btesec (Show more)
Friend of XOOPS
Posts: 623
Since: 2007/2/20
#4

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

Sounds like an excellent backup script. How are backups managed, is a new file created or is the file replaced. I ma be dum, but what about placing those backups outside the www root.
cheers

bjuti

Just can't stay away
Posted on: 2010/2/12 14:28
bjuti
bjuti (Show more)
Just can't stay away
Posts: 871
Since: 2009/1/7 2
#5

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

Tnx, I'll check that :)

wishcraft

Module Developer
Posted on: 2010/2/13 1:32
wishcraft
wishcraft (Show more)
Module Developer
Posts: 3710
Since: 2007/5/18
#6

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

btsec:

$path XOOPS_VAR_PATH.'/backups/'.strtolower(date('l')).'/'.strtolower(date('Y')).'/'.strtolower(date('m')).'/'


The backups are stored in the VAR PATH, this is your xoops_data folder which normally lives outside your root path anyway..

It creates the folders for the following. Image it is todays date Saturday,feburary 13, 2009

It will store the files in for this day:

/xoops_data/backups/saturday/2009/02/

-- for each day and database the path revolves for the date, so backups done on a Saturday are in Saturday.. backups for Friday are in Friday then separated by the year and month.

If the backup file already exists it is deleted and replaced.

dbman

Friend of XOOPS
Posted on: 2010/2/13 3:39
dbman
dbman (Show more)
Friend of XOOPS
Posts: 172
Since: 2005/4/28
#7

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

This would be a nice addition to XoopsCare.

xsell

Quite a regular
Posted on: 2010/2/13 4:31
xsell
xsell (Show more)
Quite a regular
Posts: 245
Since: 2008/9/2 1
#8

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

Thx for The Code .. but Take This .

1- Wht if someone somehow knows where is the file located.. and plays it 50 Times .. Or Million ? Wht going to happen to ur server .. So i would Suggest to creat e Simple code to check if the file was run before in X time ..

$run_already 'check.txt';
if (
file_exists($run_already)) {
    
$lastRun file_get_contents($run_already);
    if (
time() - $lastRun >= 86400) { 

       
// here  your code
        
         //update lastrun with current time
         
file_put_contents($run_alreadytime());
    } 
}


2- Email the Backup to the admin email...

ghia

Community Support Member
Posted on: 2010/2/13 10:53
ghia
ghia (Show more)
Community Support Member
Posts: 4954
Since: 2008/7/3 1
#9

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

-1- That disallows to have a recent backup during the day (at the moment the administrator chooses). Also some variation in the backup time may defy your daily backup.
Protection for that could be by given the backupfile a unique name (eg 1a2b3cbackup.php). And/Or by checking the (XOOPS) user to be the administrator or CRON (don't know if that is possible: checking invoked by localhost?).

-2- Emailing very large files is not very practical, but mailing the completion or errors would be nice.
Altough for the errors, there seems not much processing for it in the script or too much suppression with @. It seems that the script no matter what, will always end successfuly. Also I don't know if the echo output by the CRON is seen by someone.
Links are not possible by the storage outside the root.
Also mailing links or files would be security risk.

Warning:
You need very large space or some setting that keep only the last x days. Or an automated get and delete procedure by eg FTP.

bjuti

Just can't stay away
Posted on: 2010/2/13 19:30
bjuti
bjuti (Show more)
Just can't stay away
Posts: 871
Since: 2009/1/7 2
#10

Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series

I've found this:

http://www.ozerov.de/bigdump.php

Quote:
Staggered import of large and very large MySQL Dumps (like phpMyAdmin 2.x Dumps) even through the web servers with hard runtime limit and those in safe mode. The script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped.


Is is possible to have XOOPS tool/module based on this script? :)