MySQL5.7系统库SYS不可用解决方案

MySQL数据库sys库不可用解决方案

背景

在一次处理客户 MySQL 问题时，客户的 sys 库出现了无法使用的情况

在主从复制的，从库执行查询select * from sys.memory_global_total;报错如下：

mysql> select * from sys.memory_global_total;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

数据库版本：Server version: 5.7.9-log MySQL Community Server (GPL)

---

问题排查

经过初步分析，可能的原因有：

1. 权限不足；
2. sys 库的 functions 和 procedures 丢失；
3. 使用mysqldump全备后跨版本还原恢复，导致系统库异常；
4. MySQL升级后未执行 mysql_upgrade。

1. 权限问题

首先排查是否是权限问题：

mysql> SHOW GRANTS FOR root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

通过查看权限信息，确认并非权限问题。

2. sys 库的 functions 和 procedures 丢失

接下来检查 sys 库的 functions 和 procedures 是否丢失：

mysql> SELECT * FROM mysql.proc;
Empty set (0.00 sec)

mysql> SHOW PROCEDURE STATUS WHERE Db = 'sys';
Empty set (0.00 sec)

mysql> SHOW FUNCTION STATUS WHERE Db = 'sys';
Empty set (0.00 sec)

sys 库的 functions 和 procedures 确实丢失了。这表明问题可能出在 mysqldump 全备或未执行 mysql_upgrade。

---

3. 备份与还原测试

为了进一步验证是否跨版本造成的问题，进行了 mysqldump 全备恢复的测试，首先备份 MySQL 数据库并确认 sys 库是否正常：

mysql> SELECT * FROM sys.version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.0       | 5.7.9-log     |
+-------------+---------------+
1 row in set (0.10 sec)

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

接着使用 mysqldump 进行备份：

mysqldump --all-databases --set-gtid-purged=OFF --master-data=2 --single-transaction --routines --events --triggers --max_allowed_packet=256M > all.sql

然后恢复数据

mysql -uroot -pXXXXXXXX  < all.sql

检查 sys 库：

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

发现即便在同一版本的数据库下恢复，依然出现了相同的错误。

---

4. 问题分析

在备份 SQL 文件中发现了以下问题：

1. 备份文件中删除了 mysql.proc 表；
2. 重新创建了一个新的 mysql.proc 表；
3. 没有备份 CREATE ROUTINE 的数据。

这表明 mysqldump 全备在还原时，会清空 mysql.proc，导致 sys 库无法正常使用。

通过对不同 MySQL 版本（如5.7.9，5.7.10，5.7.11，5.7.12，5.7.13，5.7.21，5.7.25，5.7.28，5.7.31）的测试，发现无论版本如何，只要使用 --all-databases 参数备份，还原后就会报 ERROR 1356。

---

5. 官方说明与 BUG 反馈

根据官方文档说明，sys 库不会被默认备份，必须显式指定 sys 库进行备份。

相关 BUG 反馈：

• MySQL Bug 86807
• MySQL Bug 92631
• MySQL Bug 83259

---

解决方案

方案 1：使用 mysql_upgrade 修复 sys 库

适用于 sys 库已经损坏的情况下。

1. 删除 sys 库：

   DROP DATABASE sys;

2. 执行 mysql_upgrade：

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

​ 注意：mysql_upgrade 在修理 sys 库的同时，还修理 mysql 库和用户库表（期间加锁且速度一般），有极小可能会误伤；

​ 使用 mysql_upgrade 的时候要加上 --upgrade-system-tables，不然会扫描用户库表。

方案 2：全备时同时备份 sys 库

适用于需要恢复数据库时，确保 sys 库的正常。

mysqldump -A --set-gtid-purged=OFF --master-data=2 --single-transaction --routines --events --triggers > all.sql
mysqldump --databases --routines sys > sys_dump.sql

方案 3：使用 databases 参数全备

适用于所有场景的安全备份。

databases=$(mysql -NBe "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('performance_schema','information_schema')")
mysqldump --set-gtid-purged=OFF --master-data=2 --single-transaction --routines --events --triggers --max_allowed_packet=256M --databases $databases > all.sql

方案 4：使用 mysql-sys 开源代码修复

适用于生产环境中 sys 库已经损坏的场景。

1. 下载 mysql-sys 的创建语句：

   git clone https://github.com/mysql/mysql-sys

2. 根据数据库版本执行安装命令：

   source before_setup.sql
   source sys_57.sql
   source after_setup.sql

---

以上为针对 MySQL sys 库问题的解决方案，希望对您有所帮助。

本文由mdnice多平台发布