系统表查询

【问题】

mysql> select * from mysql.tablespaces;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.tablespaces' is rejected.

【解决办法】

参考https://zhuanlan.zhihu.com/p/377961430

mysql> SELECT name FROM mysql.tables where schema_id=1 AND hidden='System';
+------------------------------+
| name                         |
+------------------------------+
| catalogs                     |
| character_sets               |
| check_constraints            |
| collations                   |
| column_statistics            |
| column_type_elements         |
| columns                      |
| dd_properties                |
| events                       |
| foreign_key_column_usage     |
| foreign_keys                 |
| index_column_usage           |
| index_partitions             |
| index_stats                  |
| indexes                      |
| innodb_ddl_log               |
| innodb_dynamic_metadata      |
| parameter_type_elements      |
| parameters                   |
| resource_groups              |
| routines                     |
| schemata                     |
| st_spatial_reference_systems |
| table_partition_values       |
| table_partitions             |
| table_stats                  |
| tables                       |
| tablespace_files             |
| tablespaces                  |
| triggers                     |
| view_routine_usage           |
| view_table_usage             |
+------------------------------+
32 rows in set (0.02 sec)

　　

mysql> select * from mysql.tablespaces;
+----+------------------+---------------+------------------------------------------------------------------------------+---------+--------+------------------+
| id | name             | options       | se_private_data                                                              | comment | engine | engine_attribute |
+----+------------------+---------------+------------------------------------------------------------------------------+---------+--------+------------------+
|  1 | mysql            | encryption=N; | flags=18432;id=4294967294;server_version=80022;space_version=1;state=normal; |         | InnoDB | NULL             |
|  2 | innodb_system    | encryption=N; | flags=18432;id=0;server_version=80022;space_version=1;state=normal;          |         | InnoDB | NULL             |
|  3 | innodb_temporary | encryption=N; | flags=4096;id=4294967293;server_version=80022;space_version=1;state=normal;  |         | InnoDB | NULL             |
|  4 | innodb_undo_001  | encryption=N; | flags=0;id=4294967279;server_version=80022;space_version=1;state=active;     |         | InnoDB | NULL             |
|  5 | innodb_undo_002  | encryption=N; | flags=0;id=4294967278;server_version=80022;space_version=1;state=active;     |         | InnoDB | NULL             |
|  6 | sys/sys_config   | encryption=N; | flags=16417;id=1;server_version=80022;space_version=1;state=normal;          |         | InnoDB | NULL             |
|  7 | db1/t1           | encryption=N; | flags=16417;id=2;server_version=80022;space_version=1;state=normal;          |         | InnoDB | NULL             |
|  8 | db1/t2           | encryption=N; | flags=16417;id=3;server_version=80022;space_version=1;state=normal;          |         | InnoDB | NULL             |
+----+------------------+---------------+------------------------------------------------------------------------------+---------+--------+------------------+
8 rows in set (0.00 sec)