Backup Your Database into an XML File Using PHP

By  on  

Backing up data is extremely important. Most of the time the database is the most important piece of the puzzle. Imagine losing all of the data in your database -- it would be tragic. Here's a PHP snippet that outputs your database as XML.

The PHP

//connect
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all the tables
$query = 'SHOW TABLES FROM '.$name;
$result = mysql_query($query,$link) or die('cannot show tables');
if(mysql_num_rows($result))
{
	//prep output
	$tab = "\t";
	$br = "\n";
	$xml = '<?xml version="1.0" encoding="UTF-8"?>'.$br;
	$xml.= '<database name="'.$name.'">'.$br;
	
	//for every table...
	while($table = mysql_fetch_row($result))
	{
		//prep table out
		$xml.= $tab.'<table name="'.$table[0].'">'.$br;
		
		//get the rows
		$query3 = 'SELECT * FROM '.$table[0];
		$records = mysql_query($query3,$link) or die('cannot select from table: '.$table[0]);
		
		//table attributes
		$attributes = array('name','blob','maxlength','multiple_key','not_null','numeric','primary_key','table','type','default','unique_key','unsigned','zerofill');
		$xml.= $tab.$tab.'<columns>'.$br;
		$x = 0;
		while($x < mysql_num_fields($records))
		{
			$meta = mysql_fetch_field($records,$x);
			$xml.= $tab.$tab.$tab.'<column ';
			foreach($attributes as $attribute)
			{
				$xml.= $attribute.'="'.$meta->$attribute.'" ';
			}
			$xml.= '/>'.$br;
			$x++;
		}
		$xml.= $tab.$tab.'</columns>'.$br;
		
		//stick the records
		$xml.= $tab.$tab.'<records>'.$br;
		while($record = mysql_fetch_assoc($records))
		{
			$xml.= $tab.$tab.$tab.'<record>'.$br;
			foreach($record as $key=>$value)
			{
				$xml.= $tab.$tab.$tab.$tab.'<'.$key.'>'.htmlspecialchars(stripslashes($value)).'</'.$key.'>'.$br;
			}
			$xml.= $tab.$tab.$tab.'</record>'.$br;
		}
		$xml.= $tab.$tab.'</records>'.$br;
		$xml.= $tab.'</table>'.$br;
	}
	$xml.= '</database>';
	
	//save file
	$handle = fopen($name.'-backup-'.time().'.xml','w+');
	fwrite($handle,$xml);
	fclose($handle);
}

You probably don't NEED to add column nodes but I like including as much data as possible and they don't add very much to the total file size.

The Sample Output

<database name="my_database">
	<table name="wp_comments">
		<columns>
			<column name="comment_ID" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="1" primary_key="1" table="wp_comments" type="int" default="" unique_key="0" unsigned="1" zerofill="0" />
			<column name="comment_post_ID" blob="0" maxlength="" multiple_key="1" not_null="1" numeric="1" primary_key="0" table="wp_comments" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_author" blob="1" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="blob" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_author_email" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_author_url" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_author_IP" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_date" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="datetime" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_date_gmt" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="datetime" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_content" blob="1" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="blob" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_karma" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="1" primary_key="0" table="wp_comments" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_approved" blob="0" maxlength="" multiple_key="1" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_agent" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_type" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_comments" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="comment_parent" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="1" primary_key="0" table="wp_comments" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="user_id" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="1" primary_key="0" table="wp_comments" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
		</columns>
		<records>
			<record>
				<comment_ID>2</comment_ID>
				<comment_post_ID>4</comment_post_ID>
				<comment_author>Ryan</comment_author>
				<comment_author_email>ryantastad@hotmail.com</comment_author_email>
				<comment_author_url></comment_author_url>
				<comment_author_IP>66.84.199.242</comment_author_IP>
				<comment_date>2007-12-06 10:10:38</comment_date>
				<comment_date_gmt>2007-12-06 16:10:38</comment_date_gmt>
				<comment_content>Roethlisberger is coming to town!?  Sorry, Fred.</comment_content>
				<comment_karma>0</comment_karma>
				<comment_approved>1</comment_approved>
				<comment_agent>Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322)</comment_agent>
				<comment_type></comment_type>
				<comment_parent>0</comment_parent>
				<user_id>0</user_id>
			</record>
		</records>
	</table>
	<table name="wp_links">
		<columns>
			<column name="link_id" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="1" primary_key="1" table="wp_links" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_url" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_name" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_image" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_target" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_category" blob="0" maxlength="" multiple_key="1" not_null="1" numeric="1" primary_key="0" table="wp_links" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_description" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_visible" blob="0" maxlength="" multiple_key="1" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_owner" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="1" primary_key="0" table="wp_links" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_rating" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="1" primary_key="0" table="wp_links" type="int" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_updated" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="datetime" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_rel" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_notes" blob="1" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="blob" default="" unique_key="0" unsigned="0" zerofill="0" />
			<column name="link_rss" blob="0" maxlength="" multiple_key="0" not_null="1" numeric="0" primary_key="0" table="wp_links" type="string" default="" unique_key="0" unsigned="0" zerofill="0" />
		</columns>
		<records>
			<record>
				<link_id>1</link_id>
				<link_url>http://codex.wordpress.org/</link_url>
				<link_name>Documentation</link_name>
				<link_image></link_image>
				<link_target></link_target>
				<link_category>0</link_category>
				<link_description></link_description>
				<link_visible>Y</link_visible>
				<link_owner>1</link_owner>
				<link_rating>0</link_rating>
				<link_updated>0000-00-00 00:00:00</link_updated>
				<link_rel></link_rel>
				<link_notes></link_notes>
				<link_rss></link_rss>
			</record>
			<record>
				<link_id>2</link_id>
				<link_url>http://wordpress.org/development/</link_url>
				<link_name>Development Blog</link_name>
				<link_image></link_image>
				<link_target></link_target>
				<link_category>0</link_category>
				<link_description></link_description>
				<link_visible>Y</link_visible>
				<link_owner>1</link_owner>
				<link_rating>0</link_rating>
				<link_updated>0000-00-00 00:00:00</link_updated>
				<link_rel></link_rel>
				<link_notes></link_notes>
				<link_rss>http://wordpress.org/development/feed/</link_rss>
			</record>
		</records>
	</table>
</database>

XML isn't the easiest format to restore a table with so you may prefer to export the table as SQL statements. I enjoy the additional XML backup because it's easy to read.

Recent Features

  • By
    CSS vs. JS Animation: Which is Faster?

    How is it possible that JavaScript-based animation has secretly always been as fast — or faster — than CSS transitions? And, how is it possible that Adobe and Google consistently release media-rich mobile sites that rival the performance of native apps? This article serves as a point-by-point...

  • By
    Create a CSS Cube

    CSS cubes really showcase what CSS has become over the years, evolving from simple color and dimension directives to a language capable of creating deep, creative visuals.  Add animation and you've got something really neat.  Unfortunately each CSS cube tutorial I've read is a bit...

Incredible Demos

  • By
    Telephone Link Protocol

    We've always been able to create links with protocols other than the usual HTTP, like mailto, skype, irc ,and more;  they're an excellent convenience to visitors.  With mobile phone browsers having become infinitely more usable, we can now extend that convenience to phone numbers: The tel...

  • By
    CSS Rounded Corners

    The ability to create rounded corners with CSS opens the possibility of subtle design improvements without the need to include images.  CSS rounded corners thus save us time in creating images and requests to the server.  Today, rounded corners with CSS are supported by all of...

Discussion

  1. This is much easier to read than SQL inserts. I may have to look into using this script to dump a copy of the DB with my SQL backup just so i have something easy to read.

  2. Hum …

    What about simplexml ? Your would be much robust and nicer using it.

    My 2 cents ….

  3. @Tonio: Sure, but that requires PHP5. The above is PHP4-compatible.

  4. @david: yes, one reason more to do it with simplexml, PHP4 is no more supported ;)

    http://www.gophp5.org/

  5. @Tonio: But still used… :)

  6. I prefer using built in simple methods… “mysqldump -xml”. If you still want to use php or make this web enabled you can have php execute mysqldump with the exec function: exec(‘mysqldump’); You can even have it output to php and display it in the browser.

  7. If you want something that’s easy to read, I would suggest YAML. It’s much easier to read than XML.

  8. Jesus DeLaTorre

    I’m guessing the import is too follow? Backups are very useful unless you can actually use it as a backup :)

  9. Interesting, but pretty useless without a restore function…

  10. Jesus DeLaTorre

    I’m guessing the import is to follow? Backups are very useful unless you cant actually use it as a backup :)

  11. Jeff Hartman

    Know what’s easier to read?

    mysql> describe table {table};

    mysql> select * from {table};

    Backups aren’t usually meant to be read. I’ll take a database backup that is compressed, fast, and valid over readability any day.

  12. omid

    change

    $query = ‘SHOW TABLES FROM 305jive’;

    to

    $query = “SHOW TABLES FROM $name”;

  13. deos

    nice one, but you made a mistake fwrite($handle,$return); has to be fwrite($handle,$xml);

  14. thanks a lot dear. this is such a nice code and working properly with some modifications that are required to do.
    thanks
    http://www.vaseemansari.blogspot.com

  15. irwin

    hello, sorry but i try to use your script .. .nice ;-)
    but the result is a white page :-(

    i have added, the <?php markup and $host,$user,$pass, $name…
    but nothink happend..
    Why???

  16. i just think what about for joomla

  17. dude

    Just to let you know: the embedded copy button above seems to mess with the code. For example it changed:

    while($x < mysql_num_fields($records))
    

    to

    while($x < mysql_num_fields($records))
    
  18. Ashish

    Dear

    I want to execute xml file through which contains the database ; if possible please post it or send it to my email

  19. Nuno

    I’m using WampServer and it gives me a lot of errors on line 42! It says “Undefined property:…”
    Does anyone have a solution for this problem?

    Very much appreciated.

  20. thank you very much
    i just need it

  21. Great post. Yes the data backing up is really important, especially for big directories. This is snippet is really simple to understand and I will get it to my website developer to implement. With all this hack-attacks it is important to have it.

  22. ramesh

    but how can we restore it in database from xml file?

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!