1

I have a Master / Slave Setup in production and from time to time I add or remove a slave and recently I also removed the master and promoted a former slave to new master.

This procedure usually involves dumping my master DB like so:

mysqldump -u root -pPASSWORD --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > /data/dump.sql

and then reading the MySQL master log position from the dump like so:

head dump.sql -n80 | grep "MASTER_LOG_POS"

The output is something like

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=107;

(Source: http://plusbryan.com/mysql-replication-without-downtime)

Now as I have done this a dozen times I noticed that while the MASTER_LOG_FILE changes, the value for MASTER_LOG_POS is always 107 and never changes even though I have created new dumps from the master every time I add a new slave, and the dumps are weeks apart. This seems odd. I have not noticed any sorts of data corruption but should the value of MASTER_LOG_POS not change with every dump from the master?

  • Are you running 5.5? Pre-GTID? – Rick James Feb 15 '16 at 23:46
  • Yes MySQL 5.5 on Ubuntu 14.04. – rewb0rn Feb 16 '16 at 8:42
1

No, this is correct. In your MySQLDump you issue a flush-logs command, which creates a new Binary log. The initial position in the binary log (after headings etc.) is 107, and always will be (unless you upgrade to a different version - then it may be something else).

  • Thanks, does that mean my other slaves already connected to the master will have a problem if the flush-logs command is issued? – rewb0rn Feb 15 '16 at 15:59
  • They shouldn't do. Because flush logs should close the current logfile gracefully, and the slaves will then read that the logfile has closed and a new one started, and simply move to the new one. You can use the mysqlbinlog app to read the contents of the binlogs (and relay logs), and you can see the opening / closing statements if you wanted to. – IGGt Feb 15 '16 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.