How to restart a "MySQL slave-of-a-slave completely(!) from scratch?"
 

I have a MySQL server ("#2") that is the slave of a second server ("#1") that ... so far, at least, has continued to successfully remain synchronized with ("#0") an essential production database.

Well, even though "#1" (yay!!) continues to remain in-sync with "#0," since the past few days "#2" finds itself no longer in-sync with "#1."

Fairly quickly, the "IO" thread of #2 fails with this message: At this point in time, I simply want to succeed in (re-)synchronizing "#2" with "#1," without in any(!) way disturbing "#1's" all-important continuing relationship with (production!!) "#0!"

However, at this point I rather seem to be stuck. Here's what I foolishly tried so-far:

1. On #1, "PURGE BINARY LOGS SINCE 'today'"
2. "SHOW MASTER STATUS" giving magic-number x.
3. Synchronize database contents between #1 and #2 via mysqldump.
4. "CHANGE MASTER TO MASTER_LOG_POS=x" on #2, then "START MASTER."
5. Almost immediately, the IO thread stopped with this error.

Obviously, what I want to achieve here is for the "#1" server to forget everything about its master->slave relationship to "#2," without in any way impunging upon its vital(!) continuing "slave->master" relationship with "#0," which has not (thank gawd ...) thus-far been adversely affected.

If all your* tables are innodb, you could do a mysqldump with --single-transaction and with --master-data

If your tables aren't innodb then you need to ask yourself why... cas myisam is horrible! There is basically only a very few fringe reasons for using it at all.

Also there is a risk if you use alter table a lot: https://www.percona.com/blog/2012/03...qldump-secret/ Percona also has it's own tools (xtrabackup) for this type of job, so that is an alternative. Personally my preferred set-ups are MariaDB 10.x with the Percona Toolkit and Xtrabackup installed.

*ignore system tables that myisam, those need to be myisam as they load/are used prior to the innodb plugin.

An additional suggestion, if you have three servers. Unless there is any specific reason why the two back-ups need to be different, Galera maybe a better long term option, since it has lovely ways of handling resyncing automatically via processes called ISTs and SSTs.

I think that I really just need to get rid of those old log files, because the max_allowed_packet of the slave server is many times bigger than that of the master: 16 vs. 6 megabytes, respectively. Furthermore, the two systems have been successfully remaining in sync for a long time. I'm quite sure that it's a fluke.

RESET MASTER in step #1, instead of "PURGE BINARY LOGS," did the trick.

Upon inspection of the actual directory where logs are kept on the master machine, the old mysql-bin.##### files were not being deleted. They were still there, still being read, and still causing grief.

After resetting the master, I also had to reset the slave's understanding of what was the first log-file name. For instance:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001';

Otherwise the slave's I/O-thread would promptly complain that it couldn't find the file.

(Note that the log-file naming convention used by your server might be different: e.g. it might be "mysqld-bin...". Be sure to check the directory where these files are stored.)

I think that it might be useful if I summarized all of the steps from the preceding post to show, step-by-step, what worked:

1. On slave: STOP SLAVE
2. On master:
   • RESET MASTER
   • FLUSH TABLES WITH READ LOCK
   • SHOW MASTER STATUS
   • Note the value of the Position column, and call that number pos.
3. On slave: execute mysqldump using options --opt --single-transaction.
4. On master: after the dump has completely finished: UNLOCK TABLES
5. On slave:
   • CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001' (with the correct log-file basename that is used on your system)
   • CHANGE MASTER TO MASTER_LOG_POS=pos, providing the value for pos from step #2 above.
   • START SLAVE
6. On slave: SHOW SLAVE STATUS to be certain that the synchronization process is running smoothly, with both Slave_IO_Running and Slave_SQL_Running equal to Yes.

Q. E. D.

By the way: the message, log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master, is probably bogus. The file format is such that a binary length-value precedes a portion of data, and if somehow the processes get out-of-position in this file, they might suck-up text bytes and try to interpret that as a length, which is probably going to be seen as a nonsensically-large number.