Common Issues in MySql Binary Logs Based Replication

Binary Logs based replication is one of the modes of replication supported by HevoData for complete replication of MySql flavored databases. This document lists some of the frequently noticed issues, ways to diagnose and fix them when this mode of replication is used.

Prerequisite

Make sure that the steps listed in Setting up MySql Binary Logs for Replication have been followed.

Common Issues

When I select BinLog option and continue, I get an error saying “Failed to determine binlog filename/position”

Probable cause: Binary logs replication hasn’t been enabled correctly on the MySql server

Diagnosis: Launch a sql prompt and run the following queries:

SHOW VARIABLES LIKE 'log_bin'; # This should return ON  
SHOW BINARY LOGS; # This should return a list of files and their offsets

Resolution: Enable the binary logs by following the guide.

When I select BinLog option and continue, I get an error saying “schema: xyz is not tracked via bin logs”**

Probable cause: Binary logs replication is enabled but the schema in request has been excluded from replication

Diagnosis: Launch a sql prompt and run the following queries:

SHOW MASTER STATUS;

# Both of Binlog_Do_DB and Binlog_Ignore_DB should be empty OR
# xyz must be present in Binlog_Do_DB column

Resolution: Enable the schema xyz for replication by following the instructions from here.

When I select BinLog option and continue, I get an error saying “Unsupported bin-log format found. Supported formats: [ROW]“**

Probable cause: Binary logs replication hasn’t been enabled but the format isn’t supported

Diagnosis: Launch a sql prompt and run the following queries:

SHOW VARIABLES LIKE 'binlog_format'; # This should return ROW

Resolution: Change the replication format by setting binlog_format=ROW. You may follow the steps listed in Setting The Binary Log Format article to update the logging format.

The pipeline has been created. I see BinLog in the Overview section. However, nothing shows up in the Schema Mapper section**

Probable cause: Hevo shows information of the tables that have registered some activity in the binary logs since the creation of the pipeline. In the absence of any activity, neither of data nor the schema will show up.

Diagnosis: Use the mysqlbinlog utility to read samples from the binary logs. (Note that mysqlbinlog is available in the mysql server package. So, you may have to install it on the client machine from which you’d perform the tests.)

# Run this on a sql prompt to get the name of the binary log files

SHOW BINARY LOGS;

# Run the following on a shell to check if there is any activity in binary logs

mysqlbinlog -R -h<Host> -u<User> -p -P<Port> <latest_bin_log> | grep insert|delete|update|alter

# If there is nothing that shows up here, you probably need to wait

If the query logging is enabled for your database server, you may refer to the logs to check if queries have been logged in the recent times.

Resolution: Wait for some time until a few transactions are done on the database.

I had created a pipeline with binary logs based replication. The data was being replicated until a few days back. However, there isn’t any activity from a couple of days now**

Probable cause 1: Some configurations have changed on the database. Some of the user privileges were updated.

Diagnosis: Check for the messages listed in the Activity Log section of the pipeline. Iterate over the steps from the setup guide and problems listed in this article.

Probable cause 2: The pipeline was paused for duration longer than the purge period of the binary log files of the mysql server. In each poll iteration, the Hevo application reads from the offset where it had last left. If it does not find the file from which it intends to read, it stops there. When a pipeline is paused for a long duration, it is possible that old binary log files are purged.

Resolution: In case the relevant binary log files are no longer available, you may use alternate means to re-ingest the data.

Tip: It is a good practice to keep the files for a long enough period and archive them. Before purging any file, make sure that no slave is still reading it (Note that Hevo application also behaves as a slave to your server). The document Purge Binary Logs lists a couple of good practices with regards to safely purging binary log files.

Last updated on 25 Aug 2020