MariaDB replication breaks at the worst possible time, right when you need the database to be calm, predictable, and boring.

If you’re running classic (non-GTID) master–slave replication, the fastest way out of a messy drift is usually not to “skip errors” or “restart services” endlessly.

The cleanest recovery is to reseed the slave from a fresh snapshot of the master, then let replication catch up from the exact binlog coordinates that match that snapshot.

The good news, you can do all of that while the master keeps running.

This article is a practical runbook for reseeding non-GTID replication using mysqldump with --single-transaction and --master-data=2, plus one extra option that makes the whole process safer: --add-drop-table.

Why reseed instead of “fixing” replication?

When replication breaks, you might see errors like

• Duplicate key / duplicate entry
• Cannot find record (missing row)
• Foreign key issues
• Random drift that keeps reappearing after you “skip” one event

Those symptoms usually mean one thing: the slave is no longer an exact logical copy of the master.

In that situation, skipping transactions might get you a green status for a few minutes, but it also guarantees you’ll carry silent inconsistencies forward.

A reseed resets the slave to a known-good snapshot and restores a clean replication path.

The fear, “but the master is still running and the binlog keeps moving”

Yes, the binlog will keep moving. That’s normal.

When you dump the master with:

• --single-transaction (consistent snapshot for InnoDB), and
• --master-data=2 (records the master’s binlog file+position that matches that snapshot)

…the dump file contains a single “bookmark” for where replication should start. Even if the master continues writing changes during the dump, the slave can start from that recorded position and replay everything that happened after the snapshot.

What --master-data=2 actually does

--master-data=2 writes the master coordinates inside the dump file as a comment, like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;
Code language: JavaScript (javascript)

The key part is: it matches the snapshot. It’s the exact point-in-time reference you use to configure the slave.

Why =2? Because it’s safer. With =2, the command is not executed automatically during import; you extract it and run CHANGE MASTER TO manually.

The clean reseed steps (non-GTID)

1, Stop replication and reset slave metadata (on the SLAVE)

STOP SLAVE;
RESET SLAVE ALL;

At this point, you’ve cleared the old master connection info and relay logs. You’re ready to load a fresh snapshot.

2, Dump the master (on the MASTER)

Dump all replicated databases in one file (recommended). Replace db1 db2 db3 with your actual DB list.

mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  --master-data=2 \
  --add-drop-table \
  --databases db1 db2 db3 > /tmp/reseed.sql
Code language: JavaScript (javascript)

Why these flags?

• --single-transaction
  Gives a consistent snapshot without locking for InnoDB tables.
• --master-data=2
  Records the exact binlog file/pos for this snapshot.
• --add-drop-table
  Makes the dump drop existing tables on the slave before recreating them — useful if you prefer not to manually drop databases.
• --routines --triggers --events
  Because production databases aren’t just tables.

Sample how You copy it to the slave, you can use other method.

scp /tmp/reseed.sql user@SLAVE_IP:/tmp/reseed.sql
Code language: JavaScript (javascript)

3, Import the dump (on the SLAVE)

mysql -u root -p < /tmp/reseed.sql
Code language: JavaScript (javascript)

Because we included --add-drop-table, the import will overwrite tables cleanly (inside the databases you included in the dump), without you having to manually DROP DATABASE.

4, Extract the binlog coordinates from the dump (on the SLAVE)

grep -m 1 "CHANGE MASTER TO" /tmp/reseed.sql
Code language: JavaScript (javascript)

You’ll get a line similar to:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;
Code language: JavaScript (javascript)

5, Point the slave to the master, then start replication (on the SLAVE)

Replace host/user/password and the file/pos.

CHANGE MASTER TO
  MASTER_HOST='MASTER_IP',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000123',
  MASTER_LOG_POS=456789;

START SLAVE;
SHOW SLAVE STATUS\G
Code language: JavaScript (javascript)

Success means:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Last_IO_Error: empty
• Last_SQL_Error: empty

Do you need to reseed per database?

No , not conceptually.

If you replicate multiple databases, you’ll get the cleanest result by making one dump containing all replicated databases, that ensures they all reflect a consistent snapshot.

Multiple separate dumps taken at different times can create weird cross-database inconsistencies, especially if your app writes across schemas.

Notes and sharp edges

1, InnoDB vs MyISAM/Aria

--single-transaction is ideal for InnoDB. If you still have MyISAM/Aria tables that are actively changing, you may need a different strategy (brief read lock, or convert them).

2, Keep the slave read-only

A replica that accepts writes becomes a future incident waiting to happen. If you can, keep it read-only:

SET GLOBAL read_only=ON;
SET GLOBAL super_read_only=ON;
Code language: PHP (php)

3, Replication filters matter

If your slave uses replicate-do-db or wildcard filters, align your mysqldump --databases ... list to the same scope, dump what you replicate.

Closing thought

When replication breaks, it’s tempting to keep applying band-aids until the status turns green.

But green isn’t the same as correct.

A reseed is one of those maintenance tasks that feels heavy… until you do it once, watch it work, and realize you just bought back your peace of mind.