Is it safe to delete mysql-bin files?
I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these
mysql-bin
files inside/var/db/mysql/
There are hundreds of those files likemysql-bin.000123
,mysql-bin.000223
etc. I have checked the mysql replication by doingshow master status
andshow slave status
they are using some mysql-bin files at certain positions, but I guess all the other bin files are leftovers which will not be used anymore. In this case is it safe to delete all those mysql-bin files except the ones that replication is currently pointing at ?If it is safe to delete, then is there anything I could do to automatically delete those files once they are not in use ?
Please do not just delete them in the OS.
You need to let mysqld do that for you. Here is how mysqld manages it:
The file
mysql-bin.[index]
keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction withmysql-bin.[index]
are:PURGE BINARY LOGS TO 'binlogname'; PURGE BINARY LOGS BEFORE 'datetimestamp';
These will clear all binary logs before the binlog or timestamp you just specified.
For example, if you run
PURGE BINARY LOGS TO 'mysql-bin.000223';
this will erase all binary logs before
mysql-bin.000223
.If you run
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
this will erase all binary logs before midnight 3 days ago.
If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:
mysql> SET GLOBAL expire_logs_days = 3;
then add this to
/etc/my.cnf
[mysqld] expire_logs_days=3
and mysqld will delete them logs for you
SHOW SLAVE STATUS\G
This is critical. When you run
SHOW SLAVE STATUS\G
, you will see two binary logs from the Master:Master_Log_File
Relay_Master_Log_File
When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever
Relay_Master_Log_File
is, and go back to the Master and runPURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';
That way, replication is not interrupted.
Please note a typo - underscores, not dashes: `[mysqld] expire_logs_days=3` (and you must include the `[mysqld]` section
@changokun That's not a typo. my.cnf will accept dashes. Running `SET GLOBAL expire_logs_days = 3;` from the mysql client will not accept them. Example in the MySQL Docs : http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html
This works for me. Question, what is the difference between... `mysql> SET GLOBAL expire_logs_days = 3;` and `expire-logs-days=3` in `/etc/my.cnf`.. Are they the same? Is this redundant or not? Or, it is important to run `SET GLOBAL...` then add `expire-logs-days=..`? Thanks.
Quickly remove all logs, obviously: ```PURGE BINARY LOGS BEFORE DATE(NOW());``` why are there no sane defaults for this ? I have nowhere, never explicity changed log file size to some gigantic amount. I had a 10.0 GB of log files, after running this command my mysql.bin folder size shrank to 1.6GB.
This really depends on your backup strategy. One of the main reasons to keep the binary logs around is to restore your database to a 'point-in-time'. If your database crashes and requires restoration, you would restore the latest full backup, and then play back the binary logs starting with the position of the full backup.
So, if you do a full backup every day and you have 7 days worth of binary logs, it is likely that you can delete the past 4-6 days worth of binary logs. You can control how many days worth of binary logs are kept with the
expire_logs_days
setting.You can delete the binary logs you don't need by first seeing what is the oldest log you want to keep:
ls -lh /path/to/binary/logs/mysql-bin.0*
and then in mysql:
mysql> PURGE BINARY LOGS TO 'mysql-bin.XXXXX';
Try this:
RESET MASTER;
as the document said:
RESET MASTER enables you to delete any binary log files and their related binary log index file, returning the master to its state before binary logging was started.
This will delete all the related binary log files, which may not what you want.
I was searching for same thing... Thank you!
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
changokun 6 years ago
Please note a typo - underscores, not dashes: `[mysqld] expire_logs_days=3` (and you must include the `[mysqld]` section