MySQL binlogs - Don't forget to do your homework!

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade...

We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind it was an issue with the binary logs, or the disk subsystem that MySQL was writing the binary logs to.

Investigation with iostat (iostat -xtc 10), we were able to narrow it down to a disk subsystem being fully utilized and queuing.  

Easy fix right?  Just change the location of the binlogs via the startup option in my.cnf and restart.

If I had done my homework, I would have known that restarting MySQL with a new location for binlogs would reset the binary log to mysql-bin.000001, and thus break replication to the slave... Oops.

No need to panic - just check the master's log position and run the following on the slave:

 mysql> change master to master_log_file=mysql-bin.000001, master_log_pos=123

Perfect - the slave caught up and was good to go - except that I neglected to recall the fact we had enabled the application and started writing to the master before I checked the log position....  Oops. (2)

Luckily, replication was up to date when we restarted, so I was able to use the handy dandy mysqldump utility to load all the transactions from the new binary log up to the point in which I checked the master:

mysqlbinlog --stop-position=123 mysql-bin.000001 | mysql   

Moral of the story: Doing your homework is usually a good thing!