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

Question

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.

score 0 · Accepted Answer · answered Feb 13 '18 at 05:03

0

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.

answered Feb 13 '18 at 05:03

marekful

14,986
6
37
59

score 0 · Answer 2 · answered Feb 14 '18 at 10:56

We can write a C# code within the windows service program to access the values of the SHOW MASTER STATUS table. Its not an existing table. So We have to create a DataTable and fill the details of the SHOW MASTER STATUS into that.

string master_log_file = null;
long master_log_pos = 0;

        using (MySqlConnection con = new MySqlConnection(conStrM))
        {
            using (MySqlCommand cmd = new MySqlCommand("SHOW MASTER STATUS", con))
            {
                cmd.CommandType = CommandType.Text;
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    if (sda != null)
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            foreach (DataRow row in dt.Rows)
                            {
                                master_log_file = row["File"].ToString();
                                master_log_pos = Convert.ToInt64(row["Position"]);
                            }
                        }
                    }

                }
            }
        }

from the above code we can get the master_log_file and the master_log_pos.

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

2 Answers2