MySQL Master-Slave replication

Question

I have one MySQL Master and several slave machines.

master-slave replication is set and works perfectly.

At this point, I want to add a new slave machine:

My question relates to the "CHANGE MASTER TO" command:

Assuming that all the files are available on the master, I will start from mysql-bin.000001, but from which position ? MASTER_LOG_POS=0 ? MASTER_LOG_POS=1 ? Something else ?

CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='...', MASTER_PASSWORD='...', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=WHAT_SHOULD_WRITE_HERE;

10x,

Answer 1

You should execute show master status; for example,

mysql> show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 | 13966886 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

before taking dump on master and note the log position. Restore dump on slave and run change master to with same log position 13966886 which you have got before mysqldump.

Answer 2

If you have all the bin logs from the start of time you can use use master_log_file .000001 and pos=0. Most people don't have the binary logs from the master start anymore, so the position to use is the one that was when the dump was taken. The dump that you copied to your slave server. Or the position when you closed your master and copied data directories.

You already have a slave server so this makes it easier for you. You can stop the slave thread on the slave server, check the logfile name and position from the slave, copy the datafiles from the slave to the new server and start both.