MySQL Master-Slave-Slave Configuration -error 1236

Question

I currently have a Master (5.5) - Slave (5.6) set up right now and I would like to add another slave that will replicate from the existing slave.

On the existing slave:

1. STOP SLAVE;
2. SHOW SLAVE STATUS;
3. Note these values Master_Log_File: mysql-bin.018098 & Read_Master_Log_Pos: 185753950
4. FLUSH TABLES WITH READ LOCK;
5. Service mysql stop
4. Took snapshot of mysql data vol and restore it on new mysql slave server, also I changed the server-id in my.cnf on the new mysql slave.
5. service mysql start.
6. UNLOCK TABLES;

on the new mysql slave server (should replicate from the existing slave):

1. changed server-id on my.cnf
2. remove /mnt/data/mysql/auto.cnf
3. service mysql start --skip-slave-start

4.
CHANGE MASTER TO MASTER_HOST='existing slave',
MASTER_USER='xxx',
MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.018098',
MASTER_LOG_POS=185753950;

5. execute start slave

got an error:
2016-11-18 16:39:50 5736 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236)
2016-11-18 16:39:50 5736 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236
2016-11-18 16:39:50 5736 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.018098', position 185753950

Thanks !!

Answer 1

If you want to replicate from the existing 5.6 slave to another 5.6 slave, you need to setup the first 5.6 slave to behave like a master

STEP 01

Add these three(3) lines to my.cnf (under [mysqld] group header) on the first 5.6 slave

[mysqld]
log-bin=mysql-bin
log-slave-updates
skip-slave-start

STEP 02

Restart mysql on the first 5.6 slave

service mysql restart

Now, the first 5.6 slave has its own binary logs

STEP 03

Reset Binary Logs on the first 5.6 slave

mysql> RESET MASTER;

STEP 04

On the second 5.6 Slave, setup replication to the first 5.6 slave as follows:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='existing slave',
MASTER_USER='xxx',
MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
START SLAVE;

STEP 5

Start replication on the first 5.6 slave

mysql> START SLAVE;

STEP 6

Verify that Replication is running on both 5.6 Slaves

mysql> SHOW SLAVE STATUS\G

If you see Slave_IO_Running: Yes, Slave_SQL_Running: Yes, and Seconds_Behind_Master is a number (preferably 0), CONGRATULATIONS !!!

GIVE IT A TRY !!!