sql-31 Mysql5.7系统表

MySQL 5.7 系统数据库与数据表详解

MySQL 5.7 包含多个系统数据库，它们存储了MySQL服务器的元数据、权限信息、性能指标等关键数据。以下是详细的解析：

一、系统数据库概览

MySQL 5.7 默认包含以下系统数据库：

-- 查看所有数据库
SHOW DATABASES;

-- 输出示例：
+--------------------+
| Database           |
+--------------------+
| information_schema |  -- 信息模式（虚拟数据库）
| mysql              |  -- 核心系统数据库
| performance_schema |  -- 性能监控数据库
| sys                |  -- 系统数据库（5.7.7+）
| test               |  -- 测试数据库（可删除）
+--------------------+

二、information_schema 详解

1. 概述

  • 虚拟数据库，不占用物理存储空间
  • 提供元数据访问，符合SQL标准
  • 所有用户都可访问（只读）

2. 核心表结构

数据库和表信息

-- 查看所有表
USE information_schema;
SHOW TABLES LIKE '%TABLES%';

-- 重要表：
-- TABLES：所有表的信息
SELECT 
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME AS '表名',
    TABLE_TYPE AS '类型',
    ENGINE AS '存储引擎',
    TABLE_ROWS AS '行数',
    DATA_LENGTH AS '数据大小(B)',
    INDEX_LENGTH AS '索引大小(B)',
    CREATE_TIME AS '创建时间'
FROM TABLES 
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY DATA_LENGTH DESC;

-- COLUMNS：所有列的信息
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- STATISTICS：索引统计信息
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    INDEX_TYPE,
    CARDINALITY
FROM STATISTICS
WHERE TABLE_SCHEMA = 'your_database';

权限和进程信息

-- 查看用户权限
SELECT 
    GRANTEE,
    PRIVILEGE_TYPE,
    IS_GRANTABLE
FROM USER_PRIVILEGES;

-- 查看进程列表（类似SHOW PROCESSLIST）
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- 查看字符集和校对规则
SELECT 
    CHARACTER_SET_NAME,
    DEFAULT_COLLATE_NAME,
    DESCRIPTION,
    MAXLEN
FROM CHARACTER_SETS;

-- 查看存储引擎信息
SELECT 
    ENGINE,
    SUPPORT,
    COMMENT,
    TRANSACTIONS,
    XA,
    SAVEPOINTS
FROM ENGINES;

三、mysql 数据库详解

1. 概述

  • 核心系统数据库，存储权限、用户、插件等信息
  • 使用MyISAM和InnoDB混合存储
  • 需要特殊权限才能修改

2. 用户和权限表

user表（核心权限表）

-- 查看表结构
DESC mysql.user;

-- 重要字段：
-- Host, User: 用户标识（主机+用户名）
-- authentication_string: 密码哈希（5.7使用caching_sha2_password或mysql_native_password）
-- Select_priv, Insert_priv等: 全局权限
-- max_questions, max_updates: 资源限制
-- plugin: 认证插件

-- 查看用户权限
SELECT 
    Host,
    User,
    authentication_string,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Create_priv,
    Drop_priv
FROM mysql.user
ORDER BY User, Host;

-- 创建用户（示例）
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydb.* TO 'testuser'@'localhost';
FLUSH PRIVILEGES;

其他权限相关表

-- db表：数据库级权限
SELECT * FROM mysql.db WHERE User = 'testuser';

-- tables_priv表：表级权限
SELECT * FROM mysql.tables_priv;

-- columns_priv表：列级权限
SELECT * FROM mysql.columns_priv;

-- procs_priv表：存储过程权限
SELECT * FROM mysql.procs_priv;

3. 时区和字符集表

-- 时区信息
SHOW TABLES LIKE '%time%';
-- time_zone, time_zone_leap_second, time_zone_name, time_zone_transition

-- 查看时区设置
SELECT * FROM mysql.time_zone;
SELECT * FROM mysql.time_zone_name;

-- 加载时区数据（需要导入）
-- mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

-- 字符集相关
SELECT * FROM mysql.columns_priv;

4. 其他重要表

-- 事件调度器
SELECT * FROM mysql.event;

-- 存储过程和函数
SELECT * FROM mysql.proc;

-- 插件信息
SELECT * FROM mysql.plugin;

-- 服务器端帮助系统
SELECT * FROM mysql.help_topic;
SELECT * FROM mysql.help_category;
SELECT * FROM mysql.help_relation;

四、performance_schema 详解

1. 概述

  • 性能监控数据库，提供服务器运行时性能数据
  • 默认启用，但某些功能可能需要配置
  • 使用内存表，重启后数据丢失

2. 配置和使用

-- 查看是否启用
SHOW VARIABLES LIKE 'performance_schema';
-- 或
USE performance_schema;
SHOW TABLES;

-- 启用/禁用特定监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements_%';

3. 关键表分类

配置表

-- 监控配置
SELECT * FROM performance_schema.setup_actors;
SELECT * FROM performance_schema.setup_consumers;
SELECT * FROM performance_schema.setup_instruments;
SELECT * FROM performance_schema.setup_objects;
SELECT * FROM performance_schema.setup_timers;

语句和事件监控

-- 最近执行的SQL语句
SELECT 
    EVENT_ID,
    EVENT_NAME,
    TIMER_WAIT/1000000000 AS '耗时(秒)',
    LOCK_TIME/1000000000 AS '锁时间(秒)',
    SQL_TEXT,
    DIGEST,
    DIGEST_TEXT,
    ROWS_AFFECTED,
    ROWS_SENT,
    ROWS_EXAMINED,
    CREATED_TMP_TABLES,
    CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT IS NOT NULL
ORDER BY TIMER_START DESC
LIMIT 10;

-- 按SQL模式聚合统计
SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS '执行次数',
    SUM_TIMER_WAIT/1000000000 AS '总耗时(秒)',
    AVG_TIMER_WAIT/1000000000 AS '平均耗时(秒)',
    SUM_ROWS_AFFECTED AS '影响行数',
    SUM_ROWS_SENT AS '返回行数',
    SUM_ROWS_EXAMINED AS '扫描行数',
    FIRST_SEEN AS '首次出现',
    LAST_SEEN AS '最后出现'
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

连接和线程信息

-- 查看当前连接线程
SELECT 
    THREAD_ID,
    PROCESSLIST_ID AS '连接ID',
    PROCESSLIST_USER AS '用户',
    PROCESSLIST_HOST AS '主机',
    PROCESSLIST_DB AS '数据库',
    PROCESSLIST_COMMAND AS '命令',
    PROCESSLIST_TIME AS '时间(秒)',
    PROCESSLIST_STATE AS '状态',
    PROCESSLIST_INFO AS '当前SQL'
FROM performance_schema.threads
WHERE PROCESSLIST_ID IS NOT NULL
ORDER BY PROCESSLIST_TIME DESC;

-- 线程历史
SELECT * FROM performance_schema.events_waits_history;
SELECT * FROM performance_schema.events_waits_history_long;

锁和事务监控

-- 当前锁信息
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    THREAD_ID,
    PROCESSLIST_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA IS NOT NULL;

-- 行锁等待
SELECT 
    WAITING_THREAD_ID,
    WAITING_PROCESSLIST_ID,
    WAITING_QUERY,
    BLOCKING_THREAD_ID,
    BLOCKING_PROCESSLIST_ID,
    BLOCKING_QUERY
FROM performance_schema.threads 
JOIN performance_schema.data_lock_waits 
    ON THREAD_ID = WAITING_THREAD_ID;

内存使用监控

-- 内存使用汇总
SELECT 
    EVENT_NAME,
    COUNT_ALLOC,
    COUNT_FREE,
    SUM_NUMBER_OF_BYTES_ALLOC/1024/1024 AS '分配总量(MB)',
    SUM_NUMBER_OF_BYTES_FREE/1024/1024 AS '释放总量(MB)',
    CURRENT_COUNT_USED,
    CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS '当前使用(MB)'
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 15;

文件I/O监控

-- 文件I/O统计
SELECT 
    FILE_NAME,
    COUNT_READ,
    COUNT_WRITE,
    SUM_NUMBER_OF_BYTES_READ/1024/1024 AS '读取总量(MB)',
    SUM_NUMBER_OF_BYTES_WRITE/1024/1024 AS '写入总量(MB)'
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;

五、sys 数据库详解

1. 概述

  • MySQL 5.7.7+ 引入，基于performance_schema的视图
  • 提供更友好的性能诊断接口
  • 所有数据来自performance_schema和information_schema

2. 常用视图

性能诊断视图

-- 查看最耗资源的SQL
SELECT * FROM sys.statement_analysis LIMIT 10;

-- 查看全表扫描的语句
SELECT * FROM sys.statements_with_full_table_scans;

-- 查看使用临时表的语句
SELECT * FROM sys.statements_with_temp_tables;

-- 查看排序相关的语句
SELECT * FROM sys.statements_with_sorting;

-- 查看错误和警告
SELECT * FROM sys.statements_with_errors_or_warnings;

模式相关视图

-- 按大小排序的表
SELECT 
    table_schema AS '数据库',
    table_name AS '表名',
    engine AS '存储引擎',
    table_rows AS '行数',
    data_length/1024/1024 AS '数据大小(MB)',
    index_length/1024/1024 AS '索引大小(MB)',
    (data_length+index_length)/1024/1024 AS '总大小(MB)'
FROM sys.schema_table_statistics
ORDER BY (data_length+index_length) DESC;

-- 未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 冗余索引
SELECT * FROM sys.schema_redundant_indexes;

用户和连接视图

-- 用户连接统计
SELECT 
    user AS '用户',
    current_connections AS '当前连接',
    total_connections AS '总连接'
FROM sys.user_summary;

-- 查看当前会话
SELECT * FROM sys.session 
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 查看当前进程
SELECT * FROM sys.processlist;

InnoDB状态视图

-- InnoDB缓冲池统计
SELECT 
    page_type,
    count/1024 AS '页数(K)',
    data_size/1024/1024 AS '数据大小(MB)'
FROM sys.innodb_buffer_page
GROUP BY page_type
ORDER BY data_size DESC;

-- 缓冲池使用情况
SELECT * FROM sys.innodb_buffer_stats_by_schema;
SELECT * FROM sys.innodb_buffer_stats_by_table;

六、系统数据库维护

1. 备份系统数据库

# 备份mysql数据库（包含用户权限）
mysqldump -u root -p --databases mysql > mysql_backup.sql

# 备份所有系统数据库（除了information_schema）
mysqldump -u root -p --databases mysql performance_schema sys > system_dbs_backup.sql

# 备份用户权限（仅权限，无数据）
mysql -u root -p --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u root -p --skip-column-names -A | sed 's/$/;/g' > all_grants.sql

2. 恢复系统数据库

-- 恢复mysql数据库
mysql -u root -p mysql < mysql_backup.sql
FLUSH PRIVILEGES;  -- 必须执行！

-- 从权限文件恢复
mysql -u root -p < all_grants.sql

3. 常见维护操作

-- 清理performance_schema历史数据（重启后自动清理）
-- 可调整表大小限制
SET GLOBAL performance_schema_events_statements_history_size = 10000;
SET GLOBAL performance_schema_events_waits_history_size = 10000;

-- 修复mysql系统表
mysql_upgrade -u root -p

-- 检查表完整性
CHECK TABLE mysql.user;
REPAIR TABLE mysql.user;

-- 优化表
OPTIMIZE TABLE mysql.general_log;  -- 如果启用的话

4. 安全加固建议

-- 删除测试数据库
DROP DATABASE test;

-- 删除匿名用户
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

-- 检查空密码用户
SELECT User, Host FROM mysql.user 
WHERE authentication_string = '' OR authentication_string IS NULL;

-- 限制root远程登录
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;

七、故障排查示例

1. 连接数满排查

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看当前连接
SELECT * FROM information_schema.PROCESSLIST;
-- 或
SELECT * FROM sys.session;

-- 查看连接来源
SELECT 
    USER,
    HOST,
    COUNT(*) as connection_count
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY connection_count DESC;

2. 性能问题排查

-- 查看慢查询
SELECT * FROM sys.statement_analysis 
ORDER BY avg_latency DESC 
LIMIT 10;

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 查看I/O瓶颈
SELECT * FROM sys.io_global_by_file_by_bytes 
LIMIT 10;

-- 查看内存使用
SELECT * FROM sys.memory_global_by_current_bytes 
LIMIT 10;

3. 权限问题排查

-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';

-- 查看所有用户权限
SELECT 
    CONCAT('\'', user, '\'@\'', host, '\'') AS user_host,
    authentication_string
FROM mysql.user
ORDER BY user, host;

-- 检查权限冲突
SELECT * FROM mysql.user WHERE user = 'username';
SELECT * FROM mysql.db WHERE user = 'username';
SELECT * FROM mysql.tables_priv WHERE user = 'username';

八、实用查询脚本

1. 系统状态概览

-- 系统概览脚本
SELECT 
    '数据库统计' AS '类别',
    COUNT(*) AS '数量'
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
UNION ALL
SELECT 
    '表统计',
    COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
UNION ALL
SELECT 
    '总数据大小(GB)',
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

2. 监控关键指标

-- 关键性能指标监控
SELECT 
    VARIABLE_NAME AS '指标',
    VARIABLE_VALUE AS '值',
    '状态变量' AS '类型'
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'Threads_connected',
    'Threads_running',
    'Innodb_buffer_pool_pages_dirty',
    'Innodb_buffer_pool_reads',
    'Innodb_rows_read',
    'Innodb_rows_inserted',
    'Innodb_rows_updated',
    'Innodb_rows_deleted'
)
UNION ALL
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    '系统变量'
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'max_connections',
    'innodb_buffer_pool_size',
    'innodb_log_file_size',
    'query_cache_size'
);

总结

MySQL 5.7 的系统数据库提供了强大的监控和管理能力：

  1. information_schema：元数据查询，标准化接口
  2. mysql：用户权限和系统配置核心
  3. performance_schema：深度性能监控
  4. sys：用户友好的性能诊断视图

最佳实践建议：

  • 定期备份mysql数据库（特别是user表）
  • 使用performance_schema监控生产环境性能
  • 利用sys视图进行日常故障排查
  • 谨慎操作系统表，避免直接修改
  • 保持系统数据库的版本兼容性

这些系统数据库是MySQL管理和优化的核心工具，熟练掌握它们对数据库管理员至关重要。

posted @ 2025-12-22 15:30  冰冷的火  阅读(28)  评论(0)    收藏  举报