Boost MySQL Master‑Slave Replication: Key Master & Slave Settings Explained

MySQL master‑slave replication works by having the master record its execution log (binlog) and send it to the slave, which parses and executes the log to replicate data.

Binlog size greatly impacts replication efficiency because it affects I/O and network transmission.

Master side

The master has two parameters that control what is written to the binlog:

Binlog_Do_DB : specifies databases that should be logged.

Binlog_Ignore_DB : specifies databases that should not be logged.

Setting these correctly can reduce binlog size, improve I/O efficiency, and speed up network transfer, but misuse may cause data inconsistency or replication errors. MySQL decides whether to replicate an event based on the default database at execution time (the database selected on login or via USE database), not the database referenced in the statement.

For example, if the garbage database is excluded from logging and the product database is included, the following statements will cause an error on the slave because the slave lacks the garbage database:

use product;
delete from garbage.junk;

Although the DELETE statement is sent to the slave, it fails because the referenced database does not exist, causing replication to stop.

Slave side

The slave has six parameters for filtering replication:

Replicate_Do_DB : databases to replicate (comma‑separated).

Replicate_Ignore_DB : databases to ignore.

Replicate_Do_Table : tables to replicate.

Replicate_Ignore_Table : tables to ignore.

Replicate_Wild_Do_Table : same as Replicate_Do_Table but supports wildcards.

Replicate_Wild_Ignore_Table : same as Replicate_Ignore_Table but supports wildcards.

While slave‑side tuning has less impact than master‑side changes—because the master has already written the binlog—these filters can still reduce the amount of SQL applied on the slave, decreasing replication lag.