Please login or register.

Login with username, password and session length
Advanced search  

News:

You need/want an older version of sNews ? Download an older/unsupported version here.

Author Topic: Simple MySQL Database Back-up - 1.7  (Read 1208 times)

Keyrocks

  • Doug
  • ULTIMATE member
  • ******
  • Karma: 449
  • Posts: 6019
  • Semantically Challenged
    • snews.ca
Simple MySQL Database Back-up - 1.7
« on: August 23, 2010, 05:32:32 PM »

This add-on is based on the original Simple MySQL Backup v0.1 for News 1.6 by Bramsyuur.
(yes... for those who may be wondering... it's more of a MOD than an Add-on, but I'm too lazy to re-locate it at the moment)
STATUS: Complete, tested, and ready for use.

This add-on allows the administrator to back up the snews site's database, save it to a folder in the site's domain root (named dbbackups unless you change it to something else in Step 2) and send a copy of it to him or herself using either the site's contact e-mail or any other e-mail address entered into the back-up panel's e-maill address field. The administrator can then save the copy received by e-mail or download a copy of any file saved in the site's root/backup folder.

IMPORTANT: Before we get started, as we do when making any modifications... always remember to back up (make a safe copy) of all files you are modifying so that you can copy back in (over-write a buggered file) if you happen to corrupt the file you are making changes in. When copying and pasting code from the blocks below, always exclude the PHP-tags... their only purpose in the code blocks below is to make the code appear in color.


Step One: New language variable values:
Copy and paste (excluding the PHP-tags) the following new variable=value strings into your lang/EN.php file, above #basic buttons:
Code: [Select]
<?php

# DbBackup Mod: 13 variable=value strings
        
$l['t_dbbackup'] = 'Database Backup';
$l['t_dbbackup_info'] = '1. Replace the default database back-up file-name if desired.<br />2. Verify or change the e-mail that will receive the database backup file.';
$l['t_dbbackup_name'] = '<span style="font-weight: bold;">Database name</span>';
$l['t_dbbackup_email'] = '<span style="font-weight: bold;">E-mail to receive your zipped back-up file</span><br />(Site Settings Contact address by default)';
$l['t_backup'] = 'Backup now';
$l['t_dbb_subject'] = 'MySQL Backup';
$l['t_dbb_message'] = 'MySQL Backup created successfully.';
$l['db_backup_directory'] = '<span style="font-weight: bold;">Backed-up files currently available</span><br />Click any file to download for safe-keeping';
$l['db_no_files_found'] = 'There are no files in your Backup Directory.';
$l['backup_snews'] = 'Backup sNews Tables.';
$l['backup_all'] = 'Backup All Tables';
$l['dbbackup'] = 'Database Backups';
$l['dbbackup_notmailed'] = '<span style="color: #FF0000; font-weight: bold;">The backup file was not e-mailed this time.</span><br />Check the Backup panel to see if the file was saved and listed.<br />If not, please try another backup.';

?>



Step Two: in snews.php,  search for return $db[$variable]; and paste these strings above it.
    These 2 variable=value strings are where you 1) insert the e-mail address you want back-up files sent to, and 2) the name of the folder where files will be stored in the root of your domain on your host server.
Code: [Select]
<?php

# DATABASE BACKUP UTILITY SETTINGS ---------------
// E-mail address to send database back-ups to
$db['t_system_mail'] = 'System DB-Backup  <myemail@mailclient.com>';
// Name of your Back-up file storage folder: Chmod the folder to 777 on your host server.
$db['backup_folder'] = 'dbbackups';

?>

IMPORTANT NOTE: on the name of your backup folder. You can name the folder anything you want instead of  dbbackups as long as you change the name (the value) in the $db['backup_folder'] = 'dbbackups'; string in the above script, and Chmod it to 777 as instructed in the comment string above it.

Step Three: in snews.php, search for $l['cat_listSEF'] .= and replace the complete string (to include the backup function) with:
Code: [Select]
<?php

$l
['cat_listSEF'] .= ',administration,admin_category,admin_article,article_new,extra_new,page_new,snews_categories,snews_articles,extra_contents,snews_pages,snews_settings,snews_files,logout,editcomment,groupings,admin_groupings,dbbackup';

?>



Step Four: in snews.php, search for case 'process': (in function center() and paste this string in above it:
Code: [Select]
<?php

case 'dbbackup'dbbackup(); return; break; // DbBackup Mod

?>



Step Five: in snews.php, near the end of function administration() - which is just above // SETTINGS FORM - replace the end of the function:
Code: [Select]
<?php

echo '<div class="adminpanel">';
echo html_input('fieldset'''''''''''''''''''''''''''l('site_settings'));
echo '<p><a href="snews_settings/">'.l('settings').'</a></p>
<p><a href="snews_files/">'
.l('files').'</a></p></fieldset></div>
<div class="adminpanel">'
;
echo html_input('fieldset'''''''''''''''''''''''''''l('login_status'));
echo '<p><a href="logout/">'.l('logout').'</a></p></fieldset></div>';
}
}

?>


.... with this revised section that includes a text link to the Back-up Panel after the links to the "Settings" and "Files" panels:
Code: [Select]
<?php

echo '<div class="adminpanel">';
echo html_input('fieldset'''''''''''''''''''''''''''l('site_settings'));
// link to Site Settings panel
echo '<p><a href="snews_settings/" title="'.l('settings').'">'.l('settings').'</a></p>';
// link to Database Back-up panel
echo '<p><a href="dbbackup/" title="'.l('t_dbbackup').'">'.l('t_dbbackup').'</a></p>';
// link to Files Manager panel
echo '<p><a href="snews_files/" title="'.l('files').'">'.l('files').'</a></p>';
echo '</fieldset></div>';
echo '<div class="adminpanel">';
// Login status and logout text link
echo html_input('fieldset'''''''''''''''''''''''''''l('login_status'));
echo '<p><a href="logout/">'.l('logout').'</a></p>';
echo '</fieldset></div>';
}
}

?>



Step Six: in snews.php - between the end of the function you just modded and above // SETTINGS FORM,
    paste in these new add-on specific functions:

Code: [Select]
<?php

## D-BASE BACK-uPS - next 4 functions
// BACKUP ADMIN PANEL
function dbbackup() {
$bkup_snews 'ok';
        echo 
'<h4 class="paneltitle">'.l('t_dbbackup').'</h4>';
echo html_input('form''''''''''''''''''''''''post''index.php?action=process&task=dbbackup_start''');
echo '<fieldset class="adminpanel">';
echo l('t_dbbackup_info');
echo html_input('text''t_dbbackup_name''t_dbbackup_name'db('dbname'), l('t_dbbackup_name'), '''''''''''''''''''');
echo html_input('text''t_dbbackup_email''tdbm's('website_email'), l('t_dbbackup_email'), '''''''''''''''''''');
echo '<br />';
echo html_input('submit''do_backup''do_backup'l('t_backup'), '''button''''''''''''''''''');
echo '<br /><br /><p>'.l('db_backup_directory').':</p>';
        
# global variable, set in config.php
dbbackup_files (db('backup_folder'));
echo '</fieldset>'// added spacer div.
echo '</form>';
}

// Generates the e-mail message being sent out with the file attachment
function email_att ($from $to$subject$message$attachment){
$fileatt $attachment;
$fileatt_type "application/octet-stream";
    
$start= strrpos($attachment'/') == -strrpos($attachment'//') : strrpos($attachment'/')+1;
$fileatt_name substr($attachment$startstrlen($attachment));
$email_from $from;
$email_subject =  $subject;
$email_txt $message;
$email_to $to;
$headers "From: ".$email_from;
$file fopen($fileatt,'rb');
$data fread($file,filesize($fileatt));
fclose($file);
$msg_txt="\n\n";
$semi_rand md5(time());
$mime_boundary "==Multipart_Boundary_x{$semi_rand}x";
$headers .= "\nMIME-Version: 1.0\n" .
            
"Content-Type: multipart/mixed;\n" .
            
" boundary=\"{$mime_boundary}\"";
$email_txt .= $msg_txt;
$email_message .= "This is a multi-part message in MIME format.\n\n" .
                
"--{$mime_boundary}\n" .
                
"Content-Type:text/html; charset=\"iso-8859-1\"\n" .
               
"Content-Transfer-Encoding: 7bit\n\n" .
$email_txt "\n\n";
$data chunk_split(base64_encode($data));
$email_message .= "--{$mime_boundary}\n" .
                  
"Content-Type: {$fileatt_type};\n" .
                  
" name=\"{$fileatt_name}\"\n" .
                  
"Content-Transfer-Encoding: base64\n\n" .
                 
$data "\n\n" .
                  
"--{$mime_boundary}--\n";
$ok = @mail($email_to$email_subject$email_message$headers);
if($ok) {
} else {
die('<p>'.l('dbbackup_notmailed').'</p>');
}
}

/* This function creates (prints) the contents of each table in the /sql backup file.         */
function PrintOut($output,$tbl,$stats) {
   
$output "--\n-- Table structure for `$tbl`\n--\n\nCREATE TABLE `$tbl` ( ";
   
$res mysql_query("SHOW CREATE TABLE $tbl");
   while(
$al mysql_fetch_assoc($res)) {
  $str str_replace("CREATE TABLE `$tbl` ("""$al['Create Table']);
  $str str_replace(","","$str);
  $str2 str_replace("`) ) TYPE=MyISAM ""`)\n ) TYPE=MyISAM "$str);
  if ($stats$str2 $str2." AUTO_INCREMENT=".$stats;
  $output .= $str2.";\n\n";
}
$output .= "-- \n-- Dumping data for table `".$tbl."`\n-- \n\n";
   
$data mysql_query("SELECT * FROM $tbl");
   while(
$dt mysql_fetch_row($data)) {
  $output .= "INSERT INTO `$tbl` VALUES('$dt[0]'";
  for($i=1$i<sizeof($dt); $i++) {
  $dt[$i] = mysql_real_escape_string($dt[$i]);
  $output .= ", '$dt[$i]'";
  }
  $output .= ");\n";
   }
   
$output .= "\n-- --------------------------------------------------------\n\n";
   return 
$output;
}

# displays files located in the backup folder
function dbbackup_files($path) {
    
$ignore = array('index.html''index.htm''index.php''cgi-bin''.htaccess''.''..''Thumbs.db');
    
$extension = array('.sql''.sql.gz');
    
$dh = @opendir($path);
    if (
count(glob("$path".'/*.gz')) === 0) { echo '<p><b>'.l('db_no_files_found').'</b></p>'; }
    while (
false !== ($file readdir($dh))) {
        if(!
in_array($file$ignore) && !in_array(substr($file, -4), $extension)) {
        echo 
'&raquo; <a href="'.db('website').$path.'/'.$file.'">'.$file.'</a><br/>';
            }
        }
    
closedir($dh);
}

<?


Step Seven: in snews.php, go to the END of  function processing() .
    There are three curly-brackets at the end of the function.
    Paste this new string-set ABOVE the first of the three curly-brackets:
Code: [Select]
<?php

// START DbBackup Mod
case 'dbbackup_start':
if (isset($_POST['do_backup'])) {
    $mdb db('dbname');
    $mhost db('dbhost');
    $muser db('dbuname');
    $mpass db('dbpass');
    $dbackup_name $_POST['t_dbbackup_name'];
    $tables = array();
    # for folder name, $db variable setting in // DATABASE VARIABLES, top of this file
    $pathname db('backup_folder');
    $timestamp '.'.date('d-m-Y');
    $absolute_path dirname(__FILE__).DIRECTORY_SEPARATOR.$pathname.DIRECTORY_SEPARATOR;
    # Set the file-name as the posted name, date, and extension
    $filename $dbackup_name.$timestamp.".sql.gz";
    $temp $dbackup_name.".sql.gz";
    $gz = (bool) function_exists('gzopen');
    if(!is_dir($pathname) || substr(sprintf('%o'fileperms($pathname)), -4) !="0777") {
echo "Directory '".$pathname."' either doesn't exist or you need to chmod it to 777";
exit;
    }
    # Begin structuring backup file contents
    $fileheader "# mySQL backup ".$today."\n# Server: ".$_SERVER['SERVER_NAME']."\n#  Database: ".$mdb."\n\n";
ob_start();
flush();
unset($backup);
$backup "";
# Queries database to determine table status,
$tab_status mysql_query("SHOW TABLE STATUS");
# If status is not determined, display error message strings
if (!$tab_status) {
echo "Error: Could not show table status! \n";
echo 'MySQL Error: ' mysql_error();
exit;
}
while($all mysql_fetch_assoc($tab_status)) {
    $tbl_stat[$all['Name']] = $all['Auto_increment'];
}
# If no tables are counted, display error messages.
if(count($tables) == 0) {
    $tables mysql_query("SHOW TABLES FROM $mdb");
if (!$tables) {
    echo "Error: Could not list tables! \n";
    echo "MySQL Error: " mysql_error();
    exit;
}
while($tabs mysql_fetch_row($tables)) {
    $backup .= PrintOut($backup,$tabs[0],$tbl_stat[$tabs[0]]);
}
} else {
            foreach(
$tables as $table) {
$show_query ="SHOW TABLE STATUS FROM $mdb LIKE '$table'";
$show mysql_query($show_query);
if (!$show) {
    echo "Error: Could not show ".$table." \n";
    echo "MySQL Error: "mysql_error()."\n";
    exit;
}
while($tabs mysql_fetch_row($show)) {
    $backup .= PrintOut($backup,$tabs[0],$tbl_stat[$tabs[0]]);
}
    }

}
if (($gz && ($zp gzopen($absolute_path.$temp'wb'))) || (!$gz && ($fp fopen($absolute_path.$temp'wb')))) {
    if ($gzgzwrite($zp$fileheaderstrlen($fileheader));
    else fwrite($fp$fileheaderstrlen($fileheader));
}

if ($gz) {
    gzwrite($zp$backup);
    gzwrite($zp"\n\n--\n-- Backup complete! --\n--\n");
    gzclose($zp);
} else {
    fwrite($fp$backup);
    fwrite($fp"\n\n--\n-- Backup complete! --\n--\n");
    fclose($fp);
}

$final $absolute_path.$filename;
@unlink($final);
rename($absolute_path.$temp$final);
if (strtoupper(substr(PHP_OS03)) != 'WIN'touch($final);
# receiving e-mail, gets the confirmation
$dbb_to $_POST['t_dbbackup_email'];
# source e-mail, sends the confirmation
$dbb_from db('t_system_mail'); // , $db variable setting // DATABASE VARIABLES, top of this file
# e-mail subject-line
$dbb_subject l('t_dbb_subject'); // "MySQL Backup"
# e-mail message
$dbb_message l('t_dbb_message'); // "MySQL Backup created successfully."
# URL to the attachment (file) - absolute path + file-name
$dbb_attachment $absolute_path $filename;
# run function email_att with these variables (in mods/dbase_backups.php)
email_att ($dbb_from $dbb_to$dbb_subject$dbb_message$dbb_attachment);
ob_end_flush();
echo notification(0,'','administration');
}
break;
// END: DBBackup mod

?>


Finally... be sure you created that empty directory (folder) in your site's domain root, the same place you uploaded your snews.php file to, and named it accordingly as instructed in Step 2.  ;)
Completed. No more steps.
Please report any problems in this thread only.
« Last Edit: October 31, 2012, 05:13:55 PM by Keyrocks »
Logged
Do it now... later may not come.
-------------------------------------------------------------------------------------------------
sNews 1.6 MESU | sNews 1.6 MEMU

Sven

  • ULTIMATE member
  • ******
  • Karma: 88
  • Posts: 2029
  • Chasing MY bugs!
    • hiseo.fr - rédacteur Web
Re: Simple MySQL Database Back-up - 1.7
« Reply #1 on: August 26, 2010, 04:51:50 PM »

Plese report any problems in this thread only.
And if none, give a karma.
Done!

pamelapergolini.it

  • Newbie
  • *
  • Karma: 0
  • Posts: 9
Re: Simple MySQL Database Back-up - 1.7
« Reply #2 on: October 03, 2010, 02:59:42 PM »

it says to me: Directory 'dbbackups' either doesn't exist or you need to chmod it to 777 when i push the button Backup Now.....  ???

Logged

Keyrocks

  • Doug
  • ULTIMATE member
  • ******
  • Karma: 449
  • Posts: 6019
  • Semantically Challenged
    • snews.ca
Re: Simple MySQL Database Back-up - 1.7
« Reply #3 on: October 03, 2010, 10:57:02 PM »

it says to me: Directory 'dbbackups' either doesn't exist or you need to chmod it to 777 when i push the button Backup Now.....  ???

If you have not done so yet, you will need to create a folder in your site domain's root and name it dbbackups so that the back-up script will be able to save its files in that folder. If you want to name your folder something else, make the change where indicated in Step 2 (above).

If you have already created that folder, then you might need to change the permissions on it to 777 (if the current permissions are too restrictive) when you are logged into your host server with your FTP client (where you uploaded your project's file-set to).
« Last Edit: October 03, 2010, 11:05:12 PM by Keyrocks »
Logged
Do it now... later may not come.
-------------------------------------------------------------------------------------------------
sNews 1.6 MESU | sNews 1.6 MEMU

pamelapergolini.it

  • Newbie
  • *
  • Karma: 0
  • Posts: 9
Re: Simple MySQL Database Back-up - 1.7
« Reply #4 on: October 03, 2010, 11:51:14 PM »

i've done but when i click on Database Backup it brings to me to the original welcome page of my hosting....  ???

edit: Oh Damn! How stupid am i??  :-X  i wrote dbbackup instead dbbackups...  :P ::)  ...soooo sorry!
« Last Edit: October 03, 2010, 11:55:47 PM by pamelapergolini.it »
Logged