Backup Your Database into an XML File Using PHP
Written by David Walsh on Friday, February 27, 2009
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.
Follow via RSS Epic Discussion
Be Heard!
I want to hear what you have to say! Share your comments and questions below.
Feeds & Profiles
Sponsors
Tutorials & Topics
- Hot Topics
- All Topics
- Popular Posts & Classics™
-
- 9 Sign You Shouldn't Hire THAT Web Guy
- MooTools 1.2: Customize Your Tooltips
- Basic AJAX Requests Using MooTools
- Build a Slick & Simple MooTools Accordion
- MooTools SmoothScroll
- MooTools Animated Progress Bar
- Advanced CSS Printing - Page Breaks
- Open Letter to You, Webmaster
- 7 MooTools Plugins to Use on Every Website
- 7 CSS Snippets to Enhance User Experience
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.
Hum …
What about simplexml ? Your would be much robust and nicer using it.
My 2 cents ….
@Tonio: Sure, but that requires PHP5. The above is PHP4-compatible.
@david: yes, one reason more to do it with simplexml, PHP4 is no more supported ;)
http://www.gophp5.org/
@Tonio: But still used… :)
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.
If you want something that’s easy to read, I would suggest YAML. It’s much easier to read than XML.
I’m guessing the import is too follow? Backups are very useful unless you can actually use it as a backup :)
Interesting, but pretty useless without a restore function…
I’m guessing the import is to follow? Backups are very useful unless you cant actually use it as a backup :)
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.
change
$query = ‘SHOW TABLES FROM 305jive’;
to
$query = “SHOW TABLES FROM $name”;
nice one, but you made a mistake
fwrite($handle,$return);
has to be
fwrite($handle,$xml);
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