In this post we will discus about the different ways we can purge binary logs safely in MySQL, We recommend you to confirm before purging the binary logs from the master, all logs were applied to the slaves to avoid halting them. The following error is usual when binary log is purged before being applied on slave:<\/p>\n

Last_IO_Errno: 1236\r\nLast_IO_Error: Got fatal error 1236 from master when reading data from binary log: \u2018Could not open log file\u2019<\/pre>\n
How can we safely purge MySQL binary log files ?\u00a0<\/strong><\/p>\n
\n
On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.<\/li>\n
Get the binary log files details on the master with SHOW BINARY LOGS.<\/li>\n
Check for the earliest log file among all the slaves, This is the target file. If all the slaves are up to date, this is the last log file on the list.<\/li>\n
Make a backup of all log files you are about to delete (We insist this step to our customers to avoid human errors)<\/li>\n
Purge all log file up to but please do not include the target file.<\/li>\n<\/ol>\n
The default binary log expiration is 30 days, You can get this details from MySQL system variable binlog_expire_logs_seconds<\/em>\u00a0. When using MySQL replication, You should specify an expiration period that no lower than maximum time your slaves might lag behind the master.<\/p>\n
mysql> select @@binlog_expire_logs_seconds;\r\n+------------------------------+\r\n| @@binlog_expire_logs_seconds |\r\n+------------------------------+\r\n|                      2592000 |\r\n+------------------------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n
List binary log files \u00a0in MySQL<\/strong><\/p>\n
mysql> SHOW BINARY LOGS;\r\n+---------------+-----------+\r\n| Log_name      | File_size |\r\n+---------------+-----------+\r\n| binlog.000001 |       657 |\r\n| binlog.000002 |       178 |\r\n| binlog.000003 |       178 |\r\n| binlog.000004 |       178 |\r\n| binlog.000005 |       178 |\r\n| binlog.000006 |      3354 |\r\n| binlog.000007 |       199 |\r\n| binlog.000008 |       155 |\r\n+---------------+-----------+\r\n8 rows in set (0.00 sec)\r\n\r\nmysql> \r\n<\/pre>\n
** Both “SHOW BINARY LOGS” and “SHOW MASTER LOGS” are synonyms. **<\/p>\n
Purging MySQL binary log files \u00a0using PURGE BINARY LOGS command\u00a0<\/strong><\/p>\n
The PURGE BINARY LOGS command deletes all the binary log files listed in the log index file prior to the specified log file name or date .<\/p>\n
mysql> PURGE BINARY LOGS TO 'binlog.000001';\r\nQuery OK, 0 rows affected (0.01 sec)\r\n<\/pre>\n
mysql> PURGE BINARY LOGS BEFORE '2018-05-11 20:20:20';\r\nQuery OK, 0 rows affected, 1 warning (0.01 sec)\r\n\r\n<\/pre>\n
** ABOVE STATEMENTS HAS NO EFFECT IF THE MYSQL\u00a0<\/strong>SERVER WAS NOT STARTED WITH –log-bin OPTION ENABLED<\/p>\n
What you should be careful about before purging MySQL binary log files ?\u00a0<\/strong><\/span><\/p>\n
There are several ways to purge MySQL binary log files but none guarantee slave has already applied the binary log transactions and can be safely removed (Now, that’s the reason we have the title for this topic in red<\/span><\/strong> color ) , This is when “mysqlbinlogpurge<\/strong>” tool comes really helpful. The\u00a0“mysqlbinlogpurge<\/strong>” tool is a part of MySQL utilities, You can download it from here<\/a><\/span>\u00a0. This tools ensures that any binary log files that in use or required by any of the slave in replication ecosystem will not be deleted.<\/p>\n
How\u00a0“mysqlbinlogpurge<\/strong>” finds when it is safe to purge binary log files ?<\/p>\n
A MySQL slave has two entities to complete the replication successfully: the Slave_IO <\/strong>thread is accountable for collecting the events successfully from the master, While Slave_SQL <\/strong>threads(s) is responsible for executing the events locally. To monitor Slave IO \/ Slave SQL is running successfully and where they are at in their process, you have to run the command ” SHOW SLAVE STATUS<\/strong> ”<\/p>\n
mysql > show slave statusG\r\n*************************** 1. row ***************************\r\nSlave_IO_State: Waiting for master to send event\r\nMaster_Host: 192.168.56.3\r\nMaster_User: India\r\nMaster_Port: 3306\r\nConnect_Retry: 60\r\nMaster_Log_File: mysql-bin.000047\r\nRead_Master_Log_Pos: 374\r\nRelay_Log_File: mysql-relay.000713\r\nRelay_Log_Pos: 45793012\r\nRelay_Master_Log_File: mysql-bin.000038\r\n<strong>Slave_IO_Running: Yes\r\nSlave_SQL_Running: Yes\r\n...\r\n<\/strong>Exec_Master_Log_Pos: 45792781<\/pre>\n
The following are important matrices \/ parameters and their descriptions:<\/p>\n
Master_Log_File<\/strong> \/ Read_Master_Log_Pos<\/strong><\/em>\u00a0–\u00a0This is what Slave_IO is currently fetching from the master.<\/p>\n
Relay_Master_Log_File<\/strong> \/ Exec_Master_Log_Pos<\/strong><\/em> – This is what Slave_SQL thread is currently executing in terms of the Masters coordinates (master’s log file).<\/p>\n
Relay_Log_File<\/strong> \/ Relay_Log_Pos<\/strong><\/em> – This is what the SQL thread is currently executing in terms of the Slave’s coordinates (relay log file)<\/p>\n
The\u00a0Master_Log_File\u00a0<\/strong><\/em>is the latest binlog file on MySQL master that Slave_IO\u00a0<\/strong>reads from and it is this file, and any files after this on the Master server, that we must preserve for successful completion of replication. The\u00a0Relay_Log_File\u00a0<\/strong><\/em>is the point of execution in MySQL Master’s binlog that Slave_SQL thread has successfully executed.<\/p>\n
How to use “<\/strong>mysqlbinlogpurge<\/em>” for purging MySQL binary log files ?\u00a0<\/strong><\/p>\n
We recommend our customers to use –dry-run option with\u00a0“<\/strong>mysqlbinlogpurge<\/em>”\u00a0<\/strong>to check what tool is going to deliver :<\/p>\n
** if ever slave is stopped,\u00a0“mysqlbinlogpurge<\/em>” tool will report error and stop purging binary log files:<\/p>\n
$ mysqlbinlogpurge --master=root:india@192.168.56.3:3306 \r\n>           --slaves=root:singapore@192.168.56.4:3306,root:srilanka@192.168.56.5:3306 \r\n>           --dry-run\r\nERROR: Can not verify the status for slave singapore@192.168.56.4:3306. Make sure the slave are active and accessible.\r\n<\/pre>\n
You can check what the “mysqlbinlogpurge”<\/em>\u00a0is going to perform before executing, you can use the\u00a0–dry-run\u00a0option:<\/p>\n
$ mysqlbinlogpurge --master=root:root@192.168.56.3:3306 --slaves=root:root@192.168.56.4:3306,root:root@192.168.56.5:45009 --dry-run\r\n# Latest binlog file replicated by all slaves: mysql-bin.000481\r\n# To manually purge purge the binary logs Execute the following query:\r\nPURGE BINARY LOGS TO 'mysql-bin.000482'<\/pre>\n
** To remove binary log file, you just need to remove the –dry-run option.<\/p>\n
mysqlbinlogpurge\u00a0<\/em>can occasionally go wrong under following circumstances :<\/p>\n
\n
Not reliable when used in Multi-Source replication ecosystem.<\/li>\n
If slave corrupts the local relay log, We need to restart replication from Relay_Master_log_file, and\u00a0mysqlbinlogpurge\u00a0<\/em>might already have executed the purge of required binlog .<\/li>\n<\/ul>\n
Summary\u00a0<\/strong><\/p>\n
We caution our customers who regularly purge their binary log files on being reasonably certain that relay logs won’t be corrupted . We don’t recommend\u00a0mysqlbinlogpurge\u00a0<\/em>multi-source replication ecosystem.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this post we will discus about the different ways we can purge binary logs safely in MySQL, We recommend you to confirm before purging the binary logs from the master, all logs were applied […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,15],"tags":[5,6,16],"yoast_head":"\n