GCP SQL is not erasing bin logs ??

[Kasandra Padisha]

Hi

It is known that binary logs are erased after 7 days retension and you can not change this behaviour.

But one month ago, my storage is growing steadily ...

$  SHOW BINARY LOGS ;

It shows that i have 4376 files .. it is far beyong what I beleive it should.

I tryied to erase them, to change the expire_logs_days that i found in 0, but the platform does not allow to do that.

And my storage still growing

And my database is not in mainteniance mode and general logs are off

Thanks a lot

[Sam (Google Cloud Support)]

Binary logging is used for some operations such as clone and replica creation [1][2], and it is useful for recovering an instance to a specific point-in-time [3]. For First Generation instances, the space used by binary logs counts against the total storage used by the instance. For Second Generation instances, binary logs are charged at the regular storage rate. And when you disable binary logging, all the existing binary logs are deleted [4].

As binary logs use storage space and are automatically deleted with their associated automatic backup [5]. You should check to see if you have setup *automatic backup* or *on-demand backup* by Navigating to your Cloud SQL instance from your GCP Cloud Console to view information about it [6]. From there you can *edit* the instance settings [7][8].

Do you have a First Generation or Second Generation Instance? The most recent 7 automated backups, and *all on-demand backups, are retained* for Second Generation instances [9]. So if you have on-demand backup configured instead of automated backups then this could be the reason why you are seeing binary logs beyond 7 days. If the size of the binary logs is an issue to your instance, then you can delete the binary logs by disabling and then reenabling binary logging [4].

Note that you cannot manually delete binary logs, nor change the 7-day time period on automated backups. So try disabling binary logging if you want to delete existing binary logs.

To change the ‘expire_logs_days’ you need to have configured an external replica on a Cloud SQL 2nd Generation instance [10], and to purge those binary logs you need to have Super privileges. There is an internal feature request being evaluated to allow manual purging on binary logs. If you'd like to show your support for this you can do so in our Public Issue Tracker tool [11]. This carries the benefit of allowing you to interact with the engineering team directly regarding this functionality being implemented and also allows other users who would like to see this to show their support by 'starring' the issue.

[1] https://cloud.google.com/sql/docs/mysql/backup-recovery/backups#what_backups_provide

[2] https://cloud.google.com/sql/docs/mysql/replication/tips#bin-log-impact

[3] https://cloud.google.com/sql/docs/mysql/backup-recovery/restoring#pitr

[4] https://cloud.google.com/sql/docs/mysql/backup-recovery/restore#about_enabling_binary_logging

[5] https://cloud.google.com/sql/docs/mysql/instance-info#available_metrics

[6] https://cloud.google.com/sql/docs/mysql/instance-info#info

[7] https://cloud.google.com/sql/docs/mysql/edit-instance

[8] https://cloud.google.com/sql/docs/mysql/instance-settings

[9] https://cloud.google.com/sql/faq#backup-and-recovery

[10] https://cloud.google.com/sql/docs/mysql/replication/configure-external-replica#configuring_the_external_replica

[11] https://issuetracker.google.com/35904375

[Sam (Google Cloud Support)]

Additionally, there is another thread that provides insight on how manual backups deletion impact binary log deletion [1].

Hope all these help.

[1] https://groups.google.com/forum/#!topic/google-cloud-sql-discuss/KKwSpmQ85-k

[Kasandra Padisha]

Thanks a lot .. super helpfull