Usually to set up MySQL replication it’s convenient to dump database from current master with –master-data parameter. With this option mysqldump includes commented SQL operator which needs to be issued on slave to set up replication:
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000008', MASTER_LOG_POS=687808977;
But sometime it’s not very good idea to dump database from master server. For instance, you use MyISAM tables and you don’t want to lock them while dumping. In this case you can dump database from on e of the slaves (of course, if you already have them). That is what you need to do. Stop SQL_THREAD:
STOP SLAVE SQL_THREAD;
Check slave status:
SHOW SLAVE STATUSG;
Get master binary log coordinates:
mysql> pager egrep '(Relay_Master_Log_File|Exec_Master_Log_Pos)' ; show slave statusG; PAGER set to 'egrep '(Relay_Master_Log_File|Exec_Master_Log_Pos)'' Relay_Master_Log_File: mysql-bin.000225 Exec_Master_Log_Pos: 632106728 1 row in set (0.00 sec) ERROR: No query specified mysql>
Save last output to use it later.
At thus point we can start slave:
START SLAVE SQL_THREAD;
Make sure it’s working:
SHOW SLAVE STATUSG;
After restoring database on new slave change its master to your current one using values above:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000225',MASTER_LOG_POS=632106728;
First value is Relay_Master_Log_File, and the second one is Exec_Master_Log_Pos.
More information you can get in man mysqldump.