Thủ thuật Backup Database thành một file XML với PHP

PHP & MySQL 25-4-2015

Backup hay sao lưu dữ liệu luôn luôn là việc làm cần thiết và quan trọng nhất. Có rất nhiều cách để backup database cho website của các bạn. Hôm nay mình xin chia sẻ cho các bạn thêm một cách nữa đó là sử dụng đoạn code PHP bên dưới để backup dữ liệu thành một file XML.

backup, code Snipppets, php code, backup database, php tips

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);

}

Các bạn nhớ thay đổi tham số $name với tên database mà các bạn cần sao lưu (backup). Và sau đây là một file XML mẫu sau khi đã được sao lưu :

<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>

Mình hy vọng đoạn PHP mà mình giới thiệu cho các bạn trong ngày hôm nay sẽ giúp ích nhiều cho các bạn.