It is also possible to set up a slave by dumping an existing slave of the master.
To do this, use the following procedure on the existing slave:
1.Stop the slave’s SQL thread and get its current status:
mysql> STOP SLAVE SQL_THREAD;
mysql> SHOW SLAVE STATUS;
2.From the output of the SHOW SLAVE STATUS statement, get the binary log coordinates of the master server
from which the new slave should start replicating. These coordinates are the values of the
Relay_Master_Log_File and Exec_Master_Log_Pos values. Denote those values as file_name and file_pos.
3.Dump the slave server:
shell> mysqldump –master-data=2 –all-databases > dumpfile
4.Restart the slave:
mysql> START SLAVE;
5.On the new slave, reload the dump file:
shell> mysql < dumpfile
6.On the new slave, set the replication coordinates to those of the master server obtained earlier:
mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE = ‘file_name’, MASTER_LOG_POS = file_pos;
The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master server host. Add any such parameters as necessary.
–flush-logs
“Flushing the logs”, in MySQL parlance, means closing the current log and opening a new one.
It does not get rid of old logs.
Old logs can be removed explicitly with RESET MASTER or PURGE MASTER LOGS,
else they expire automatically according to the expire_logs_days system variable.
————————————————————————————————
I tried the same thing but after following the step suggested:
CHANGE MASTER TO master_log_file=’name_of_current_file_on_master’,master_log_pos=4;
I repeated the step with old log name and old file position before starting the slave, didn’t loose any data and the stuff worked perfect.
Not sure if it is a bug as it happened to two of the servers in both cases after reboot.
————————————————————————————————
Another solution:
reset slave;
start slave;
you can also do,
reset slave —-This will remove/delete any old master and relay log
information
and use the CHANGE MASTER command.
eg.
CHANGE MASTER TO MASTER_LOG_FILE=’c-server-bin.000090′, MASTER_LOG_POS=4;
CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’