解决MySQL数据目录迁移后表只读问题

背景

在博客迁移过程中，由于误操作导致原MySQL实例无法启动。为恢复数据，创建一个新的MySQL实例，清空其data目录，并将原实例的data目录直接拷贝过去。新实例启动后，可以正常登录和读取数据，但无法执行写入操作，报错：Table 'xxx' is read only。经排查，问题源于迁移后修改my.cnf配置文件，添加了innodb_force_recovery = 4。

本文分析问题原因，评估常见解决方案，并详细说明有效解决方法，重点讨论同版本MySQL下直接拷贝data目录的场景。

问题分析

“只读”错误的根源是my.cnf中的innodb_force_recovery = 4配置。innodb_force_recovery是InnoDB存储引擎用于恢复损坏数据库的参数，值设为1到6时，MySQL以恢复模式启动，允许SELECT、CREATE、DROP操作，但禁止INSERT、UPDATE、DELETE等写操作，以避免数据进一步损坏。级别4会跳过更多一致性检查，导致表只读。

在本例中，数据库本身可能未损坏，因为登录和读取正常。innodb_force_recovery = 4可能是为强制启动而添加的配置，意外导致写操作受限。

常见解决方案及适用性

网上常见的解决方案有三种，但只有一种解决了问题。

方案一：使用mysqladmin刷新权限

• 描述：运行mysqladmin flush-privileges或FLUSH PRIVILEGES重新加载权限表。
• 适用场景：权限表（如mysql.user）未正确加载或配置错误，导致访问问题。
• 适用性：本问题与权限无关，错误明确指向InnoDB配置导致的只读状态，因此此方案无效。

方案二：修复数据目录权限

• 描述：确保data目录的归属（如mysql:mysql）和权限（如700）正确。
• 适用场景：迁移后目录权限不足，可能导致MySQL无法写入。
• 适用性：虽然权限问题是迁移中的常见错误，但本例的“只读”错误明确由InnoDB配置引起，与权限无关，此方案不适用。

方案三：移除my.cnf中的innodb_force_recovery

• 描述：注释或删除my.cnf中的innodb_force_recovery = 4，然后重启MySQL。
• 适用场景：恢复模式导致的只读问题。
• 适用性：此方案直接解决了问题。注释该配置后，MySQL以正常模式启动，恢复写操作功能。

为什么选择直接拷贝data目录？

在同版本MySQL下，直接拷贝data目录是一种快速、便捷的迁移方式，优点包括：

• 速度快：相比mysqldump导出导入，拷贝文件速度更快，尤其适合大数据库。
• 操作简单：无需额外工具或复杂命令。
• 完整性：保留所有数据库对象（如表、索引、触发器）。

但即使同版本，仍然存在风险：

• 事务日志冲突：ib_logfile0和ib_logfile1可能与新环境的配置（如innodb_log_file_size）冲突。
• 权限问题：拷贝后的目录可能需要调整归属和权限。
• 配置差异：my.cnf设置不一致（如innodb_force_recovery）可能引发问题。

优化的迁移流程

为确保同版本下直接拷贝data目录的可靠性，建议以下步骤：

1. 停止原MySQL服务：

   systemctl stop mysql
   

   确保拷贝过程中无数据写入，避免文件不一致。

2. 准备目标环境：

   • 清空目标data目录：rm -rf /path/to/data/*。
   • 可选：删除ib_logfile*文件，防止事务日志冲突。

3. 拷贝数据目录：

   rsync -av /old/data/ /new/data/
   

   使用rsync或cp -r确保完整拷贝。

4. 修复权限：

   chown -R mysql:mysql /new/data
   chmod -R 700 /new/data
   

5. 检查my.cnf配置：

   • 对比新旧my.cnf，确保关键参数一致。
   • 删除恢复模式配置，如innodb_force_recovery。

6. 启动并测试：

   systemctl start mysql
   

   • 测试读取：SELECT * FROM table_name LIMIT 1。
   • 测试写入：INSERT INTO table_name ...。
   • 检查错误日志（如/var/log/mysql/error.log）。

7. 验证数据完整性：

   mysqlcheck --all-databases
   

   使用CHECK TABLE或mysqlcheck确保表无损坏。

本案例的经验教训

通过注释my.cnf中的innodb_force_recovery = 4，问题得以解决，表明data目录本身完整且兼容。该配置可能是为强制启动而添加，意外限制了写操作。这提醒我们在迁移后需仔细检查配置文件。

建议

• 谨慎使用恢复模式：仅在数据库无法启动时使用innodb_force_recovery，从1开始逐级尝试。
• 定期备份：即使拷贝方便，仍需用mysqldump或XtraBackup定期备份。
• 全面测试：迁移后验证读写功能，检查错误日志。
• 替代方案：对于复杂迁移，考虑mysqldump或Percona XtraBackup，确保更安全。

结论

在同版本MySQL下，直接拷贝data目录是高效的迁移方式，但需注意配置和权限管理。本例中，innodb_force_recovery导致的只读问题通过移除配置解决，凸显了检查my.cnf的重要性。遵循优化流程，可最大程度发挥直接拷贝的便捷性，同时降低风险。