Bug #79592 Anything wrong with replication of MySQL 5.7?
Submitted: 10 Dec 2015 12:07 Modified: 31 Mar 2017 14:22
Reporter: weixing zou Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7 OS:CentOS (centos 6.6)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: replication

[10 Dec 2015 12:07] weixing zou
Anything wrong with replication of MySQL 5.7?
On the master-slave Architecture, the mysql instance on slave server always cost 100% or around CPU, but under the command show processlist, no running  SQL statement can be seen, and the data on master node can not by replicated to slave node for even more than 2 days. After a very long time, the CPU cost reduced, at this time, the data on master node was replicated to slave node. But this issue will happen again.
By the way, the slave node is idle and was not accessed by any other applications.

on error log file the following info can be seen:
2015-12-10T07:48:27.737960Z 25840 [Note] Aborted connection 25840 to db: 'game' user: 'game' host: 'redis1.server' (Got an error reading communication packets)

Does this log info have anything to do with the above problem?

How to repeat:
Running many complex and long time SQL statement on master server.

Suggested fix:
show the exactly SQL statement on slave server running while replication.
speed up the process of replication.
[21 Dec 2015 7:38] weixing zou
Can anybody update this issue and give my some helpful info?
[4 Jan 2016 8:30] weixing zou
I got the idea. It was because the default binlog_format = ROW.
[29 Jan 2016 17:36] weixing zou
while running show slave status, I got the following info:

Slave_SQL_Running_State: Waiting for dependent transaction to commit
I cannot get any extra info by searching.

Can anybody give me hints?

[31 Mar 2017 14:22] MySQL Verification Team

> Anything wrong with replication of MySQL 5.7?

No, nothing wrong with it.

You need to be sure it's properly configured.

High CPU usage and long delay usually means something in your setup is wrong, often it means you replicate tables that do not have primary key. If there's no primary key on a table being replicated the replication can have this type of problems.

kind regards