Mysql Slave doesn’t find binary log from slave

MySQLreplication

I have to setup a MySQL slave on an other infrasctructure. It will become the Master once that other infrasctructure is ready.

Mysql 5.6.29
Slave A in old infrasctructure
Slave B in new one

I dumped the Slave A database by performing a STOP SLAVE;, FLUSH TABLES WITH READ LOCK then the mysqldump and restarted the slave with START SLAVE.
I've now a perfect dump of my slave A with the master binlog and its position.

I dumped the dump into the Slave B and setup the correct binlog and position for the replication.
Once started, the Slave B couldn't find the binlog. But they are all in the right /var/lib/mysql folder on the Slave A. When I say they are all there I mean I put them myself in the folder in fact, otherwise they are deleted (I don't know why).

I really don't understand what I'm missing here.

log-slave-updates is ON on the Slave A

Best Answer

Master -> A -> B

binlog on A needs to be turned on.

B must use the binlogs from A, not from the Master.

So...

Turn on binlogging on A.
Stop replication from Master -> A.
Dump A
Load dump onto B
CHANGE MASTER to point B to A.
Start replication from Master -> A. Things should flow all the way to B.

Related Solutions

Mysql – Moving the binary location of MySQL for replication

Yes, you have two options. Both require none of the data changes in any of the files during copying.

Completely shutdown the server you are going to clone. Copy it over, make relevant .cnf changes (especially server-id), start up the new instance.
If you're using LVMs you can take an LVM snapshot while the server is running and then copy that snapshot over.

If you're doing this to get a slave running, in both cases you'll want to have run show master status before shutting down/taking the snapshot so you'll know what to CHANGE MASTER TO when spinning up the new slave. I would recommend setting read_only=1, then check the position. If going the snapshot route you can set read_only=0 so the master can continue to take writes.

MySQL Slave Relay Logging but not logging Binary Log

You forgot one thing

binlog-do-db=our_database  
binlog-ignore-db=mysql  
binlog-ignore-db=test  
replicate-do-db=our_database  
log-slave-updates

The option log-slave-updates takes SQL that was read from relay logs and records them in the Slave Binary Logs when the SQL is completed.

Please be very careful. You should never use binlog-do-db and binlog-ignore-db simultaneously. I wrote about this back on Jun 14, 2012 ( Configured MySQL replication but its not working ) which points to an old Percona article on binlog-do-db being dangerous.

SUGGESTION

Of the things you are using, you only need these lines

replicate-do-db=our_database  
log-slave-updates

You really don't need binlog-do-db=our_database once you have log-slave-updates working properly.

Best Answer

Related Solutions

Mysql – Moving the binary location of MySQL for replication

MySQL Slave Relay Logging but not logging Binary Log

SUGGESTION

Related Question