MySQL 5.7 sys库里的视图不能访问

听了课之后准备看下sys库的情况
结果发现生产环境的sys库视图不能访问
SELECT * FROM sys.host_summary;
#########################################
1 queries executed, 0 success, 1 errors, 0 warnings

查询:SELECT * FROM sys.host_summary LIMIT 0, 1000

错误代码: 1356
View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.002 sec
#############################################
我看错误代码说,可能是definer/invoker 造成权限不足。我就查看了视图的内容
 
DELIMITER $$

ALTER ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `host_summary` AS
SELECT
IF(ISNULL(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,
SUM(`stmt`.`total`) AS `statements`,
`sys`.`format_time`(
SUM(`stmt`.`total_latency`)) AS `statement_latency`,
`sys`.`format_time`(
IFNULL((SUM(`stmt`.`total_latency`) / NULLIF(SUM(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,
SUM(`stmt`.`full_scans`) AS `table_scans`,
SUM(`io`.`ios`) AS `file_ios`,
`sys`.`format_time`(
SUM(`io`.`io_latency`)) AS `file_io_latency`,
SUM(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,
SUM(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,
COUNT(DISTINCT `performance_schema`.`accounts`.`USER`) AS `unique_users`,
`sys`.`format_bytes`(
SUM(`mem`.`current_allocated`)) AS `current_memory`,
`sys`.`format_bytes`(
SUM(`mem`.`total_allocated`)) AS `total_memory_allocated`
FROM (((`performance_schema`.`accounts`
JOIN `sys`.`x$host_summary_by_statement_latency` `stmt`
ON ((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`)))
JOIN `sys`.`x$host_summary_by_file_io` `io`
ON ((`performance_schema`.`accounts`.`HOST` = `io`.`host`)))
JOIN `sys`.`x$memory_by_host_by_current_bytes` `mem`
ON ((`performance_schema`.`accounts`.`HOST` = `mem`.`host`)))
GROUP BY IF(ISNULL(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)$$

DELIMITER ;
果然,权限校验是 invoker 。不过我发现有些节点可以访问sys视图,但是也是invoker 校验级别。
我还查了两个账号的权限,完全一样。
不过,我想死马当活马医,先试试,改变下校验级别,为definer ,因为创建者正是mysql.sys
当我执行了修改,又报错了
 
1 queries executed, 0 success, 1 errors, 0 warnings

查询:ALTER ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY definer VIEW `host_summary` AS select if(isnull(`performa...

错误代码: 1305
FUNCTION sys.format_time does not exist

执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec
这里又说视图涉及到的函数缺失了。于是我又查看sys库的函数。
 
果然,一个函数都没有!!!
 
真是很奇怪,这些sys库访问有问题的节点,情况各不相同,版本从5.7.17-5.7.20  
其中实例里的节点,是从5.7.6升级到5.7.17的,当时是做了一个5.7.17 的实例做了slave,切换升级的,使用一年半了没发生任何问题。不知怎么的sys库就不可用了。
其他sys库不可用的节点,情况不完全相同,有的是直接做的新节点,但是这些sys有问题的节点大多进行过全量恢复  mysqldump -A 那种,就是mysql 被备份替换过。不过备份和恢复过程都没有出错。所以目前,还没找到问题的根本原因。
 
如果是因为升级的时候,低版本的系统库把高版本的系统库覆盖了,应该可以用的高版本系统库,给覆盖一下。
 
已邀请:

liyh

赞同来自: Archer

已经解决了!
找一个可以使用的sys库的节点,备份mysql.proc 表里面更sys相关的信息
 
select * from mysql.proc where db='sys';
 
把这部分数据备份成insert文件,在sys库有问题的节点执行,即可。
 
如果是一对主从有问题,就可以直接执行,如果是单节点问题,可以使用set sql_log_bin=0; 令执行的命令不写入binlog。
 
 

要回复问题请先登录注册