I have a problem with MySQL binary log files. I want to tell you something about my database system:
I have a master-slave replication with two database instances.
Our master database creates 1.7GB of binary logs every 8 minutes. My problem is actually about these files. I'm trying to figure out how long I should keep these files. My disk partition is 80GB for these files.
According to the disk size I mentioned above, the value "expire_logs_days=1" that I used in MySQL became meaningless. Because I cannot keep these files even for 8 hours when there is a problem.
Therefore, I wrote a cronjob that runs every hour and deletes the synchronized binary log files from the master server with the "PURGE BINARY LOGS" command. If the master server cannot reach the slave server, I give it 3 hours, if the problem still persists after 3 hours, I delete the binary log files from the master server.
I have a daily database backup cronjob.
My problem is about the backup and restore procedure of the slave server. When I clean the binary log with the cronjob running every hour on the master server, the backup taken by the slave server during the day becomes unusable. Let's also consider another scenario. For example, if there is a problem in the slave database server, take a backup from the master server and restore this backup on the slave server. Let the restore take about 5 hours. While restoring the slave server, new binary log files are created on the master server. Also, while the slave server is being restored, the master will see that the slave server is not working properly after 3 hours and will clear the binary log files. After the restore process is completed on the slave server, the replication will not work properly and error 1236 will be received. Because the binary log file specified in the backup taken by the slave will no longer exist on the master server.
What should we do to get out of this cycle?