PHP Recursive Backup of MySql Database

PHP Recursive Backup of MySql Database

 
 

This script can be used to make backup of your MySql database, you can use the script in conjunction with cronjobs


	$user = 'myuser';
	$passwd = 'mypass';
	$host = 'myhost';
	$db = 'mydb';
	

	
	// Delete the tables if they exists?
	$drop = true;
	// Tables that will be created
	$tables = false;
	// Compression algorythm that we will use
	$compression = false;

	// DB Connection
	$connection = mysql_connect($host, $user, $passwd)
	or die("Can't connect to MySql server: ".mysql_error());
	mysql_select_db($db, $connection)
	or die("Can't select the DataBase: ". mysql_error());


	// Search the table in the database
	if (empty($tables)){
			$query = "SHOW TABLES FROM $db;";
			$result = mysql_query($query, $connection)
			or die("Can't execute the query: ".mysql_error());
			while ($row = mysql_fetch_array($result, MYSQL_NUM)){
					$tables[] = $row[0];
			}
	}

	// Create the header archive
	$info['dumpversion'] = "1.2";
	$info['date'] = date("d-m-Y");
	$info['time'] = date("h:m:s A");
	$info['mysqlver'] = mysql_get_server_info();
	$info['phpver'] = phpversion();
	
	ob_start();
	
	print_r($tables);
	$representation = ob_get_contents();	
	ob_end_clean ();
	
	preg_match_all('/(\[\d+\] => .*)\r\n/', $representation, $matches);
	$info['tables'] = implode(";  ", $matches[1]);

	// Dump variable
	$dump= "";

	foreach ($tables as $table){
		
		$drop_table_query = "";
		$create_table_query = "";
		$insert_into_query = "";
	
		// Start the query that create the db.
		if ($drop){
				$drop_table_query = "DROP TABLE IF EXISTS `$table`;";
		} else {
				$drop_table_query = "# No specified.";
		}
		$create_table_query = "";
		$query = "SHOW CREATE TABLE $table;";
		$result = mysql_query($query, $connection)
		or die("Can't execute the query: ".mysql_error());
	
		while ($row = mysql_fetch_array($result, MYSQL_NUM)){
				$create_table_query = $row[1].";";
		}
		
		// This query insert the datas.
		$insert_into_query = "";
		$query = "SELECT * FROM $table;";
		$result = mysql_query($query, $connection)
		or die("Can't execute the query: ".mysql_error());
	
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
				$columns = array_keys($row);
				foreach ($columns as $column){
						if ( gettype($row[$column]) == "NULL" ){
								$values[] = "NULL";
						} else { 
								$values[] = "'".mysql_real_escape_string($row[$column])."'";
						}
				}
				$insert_into_query .= "INSERT INTO `$table` VALUES (".implode(", ", $values).");\r\n";
				unset($values);
		}
	
		$dump .="
		
		# | Empty Table '$table'
		# +------------------------------------->
		$drop_table_query
		
		# | Structure of table '$table'
		# +------------------------------------->
		$create_table_query
		
		# | Data loading of table '$table'
		# +------------------------------------->
		$insert_into_query
		
		" ;
		
	}


$myFile = $_SERVER['DOCUMENT_ROOT'].'/backup/'.'database.sql';

// if the backup exists we delete and rewrite it
if (file_exists($myFile)){ 
 unlink($myFile);
} 

$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $dump);
fclose($fh);

 
 

tags: php recursive backup database mysql


We use our own cookies and third-party cookies to improve our services, show products based on your preferences, analyse the browsing habits of our users, and enable interaction with social networks. Continuing to browse our sites implies full acceptance of their use. You can change your cookie setting or get more information here: Cookies policy .