0

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.

Improve this question
9
  • 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? Commented Aug 24 at 14:18
  • No to both. Its all small changes. Commented Aug 24 at 14:29
  • 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. Commented Aug 24 at 17:16
  • 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. Commented Aug 25 at 2:17
  • 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. Commented Aug 25 at 2:19

1 Answer 1

Reset to default
2

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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.