MySQL授予用户权限时tables_priv表损坏的修复方案咨询

我为大学作业搭建了一个宜家商店的模拟数据库，已成功创建多个视图及新用户，操作代码如下：

-- Active: 1695563150024@@127.0.0.1@3306@ikea
USE ikea;

--No 1
CREATE VIEW view_service_and_total AS
SELECT o.OrderID AS `Order ID`, DATE_FORMAT(o.OrderDate, '%d %M %y') AS `Date`, 
s.ServiceDesc AS `Service Type`, s.Price AS `Service`, SUM(s.Price) + SUM(od.Price * od.Qty) AS `Total`
FROM orders AS o 
LEFT JOIN orderdetails AS od ON o.OrderID = od.OrderID
LEFT JOIN servicedetails AS sd ON o.OrderID = sd.OrderID
LEFT JOIN services AS s ON s.`ServiceID` = sd.`ServiceID`
WHERE ISNULL(s.ServiceDesc) = 0 AND ISNULL(s.Price) = 0
GROUP BY `Order ID`;


--No 3
CREATE VIEW nama_employees_sales AS
SELECT e.EmployeeID, 
CONCAT(e.FirstName, ' ', e.LastName) AS `Employee Name`,
j.JobDesc AS `Job`, j.Salary
FROM employees AS e 
LEFT JOIN jobs AS j ON e.JobID = j.JobID
ORDER BY `Employee Name` ASC;


--No 4
CREATE VIEW food_sales AS
SELECT r.F_OrderID AS ID, DATE_FORMAT(r.F_OrderDate, '%d %M %y') AS DATE,
CONCAT(e.`FirstName`, ' ', e.`LastName`) AS `Employee Name`, p.ProductName AS `Order Food`,
fod.Price, fod.`Qty`, (fod.Price * fod.`Qty`) AS `Total`
FROM restaurant AS r
LEFT JOIN employees AS e ON r.`EmployeeID` = e.`EmployeeID`
LEFT JOIN forderdetails AS fod ON fod.`F_OrderID` = r.`F_OrderID`
LEFT JOIN products AS p ON p.`ArticleID` = fod.`ArticleID`;


SELECT * FROM food_sales;
--No 5

CREATE USER 'Aldwin'@'localhost' IDENTIFIED BY 'aldsetiawan';
CREATE USER 'Hansen'@'localhost' IDENTIFIED BY 'hnsedrick';
CREATE USER 'Reza'@'localhost' IDENTIFIED BY 'rzsatyawijaya';
CREATE USER 'Sintya'@'localhost' IDENTIFIED BY 'styoktaviani';

但执行以下授予用户权限的操作时出错：

--No 6
GRANT SELECT ON food_sales 
TO 'Aldwin'@'localhost';

报错信息：

"Table 'tables_priv' is marked as crashed and should be repaired".

尝试执行REPAIR TABLE tables_priv;但无效，因为该表不在当前数据库中。

tables_priv是MySQL系统库mysql下的权限表，修复需切换到对应库操作，具体步骤如下：

1. 切换到mysql系统数据库
   执行命令：

   USE mysql;
   

2. 修复损坏的tables_priv表
   执行基础修复命令：

   REPAIR TABLE tables_priv;
   

   若修复失败，尝试带EXTENDED参数强制修复：

   REPAIR TABLE tables_priv EXTENDED;
   

3. 若命令行修复无效，尝试手动修复

   • 先停止MySQL服务
   • 找到tables_priv的物理文件（通常在MySQL数据目录的mysql文件夹下，对应文件为tables_priv.MYD、tables_priv.MYI、tables_priv.frm）
   • 执行MyISAM修复工具命令：

     myisamchk -r /path/to/mysql/tables_priv
     

     替换/path/to/mysql/为实际的MySQL数据目录路径
   • 修复完成后重启MySQL服务

4. 验证修复并重新授权
   回到ikea库重新执行授权：

   USE ikea;
   GRANT SELECT ON food_sales TO 'Aldwin'@'localhost';
   

内容的提问来源于stack exchange，提问作者Kevin H