Properties:

Brian Aker has found general agreement with his post: "The Death of Read Replication".

Arjen Lentz says "I think Brian is right...", and Frank Mash confirmed: "what Brian says about replication, caching and memcached is very true".

Just like Video killed the Radio Star it looks like maybe Memcached killed the Replication Hierarchy!

But of course, Brian and others are talking about replication for scaling reads.

In my session on PBXT next week at the conference I will be talking about how we plan …

Over-the-Top Tales from the Trenches.
Motto: Bringing order to the chaos of every day DBA life.

So you have got your nice MySQL Master-Slave replication pair setup. Everything is sweet, then the master dies/restarts or you have a slightly extended network outage.

Your monitoring software (in our case Avail) fires off a page and you are rudely interrupted from reading the Pythian blog.

These real world interruptions, what can I say… it pays the bills.

Anyway being the rounded DBA or enlightened DBA as Babette would say, you are capable of handling any type of database. You log into the machine and check out why the slave threw an error or if your monitoring is slow, why the slave is lagging by 2 hours.

You run SHOW SLAVE STATUS\G

mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event …

A

I’m happy to report that my patch for a potentially nasty bug in the mysqlhotcopy script has been accepted into the mysql codebase. It’s a great feeling to finally contribute something, however small, back to the mysql community.

If you have a master/slave environment with multiple slaves, and you do periodic backups of your system on the slave with mysqlhotcopy, the –record_log_pos parameter will pull the wrong co-ordinates from the master and cause you to restore new copies of your database that are potentially corrupt.
An example failure scenario can be found here.

Yea, yea, yea... best practices.I spent about a week troubleshooting issues on a MySQL 5.1 replication issue where certain transactions causing duplicate key on index errors stopped replication. Easy enough to fix, right? mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> start slave;Hmm... nice until it happens almost every minute...So, after reading every link I could find on the internet,

Download Maatkit

Maatkit contains essential command-line utilities for MySQL, such as a table checksum tool and query profiler. It provides missing features such as checking slaves for data consistency, with emphasis on quality and scriptability.

This release contains major bug fixes and new features. Some of the changes are not backwards-compatible. It also contains new tools to help you discover replication slaves and move them around the replication hierarchy.

Changelog for mk-archiver:

2008-03-16: version 1.0.8

   * Added --setvars option (bug #1904689, bug #1911371).
   * Added --charset option (bug #1877548).
   * Changed short form of --analyze to -Z to avoid conflict with --charset.

Changelog for mk-deadlock-logger:

2008-03-16: version 1.0.9

   * Added --setvars option (bug #1904689, bug #1911371).
   * Added 'A' part to DSNs (bug #1877548).

Changelog for …

Suppose you have a master-master replication setup, and you know one of the tables has the wrong data. How do you re-sync it with the other server?

Warning: don’t just use any tool for this job! You may destroy your good copy of the data.

If your table is large, you’ll probably want to use a tool that can smartly find the differences in a very large dataset, and fix only the rows that need to be fixed. There are several tools that are either able to do this, or claim to be able to do this. However, most of them are not replication-aware, and are likely to either break replication or destroy data.

To see why this is, let’s look at a typical scenario. You have server1 and server2 set up as co-masters. On server1, your copy of sakila.film has correct data. On server2, somehow you are missing a row in that table. A hypothetical sync tool will compare the two copies of the data and find …

Let’s begin by assuming you have a server that runs MySQL and lots and lots of traffic flows through it everyday, let’s say… something like 50% of the size of the partition that the mysql binary logs are written to is on, then we will assume the binary log is turned on. Then we assume that expire_logs_days is not set.

What happens? Nagios/etc alerts that the partition is reaching a usage threshold because - low and behold the binary logs are filling up the partition. Tuning this variable is also important. It may need to be set to as low as 1 day, in which case I would say we need a bigger partition for binary logs, but setting it so low can cause replication problems if the slave(s) gets behind more than 1 day - god help us if it does - then those binary logs that the slave is reading are no longer available, and rebuilding replication will be next on the task list. (or using maatkit)

While I’m at it here are some good ones …

Download Maatkit

This release contains minor bug fixes and new features. Besides the little bug fixes, there's a fun new feature in mk-heartbeat: it can auto-discover slaves recursively, and show the replication delay on all of them, to wit:

baron@keywest ~ $ mk-heartbeat --check --host master -D rkdb --recurse 10
master 0
slave1 1
slave2 1
slave3 4

(Not actual results. Your mileage may vary. Closed course, professional driver. Do not attempt).

Nothing else in this release is very exciting. I just wanted to get the bug fixes out there.