What is the difference between binlog-do-db and replicate-do-db?
I'm a beginner for MySQL Master-Slave .
and I have read two tutorials .
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;
Answer
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.