0

What exactly does this mean, when the SQL is running but the IO is not running on the Slave in a Master-Master replication setup?

There are no other errors showing when running show slave status\G

Image as per the below;

enter image description here

and of course the log file and log positions do not match that of the master.

3
  • 1
    Please only tag the DBMS you're actually using. I have removed the MariaDB tag as your question explicitly mentions MySQL. Though they share a common history, they are not the same DBMS, so you should not tag both. Aug 27, 2021 at 7:11
  • Is any error mentioned in Last_Errno, Last_Error, Last_IO_Errno, Last_IO_Error, or Slave_SQL_Running_State? If not, is there an error reported in the appropriate database log file?
    – matigo
    Aug 27, 2021 at 8:37
  • @MarkRotteveel - I believe that 99% of replication stuff is the same between MySQL and MariaDB; I am adding the tag back on.
    – Rick James
    Aug 27, 2021 at 19:30

1 Answer 1

1

Replication works in 2 steps:

  1. The information in the "binlog" on the Primary is continually shipped to each Replica, where it is written to the "relay log". Slave_IO_Running=No means that that has stopped for some reason. Possibly network issue. The Replica will try several times (tunable) before giving up. Use of SLAVE STOP; and SLAVE START; might get it going again.

  2. The relay log is read by the "SQL" thread, applying the changes as it gets to them.

The reason for this 2-step process is to copy stuff to the Replicas as quickly as possible, for fear that the Primary might crash. A crash could lead to stuff not yet getting to the Replica. (See "semi-sync replication" as a partial remedy for such.)

Also, the SQL thread could get slowed down on some long query (a big DELETE, ALTER, etc); meanwhile, future queries are busy queuing up in the relay log.

Originally, queries were applied strictly in the order they came through the binlog and relay log.

All that dates back 2 decades. More recent techniques include applying the queries in parallel.

Note that the queries are shipped, not the file -- this does lead to the positions being different. When a server is restarted (for any reason), a new binlog or relay log is started. This definitely throws the positions out of sync. So, why bother trying to keep then in sync?

Also, binlog/relaylog-do/ignore configuration controls what needs to be shipped. In some cases this leads the relay log having fewer queries than the binlog. Note also, that different Replicas can have different configurations, making them inconsistent. Do not expect two Replicas to have identical positions.

"Master-Master" is essentially Primary-Replica in both directions. (And you should use the do/ignore settings if you ever expect to failover between them.)

After you get the side with the IO stopped going again, replication should quickly catch up, and all will be well.

3
  • (I added some more.)
    – Rick James
    Aug 27, 2021 at 19:33
  • Thanks for that. I think what confused me was seeing several legacy diagrams for the database servers in question also showing the IO to be in the stopped state and wondered if for whatever reason, this was on purpose, if that is even possible.
    – plisken
    Aug 27, 2021 at 20:00
  • Once in a long while, that STOP..START has fixed problems. And I failed to discover the real cause. Even if it does not help, it is at least harmless.
    – Rick James
    Aug 27, 2021 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.