MySQL Replication working but not updating

Question

This question was migrated from Stack Overflow because it can be answered on Database Administrators Stack Exchange. Migrated last month.

I seem to have an impossible situation.

I have set up MySQL (8.0) on a Master and Slave and followed instructions to set up replication.

Replication seems to be running but none of the tables in the slave are being updated.

Running in AlmaLinux 9.

Details from Master:

In \etc\my.cnf.d\mysql-server.cnf I have added

server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON

show master status shows

File: mysql-bin.000003
Position: 31142290
Gtd: f675fc68-1ade-11f0-8732-0201eb49f50d:1-38965

Details from Slave:

In \etc\my.cnf.d\mysql-server.cnf I have added

server-id = 2
log-bin = mysql-bin
relay-log = relay-log-server
read-only = OFF
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

show replica status shows

Replica_IO_State Waiting for source to send event
Source_Host ionos1.xxxx.co.uk
Source_User replica_user
Source_Port 3306
Connect_Retry 60
Source_Log_File mysql-bin.000003
Read_Source_Log_Pos 31141859
Relay_Log_File relay-log-server.000004
Relay_Log_Pos 31141644
Relay_Source_Log_File mysql-bin.000003
Replica_IO_Running Yes
Replica_SQL_Running Yes

The Log_Pos values seem to be strange - and the slave is always lagging behind the master even though it always shows 'Waiting for source to send event'

The total size of the 3 bin files is 321,141 KB

Thanks in advance.

Edit added:

As I'm not concerned about corrupting the slave data, I thought I would try some changes. In the cnf file of the slave I removed what looked like redundant lines, leaving just:

server-id = 2;
relay-log = relay-log-server
read-only = OFF.

I then executed:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
START SLAVE;

I restarted mysqld for both servers. Now replica status shows 'connecting to server' with log_pos stuck at 4.

The log shows: "Can't connect to MySQL server on 'ionos1.xxxx.co.uk:3306' (110), Error_code: MY-002003" so my change must have broken something.

So I put back the removed lines and restarted mysqld for both - but still failing to connect.

I would not mind totally removing all reference to replication on both servers and starting afresh, but I have tried that already once and still ended up with the same problem.

• Bill Karwin: Are there a slow or unreliable network in between the source and replica instances? Or are the individual binlog events very large? For example, insert/update of large text/blob/json values?
• LesD: No to both. Its all small changes.
• Bill Karwin: The 2003 error is a common error, "Can't connect." This can happen for a variety of reasons, including: the replica can't resolve the source hostname; the replica has no network route to the source; the replica or the source has a firewall preventing the connection; the source MySQL Server is not running, or is listening on a different port; and other reasons. I would first try to troubleshoot those issues. See dev.mysql.com/doc/refman/en/problems-connecting.html for more related suggestions.
• LesD: Thank you. Connection problem resolved. Some days back the 3306 was shut on the primary server as all MySQL connections are either local or via SSH tunnel. Restarting mysqld yesterday lost the old connection. 3306 now open and its connecting.
• LesD: mysqld would not start unless I put back the two gtid lines in the cnf file. I then reset the source back to the 1st bin file position 1 - back to the original problem.

Answer 1

In the absence of anyone saying otherwise, it seems my original setup was correct - using the GTID method. Unfortunately it looked like it was working, but it was not actually updating the tables.

Removing GTID (two lines) from the cnf files at each end solved the problem.

Replication is now working.

If anyone can comment on getting GTID to work, I would be interested to hear but take on board Bill's advice - there is no need to get involved with GTID.

With hindsight, setting up basic replication is quite trivial.

For the record, the final relevant cnf entries in the master cnf are

server-id = 1
log-bin = mysql-bin
binlog_format = row

and for the slave

server-id = 2
relay-log = relay-log-server