Interesting article about some gotchas and things worthwhile knowing about MySQL's built-in replication system.

An in-depth article that explains how to use MySQL replication for high-availability purposes.

Video: Building a MySQL Slave and Keeping it in Sync At the Boston MySQL User Group Sheeri Cabral presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies. This link contains the slides and the videos

Informative post about backup and restore procedures, disaster recovery plans and load balancing tips.

"There's a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table." "the moral of the story. Don't use non-transactional tables in the middle of a transaction. Ever. You will only cause yourself more pain than you can possibly imagine. Instead, move the writes to the non-transactional tables outside of the transaction."

Over the weekend, I worked on a client's two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:a) how this never should have happened in the first place.b) how "slave drift" could be kept from happening.c) how this is probably keeping some businesses from using MySQL.d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.I'll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line "CHANGE MASTER" command to set up a new slave. Even so, it makes sense.It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log  []

Recently I had an interesting issue crop up. Due to an unfortunate migration incident in which involved master/master replication and not checking to see if replication was caught up, we ended up with an infinite replication loop of a number of SQL statements. "awk" helped immensely in the aftermath cleanup.The basics of the replication infinite loop were

Do you have a master-slave MySQL set up?  Ever do DDL changes on the master?  You may be hit with a serious data integrity bug.  Read on.One of our clients does a regular "rename tables" on the master to keep the current table small and archive off old data.  We'd occasionally be hit by a 'duplicate key' error on the slave on the current table and have to resolve it manually.  Digging into the issue, I managed to replicate it on demand and filed bug 39675 with MySQL, which subsequently has been verified and slated for fix, though from what it seems only in version 6.0.  The bug affects all versions of MySQL from 4.1 to 6.0.In a nutshell, here is what happens. The "rename tables" command only checks for pending transactions or locks in the current session.  If there is a pending transaction in another session on the table being renamed, the rename will succeed, but the order in which the transaction is written  []

This is a post about "SYSDATE()" and "NOW()" and "CURRENT_TIMESTAMP()" functions in MySQL.Firstly, note is that of these three, only "CURRENT_TIMESTAMP()" is part of the SQL Standard. "NOW()" happens to be an alias for "CURRENT_TIMESTAMP()" in MySQL.Secondly, note that replication does not work well with non-deterministic functions. And "hey, what time is it?" is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).You can start to see the problem here....but there's more....

This tip is to overcome a type of expensive query that I see all the time. In doing this, I uncovered a nasty bug in MySQL v5.1.ScenarioI've seen queries like these several times:SELECT COUNT(*) FROM sample WHERE status = n;Normally, this is OK, but if you have hundreds of millions of rows, only a handful of possible statuses and execute this query dozens of times per minute on an active InnoDB table, it quickly becomes a bottleneck.One SuggestionEstablis

A while ago me and Singer Wang (another DBA here at pythian) was working on a problem where the slave went from 800M to 1200 to 2700 to 3200 to ~800M again. I have to admit I haven't seen this before.The specific setting was 5.0.45 slave that also acted as a master, and the instance the experienced the problems was running 5.0.50.After upgrading to 5.0.74, it worked again. But here's a few of the show slave status while experiencing these problems:

Welcome back to my series of blogposts, "Back to Basics". Today I wanted to share a few commands that could be useful in various situations, such as delaying replication, stopping the slave (or parts of it).I'm sorry, but I will not be going through how to set up replication, so my assumption is that you already have replication working and that you want to know some of the details surrounding it.Let's begin by taking a look at "STOP SLAVE"STOP SLAVE stops the slave threads

One very helpful use of the technique Sheeri described in Remote connections without leaving the mysql shell is making sure that replication is working properly.According to the MySQL Reference Manual's section on SHOW SLAVE STATUS Syntax, it shows information corresponding to the slave thread in the slave server. When replication is broken, however, or not working properly due to network issues between master and slave, this information may not be accurate. This has improved over recent releases, but it's still not perfect.The question, then, is: how to be 100% sure (or as close as you can get to 100%) that replication is running fine? The answer, as offered by Sheeri: use "CONNECT".Example\tLog into the slave using the mysql client and issue "SHOW SLAVE STATUS:

Or, When MySQL Lies!When I do a "show slave status\\G", sometimes mysqld will lie to me and give me a wrong "Exec_Master_Log_Pos". Let me explain with a situation from last night.This is the output of "show slave status\\G" from mysql version 5.0.41-community-log:mysql> show slave status \\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXX.XXX.XXX.XXX Master_User: replic_username Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000480 Read_Master_Log_Pos: 690470773 Relay_Log_File: db2-relay-bin.000028 Relay_Log_Pos: 683977007 Relay_Master_Log_File: mysql-bin.000480 Slave_IO_Running: Yes Slave_SQL_Running: No  []

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\\Gmysql> show slave status \\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: x.x.x.x  []

Slaves can be used for:Horizontal read scalability -- take the load off a master database by spreading reads to a replicated slave.Disaster recovery -- some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).Consistent Backups -- without disrupting production usage, a slave can be used to ta

Continuent CTO Robert Hodges talks about future of database clustering and how Tungsten open source project fits in that picture

Watch out for hostname changes with replication! a blog post with facts and figures

How to simulate loads on MySQL servers using Proxy and MockLoad

The title says it all, and the explanation is very interesting. The consequences of disabling binlog due to lack of storage are catastrophic. Entertaining and worrying at the same time.