On Thursday January 27th at 10am PST I will doing a webinar on Tungsten Replicator together with my colleague Giuseppe Maxia.  The title is "What MySQL Replication Cannot Do.  And How to Get Around It."  Basically it is a nuts and bolts description of Tungsten Replicator capabilities like multi-master replication, failover, parallel apply, and using replication for zero-downtime upgrade.  If you have ever wanted an in-depth look at the Tungsten Replicator this is a good opportunity. 

During 2010 we implemented an amazing number of new replication features ranging from pipelines early in the year to fast disk logs, multiple replication services per process, bi-directional replication, and parallel apply by the end.  We will be building out all of …

In case you missed it here, we are very proud to announce that Drizzle’s transaction log is passing all of our tests.  For quite some time, David Shrewsbury, Stewart Smith, and Joe Daly have been putting a lot of love into the log code.  Please don’t be fooled by Dave’s praise of QA now that the storm has passed…you should have heard the names he called me and the things he plotted when we were rooting these bugs out ; )  However, now that there is a permanent record of his words, I’ll be reminding him about this post the next time my testing becomes a pain in his posterior and I feel him giving me the stink-eye in IRC (heheh)

With that said, we really have been putting tons of …

I’ve seen many a good DBA make the master of starting slaves from the position in the master.info file, most recently this week, that I want to bring it to everyone’s attention. Of course I mean the underlying issue and not the names of the DBA because that would be cruel.

In the typical scenario where this is an issue, the sequence of events is roughly the same with some small variation. A cold backup or a snapshot is restored onto a new server to build out a new slave. The binary log position from the master.info file, which is part of the backup, is used to start replication. Eventually after a short while, someone notices data discrepancies on the new slave compared to the master or replication stops due to an error.

The problem can be best looked by looking the slave status output in MySQL like below:

mysql> show slave status\G
*************************** 1. row *************************** …

A lot of people are running MySQL Master-Master replication pairs in Active-Passive mode for purpose of high availabilities using MMM or other solutions. Such solutions generally have one major problem – you have to be very carefully switching writes as if you do not do it atomically (such as some scripts continue to write to old master) or if you had slave lag during the switch you can have replication stopped with the error or silently become inconsistent. You can pick what you dislike the most.

There are people to tell you you just should use auto_increment_increment and auto_increment_offset and you would not have any conflicts, I would tell you they are wrong but there is still a good reason you may consider using these options.

Let me start by saying these options help to avoid some conflicts – if you have insert in table with …

A couple of days ago I ran into a Tungsten Replicator case where several MySQL tables became corrupted on slaves and needed to be restored from the master.   We identified the tables that had problems fairly quickly using Tungsten Replicator's consistency checks.  However, that led to another problem:  how to restore the slave tables efficiently from the master.  The MySQL server in question processes around 10M tranactions per day--there is virtually no downtime.  Though the tables were not large, we could not be sure whether they were in use. 

Fortunately, you can use a simple MySQL trick to get all the rows of a table to replicate through to slaves.  The idea is to dump the table, delete the rows, then reload it again.  The delete and subsequent reload replicate out to slaves, after which everything is consistent again.  Let's say we have a table called tpcb.history that needs to be …

Scenario Master – Master replication

MasterA is a client facing server
MasterB is a warm standby server (read only)

MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error:

MasterA has the following error in show slave status:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Solution:

Slave: stop slave;

Master: flush logs
Master: show master status; — take note of the master log file and master log position

Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;
Slave: start slave;

BackgroundNormally, I would use a comment. The first thing I would think is
CREATE PROCEDURE p1(i int) select "hello" /* This is my text */
But most client libraries will strip it.
There was a …

Over the last few months I have been pleasantly surprised by the number of people using open source builds of Tungsten.  My company, Continuent, has therefore started to offer support for open source users and will likely expand these services to meet demand.

There have also been a number of requests to add specific features to open source builds, especially for replication. We have added a few already but are now considering pushing even more features into open source if we can find sponsors.  These add to a number of great features already in open source like global transaction IDs, MySQL 5.0/5.1, basic drizzle replication, transaction filtering, and many others. 

Do you have special replication or clustering features you would like to see added to Tungsten? Specialized MySQL to PostgreSQL replication?  Management and monitoring commands?  Cool parallel replication problems?  …

I will present two talks at the MySQL Conference next April.
One is a three hours tutorial on Advanced MySQL Replication Techniques, and the other is a normal session on The art of sandboxing. Reducing Complex Systems to Manageable Boxes.
The first topic is not a first to me. But the contents are going to be fresh and new. There has been so much going on in the replication field, that the talk on this topic that I presented in 2007 looks like ancient history.
The second topic is completely new. I have often presented the result of my sandboxing efforts, but I have never thought of explaining the techniques themselves. Now that I have got some experience at reducing differently complex systems to sandboxes, I want to share the knowledge, to promote more work in this field.

The following quirky dynamic SQL will scan each index of each table so that they’re loaded into the key_buffer (MyISAM) or innodb_buffer_pool (InnoDB). If you also use the PBXT engine which does have a row cache but no clustered primary key, you could also incorporate some full table scans.

To make mysqld execute this on startup, create /var/lib/mysql/initfile.sql and make it be owned by mysql:mysql

SET SESSION group_concat_max_len=100*1024*1024;
SELECT GROUP_CONCAT(CONCAT('SELECT COUNT(`',column_name,'`) FROM `',table_schema,'`.`',table_name,'` FORCE INDEX (`',index_name,'`)') SEPARATOR ' UNION ALL ') INTO @sql FROM information_schema.statistics WHERE table_schema NOT IN ('information_schema','mysql') AND seq_in_index = 1;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET SESSION group_concat_max_len=@@group_concat_max_len;

and in my.cnf add a line in the [mysqld] block

init-file = …