MySQL 5.7 performance_schema库和sys库常用SQL

本文分享了MySQL数据库性能监控的实用SQL语句,包括检查无主键表、临时表创建者、未正确关闭的连接、DDL元数据锁跟踪及执行进度等。同时介绍了sys库用于监控表访问量、数据库连接、冗余与未使用索引、自增ID监控及磁盘IO文件的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

performance_schema库常用SQL:

查看没有主键的表:

    SELECT DISTINCT t.table_schema, t.table_name

      FROM information_schema.tables AS t

      LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema 

AND t.table_name = c.table_name AND c.column_key = "PRI"

     WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')

       AND c.table_name IS NULL AND t.table_type != 'VIEW';


例如:

mysql> SELECT DISTINCT t.table_schema, t.table_name

    ->       FROM information_schema.tables AS t

    ->       LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema 

AND t.table_name = c.table_name  AND c.column_key = "PRI"

    ->      WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')

    ->        AND c.table_name IS NULL AND t.table_type != 'VIEW';


+--------------+---------------------------+

| table_schema | table_name                |

+--------------+---------------------------+

| S85          | dsf                       |

| test         | innodb_lock_monitor       |

| test         | innodb_monitor            |

| test         | innodb_table_monitor      |

| test         | innodb_tablespace_monitor |

| zhwp102      | t_orgpriority             |

| zhwp102      | t_task_ext                |

| zhwp102      | t_web_common              |

| zhwp111      | t_orgpriority             |

| zhwp111      | t_task_ext                |

| zhwp111      | t_web_common              |

| zhwp111      | t_weibo                   |

| zhwp_prod    | t_orgpriority             |

| zhwp_prod    | t_task_ext                |

| zhwp_prod    | t_web_common              |

| zhwp_prod    | t_weibo                   |

| zhwpzj111    | t_orgpriority             |

| zhwpzj111    | t_task_ext                |

| zhwpzj111    | t_web_common              |

| zhwpzj111    | t_weibo                   |

+--------------+---------------------------+

20 rows in set (1 min 27.55 sec)


没有主键:

mysql> desc S85.dsf;    

+------------+----------------------+------+-----+-------------------+-------+

| Field      | Type                 | Null | Key | Default           | Extra |

+------------+----------------------+------+-----+-------------------+-------+

| sourceDay  | date                 | YES  |     | NULL              |       |

| sourceTime | datetime             | NO   |     | CURRENT_TIMESTAMP |       |

| affections | smallint(5) unsigned | NO   |     | 1                 |       |

+------------+----------------------+------+-----+-------------------+-------+

3 rows in set (0.00 sec)


查看是谁创建的临时表


    SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, 

sum_created_tmp_tables AS tmp_tables

      FROM performance_schema.events_statements_summary_by_account_by_event_name

     WHERE sum_created_tmp_disk_tables > 0

        OR sum_created_tmp_tables > 0 ;



没有正确关闭数据库连接的用户

    SELECT ess.user, ess.host

         , (a.total_connections - a.current_connections) - ess.count_star as not_closed

         , ((a.total_connections - a.current_connections) - ess.count_star) * 100 /

           (a.total_connections - a.current_connections) as pct_not_closed

      FROM performance_schema.events_statements_summary_by_account_by_event_name ess

      JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)

     WHERE ess.event_name = 'statement/com/quit'

       AND (a.total_connections - a.current_connections) > ess.count_star ;


DDL元数据锁跟踪

1.打开跟踪:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE 

NAME = 'wait/lock/metadata/sql/mdl';

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE

 NAME = 'global_instrumentation';

2.查询metadata lock:

select  * from performance_schema.metadata_locks;

select  * from performance_schema.metadata_locks where LOCK_STATUS like 'PENDING%';

select ID from information_schema.processlist where Info  like '%20190416%' \G

SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_STATUS,processlist_id 

    FROM performance_schema.metadata_locks mdl

    INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id 

    WHERE processlist_id <> @@pseudo_thread_id;


3.关闭跟踪:

UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE 

NAME = 'wait/lock/metadata/sql/mdl';    

   

DDL执行进度跟踪

1.打开跟踪:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';

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

2.查看DDL执行进度:

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 

as COMPLETED FROM performance_schema.events_stages_current;


sys库常用SQL:

查看表访问量

select table_schema,table_name,sum(io_read_requests+io_write_requests) io from sys.schema_table_statistics 

group by table_schema,table_name order by io desc limit 10;


查看数据库连接情况

select * from sys.processlist \G

select * from sys.session limit 10 \G

select * from sys.x$processlist \G

select * from sys.x$session \G


查看冗余索引

select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,

dominant_index_columns  from sys.schema_redundant_indexes;


查看未使用索引

select * from sys.schema_unused_indexes;


表自增ID监控

select * from sys.schema_auto_increment_columns limit 10;


查看实际消耗磁盘IO的文件

select file,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_io desc limit 10;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15498/viewspace-2641633/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2641633/

<think>我们正在讨论的是MySQL中不同用户权限(root vs admin)导致的数据库可见性差异。根据引用[1]中提到的MySQL权限检查流程,权限检查是从user表开始,逐级向下检查(db、tables_priv、columns_priv)。因此,不同用户被授予的权限不同,能够访问的数据库表也会不同。 首先,root用户是MySQL的超级用户,通常拥有所有权限(ALL PRIVILEGES),包括全局级别的权限(在user表中被授予)。而admin用户可能是我们自定义的一个用户,它的权限需要具体查看。 在MySQL中,一个用户能看到的数据库(通过SHOW DATABASES命令)是由其权限决定的。具体来说: 1. 如果用户拥有全局级别的SHOW DATABASES权限(在user表中设置),则可以看到所有的数据库。 2. 如果用户没有全局的SHOW DATABASES权限,那么只能看到那些被授予了权限的数据库(在db表中被授权)以及information_schema数据库。 另外,MySQL中有一个系统变量`show databases`权限,它可以通过以下方式查看: - 对于root用户:默认拥有全局SHOW DATABASES权限,因此可以看到所有数据库。 - 对于admin用户:我们需要检查其是否被授予了SHOW DATABASES权限。 如何检查admin用户的权限? 我们可以使用以下命令: ```sql SHOW GRANTS FOR 'admin'@'%'; ``` 假设admin用户的权限如下: - 如果admin用户被授予了全局的SHOW DATABASES权限(例如:GRANT SHOW DATABASES ON *.* TO 'admin'@'%'),那么admin用户可以看到所有数据库。 - 如果admin用户只被授予了某个特定数据库的权限(例如:GRANT ALL ON mydb.* TO 'admin'@'%'),那么admin用户只能看到这个数据库(mydb)information_schema。 因此,root用户admin用户在数据库可见性上的差异主要取决于admin用户被授予的权限范围。 另外,引用[1]还提到,MySQL 5.7在安全性上有提升,比如使用AES 256加密(这里不展开,但说明权限管理也是安全的一部分)。 总结: - root用户:默认拥有所有权限,包括全局的SHOW DATABASES,因此可以看到所有数据库。 - admin用户:需要具体看其权限。如果只授予了特定数据库的权限,则只能看到该数据库information_schema;如果授予了全局的SHOW DATABASES,则可以看到所有数据库。 示例: 假设我们有两个数据库:db1db2。 - root用户执行`SHOW DATABASES;`会看到db1, db2, information_schema, mysql, performance_schema等。 - 如果admin用户只被授予了db1的权限,那么admin用户登录后执行`SHOW DATABASES;`只能看到db1information_schema。 验证方法: 我们可以用admin用户登录,然后执行: ```sql SHOW DATABASES; ``` 另外,我们还可以通过查看系统表来验证admin用户的权限: ```sql -- 查看全局权限(user表) SELECT * FROM mysql.user WHERE user='admin' AND host='%'\G -- 查看数据库级权限(db表) SELECT * FROM mysql.db WHERE user='admin' AND host='%'\G ``` 注意:在MySQL中,权限的生效可能需要刷新权限(FLUSH PRIVILEGES;),但通常创建用户或授权后会自动刷新,除非手动修改了权限表。 因此,要解决用户可见数据库的差异,我们需要为admin用户授予相应的权限。例如,如果我们希望admin用户能看到所有数据库,可以执行: ```sql GRANT SHOW DATABASES ON *.* TO 'admin'@'%'; ``` 但注意:授予SHOW DATABASES权限并不代表可以访问这些数据库,还需要对具体的数据库或表有相应的操作权限(如SELECT、INSERT等)。 最后,引用[3]中提到了MySQL的权限层级(从user表到columns_priv表),所以权限检查是逐层进行的。因此,admin用户能否看到某个数据库,取决于在user表或db表中是否有相应的权限。 如果admin用户看不到某些数据库,那么可能是因为没有在全局(user表)或数据库级(db表)被授予SHOW DATABASES权限或具体的数据库权限。 根据以上分析,我们可以回答用户的问题:root用户admin用户权限差异导致数据库可见性不同,主要是因为admin用户的权限可能被限制在特定的数据库上,而root用户拥有所有权限。 相关问题: 1. 如何查看MySQL用户的权限? 2. 如何控制用户可见的数据库? 3. MySQL的权限层级是如何工作的? 4. 为什么新建的用户无法看到所有数据库5. 如何为用户授予全局的SHOW DATABASES权限? 注意:在回答中,我们引用了用户提供的引用[1][3]的内容,并在适当位置添加了引用标记[^1][^3]。</think>在 MySQL 中,`root` 用户 `admin` 用户的权限差异会导致数据库可见性显著不同,这主要源于 MySQL 的权限层级结构权限检查机制。以下是详细分析: ### 一、权限层级结构与检查机制[^1] MySQL 权限检查遵循严格的层级顺序: 1. **User 表**(全局权限) ```sql SELECT * FROM mysql.user WHERE user='admin' AND host='%'; ``` 2. **DB 表**(数据库级权限) ```sql SELECT * FROM mysql.db WHERE user='admin' AND host='%'; ``` 3. **Tables_priv 表**(表级权限) 4. **Columns_priv 表**(列级权限) 系统**自上而下检查权限**,一旦在某一层找到匹配权限即停止检查。例如: - 若 `admin` 在 `user` 表有全局 `SELECT` 权限,则无需检查下层权限 - 若 `user` 表无权限,则检查 `db` 表(数据库级权限) ### 二、root 用户 vs admin 用户的权限差异 | 特性 | root 用户 | admin 用户 | |--------------------|------------------------------|--------------------------------| | **默认权限** | 拥有所有全局权限 (`GRANT ALL`) | 需显式授权 | | **系统可见性** | 可见所有系统 (`mysql`, `sys`等) | 可能不可见(除非显式授权) | | **数据库创建权限** | ✅ 可创建新数据库 | ❌ 默认无此权限 | | **用户管理权限** | ✅ 可创建/删除用户 | ❌ 默认无此权限 | | **权限深度** | 覆盖所有层级 | 通常仅限特定数据库/表 | ### 三、可见性差异的典型场景 #### 场景 1:系统不可见 当 `admin` 用户执行 `SHOW DATABASES;` 时: ```sql -- admin 用户可能看到的数据库 +--------------------+ | Database | +--------------------+ | myapp_db | -- 被显式授权的数据库 | information_schema | -- 所有用户可见 +--------------------+ -- root 用户看到的数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | -- 系统权限表 | performance_schema | | sys | | myapp_db | +--------------------+ ``` **原因**:`admin` 缺乏对系统的 `SHOW DATABASES` 权限[^1]。 #### 场景 2:表结构查询失败 当 `admin` 查询系统表时: ```sql -- admin 执行 DESC mysql.user; ERROR 1142 (42000): SELECT command denied to user 'admin'@'%' for table 'user' -- root 执行成功 +--------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | ... | ... | ... | ... | ... | ... | +--------------------------+-----------------------------------+------+-----+---------+-------+ ``` **原因**:`mysql.user` 表需要全局权限才能访问。 ### 四、权限验证与解决方案 #### 1. 检查 admin 的实际权限 ```sql -- 查看全局权限 SHOW GRANTS FOR 'admin'@'%'; ``` 典型输出: ```sql GRANT USAGE ON *.* TO `admin`@`%` -- 无全局权限 GRANT SELECT ON `myapp_db`.* TO `admin`@`%` -- 仅限特定 ``` #### 2. 授权解决可见性问题 ```sql -- 授予查看所有数据库权限 GRANT SHOW DATABASES ON *.* TO 'admin'@'%'; -- 授予系统表访问权限 GRANT SELECT ON mysql.* TO 'admin'@'%'; -- 刷新权限 FLUSH PRIVILEGES; ``` #### 3. 安全建议 ```sql -- 最小权限原则示例 GRANT SELECT, INSERT, UPDATE ON myapp_db.* TO 'admin'@'%'; REVOKE DROP ON myapp_db.* FROM 'admin'@'%'; -- 移除危险权限 ``` ### 五、MySQL 8.0 的权限改进 新版 MySQL 增强了权限控制: 1. **角色权限管理** ```sql CREATE ROLE auditor; GRANT SELECT ON *.* TO auditor; GRANT auditor TO 'admin'@'%'; ``` 2. **动态权限** ```sql GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'admin'@'%'; ``` > **关键结论**:`root` 用户拥有完整的权限树,而 `admin` 用户的可见性完全取决于其被授予的权限范围。权限差异本质上是 MySQL 权限模型的层级化设计所致[^1][^3]。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值