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
    5 Awesome New Mozilla Technologies You&#8217;ve Never Heard Of

    My trip to Mozilla Summit 2013 was incredible.  I've spent so much time focusing on my project that I had lost sight of all of the great work Mozillians were putting out.  MozSummit provided the perfect reminder of how brilliant my colleagues are and how much...

Incredible Demos

  • By
    Full Width Textareas

    Working with textarea widths can be painful if you want the textarea to span 100% width.  Why painful?  Because if the textarea's containing element has padding, your "width:100%" textarea will likely stretch outside of the parent container -- a frustrating prospect to say the least.  Luckily...

  • By
    Create Tiny URLs with TinyURL, MooTools, and PHP

    Since we've already figured out how to create TinyURL URLs remotely using PHP, we may as well create a small AJAX-enabled tiny URL creator. Using MooTools to do so is almost too easy. The XHTML (Form) We need an input box where the user will enter...

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!