mysql master slave replication scenario

Question

We are creating a new mysql slave, but for dump we are taking from a backup server which is a slave of current master. Now to enable replication we want to connect it with the master so should I take log position from the master or from the backup server where i took the dump from.

Secondly the new server we have created is 5.6 and old server is 5.5, would there be any replication issues as well ?

Answer 1

If you want to setup slave from another slave steps would be as follows

• Flush tables with read lock to lock the slave so it should not get updates
• show slave status\G and note master co-ordinates
• Take backup now using mysqldump
• unlock tables to unlock the slave
• transfer that backup and restore it to new one
• Hope you have issues grant replication slave on master so need to use change master on the new slave and give co-ordinates noted in step-2

For details regarding master-slave version difference, shouldn't be a big issue but a good idea is to go through MySQL documentation.

Hope it helps

Answer 2

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 !!!