What is the difference between binlog-do-db and re

问题:

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;

---

回答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.