Maatkit Table Sync

I use different Maatkit tools almost every day, but until recently I hadn't needed to use mk-table-sync. I will briefly document what I did here so that Future Me can refer back to it.

What happened: A long running query on a slave had to be killed and skipped in order to get critical reporting back up to date. We needed to diff the tables between master and slave and fill in the holes on the slave. The table in question has an auto-increment PK, so it was an excellent candidate to use the Maatkit table sync script.

Get the tool:

wget http://www.maatkit.org/get/mk-table-sync
chmod +x mk-table-sync

I ran it from the slave db like this:

./mk-table-sync --print --user=foo --ask-pass --tables=db.table_to_fix --nocheck-triggers --sync-to-master slave_db_name

I used --print to generate a list of sql statements to run. I could have used --execute to simply have them run, but I wanted to look at the changes that would be made first. --nocheck-triggers was used because the table had triggers on it. I verified that inserting the missing rows would not affect what the triggers did, so I felt comfortable using that option.

The script very intelligently examined the table on both master and slave in small chunks to determine missing rows. There was no adverse affect in my case on either master or slave. The end result was a list of about 1400 REPLACE statement that I verified and then used to fix the problem.

Great tool!

Testing code formatting

use strict;
use warnings;
use Getopt::Long;

my $sleep_interval = 10; # default is 10 seconds
my $server_list;

GetOptions (    'interval=i' => \$sleep_interval,
                'server-list=s' => \$server_list
            );

Another reason to delete default MySQL users

One of the first things that I always do when I bring up a new MySQL instance is to delete the two users that are created with no user name or password.  I consider it an important step in securing the database.

It turns out there is another very good reason to get rid of those accounts.  This is probably an edge case, but it took me a bit of time to figure it out so I'll post it here.  Some of our developers transferred all of the user grants from a dev mysql instance they had set up to a new box for testing.  Unfortunately they hadn't removed the user accounts with an empty string as the user name, so they were transferred as well.  This lead to a case where mysql logins from the dev box would fail when attempted on the new box.

After the grants were set up, here is what they looked like on the new box:

User:	Host:	Password:
foo	10.%	[password hash]
[empty string]	10.1.1.1	[empty string - no password]

[The second one is the default account that should have been removed]

Login attempts using foo worked from all hosts except from 10.1.1.1.  What was happening was that MySQL was evaluating the incoming access request from 10.1.1.1 using the ''@10.1.1.1 credentials.  It clearly started with the most specific match on the host ip and then accepted the '' user name as a wildcard - so 'foo' was OK.  At that point the passwords would not match and an error is thrown to the user on 10.1.1.1.  

You can verify this by attempting to log in as foo from 10.1.1.1 with no password.  This lets you in with the grants for the ''@10.1.1.1 account.  Or removing the ''@10.1.1.1 user will allow the foo@10.% account to match correctly against the provided credentials.

Like I said, this is definitely an edge case - but it did provide a good learning experience for me to dig a little deeper into how MySQL evaluates incoming connections against the grant tables.  Sometimes things aren't how you would expect!

Modifying a 3 step replication chain

Some of our MySQL replication chains look like this:

Server A --> Server B --> Server C
Server A --> Server D

I needed to change the overall replication configuration so it looked like this:

Server A --> Server B
Server A --> Server D --> Server C

The trick was in getting B and D to stop at the same position. It wasn't too hard, but I got to use an option with 'start slave' that I hadn't used before.

The steps that I used were:

1) Make sure replication is caught up on B, D, and C.

2) Stop the slaves on B and D.

3) Run 'flush logs' on A. Then 'show master status' to see the new binary log number it advanced to.

4) On B and D I ran: start slave until master_log_file = 'new file above', master_log_pos = 98;
This advances B and D to the same log position (the beginning of the new binary log we started in the previous step) and then stops the sql_thread.

5) Make sure C is caught up, then run 'stop slave'.

6) Run 'reset master' on D. I do this to clean up binary logs we don't need and get a fresh start position.

7) Re-master C: change master to master_host = 'D', master_log_file = 'mysql-bin.000001', master_log_pos = 98, master_user = 'foo', master_password = 'bar';

8) Restart all of the slaves and verify replication is working as expected.

You don't need to do the step where you flush logs on A, but I prefer it because it gives me a known log file that hasn't been processed yet. I like using the position '98' as well because I know it's the beginning of a binary log file. Similarly you needn't 'reset master' in step 6 (you could use the existing log file positions), but I like to have a clean start.

Binlog Sync in MySQL

I had an interesting case today where a master had a surprise reboot. Upon recovery we saw that the slave db had processed a few records from the master that hadn't made it into the binlog on the master host. This ran contrary to my mental model of MySQL replication where statements were not picked up by the slave until written to the binary log and then pulled.

So I learned all about the sync-binlog parameter. Default behavior is to rely upon the native filesystem for writes to the binlog, so they may be in the filesystem cache before actually getting written. If your system goes down in that state those statements will have been sent to the slave, but not actually written to the master binlog.

Note that this issue also threw an error on the slave as it tried to start replicating from a binlog position that hadn't actually been written to the binlog file.

Nice to learn something new :).

Setting up MySQL Sandbox and MySQL 5.4 on OS X

After the announcement of MySQL 5.4 I wanted to set up a test environment on my Macbook Pro to check it out. It turned out to be pretty easy, but I will document the steps here for future reference:

Download the source code from mysql: http://dev.mysql.com/downloads/mysql/5.4.html#source

I did the following steps as the superuser.

After expanding the tarball I compiled it like so:

./configure --prefix=/Users/dcr/opt/mysql/5.4.0 --enable-dtrace --enable-profiling --with-plugins=all

make
make install

The next step is to download the latest MySQL Sandbox: https://launchpad.net/mysql-sandbox/+download

This also has a super easy install:

tar zxvf MySQL-Sandbox-3.0.04.tar.gz
cd MySQL-Sandbox-3.0.04
perl Makefile
make
make test
make install

Once that's done the mysql-sandbox scripts will be installed in /usr/bin.

Setting up the 5.4 sandbox is now a matter of a single command:

make_sandbox 5.4.0

This works because mysql sandbox looks for the mysql system files in ~/opt/mysql/n.n.n, where n.n.n is the version number (5.4.0 in our case).

The sandbox is created in ~/sandboxes/msb_5_4_0. From that directory you can manage it in the usual ways.

Additionally, adding other versions of mysql for use in the sandbox is easy. Just download the os x tar.gz file from mysql, expand it, and move the contents to ~/opt/mysql/n.n.n (substituting the actual version number in the path).

Using these steps I had a fully functional test instance up and running in just a few minutes (most of which was compile time for 5.4).