1

I want to take the current binlog file and binlog position of the master server in a mysqldump on the slave server. I cannot use master-data=2 on slave - how can I do this nevertheless?

1
  • Welcome on the DBA SE! Pro tip: if you have a problem, don't forget any of this 3 key components: 1) what you want 2) what you tried 3) why it doesn't work. Good luck on the site!
    – peterh
    Aug 28, 2018 at 20:53

1 Answer 1

2

Surprise, you can get the Master's binlog file/position from the dump !!! Use --dump-slave.

MySQL 5.6 Documentation says :

This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master. These are the master server coordinates from which the slave should start replicating.

Just get the CHANGE MASTER TO command from Lines 23-25 of the dump as always.

2
  • Hi Rolando , when i am using --dump-slave in my backup command ,i am getting these messages in my error log ,1. [Note] Slave I/O thread killed while reading event , 2. [Note] Slave I/O thread exiting, read up to log 'myserv12.000822', position 9500xxxx , do i have to worry about this , and again it is getting reconnected to master
    – JABIR BAIG
    Sep 24, 2018 at 16:28
  • @JABIRBAIG --dump-slave is supposed to stop the SQL thread not the I/O Thread. The Documentation says : This option causes mysqldump to stop the slave SQL thread before the dump and restart it again after.. You should run START SLAVE IO_THREAD;. Watch the SHOW SLAVE STATUS\G to make sure the mysqldump turns the SQL thread back on. Do not run START SLAVE unless the mysqldump is done and replication is still disabled. Make sure you see Slave_IO_Running : Yes and Slave_SQL_Running : Yes when you run SHOW SLAVE STATUS\G after mysqldump is done. Sep 24, 2018 at 16:33

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.