{"id":1388,"date":"2014-12-05T06:25:16","date_gmt":"2014-12-05T06:25:16","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=1388"},"modified":"2014-12-05T06:25:16","modified_gmt":"2014-12-05T06:25:16","slug":"delete-mysql-bin-files-safely","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2014\/12\/05\/delete-mysql-bin-files-safely\/","title":{"rendered":"Delete mysql-bin files safely"},"content":{"rendered":"

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 with mysql-bin.index are:<\/p>\n

PURGE BINARY LOGS TO ‘binlogname’;
\nPURGE BINARY LOGS BEFORE ‘datetimestamp’;<\/p>\n

These will clear all binary logs before the binlog or timestamp you just specified.<\/p>\n

For example, if you login to mysql run<\/p>\n

\n\nmysql> PURGE BINARY LOGS TO 'mysql-bin.000223';\n\n<\/pre>\n
this will erase all binary logs before ‘mysql-bin.000223’.<\/p>\n
If you run<\/p>\n
\n\nmysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;\n\n<\/pre>\n
this will erase all binary logs before midnight 3 days ago.<\/p>\n
If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:<\/p>\n
\n\nmysql> SET GLOBAL expire_logs_days = 3;\n\n<\/pre>\n
then add this to \/etc\/my.cnf<\/p>\n
\n\n[mysqld]\nexpire-logs-days=3\n\n<\/pre>\n
and mysqld will delete them logs for you
\nSHOW SLAVE STATUS\\G<\/p>\n
This is critical. When you run SHOW SLAVE STATUS\\G, you will see two binary logs from the Master:<\/p>\n
    Master_Log_File
\n    Relay_Master_Log_File<\/p>\n
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 run<\/p>\n
\n\nPURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';\n\n<\/pre>\n
To clean up Binary Log on Master Server<\/p>\n
\n\nshell> mysql -u username -p\nmysql> RESET MASTER;\n\n<\/pre>\n
To clean up Binary Log on Slave Server<\/p>\n
\n\nmysql -u username -p\nmysql> RESET SLAVE;\n\n<\/pre>\n
Remove MySQL Binary Log with PURGE BINARY LOGS Statement
\nPURGE BINARY LOGS statement can remove Binary Log based on date or up to a Binary Log sequence number
\nBase on the binary logs example shown above, I would like to remove binary up to mysql-bin.000015<\/p>\n
\n\nshell> mysql -u username -p\nmysql>PURGE BINARY LOGS TO 'mysql-bin.000015';\n\n<\/pre>\n
Alternatively, you can remove the binary older than a specific date.<\/p>\n
\n\nshell> mysql -u username -p\nmysql> PURGE BINARY LOGS BEFORE '2009-11-01 00:00:00';\n\n<\/pre>\n
Remove MySQL Binary Log with mysqladmin flush-logs Command
\nAnother method is running mysqladmin flush-logs command, it will remove binary logs more than 3 days old.<\/p>\n
\n\nshell> mysqladmin -u username -p flush-logs\n\n<\/pre>\n
Keep MySQL Binary Log for X Days<\/p>\n
All of the methods above required monitoring on disk usage, to \u201crotate\u201d and keep the binary logs for x number of day. The option below can be configured on MySQL\u2019s config file, my.cnf<\/p>\n
\n\nexpire_logs_days = 7\n\n<\/pre>\n
Consider turning off MySQL Binary Log if MySQL Replication is not deploy on the database server and recovery is not the main concern.<\/p>\n
\n\nnano \/etc\/my.cnf\n# binary logging - not required for slaves, but recommended\n#log-bin=mysql-bin\n\n\n<\/pre>\n
If you drive is 100% full <\/p>\n
Mysql error log shows – <\/p>\n
\n\nERROR 3 (HY000): Error writing file '.\/mysql-bin.~rec~' (Errcode: 28)\n\n<\/pre>\n
and the commands do not work, try this:<\/p>\n
Stop mysql or kill the process
\nZero out the first log file
\nremove the line for the first binary file in mysql-bin.index
\nrestart mysql
\nThen run the commands to purge<\/p>\n","protected":false},"excerpt":{"rendered":"
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 with mysql-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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70],"tags":[],"class_list":["post-1388","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/1388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=1388"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/1388\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=1388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=1388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=1388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}