DB Instance Becoming Read-Only Due to Insufficient Storage

Scenario

The MySQL server is running with the --read-only option so it cannot execute this statement

Troubleshooting

1. Go to the instance details page and check whether the storage is full.
2. Log in to the database and check the read_only variable.

   show variables like 'read_only';

3. From the previous steps, you can find that the storage space is full and the instance status has changed to read-only. As a result, the SQL statement fails to be executed.

Solution

1. For insufficient storage caused by increased workload data, scale up storage space.

   If the original storage has reached the maximum, upgrade the specifications first.

2. If too much data is stored, delete unnecessary historical data.
   1. If the instance becomes read-only, you need to contact technical support to cancel the read-only status first.
   2. To clear up space, you can optimize tables with a high fragmentation rate during off-peak hours.

      To delete data of an entire table, run DROP or TRUNCATE. To delete part of table data, run DELETE and OPTIMIZE TABLE.

3. If binlog files occupy too much space, clear local binlogs.
4. If temporary files generated by sorting queries occupy too much storage space, optimize your SQL queries.