Resolve a Locked RDS for MySQL instance due to exhausted storage capacity - ApsaraDB RDS - Alibaba Cloud - ApsaraDB RDS

When data files exhaust the storage capacity of your ApsaraDB RDS for MySQL instance, RDS automatically locks the instance to prevent data loss. While locked, you cannot write data to your RDS instance.

To unlock the instance, either expand its storage capacity or delete historical and unnecessary data. After resolving the issue, configure automatic storage expansion to prevent recurrence.

Prerequisites

Before deleting data files, check the minor engine version of your instance. If the instance runs MySQL 5.6, 5.7, or 8.0 and the minor engine version is earlier than 20190815, update the minor engine version first.

For instructions, see Update the minor engine version.

Important notes

Back up table data before deleting it to prevent data loss.
DELETE does not release storage on ApsaraDB RDS for MySQL. Use DROP TABLE or TRUNCATE TABLE instead.
OPTIMIZE TABLE locks the table during execution. Run it during off-peak hours.
After deleting data files, allow some time for storage to be released.
After storage is freed or instance specifications are changed, RDS unlocks the instance in approximately 5 minutes.

Unlock the instance

Choose one of the following methods.

Option 1: Expand the storage capacity

Expand the storage capacity of the instance. For instructions, see Change instance specifications.

After the specification change takes effect, RDS unlocks the instance in approximately 5 minutes.

Option 2: Delete historical or unnecessary data

Connect to the instance using Data Management (DMS). For instructions, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

On the SQL Console tab, run the following query to identify large data files:

SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size'
FROM INFORMATION_SCHEMA.FILES
ORDER BY TOTAL_EXTENTS DESC;

Delete historical or unnecessary data using one of the following statements:
- Drop a table — permanently deletes the table and all its data:
```
DROP TABLE <Database name>.<Table name>;
```
- Truncate a table — deletes all records from the table while keeping the table structure:
```
TRUNCATE TABLE <Database name>.<Table name>;
```

After the deletion is complete, RDS unlocks the instance in approximately 5 minutes.

What's next

After the instance is unlocked, take the following steps to prevent it from being locked again:

Configure automatic storage expansion. When storage usage reaches a configured threshold, RDS automatically expands the storage capacity. For instructions, see Configure automatic storage expansion.
Reclaim fragmented tablespace. If DELETE runs frequently on a table, the table becomes fragmented and the freed space is not released to the OS. During off-peak hours, run OPTIMIZE TABLE to reclaim it:
```
OPTIMIZE TABLE <Database name>.<Table name>;
```
For more information, see How do I use the OPTIMIZE TABLE statement to release the tablespace of an ApsaraDB RDS for MySQL instance?