Possible to get equivalent MySQL bin log position on another server?

Question

Let's say we have three MySQL servers, mysql1, mysql2 and mysql3. Replication is set up such that mysql3 is the slave of mysql2, and mysql2 is the slave of mysql1.

At some point I take a dump of mysql2 and record the binlog file and position of it by running show master status;. Is there a way to find the equivalent binlog file and position on mysql1 - for example so that I could load up the dump and continue replicating from mysql1 instead of mysql2?

Answer 1

I managed to do this by examining the log files using mysqlbinlog.

Given the following master log position on mysql2:

Master log file = mysql-bin.003151
Master log pos  = 46678888

I opened the log file:

mysql2> mysqlbinlog mysql-bin.003151 | less

And searched for the position (46678888).

# at 46678861
#170301 12:15:33 server id 2  end_log_pos 46678888
  Xid = 12652204449 COMMIT/*!*/;
# at 46678888
#170301 12:15:36 server id 2  end_log_pos 46678967
  Query   thread_id=414919 69      exec_time=1     error_code=0
  SET TIMESTAMP=1488370536/*!*/; BEGIN /*!*/;
# at 46678967

The nearby line of SET TIMESTAMP=1488370536 looked like a good candidate to search for in the corresponding log on mysql1. So I opened up the master log file on mysql1 (there were multiple to choose from, but I went with the one with the closest timestamp to that on the slave, you might have to try a few files):

mysql1> mysqlbinlog mysql-bin.002966 | less

And searched for SET TIMESTAMP=1488370536. This is what I found:

# at 46236234
#170301 12:15:33 server id 2  end_log_pos 46236261
  Xid = 88972150 COMMIT/*!*/;
# at 46236261
#170301 12:15:36 server id 2  end_log_pos 46236325
  Query   thread_id=414919    69      exec_time=1     error_code=0
  SET TIMESTAMP=1488370536/*!*/; BEGIN /*!*/;
# at 46236325

I checked surrounding lines to ensure I'd got the right place and then used the corresponding end_log_pos (in this case 46236261) as the new log position. Therefore my master log position on mysql1 is:

Master log file = mysql-bin.002966
Master log pos  = 46236261

It's probably possible to automate this process somewhat, but to be honest it was very quick and easy to do it manually (and I don't expect to have to do again much if at all) - it's taken longer to write up the process than actually do it.

Answer 2

I think what you're looking for is the --dump-slave option on mysqldump:

--dump-slave[=#] This causes the binary log position and filename of the master to be appended to the dumped data output. Setting the value to 1, will printit as a CHANGE MASTER command in the dumped data output; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump - don't forget to read about --single-transaction below). In all cases any action on logs will happen at the exact moment of the dump.Option automatically turns --lock-tables off.

So to clone a slave with mysqldump, you'd do something like this:

mysqldump --all-databases --dump-slave --flush-logs > somedumpfile.sql

Then load somedumpfile.sql on the new slave.