Backup Your Database into an XML File Using PHP
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 befwrite($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:
to
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
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.
but how can we restore it in database from xml file?