We have a read replica set up for the Azure MYSQL server, we would like to know how to identify when was replica server last synced with the master in case of a disaster or heath event. We need this to identify the data gap between the master and replica server and to bring back the missing data to fill the gap.

Hi @VaishnavAV-2087, welcome to Microsoft Q&A forum. As I understand you would like to know how much is the replication lag between the master and read replica in Azure MySQL Server. This can be known through command ' SHOW SLAVE STATUS ' running on Read replica, please check the 'Seconds_Behind_Master ' (Displays replication latency in seconds) column in below screenshot. This will tell us the replication latency time in seconds like how many second latency is there for current data sync between master and read replica. You can refer to below article for more details on the same: Troubleshoot replication latency in Azure Database for MySQL Please let us know if this helps or else we can discuss further on the same. ---------- If answer is helpful please click on as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!

How to find the replication lag or last succesful replication time between two MYSQL servers

Accepted answer

Anurag Sharma 17,571 Reputation points

2022-05-11T07:42:07.143+00:00

Hi @VaishnavAV-2087, welcome to Microsoft Q&A forum.

As I understand you would like to know how much is the replication lag between the master and read replica in Azure MySQL Server.

This can be known through command 'SHOW SLAVE STATUS' running on Read replica, please check the 'Seconds_Behind_Master' (Displays replication latency in seconds) column in below screenshot. This will tell us the replication latency time in seconds like how many second latency is there for current data sync between master and read replica.

You can refer to below article for more details on the same:

Troubleshoot replication latency in Azure Database for MySQL

Please let us know if this helps or else we can discuss further on the same.

----------

If answer is helpful please click on as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!
Please sign in to rate this answer.

1 person found this answer helpful.
Vaishnav AV-FT 81 Reputation points

2022-05-11T10:41:39.763+00:00

Thankyou for your response @AnuragSharma-MSFT .

Currently I am working with a BCP scenario, I require the time frame when the master server went down and till what time the replica was synced.

show slave status will not work once the replication is stopped so I will not get exact time frame.

similarly I will not be able to test the same trying to stop master and check the replication lag as We are not allowed to stop a master server when replication is enable

Is there any other method to get when was replica synced?

Anurag Sharma 17,571 Reputation points

2022-05-12T05:31:34.8+00:00

Thanks for responding back @VaishnavAV-2087 and providing more details.

We can read through the binlog file as mentioned in the below article by configuring the 'binlog_expire_logs_seconds' parameter to higher value. As it copies the same records from source and maintain its own copies.

Accessing binary logs from Azure Database for MySQL

Also if we are using auto_increment values, we can use last_insert_id function to identify last inserted id on source/master.

Obtaining Auto-Increment Values

LAST_INSERT_ID(), LAST_INSERT_ID

Anurag Sharma 17,571 Reputation points

2022-05-13T09:17:46.327+00:00

Hi @VaishnavAV-2087 just wanted to check if this helps or we can discuss further.

Vaishnav AV-FT 81 Reputation points

2022-05-19T09:51:23.413+00:00

Thankyou @AnuragSharma-MSFT, is there a way to fetch Binlog as a file from Azure MySQL in a readable format?

Anurag Sharma 17,571 Reputation points

2022-05-20T07:44:48.22+00:00

Thanks for replying back @VaishnavAV-2087, yes we can do that. Below is the document link that has step by step description of the same:

Accessing binary logs from Azure Database for MySQL – Flexible Server

I tried to run the same commands on my windows machines and was able to fetch the file and then convert it to readable text file:

Firstly just verify if you have mysqlbinlog.exe present as mentioned in below screenshot:

Then we need to run below command in MySQL workbench and copy the name of binary log:

show binary logs;

After this we can run below command in command window:

mysqlbinlog --verify-binlog-checksum --host=<ServerName>.mysql.database.azure.com --port=3306 --user=<username> --raw --read-from-remote-server --stop-never --connection-server-id=101 --password=<password> <binaryLogFileName>

This is going to copy the files in same directory and then finally we can run below command to convert it into text file:

mysqlbinlog --verbose <binaryLogFileName> > file_bin_000040.txt

Please let us know if this helps or else we can discuss further.

Vaishnav AV-FT 81 Reputation points

2022-05-26T14:28:19.667+00:00

Thankyou @AnuragSharma-MSFT , I will check further and get back if I need help.
Sign in to comment

How to find the replication lag or last succesful replication time between two MYSQL servers

0 additional answers