MySQL的备份

本来想着用SHOW CREATE TABLE + ergodic 来做备份的但是发现如果Table 有 comment 而且还是乱码的话，会导致SHOW CREATE TABLE 出来的脚本缺少一个符号。所以有了这个版本。

/**
* database connect
*/
private $_db;
private $_resource;
/**
* create table structure sql
*/
private $_create_table = '';
public function __construct($host = '', $user = '', $pass = '', $db = '', $port = 3306) {
if (empty($host) || empty($user)) {
} else {
$this->real_connect($host, $user, $pass, $db, $port);
}
}
public function init() {
return $this;
}
/**
* 连接数据库
*/
public function real_connect($host, $user, $pass, $db = '', $port = 3306) {
$this->_db = mysql_connect($host . ':' . $port, $user, $pass);
if ($db) {
$this->select_db($db);
}
return $this->init();
}
/**
* 选择数据库
*/
public function select_db($db) {
if (mysql_select_db($db, $this->_db)) {
return true;
}
}
/**
* 查询语句
*/
public function query($sql) {
if ($this->_db) {
if ($this->_resource = mysql_query($sql, $this->_db)) {
return $this->init();
}
}
throw new Exception($this->get_error());
}
/**
* 获取结果集
*/
public function fetch_array($arg = MYSQL_BOTH) {
$result = array();
if ($this->_resource && !mysql_errno($this->_db)) {
while ($rs = mysql_fetch_array($this->_resource, $arg)) {
$result[] = $rs;
}
}
return $result;
}
/**
* 获取错误
*/
public function get_error() {
return mysql_errno($this->_db) . ": " . mysql_error($this->_db). "\n";
}
/**
* 显示数据表
*/
public function show_tables($table = '') {
$sql = "SHOW TABLES";
$sql .= $table ? " LIKE '{$table}'" : '';
$result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
return $result;
}
/**
* 显示数据表字段
*/
public function show_columns($table) {
if (empty($table)) {
return array();
}
$sql = "SHOW FULL COLUMNS FROM {$table}";
$result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
return $result;
}
/**
* 显示数据表状态
*/
public function show_table_status($table) {
if (empty($table)) {
return array();
}
$result = $this->query("SHOW TABLE STATUS LIKE '{$table}'")->fetch_array(MYSQL_ASSOC);
$result = reset($result);
return $result;
}
/**
* 显示数据表结构
*/
public function show_create_table($table) {
if (empty($table)) {
return '';
}
$this->_create_table = "CREATE TABLE IF NOT EXISTS `{$table}`(" . PHP_EOL;
$table_status = $this->show_table_status($table);
$columns = $this->show_columns($table);
foreach ($columns AS $col) {
$this->_create_table .= "`{$col['Field']}` {$col['Type']} NOT NULL {$col['Extra']}," . PHP_EOL;
}
$this->_create_table .= $this->create_indexSyntax($table);
$char = substr($table_status['Collation'], 0, strpos($table_status['Collation'], '_'));
$table_status['Auto_increment'] = $table_status['Auto_increment'] ? $table_status['Auto_increment'] : 0;
$this->_create_table .= ")Engine={$table_status['Engine']} AUTO_INCREMENT={$table_status['Auto_increment']} DEFAULT CHARSET={$char};" . str_repeat(PHP_EOL, 3);
return $this->_create_table;
}
/**
* 显示触发器
*/
public function show_constraint($db_name) {
if (empty($db_name)) {
return array();
}
$sql = "SELECT a.CONSTRAINT_NAME AS constrint_name, a.TABLE_name AS table_name, a.COLUMN_NAME AS column_name, a.REFERENCED_TABLE_NAME as referenced_table_name, a.REFERENCED_COLUMN_NAME AS referenced_column_name, b.UPDATE_RULE as update_rule, b.DELETE_RULE AS delete_rule FROM information_schema.KEY_COLUMN_USAGE AS a LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS AS b ON a.constraint_name=b.constraint_name WHERE a.constraint_schema = '{$db_name}' AND a.POSITION_IN_UNIQUE_CONSTRAINT = 1";
$result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
}
/**
* 显示索引
*/
public function show_index($table) {
if (empty($table)) {
return array();
}
$sql = "SHOW INDEX FROM {$table}";
$result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
return $result;
}
/**
* 显示数据库结构
*/
public function show_database_char() {
$sql = "SHOW VARIABLES LIKE 'character_set_database'";
$char = $this->query($sql)->fetch_array(MYSQL_ASSOC);
return reset($char);
}
/**
* 创建索引语法
*/
public function create_indexSyntax($table) {
if (empty($table)) {
return array();
}
$indexing = $this->show_index($table);
$syntax = array();
$indexSyntax = array();
foreach ($indexing as $index) {
$syntax[$index['Index_type']][$index['Key_name']][] = $index['Column_name'];
}
foreach ($syntax as $index_type => $index_value) {
foreach ($index_value as $key_name => $columns) {
if ($key_name == 'PRIMARY') {
$indexSyntax[] = 'PRIMARY KEY (`' . implode("`,`", $columns) . '`)';
} else {
if ($index_type == 'FULLTEXT') {
$indexSyntax[] = "FULLTEXT KEY `{$key_name}` (`" . implode("`,`", $columns) . '`)';
} else{
$indexSyntax[] = "KEY `{$key_name}` USING {$index_type} (`" . implode("`,`", $columns) . '`)';
}
}
}
}
return implode(',' . PHP_EOL, $indexSyntax) . PHP_EOL;
}
/**
* 创建 insert 语法
*/
public function create_insertSyntax($table) {
if (empty($table)) {
return '';
}
$sql = "SELECT * FROM {$table}";
$result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
$insertStr = '';
if ($result) {
$first = reset($result);
$key = implode('`,`', array_keys($first));
$insert = "INSERT INTO `{$table}` (`{$key}`) VALUES ";
$valuesStr = array();
foreach ($result as $value) {
$values = array();
foreach ($value as $v) {
$v = mysql_real_escape_string($v);
$values[] = preg_replace("#\\\+#", "\\", $v);
}
$valuesStr[] = "('" . implode("','", $values) . "')";
}
$valuesStr = array_chunk($valuesStr, 5000);
foreach ($valuesStr as $str) {
$insertStr .= $insert . implode(',', $str) . ';' . PHP_EOL;
}
}
return $insertStr . str_repeat(PHP_EOL, 3);
}
}
$export = '';
$test = new MysqlExport('localhost', 'root', '', 'pm_cms');
$char = $test->show_database_char();
$test->query("SET NAMES {$char['Value']}");
$tables = $test->show_tables();
foreach ($tables as $table) {
list($table_name) = array_values($table);
$export .= $test->show_create_table($table_name);
$export .= $test->create_insertSyntax($table_name);
}
$fp = fopen('pm_cms.sql', 'w');
fwrite($fp, $export);
fclose($fp);
?>

复制代码

table

MySQL的备份