BinLog Storage has reached huge proportions and still growing

Hi Simu Dreamproduction,

Greetings!

We apologize for the inconvenience caused.

To manage the growing BinLog storage in your Azure Database for MySQL, Here are some steps and considerations to help you manage and reduce the BinLog storage:

1. BinLog Expiry Configuration: The binlog_expire_logs_seconds parameter specifies the number of seconds that the service waits before deleting the binary log file. By default, this is set to 0, meaning logs are deleted as soon as they are no longer needed. Increasing this value can delay deletion and increase storage usage. https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-server-parameters Ensure that binlog_expire_logs_seconds is set appropriately to balance between data retention for replication and storage usage.
2. Replication and Data Recovery: Binary logs are used for replication and data recovery. If you have multiple replicas, the logs will wait for the slowest replica to read the changes before deletion. https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-server-parameters If you are using read replicas, ensure that they are functioning correctly and not causing delays in log deletion. https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-read-replicas
3. Monitoring and Alerts: Use Azure Monitor to track the storage usage of your BinLogs. Set up alerts to notify you when storage usage exceeds a certain threshold. https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-backup-restore Regularly review the storage metrics and adjust the binlog_expire_logs_seconds parameter as needed to manage storage efficiently. https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-server-parameters
4. Permissions and Access: Ensure you have the necessary permissions to modify server parameters. If you lack permissions, you may need to contact your Azure administrator or support team to make the necessary changes. https://eng.ms/docs/cloud-ai-platform/azure-data/azure-data-azure-databases/mysqlmariadb/azure-database-for-mysql-flexible-server/mysql-flexible-server-tsgs/sop/network-billing-entraid-errorlog-triage

Steps to Reduce BinLog Storage:

• Check Current BinLog Settings: Verify the current value of binlog_expire_logs_seconds and adjust it if necessary. For example, setting it to a lower value can help reduce storage usage.
• Purge Old BinLogs: Manually purge old BinLogs if they are no longer needed. This can be done by setting the binlog_expire_logs_seconds to a lower value temporarily and then reverting it back.
• Review Replication Setup: Ensure that your replication setup is optimized and that there are no delays in log processing by replicas.

Here are links to several parallel threads that may be helpful to you:

https://learn.microsoft.com/en-us/answers/questions/2074307/azure-mysql-flexible-server-reduce-storage-size-by

I hope this information helps. Please do let us know if you have any further queries.

Hello,

Unfortunately we tried all those steps.

binlog_expire_logs_seconds was already set to 0. We tried yesterday to set it to 120 and waited couple of hours, did not help, set it for 1 day to see what happens over night, still the same. Binlog still growing, is now at 82GB out of 100GB.

Restart of the SQL server is also not helping.

Our database has no replication and no read replicas.

Hi @Simu Dreamproduction,

Thank you for the update.

Could you please provide the server details via private message as it will help us conduct a more thorough investigation.

How do I send private messages? :)

Hi @Simu Dreamproduction,

We have already initiated a private message, and you may have received an email notification regarding the same. please check and share the requested details through the private message.