Search code examples
mysqldatabasewindows-servicesmaster-slavemysqlbinlog

How to Resync the Slave database automatically from Master database using a Windows Service?

I am writing a Windows Service program to sync a local Slave database from the Master database continuously. I have a master database called nsbm_syncdb. If I want to Sync the slave database, I have to write the following query.

CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_USER='nsbm_root',
MASTER_PASSWORD='1234',
MASTER_LOG_FILE='mysql-bin.0000xx',
MASTER_LOG_POS=234;

For the above code, I should take the values of MASTER_LOG_FILE and MASTER_LOG_POS from the Master database by entering the query "SHOW MASTER STATUS".

How can I retrieve the log position and log file name from the Master database using a MySql query?

I went through the internet but could not find a query yet.

Solution

  • You can use mysqldump program (shipped with MySQL) with the --master-data=2 option.

    This will write the complete CHANGE MASTER ... command to the output.