• Subcribe to Our RSS Feed

[Mysql] Export Mysql Tables Records By Day From Large DB

Jul 30, 2013   //   by admin   //   Mysql, Open Source, PHP  //  No Comments

Today i am giving you tips which is Php Script For Export Mysql Tables Records By Day. This kind of script only use if your database is to big so every day you are not able to take backup by phpmyadmin because its waste of time. and every day you are changing lots of thing through mysql in this case you database become to large. I solved large database export problem by my script. I developed this script for custom CRM system. This system’s current database size is 700MB.So its not possible to take backup per day by phpmyadmin.

Large DB Export

So i write script to solve this issue.In this script you can get only records which is inserted today or updated today. Means we will export only record which added date or modified date are today.We will get only few records from database so our code execution will be fast.

Now i am giving you theoretical information about my code.Please see below.

Export Mysql Table’s Records By Day

  • You must need two columns for added date and modified date.(Ex. d_added, d_modified)
  • We have to insert or update relevant date fields values in table when we execute insert or update query.
  • Make groups of table’s whose added date and modified date are same means very group table have same column name for added date and modified date.(Ex. $tables_1,$tables_2,$tables_3)
  • You can easily get record by my code day wise.
  • Make Mysql query of record which you get from database.
  • Write all query in backup.sql files and email to client.
<?php 
include("inc/config.php");
 
//Get all tables from database
$tables_res=$dclass>query("SHOW TABLES");
$tables=$dclass->fetchResult($tables_res);
$query="";
 
//Table Groups by added and modified dates.
$tables_1=array("tbl_benefits","tbl_category","tbl_client","tbl_client_notes","tbl_department","tbl_document_creator","tbl_event","tbl_field","tbl_field_client_value","tbl_field_prospect_value","tbl_field_section","tbl_field_user_value","tbl_offshores","tbl_offshores_benefits","tbl_offshores_services","tbl_order","tbl_order_canada","tbl_order_usa","tbl_packages","tbl_prospect","tbl_prospect_documents","tbl_prospect_notes","tbl_prospect_task","tbl_quote_notes","tbl_services","tbl_status","tbl_task","tbl_user","tbl_user_documents","tbl_user_notes");
 
$tables_2=array("tbl_conferencecenter","tbl_conference_chat","tbl_conference_screenshare");
$tables_3=array("tbl_prior_order");
 
  foreach($tables as $table){
	//Getting records which are changed today
	if(in_array($table[0],$tables_1)){
		$c_table=$table[0];
		$rows=$dclass->select("*",$c_table," AND ( d_added LIKE '".date("Y-m-d")."%' OR d_modified LIKE '".date("Y-m-d")."%' )");
	}elseif(in_array($table[0],$tables_2)){
		$c_table=$table[0];
		$rows=$dclass->select("*",$c_table," AND ( d_datetime LIKE '".date("Y-m-d")."%')");
	}elseif(in_array($table[0],$tables_3)){
		$c_table=$table[0];
		$rows=$dclass->select("*",$c_table," AND ( AddedByDate LIKE '".date("Y-m-d")."%' OR ChangedByDate LIKE '".date("Y-m-d")."%')");
	}
	//create sql query
	if(!empty($rows)){
		foreach($rows as $row){
			$query .=" DELETE FROM ".$c_table." WHERE `id` = '".$row['id']."' LIMIT 1;\n";
			$query .=" INSERT INTO ".$c_table." VALUES ('".implode("','",$row)."');\n";
		}
 
	}
 
 }
	//Write query in SQL files
	if($query !=''){
	$filename="Backup-Of-".date("Y-m-d").".sql";
 
	$fp = fopen("dbBackup/".$filename,"wb");
	fwrite($fp,$query);
	fclose($fp);
 
	create_archive("dbBackup/".$filename,"dbBackup/Backup-Of-".date("Y-m-d").".zip");
 
	$path='dbBackup/';
	$uid = md5(uniqid(time()));
	$file_name="Backup-Of-".date("Y-m-d").".zip";
	$filename = $path.$file_name;
 
	//email set up
	$to='shailpatel05@gmail.com';
 
	$subject = "Backup-Of-".date("Y-m-d");
 
	$headers = "From: no-reply@phpfreelancerszone.com\r\nReply-To: no-reply@phpfreelancerszone.com";
 
	//setting the boundary
	$boundary = md5(time());
 
	$messageTxt = '
 
	Hi Admin,
 
	Please find  backup of date '.date("Y-m-d").'
 
	<a href="'.SITE_URL.'admin/'.$filename.'">Click here</a> to download from server.
 
	';
 
	$headers .= "MIME-Version: 1.0\n".
	"Content-Type: multipart/mixed; boundary=".$boundary."; ";
 
	//attachments
	$file = fopen($filename,"rb");
	$data = fread($file,filesize($filename));
	fclose($file);
	$data = chunk_split(base64_encode($data));
 
	$mail_attached .= "--" . $boundary . "\n"
	. "Content-Type: binary/octet-stream; name=".$file_name." \n"
	. "Content-Transfer-Encoding: base64 \n" . $data."\n"
	. "Content-Disposition: attachment; filename=".$file_name." \n"
	. "--".$boundary."\n";
 
	$message = "--".$boundary."\n"
	. "Content-Type: text/html; charset='UTF-8'\n"
	. "Content-Transfer-Encoding: 8bit \n\n"
	. $messageTxt." \n\n"
	. $mail_attached;
 
	$mail_sent = @mail( $to, $subject, $message, $headers );
	//echo $mail_sent ? "Mail sent" : "Mail failed";
}
//Creating Zip files
function create_archive($source_file, $destination_file,$overwrite='1'){
  $zip = new ZipArchive();
  if($zip->open($destination_file,$overwrite?ZIPARCHIVE::OVERWRITE:ZIPARCHIVE::CREATE)===TRUE){
    $zip->addFile($source_file);
    $zip->close(); 
  }
}

You can set this code in cron jobs and you will get daily backup of your database. Let me know if you have any query?

Thanks.

Leave a comment

*

Social Media Auto Publish Powered By : XYZScripts.com