Bug #102046 Allow purging of binary logs through sql commands when log_bin is set to 0

• Submitted: 22 Dec 2020 1:06
• Modified: 22 Dec 2020 6:20
• Reporter: Pranay Motupalli
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S4 (Feature request)
• Version: 5.7, 8.0
• OS: Any
• CPU Architecture: Any

[22 Dec 2020 1:06] Pranay Motupalli

Description:
This is feature request to allow purging of existing binary logs when log_bin is set to 0.

The only way currently available is by manually deleting them at the filesystem level.

How to repeat:
When binlog enabled, I have 18 binary logs:

[root@ip-172-31-64-197 mysql]# ls -lhtr mysqlbin*
-rw-r----- 1 mysql mysql  724 Jun 18  2020 mysqlbinlog.000001
-rw-r----- 1 mysql mysql 1.3K Jun 18  2020 mysqlbinlog.000002
-rw-r----- 1 mysql mysql  383 Jun 18  2020 mysqlbinlog.000003
-rw-r----- 1 mysql mysql  194 Jun 18  2020 mysqlbinlog.000004
-rw-r----- 1 mysql mysql  217 Jun 18  2020 mysqlbinlog.000005
-rw-r----- 1 mysql mysql  217 Jun 18  2020 mysqlbinlog.000006
-rw-r----- 1 mysql mysql 1.5K Jun 19  2020 mysqlbinlog.000007
-rw-r----- 1 mysql mysql  606 Jun 19  2020 mysqlbinlog.000008
-rw-r----- 1 mysql mysql 1.9K Jun 20  2020 mysqlbinlog.000009
-rw-r----- 1 mysql mysql  52M Jun 23 08:16 mysqlbinlog.000010
-rw-r----- 1 mysql mysql 4.1K Jul 22 15:43 mysqlbinlog.000011
-rw-r----- 1 mysql mysql 1.6K Jul 24 07:34 mysqlbinlog.000012
-rw-r----- 1 mysql mysql 1.1K Jul 24 07:42 mysqlbinlog.000013
-rw-r----- 1 mysql mysql  217 Jul 24 08:03 mysqlbinlog.000014
-rw-r----- 1 mysql mysql  37M Aug 14 01:27 mysqlbinlog.000015
-rw-r----- 1 mysql mysql  217 Nov 10 03:03 mysqlbinlog.000016
-rw-r----- 1 mysql mysql  34M Dec  6 04:11 mysqlbinlog.000017
-rw-r----- 1 mysql mysql  378 Dec  6 04:11 mysqlbinlog.index
-rw-r----- 1 mysql mysql 1.3K Dec 21 07:39 mysqlbinlog.000018

--- Disabled Binary logs and restarted mysqld service -----

mysql> select @@version,@@log_bin;
+-----------+-----------+
| @@version | @@log_bin |
+-----------+-----------+
| 5.7.23    |         0 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

mysql> PURGE BINARY LOGS BEFORE '2020-12-21 01:03:25';
Query OK, 0 rows affected (0.00 sec)

mysql> PURGE BINARY LOGS TO 'mysqlbinlog.000005';
Query OK, 0 rows affected (0.00 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.00 sec)

=== Checking at File System level ====

[root@ip-172-31-64-197 mysql]# ls -lhtr mysqlbin*
-rw-r----- 1 mysql mysql  724 Jun 18  2020 mysqlbinlog.000001
-rw-r----- 1 mysql mysql 1.3K Jun 18  2020 mysqlbinlog.000002
-rw-r----- 1 mysql mysql  383 Jun 18  2020 mysqlbinlog.000003
-rw-r----- 1 mysql mysql  194 Jun 18  2020 mysqlbinlog.000004
-rw-r----- 1 mysql mysql  217 Jun 18  2020 mysqlbinlog.000005
-rw-r----- 1 mysql mysql  217 Jun 18  2020 mysqlbinlog.000006
-rw-r----- 1 mysql mysql 1.5K Jun 19  2020 mysqlbinlog.000007
-rw-r----- 1 mysql mysql  606 Jun 19  2020 mysqlbinlog.000008
-rw-r----- 1 mysql mysql 1.9K Jun 20  2020 mysqlbinlog.000009
-rw-r----- 1 mysql mysql  52M Jun 23 08:16 mysqlbinlog.000010
-rw-r----- 1 mysql mysql 4.1K Jul 22 15:43 mysqlbinlog.000011
-rw-r----- 1 mysql mysql 1.6K Jul 24 07:34 mysqlbinlog.000012
-rw-r----- 1 mysql mysql 1.1K Jul 24 07:42 mysqlbinlog.000013
-rw-r----- 1 mysql mysql  217 Jul 24 08:03 mysqlbinlog.000014
-rw-r----- 1 mysql mysql  37M Aug 14 01:27 mysqlbinlog.000015
-rw-r----- 1 mysql mysql  217 Nov 10 03:03 mysqlbinlog.000016
-rw-r----- 1 mysql mysql  34M Dec  6 04:11 mysqlbinlog.000017
-rw-r----- 1 mysql mysql  378 Dec  6 04:11 mysqlbinlog.index
-rw-r----- 1 mysql mysql 1.3K Dec 21 07:39 mysqlbinlog.000018

[22 Dec 2020 6:20] MySQL Verification Team

Hello Pranay,

Thank you for the feature request!

regards,
Umesh