How to fix MySQL 8 replication error 1062 after system reboot?

Question

after a system reboot my replication stopped suddenly. So I'm trying to bring the slave back to the most current state.

To do this I create a dump from the master's database and import it on the slave, like

mysqldump --host=MASTERADDR --port=MYPORT -u root --password=DBPASS DBNAME > FILE.sql
mysql --host=SLAVEADDR --port=MYPORT -u root --password=DBPASS DBNAME < FILE.sql

Then on the slave I "restart" my replication:

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST = 'MASTERADDR', MASTER_PORT = MYPORT, MASTER_USER = 'REPLUSER', MASTER_PASSWORD = 'REPLPW', MASTER_AUTO_POSITION = 1;
START SLAVE;
SELECT SLEEP(1);
SHOW SLAVE STATUS\G

This worked pretty well in the past. But now I get the following error code:

Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '691042d7-6ca8-11ed-a122-0242ac120002:4' at master log mysql-bin.000001, end_log_pos 1938. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

I already tried to:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

But this doesn't work and seems to be ignored.

Please, what can I do to start my replication again?

score 0 · Answer 1 · answered Jan 28 '23 at 00:04

MASTER_AUTO_POSITION = 1; is probably invalid. 0 or 4 is the start of the binlog. But you have not specified which binlog.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; says to skip the next command in the replication stream. This is destructive, and should not be used without knowing what query is being skipped.

RESET SLAVE is rather drastic, too.

Do you have all the binlogs since the system was set up? If not do you have a particular point (binlog name + pos)? (It sounds like you don't.)

Is GTID enabled?

Bottom line, I suspect you need to rebuild the Replica from the Primary.

Homework: Read a lot more about Replication.

How to fix MySQL 8 replication error 1062 after system reboot?

1 Answers1