I’m migrating a number of internal web application databases off of a mysql server and I wanted a way to see which databases are being accessed and by which hosts.

# tshark -R "mysql.opcode == 2" -e ip.src -e mysql.schema -T fields port mysql

When run on the mysql server this produces a tab separated list of values compromised of the client ip address and the database name when a mysql client specifies a database. See the man page for tshark for more information.

Update:

This catches the circumstance where database is set on login as well:
# tshark -R "mysql.schema" -e ip.src -e mysql.schema -T fields port mysql

I’ve amassed enough mysql databases that it’s time there should be some high availability. Note that this isn’t a single huge database, it’s a pile of wordpress, request-tracker, mediawiki, etc databases. Performance isn’t the goal here, it is automatic failover in case of impending doom.

I happen to have an iSCSI san, but in the efforts of simplicity I’m looking at Heartbeat+DRBD or Heartbeat+Replication.

Most tutorials, and comments from colleagues lean towards using Heartbeat+DRBD. There is good discussion of the two, and more recent followup regarding when to use DRBD. There’s a nice little table at the bottom of this page. If you dig deeper, there are respectable comments about using what’s appropriate to the situation, the exercise of which is left up to the reader.

The problem is that mysql defaults to using MyISAM as the storage engine, which lacks a transactional journal. When your primary host crashes and your secondary host comes up, unless there’s a journal to replay you’re just assuming everything isn’t corrupt without some kind of through consistency check. Which sounds time consuming. So switch all your tables to a transactional storage engine like InnoDB?

Replication has both a slave IO and a SQL process running, which I believe avoids this, since the replication slave isn’t going to run an incomplete SQL statement if the master dies while sending it to the slave it is dropped. Which leaves you possibly behind the master, but consistent.

So I’m going to try to configure heartbeat with mysql running replication between two guests. The best information I’ve found so far is from Charles Bennington. I’ll post a followup when I’m done with that project.

Playing with drbd8 on Ubuntu, loosely following these instructions, and I ran into a couple problems.

First, you need to use a kernel that has the drbd module as there is no drbd8-module-source, -server definitely has the drbd module, -virtual did not. Instructions about building the drbd module are old.

My secondary was also stuck in a connection state of “WFBitMapT”. I noticed the secondary was Ubuntu jaunty while the primary was Ubuntu intrepid. Upgrading the primary to jaunty resolved this.

I saw the error “local disk flush failed with status -95” in the logs and wasn’t entirely sure about it but eventually found an explanation that made some sense and made me not worry about it.

drbd (/etc/init.d/drbd) doesn’t start on startup on it’s own. Most of the debugging information you’re looking for is in /proc/drbd or in your syslog output in /var/log. The only trouble is deciphering what is good and what is bad.