故障描述

客户反馈，通过Commvault软件备份MySQL报错，错误显示

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

问题分析

环境实例是通过其他环境数据库进行过一次数据库全库备份 --all-databases ，并且将dump文件进行了source全库导入。

在5.7x的mysql版本中，mysqldump全库备份 --all-databases导出会缺少sys库里面的函数存储过程，这是一个 BUG。
平时业务使用没有问题，但是逻辑备份会访问sys的视图，导致报错。

问题复现

1.构造问题环境

在一个正常环境进行一次数据库全库备份

mysqldump -uroot -pxxxxx -A --set-gtid-purged=OFF --single-transaction --routines --events --triggers > all.sql

在一个初始化实例进行全量导入

mysql -uroot -pxxxxx <all.sql

2.复现报错

访问sys的视图报错ERROR 1356

mysql> SHOW FIELDS FROM sys.host_summary;
ERROR 1356 (HY000): View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

查询sys库的函数存储过程

mysql> SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'sys';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)

问题解决

缺失系统库sys的函数存储过程，本来是可以通过初始化系统数据

mysql_upgrade --upgrade-system-tables --skip-verbose --force -uroot -p

但是考虑到目前是生产环境，mysql_upgrade对线上业务影响未知，为了减少对业务的影响，计划通过导入导出恢复sys的函数存储过程。

1.导出sys

在一个正常环境导出

mysqldump -uroot -pxxxxx --set-gtid-purged=OFF --single-transaction --databases --routines sys > sys_dump.sql

vi sys_dump.sql
"sys_dump.sql" 4961L, 510049C-- MySQL dump 10.13 Distrib 5.7.26, for linux-glibc2.12 (x86_64)
-- Host: localhost Database: sys

-- Server version 5.7.26-enterprise-commercial-advanced-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: sys
CREATE DATABASE /!32312 IF NOT EXISTS/ sys /*!40100 DEFAULT CHARACTER SET utf8 */;

USE sys;

--
-- Temporary table structure for view host_summary
DROP TABLE IF EXISTS host_summary;
/!50001 DROP VIEW IF EXISTS host_summary/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/!50001 CREATE VIEW host_summary AS SELECT
1 AS host,
1 AS statements,
1 AS statement_latency,
1 AS statement_avg_latency,
1 AS table_scans,
1 AS file_ios,
1 AS file_io_latency,
1 AS current_connections,
1 AS total_connections,
1 AS unique_users,
1 AS current_memory,
1 AS total_memory_allocated/;
SET character_set_client = @saved_cs_client;
。。。。。。。。。。。。。。。。。。。。。。。。。。。

2.导入sys

mysql -uroot -pxxxxx <sys_dump.sql

3. 验证

访问sys视图

SHOW FIELDS FROM sys.host_summary;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| host | varchar(60) | YES | | NULL | |
| statements | decimal(64,0) | YES | | NULL | |
| statement_latency | text | YES | | NULL | |
| statement_avg_latency | text | YES | | NULL | |
| table_scans | decimal(65,0) | YES | | NULL | |
| file_ios | decimal(64,0) | YES | | NULL | |
| file_io_latency | text | YES | | NULL | |
| current_connections | decimal(41,0) | YES | | NULL | |
| total_connections | decimal(41,0) | YES | | NULL | |
| unique_users | bigint(21) | NO | | 0 | |
| current_memory | text | YES | | NULL | |
| total_memory_allocated | text | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

查询sys库的函数存储过程

mysql> SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'sys';
+----------+
| count() |
+----------+
| 48 |
+----------+
1 row in set (0.00 sec)