MySQL基础： information_schema视图库（虚拟库）-MySQL学习笔记

文章目录

information_schema数据库表说明：
生产中用的最多的就是库中的tables表
利用concat生成整个world库下的所有表的单独备份语句
可以举一反三，比如单独备份每个库什么的

表：元数据+数据行
元数据存储在'基表'，是我们无法直接访问的
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 TABLE TABLES (

  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

MySQL基础： information_schema视图库（虚拟库）

information_schema数据库表说明：

生产中用的最多的就是库中的tables表

利用concat生成整个world库下的所有表的单独备份语句

可以举一反三，比如单独备份每个库什么的

近期文章

友情链接