Can I use mysql binlog from master as relay log on slave?

Question

I have following Mysql replication schema:

A(master)->B(slave/master)->C(slave)

• A writes binlog
• B reads A's binlog applies relaylog and writes it's own binlog
• C reads from B and applies.

If replication become broken by some reason (A->B) can I copy A's binlog, find which position is corresponded to B last executed statement and replay it. Is order of transactions/statements in bin/relay logs the same in all replication chain? (Replication uses one thread so it might be the same order.)

Update: I should have asked like: "Is the order of statements/transactions in binlogs the same across all replication chain? Can we replay any log on any host and repoint any slave(c) to master (A)" Seems that the answer is: "Yes". But official confirmation or documentation(source code) link hasn't been posted yet.

UPDATE2: from official docs to innodb_support_xa:

Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you disable innodb_support_xa, transactions can be written to the binary log in a different order than the live database is committing them, which can produce different data when the binary log is replayed in disaster recovery or on a replication slave.

Answer 1

To directly answer your question, no.

Your Topology:

(a) master -> (b) replica -> (c) replica

• A = Master, bin-log must be enabled
• B = Replica of A, must have log_slave_updates enabled
• C = Replica of B

Each bin-log for each server will have its own bin-log file name and position, you cannot copy bin-logs between servers.

If you are wishing to manage your replication topology, moving slaves around and failover you should look at using one of the follow:

1. MySQL 5.6 with GTID
2. MHA
3. Orchestrator

UPDATE:

Its worth nothing that you should root cause how mysql replication got out of sync and fix that problem to prevent this problem.

Answer 2

To clarify your question. If replication stops between A -> B and perhaps is irrepairable. Is it possible to replicate from A -> C instead. The answer is yes.

In your example both A & B are writing to binlog. The order of statements in these logs will be the same, although I can't find the documentation to prove it, it's an underlying principle of replication. If the order was different then it would be possible for data to get out of sync pretty easily. And you are right, the replication slave thread is single threaded so host B would be reading and writing statements in order.

However if some data was written to host 'B' directly, then of course B & C would have different data to 'A' depending on what was written.

Before making changes, ensure you have backed up your servers. Run 'SHOW SLAVE STATUS' on B & C and copy/paste the output somewhere as a reference as well.

To make 'C' replicate from 'A', you need to find the position on the binlogs from 'A' which correspond to where 'C' is currently looking at 'B'. There are a few ways to do this, including using the mysqlbinlog tool to manually find the queries and start from that point.

A quicker way is to let 'C' catch up 100% with 'B'. Assuming replication is already stopped on 'B'. Use 'SHOW SLAVE STATUS' on B to get the parameters for the following query to run on 'C'.

 CHANGE MASTER TO MASTER_HOST = '[Master_Host]',  MASTER_LOG_FILE='[Master_Log_File]',  '[Exec_Master_Log_Pos]';

You may need to add other options:

 MASTER_USER='__USER__', MASTER_PASS='__PASS__', 

This will tell host 'C' to continue it's replication starting from where 'B' got to. If you are paranoid like a good dbadmin then you would use mysqlbinlog to check the binlogs on host 'A' and confirm the queries/timestamps at the new positions for 'C' and compare the queries around that point with the data on 'C' to confirm this is the point to restart replication. Something like:

mysqlbinlog  --read-from-remote-server --host=HostA --user.. --password=.. --start-position=[Exec_Master_Log_Pos - 100] --stop-position=[Exec_Master_Log_Pos + 100] Master_Log_File

The good news about mysqlbinlog is that it will also let you read a copy of the binlog from another server and transform it into SQL statements which you can replay locally. This is very useful in disaster recovery scenarios.

Answer 3

In normal scenario if you your show slave status is showing the pointer at which your replication (A > B) was broken then you should correct it, in this way your slave B will be fine and now data will be replicated to Slave C also successfully.

If due to any specific reason you don't want to use Slave B and you are sure that that before Slave B replication broken all data from B has been replicated to C and you know the pointer where replication was broken then you can execute binlogs directly on slave C and now you can make slave C slave of Master A instead of B.

If the issue is something different then please elaborate.