How to dump all MySQL tables in PHP, on hosting without OUTFILE privilege
global $db_tables;
ob_start();
require_once("config.php");
ob_end_clean();
//backup_db_tables();
$date = date("YmdHis");
$db = new DB;
$db->connect();
$tables = "SHOW TABLES";
$res = mysql_query($tables) or die( "Error executing query: ".mysql_error() );
while( $table = mysql_fetch_row($res) )
{
$fp = fopen( ABSPATH."_backup/".$table[0]."_".$date."_dump.sql", "a" );
if ( $fp )
{
$query = "TRUNCATE TABLE `".$table[0]."`;
";
fwrite ($fp, $query);
$rows = 'SELECT * FROM `'.$table[0].'`';
$r = mysql_query($rows) or die("Error executing query: ".mysql_error());
while( $row = mysql_fetch_row($r) )
{
$query = "";
foreach ( $row as $field )
{
if ( is_null($field) )
$field = "NULL";
else
$field = "'".mysql_escape_string( $field )."'";
if ( $query == "" )
$query = $field;
else
$query = $query.', '.$field;
}
$query = "INSERT INTO `".$table[0]."` VALUES (".$query.");
";
fwrite ($fp, $query);
}
fclose ($fp);
//getting the file with the tables dump ABSPATH."_backup/".$table[0]."_".$date."_dump.sql"
}
}
and what if you need to pack it into a ZIP and then send the dump archive by email?
require_once('pclzip.lib.php');
global $db_tables;
ob_start();
require_once("config.php");
ob_end_clean();
//backup_db_tables();
$date = date("YmdHis");
$date2 = date("Y-m-d-H-i");
// creating the archive
$archive = new PclZip(ABSPATH."_backup/".$date2.'.zip');
$archive->create(ABSPATH."_backup/".'file.txt', PCLZIP_OPT_REMOVE_PATH, ABSPATH."_backup/");
$dfmail = "noreply@".str_replace('www.', '', $_SERVER['HTTP_HOST']);
$mail = new PHPMailer(); // defaults to using php "mail()"
$mail->CharSet = 'UTF-8';
$body = 'Automatic system backup. To restore lost data, use a database manager (for example, phpMyAdmin) and import the attached files. Warning! Importing the data will destroy the current records in the tables.';
$mail->AddReplyTo($dfmail,"name");
$mail->SetFrom($dfmail, 'name ');
$mail->AddAddress("name @name.ua", "name ");
$mail->Subject = "automatic backup from ".date('Y-m-d H:i:s');
$mail->AltBody = "To view the message, please use an HTML compatible email viewer!"; // optional, comment out and test
$mail->MsgHTML($body);
$db = new DB;
$db->connect();
$tables = "SHOW TABLES";
$res = mysql_query($tables) or die( "Query execution error: ".mysql_error() );
while( $table = mysql_fetch_row($res) )
{
$fp = fopen( ABSPATH."_backup/".$table[0]."_".$date."_dump.sql", "a" );
if ( $fp )
{
$query = "TRUNCATE TABLE `".$table[0]."`;
";
fwrite ($fp, $query);
$rows = 'SELECT * FROM `'.$table[0].'`';
$r = mysql_query($rows) or die("Query execution error: ".mysql_error());
while( $row = mysql_fetch_row($r) )
{
$query = "";
foreach ( $row as $field )
{
if ( is_null($field) )
$field = "NULL";
else
$field = "'".mysql_escape_string( $field )."'";
if ( $query == "" )
$query = $field;
else
$query = $query.', '.$field;
}
$query = "INSERT INTO `".$table[0]."` VALUES (".$query.");
";
fwrite ($fp, $query);
}
fclose ($fp);
if ( is_file(ABSPATH."_backup/".$table[0]."_".$date."_dump.sql") )
{$error_ar = $archive->add(ABSPATH."_backup/".$table[0]."_".$date."_dump.sql", PCLZIP_OPT_REMOVE_PATH, ABSPATH."_backup/");
if ($error_ar == 0) { echo ("
Error : '".$archive->errorInfo()."'"); } else {echo ("
file : '".ABSPATH."_backup/".$table[0]."_".$date."_dump.sql" ."' add to archive");}
} else { echo "
error ! file -".ABSPATH."_backup/".$table[0]."_".$date."_dump.sql - not found";}
if(unlink ( ABSPATH."_backup/".$table[0]."_".$date."_dump.sql"))
{ echo "
Delete ok: " .ABSPATH."_backup/".$table[0]."_".$date."_dump.sql"; }
else{ echo "
Error delete file!".ABSPATH."_backup/".$table[0]."_".$date."_dump.sql"; }
// used to attach them one at a time - now we attach and delete them all at once
//$mail->AddAttachment(ABSPATH."_backup/".$table[0]."_".$date."_dump.sql");
}
}
$mail->AddAttachment(ABSPATH."_backup/".$date2.'.zip');
if(!$mail->Send()) {
echo "
Backup Error: " . $mail->ErrorInfo;
}else{
echo "
Backup sent!";
}
how can this be automated so it sends itself at a set time?
add a command to cron that will run at a set time
/usr/local/bin/curl http://example.com/cron.php?token='334563464568'
and wrap the contents of cron.php in
if($_GET['token'] == '334563464568'){
************************
}else{
die('
Permission denied!');
}
?>
what are these libraries? 'pclzip.lib.php'
and $mail ?
http://www.phpconcept.net/pclzip/pclzip-downloads
Comments