What is the difference between binlog-do-db and replicate-do-db?

Question

I'm a beginner for MySQL Master-Slave .

and I have read two tutorials .

1. How to Setup MariaDB (Master-Slave) Replication

2. Setup MariaDB Master-Slave Replication

---

In first tutorial. It make me that

[mysqld] Master section

log-bin

server_id=1

replicate-do-db=employees

bind-address=192.168.0.18


[mysqld] SLAVE Section

server_id=2
replicate-do-db=employees

---

But in the second tutorial, it show me that

[mysqld] Master
server_id=1
log-basename=master
log-bin
binlog-format=row
binlog-do-db=unixmen

[mysqld] Slave
server-id = 2
replicate-do-db=unixmen

---

And why I should LOCK TABLES, and mysqldump sql, then import it ?

FLUSH TABLES WITH READ LOCK;

---

Homologue

these settings are actually quite dangerous - it caused missing data on our databases - read here - percona.com/blog/2009/05/14/… – Batavia

@Batavia : if a service that needs db, why not put each service's db in separate instance of mysql containers ? wouldnt that be much easier to manage. – Effectually

Answer 1

DISCLAIMER: To keep things easy and non-confusing here, I talk about a simple 1 Master - 1 Slave setup. No chains, no master-master, or whatever...

Your first tutorial is wrong, it should be binlog-do-db there.

Replication works like this.

The master writes all transactions into its binary log.
The slave reads the transactions from masters binary log and writes them to its relay log.
Only after that the slave executes the statements from its relay log.

binlog-do-db makes the master write only statements for the specified DB into its binary log.

replicate-do-db makes the slave just read statements from the relay log, that are for the specified DB.

replicate-do-db has no effect on the master, since there is no relay log to read from.

---

The LOCK TABLES part is there, so that the data is consistent. It prevents that the data on the master is modified while backing up the data is still in process.

You restore the database from this backup on the slave, because when you set up a slave, you don't always start from fresh. Therefore it's made so, that you just provide the same data basis on both servers, then you tell the slave at which transaction coordinates the master is and voila, you can start your replication. You can also unlock the master after having dumped the data. Just make sure, that you get the slave up in time before statements in the binary log get lost due to log rotation.

Conformation

I have a scenario. I have 2 dbs A and B A has insert triggers to insert values in to B, and database B is replicated binlog-do-db =B in master replicate-do-db=B in slave master machine logs A's insert statements, Note: I have configured Statement based replication – Kimberleekimberley

Sorry, pal, but why don't you just try it out? I'm not willing to spend time on something like this right now, because that's a setup I would never use in production. I'm sure you can find a better solution than writing with triggers from one DB to another. You might want to ask this in a separate question. It certainly is too much to discuss in comments here. – Conformation

@Kimberleekimberley you need master - master replication than master slave. if I am not wrong – Effectually