欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

MySQL的备份

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