database backup and restore

Posted: April 18, 2011 in MySql, PHP
<?
	define(DB_NAME,'db_name');
	
	//============= db backup =================
	function createBackup($fileName)
	{
		$file = fopen($fileName,"w");	
		$line_count = 0;
	  
		$tables = mysql_list_tables(DB_NAME);
		$sql_string = NULL;
		while ($table = mysql_fetch_array($tables)) 
		{   
		  $table_name = $table[0];
		  
		  $delim = "\n-- ##NEW_QUERY## --\n";
		  $sql_string = "";
		  $sql_string.= "\n\n-- --------- Table : $table_name --------\n";
		  $sql_string.= "DROP TABLE IF EXISTS `$table_name`;\n".$delim;
		  
		  $createTableQuery = mysql_query("SHOW CREATE TABLE ".$table_name.";"); //mysql_query("SHOW COLUMNS FROM $table;");
		  $createTable      = mysql_fetch_row($createTableQuery);
		  $sql_string.= $createTable[1].";\n\n".$delim;   
		  
		  $table_query = mysql_query("SELECT * FROM `$table_name`");
		  $num_fields = mysql_num_fields($table_query);
		  $num = mysql_num_rows($table_query);
		  if($num > 0)
			$sql_string .= "INSERT INTO $table_name VALUES \n";
		  $cnt = 1;
		  while ($fetch_row = mysql_fetch_array($table_query)) 
		  {		  
			$sql_string .= "(";
			$first = TRUE;
			for ($field_count=1;$field_count<=$num_fields;$field_count++)
			{
			  if (TRUE == $first) 
			  {
				  $sql_string .= "'".mysql_real_escape_string($fetch_row[($field_count - 1)])."'";
				  $first = FALSE;            
			  } 
			  else
			  {
				$sql_string .= ", '".mysql_real_escape_string($fetch_row[($field_count - 1)])."'";
			  }
			}
			$sql_string .= ")".(($cnt==$num)?(';'.$delim):(','))."\n";  
			$cnt++;
		  }
		 
		  if ($sql_string != "")
		  {         
			fwrite($file, $sql_string);
		  }
		}
		fclose($file);
		return;
	}

	//============= db restore =================
	function restoreBackup($fileName)
	{
		$str = file_get_contents($fileName); 
		$arr = explode('-- ##NEW_QUERY## --',$str);
		for($i=0;$i<count($arr);$i++)
		{
			$sql = trim($arr[$i]);	
			if($sql!="")
			{
				mysql_query($sql) or die( '<br /><strong>Query: </strong>'.$sql.'<br /><br /><strong>Error: </strong>'. mysql_errno().':'.mysql_error());
			}
			
		}	
	}


	$fileName = date('Y_m_d_h_i_s').'.sql';
	createBackup('db_backup/'.$fileName);
	
	$fileName = 'db_backup/2011_04_18_11_23_32.sql';
	restoreBackup($fileName);
?>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s