MySQL – How to Debug Huge Binary Log File Increases in Replication Setup

MySQLmysql-5.7replication

I'm running a single slave off a MySQL master. I'm using this version:

Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper

The problem I have is that the system will replicate quite happily for a day or so and then something hits it and the log files on the master grow hugely.

The data directory for the database is about 9.5GB in size.

When the replication problem happens, the log files can take up 175GB of disk space in a very short amount of time (filling the disk).

How do I debug what is happening? Can anyone suggest what to look for in the database operations that may cause this huge blow-out?

Best Answer

I think your replication uses ROW as binlog_format. To verify, please run this on the master: 

SHOW VARIABLES LIKE 'binlog_format'

The result would be one of the following values: statement, row, or mixed. More info.

In the case of ROW format, every changed row will add info to the bin log. If there are statements that affect a table of 1000000 rows, all these changes will be written to the binlog.

For example, suppose you have a huge table, with 1M rows, and you run a statement like this:

UPDATE big_table SET hashed_email=md5(email)

This statement will add 1M entries to the binlog when it is row based, while it adds only one entry to the binlog when it is statement based.

Suggested solution:

Enable the general query log around the time when this this issue usually happens, and analyze all the statements to see if there are many that does update operation on huge number of rows.

Related Question