mysql replication problems

General support questions
didds1962
Posts: 17
Joined: 2020/01/20 16:19:25

Re: DRBD "failing"?

Post by didds1962 » 2021/05/04 12:03:43

TrevorH wrote:
2021/05/04 10:46:40
Edit: actually this may only be required if you're running in master-master mode

ah - no its not. master-slave. so not needed after all?

cheers

ian
Last edited by didds1962 on 2021/05/04 12:26:16, edited 1 time in total.

User avatar
TrevorH
Site Admin
Posts: 33202
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: DRBD "failing"?

Post by TrevorH » 2021/05/04 12:20:53

With a single master, no, I don't think it's required.

It would help to know what error messages you get when it goes wrong.
The future appears to be RHEL or Debian. I think I'm going Debian.
Info for USB installs on http://wiki.centos.org/HowTos/InstallFromUSBkey
CentOS 5 and 6 are deadest, do not use them.
Use the FAQ Luke

didds1962
Posts: 17
Joined: 2020/01/20 16:19:25

Re: DRBD "failing"?

Post by didds1962 » 2021/05/04 12:39:57

looking in mysqld.log there are no errors showing that we typically see when trying show slave status \G etc (replication now allegedlty fine as noted before) - but there is a lot of this going on...

2021-04-29T07:58:19.472259Z 11491 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for
START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2021-04-29T07:58:19.473044Z 11492 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2021-04-29T07:58:19.473161Z 11492 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.014734' at position 453250881, relay log './<redacted>-relay-bin.000585' position: 426869203
2021-04-29T07:58:19.476041Z 11491 [Note] Slave I/O thread for channel '': connected to master '<redacted>@<redacted>:3306',replication started in log 'mysql-bin.014748' at position 511958396
2021-04-29T07:58:19.681346Z 11492 [Note] Slave SQL thread for channel '' exiting, replication stopped in log 'mysql-bin.014734' at position 462456134
2021-04-29T07:58:19.682074Z 11491 [Note] Slave I/O thread killed while reading event for channel ''
2021-04-29T07:58:19.682113Z 11491 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.014748', position 511967945

didds1962
Posts: 17
Joined: 2020/01/20 16:19:25

Re: DRBD "failing"?

Post by didds1962 » 2021/05/05 07:57:51

I just checked - mysql logging is set to its maximum level of 3 (by default).

last night we reset replication from scratch - broke slave, dumped with master host status and locked tables etc, copied then imported and reset slave ...

still saying its broken wrt the heartbeat check.

Im in despair.

User avatar
TrevorH
Site Admin
Posts: 33202
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: DRBD "failing"?

Post by TrevorH » 2021/05/05 08:12:13

And when it stops, what do you have to do to restart it? Is it as simple as stop slave;start slave; ? Or more complicated.

My first - and so far only - thought when I saw your log was that you have a duplicate ip address on the network, either slave or master end, so that it occasionally talks to the wrong thing and gets an invalid response and just stops. But there isn't really enough data there to tell.
The future appears to be RHEL or Debian. I think I'm going Debian.
Info for USB installs on http://wiki.centos.org/HowTos/InstallFromUSBkey
CentOS 5 and 6 are deadest, do not use them.
Use the FAQ Luke

didds1962
Posts: 17
Joined: 2020/01/20 16:19:25

Re: DRBD "failing"?

Post by didds1962 » 2021/05/05 15:15:40

TrevorH wrote:
2021/05/05 08:12:13
And when it stops, what do you have to do to restart it? Is it as simple as stop slave;start slave; ? Or more complicated.

My first - and so far only - thought when I saw your log was that you have a duplicate ip address on the network, either slave or master end, so that it occasionally talks to the wrong thing and gets an invalid response and just stops. But there isn't really enough data there to tell.
literally STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE


maybe a few times as it steps though various errors ( no record of those this time...)

then show slacve status shows double yes
and it picks up with itself.

User avatar
TrevorH
Site Admin
Posts: 33202
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: DRBD "failing"?

Post by TrevorH » 2021/05/05 16:11:08

If you do the set global sql_slave_slip_counter then it skips over a replication packet. What happens if you do not do that?

It's the errors and why it's stopping that are the interesting bit and the bits that need fixing.

Are you running the same version of the server at both ends of the connection?

I've also changed the title of the first post in this thread since we're not talking about DRBD any more and reflecting the true nature of the problem might attract more people with more knowledge.
The future appears to be RHEL or Debian. I think I'm going Debian.
Info for USB installs on http://wiki.centos.org/HowTos/InstallFromUSBkey
CentOS 5 and 6 are deadest, do not use them.
Use the FAQ Luke

didds1962
Posts: 17
Joined: 2020/01/20 16:19:25

Re: DRBD "failing"?

Post by didds1962 » 2021/05/06 11:48:21

TrevorH wrote:
2021/05/05 16:11:08
If you do the set global sql_slave_slip_counter then it skips over a replication packet. What happens if you do not do that?
show slave status \G never moves on. It continues to show the same error.
Are you running the same version of the server at both ends of the connection?
yes.


cheers

ian

User avatar
TrevorH
Site Admin
Posts: 33202
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: mysql replication problems

Post by TrevorH » 2021/05/06 11:54:22

Once it stops because of an error then it won't restart until you stop/start it (unless it's a transient connection error).

You need to show us the errors you get before it stops.
The future appears to be RHEL or Debian. I think I'm going Debian.
Info for USB installs on http://wiki.centos.org/HowTos/InstallFromUSBkey
CentOS 5 and 6 are deadest, do not use them.
Use the FAQ Luke

didds1962
Posts: 17
Joined: 2020/01/20 16:19:25

Re: mysql replication problems

Post by didds1962 » 2021/05/06 15:35:56

k... no errors currently (double yes scenario).

though its not yet "caught up" as demonstrated by the heartbeat check still being out.


Next time...


didds

Post Reply