备份MYSQL数据表结构与数据库结构教程步骤
发布时间：2022-06-29 21:02:01 所属栏目：MySql教程 来源：互联网
导读：下面给各位介绍一篇备份MYSQL数据表结构与数据库结构教程,希望此教程能帮助到各位哦,有兴趣了解mysql备份数据表结构的朋友会带来帮助哦. 备份MYSQL数据表结构: [root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -padmin -d mysql servers/backup/mysq
|
下面给各位介绍一篇备份MYSQL数据表结构与数据库结构教程,希望此教程能帮助到各位哦,有兴趣了解mysql备份数据表结构的朋友会带来帮助哦. 备份MYSQL数据表结构: [root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -d mysql servers>/backup/mysql-structure-tables-$(date +%F).sql [root@Mysql ~]# egrep -v "^$|^--|*" /backup/mysql-structure-tables-2014-06-05.sql DROP TABLE IF EXISTS `servers`; CREATE TABLE `servers` ( `Server_name` char(64) NOT NULL DEFAULT '', `Host` char(64) NOT NULL DEFAULT '', `Db` char(64) NOT NULL DEFAULT '', `Username` char(64) NOT NULL DEFAULT '', `Password` char(64) NOT NULL DEFAULT '', `Port` int(4) NOT NULL DEFAULT '0', `Socket` char(64) NOT NULL DEFAULT '', `Wrapper` char(64) NOT NULL DEFAULT '', `Owner` char(64) NOT NULL DEFAULT '', PRIMARY KEY (`Server_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'; [root@Mysql ~]# 备份MYSQL数据库结构: [root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin123' -d mysql>/backup/mysql-structure-$(date +%F).sql [root@Mysql ~]# egrep -v "^$|^--|*" /backup/mysql-structure-2014-06-05.sql DROP TABLE IF EXISTS `columns_priv`; CREATE TABLE `columns_priv` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', ############################省略############################ PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges'; DROP TABLE IF EXISTS `db`; CREATE TABLE `db` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', ############################省略############################ `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`Db`,`User`), KEY `User` (`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'; DROP TABLE IF EXISTS `event`; CREATE TABLE `event` ( `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `name` char(64) NOT NULL DEFAULT '', ############################省略############################ `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', ############################省略############################ `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `body_utf8` longblob, PRIMARY KEY (`db`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'; DROP TABLE IF EXISTS `func`; CREATE TABLE `func` ( `name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', ############################省略############################ `type` enum('function','aggregate') CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions'; DROP TABLE IF EXISTS `help_category`; CREATE TABLE `help_category` ( `help_category_id` smallint(5) unsigned NOT NULL, ############################省略############################ PRIMARY KEY (`help_category_id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help categories'; DROP TABLE IF EXISTS `help_keyword`; CREATE TABLE `help_keyword` ( `help_keyword_id` int(10) unsigned NOT NULL, `name` char(64) NOT NULL, PRIMARY KEY (`help_keyword_id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help keywords'; DROP TABLE IF EXISTS `help_relation`; CREATE TABLE `help_relation` ( `help_topic_id` int(10) unsigned NOT NULL, `help_keyword_id` int(10) unsigned NOT NULL, PRIMARY KEY (`help_keyword_id`,`help_topic_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='keyword-topic relation'; DROP TABLE IF EXISTS `help_topic`; CREATE TABLE `help_topic` ( `help_topic_id` int(10) unsigned NOT NULL, ############################省略############################ PRIMARY KEY (`help_topic_id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help topics'; DROP TABLE IF EXISTS `innodb_index_stats`; CREATE TABLE `innodb_index_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, ############################省略############################ `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; DROP TABLE IF EXISTS `innodb_table_stats`; CREATE TABLE `innodb_table_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, ############################省略############################ `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; DROP TABLE IF EXISTS `ndb_binlog_index`; CREATE TABLE `ndb_binlog_index` ( `Position` bigint(20) unsigned NOT NULL, `File` varchar(255) NOT NULL, ############################省略############################ `orig_epoch` bigint(20) unsigned NOT NULL, `gci` int(10) unsigned NOT NULL, PRIMARY KEY (`epoch`,`orig_server_id`,`orig_epoch`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `plugin`; CREATE TABLE `plugin` ( `name` varchar(64) NOT NULL DEFAULT '', `dl` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL plugins'; DROP TABLE IF EXISTS `proc`; CREATE TABLE `proc` ( `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `name` char(64) NOT NULL DEFAULT '', `type` enum('FUNCTION','PROCEDURE') NOT NULL, `specific_name` char(64) NOT NULL DEFAULT '', `language` enum('SQL') NOT NULL DEFAULT 'SQL', `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL', `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO', ############################省略############################ `modified` timestamp NOT NULL DEFAULT '0000-00-00 00: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') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `body_utf8` longblob, PRIMARY KEY (`db`,`name`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'; DROP TABLE IF EXISTS `procs_priv`; CREATE TABLE `procs_priv` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', ############################省略############################ `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`), KEY `Grantor` (`Grantor`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges'; DROP TABLE IF EXISTS `proxies_priv`; CREATE TABLE `proxies_priv` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', ############################省略############################ `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`), KEY `Grantor` (`Grantor`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges'; DROP TABLE IF EXISTS `servers`; CREATE TABLE `servers` ( `Server_name` char(64) NOT NULL DEFAULT '', `Host` char(64) NOT NULL DEFAULT '', ############################省略############################ `Wrapper` char(64) NOT NULL DEFAULT '', `Owner` char(64) NOT NULL DEFAULT '', PRIMARY KEY (`Server_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'; DROP TABLE IF EXISTS `slave_master_info`; CREATE TABLE `slave_master_info` ( `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.', `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.', ############################省略############################ `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.', PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information'; DROP TABLE IF EXISTS `slave_worker_info`; CREATE TABLE `slave_worker_info` ( `Id` int(10) unsigned NOT NULL, `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, ############################省略############################ `Checkpoint_group_size` int(10) unsigned NOT NULL, `Checkpoint_group_bitmap` blob NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information'; DROP TABLE IF EXISTS `tables_priv`; CREATE TABLE `tables_priv` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', ############################省略############################ `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '', `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`), KEY `Grantor` (`Grantor`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'; DROP TABLE IF EXISTS `time_zone`; CREATE TABLE `time_zone` ( `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N', PRIMARY KEY (`Time_zone_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones'; DROP TABLE IF EXISTS `time_zone_leap_second`; CREATE TABLE `time_zone_leap_second` ( `Transition_time` bigint(20) NOT NULL, `Correction` int(11) NOT NULL, PRIMARY KEY (`Transition_time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Leap seconds information for time zones'; DROP TABLE IF EXISTS `time_zone_name`; CREATE TABLE `time_zone_name` ( `Name` char(64) NOT NULL, `Time_zone_id` int(10) unsigned NOT NULL, PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names'; DROP TABLE IF EXISTS `time_zone_transition`; CREATE TABLE `time_zone_transition` ( `Time_zone_id` int(10) unsigned NOT NULL, ############################省略############################ PRIMARY KEY (`Time_zone_id`,`Transition_time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transitions'; DROP TABLE IF EXISTS `time_zone_transition_type`; CREATE TABLE `time_zone_transition_type` ( `Time_zone_id` int(10) unsigned NOT NULL, ############################省略############################ PRIMARY KEY (`Time_zone_id`,`Transition_type_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transition types'; --phpfensi.com DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', ############################省略############################ `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'; CREATE TABLE IF NOT EXISTS `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ############################省略############################ `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; CREATE TABLE IF NOT EXISTS `slow_log` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ############################省略############################ `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; [root@Mysql ~]#。 （编辑：淮安站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
