LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 02-12-2017, 08:29 PM   #1
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 9,078
Blog Entries: 4

Rep: Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170
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:
  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.

Last edited by sundialsvcs; 02-13-2017 at 03:44 PM.
 
Old 02-13-2017, 01:50 PM   #2
r3sistance
Senior Member
 
Registered: Mar 2004
Location: UK
Distribution: CentOS 6/7
Posts: 1,375

Rep: Reputation: 217Reputation: 217Reputation: 217
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.
 
Old 02-13-2017, 03:37 PM   #3
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 9,078
Blog Entries: 4

Original Poster
Rep: Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170
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.
 
Old 02-13-2017, 03:46 PM   #4
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 9,078
Blog Entries: 4

Original Poster
Rep: Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170
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.
 
Old 02-13-2017, 06:36 PM   #5
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 9,078
Blog Entries: 4

Original Poster
Rep: Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170Reputation: 3170
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.

Last edited by sundialsvcs; 02-13-2017 at 06:42 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
A Win8 "slave" disk is using "UEFI secure boot" - How to copy data from it with Linux ghost123uk Linux - Software 8 08-15-2015 06:28 AM
MySQL Slave - Log queries including those executed on Slave Replay helptonewbie Linux - Server 0 04-11-2012 06:17 AM
MySQL Master-Slave Replication - How to make the slave read-only? saagar Linux - Server 1 08-31-2010 08:13 AM
can't burn DVDs, Konqueror gives "Unable to create io-slave:" geoff3 Linux - Desktop 2 12-28-2008 02:39 AM
Named slave update fails: "Server is not an authority for the domain" usernamenumber Linux - Networking 0 09-29-2004 10:42 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 05:47 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration