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?
-
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!– peterhAug 28, 2018 at 20:53
Add a comment
|
1 Answer
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.
answered Aug 28, 2018 at 17:59
-
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 Sep 24, 2018 at 16:28
-
@JABIRBAIG
--dump-slaveis 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 runSTART SLAVE IO_THREAD;. Watch theSHOW SLAVE STATUS\Gto make sure the mysqldump turns the SQL thread back on. Do not runSTART SLAVEunless the mysqldump is done and replication is still disabled. Make sure you seeSlave_IO_Running : YesandSlave_SQL_Running : Yeswhen you runSHOW SLAVE STATUS\Gafter mysqldump is done. Sep 24, 2018 at 16:33