php备份mysql为mdb_PHP 备份 Mysql 数据库的实现

// 变量参数设置

$options = array(

'db_host' =>'localhost',

'db_user' =>'root',

'db_pwd' =>'',

'db_name' =>'test',

'db_charset'=>'utf8',

);

// 连接数据库

$conn = mysql_connect($options['db_host'], $options['db_user'], $options['db_pwd']);

mysql_select_db('test', $conn);

mysql_query("SET NAMES {$options['db_charset']}", $conn);

// 获取数据库的所有表

$tableArr = array();

$resource = mysql_query('SHOW TABLES FROM test', $conn);

while ($tempArr = mysql_fetch_row($resource)) {

$tableArr[] = $tempArr[0];

}

// 循环操作每个表

foreach ($tableArr as $table) {

// (1)获取创建表的 SQL 语句

$resource = mysql_query('SHOW CREATE TABLE '.$table, $conn);

$tempArr = mysql_fetch_row($resource);

$sqlStr = 'DROP TABLE IF EXISTS '.$table.";\n";

$sqlStr .= $tempArr[1].";\n";

// (2)获取数据表中的字段信息

$fieldArr = array();

$resource = mysql_query('SHOW COLUMNS FROM '.$table);

while ($tempArr = mysql_fetch_assoc($resource)) {

if ($tempArr['Key'] == 'PRI') {

$fieldArr['PRI'] = $tempArr['Field'];

} else {

$fieldArr[] = $tempArr['Field'];

}

}

// (3)查询每个字段的内容, 拼成 SQL 字符串

$sql = 'SELECT * FROM '.$table;

if (!empty($fieldArr['PRI'])) $sql .= ' ORDER BY '.$fieldArr['PRI'].' ASC';

$resource = mysql_query($sql);

while ($tempArr = mysql_fetch_row($resource)) {//获取每张表对应的 INSERT SQL 语句

$sqlStr .= 'INSERT INTO '.$table.' VALUES (';

foreach ($tempArr as $value) {

$sqlStr .= '\''.addslashes($value).'\', ';//转义特殊字符

}

$sqlStr = substr($sqlStr, 0, -2).");\n";

}

$sqlStr .= "\n\n-- =========================$table\n\n";

// (4)输出内容到文件

$handle = fopen("db_bakup_{$options['db_name']}.sql", 'a+');

if (flock($handle, LOCK_EX)) {

fwrite($handle, $sqlStr);

flock($handle, LOCK_UN);

}

fclose($handle);

}// 释放Mysql资源, 关闭连接mysql_free_result($resource);mysql_close($conn);?>