In MySQL master-slave could it happen that the oldest log bin data gets deleted before the slave has read it?

Question

I have a MySQL database with a large number of individual tables having the same definitions. The number of tables is about 2000-3000 and their total size is about 50 GB. Of course they have different content. I want to put them in one common table and get rid of them. To do that I will use the script like this (some variables are redacted!)

#!/bin/bash
pattern='individual_table_(.+)'
sudo mysql -ss --execute "SHOW TABLES;" -A database_name | while read -r line
do
        if [[ $line =~ $pattern ]];
        then echo "Inserting $line for object with id ${BASH_REMATCH[1]}";
    sudo mysql -e "insert into common_table (timestamp, objectID, data)  select timestamp, objectID, data from $line;" -A database_name 
    fi
done

echo "Script finished!"

So far so good. The database uses a master-slave strategy. I'll apply that script to the master. Once I start the script it will operates continuously several hours while serving another querries. Normaly the bin log gets updated and it's data grows. The log must be deleted from time to time otherwise the host's machine storage would get overfilled. In other words it acts as a "last in - first out" buffer. You can configure in what conditions MySQL to clear the old data. It's an excerpt from the master's my.cnf file.

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 30
max_binlog_size         = 4000M

So my question is at some point in time after I've started the script, could it happen that the oldest log bin data gets deleted before the slave has read it? Could it lead to a data and synchronization loss between master and slave and how to avoid it? I could put a sleep line between each insert in my script but that will make its executions slower. Is there any other solution?

Answer 1

It's unlikely for a replica to fail to at least download the binary log. As long as the replica is actually running and replication is started, the I/O thread downloads events very quickly. It might lag in applying those events on the replica, but the files are present on the replica server.

If the replica is downloading over a slow network, or if the replica is down for hours or days, this can lead to it missing some binary logs and failing to download them before they expire.

So to answer your question directly: If the replica does fail to download some part of a binlog file before that file is expired, then the replica can never catch up. Your best action is to trash the datadir of that replica, and reinitialize it using a new full backup from the source instance.

At my last job, we did this frequently. We had scripts to do the whole sequence of steps. Later we developed a microservice in Go to do it. We could just issue a command like "fix cluster 12345" (paraphrasing), and the Go service would figure out which replica was broken, make a fresh backup from the source instance in the cluster, move the backup files to the broken replica server, restore it, and configure replication and start it.

Is it possible to re-synchronize a replica with the data it missed, without doing a full re-init? In theory, yes. You could use a tool like pt-table-sync to try it. But you are still left wondering "what binary log coordinates should I start replication from?" It becomes a race condition, trying to perform a sync, then read the source binlog position, then use that to configure replication. How do you know you've sync all changes up to that binlog position and no others?

Trust me, it's simpler to just reinitialize the replica fully. Then you know you have copied all the data and maintained consistency up to a precise binlog position. You know exactly where to start replication from.

Really, the best solution for your case to get larger storage, so you don't have to shuffle the deck chairs (i.e. the binary log) so much.

Moving 50G of data around shouldn't be such a struggle.