搜索

首页  >  文章  >  后端开发  >  MySQL的备份

MySQL的备份

WBOY
WBOY原创
2016-07-25 08:50:37738浏览
本来想着用SHOW CREATE TABLE + ergodic 来做备份的但是发现如果Table 有 comment 而且还是乱码的话，会导致SHOW CREATE TABLE 出来的脚本缺少一个符号。所以有了这个版本。
  1. class MysqlExport{
  3. /**
  4. * database connect
  5. */
  6. private $_db;
  8. private $_resource;
  10. /**
  11. * create table structure sql
  12. */
  13. private $_create_table = '';
  15. public function __construct($host = '', $user = '', $pass = '', $db = '', $port = 3306) {
  16. if (empty($host) || empty($user)) {
  18. } else {
  19. $this->real_connect($host, $user, $pass, $db, $port);
  20. }
  21. }
  23. public function init() {
  24. return $this;
  25. }
  27. /**
  28. * 连接数据库
  29. */
  30. public function real_connect($host, $user, $pass, $db = '', $port = 3306) {
  31. $this->_db = mysql_connect($host . ':' . $port, $user, $pass);
  32. if ($db) {
  33. $this->select_db($db);
  34. }
  35. return $this->init();
  36. }
  38. /**
  39. * 选择数据库
  40. */
  41. public function select_db($db) {
  42. if (mysql_select_db($db, $this->_db)) {
  43. return true;
  44. }
  45. }
  47. /**
  48. * 查询语句
  49. */
  50. public function query($sql) {
  51. if ($this->_db) {
  52. if ($this->_resource = mysql_query($sql, $this->_db)) {
  53. return $this->init();
  54. }
  55. }
  56. throw new Exception($this->get_error());
  57. }
  59. /**
  60. * 获取结果集
  61. */
  62. public function fetch_array($arg = MYSQL_BOTH) {
  63. $result = array();
  64. if ($this->_resource && !mysql_errno($this->_db)) {
  65. while ($rs = mysql_fetch_array($this->_resource, $arg)) {
  66. $result[] = $rs;
  67. }
  68. }
  69. return $result;
  70. }
  72. /**
  73. * 获取错误
  74. */
  75. public function get_error() {
  76. return mysql_errno($this->_db) . ": " . mysql_error($this->_db). "\n";
  77. }
  79. /**
  80. * 显示数据表
  81. */
  82. public function show_tables($table = '') {
  83. $sql = "SHOW TABLES";
  84. $sql .= $table ? " LIKE '{$table}'" : '';
  85. $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
  86. return $result;
  87. }
  89. /**
  90. * 显示数据表字段
  91. */
  92. public function show_columns($table) {
  93. if (empty($table)) {
  94. return array();
  95. }
  96. $sql = "SHOW FULL COLUMNS FROM {$table}";
  97. $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
  98. return $result;
  99. }
  101. /**
  102. * 显示数据表状态
  103. */
  104. public function show_table_status($table) {
  105. if (empty($table)) {
  106. return array();
  107. }
  108. $result = $this->query("SHOW TABLE STATUS LIKE '{$table}'")->fetch_array(MYSQL_ASSOC);
  109. $result = reset($result);
  110. return $result;
  111. }
  113. /**
  114. * 显示数据表结构
  115. */
  116. public function show_create_table($table) {
  117. if (empty($table)) {
  118. return '';
  119. }
  120. $this->_create_table = "CREATE TABLE IF NOT EXISTS `{$table}`(" . PHP_EOL;
  121. $table_status = $this->show_table_status($table);
  122. $columns = $this->show_columns($table);
  124. foreach ($columns AS $col) {
  125. $this->_create_table .= "`{$col['Field']}` {$col['Type']} NOT NULL {$col['Extra']}," . PHP_EOL;
  126. }
  128. $this->_create_table .= $this->create_indexSyntax($table);
  130. $char = substr($table_status['Collation'], 0, strpos($table_status['Collation'], '_'));
  131. $table_status['Auto_increment'] = $table_status['Auto_increment'] ? $table_status['Auto_increment'] : 0;
  132. $this->_create_table .= ")Engine={$table_status['Engine']} AUTO_INCREMENT={$table_status['Auto_increment']} DEFAULT CHARSET={$char};" . str_repeat(PHP_EOL, 3);
  134. return $this->_create_table;
  135. }
  137. /**
  138. * 显示触发器
  139. */
  140. public function show_constraint($db_name) {
  141. if (empty($db_name)) {
  142. return array();
  143. }
  144. $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";
  145. $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
  146. }
  148. /**
  149. * 显示索引
  150. */
  151. public function show_index($table) {
  152. if (empty($table)) {
  153. return array();
  154. }
  155. $sql = "SHOW INDEX FROM {$table}";
  156. $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
  157. return $result;
  158. }
  160. /**
  161. * 显示数据库结构
  162. */
  163. public function show_database_char() {
  164. $sql = "SHOW VARIABLES LIKE 'character_set_database'";
  165. $char = $this->query($sql)->fetch_array(MYSQL_ASSOC);
  166. return reset($char);
  167. }
  169. /**
  170. * 创建索引语法
  171. */
  172. public function create_indexSyntax($table) {
  173. if (empty($table)) {
  174. return array();
  175. }
  176. $indexing = $this->show_index($table);
  177. $syntax = array();
  178. $indexSyntax = array();
  179. foreach ($indexing as $index) {
  180. $syntax[$index['Index_type']][$index['Key_name']][] = $index['Column_name'];
  181. }
  183. foreach ($syntax as $index_type => $index_value) {
  184. foreach ($index_value as $key_name => $columns) {
  185. if ($key_name == 'PRIMARY') {
  186. $indexSyntax[] = 'PRIMARY KEY (`' . implode("`,`", $columns) . '`)';
  187. } else {
  188. if ($index_type == 'FULLTEXT') {
  189. $indexSyntax[] = "FULLTEXT KEY `{$key_name}` (`" . implode("`,`", $columns) . '`)';
  190. } else{
  191. $indexSyntax[] = "KEY `{$key_name}` USING {$index_type} (`" . implode("`,`", $columns) . '`)';
  192. }
  193. }
  194. }
  195. }
  197. return implode(',' . PHP_EOL, $indexSyntax) . PHP_EOL;
  198. }
  200. /**
  201. * 创建 insert 语法
  202. */
  203. public function create_insertSyntax($table) {
  204. if (empty($table)) {
  205. return '';
  206. }
  207. $sql = "SELECT * FROM {$table}";
  208. $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
  209. $insertStr = '';
  210. if ($result) {
  211. $first = reset($result);
  212. $key = implode('`,`', array_keys($first));
  213. $insert = "INSERT INTO `{$table}` (`{$key}`) VALUES ";
  214. $valuesStr = array();
  215. foreach ($result as $value) {
  216. $values = array();
  217. foreach ($value as $v) {
  218. $v = mysql_real_escape_string($v);
  219. $values[] = preg_replace("#\\\+#", "\\", $v);
  220. }
  221. $valuesStr[] = "('" . implode("','", $values) . "')";
  222. }
  223. $valuesStr = array_chunk($valuesStr, 5000);
  224. foreach ($valuesStr as $str) {
  225. $insertStr .= $insert . implode(',', $str) . ';' . PHP_EOL;
  226. }
  228. }
  229. return $insertStr . str_repeat(PHP_EOL, 3);
  230. }
  232. }
  234. $export = '';
  235. $test = new MysqlExport('localhost', 'root', '', 'pm_cms');
  236. $char = $test->show_database_char();
  237. $test->query("SET NAMES {$char['Value']}");
  238. $tables = $test->show_tables();
  239. foreach ($tables as $table) {
  240. list($table_name) = array_values($table);
  241. $export .= $test->show_create_table($table_name);
  242. $export .= $test->create_insertSyntax($table_name);
  243. }
  244. $fp = fopen('pm_cms.sql', 'w');
  245. fwrite($fp, $export);
  246. fclose($fp);
  247. ?>
复制代码


声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
上一篇：微信公众平台天气查询，火车票查询，快递查询，小黄鸡，翻译，公交查询的实现 下一篇：日历表格

相关文章

查看更多