[SOLVED] How to restart a "MySQL slave-of-a-slave completely(!) from scratch?"
Linux - ServerThis forum is for the discussion of Linux Software used in a server related context.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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:
Code:
Got fatal error 1236 from master when reading data from binary log:
'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.000037' at 185483410,
the last event read from './mysql-bin.000037' at 185483410,
the last byte read from './mysql-bin.000037' at 185483429.'
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:
On #1, "PURGE BINARY LOGS SINCE 'today'"
"SHOW MASTER STATUS" giving magic-number x.
Synchronize database contents between #1 and #2 via mysqldump.
"CHANGE MASTER TO MASTER_LOG_POS=x" on #2, then "START MASTER."
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.
Last edited by sundialsvcs; 02-13-2017 at 03:44 PM.
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.
Last edited by r3sistance; 02-13-2017 at 01:59 PM.
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.)
Last edited by sundialsvcs; 02-13-2017 at 05:43 PM.
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:
On slave: STOP SLAVE
On master:
RESET MASTER
FLUSH TABLES WITH READ LOCK
SHOW MASTER STATUS
Note the value of the Position column, and call that number pos.
On slave: execute mysqldump using options --opt --single-transaction.
On master: after the dump has completely finished: UNLOCK TABLES
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
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.
Last edited by sundialsvcs; 02-13-2017 at 06:42 PM.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.