Mysql: slave read binlog from local storage

MySQLreplication

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.

Best Answer

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)

Related Question