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

Janeth Kent

Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.