Backup MySQL data table structure and database structure tutorial

Back up the MySQL data table structure

[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 (+) not NULL DEFAULT ',
' Host ' char (+) not NULL DEFAULT ',
' Db ' char (+) not NULL DEFAULT ',
' Username ' char (+) not NULL DEFAULT ',
' Password ' char (+) not NULL DEFAULT ',
' Port ' int (4) not NULL DEFAULT ' 0 ',
' Socket ' char (+) not NULL DEFAULT ',
' Wrapper ' char (+) not NULL DEFAULT ',
' Owner ' char (+) not NULL DEFAULT ',
PRIMARY KEY (' server_name ')
Engine=myisam DEFAULT Charset=utf8 comment= ' MySQL Foreign Servers table ';
[Root@mysql ~]#

backup MySQL database structure

[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 (COLLATE utf8_bin not NULL DEFAULT '),
' Db ' char (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 (COLLATE utf8_bin not NULL DEFAULT '),
' Db ' char (COLLATE utf8_bin not NULL DEFAULT '),
' User ' char (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 (CHARACTER SET UTF8 COLLATE utf8_bin not NULL DEFAULT ',
' Name ' char ' not NULL DEFAULT ',
########################### #省略 ############################
' Originator ' int (a) unsigned not NULL,
' Time_zone ' char () CHARACTER SET latin1 not NULL DEFAULT ' SYSTEM ',
########################### #省略 ############################
' Db_collation ' char () 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 (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 (a) unsigned not NULL,
' Name ' char (+) 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 (a) unsigned not NULL,
' help_keyword_id ' int (a) 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 (a) 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 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 COLLATE utf8_bin not NULL,
########################### #省略 ############################
' sum_of_other_index_sizes ' bigint 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 unsigned not NULL,
' File ' varchar (255) Not NULL,
########################### #省略 ############################
' Orig_epoch ' bigint unsigned not NULL,
' GCI ' int (a) 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 ' 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 (CHARACTER SET UTF8 COLLATE utf8_bin not NULL DEFAULT ',
' Name ' char ' not NULL DEFAULT ',
' Type ' enum (' FUNCTION ', ' PROCEDURE ') not NULL,
' Specific_name ' char (+) 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 () character set UTF8 COLLATE utf8_bin DEFAULT NULL,
' Collation_connection ' char () CHARACTER SET UTF8 COLLATE utf8_bin DEFAULT NULL,
' Db_collation ' char () 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 (COLLATE utf8_bin not NULL DEFAULT '),
' Db ' char (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 (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 (+) not NULL DEFAULT ',
' Host ' char (+) not NULL DEFAULT ',
########################### #省略 ############################
' Wrapper ' char (+) not NULL DEFAULT ',
' Owner ' char (+) 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 (a) 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 B Eing read from the master. ',
########################### #省略 ############################
' id ' int ' unsigned not NULL COMMENT ' Internal ID ' uniquely identifies the 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 (a) unsigned not NULL,
' Relay_log_name ' text CHARACTER SET UTF8 COLLATE utf8_bin not NULL,
########################### #省略 ############################
' Checkpoint_group_size ' int (a) 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 (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 (a) 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 not NULL,
' Correction ' int (one) is 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 (+) not NULL,
' time_zone_id ' int (a) 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 (a) 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 (a) unsigned not NULL,
########################### #省略 ############################
PRIMARY KEY (' time_zone_id ', ' transition_type_id ')
) Engine=myisam DEFAULT Charset=utf8 comment= ' time zone transition types ';
DROP TABLE IF EXISTS ' user ';
CREATE TABLE ' user ' (
' Host ' char (COLLATE utf8_bin not NULL DEFAULT '),
' User ' char (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 unsigned not NULL
) engine=csv DEFAULT charset=utf8 comment= ' slow log ';
[Root@mysql ~]#