How to fix MySQL replication when duplicated key problem appears?

Question

I have data logger software that writes data to master database. There are just 2 tables. If this database is locked or stopped for more than 1 minute I will loose some data that should be stored.

Master database is replicated to another machine via cellular connection which is not stable sometimes. Replica is used to see the data on Grafana. It can be stopped for a long time and nothing bad will happen.

I have experienced duplicated key problem second time and I'm looking for productive way of fixing it. Last time it took me some time to find a way to fix it and the master database was locked for a few minutes because I couldn't do it properly and made several attempts.

I think I can just lock master database for a moment, execute mysql dump, check current log position, unlock it and start rebuilding my slave database.

I don't know exactly how I should rebuild the slave from master dump file. Should I truncate or remove tables from the slave? What about users and permissions?

Could someone please explain step by step how should I do it?

---

Here is my create table from the replica (created from master dump, so DDL is the same):

CREATE TABLE `bits` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Datetime` datetime NOT NULL,
  `B01` tinyint(1) NOT NULL,
  `B02` tinyint(1) NOT NULL,
  `B03` tinyint(1) NOT NULL,
  `B04` tinyint(1) NOT NULL,
  `B05` tinyint(1) NOT NULL,
  `B06` tinyint(1) NOT NULL,
  `B07` tinyint(1) NOT NULL,
  `B08` tinyint(1) NOT NULL,
  `B09` tinyint(1) NOT NULL,
  `B10` tinyint(1) NOT NULL,
  `B11` tinyint(1) NOT NULL,
  `B12` tinyint(1) NOT NULL,
  `B13` tinyint(1) NOT NULL,
  `B14` tinyint(1) NOT NULL,
  `B15` tinyint(1) NOT NULL,
  `B16` tinyint(1) NOT NULL,
  `B17` tinyint(1) NOT NULL,
  `B18` tinyint(1) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `idx_dt` (`Datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=92553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `words` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Datetime` datetime NOT NULL,
  `W01` int NOT NULL,
  `W02` int NOT NULL,
  `W03` int NOT NULL,
  `W04` int NOT NULL,
  `W05` int NOT NULL,
  `W06` int NOT NULL,
  `W07` int NOT NULL,
  `W08` int NOT NULL,
  `W09` int NOT NULL,
  `W10` int NOT NULL,
  `W11` int NOT NULL,
  `W12` int NOT NULL,
  `W13` int NOT NULL,
  `W14` int NOT NULL,
  `W15` int NOT NULL,
  `W16` int NOT NULL,
  `W17` int NOT NULL,
  `W18` int NOT NULL,
  `W19` int NOT NULL,
  `W20` int NOT NULL,
  `W21` int NOT NULL,
  `W22` int NOT NULL,
  `W23` int NOT NULL,
  `W24` int NOT NULL,
  `W25` int NOT NULL,
  `W26` int NOT NULL,
  `W27` int NOT NULL,
  `W28` int NOT NULL,
  `W29` int NOT NULL,
  `W30` int NOT NULL,
  `W31` int NOT NULL,
  `W32` int NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `idx_dt` (`Datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=92553 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

There are single primary keys in the tables (bigint Id).

There are no INSERT operation sources on the replica, except replication from master.

Here is the log tail from my replica:

2023-02-14T09:48:00.884440Z 161 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '10.15.0.12:3306' (110), Error_code: MY-002003
2023-02-21T13:13:38.933590Z 161 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '10.15.0.12:3306' (110), Error_code: MY-002003
2023-02-27T01:05:20.054098Z 7 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log NBMS-DELL-bin.000009, end_log_pos 8885352; Could not execute Write_rows event on table nbmslogger.bits; Duplicate entry '91393' for key 'bits.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 8885352, Error_code: MY-001062
2023-02-27T11:58:07.334955Z 37 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log NBMS-DELL-bin.000009, end_log_pos 8885352; Could not execute Write_rows event on table nbmslogger.bits; Duplicate entry '91393' for key 'bits.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 8885352, Error_code: MY-001062

When I open that table in DBeaver I see that there are more records replicated from the master, but replication will probably try to insert them again for some reason.

Answer 1

Do not have two tables that share the same AUTO_INCREMENT id. Instead, insert into one table with an AUTO_INCREMENT, get the id from that table (using, for example, LAST_INSERT_ID()) for the other table that does not have AUTO_INCREMENT.

Better yet, combine the two tables into a single table.

Also:

PRIMARY KEY(id),
UNIQUE KEY (id)  -- This is redundant, DROP it.

Answer 2

Well, I managed to do a quick fix.

On my replica I just removed >=91393 records from my bits table.

Then I have stopped replica (stop replica;) and started it again (start replica;). After this new error in mysql logs appeared. It was about duplicated keys in second, words table. I removed rows again, like in first table.

But still it is a mystery why it happens...