表:元数据+数据行
元数据存储在’基表’,是我们无法直接访问的
mysql给我们提供了DDL,DCL来进行对元数据修改
提供了information_schema,和show语句查询元数据
information_schema是mysql运行之后生成的运行在内存中的库
information_schema数据库表说明:
db01 [information_schema]>use information_schema; db01 [information_schema]>show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS |mysql实例可用字符集信息 | COLLATIONS |关于各字符集的对照信息 | COLLATION_CHARACTER_SET_APPLICABILITY |可用于校对的字符集 | COLUMNS |提供了表中的列信息,详细表述了某张表的所有列以及每个列的信息 | COLUMN_PRIVILEGES |列权限信息 | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE |具有约束的键列 | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES |关于存储子程序的信息 | SCHEMATA |提供了当前mysql实例中所有数据库的信息,show database的结果取值此表 | SCHEMA_PRIVILEGES |数据库权限信息 | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | 提供了关于表索引的信息 | TABLES |提供了关于数据库中的表的信息,详细表述了某个表属于哪个schema,表类型,表引擎, 创建时间等信息,show tables from schemaname的结果取之此表 | TABLESPACES | | TABLE_CONSTRAINTS |存在约束的表,以及表的约束类型 | TABLE_PRIVILEGES |表权限信息 | TRIGGERS |关于触发程序的信息 | USER_PRIVILEGES |用户权限,用户拥有具体权限的表 | VIEWS |给出了关于数据库中的试图的信息,需要与show views权限 | INNODB_LOCKS | | INNODB_TRX | | INNODB_SYS_DATAFILES | | INNODB_FT_CONFIG | | INNODB_SYS_VIRTUAL | | INNODB_CMP | | INNODB_FT_BEING_DELETED | | INNODB_CMP_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMPMEM_RESET | | INNODB_FT_DELETED | | INNODB_BUFFER_PAGE_LRU | | INNODB_LOCK_WAITS | | INNODB_TEMP_TABLE_INFO | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_FIELDS | | INNODB_CMP_PER_INDEX_RESET | | INNODB_BUFFER_PAGE | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_INDEX_TABLE | | INNODB_FT_INDEX_CACHE | | INNODB_SYS_TABLESPACES | | INNODB_METRICS | | INNODB_SYS_FOREIGN_COLS | | INNODB_CMPMEM | | INNODB_BUFFER_POOL_STATS | | INNODB_SYS_COLUMNS | | INNODB_SYS_FOREIGN | | INNODB_SYS_TABLESTATS | +---------------------------------------+ 61 rows in set (0.00 sec)
生产中用的最多的就是库中的tables表
db01 [information_schema]>show create table tables; | TABLES | CREATE TEMPORARY TABLETABLES
(TABLE_CATALOG
varchar(512) NOT NULL DEFAULT '',TABLE_SCHEMA
varchar(64) NOT NULL DEFAULT '', 库名TABLE_NAME
varchar(64) NOT NULL DEFAULT '', 表名TABLE_TYPE
varchar(64) NOT NULL DEFAULT '',ENGINE
varchar(64) DEFAULT NULL, 引擎VERSION
bigint(21) unsigned DEFAULT NULL,ROW_FORMAT
varchar(10) DEFAULT NULL,TABLE_ROWS
bigint(21) unsigned DEFAULT NULL, 表的行数AVG_ROW_LENGTH
bigint(21) unsigned DEFAULT NULL, 表中平均行大小,字节DATA_LENGTH
bigint(21) unsigned DEFAULT NULL,MAX_DATA_LENGTH
bigint(21) unsigned DEFAULT NULL,INDEX_LENGTH
bigint(21) unsigned DEFAULT NULL, 索引占用的空间大小DATA_FREE
bigint(21) unsigned DEFAULT NULL,AUTO_INCREMENT
bigint(21) unsigned DEFAULT NULL,CREATE_TIME
datetime DEFAULT NULL,UPDATE_TIME
datetime DEFAULT NULL,CHECK_TIME
datetime DEFAULT NULL,TABLE_COLLATION
varchar(32) DEFAULT NULL,CHECKSUM
bigint(21) unsigned DEFAULT NULL,CREATE_OPTIONS
varchar(255) DEFAULT NULL,TABLE_COMMENT
varchar(2048) NOT NULL DEFAULT '' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
查询整个数据库中所有库和对应的表的信息 db01 [information_schema]>select table_schema,group_concat(table_name) from tables group by table_schema; | information_schema | INNODB_METRICS,SESSION_VARIABLES,INNODB_LOCK_WAITS,OPTIMIZER_TRACE,INNODB_TRX,CHARACTER_SETS,INNODB_SYS_TABLESPACES,SESSION_STATUS,KEY_COLUMN_USAGE,INNODB_BUFFER_PAGE_LRU,INNODB_LOCKS,INNODB_FT_INDEX_CACHE,SCHEMA_PRIVILEGES,GLOBAL_VARIABLES,INNODB_FT_DELETED,VIEWS,INNODB_FT_INDEX_TABLE,SCHEMATA, | | mysql | time_zone,plugin,event,tables_priv,ndb_binlog_index,engine_cost,slow_log,innodb_table_stats,db,slave_worker_info,innodb_index_stats,columns_priv,slave_relay_log_info,help_topic,slave_master_info,help_relation,user,servers,
统计所有库的表的个数 db01 [information_schema]>select table_schema,count(table_name) from tables group by table_schema; +--------------------+-------------------+ | table_schema | count(table_name) | +--------------------+-------------------+ | information_schema | 61 | | mysql | 31 | | performance_schema | 87 | | sys | 101 | | test1 | 1 | | world | 3 | +--------------------+-------------------+ 6 rows in set (0.00 sec)
查询innodb引擎的表及所在的库 db01 [information_schema]>select table_schema,table_name,engine from tables where engine='innodb'; +--------------------+---------------------------+--------+ | table_schema | table_name | engine | +--------------------+---------------------------+--------+ | information_schema | COLUMNS | InnoDB | | information_schema | EVENTS | InnoDB | | information_schema | OPTIMIZER_TRACE | InnoDB | | information_schema | PARAMETERS | InnoDB | | information_schema | PARTITIONS | InnoDB | | information_schema | PLUGINS | InnoDB | | information_schema | PROCESSLIST | InnoDB | | information_schema | ROUTINES | InnoDB | | information_schema | TRIGGERS | InnoDB | | information_schema | VIEWS | InnoDB | | mysql | engine_cost | InnoDB | | mysql | gtid_executed | InnoDB | | mysql | help_category | InnoDB | | mysql | help_keyword | InnoDB | | mysql | help_relation | InnoDB | | mysql | help_topic | InnoDB | | mysql | innodb_index_stats | InnoDB | | mysql | innodb_table_stats | InnoDB |
统计world库下每张表磁盘占用的空间 db01[information_schema]>selecttable_schema,table_name,(table_rows*avg_row_length+index_length)/1024 from tables where table_schema='world'; +--------------+-----------------+-----------------------------------------------+ | table_schema | table_name | (table_rowsavg_row_length+index_length)/1024 | +--------------+-----------------+-----------------------------------------------+ | world | city | 524.7148 | | world | country | 95.9268 | | world | countrylanguage | 159.1328 | +--------------+-----------------+-----------------------------------------------+ 3 rows in set (0.00 sec) 用行数×每行字节+索引大小,就是每张表的大小 括号里面是字节单位,所以除了一个1024,是kb单位
所有库占用的空间 db01[information_schema]>selecttable_schema,sum (table_rows*avg_row_length+index_length)/1024 from tables group by table_schema; +--------------------+--------------------------------------------------+ | information_schema | NULL | | mysql | 2340.0479 | | performance_schema | 0.0000 | | sys | 15.9961 | | test1 | 15.9961 | | world | 779.7744 | +--------------------+--------------------------------------------------+
利用concat生成整个world库下的所有表的单独备份语句
就是拼接备份语句,自己添加的需要用引号引起来 db01 [information_schema]>SELECT CONCAT("mysqldump -uroot -p123 ",TABLE_SCHEMA ," ",table_name," >/bak/",TABLE_SCHEMA,"_",table_name,".sql") -> FROM tables -> WHERE table_schema='world' -> INTO OUTFILE '/tmp/bak.sh'; Query OK, 3 rows affected (0.41 sec) [root@db01 ~]# cat /tmp/bak.sh mysqldump -uroot -p123 world city>/bak/world_city.sql mysqldump -uroot -p123 world country>/bak/world_country.sql mysqldump -uroot -p123 world countrylanguage>/bak/world_countrylanguage.sql
评论前必须登录!
注册