Resolving MySQL Read-Only Table Issues After Data Directory Migration

Background

During a blog migration, an operational error rendered the original MySQL instance unusable. To recover, a new MySQL instance was created, its data directory cleared, and the original data directory was copied over. The new instance started successfully, allowing login and data reads, but write operations failed with the error: Table 'xxx' is read only. The root cause was traced to a configuration change in my.cnf, specifically the addition of innodb_force_recovery = 4.

This article explores the issue, evaluates common solutions, and details the effective resolution, focusing on the context of direct data directory copying under the same MySQL version.

Problem Analysis

The "read-only" error stemmed from the innodb_force_recovery = 4 setting in the my.cnf configuration file. This InnoDB parameter is used to recover corrupted databases by forcing MySQL to start in a recovery mode. When set to a value from 1 to 6, it enables operations like SELECT, CREATE, and DROP, but restricts write operations (INSERT, UPDATE, DELETE) to prevent further data corruption. At level 4, InnoDB skips significant consistency checks, rendering tables read-only.

In this case, the database was likely intact, as login and read operations worked fine. The innodb_force_recovery setting was added unnecessarily, possibly to force the database to start after the migration, causing the write restriction.

Common Solutions and Their Relevance

Online research highlighted three potential solutions, but only one addressed the issue.

Solution 1: Refresh Permissions with mysqladmin

• Description: Use mysqladmin flush-privileges or FLUSH PRIVILEGES to reload permission tables.
• Applicability: This addresses issues where permission tables (e.g., mysql.user) are not loaded correctly, causing access errors.
• Relevance: The problem was unrelated to permissions, as the error was specifically about read-only tables due to InnoDB settings. This solution was ineffective.

Solution 2: Fix Directory Ownership and Permissions

• Description: Ensure the data directory has correct ownership (e.g., mysql:mysql) and permissions (e.g., 700).
• Applicability: Incorrect ownership or permissions can prevent MySQL from writing to the data directory, causing errors.
• Relevance: While this is a common issue in migrations, the specific "read-only" error pointed to InnoDB configuration, not file permissions. This solution was not applicable.

Solution 3: Remove innodb_force_recovery from my.cnf

• Description: Comment out or remove the innodb_force_recovery = 4 line in my.cnf and restart MySQL.
• Applicability: Directly addresses the read-only issue caused by recovery mode, which restricts write operations.
• Relevance: This was the correct solution. Commenting out the setting allowed MySQL to start in normal mode, restoring write functionality.

Why Direct Data Directory Copying?

Directly copying the data directory is a convenient and fast method for migrating MySQL databases, especially under the same MySQL version, where compatibility is assured. Benefits include:

• Speed: Copying files is faster than exporting and importing via mysqldump, especially for large databases.
• Simplicity: No additional tools or commands are needed.
• Completeness: Retains all database objects, including tables, indexes, and triggers.

However, even with the same MySQL version, risks include:

• Transaction Log Conflicts: Files like ib_logfile0 and ib_logfile1 may conflict with new configurations (e.g., innodb_log_file_size).
• Permissions: Copied directories may require ownership (chown -R mysql:mysql) and permission (chmod -R 700) adjustments.
• Configuration Mismatches: Differences in my.cnf settings, like innodb_force_recovery, can introduce issues.

Optimized Migration Workflow

To ensure a reliable migration using direct data directory copying under the same MySQL version, follow these steps:

1. Stop the Original MySQL Service:

   systemctl stop mysql
   

   Ensure no writes occur during the copy process to avoid file inconsistencies.

2. Prepare the Target Environment:

   • Clear the target data directory: rm -rf /path/to/data/*.
   • Optionally, remove ib_logfile* files to prevent transaction log conflicts.

3. Copy the Data Directory:

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

   Use rsync or cp -r for reliable copying.

4. Fix Permissions:

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

5. Verify my.cnf Configuration:

   • Compare old and new my.cnf files for consistency.
   • Remove recovery settings like innodb_force_recovery.

6. Start MySQL and Test:

   systemctl start mysql
   

   • Test reads: SELECT * FROM table_name LIMIT 1.
   • Test writes: INSERT INTO table_name ....
   • Check the error log (e.g., /var/log/mysql/error.log).

7. Validate Data Integrity:

   mysqlcheck --all-databases
   

   Use CHECK TABLE or mysqlcheck to ensure tables are intact.

Lessons from the Case

The issue was resolved by commenting out innodb_force_recovery = 4 in my.cnf, confirming that the data directory was intact and compatible. The setting was likely added to force the database to start, but it unnecessarily restricted writes. This highlights the importance of reviewing configuration changes post-migration.

Recommendations

• Avoid Unnecessary Recovery Settings: Only use innodb_force_recovery (1–6) when the database fails to start, starting with the lowest value.
• Backup Regularly: Even with direct copying, maintain mysqldump or XtraBackup backups for safety.
• Test Thoroughly: Post-migration, verify read and write operations and check logs for errors.
• Consider Alternatives: For complex migrations, tools like mysqldump or Percona XtraBackup offer safer, more structured approaches.

Conclusion

Directly copying the MySQL data directory is a convenient migration method under the same MySQL version, but it requires careful handling of configurations and permissions. The read-only issue caused by innodb_force_recovery serves as a reminder to verify my.cnf settings after migration. By following the optimized workflow above, you can leverage the speed and simplicity of this approach while minimizing risks.