Azure Mysql flexible server - reduce storage size by purging binlog via binlog_expire_seconds

Question

I have set the server parameter binlog_expire_seconds to 30 in Azure mysql flexible server. I was trying to signal the server to purge the binlog in order to reduce the storage size, but the storage size didn't change at all. Any ideas?

Answer 1

Hi Avan Tan,

Thanks for reaching out to Microsoft Q&A.

In Azure MySQL Flexible Server, setting the binlog_expire_seconds to a lower value (like 30 secs) ensures that binary logs older than 30 seconds will be automatically purged, but it doesn't trigger an immediate storage reduction. Here's why:

Binlog Purging Process: The binlog_expire_seconds parameter only controls the automatic expiration of binary logs. It doesn't instantly delete them but will expire and purge logs as new binary logs are generated. If your server is not actively generating new logs, the old logs will not be purged until new logs are created.

You can manually force a purge by issuing a command:

PURGE BINARY LOGS BEFORE NOW();

Storage Size Reporting Delay: Even after purging the binary logs, it may take some time for Azure's storage reporting to reflect the change. Storage metrics might not be updated immediately, and there could be a lag of several hours.

Other Factors Contributing to Storage: Check if other factors are contributing to the storage size, such as general database size growth, temporary tables, or logs not being removed due to replication or other server configurations.

Restarting the Server: In some cases, restarting the MySQL server can help trigger the purging and refresh the storage reporting.

If the storage size still doesn't change after these steps, consider contacting Azure support for a deeper investigation into whether there’s a delay in reflecting the storage usage or any other underlying issue with your MySQL Flexible Server.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.