1
wishcraft
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 tohttp://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;

   }
?>

2
bjuti
Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series
  • 2010/2/12 10:58

  • bjuti

  • Just can't stay away

  • Posts: 871

  • Since: 2009/1/7 2


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? :)

3
wishcraft
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.

4
btesec
Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series
  • 2010/2/12 14:23

  • btesec

  • Friend of XOOPS

  • Posts: 623

  • Since: 2007/2/20


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

5
bjuti
Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series
  • 2010/2/12 14:28

  • bjuti

  • Just can't stay away

  • Posts: 871

  • Since: 2009/1/7 2


Tnx, I'll check that :)

6
wishcraft
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.

7
dbman
Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series
  • 2010/2/13 3:39

  • dbman

  • Friend of XOOPS

  • Posts: 172

  • Since: 2005/4/28


This would be a nice addition to XoopsCare.

8
xsell
Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series
  • 2010/2/13 4:31

  • xsell

  • Quite a regular

  • Posts: 245

  • Since: 2008/9/2 1


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...


9
ghia
Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series
  • 2010/2/13 10:53

  • ghia

  • Community Support Member

  • Posts: 4953

  • Since: 2008/7/3 1


-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.

10
bjuti
Re: CRON MySQL Backup Script for 2.3, 2.4, 2.5 Series
  • 2010/2/13 19:30

  • bjuti

  • Just can't stay away

  • Posts: 871

  • Since: 2009/1/7 2


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? :)

Login

Who's Online

95 user(s) are online (62 user(s) are browsing Support Forums)


Members: 0


Guests: 95


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits