Please login or register.

Login with username, password and session length
Advanced search  
Pages: [1] 2 3 4

Author Topic: [MOD] Simple MySQL Backup v0.1 (no system calls)  (Read 20150 times)

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« on: May 19, 2007, 12:58:54 AM »

Information:
MOD name: Simple MySQL Backup v.01 (no system calls)
sNews version: v1.6

Function: With this mod you can create a compressed file backup of your current database from your administration panel by one click. The system ask to you a valid email address to send to you a copy of a compressed dabatase file. The backup of the database include database structure and data, all ready to be uploaded into your website if you get a disaster.

Updates:
- Fixed step 2. Thanks to Philmoz!
- Added info to chmod 777 database folder.
- Fixed, in the step 6, an error related to e-mail to user.

Quote
This is my second version of this mod (without system calls, my previous one are in the sNews 1.5.31 mod section).
Important note:
Quote
If your hosting provider don't support gZip compression your database backup will be saved in a plain text.
So... let's go! :)
Step #1:
Create a backup of your snews.php file... as you know! ;)
Next, make a folder into the root of your sNews installation to store your database backups and chmod it to 777, in this mod, I've named it as _backups_ but I recommend to you to use a better name folder with alphanumerical / upper and lower case characters like: M1b4ckUp2007 for example and put inside of this folder an empty index.html file to list protection. (hey! this is not a great protection in any case, but is a minimal option for protect your folder).

Create a set of language variables used in this mod:
Quote
//MOD START: DBBackup by bramsyuur
   $l['t_dbbackup'] = 'Database Backup';
   $l['t_dbbackup_info'] = 'Please, first, provide a \' <b>backup name</b>\' for the database and, next, verify or moddify the e-mail that your want to be used to get the database backup file.
';
   $l['t_dbbackup_name'] = 'Database name';
   $l['t_dbbackup_email'] = 'E-mail to send your created backup';
   $l['t_backup'] = 'Backup now';
   $l['t_system_mail'] = 'System DB-Backup <admin@your.domain.com>';  // <--- Please, check this e-mail address to meet your needs!!
   $l['t_dbb_subject'] = 'MySQL Backup';
   $l['t_dbb_message'] = 'MySQL Backup created successfully.';
   $l['db_backup_directory'] = 'Currently Backup Files';
   $l['db_no_files_found'] = 'No files found!';
   //MOD END
Step #2:
Into "#SYSTEM VARIABLES (not to be translated)" locate:
Code: [Select]
$l['cat_listSEF'] = $l['home_sef'].',archive,contact,sitemap,rss-articles,rss-pages,rss-comments,login,administration,admin_category,admin_article,article_new,extra_new,page_new,categories,articles,extra_contents,pages,settings,files,logout'; //SEF links of the hardcoded categoriesand replace with:
Code: [Select]
$l['cat_listSEF'] = $l['home_sef'].',archive,contact,sitemap,rss-articles,rss-pages,rss-comments,login,administration,admin_category,admin_article,article_new,extra_new,page_new,categories,articles,extra_contents,pages,settings,files,logout,dbbackup'; //SEF links of the hardcoded categoriesStep #3:
Inside the "function center()" locate "case 'settings': settings(); return; break;" and just next to it, put:
Quote
//MOD START: DBBackup by bramsyuur
case 'dbbackup': dbbackup(); return; break;
//MOD END
Step #4:
Find, inside "function administration()" this line:
Quote
echo '<p><a href="settings/" title="'.l('settings').'">'.l('settings').'</a></p>';
And add next to it, this:
Quote
//MOD START: DBBackup by bramsyuur
echo '<p><a href="dbbackup/" title="'.l('t_dbbackup').'">'.l('t_dbbackup').'</a></p>';
//MOD END
Step #5:
Next, few lines down, under the same function (function administration()) locate "}}" and, next to it, add:
Remember: Change _backups_ with your created backup folder!!!!
Code: [Select]
//MOD START: DBBackup by bramsyuur
function dbbackup() {
echo html_input('form', '', '', '', '', '', '', '', '', '', '', '', 'post', 'index.php?action=process&task=dbbackup_start', '');
echo html_input('fieldset', '', '', '', '', '', '', '', '', '', '', '', '', '','<a title="'.l('t_dbbackup').'">'.l('t_dbbackup').'</a>');
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 '
';
echo html_input('submit', 'do_backup', 'do_backup', l('t_backup'), '', 'button', '', '', '', '', '', '', '', '', '');
echo '

<p>'.l('db_backup_directory').':</p>';
     dbbackup_files("_backups_");  // REMEMBER: Change _backups_ with your created backup folder!!!!
echo '</fieldset></form>';
}

function email_att ($from , $to, $subject, $message, $attachment){
$fileatt = $attachment;
$fileatt_type = "application/octet-stream";
     $start= strrpos($attachment, '/') == -1 ? strrpos($attachment, '//') : strrpos($attachment, '/')+1;
$fileatt_name = substr($attachment, $start, strlen($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("Sorry but the email could not be sent. Please go back and try again!");
}
}

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;
}

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 '· <a href="'.db('website').$path.'/'.$file.'">'.$file.'</a><br/>';
            }
        }
    closedir($dh);
}
//MOD END: DBBackup by bramsyuur
Step #6:
Finally, locate under "function processing()" the last "break;" at the end and next to it, add:
Remember: Change _backups_ with your created backup folder!!!!
Code: [Select]
//MOD START: DBBackup by bramsyuur
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();
$pathname = "_backups_"; // REMEMBER: Change _backups_ with your created backup folder!!!!
$timestamp = '.'.date('d-m-Y');
$absolute_path = dirname(__FILE__).DIRECTORY_SEPARATOR.$pathname.DIRECTORY_SEPARATOR;
$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;
}
$fileheader = "# mySQL backup ".$today."\n# Server: ".$_SERVER['SERVER_NAME']."\n#  Database: ".$mdb."\n\n";
ob_start();
flush();
unset($backup);
$backup = "";
$tab_status = mysql_query("SHOW TABLE STATUS");
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(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 ($gz) gzwrite($zp, $fileheader, strlen($fileheader));
else fwrite($fp, $fileheader, strlen($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_OS, 0, 3)) != 'WIN') touch($final);
$dbb_to = $_POST['t_dbbackup_email'];
$dbb_from = l('t_system_mail');
$dbb_subject = l('t_dbb_subject');
$dbb_message = l('t_dbb_message');
$dbb_attachment = $absolute_path . $filename;
email_att ($dbb_from , $dbb_to, $dbb_subject, $dbb_message, $dbb_attachment);
ob_end_flush();
echo notification(0,'','administration');
}
break;
//MOD END
And this is all! :)
If you want, you can test it here: (user and password: test
http://snews.vietbee.net/demos/sNews16/
And here's an example image:

Thanks for using it! :)
« Last Edit: August 22, 2007, 12:17:56 AM by Phil Martin »
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol

piXelatedEmpire

  • MIA
  • ULTIMATE member
  • ******
  • Karma: 37
  • Posts: 1401
  • currently MIA
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #1 on: May 19, 2007, 03:18:22 AM »

Without System Calls? Excellent update Bram!! :cool:
Logged
my apologies to the sNews crew, but I will be MIA for the forseeable future

Rui Mendes

  • Development,Testing, Support
  • Hero Member
  • *****
  • Karma: 195
  • Posts: 1009
  • sNews1.7
    • Comunidade Portuguesa
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #2 on: May 19, 2007, 03:58:29 AM »

Excellent Work Bram! :D

 Very usefull MOD, thank you
Logged
Need a Job on Europe. Linkdin - Facebook / Group

philmoz

  • High flyer
  • ULTIMATE member
  • ******
  • Karma: 161
  • Posts: 1988
    • fiddle 'n fly
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #3 on: May 19, 2007, 06:10:00 AM »

Step 2 should read


Quote
snipped .. mod adjusted..
Logged
Of all the things I have lost, it is my mind that I miss the most.

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #4 on: May 19, 2007, 11:46:23 AM »

Thanks Philmoz, I've fixed it and noted in my post.  :)
Also I've added a notice to chmod the backup folder to 777.
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #5 on: May 20, 2007, 07:21:20 PM »

Fixed an error in the step 6 related to user e-mail and added an online demo to check it out!  ;)
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol

Rui Mendes

  • Development,Testing, Support
  • Hero Member
  • *****
  • Karma: 195
  • Posts: 1009
  • sNews1.7
    • Comunidade Portuguesa
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #6 on: May 20, 2007, 10:01:58 PM »

For who want default Database name with dbname_daymonthyear_hourmin

in dbbackup function, find
Code: [Select]
echo html_input('text', 't_dbbackup_name', 't_dbbackup_name', db('dbname'), l('t_dbbackup_name'), '', '', '', '', '', '', '', '', '', '');Add red text
Quote
echo html_input('text', 't_dbbackup_name', 't_dbbackup_name', db('dbname').'_'.).date('dmY'.date('H').date('i'), strtotime($r['date']))).date('dmY'.date('H').date('i'), strtotime($r['date'])) , l('t_dbbackup_name'), '', '', '', '', '', '', '', '', '', '');
or replace line complete
echo html_input('text', 't_dbbackup_name', 't_dbbackup_name', db('dbname').date('dmY'.date('H').date('i'), strtotime($r['date'])), l('t_dbbackup_name'), '', '', '', '', '', '', '', '', '', '');
« Last Edit: August 22, 2007, 12:19:39 AM by Phil Martin »
Logged
Need a Job on Europe. Linkdin - Facebook / Group

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #7 on: May 21, 2007, 03:57:06 PM »

Quote from: ruimendes
For who want default Database name with dbname_daymonthyear_hourmin
Rui, this mod currently add automatically a date to your data backup name. If you enter a database name like "snews16" you get as a result: snews16.20-05-2007.sql.gz.
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol

piXelatedEmpire

  • MIA
  • ULTIMATE member
  • ******
  • Karma: 37
  • Posts: 1401
  • currently MIA
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #8 on: June 11, 2007, 11:58:09 AM »

Quote from: bramsyuur
... I recommend to you to use a better name folder with alphanumerical / upper and lower case characters like: M1b4ckUp2007 for example and put inside of this folder an empty index.html file to list protection. (hey! this is not a great protection in any case, but is a minimal option for protect your folder).
I've been thinking about this somewhat, and an idea came to me after reading this thread, and seeing this in particular:
Code: [Select]
# Prevent viewing of .htaccess

order allow,deny
deny from all
satisfy all


# Prevent viewing of the data files

deny from all
Now, my thoughts are would it be possible to modify this .htaccess file and place it in the _backups_ folder for added security?  Perhaps instead of preventing data files from being viewed we could prevent .gz files from being viewed/downloaded?  Or would this impact of the MODs ability to function correctly?
Logged
my apologies to the sNews crew, but I will be MIA for the forseeable future

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #9 on: June 11, 2007, 12:07:51 PM »

I'll try to update the security of the current database folder with something like this. Wen finish I'll update my post.
Thanks pix' ! :)
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #10 on: June 11, 2007, 12:31:12 PM »

Well pix, If I use any .htaaccess protection to see the .sql.gz files, one part of this mod (the current view of data backups) don't work correctly and you get an error due to protection of the .htaccess file.
If you want to use a .htaccess protection may be you need to remove the "current backups view" part of this mod.
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol

piXelatedEmpire

  • MIA
  • ULTIMATE member
  • ******
  • Karma: 37
  • Posts: 1401
  • currently MIA
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #11 on: June 11, 2007, 12:38:03 PM »

That's probably not a bad idea bramsyuur.  Actually taking it a step further, do we actually need to store the database backups on the server?  I mean ideally I think it would be best if once the file was sent to the desired email address, the actual backup file was deleted.  Is that a possibility?
Logged
my apologies to the sNews crew, but I will be MIA for the forseeable future

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #12 on: June 11, 2007, 12:39:47 PM »

You're right Pix'... I'll post a 'second' version of this mod soon.
Thanks.  :)
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol

piXelatedEmpire

  • MIA
  • ULTIMATE member
  • ******
  • Karma: 37
  • Posts: 1401
  • currently MIA
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #13 on: June 23, 2007, 05:25:18 AM »

Hey bram, just wondering if you'd worked on this any further?  I know you've been a busy bee with the MU MOD! :D
Logged
my apologies to the sNews crew, but I will be MIA for the forseeable future

bramsyuur

  • Hero Member
  • *****
  • Karma: 23
  • Posts: 873
    • http://snews.vietbee.net
[MOD] Simple MySQL Backup v0.1 (no system calls)
« Reply #14 on: June 23, 2007, 12:55:12 PM »

Not yet Pix' but soon... :)
Logged
La comunidad sNews en tu idioma!
Comunidad sNews en Espaņol
Pages: [1] 2 3 4