<?php

$link = mysql_connect("localhost", "root","");

mysql_query("set names utf8");

// mysql_select_db('数据库名称', $link);

$version = mysql_get_server_info($link);

$sys_info['os'] = PHP_OS;

$sys_info['php_ver'] = PHP_VERSION;

$sys_info['date'] = date('Y-m-d H:i:s', time());

// 头文件

$head = "-- lin3615 SQL Dump Program\r\n".

                "-- " . $sys_info['os'] . "\r\n".

                "-- PHP VERSION : " . $sys_info['php_ver'] . "\r\n";

                "-- MYSQL VERSION : " . $version . "\r\n";

                "-- Date " . $sys_info['date'] . "\r\n";

// 写入文件中

$fileName = date('Y-m-d-H-i-s', time()) . ".sql";

$str = $head;

//获取数据表列表

$tableSQL = "show  tables";

$tableNameArr = array(); // 数据表名称列表

$res = mysql_query($tableSQL, $link);

while($row = mysql_fetch_assoc($res))

{

    $tableNameArr[] = $row['Tables_in_数据库名']; // Tables_in_数据库名称

}

foreach($tableNameArr as $table)

{

    // 获取数据表定义

    $tableDefineSQL = "show create table " . $table;

    $resTableDefine = mysql_query($tableDefineSQL, $link);

    $res = mysql_fetch_assoc($resTableDefine);

    $tableDataDefine = $res['Create Table'] . ";\r\n";     // 数据表定义

    $str = $str . $tableDataDefine;

    $str = $str. "-- --------------------\r\n";

    $str = $str. "-- Records of ".$table."\r\n";

    $str = $str. "-- --------------------\r\n";

    // 获取数据表的数据

    $sql = "select * from " . $table;

    $res = mysql_query($sql, $link);

    while($row = mysql_fetch_assoc($res))

    {

        $keysArray = $row;

        $record = array_map("mysql_real_escape_string",$row); // 处理换行符，转义字符

        $rr = implode("','", $record);

        $keyRecord = array_keys($keysArray);

        $ss = implode('`,`', $keyRecord);

        $fields = "`" . $ss . "`";

        $sql = "insert into `".$table."` (".$fields.") values ('" . $rr . "');\r\n";

        $str = $str . $sql;

    }

}

$resultEnd = file_put_contents($fileName, $str);

// 以上结束数据备份

// 以下开始数据还原操作

$arr = file('备份数据库名.sql');

// 移除注释

function remove_comment($arr)

{

    return (substr($arr, 0,2) != '--');

}

$sql_str = array_filter($arr,'remove_comment');

$sql_str = str_replace("\r", "",implode('',$sql_str));

$ret = explode(";\n", $sql_str);

foreach($ret as $val)

{

    $val = trim($val, " \r\n;");

    mysql_query($val, $link);

}