PHP Recursive Backup of MySql Database

PHP Recursive Backup of MySql Database
by Janeth Kent Date: 24-06-2013 php recursive backup database mysql

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();
	$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";
		$dump .="
		# | Empty Table '$table'
		# +------------------------------------->
		# | Structure of table '$table'
		# +------------------------------------->
		# | Data loading of table '$table'
		# +------------------------------------->
		" ;

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

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

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

by Janeth Kent Date: 24-06-2013 php recursive backup database mysql hits : 5730  
Janeth Kent

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.


Related Posts

Examine the 10 key PHP functions I use frequently

PHP never ceases to surprise me with its built-in capabilities. These are a few of the functions I find most fascinating.   1. Levenshtein This function uses the Levenshtein algorithm to calculate the…

How to Track Flight Status in real-time using the Flight Tracker API

The Flight Tracker API provides developers with the ability to access real-time flight status, which is extremely useful for integrating historical tracking or live queries of air traffic into your…

What is a JWT token and how does it work?

JWT tokens are a standard used to create application access tokens, enabling user authentication in web applications. Specifically, it follows the RFC 7519 standard. What is a JWT token A JWT token…

PHP - The Singleton Pattern

The Singleton Pattern is one of the GoF (Gang of Four) Patterns. This particular pattern provides a method for limiting the number of instances of an object to just one.…

How to Send Email from an HTML Contact Form

In today’s article we will write about how to make a working form that upon hitting that submit button will be functional and send the email (to you as a…

The State of PHP 8: new features and changes

PHP 8.0 has been released last November 26: let's discover together the main innovations that the new version introduces in this language. PHP is one of the most popular programming languages…

HTTP Cookies: how they work and how to use them

Today we are going to write about the way to store data in a browser, why websites use cookies and how they work in detail. Continue reading to find out how…

The most popular Array Sorting Algorithms In PHP

There are many ways to sort an array in PHP, the easiest being to use the sort() function built into PHP. This sort function is quick but has it's limitations,…

MySQL 8.0 is now fully supported in PHP 7.4

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules…

Java Sorting Algorithm: Selection Sort

Today we are going to analyze a sorting algorithm that is not very efficient but often used in various fields. We are talking abou the Selection Sort. Let's have a look. Intuition The…

A roadmap to becoming a web developer in 2019

There are plenty of tutorials online, which won't cost you a cent. If you are sufficiently self-driven and interested, you have no difficulty training yourself. The point to learn coding…

10 PHP code snippets to work with dates

Here we have some set of Useful PHP Snippets, which are useful for PHP Developers. In this tutorial we'll show you the 10 PHP date snippets you can use on…