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 : 3435  
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 END DEVELOPMENT.


Related Posts

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…

8 Free PHP Books to Read in Summer 2018

In this article, we've listed 8 free PHP books that can help you to learn new approaches to solving problems and keep your skill up to date.   Practical PHP Testing This book…

Best Websites to Learn Coding Online

You know and we know that it’s totally possible to learn to code for free... If you can teach yourself how to write code, you gain a competitive edge over your…

PHP and XMP format: Create a Panorama 360/VR viewer in PHP and A-Frame

The Extensible Metadata Platform (XMP) is an ISO standard, originally created by Adobe Systems Inc., for the creation, processing and interchange of standardized and custom metadata for digital documents and…

PHP: Basic Introduction to Namespaces

History of PHP Namespaces In PHP prior to 5.3 (2009), any class you define lived at the same global level as other classes. Class User, class Contact, class Db they're all together…

PHP 7.2 will be the first Programming Language to add Modern Cryptography to its Standard Library

Last week, the voting phase closed on an RFC to add libsodium to PHP 7.2. The result was unanimous (37 in favor, 0 against). When version 7.2 releases at the end…

Php: How to extend the highlight_string function

PHP has a cool function that automatically highlights PHP code called highlight_string(); Theoretically this could be used to roll your own code highlighting on a site, rather than rely on…

We use our own and third-party cookies to improve our services, compile statistical information and analyze your browsing habits. This allows us to personalize the content we offer and to show you advertising related to your preferences. By clicking on "Understood" YOU AGREE TO THE USE OF THESE COOKIES. You can also SET OR DISCONTINUE the installation of cookies by clicking on "Settings" or "Reject".

Understood Reject Settings More Info