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?
Best Answer
No, this is correct. In your
MySQLDumpyou issue aflush-logscommand, 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).