0

I am running master-slave replication, Can I manually move binlog files from the master server to the slave and make the slave read them locally?

I am willing to do so because I am running out of space on the master storage. and the number of binlog files is huge.

1

You don't need all binary logs from the master. Just the ones that your slave hasn't read yet.

On your slave you can do show slave status\G. Look for the lines Master_Log_File and Relay_Master_Log_File. The first is the binary log file, that your master is using, the second is the log file that your slave is currently reading.

You can then delete all binary logs on the master, that the slave has already read. For example, Relay_Master_Log_File is mysql-bin.011096, then you could execute the following statement on the master:

purge binary logs to 'mysql-bin.011096';

This is described in more detail here.

You can also configure your master to hold less binary logs by configuring the expire_logs_days variable. Read more about it here.

I assume this already solves your storage problems.

UPDATE based on comment: You can simply copy the binary logs to the slave. Remember the position you already have from the show slave status output. Stop the slave. Then execute the binary logs on the slave with

# mysqlbinlog binlog-file1 binlog-file2 --start-position=x | mysql 

Note that the --start-position parameter is only applied to the first binlog file you specify as parameter. Make sure to specify the right one.

Then read the last position from the last binary log with

# mysqlbinlog binlog-file99 | tail 

and change the coordinates with

mysql> change master to master_log_file='...', ...

to the appropriate position and start the slave again.

Read more about this here (I guess)

  • I surely know that I can delete files already read by slave, the problem is the difference between them both is huge, and the number of unread files as well. here the slave needs days to catch up the master and be on sync. – Eng7 Oct 22 at 14:36
  • Updated my answer. – tombom Oct 22 at 15:14
1

The Slave purges old log files on its own. No problem.

The Master does not have a perfect way to get rid of old binlogs.

The Master can't remove an old binlog until all the slaves have copied it to their relay logs. But the Master neither has a list of Slaves, nor does it know where each is in the copying process.

expire_logs_days works pretty well, but if a slave is 8 days behind, yet the setting is 7, you are hosed.

I don't think the binlog and the relaylog have identical formats. They certainly cannot be assumed to be in sync -- That is, the first item in a relay log does not necessarily match the first entry in some binlog.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.