备份数据非常重要。大多数时候，数据库是最重要的拼图。想象一下丢失数据库中的所有数据——那将是一场悲剧。这是将数据库输出为 XML 的 PHP 片段。
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);
}
您可能不需要添加列节点，但我喜欢包含尽可能多的数据，它们不会增加太多的总文件大小。
示例输出
<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 不是用于恢复表的最简单格式，因此您可能更愿意将表导出为 SQL 语句。我喜欢额外的 XML 备份，因为它易于阅读。
