0

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

Kaymaz
  • 103
  • 5
  • The error message is Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' Is both Mysql but Slave A is on Debian and the other in AWS RDS MySQL. I cannot check the my.cnf of the AWS RDS. @RolandoMySQLDBA suggests something: https://dba.stackexchange.com/questions/87002/mysql-binlogs-exists-show-binary-logs-is-empty – Kaymaz Dec 09 '17 at 15:33
  • Do you intend to hook B to A (daisy-chain)? Or B to the Master? – Rick James Dec 09 '17 at 17:02
  • Master -> A -> B. When I can begin the migration, B will become Master. Old Master and A will be killed. – Kaymaz Dec 09 '17 at 18:02

2 Answers2

1

Master -> A -> B

binlog on A needs to be turned on.

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

So...

  1. Turn on binlogging on A.
  2. Stop replication from Master -> A.
  3. Dump A
  4. Load dump onto B
  5. CHANGE MASTER to point B to A.
  6. Start replication from Master -> A. Things should flow all the way to B.
Rick James
  • 78,038
  • 5
  • 47
  • 113
  • Except that the binlogs on A are not there anymore. Can I change the mysql-bin.index on A? – Kaymaz Dec 09 '17 at 18:38
  • You can't hang a slave off of A without having binlogging turned on. With A stopped, you can safely have an "empty" binlog and the index. But "no" binlog sounds like disaster - especially if A does not write binlogs after step 6. – Rick James Dec 09 '17 at 18:47
  • The backup is done during the night. And in the morning, the binlog file on A referred to in the dump file is not there anymore. – Kaymaz Dec 09 '17 at 18:57
  • 1
    What is purging it? Not MySQL in less than a day -- see expire_logs_days. Or are you referring to the "relay" logs, which are not relevant. – Rick James Dec 09 '17 at 19:55
  • There must be something but I don't find it! expire_logs_days is not set at all in the confi which means it's 0 (no delete). There is a hole of 13 binlogs missing between today 8.30am and 9pm. Hopefuly I backup the binlogs on A... – Kaymaz Dec 09 '17 at 20:11
0

I finally managed to daisy-chain.

  1. Since the binlogs were removed, I copied rsync them each 30 seconds
  2. Dump Slave A to Slave B
  3. Stop MySQL server service mysql stop
  4. Copy back the deleted binlogs on Slave A in the MySQL data folder
  5. Add the binlogs file name in [mysql-bin].index
  6. Start Slave A service mysql start and the replication
  7. Start the replication on Slave B
  8. Monitor with SHOW SLAVE STATUS on A and B
Kaymaz
  • 103
  • 5