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.

Lizliza

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

It's worth nothing that you should root cause how MySQL replication got out of sync and fix that problem to prevent this problem.

Conflux

Hello, Dave. I'm aware about different position and filename. What if I'm able to find position of last executed statment on slave (using mysqlbinlog) and appropriate positions on master's binlog. Will it work? – Lizliza

Reading your update to your post, as long as nothing was written to the B server outside of replication from A, that should work. MHA actually works this way, see code.google.com/p/mysql-master-ha/wiki/Architecture. – Conflux

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.

Rhotacism

Hello, What if I B is dead at all (AWS terminated instance) but C is writing binlog too. So can I replay binlogs from master(A) on slave(C) using mysqlbinlog after A and C logs inspection? – Lizliza

yes. You can either CHANGE MASTER on C to use the binlogs on A via MySQL, or you can use the mysqlbinlog tool to read the binlog files and import them onto C. – Rhotacism

So seems that order of transactions is the same across all replication chain. But It would be great too have some documentation about that. We have big DBs (~900GB) and it's really risky to operate without any official confirmation and hard to compare two DB and check integrity of data. – Lizliza

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.

Infinitesimal

Hello, Zafar. The primary question is "Can I replay binlog from one server on another one which is into replication chain?" – Lizliza

As your server B's replication is not working so now your servers are not in replication chain..yes you can replay binlog from A to C as I mentioned in my answer.If you want to continue replication then either you have to correct replication of B so that C can replicate data from B or you have to start replication of C from directly from A, but you have to take decision on server B data. – Infinitesimal

Are you completely sure that order of statements and transactions are the same in all bin/relay logs? Where I can read about this? – Lizliza