H
H
Hint2018-06-24 16:08:58
replication
Hint, 2018-06-24 16:08:58

Recovery of replication after failure?

There are master and slave servers on MariaDB in different data centers. Seconds behind master is always 0. There was a power failure in the data center with master, the server turned off. After switching on, a desynchronization occurred. On the slave server, there was data that was not on the master (new id, more recent information), so when starting replication, the "Duplicate entry" error occurred. After removal on the slave of the corresponding records, the process started. After the slave caught up with the master, data somehow ended up on the master that never appeared on the slave.
Additional information: XtraDB tables, innodb_flush_log_at_trx = 2 (apparently this is the problem), sync_binlog = 0, binlog_format = MIXED, GTID replication, log-bin, log_slave_updates.
Two questions:
1. Is it possible to avoid such situations? So that in case of a failure such porridge does not work out.
2. If this happened, what is the easiest way to synchronize the slave without re-transferring all the data? Full backups are taken from the slave via mysqldump in tab format (in sql, only table creation plus data in txt). Separately, the result of SHOW MASTER STATUS is saved at the time of backup (replication stopped). Isn't it enough to, for example, roll back the slave and reapply the changes from the master?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
V
Vladimir, 2018-06-25
@MechanID

1 then you'd better do innodb_flush_log_at_trx = 1 and binlog_format = ROW
2 in percona tools it is possible to check and fix data between master and slave on the go, dock - https://www.percona.com/doc/percona-toolkit/2.2/ pt... (Attention! - probably does not work with mariadb - look for an analogue)
for regular backups, mysqldump has the --dump-slave option, use it instead of SHOW MASTER STATUS

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question