By all means, learn from my mistakes as a DBA!

Here are 3 recent ' oops... wish I hadn't done that :/ ' mistakes I've made since joining moz that you might as well avoid (I'm sure there will be more, but they better not be the same)

Reviewing config files for MySQL, but not all of the defaults 

    We recently migrated a few MySQL databases to a new datacenter, and took advantage of the migration to upgrade the MySQL version(s) at the same time.  Of course I made sure to have another DBA review all my new config changes that were deprecated or new that we wanted take advantage of.  After things were migrated and the new applications were tested, things seemed fine - just a few bugs here and there, primarily around character set issues ( which, obviously, would be a dev problem, right? ).

Oops...  the default character set for MySQL is not utf8 !

     Lesson Learned:  

Don't just check that the configs have the correct settings; check for settings that are not there (defaults), as they are the ones that may be great for most, but perhaps not so great for you.

Restricting the # connections allowed for a user

    In order to introduce a quick 'throttling' mechanism for a job based system with a MySQL backend we implemented user resource restrictions.  It worked like a charm in staging, so we rolled it out to prod; as soon as the job connections exceeded 30 they were disconnected and had to wait till there other jobs completed, and all the devs had to do was change the user name for the job connection url.  Sweet!

   Later that day we started getting host denied errors...

Oops...  max_connection_errors matters !

     Lesson Learned:  

Make sure to take time to understand secondary effects of changes over time, as often issues don't show up immediately   That's why 'soak' or 'bake time' is not something that should be overlooked when implementing changes.

Upgrading percona toolkit and with Capistrano

    Awesome!  I got my Capistrano scripts working great - it's time to upgrade our percona toolkit on all our servers, so let's start with all our slave instances first ( it is production... ).  What's cool it with cap I can deploy it to all the slaves at once.

   What's not so awesome is I can also upgrade and restart mysql on all the slaves at once too :/

Oops...   'apt-get -y upgrade' percona-toolkit still upgrades everything, including MySQL !

     Lesson Learned:  

Make sure staging / test systems exactly mimic production when verifying deployment changes, and if you're working on a different distribution of Linux or MySQL than you are used to make sure to read the man pages!  ( apt-get install -y percona-toolkit is what I wanted, and what the man page said to do )

Yes, even those of us that have been around the block a few times still make mistakes .... 

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! 

Partition Management....(Don't forget the Management)

You'd think by now I'd know better than to cut corners. But for some reason, I still somehow seem to decide once in a while for short term gains over long term benefits...

A while back I was overloaded with stuff so I set up a year's worth of new monthly partitions in one of our reporting databases. I did not, however, chose to create any nice job to automatically create new partitions or anything like that, as I did not have time (or so I thought).

Well, 'A while back' is now 18 months ago.... Hmm... 6 months of data in one partition. Ouch.

Might not be a big deal with Oracle, but it's SQLServer 2005. Sadly, just splitting 1 day of data into a new partition takes 10 minutes and 9 GB of transaction log space. Very depressing. (At least I tested and timed it before I tried to split one month's data into a new partition).

Kills me some times. Don't put off till tomorrow what you can do today, if tomorrow might become next year... ;)

Hopefully I'll come up with a slick way to split those partitions faster... we'll see.

And yes, I'm going to create a job to add partitions in advance.... :)

MySQL: replicate-* rules should be dynamically configurable

I wonder what the best way is to get a feature request more visibility (convert a feature request to an actual work item).

We use replicate-do-db on all our slave servers , so after many, many, restarts of our slave servers, I checked the bug list for any feature requests surrounding this, and about 6 months ago, one was opened:

replicate-* rules should be dynamically configurable

Sadly, there does not appear to be a work log for it, nor any targeted version. It seems to me that it's probably a fairly common use case...

I suppose adding or removing a db from the replicate list, might require restarting the slave processes that scan binlogs for transactions to apply, or at least, some way to notify the slave processes that additional databases need to be accounted for. Then we have to make sure that we start at the right point in the binlog, right? (what if the database as been active for a while, and then I try to dynamically add it to the replicate list)

There is a worklog item for having the master filter the database replication (to reduce network traffic), which has a proposed option of allowing this to be dynamic through the use of a CHANGE MASTER command. That might work for the dynamic replicate-* rules as well...

Maybe this is an opportunity to open my MySQL Internals book, and see if I can pull those C programming skills out of my cobweb filled dba brain...

Anyway - it would be interesting to know who all out there would like this as a feature...