备份MYSQL数据表结构与数据库结构教程

下面给各位介绍一篇备份MYSQL数据表结构与数据库结构教程,希望此教程能帮助到各位哦,有兴趣了解mysql备份数据表结构的朋友会带来帮助哦.

备份MYSQL数据表结构:

1. [root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin'-dmysqlservers>/backup/mysql-structure-tables-$(date+%F).sql
2. [root@Mysql~]#egrep-v"^$|^--|*"/backup/mysql-structure-tables-2014-06-05.sql
3. DROPTABLEIFEXISTS`servers`;
4. CREATETABLE`servers`(
5. `Server_name`char(64)NOTNULLDEFAULT'',
6. `Host`char(64)NOTNULLDEFAULT'',
7. `Db`char(64)NOTNULLDEFAULT'',
8. `Username`char(64)NOTNULLDEFAULT'',
9. `Password`char(64)NOTNULLDEFAULT'',
10. `Port`int(4)NOTNULLDEFAULT'0',
11. `Socket`char(64)NOTNULLDEFAULT'',
12. `Wrapper`char(64)NOTNULLDEFAULT'',
13. `Owner`char(64)NOTNULLDEFAULT'',
14. PRIMARYKEY(`Server_name`)
15. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='MySQLForeignServerstable';
16. [root@Mysql~]#

备份MYSQL数据库结构:

1. [root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p'admin123'-dmysql>/backup/mysql-structure-$(date+%F).sql
2. [root@Mysql~]#egrep-v"^$|^--|*"/backup/mysql-structure-2014-06-05.sql
3. DROPTABLEIFEXISTS`columns_priv`;
4. CREATETABLE`columns_priv`(
5. `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',
6. `Db`char(64)COLLATEutf8_binNOTNULLDEFAULT'',
7. ############################省略############################
8. PRIMARYKEY(`Host`,`Db`,`User`,`Table_name`,`Column_name`)
9. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Columnprivileges';
10. DROPTABLEIFEXISTS`db`;
11. CREATETABLE`db`(
12. `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',
13. `Db`char(64)COLLATEutf8_binNOTNULLDEFAULT'',
14. `User`char(16)COLLATEutf8_binNOTNULLDEFAULT'',
15. ############################省略############################
16. `Event_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',
17. `Trigger_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',
18. PRIMARYKEY(`Host`,`Db`,`User`),
19. KEY`User`(`User`)
20. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Databaseprivileges';
21. DROPTABLEIFEXISTS`event`;
22. CREATETABLE`event`(
23. `db`char(64)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'',
24. `name`char(64)NOTNULLDEFAULT'',
25. ############################省略############################
26. `originator`int(10)unsignedNOTNULL,
27. `time_zone`char(64)CHARACTERSETlatin1NOTNULLDEFAULT'SYSTEM',
28. ############################省略############################
29. `db_collation`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
30. `body_utf8`longblob,
31. PRIMARYKEY(`db`,`name`)
32. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='Events';
33. DROPTABLEIFEXISTS`func`;
34. CREATETABLE`func`(
35. `name`char(64)COLLATEutf8_binNOTNULLDEFAULT'',
36. ############################省略############################
37. `type`enum('function','aggregate')CHARACTERSETutf8NOTNULL,
38. PRIMARYKEY(`name`)
39. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Userdefinedfunctions';
40. DROPTABLEIFEXISTS`help_category`;
41. CREATETABLE`help_category`(
42. `help_category_id`smallint(5)unsignedNOTNULL,
43. ############################省略############################
44. PRIMARYKEY(`help_category_id`),
45. UNIQUEKEY`name`(`name`)
46. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='helpcategories';
47. DROPTABLEIFEXISTS`help_keyword`;
48. CREATETABLE`help_keyword`(
49. `help_keyword_id`int(10)unsignedNOTNULL,
50. `name`char(64)NOTNULL,
51. PRIMARYKEY(`help_keyword_id`),
52. UNIQUEKEY`name`(`name`)
53. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='helpkeywords';
54. DROPTABLEIFEXISTS`help_relation`;
55. CREATETABLE`help_relation`(
56. `help_topic_id`int(10)unsignedNOTNULL,
57. `help_keyword_id`int(10)unsignedNOTNULL,
58. PRIMARYKEY(`help_keyword_id`,`help_topic_id`)
59. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='keyword-topicrelation';
60. DROPTABLEIFEXISTS`help_topic`;
61. CREATETABLE`help_topic`(
62. `help_topic_id`int(10)unsignedNOTNULL,
63. ############################省略############################
64. PRIMARYKEY(`help_topic_id`),
65. UNIQUEKEY`name`(`name`)
66. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='helptopics';
67. DROPTABLEIFEXISTS`innodb_index_stats`;
68. CREATETABLE`innodb_index_stats`(
69. `database_name`varchar(64)COLLATEutf8_binNOTNULL,
70. ############################省略############################
71. `stat_description`varchar(1024)COLLATEutf8_binNOTNULL,
72. PRIMARYKEY(`database_name`,`table_name`,`index_name`,`stat_name`)
73. )ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binSTATS_PERSISTENT=0;
74. DROPTABLEIFEXISTS`innodb_table_stats`;
75. CREATETABLE`innodb_table_stats`(
76. `database_name`varchar(64)COLLATEutf8_binNOTNULL,
77. ############################省略############################
78. `sum_of_other_index_sizes`bigint(20)unsignedNOTNULL,
79. PRIMARYKEY(`database_name`,`table_name`)
80. )ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binSTATS_PERSISTENT=0;
81. DROPTABLEIFEXISTS`ndb_binlog_index`;
82. CREATETABLE`ndb_binlog_index`(
83. `Position`bigint(20)unsignedNOTNULL,
84. `File`varchar(255)NOTNULL,
85. ############################省略############################
86. `orig_epoch`bigint(20)unsignedNOTNULL,
87. `gci`int(10)unsignedNOTNULL,
88. PRIMARYKEY(`epoch`,`orig_server_id`,`orig_epoch`)
89. )ENGINE=MyISAMDEFAULTCHARSET=utf8;
90. DROPTABLEIFEXISTS`plugin`;
91. CREATETABLE`plugin`(
92. `name`varchar(64)NOTNULLDEFAULT'',
93. `dl`varchar(128)NOTNULLDEFAULT'',
94. PRIMARYKEY(`name`)
95. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='MySQLplugins';
96. DROPTABLEIFEXISTS`proc`;
97. CREATETABLE`proc`(
98. `db`char(64)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'',
99. `name`char(64)NOTNULLDEFAULT'',
100. `type`enum('FUNCTION','PROCEDURE')NOTNULL,
101. `specific_name`char(64)NOTNULLDEFAULT'',
102. `language`enum('SQL')NOTNULLDEFAULT'SQL',
103. `sql_data_access`enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')NOTNULLDEFAULT'CONTAINS_SQL',
104. `is_deterministic`enum('YES','NO')NOTNULLDEFAULT'NO',
105. ############################省略############################
106. `modified`timestampNOTNULLDEFAULT'0000-00-0000:00:00',
107. `sql_mode`set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')NOTNULLDEFAULT'',
108. `comment`textCHARACTERSETutf8COLLATEutf8_binNOTNULL,
109. `character_set_client`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
110. `collation_connection`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
111. `db_collation`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
112. `body_utf8`longblob,
113. PRIMARYKEY(`db`,`name`,`type`)
114. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='StoredProcedures';
115. DROPTABLEIFEXISTS`procs_priv`;
116. CREATETABLE`procs_priv`(
117. `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',
118. `Db`char(64)COLLATEutf8_binNOTNULLDEFAULT'',
119. ############################省略############################
120. `Timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
121. PRIMARYKEY(`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
122. KEY`Grantor`(`Grantor`)
123. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Procedureprivileges';
124. DROPTABLEIFEXISTS`proxies_priv`;
125. CREATETABLE`proxies_priv`(
126. `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',
127. ############################省略############################
128. `Timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
129. PRIMARYKEY(`Host`,`User`,`Proxied_host`,`Proxied_user`),
130. KEY`Grantor`(`Grantor`)
131. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Userproxyprivileges';
132. DROPTABLEIFEXISTS`servers`;
133. CREATETABLE`servers`(
134. `Server_name`char(64)NOTNULLDEFAULT'',
135. `Host`char(64)NOTNULLDEFAULT'',
136. ############################省略############################
137. `Wrapper`char(64)NOTNULLDEFAULT'',
138. `Owner`char(64)NOTNULLDEFAULT'',
139. PRIMARYKEY(`Server_name`)
140. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='MySQLForeignServerstable';
141. DROPTABLEIFEXISTS`slave_master_info`;
142. CREATETABLE`slave_master_info`(
143. `Number_of_lines`int(10)unsignedNOTNULLCOMMENT'Numberoflinesinthefile.',
144. `Master_log_name`textCHARACTERSETutf8COLLATEutf8_binNOTNULLCOMMENT'Thenameofthemasterbinarylogcurrentlybeingreadfromthemaster.',
145. ############################省略############################
146. `Id`int(10)unsignedNOTNULLCOMMENT'InternalIdthatuniquelyidentifiesthisrecord.',
147. PRIMARYKEY(`Id`)
148. )ENGINE=InnoDBDEFAULTCHARSET=utf8STATS_PERSISTENT=0COMMENT='RelayLogInformation';
149. DROPTABLEIFEXISTS`slave_worker_info`;
150. CREATETABLE`slave_worker_info`(
151. `Id`int(10)unsignedNOTNULL,
152. `Relay_log_name`textCHARACTERSETutf8COLLATEutf8_binNOTNULL,
153. ############################省略############################
154. `Checkpoint_group_size`int(10)unsignedNOTNULL,
155. `Checkpoint_group_bitmap`blobNOTNULL,
156. PRIMARYKEY(`Id`)
157. )ENGINE=InnoDBDEFAULTCHARSET=utf8STATS_PERSISTENT=0COMMENT='WorkerInformation';
158. DROPTABLEIFEXISTS`tables_priv`;
159. CREATETABLE`tables_priv`(
160. `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',
161. ############################省略############################
162. `Timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
163. `Table_priv`set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','CreateView','Showview','Trigger')CHARACTERSETutf8NOTNULLDEFAULT'',
164. `Column_priv`set('Select','Insert','Update','References')CHARACTERSETutf8NOTNULLDEFAULT'',
165. PRIMARYKEY(`Host`,`Db`,`User`,`Table_name`),
166. KEY`Grantor`(`Grantor`)
167. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Tableprivileges';
168. DROPTABLEIFEXISTS`time_zone`;
169. CREATETABLE`time_zone`(
170. `Time_zone_id`int(10)unsignedNOTNULLAUTO_INCREMENT,
171. `Use_leap_seconds`enum('Y','N')NOTNULLDEFAULT'N',
172. PRIMARYKEY(`Time_zone_id`)
173. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='Timezones';
174. DROPTABLEIFEXISTS`time_zone_leap_second`;
175. CREATETABLE`time_zone_leap_second`(
176. `Transition_time`bigint(20)NOTNULL,
177. `Correction`int(11)NOTNULL,
178. PRIMARYKEY(`Transition_time`)
179. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='Leapsecondsinformationfortimezones';
180. DROPTABLEIFEXISTS`time_zone_name`;
181. CREATETABLE`time_zone_name`(
182. `Name`char(64)NOTNULL,
183. `Time_zone_id`int(10)unsignedNOTNULL,
184. PRIMARYKEY(`Name`)
185. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='Timezonenames';
186. DROPTABLEIFEXISTS`time_zone_transition`;
187. CREATETABLE`time_zone_transition`(
188. `Time_zone_id`int(10)unsignedNOTNULL,
189. ############################省略############################
190. PRIMARYKEY(`Time_zone_id`,`Transition_time`)
191. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='Timezonetransitions';
192. DROPTABLEIFEXISTS`time_zone_transition_type`;
193. CREATETABLE`time_zone_transition_type`(
194. `Time_zone_id`int(10)unsignedNOTNULL,
195. ############################省略############################
196. PRIMARYKEY(`Time_zone_id`,`Transition_type_id`)
197. )ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT='Timezonetransitiontypes';--phpfensi.com
198. DROPTABLEIFEXISTS`user`;
199. CREATETABLE`user`(
200. `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',
201. `User`char(16)COLLATEutf8_binNOTNULLDEFAULT'',
202. ############################省略############################
203. `password_expired`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',
204. PRIMARYKEY(`Host`,`User`)
205. )ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Usersandglobalprivileges';
206. CREATETABLEIFNOTEXISTS`general_log`(
207. `event_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
208. ############################省略############################
209. `argument`mediumtextNOTNULL
210. )ENGINE=CSVDEFAULTCHARSET=utf8COMMENT='Generallog';
211. CREATETABLEIFNOTEXISTS`slow_log`(
212. `start_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
213. ############################省略############################
214. `thread_id`bigint(21)unsignedNOTNULL
215. )ENGINE=CSVDEFAULTCHARSET=utf8COMMENT='Slowlog';
216. [root@Mysql~]#