使用mysqldump恢复SQL文件时遇InnoDB只读错误的解决求助

问题描述 #

使用mysqldump恢复SQL文件时触发错误：

ERROR 1874 (HY000) at line 42: InnoDB is in read only mode.

已尝试执行SET GLOBAL read_only = OFF;，并修改my.cnf配置innodb_read_only = off，但问题仍未解决。相关代码片段如下：

backup.sql片段 #

DROP TABLE IF EXISTS `mylife_2010`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mylife_2010` (
  `number` int(20) NOT NULL AUTO_INCREMENT,
  `user_no` int(20) DEFAULT NULL,
  `datestr` int(8) DEFAULT NULL,
  `time` int(2) DEFAULT NULL,
  PRIMARY KEY (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=516 DEFAULT CHARSET=utf8mb4;

my.cnf配置 #

innodb_read_only = off

已执行命令 #

SET GLOBAL read_only = OFF;

解决步骤 #

1. 排查InnoDB只读的真实触发原因 #

InnoDB进入只读模式通常不是由read_only全局变量控制，而是自身触发的保护机制。执行以下命令确认状态：

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';
SHOW GLOBAL STATUS LIKE 'InnoDB_read_only';

如果InnoDB_read_only返回ON，说明是InnoDB自身触发的只读，需要进一步排查磁盘、权限或表空间问题。

2. 检查磁盘空间与目录权限 #

• 查看数据库所在磁盘是否已满：Linux执行df -h，Windows直接查看磁盘属性，确保有足够剩余空间。
• 修复MySQL数据目录权限：确保运行MySQL的用户（通常为mysql）对数据目录拥有读写权限，执行：

chown -R mysql:mysql /var/lib/mysql
chmod -R 755 /var/lib/mysql

3. 修复InnoDB表空间异常 #

若因表空间损坏触发只读，按以下步骤操作：

1. 停止MySQL服务：

   systemctl stop mysqld  # Linux
   # 或Windows
   net stop mysql
   

2. 在my.cnf中添加临时恢复配置：

   innodb_force_recovery = 1
   

3. 启动MySQL服务：

   systemctl start mysqld  # Linux
   # 或Windows
   net start mysql
   

4. 导出所有数据库备份：

   mysqldump -u root -p --all-databases > full_backup.sql
   

5. 停止MySQL，移除innodb_force_recovery配置，删除数据目录下的ibdata1、ib_logfile0、ib_logfile1文件。
6. 重新初始化MySQL：

   mysqld --initialize --user=mysql
   

7. 启动MySQL，导入新备份：

   mysql -u root -p < full_backup.sql
   

4. 确认全局变量设置生效 #

执行SET GLOBAL read_only = OFF;后，退出当前会话重新连接，再执行SELECT @@read_only;确认值为OFF。同时检查是否有会话级别的只读设置。

5. 验证恢复账户权限 #

确认执行恢复操作的MySQL账户拥有SUPER、INSERT、CREATE、DROP等必要权限：

SELECT user, host FROM mysql.user WHERE user='你的账户名';
SHOW GRANTS FOR '你的账户名'@'localhost';

若权限不足，执行授权命令补充：

GRANT ALL PRIVILEGES ON *.* TO '你的账户名'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

内容的提问来源于stack exchange，提问作者박승제