MySQL的备份

本来想着用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. ?>

复制代码