备份MYSQL数据表结构与数据库结构教程
下面给各位介绍一篇备份MYSQL数据表结构与数据库结构教程,希望此教程能帮助到各位哦,有兴趣了解mysql备份数据表结构的朋友会带来帮助哦.
备份MYSQL数据表结构:
[root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p''admin''-dmysqlservers>/backup/mysql-structure-tables-$(date+%F).sql
[root@Mysql~]#egrep-v"^$|^--|"/backup/mysql-structure-tables-2014-06-05.sql
DROPTABLEIFEXISTS`servers`;
CREATETABLE`servers`(
`Server_name`char(64)NOTNULLDEFAULT'''',
`Host`char(64)NOTNULLDEFAULT'''',
`Db`char(64)NOTNULLDEFAULT'''',
`Username`char(64)NOTNULLDEFAULT'''',
`Password`char(64)NOTNULLDEFAULT'''',
`Port`int(4)NOTNULLDEFAULT''0'',
`Socket`char(64)NOTNULLDEFAULT'''',
`Wrapper`char(64)NOTNULLDEFAULT'''',
`Owner`char(64)NOTNULLDEFAULT'''',
PRIMARYKEY(`Server_name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''MySQLForeignServerstable'';
[root@Mysql~]#
备份MYSQL数据库结构:
[root@Mysql~]#/usr/local/mysql/bin/mysqldump-uroot-p''admin123''-dmysql>/backup/mysql-structure-$(date+%F).sql
[root@Mysql~]#egrep-v"^$|^--|"/backup/mysql-structure-2014-06-05.sql
DROPTABLEIFEXISTS`columns_priv`;
CREATETABLE`columns_priv`(
`Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'''',
`Db`char(64)COLLATEutf8_binNOTNULLDEFAULT'''',
############################省略############################
PRIMARYKEY(`Host`,`Db`,`User`,`Table_name`,`Column_name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT=''Columnprivileges'';
DROPTABLEIFEXISTS`db`;
CREATETABLE`db`(
`Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'''',
`Db`char(64)COLLATEutf8_binNOTNULLDEFAULT'''',
`User`char(16)COLLATEutf8_binNOTNULLDEFAULT'''',
############################省略############################
`Event_priv`enum(''N'',''Y'')CHARACTERSETutf8NOTNULLDEFAULT''N'',
`Trigger_priv`enum(''N'',''Y'')CHARACTERSETutf8NOTNULLDEFAULT''N'',
PRIMARYKEY(`Host`,`Db`,`User`),
KEY`User`(`User`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT=''Databaseprivileges'';
DROPTABLEIFEXISTS`event`;
CREATETABLE`event`(
`db`char(64)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'''',
`name`char(64)NOTNULLDEFAULT'''',
############################省略############################
`originator`int(10)unsignedNOTNULL,
`time_zone`char(64)CHARACTERSETlatin1NOTNULLDEFAULT''SYSTEM'',
############################省略############################
`db_collation`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
`body_utf8`longblob,
PRIMARYKEY(`db`,`name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''Events'';
DROPTABLEIFEXISTS`func`;
CREATETABLE`func`(
`name`char(64)COLLATEutf8_binNOTNULLDEFAULT'''',
############################省略############################
`type`enum(''function'',''aggregate'')CHARACTERSETutf8NOTNULL,
PRIMARYKEY(`name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT=''Userdefinedfunctions'';
DROPTABLEIFEXISTS`help_category`;
CREATETABLE`help_category`(
`help_category_id`smallint(5)unsignedNOTNULL,
############################省略############################
PRIMARYKEY(`help_category_id`),
UNIQUEKEY`name`(`name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''helpcategories'';
DROPTABLEIFEXISTS`help_keyword`;
CREATETABLE`help_keyword`(
`help_keyword_id`int(10)unsignedNOTNULL,
`name`char(64)NOTNULL,
PRIMARYKEY(`help_keyword_id`),
UNIQUEKEY`name`(`name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''helpkeywords'';
DROPTABLEIFEXISTS`help_relation`;
CREATETABLE`help_relation`(
`help_topic_id`int(10)unsignedNOTNULL,
`help_keyword_id`int(10)unsignedNOTNULL,
PRIMARYKEY(`help_keyword_id`,`help_topic_id`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''keyword-topicrelation'';
DROPTABLEIFEXISTS`help_topic`;
CREATETABLE`help_topic`(
`help_topic_id`int(10)unsignedNOTNULL,
############################省略############################
PRIMARYKEY(`help_topic_id`),
UNIQUEKEY`name`(`name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''helptopics'';
DROPTABLEIFEXISTS`innodb_index_stats`;
CREATETABLE`innodb_index_stats`(
`database_name`varchar(64)COLLATEutf8_binNOTNULL,
############################省略############################
`stat_description`varchar(1024)COLLATEutf8_binNOTNULL,
PRIMARYKEY(`database_name`,`table_name`,`index_name`,`stat_name`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binSTATS_PERSISTENT=0;
DROPTABLEIFEXISTS`innodb_table_stats`;
CREATETABLE`innodb_table_stats`(
`database_name`varchar(64)COLLATEutf8_binNOTNULL,
############################省略############################
`sum_of_other_index_sizes`bigint(20)unsignedNOTNULL,
PRIMARYKEY(`database_name`,`table_name`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binSTATS_PERSISTENT=0;
DROPTABLEIFEXISTS`ndb_binlog_index`;
CREATETABLE`ndb_binlog_index`(
`Position`bigint(20)unsignedNOTNULL,
`File`varchar(255)NOTNULL,
############################省略############################
`orig_epoch`bigint(20)unsignedNOTNULL,
`gci`int(10)unsignedNOTNULL,
PRIMARYKEY(`epoch`,`orig_server_id`,`orig_epoch`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
DROPTABLEIFEXISTS`plugin`;
CREATETABLE`plugin`(
`name`varchar(64)NOTNULLDEFAULT'''',
`dl`varchar(128)NOTNULLDEFAULT'''',
PRIMARYKEY(`name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''MySQLplugins'';
DROPTABLEIFEXISTS`proc`;
CREATETABLE`proc`(
`db`char(64)CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT'''',
`name`char(64)NOTNULLDEFAULT'''',
`type`enum(''FUNCTION'',''PROCEDURE'')NOTNULL,
`specific_name`char(64)NOTNULLDEFAULT'''',
`language`enum(''SQL'')NOTNULLDEFAULT''SQL'',
`sql_data_access`enum(''CONTAINS_SQL'',''NO_SQL'',''READS_SQL_DATA'',''MODIFIES_SQL_DATA'')NOTNULLDEFAULT''CONTAINS_SQL'',
`is_deterministic`enum(''YES'',''NO'')NOTNULLDEFAULT''NO'',
############################省略############################
`modified`timestampNOTNULLDEFAULT''0000-00-0000:00:00'',
`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'''',
`comment`textCHARACTERSETutf8COLLATEutf8_binNOTNULL,
`character_set_client`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
`collation_connection`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
`db_collation`char(32)CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
`body_utf8`longblob,
PRIMARYKEY(`db`,`name`,`type`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''StoredProcedures'';
DROPTABLEIFEXISTS`procs_priv`;
CREATETABLE`procs_priv`(
`Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'''',
`Db`char(64)COLLATEutf8_binNOTNULLDEFAULT'''',
############################省略############################
`Timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
PRIMARYKEY(`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
KEY`Grantor`(`Grantor`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT=''Procedureprivileges'';
DROPTABLEIFEXISTS`proxies_priv`;
CREATETABLE`proxies_priv`(
`Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'''',
############################省略############################
`Timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
PRIMARYKEY(`Host`,`User`,`Proxied_host`,`Proxied_user`),
KEY`Grantor`(`Grantor`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT=''Userproxyprivileges'';
DROPTABLEIFEXISTS`servers`;
CREATETABLE`servers`(
`Server_name`char(64)NOTNULLDEFAULT'''',
`Host`char(64)NOTNULLDEFAULT'''',
############################省略############################
`Wrapper`char(64)NOTNULLDEFAULT'''',
`Owner`char(64)NOTNULLDEFAULT'''',
PRIMARYKEY(`Server_name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''MySQLForeignServerstable'';
DROPTABLEIFEXISTS`slave_master_info`;
CREATETABLE`slave_master_info`(
`Number_of_lines`int(10)unsignedNOTNULLCOMMENT''Numberoflinesinthefile.'',
`Master_log_name`textCHARACTERSETutf8COLLATEutf8_binNOTNULLCOMMENT''Thenameofthemasterbinarylogcurrentlybeingreadfromthemaster.'',
############################省略############################
`Id`int(10)unsignedNOTNULLCOMMENT''InternalIdthatuniquelyidentifiesthisrecord.'',
PRIMARYKEY(`Id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8STATS_PERSISTENT=0COMMENT=''RelayLogInformation'';
DROPTABLEIFEXISTS`slave_worker_info`;
CREATETABLE`slave_worker_info`(
`Id`int(10)unsignedNOTNULL,
`Relay_log_name`textCHARACTERSETutf8COLLATEutf8_binNOTNULL,
############################省略############################
`Checkpoint_group_size`int(10)unsignedNOTNULL,
`Checkpoint_group_biwww.shanxiwang.nettmap`blobNOTNULL,
PRIMARYKEY(`Id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8STATS_PERSISTENT=0COMMENT=''WorkerInformation'';
DROPTABLEIFEXISTS`tables_priv`;
CREATETABLE`tables_priv`(
`Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'''',
############################省略############################
`Timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
`Table_priv`set(''Select'',''Insert'',''Update'',''Delete'',''Create'',''Drop'',''Grant'',''References'',''Index'',''Alter'',''CreateView'',''Showview'',''Trigger'')CHARACTERSETutf8NOTNULLDEFAULT'''',
`Column_priv`set(''Select'',''Insert'',''Update'',''References'')CHARACTERSETutf8NOTNULLDEFAULT'''',
PRIMARYKEY(`Host`,`Db`,`User`,`Table_name`),
KEY`Grantor`(`Grantor`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT=''Tableprivileges'';
DROPTABLEIFEXISTS`time_zone`;
CREATETABLE`time_zone`(
`Time_zone_id`int(10)unsignedNOTNULLAUTO_INCREMENT,
`Use_leap_seconds`enum(''Y'',''N'')NOTNULLDEFAULT''N'',
PRIMARYKEY(`Time_zone_id`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''Timezones'';
DROPTABLEIFEXISTS`time_zone_leap_second`;
CREATETABLE`time_zone_leap_second`(
`Transition_time`bigint(20)NOTNULL,
`Correction`int(11)NOTNULL,
PRIMARYKEY(`Transition_time`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''Leapsecondsinformationfortimezones'';
DROPTABLEIFEXISTS`time_zone_name`;
CREATETABLE`time_zone_name`(
`Name`char(64)NOTNULL,
`Time_zone_id`int(10)unsignedNOTNULL,
PRIMARYKEY(`Name`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''Timezonenames'';
DROPTABLEIFEXISTS`time_zone_transition`;
CREATETABLE`time_zone_transition`(
`Time_zone_id`int(10)unsignedNOTNULL,
############################省略############################
PRIMARYKEY(`Time_zone_id`,`Transition_time`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''Timezonetransitions'';
DROPTABLEIFEXISTS`time_zone_transition_type`;
CREATETABLE`time_zone_transition_type`(
`Time_zone_id`int(10)unsignedNOTNULL,
############################省略############################
PRIMARYKEY(`Time_zone_id`,`Transition_type_id`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COMMENT=''Timezonetransitiontypes'';--phpfensi.com
DROPTABLEIFEXISTS`user`;
CREATETABLE`user`(
`Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'''',
`User`char(16)COLLATEutf8_binNOTNULLDEFAULT'''',
############################省略############################
`password_expired`enum(''N'',''Y'')CHARACTERSETutf8NOTNULLDEFAULT''N'',
PRIMARYKEY(`Host`,`User`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT=''Usersandglobalprivileges'';
CREATETABLEIFNOTEXISTS`general_log`(
`event_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
############################省略############################
`argument`mediumtextNOTNULL
)ENGINE=CSVDEFAULTCHARSET=utf8COMMENT=''Generallog'';
CREATETABLEIFNOTEXISTS`slow_log`(
`start_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
############################省略############################
`thread_id`bigint(21)unsignedNOTNULL
)ENGINE=CSVDEFAULTCHARSET=utf8COMMENT=''Slowlog'';
[root@Mysql~]#
|
|
