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.
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
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???
i just think what about for joomla
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))
[...] Backup Your Database Into an XML File By Using PHP(通过PHP实现XML备份数据库) [...]
Dear
I want to execute xml file through which contains the database ; if possible please post it or send it to my email
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.
thank you very much
i just need it
[...] 5. Backup Your Database into an XML File Using PHP [...]
[...] 5. Backup Your Database into an XML File Using PHP [...]
[...] 5. 把数据库备份到 XML 文件 [...]
[...] 8) Backup Database XML PHP [...]
[...] 8) Backup Database XML PHP [...]
[...] 8) Backup Database XML PHP [...]
[...] 5) Backup Your Database into an XML File Using PHP [...]
[...] 25. Optimera en php applikation på 5 minuter [...]
[...] 8) Backup Database XML PHP [...]
[...] 7. Backup Your Database Into an XML File By Using PHP [...]
[...] 7. Backup Your Database Into an XML File By Using PHP [...]
[...] Backup Your Database into an XML File Using PHP [...]