In MySQL 5.6, mysqldump can do that for you with --dump-slave=1 and --single-transaction. It is the same as --master-data=1, but here is how the log file and position are retrieved:
- --master-data=1: log file and position from
SHOW MASTER STATUS;
- --dump-slave=1:
Relay_Master_Log_File
,Exec_Master_Log_Pos
from SHOW SLAVE STATUS\G
.
--dump-slave=1 will run do the following:
STOP SLAVE SQL_THREAD;
- perform the dump
START SLAVE SQL_THREAD;
No need to run FLUSH TABLES WITH READ LOCK;
You also asked
Secondly the new server we have created is 5.6 and old server is 5.5, would there be any replication issues as well ?
This is only one major issue that can break replication from 5.5 to 5.6
I discussed binlog format differences between versions (See mysql replication master 5.5 slave 5.1 error on create database)
In the case to MySQL 5.5 Master to MySQL 5.6 Slave, the issue my boss recently discovered has to do with the new TIMESTAMP format. MySQL 5.6 now supports microseconds in a TIMESTAMP. MySQL 5.5 does not. When doing row-based replication and Master and Slave have configured binlog_format is either ROW or MIXED, MySQL 5.6's IO Thread will replicate the MySQL 5.5 event, storing it in the relay. If the event contains a TIMESTAMP, MySQL 5.6's SQL Thread will break trying to unpack the event from the relay log.
The only workaround for this rare event is to set binlog_format to STATEMENT on both Master and Slave. Then, set up replication.
EPILOGUE
STEP 01 : Just run this on the current slave
mysqldump --dump-slave=1 --single-transaction ... > slave_data.sql
echo "START SLAVE;" >> slave_data.sql
STEP 02 : Move slave_data.sql
to the new Slave server
STEP 03 : Run this on the new server
CHANGE MASTER TO
master_host='ipaddr_of_master',master_port=3306,
master_user='repluser',master_password='repluserpassword',
master_log_file='bin-log.000000',master_log_pos=4;
source slave_data.sql
This will setup replication and start it up for you.
GIVE IT A TRY !!!