The David Walsh Blog

Backup Your Database into an XML File Using PHP

Written by David Walsh on February 27, 2009 · 33 Comments

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.

Comments

  1. Matt February 27, 2009

    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. Tonio February 27, 2009

    Hum …

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

    My 2 cents ….

  3. david February 27, 2009

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

  4. Tonio February 27, 2009

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

    http://www.gophp5.org/

  5. david February 27, 2009

    @Tonio: But still used… :)

  6. Mark Sanborn February 27, 2009

    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. Jeremy Parrish February 27, 2009

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

  8. Jesus DeLaTorre February 27, 2009

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

  9. Adriaan February 27, 2009

    Interesting, but pretty useless without a restore function…

  10. Jesus DeLaTorre February 27, 2009

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

  11. Jeff Hartman February 27, 2009

    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 March 3, 2009

    change

    $query = ‘SHOW TABLES FROM 305jive’;

    to

    $query = “SHOW TABLES FROM $name”;

  13. deos March 4, 2009

    nice one, but you made a mistake

    fwrite($handle,$return);

    has to be

    fwrite($handle,$xml);

  14. Vaseem Ansari January 5, 2010

    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 October 24, 2010

    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. herraldjert November 24, 2010

    i just think what about for joomla

  17. dude August 26, 2011

    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. 10个MySQL数据库备份教程推荐 November 16, 2011

    [...] Backup Your Database Into an XML File By Using PHP（通过PHP实现XML备份数据库） [...]

  19. Ashish March 12, 2012

    Dear

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

  20. Nuno May 28, 2012

    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.

  21. monpub July 19, 2012

    thank you very much
    i just need it

  22. 10 Ways to Automatically & Manually Backup MySQL Database | Tôi yêu Việt Nam October 12, 2012

    [...] 5. Backup Your Database into an XML File Using PHP [...]

  23. 10 Ways to Automatically & Manually Backup MySQL Database | Chuot con's Blog October 15, 2012

    [...] 5. Backup Your Database into an XML File Using PHP [...]

  24. 简化 PHP 开发的10个工具 – 90互联网数据中心 October 15, 2012

    [...] 5. 把数据库备份到 XML 文件 [...]

  25. PHP程序员的10个有用的技巧和教程 | 蓝枭博客 November 22, 2012

    [...] 8） Backup Database XML PHP [...]

  26. PHP程序员的10个有用的技巧和教程 | 编程·早晨 November 22, 2012

    [...] 8） Backup Database XML PHP [...]

  27. 分享10个常用的PHP技术的教程 – VSA站长 - 关注IT业界 November 25, 2012

    [...] 8） Backup Database XML PHP [...]

  28. Highly Useful Tools and Techniques for PHP Developers | Techno Tab February 4, 2013

    [...] 5) Backup Your Database into an XML File Using PHP [...]

  29. 25 Nya och Användbara PHP Tekniker & handledningar | Artiklar March 20, 2013

    [...] 25. Optimera en php applikation på 5 minuter [...]

  30. PHP程序员的10个有用的技巧和教程 — 程序猿的那点事 April 3, 2013

    [...] 8） Backup Database XML PHP [...]

  31. គន្លឹះ​មេរៀន​ទាំង ១០ ក្នុងការ​ Backup MySQL Database | សំបុកអាយធី | www.sombokit.com June 9, 2013

    [...] 7. Backup Your Database Into an XML File By Using PHP [...]

  32. គន្លឹះ​មេរៀន​ទាំង ១០ ក្នុងការ​ Backup MySQL Database | ខ្ញុំ និង អ្នក June 9, 2013

    [...] 7. Backup Your Database Into an XML File By Using PHP [...]

  33. How to Schedule Daily Backup of WordPress Database June 17, 2013

    [...] Backup Your Database into an XML File Using PHP [...]

Be Heard

Tip: Wrap your code in <pre> tags or link to a GitHub Gist!

Use Code Editor
Older
MooTools Tip: Adding Events During Element Creation
Newer
jQuery topLink Plugin