MySQL internal databases
MySQL internal databases are used by MySQL instance to manage and maintain the MySQL server. Following are the four internal databases of MySQL.
1. information_schema
2. mysql
3. performance_schema
4. sys
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
information_schema is a database provides access to metadata about database objects such as tables, columns, data types, and indexes. It allows you to query information about your database structure and configuration.
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| COLUMNS |
| COLUMNS_EXTENSIONS |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEY_COLUMN_USAGE |
| KEYWORDS |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMA_PRIVILEGES |
| SCHEMATA |
| SCHEMATA_EXTENSIONS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| STATISTICS |
| TABLE_CONSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
| TABLE_PRIVILEGES |
| TABLES |
| TABLES_EXTENSIONS |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
| VIEWS |
+---------------------------------------+
79 rows in set (0.00 sec)
Sample Queries
1. List all databases
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;mysql> SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;
+--------------------+
| SCHEMA_NAME |
+--------------------+
| mysql |
| information_schema |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
2. List all tables in a specific database
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';mysql> SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys';
+-----------------------------------------------+
| TABLE_NAME |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_bytes |
| memory_global_total |
| metrics |
| processlist |
| ps_check_lost_instrumentation |
| schema_auto_increment_columns |
| schema_index_statistics |
| schema_object_overview |
| schema_redundant_indexes |
| schema_table_lock_waits |
| schema_table_statistics |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |
| schema_unused_indexes |
| session |
| session_ssl_status |
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
| sys_config |
| user_summary |
| user_summary_by_file_io |
| user_summary_by_file_io_type |
| user_summary_by_stages |
| user_summary_by_statement_latency |
| user_summary_by_statement_type |
| version |
| wait_classes_global_by_avg_latency |
| wait_classes_global_by_latency |
| waits_by_host_by_latency |
| waits_by_user_by_latency |
| waits_global_by_latency |
| x$host_summary |
| x$host_summary_by_file_io |
| x$host_summary_by_file_io_type |
| x$host_summary_by_stages |
| x$host_summary_by_statement_latency |
| x$host_summary_by_statement_type |
| x$innodb_buffer_stats_by_schema |
| x$innodb_buffer_stats_by_table |
| x$innodb_lock_waits |
| x$io_by_thread_by_latency |
| x$io_global_by_file_by_bytes |
| x$io_global_by_file_by_latency |
| x$io_global_by_wait_by_bytes |
| x$io_global_by_wait_by_latency |
| x$latest_file_io |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_bytes |
| x$memory_global_total |
| x$processlist |
| x$ps_digest_95th_percentile_by_avg_us |
| x$ps_digest_avg_latency_distribution |
| x$ps_schema_table_statistics_io |
| x$schema_flattened_keys |
| x$schema_index_statistics |
| x$schema_table_lock_waits |
| x$schema_table_statistics |
| x$schema_table_statistics_with_buffer |
| x$schema_tables_with_full_table_scans |
| x$session |
| x$statement_analysis |
| x$statements_with_errors_or_warnings |
| x$statements_with_full_table_scans |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting |
| x$statements_with_temp_tables |
| x$user_summary |
| x$user_summary_by_file_io |
| x$user_summary_by_file_io_type |
| x$user_summary_by_stages |
| x$user_summary_by_statement_latency |
| x$user_summary_by_statement_type |
| x$wait_classes_global_by_avg_latency |
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
+-----------------------------------------------+
101 rows in set (0.00 sec)
3. Describe the structure of a specific table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
-> FROM information_schema.COLUMNS
-> WHERE TABLE_NAME = 'user_summary' AND TABLE_SCHEMA = 'sys';
+------------------------+-----------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+------------------------+-----------+-------------+----------------+
| user | varchar | YES | NULL |
| statements | decimal | YES | NULL |
| statement_latency | varchar | YES | NULL |
| statement_avg_latency | varchar | YES | NULL |
| table_scans | decimal | YES | NULL |
| file_ios | decimal | YES | NULL |
| file_io_latency | varchar | YES | NULL |
| current_connections | decimal | YES | NULL |
| total_connections | decimal | YES | NULL |
| unique_hosts | bigint | NO | 0 |
| current_memory | varchar | YES | NULL |
| total_memory_allocated | varchar | YES | NULL |
+------------------------+-----------+-------------+----------------+
12 rows in set (0.01 sec)
Print all tables of information_schema database
Switch to information_schema database by executing below command.
use information_schema;You can confirm the database that you are in by executing the query ‘SELECT DATABASE();’.
mysql> use information_schema;
Database changed
mysql>
mysql> SELECT DATABASE();
+--------------------+
| DATABASE() |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)Let’s print all the tables in information_schema table by executing ‘SHOW tables’ command.
mysql> SHOW tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| COLUMNS |
| COLUMNS_EXTENSIONS |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEY_COLUMN_USAGE |
| KEYWORDS |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMA_PRIVILEGES |
| SCHEMATA |
| SCHEMATA_EXTENSIONS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| STATISTICS |
| TABLE_CONSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
| TABLE_PRIVILEGES |
| TABLES |
| TABLES_EXTENSIONS |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
| VIEWS |
+---------------------------------------+
79 rows in set (0.03 sec)
Core tables of information_schema
The information_schema database contains around 79 tables, but we will focus on some of the most commonly used ones.
1. SCHEMATA: Information about all databases.
2. TABLES: Information about all tables.
3. COLUMNS: Information about columns in tables.
4. STATISTICS: Information about table indexes.
5. KEY_COLUMN_USAGE: Information about columns that are indexed.
6. TABLE_CONSTRAINTS: Information about table constraints.
7. USER_PRIVILEGES: Information about user privileges.
SCHEMATA: Information about all databases
The SCHEMATA table provides information about all the databases on the server.
List all databases
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;mysql> SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;
+--------------------+
| SCHEMA_NAME |
+--------------------+
| mysql |
| information_schema |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
Get default character set and collation for each database
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| mysql | utf8mb4 | utf8mb4_0900_ai_ci |
| information_schema | utf8mb3 | utf8mb3_general_ci |
| performance_schema | utf8mb4 | utf8mb4_0900_ai_ci |
| sys | utf8mb4 | utf8mb4_0900_ai_ci |
+--------------------+----------------------------+------------------------+
4 rows in set (0.01 sec)
2. TABLES
The TABLES table provides information about all tables in the databases.
List all tables in a specific database
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
mysql> SELECT TABLE_NAME
-> FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema';
+------------------------------------------------------+
| TABLE_NAME |
+------------------------------------------------------+
| accounts |
| binary_log_transaction_compression_stats |
| cond_instances |
| data_lock_waits |
| data_locks |
| error_log |
| events_errors_summary_by_account_by_error |
| events_errors_summary_by_host_by_error |
| events_errors_summary_by_thread_by_error |
| events_errors_summary_by_user_by_error |
| events_errors_summary_global_by_error |
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name |
| events_stages_summary_by_thread_by_event_name |
| events_stages_summary_by_user_by_event_name |
| events_stages_summary_global_by_event_name |
| events_statements_current |
| events_statements_histogram_by_digest |
| events_statements_histogram_global |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_program |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
| events_transactions_current |
| events_transactions_history |
| events_transactions_history_long |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name |
| events_transactions_summary_by_thread_by_event_name |
| events_transactions_summary_by_user_by_event_name |
| events_transactions_summary_global_by_event_name |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_by_user_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| global_status |
| global_variables |
| host_cache |
| hosts |
| innodb_redo_log_files |
| keyring_component_status |
| keyring_keys |
| log_status |
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
| metadata_locks |
| mutex_instances |
| objects_summary_global_by_type |
| performance_timers |
| persisted_variables |
| prepared_statements_instances |
| processlist |
| replication_applier_configuration |
| replication_applier_filters |
| replication_applier_global_filters |
| replication_applier_status |
| replication_applier_status_by_coordinator |
| replication_applier_status_by_worker |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_connection_configuration |
| replication_connection_status |
| replication_group_member_stats |
| replication_group_members |
| rwlock_instances |
| session_account_connect_attrs |
| session_connect_attrs |
| session_status |
| session_variables |
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_meters |
| setup_metrics |
| setup_objects |
| setup_threads |
| socket_instances |
| socket_summary_by_event_name |
| socket_summary_by_instance |
| status_by_account |
| status_by_host |
| status_by_thread |
| status_by_user |
| table_handles |
| table_io_waits_summary_by_index_usage |
| table_io_waits_summary_by_table |
| table_lock_waits_summary_by_table |
| threads |
| tls_channel_status |
| user_defined_functions |
| user_variables_by_thread |
| users |
| variables_by_thread |
| variables_info |
+------------------------------------------------------+
113 rows in set (0.00 sec)
Get table type (BASE TABLE or VIEW) and row count estimate.
SELECT TABLE_NAME, TABLE_TYPE, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
mysql> SELECT TABLE_NAME, TABLE_TYPE, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql';
+------------------------------------------------------+------------+------------+
| TABLE_NAME | TABLE_TYPE | TABLE_ROWS |
+------------------------------------------------------+------------+------------+
| columns_priv | BASE TABLE | 0 |
| component | BASE TABLE | 0 |
| db | BASE TABLE | 2 |
| default_roles | BASE TABLE | 0 |
| engine_cost | BASE TABLE | 2 |
| func | BASE TABLE | 0 |
| general_log | BASE TABLE | 2 |
| global_grants | BASE TABLE | 21 |
| gtid_executed | BASE TABLE | 0 |
| help_category | BASE TABLE | 53 |
| help_keyword | BASE TABLE | 954 |
| help_relation | BASE TABLE | 2059 |
| help_topic | BASE TABLE | 535 |
| innodb_index_stats | BASE TABLE | 6 |
| innodb_table_stats | BASE TABLE | 2 |
| password_history | BASE TABLE | 0 |
| plugin | BASE TABLE | 0 |
| procs_priv | BASE TABLE | 0 |
| proxies_priv | BASE TABLE | 1 |
| replication_asynchronous_connection_failover | BASE TABLE | 0 |
| replication_asynchronous_connection_failover_managed | BASE TABLE | 0 |
| replication_group_configuration_version | BASE TABLE | 1 |
| replication_group_member_actions | BASE TABLE | 2 |
| role_edges | BASE TABLE | 0 |
| server_cost | BASE TABLE | 6 |
| servers | BASE TABLE | 0 |
| slave_master_info | BASE TABLE | 0 |
| slave_relay_log_info | BASE TABLE | 0 |
| slave_worker_info | BASE TABLE | 0 |
| slow_log | BASE TABLE | 2 |
| tables_priv | BASE TABLE | 2 |
| time_zone | BASE TABLE | 0 |
| time_zone_leap_second | BASE TABLE | 0 |
| time_zone_name | BASE TABLE | 0 |
| time_zone_transition | BASE TABLE | 0 |
| time_zone_transition_type | BASE TABLE | 0 |
| user | BASE TABLE | 4 |
+------------------------------------------------------+------------+------------+
37 rows in set (0.13 sec)
3. COLUMNS table
The COLUMNS table provides information about columns in tables.
List all columns in a specific table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
-> FROM information_schema.COLUMNS
-> WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'mysql';
+--------------------------+-----------+-------------+-----------------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+--------------------------+-----------+-------------+-----------------------+
| Host | char | NO | |
| User | char | NO | |
| Select_priv | enum | NO | N |
| Insert_priv | enum | NO | N |
| Update_priv | enum | NO | N |
| Delete_priv | enum | NO | N |
| Create_priv | enum | NO | N |
| Drop_priv | enum | NO | N |
| Reload_priv | enum | NO | N |
| Shutdown_priv | enum | NO | N |
| Process_priv | enum | NO | N |
| File_priv | enum | NO | N |
| Grant_priv | enum | NO | N |
| References_priv | enum | NO | N |
| Index_priv | enum | NO | N |
| Alter_priv | enum | NO | N |
| Show_db_priv | enum | NO | N |
| Super_priv | enum | NO | N |
| Create_tmp_table_priv | enum | NO | N |
| Lock_tables_priv | enum | NO | N |
| Execute_priv | enum | NO | N |
| Repl_slave_priv | enum | NO | N |
| Repl_client_priv | enum | NO | N |
| Create_view_priv | enum | NO | N |
| Show_view_priv | enum | NO | N |
| Create_routine_priv | enum | NO | N |
| Alter_routine_priv | enum | NO | N |
| Create_user_priv | enum | NO | N |
| Event_priv | enum | NO | N |
| Trigger_priv | enum | NO | N |
| Create_tablespace_priv | enum | NO | N |
| ssl_type | enum | NO | |
| ssl_cipher | blob | NO | NULL |
| x509_issuer | blob | NO | NULL |
| x509_subject | blob | NO | NULL |
| max_questions | int | NO | 0 |
| max_updates | int | NO | 0 |
| max_connections | int | NO | 0 |
| max_user_connections | int | NO | 0 |
| plugin | char | NO | caching_sha2_password |
| authentication_string | text | YES | NULL |
| password_expired | enum | NO | N |
| password_last_changed | timestamp | YES | NULL |
| password_lifetime | smallint | YES | NULL |
| account_locked | enum | NO | N |
| Create_role_priv | enum | NO | N |
| Drop_role_priv | enum | NO | N |
| Password_reuse_history | smallint | YES | NULL |
| Password_reuse_time | smallint | YES | NULL |
| Password_require_current | enum | YES | NULL |
| User_attributes | json | YES | NULL |
+--------------------------+-----------+-------------+-----------------------+
51 rows in set (0.00 sec)
Find columns of a specific data type in a database
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE DATA_TYPE = 'varchar' AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT TABLE_NAME, COLUMN_NAME
-> FROM information_schema.COLUMNS
-> WHERE DATA_TYPE = 'json' AND TABLE_SCHEMA = 'mysql';
+------------------------------------------------------+-----------------+
| TABLE_NAME | COLUMN_NAME |
+------------------------------------------------------+-----------------+
| replication_asynchronous_connection_failover_managed | Configuration |
| user | User_attributes |
+------------------------------------------------------+-----------------+
2 rows in set (0.04 sec)
4. STATISTICS table
The STATISTICS table provides information about table indexes.
List all indexes for a specific table
SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE
-> FROM information_schema.STATISTICS
-> WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'mysql';
+------------+-------------+--------------+------------+
| INDEX_NAME | COLUMN_NAME | SEQ_IN_INDEX | NON_UNIQUE |
+------------+-------------+--------------+------------+
| PRIMARY | Host | 1 | 0 |
| PRIMARY | User | 2 | 0 |
+------------+-------------+--------------+------------+
2 rows in set (0.01 sec)
Find all unique indexes in a database
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM information_schema.STATISTICS
WHERE NON_UNIQUE = 0 AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME
-> FROM information_schema.STATISTICS
-> WHERE NON_UNIQUE = 0 AND TABLE_SCHEMA = 'mysql';
+------------------------------------------------------+------------+--------------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME |
+------------------------------------------------------+------------+--------------------+
| innodb_table_stats | PRIMARY | database_name |
| innodb_table_stats | PRIMARY | table_name |
| innodb_index_stats | PRIMARY | database_name |
| innodb_index_stats | PRIMARY | table_name |
| innodb_index_stats | PRIMARY | index_name |
| innodb_index_stats | PRIMARY | stat_name |
| replication_group_configuration_version | PRIMARY | name |
| component | PRIMARY | component_id |
| columns_priv | PRIMARY | Host |
| columns_priv | PRIMARY | Db |
| columns_priv | PRIMARY | User |
| columns_priv | PRIMARY | Table_name |
| columns_priv | PRIMARY | Column_name |
| db | PRIMARY | Host |
| db | PRIMARY | Db |
| db | PRIMARY | User |
| default_roles | PRIMARY | HOST |
| default_roles | PRIMARY | USER |
| default_roles | PRIMARY | DEFAULT_ROLE_HOST |
| default_roles | PRIMARY | DEFAULT_ROLE_USER |
| engine_cost | PRIMARY | engine_name |
| engine_cost | PRIMARY | device_type |
| engine_cost | PRIMARY | cost_name |
| global_grants | PRIMARY | USER |
| global_grants | PRIMARY | HOST |
| global_grants | PRIMARY | PRIV |
| help_category | PRIMARY | help_category_id |
| help_category | name | name |
| help_keyword | PRIMARY | help_keyword_id |
| help_keyword | name | name |
| help_relation | PRIMARY | help_topic_id |
| help_relation | PRIMARY | help_keyword_id |
| help_topic | PRIMARY | help_topic_id |
| help_topic | name | name |
| plugin | PRIMARY | name |
| password_history | PRIMARY | Host |
| password_history | PRIMARY | User |
| password_history | PRIMARY | Password_timestamp |
| proxies_priv | PRIMARY | Host |
| proxies_priv | PRIMARY | User |
| proxies_priv | PRIMARY | Proxied_host |
| proxies_priv | PRIMARY | Proxied_user |
| role_edges | PRIMARY | FROM_HOST |
| role_edges | PRIMARY | FROM_USER |
| role_edges | PRIMARY | TO_HOST |
| role_edges | PRIMARY | TO_USER |
| servers | PRIMARY | Server_name |
| server_cost | PRIMARY | cost_name |
| replication_asynchronous_connection_failover | PRIMARY | Channel_name |
| replication_asynchronous_connection_failover | PRIMARY | Host |
| replication_asynchronous_connection_failover | PRIMARY | Port |
| replication_asynchronous_connection_failover | PRIMARY | Network_namespace |
| replication_asynchronous_connection_failover | PRIMARY | Managed_name |
| replication_asynchronous_connection_failover_managed | PRIMARY | Channel_name |
| replication_asynchronous_connection_failover_managed | PRIMARY | Managed_name |
| replication_group_member_actions | PRIMARY | name |
| replication_group_member_actions | PRIMARY | event |
| tables_priv | PRIMARY | Host |
| tables_priv | PRIMARY | Db |
| tables_priv | PRIMARY | User |
| tables_priv | PRIMARY | Table_name |
| time_zone_name | PRIMARY | Name |
| time_zone_leap_second | PRIMARY | Transition_time |
| time_zone_transition | PRIMARY | Time_zone_id |
| time_zone_transition | PRIMARY | Transition_time |
| time_zone_transition_type | PRIMARY | Time_zone_id |
| time_zone_transition_type | PRIMARY | Transition_type_id |
| func | PRIMARY | name |
| slave_master_info | PRIMARY | Channel_name |
| slave_worker_info | PRIMARY | Id |
| slave_worker_info | PRIMARY | Channel_name |
| slave_relay_log_info | PRIMARY | Channel_name |
| procs_priv | PRIMARY | Host |
| procs_priv | PRIMARY | Db |
| procs_priv | PRIMARY | User |
| procs_priv | PRIMARY | Routine_name |
| procs_priv | PRIMARY | Routine_type |
| user | PRIMARY | Host |
| user | PRIMARY | User |
| time_zone | PRIMARY | Time_zone_id |
| gtid_executed | PRIMARY | source_uuid |
| gtid_executed | PRIMARY | interval_start |
| gtid_executed | PRIMARY | gtid_tag |
+------------------------------------------------------+------------+--------------------+
83 rows in set (0.01 sec)
5. KEY_COLUMN_USAGE table
The KEY_COLUMN_USAGE table provides information about columns that are indexed.
List all columns involved in constraints for a specific table.
SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT CONSTRAINT_NAME, COLUMN_NAME
-> FROM information_schema.KEY_COLUMN_USAGE
-> WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'mysql';
+-----------------+-------------+
| CONSTRAINT_NAME | COLUMN_NAME |
+-----------------+-------------+
| PRIMARY | Host |
| PRIMARY | User |
+-----------------+-------------+
2 rows in set (0.01 sec)
Find primary key columns in a database
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = 'PRIMARY' AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT TABLE_NAME, COLUMN_NAME
-> FROM information_schema.KEY_COLUMN_USAGE
-> WHERE CONSTRAINT_NAME = 'PRIMARY' AND TABLE_SCHEMA = 'mysql';
+------------------------------------------------------+--------------------+
| TABLE_NAME | COLUMN_NAME |
+------------------------------------------------------+--------------------+
| columns_priv | Host |
| columns_priv | Db |
| columns_priv | User |
| columns_priv | Table_name |
| columns_priv | Column_name |
| component | component_id |
| db | Host |
| db | Db |
| db | User |
| default_roles | HOST |
| default_roles | USER |
| default_roles | DEFAULT_ROLE_HOST |
| default_roles | DEFAULT_ROLE_USER |
| engine_cost | engine_name |
| engine_cost | device_type |
| engine_cost | cost_name |
| func | name |
| global_grants | USER |
| global_grants | HOST |
| global_grants | PRIV |
| gtid_executed | source_uuid |
| gtid_executed | interval_start |
| gtid_executed | gtid_tag |
| help_category | help_category_id |
| help_keyword | help_keyword_id |
| help_relation | help_topic_id |
| help_relation | help_keyword_id |
| help_topic | help_topic_id |
| innodb_index_stats | database_name |
| innodb_index_stats | table_name |
| innodb_index_stats | index_name |
| innodb_index_stats | stat_name |
| innodb_table_stats | database_name |
| innodb_table_stats | table_name |
| password_history | Host |
| password_history | User |
| password_history | Password_timestamp |
| plugin | name |
| procs_priv | Host |
| procs_priv | Db |
| procs_priv | User |
| procs_priv | Routine_name |
| procs_priv | Routine_type |
| proxies_priv | Host |
| proxies_priv | User |
| proxies_priv | Proxied_host |
| proxies_priv | Proxied_user |
| replication_asynchronous_connection_failover | Channel_name |
| replication_asynchronous_connection_failover | Host |
| replication_asynchronous_connection_failover | Port |
| replication_asynchronous_connection_failover | Network_namespace |
| replication_asynchronous_connection_failover | Managed_name |
| replication_asynchronous_connection_failover_managed | Channel_name |
| replication_asynchronous_connection_failover_managed | Managed_name |
| replication_group_configuration_version | name |
| replication_group_member_actions | name |
| replication_group_member_actions | event |
| role_edges | FROM_HOST |
| role_edges | FROM_USER |
| role_edges | TO_HOST |
| role_edges | TO_USER |
| server_cost | cost_name |
| servers | Server_name |
| slave_master_info | Channel_name |
| slave_relay_log_info | Channel_name |
| slave_worker_info | Id |
| slave_worker_info | Channel_name |
| tables_priv | Host |
| tables_priv | Db |
| tables_priv | User |
| tables_priv | Table_name |
| time_zone | Time_zone_id |
| time_zone_leap_second | Transition_time |
| time_zone_name | Name |
| time_zone_transition | Time_zone_id |
| time_zone_transition | Transition_time |
| time_zone_transition_type | Time_zone_id |
| time_zone_transition_type | Transition_type_id |
| user | Host |
| user | User |
+------------------------------------------------------+--------------------+
80 rows in set (0.02 sec)
6. TABLE_CONSTRAINTS table
The TABLE_CONSTRAINTS table provides information about table constraints.
List all constraints for a specific table
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';
mysql> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
-> FROM information_schema.TABLE_CONSTRAINTS
-> WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'mysql';
+-----------------+-----------------+
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-----------------+-----------------+
| PRIMARY | PRIMARY KEY |
+-----------------+-----------------+
1 row in set (0.00 sec)
List all the constraints
SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS;mysql> SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS;
+------------------------------------------------------+-----------------+
| TABLE_NAME | CONSTRAINT_NAME |
+------------------------------------------------------+-----------------+
| columns_priv | PRIMARY |
| component | PRIMARY |
| db | PRIMARY |
| default_roles | PRIMARY |
| engine_cost | PRIMARY |
| func | PRIMARY |
| global_grants | PRIMARY |
| gtid_executed | PRIMARY |
| help_category | name |
| help_category | PRIMARY |
| help_keyword | name |
| help_keyword | PRIMARY |
| help_relation | PRIMARY |
| help_topic | name |
| help_topic | PRIMARY |
| innodb_index_stats | PRIMARY |
| innodb_table_stats | PRIMARY |
| password_history | PRIMARY |
| plugin | PRIMARY |
| procs_priv | PRIMARY |
| proxies_priv | PRIMARY |
| replication_asynchronous_connection_failover | PRIMARY |
| replication_asynchronous_connection_failover_managed | PRIMARY |
| replication_group_configuration_version | PRIMARY |
| replication_group_member_actions | PRIMARY |
| role_edges | PRIMARY |
| server_cost | PRIMARY |
| servers | PRIMARY |
| slave_master_info | PRIMARY |
| slave_relay_log_info | PRIMARY |
| slave_worker_info | PRIMARY |
| tables_priv | PRIMARY |
| time_zone | PRIMARY |
| time_zone_leap_second | PRIMARY |
| time_zone_name | PRIMARY |
| time_zone_transition | PRIMARY |
| time_zone_transition_type | PRIMARY |
| user | PRIMARY |
| accounts | ACCOUNT |
| cond_instances | PRIMARY |
| data_locks | PRIMARY |
| error_log | PRIMARY |
| events_errors_summary_by_account_by_error | ACCOUNT |
| events_errors_summary_by_host_by_error | HOST |
| events_errors_summary_by_thread_by_error | THREAD_ID |
| events_errors_summary_by_user_by_error | USER |
| events_errors_summary_global_by_error | ERROR_NUMBER |
| events_stages_current | PRIMARY |
| events_stages_history | PRIMARY |
| events_stages_summary_by_account_by_event_name | ACCOUNT |
| events_stages_summary_by_host_by_event_name | HOST |
| events_stages_summary_by_thread_by_event_name | PRIMARY |
| events_stages_summary_by_user_by_event_name | USER |
| events_stages_summary_global_by_event_name | PRIMARY |
| events_statements_current | PRIMARY |
| events_statements_histogram_by_digest | SCHEMA_NAME |
| events_statements_histogram_global | PRIMARY |
| events_statements_history | PRIMARY |
| events_statements_summary_by_account_by_event_name | ACCOUNT |
| events_statements_summary_by_digest | SCHEMA_NAME |
| events_statements_summary_by_host_by_event_name | HOST |
| events_statements_summary_by_program | PRIMARY |
| events_statements_summary_by_thread_by_event_name | PRIMARY |
| events_statements_summary_by_user_by_event_name | USER |
| events_statements_summary_global_by_event_name | PRIMARY |
| events_transactions_current | PRIMARY |
| events_transactions_history | PRIMARY |
| events_transactions_summary_by_account_by_event_name | ACCOUNT |
| events_transactions_summary_by_host_by_event_name | HOST |
| events_transactions_summary_by_thread_by_event_name | PRIMARY |
| events_transactions_summary_by_user_by_event_name | USER |
| events_transactions_summary_global_by_event_name | PRIMARY |
| events_waits_current | PRIMARY |
| events_waits_history | PRIMARY |
| events_waits_summary_by_account_by_event_name | ACCOUNT |
| events_waits_summary_by_host_by_event_name | HOST |
| events_waits_summary_by_instance | PRIMARY |
| events_waits_summary_by_thread_by_event_name | PRIMARY |
| events_waits_summary_by_user_by_event_name | USER |
| events_waits_summary_global_by_event_name | PRIMARY |
| file_instances | PRIMARY |
| file_summary_by_event_name | PRIMARY |
| file_summary_by_instance | PRIMARY |
| global_status | PRIMARY |
| global_variables | PRIMARY |
| host_cache | PRIMARY |
| hosts | HOST |
| memory_summary_by_account_by_event_name | ACCOUNT |
| memory_summary_by_host_by_event_name | HOST |
| memory_summary_by_thread_by_event_name | PRIMARY |
| memory_summary_by_user_by_event_name | USER |
| memory_summary_global_by_event_name | PRIMARY |
| metadata_locks | PRIMARY |
| mutex_instances | PRIMARY |
| objects_summary_global_by_type | OBJECT |
| persisted_variables | PRIMARY |
| prepared_statements_instances | OWNER_THREAD_ID |
| prepared_statements_instances | PRIMARY |
| processlist | PRIMARY |
| replication_applier_configuration | PRIMARY |
| replication_applier_status | PRIMARY |
| replication_applier_status_by_coordinator | PRIMARY |
| replication_applier_status_by_worker | PRIMARY |
| replication_connection_configuration | PRIMARY |
| replication_connection_status | PRIMARY |
| rwlock_instances | PRIMARY |
| session_account_connect_attrs | PRIMARY |
| session_connect_attrs | PRIMARY |
| session_status | PRIMARY |
| session_variables | PRIMARY |
| setup_actors | PRIMARY |
| setup_consumers | PRIMARY |
| setup_instruments | PRIMARY |
| setup_meters | PRIMARY |
| setup_metrics | PRIMARY |
| setup_objects | OBJECT |
| setup_threads | PRIMARY |
| socket_instances | PRIMARY |
| socket_summary_by_event_name | PRIMARY |
| socket_summary_by_instance | PRIMARY |
| status_by_account | ACCOUNT |
| status_by_host | HOST |
| status_by_thread | PRIMARY |
| status_by_user | USER |
| table_handles | PRIMARY |
| table_io_waits_summary_by_index_usage | OBJECT |
| table_io_waits_summary_by_table | OBJECT |
| table_lock_waits_summary_by_table | OBJECT |
| threads | PRIMARY |
| user_defined_functions | PRIMARY |
| user_variables_by_thread | PRIMARY |
| users | USER |
| variables_by_thread | PRIMARY |
| sys_config | PRIMARY |
+------------------------------------------------------+-----------------+
134 rows in set (0.02 sec)
7. USER_PRIVILEGES table
The USER_PRIVILEGES table provides information about user privileges.
1. List all user privileges
SELECT * FROM information_schema.USER_PRIVILEGES;mysql> SELECT * FROM information_schema.USER_PRIVILEGES;
+--------------------------------+---------------+------------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------------------+---------------+------------------------------+--------------+
| 'mysql.infoschema'@'localhost' | def | SELECT | NO |
| 'mysql.infoschema'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO |
| 'mysql.infoschema'@'localhost' | def | FIREWALL_EXEMPT | NO |
| 'mysql.infoschema'@'localhost' | def | SYSTEM_USER | NO |
| 'mysql.session'@'localhost' | def | SHUTDOWN | NO |
| 'mysql.session'@'localhost' | def | SUPER | NO |
| 'mysql.session'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO |
| 'mysql.session'@'localhost' | def | AUTHENTICATION_POLICY_ADMIN | NO |
| 'mysql.session'@'localhost' | def | BACKUP_ADMIN | NO |
| 'mysql.session'@'localhost' | def | CLONE_ADMIN | NO |
| 'mysql.session'@'localhost' | def | CONNECTION_ADMIN | NO |
| 'mysql.session'@'localhost' | def | FIREWALL_EXEMPT | NO |
| 'mysql.session'@'localhost' | def | PERSIST_RO_VARIABLES_ADMIN | NO |
| 'mysql.session'@'localhost' | def | SESSION_VARIABLES_ADMIN | NO |
| 'mysql.session'@'localhost' | def | SYSTEM_USER | NO |
| 'mysql.session'@'localhost' | def | SYSTEM_VARIABLES_ADMIN | NO |
| 'mysql.sys'@'localhost' | def | USAGE | NO |
| 'mysql.sys'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO |
| 'mysql.sys'@'localhost' | def | FIREWALL_EXEMPT | NO |
| 'mysql.sys'@'localhost' | def | SYSTEM_USER | NO |
| 'root'@'localhost' | def | SELECT | YES |
| 'root'@'localhost' | def | INSERT | YES |
| 'root'@'localhost' | def | UPDATE | YES |
| 'root'@'localhost' | def | DELETE | YES |
| 'root'@'localhost' | def | CREATE | YES |
| 'root'@'localhost' | def | DROP | YES |
| 'root'@'localhost' | def | RELOAD | YES |
| 'root'@'localhost' | def | SHUTDOWN | YES |
| 'root'@'localhost' | def | PROCESS | YES |
| 'root'@'localhost' | def | FILE | YES |
| 'root'@'localhost' | def | REFERENCES | YES |
| 'root'@'localhost' | def | INDEX | YES |
| 'root'@'localhost' | def | ALTER | YES |
| 'root'@'localhost' | def | SHOW DATABASES | YES |
| 'root'@'localhost' | def | SUPER | YES |
| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'localhost' | def | LOCK TABLES | YES |
| 'root'@'localhost' | def | EXECUTE | YES |
| 'root'@'localhost' | def | REPLICATION SLAVE | YES |
| 'root'@'localhost' | def | REPLICATION CLIENT | YES |
| 'root'@'localhost' | def | CREATE VIEW | YES |
| 'root'@'localhost' | def | SHOW VIEW | YES |
| 'root'@'localhost' | def | CREATE ROUTINE | YES |
| 'root'@'localhost' | def | ALTER ROUTINE | YES |
| 'root'@'localhost' | def | CREATE USER | YES |
| 'root'@'localhost' | def | EVENT | YES |
| 'root'@'localhost' | def | TRIGGER | YES |
| 'root'@'localhost' | def | CREATE TABLESPACE | YES |
| 'root'@'localhost' | def | CREATE ROLE | YES |
| 'root'@'localhost' | def | DROP ROLE | YES |
| 'root'@'localhost' | def | ALLOW_NONEXISTENT_DEFINER | YES |
| 'root'@'localhost' | def | APPLICATION_PASSWORD_ADMIN | YES |
| 'root'@'localhost' | def | AUDIT_ABORT_EXEMPT | YES |
| 'root'@'localhost' | def | AUDIT_ADMIN | YES |
| 'root'@'localhost' | def | AUTHENTICATION_POLICY_ADMIN | YES |
| 'root'@'localhost' | def | BACKUP_ADMIN | YES |
| 'root'@'localhost' | def | BINLOG_ADMIN | YES |
| 'root'@'localhost' | def | BINLOG_ENCRYPTION_ADMIN | YES |
| 'root'@'localhost' | def | CLONE_ADMIN | YES |
| 'root'@'localhost' | def | CONNECTION_ADMIN | YES |
| 'root'@'localhost' | def | ENCRYPTION_KEY_ADMIN | YES |
| 'root'@'localhost' | def | FIREWALL_EXEMPT | YES |
| 'root'@'localhost' | def | FLUSH_OPTIMIZER_COSTS | YES |
| 'root'@'localhost' | def | FLUSH_STATUS | YES |
| 'root'@'localhost' | def | FLUSH_TABLES | YES |
| 'root'@'localhost' | def | FLUSH_USER_RESOURCES | YES |
| 'root'@'localhost' | def | GROUP_REPLICATION_ADMIN | YES |
| 'root'@'localhost' | def | GROUP_REPLICATION_STREAM | YES |
| 'root'@'localhost' | def | INNODB_REDO_LOG_ARCHIVE | YES |
| 'root'@'localhost' | def | INNODB_REDO_LOG_ENABLE | YES |
| 'root'@'localhost' | def | PASSWORDLESS_USER_ADMIN | YES |
| 'root'@'localhost' | def | PERSIST_RO_VARIABLES_ADMIN | YES |
| 'root'@'localhost' | def | REPLICATION_APPLIER | YES |
| 'root'@'localhost' | def | REPLICATION_SLAVE_ADMIN | YES |
| 'root'@'localhost' | def | RESOURCE_GROUP_ADMIN | YES |
| 'root'@'localhost' | def | RESOURCE_GROUP_USER | YES |
| 'root'@'localhost' | def | ROLE_ADMIN | YES |
| 'root'@'localhost' | def | SENSITIVE_VARIABLES_OBSERVER | YES |
| 'root'@'localhost' | def | SERVICE_CONNECTION_ADMIN | YES |
| 'root'@'localhost' | def | SESSION_VARIABLES_ADMIN | YES |
| 'root'@'localhost' | def | SET_ANY_DEFINER | YES |
| 'root'@'localhost' | def | SET_USER_ID | YES |
| 'root'@'localhost' | def | SHOW_ROUTINE | YES |
| 'root'@'localhost' | def | SYSTEM_USER | YES |
| 'root'@'localhost' | def | SYSTEM_VARIABLES_ADMIN | YES |
| 'root'@'localhost' | def | TABLE_ENCRYPTION_ADMIN | YES |
| 'root'@'localhost' | def | TELEMETRY_LOG_ADMIN | YES |
| 'root'@'localhost' | def | TRANSACTION_GTID_TAG | YES |
| 'root'@'localhost' | def | XA_RECOVER_ADMIN | YES |
+--------------------------------+---------------+------------------------------+--------------+
89 rows in set (0.00 sec)
Find privileges for a specific user.
SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE LIKE "'username'%";mysql> SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE LIKE "'root'%";
+--------------------+---------------+------------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+------------------------------+--------------+
| 'root'@'localhost' | def | SELECT | YES |
| 'root'@'localhost' | def | INSERT | YES |
| 'root'@'localhost' | def | UPDATE | YES |
| 'root'@'localhost' | def | DELETE | YES |
| 'root'@'localhost' | def | CREATE | YES |
| 'root'@'localhost' | def | DROP | YES |
| 'root'@'localhost' | def | RELOAD | YES |
| 'root'@'localhost' | def | SHUTDOWN | YES |
| 'root'@'localhost' | def | PROCESS | YES |
| 'root'@'localhost' | def | FILE | YES |
| 'root'@'localhost' | def | REFERENCES | YES |
| 'root'@'localhost' | def | INDEX | YES |
| 'root'@'localhost' | def | ALTER | YES |
| 'root'@'localhost' | def | SHOW DATABASES | YES |
| 'root'@'localhost' | def | SUPER | YES |
| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'localhost' | def | LOCK TABLES | YES |
| 'root'@'localhost' | def | EXECUTE | YES |
| 'root'@'localhost' | def | REPLICATION SLAVE | YES |
| 'root'@'localhost' | def | REPLICATION CLIENT | YES |
| 'root'@'localhost' | def | CREATE VIEW | YES |
| 'root'@'localhost' | def | SHOW VIEW | YES |
| 'root'@'localhost' | def | CREATE ROUTINE | YES |
| 'root'@'localhost' | def | ALTER ROUTINE | YES |
| 'root'@'localhost' | def | CREATE USER | YES |
| 'root'@'localhost' | def | EVENT | YES |
| 'root'@'localhost' | def | TRIGGER | YES |
| 'root'@'localhost' | def | CREATE TABLESPACE | YES |
| 'root'@'localhost' | def | CREATE ROLE | YES |
| 'root'@'localhost' | def | DROP ROLE | YES |
| 'root'@'localhost' | def | ALLOW_NONEXISTENT_DEFINER | YES |
| 'root'@'localhost' | def | APPLICATION_PASSWORD_ADMIN | YES |
| 'root'@'localhost' | def | AUDIT_ABORT_EXEMPT | YES |
| 'root'@'localhost' | def | AUDIT_ADMIN | YES |
| 'root'@'localhost' | def | AUTHENTICATION_POLICY_ADMIN | YES |
| 'root'@'localhost' | def | BACKUP_ADMIN | YES |
| 'root'@'localhost' | def | BINLOG_ADMIN | YES |
| 'root'@'localhost' | def | BINLOG_ENCRYPTION_ADMIN | YES |
| 'root'@'localhost' | def | CLONE_ADMIN | YES |
| 'root'@'localhost' | def | CONNECTION_ADMIN | YES |
| 'root'@'localhost' | def | ENCRYPTION_KEY_ADMIN | YES |
| 'root'@'localhost' | def | FIREWALL_EXEMPT | YES |
| 'root'@'localhost' | def | FLUSH_OPTIMIZER_COSTS | YES |
| 'root'@'localhost' | def | FLUSH_STATUS | YES |
| 'root'@'localhost' | def | FLUSH_TABLES | YES |
| 'root'@'localhost' | def | FLUSH_USER_RESOURCES | YES |
| 'root'@'localhost' | def | GROUP_REPLICATION_ADMIN | YES |
| 'root'@'localhost' | def | GROUP_REPLICATION_STREAM | YES |
| 'root'@'localhost' | def | INNODB_REDO_LOG_ARCHIVE | YES |
| 'root'@'localhost' | def | INNODB_REDO_LOG_ENABLE | YES |
| 'root'@'localhost' | def | PASSWORDLESS_USER_ADMIN | YES |
| 'root'@'localhost' | def | PERSIST_RO_VARIABLES_ADMIN | YES |
| 'root'@'localhost' | def | REPLICATION_APPLIER | YES |
| 'root'@'localhost' | def | REPLICATION_SLAVE_ADMIN | YES |
| 'root'@'localhost' | def | RESOURCE_GROUP_ADMIN | YES |
| 'root'@'localhost' | def | RESOURCE_GROUP_USER | YES |
| 'root'@'localhost' | def | ROLE_ADMIN | YES |
| 'root'@'localhost' | def | SENSITIVE_VARIABLES_OBSERVER | YES |
| 'root'@'localhost' | def | SERVICE_CONNECTION_ADMIN | YES |
| 'root'@'localhost' | def | SESSION_VARIABLES_ADMIN | YES |
| 'root'@'localhost' | def | SET_ANY_DEFINER | YES |
| 'root'@'localhost' | def | SET_USER_ID | YES |
| 'root'@'localhost' | def | SHOW_ROUTINE | YES |
| 'root'@'localhost' | def | SYSTEM_USER | YES |
| 'root'@'localhost' | def | SYSTEM_VARIABLES_ADMIN | YES |
| 'root'@'localhost' | def | TABLE_ENCRYPTION_ADMIN | YES |
| 'root'@'localhost' | def | TELEMETRY_LOG_ADMIN | YES |
| 'root'@'localhost' | def | TRANSACTION_GTID_TAG | YES |
| 'root'@'localhost' | def | XA_RECOVER_ADMIN | YES |
+--------------------+---------------+------------------------------+--------------+
69 rows in set (0.02 sec)
8. VIEWS table
The VIEWS table contains information about views in the database.
List all views in a specific database
SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'your_database_name';
9. ROUTINES table
The ROUTINES table provides information about stored procedures and functions.
List all stored procedures and functions in a specific database
SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database_name';
10. TRIGGERS table
The TRIGGERS table contains information about triggers.
List all triggers in a specific database
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name';
11. PARTITIONS Table
The PARTITIONS table provides information about table partitions.
List all partitions of a specific table.
SELECT PARTITION_NAME, TABLE_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
mysql> SELECT PARTITION_NAME, TABLE_NAME, TABLE_ROWS
-> FROM information_schema.PARTITIONS
-> WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'user';
+----------------+------------+------------+
| PARTITION_NAME | TABLE_NAME | TABLE_ROWS |
+----------------+------------+------------+
| NULL | user | 4 |
+----------------+------------+------------+
1 row in set (0.02 sec)
Adhoc Queries
1. Get detailed information about the size of each table in a specific database.
SELECT
TABLE_NAME,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS table_size_mb,
TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY table_size_mb DESC;mysql> SELECT
-> TABLE_NAME,
-> ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS table_size_mb,
-> TABLE_ROWS
-> FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA = 'mysql'
-> ORDER BY table_size_mb DESC;
+------------------------------------------------------+---------------+------------+
| TABLE_NAME | table_size_mb | TABLE_ROWS |
+------------------------------------------------------+---------------+------------+
| help_topic | 1.61 | 535 |
| help_keyword | 0.27 | 954 |
| help_relation | 0.11 | 2059 |
| global_grants | 0.05 | 21 |
| proxies_priv | 0.03 | 1 |
| tables_priv | 0.03 | 2 |
| db | 0.03 | 2 |
| replication_group_member_actions | 0.03 | 2 |
| replication_asynchronous_connection_failover | 0.03 | 0 |
| procs_priv | 0.03 | 0 |
| help_category | 0.03 | 53 |
| gtid_executed | 0.02 | 0 |
| innodb_index_stats | 0.02 | 6 |
| innodb_table_stats | 0.02 | 2 |
| password_history | 0.02 | 0 |
| plugin | 0.02 | 0 |
| func | 0.02 | 0 |
| columns_priv | 0.02 | 0 |
| engine_cost | 0.02 | 2 |
| replication_asynchronous_connection_failover_managed | 0.02 | 0 |
| replication_group_configuration_version | 0.02 | 1 |
| default_roles | 0.02 | 0 |
| role_edges | 0.02 | 0 |
| server_cost | 0.02 | 6 |
| servers | 0.02 | 0 |
| slave_master_info | 0.02 | 0 |
| slave_relay_log_info | 0.02 | 0 |
| slave_worker_info | 0.02 | 0 |
| component | 0.02 | 0 |
| time_zone | 0.02 | 0 |
| time_zone_leap_second | 0.02 | 0 |
| time_zone_name | 0.02 | 0 |
| time_zone_transition | 0.02 | 0 |
| time_zone_transition_type | 0.02 | 0 |
| user | 0.02 | 4 |
| general_log | 0.00 | 2 |
| slow_log | 0.00 | 2 |
+------------------------------------------------------+---------------+------------+
37 rows in set (0.01 sec)
2. Dynamic query to generate DROP TABLE statements for all tables in a specific database.
SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';') AS drop_statement
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';
>br />mysql> SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';') AS drop_statement
-> FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA = 'mysql';
+------------------------------------------------------------------+
| drop_statement |
+------------------------------------------------------------------+
| DROP TABLE columns_priv; |
| DROP TABLE component; |
| DROP TABLE db; |
| DROP TABLE default_roles; |
| DROP TABLE engine_cost; |
| DROP TABLE func; |
| DROP TABLE general_log; |
| DROP TABLE global_grants; |
| DROP TABLE gtid_executed; |
| DROP TABLE help_category; |
| DROP TABLE help_keyword; |
| DROP TABLE help_relation; |
| DROP TABLE help_topic; |
| DROP TABLE innodb_index_stats; |
| DROP TABLE innodb_table_stats; |
| DROP TABLE password_history; |
| DROP TABLE plugin; |
| DROP TABLE procs_priv; |
| DROP TABLE proxies_priv; |
| DROP TABLE replication_asynchronous_connection_failover; |
| DROP TABLE replication_asynchronous_connection_failover_managed; |
| DROP TABLE replication_group_configuration_version; |
| DROP TABLE replication_group_member_actions; |
| DROP TABLE role_edges; |
| DROP TABLE server_cost; |
| DROP TABLE servers; |
| DROP TABLE slave_master_info; |
| DROP TABLE slave_relay_log_info; |
| DROP TABLE slave_worker_info; |
| DROP TABLE slow_log; |
| DROP TABLE tables_priv; |
| DROP TABLE time_zone; |
| DROP TABLE time_zone_leap_second; |
| DROP TABLE time_zone_name; |
| DROP TABLE time_zone_transition; |
| DROP TABLE time_zone_transition_type; |
| DROP TABLE user; |
+------------------------------------------------------------------+
37 rows in set (0.02 sec)
Note
1. Running complex queries on the information_schema database can strain system resources, especially for larger databases. This can lead to slower performance for everyone.
2. Information_schema tables store valuable database details, but unlike user tables with optimized lookups, they aren't indexed in the same way. This means retrieving information from information_schema might take longer.
3. Since information_schema contains sensitive details about the database structure and potentially user information, it's crucial to control access using MySQL privileges. Only authorized users with legitimate needs should be able to access this data.
4. Be careful about who sees information retrieved from information_schema. Revealing these details, like table structures and data types, could be used to exploit vulnerabilities or craft malicious attacks. Limit access to authorized users and only expose what's necessary.
No comments:
Post a Comment