The Data Charmer

More Oracle ACEs for MySQL

2012-01-27T20:32:00.001+01:00

As Keith announced today, there are two more Oracle ACE Directors for MySQL expertise. In case you are wondering how an ACE Director compares to a regular ACE, here is an overview and some FAQ.

MySQL and Friends schedule at FOSDEM 2012

2012-01-18T16:46:00.001+01:00

The MySQL DevRoom at FOSDEM is ready. The schedule has been voted. Thanks to all who have participated. Now, let's make sure that the event is successful. The schedule is juicy, and not only because I have three talks in it!

Event	Speaker	Room	When

Sunday 2012-02-05
All you need to know about migrations and you never dared to ask	Ralf Gebhardt	H.1309	09:05-09:30
Sphinx User stories	Stéphane Varoqui	H.1309	09:35-10:00
MySQL HA reloaded - old tricks and cool new tools to guarantee high availability to your MySQL Servers	Ivan Zoratti	H.1309	10:00-10:25
MariaDB 5.3's query optimizer: taking the dolphin to where he's never been before	Sergey Petrunya	H.1309	10:30-10:55
How to offload MySQL server with Sphinx	Vladimir Fedorkov	H.1309	11:00-11:25
** Build simple and complex replication clusters with Tungsten Replicator	Giuseppe Maxia	H.1309	11:30-11:55
Cluster internals	Ralf Gebhardt	H.1309	12:00-12:25
Optimising SQL applications by using client side tools	Mark Riddoch	H.1309	12:30-12:55
** ~~MySQL Replication 101~~	Giuseppe Maxia	H.1309	13:00-13:25
Choosing Hardware for MySQL	Kenny Gryp	H.1309	13:30-13:55
Replication features of 2011: what they were, how to get and how to use them	Sergey Petrunya	H.1309	14:00-14:25
** MySQL creatively in a sandbox	Giuseppe Maxia	H.1309	14:30-14:55
Case Study: La Poste - Real Time, High Volume Data Warehousing Using MySQL & InfiniDB	Stéphane Varoqui	H.1309	15:00-15:25
Sphinx performance top secret	Vladimir Fedorkov	H.1309	15:30-15:55
Managing MySQL with Percona Toolkit	Frédéric Descamps	H.1309	16:00-16:25
Data Warehousing with MySQL	Ivan Zoratti	H.1309	16:30-16:55

UPDATE The schedule has changed. Speakers with more than one talk have been asked to give up one. Now I have two talks instead of three.

Time to vote for MySQL sessions at FOSDEM

2012-01-02T23:16:00.001+01:00

There is a room dedicated to MySQL at FOSDEM 2012. (Thanks to @lefred for organizing).The CfP has received 37 submissions, but there will be time slots only for 12 to 15 talks. So now it's up to the community. If you want to attend a particular talk, you should vote for it.Like in previous years, the selection of the talks is public. You can see the list of the proposals, with the instructions, which I repeat here.You can vote either publicly, using Twitter, or privately, by sending an email. Each talk proposal will be referred by the number immediately after the title in this page. This number indicates the order in which the proposals were received. In public, you should send a tweet to @opensqlcamp, indicating a maximum of 12 talks that you would like to see, in the order you like them. e.g. "@opensqlcamp #FOSDEM2012 1,2,3,4,5,6,7,8,9,10,11,12 http://bit.ly/mysql_fosdem_2012" (adding the link will help others to find the page.In private, by email at mysqlfriends AT gmail DOT com, using the same method used for Twitter. Maximum 12 talks, in the order of your preference.In both cases, votes for your preferences will result in 1 point for each talk. In case of equal voting, we will assign 12 points to the first in the list, 11 to the second, and so on. We'll do the tally, and choose the most popular ones.Anonymous votes either by Twitter or email won't be counted. If you want your vote to count, make sure your twitter account has a recognized name (or known nick) on it. If your email address doesn't spell your name, please sign the message with your real one.DEADLINE: Your votes must be entered by January 8th, 2011.

Testing new builds with MySQL-Sandbox 3.0.24

2011-12-19T15:53:00.001+01:00

MySQL::Sandbox 3.0.24 was released yesterday, with many new features.

More than vanilla MySQL

If you have missed my previous announcement, here's the gist of it. MySQL Sandbox can now deal with tarballs from either Percona Server or MariaDB. The main difference after this change is that you can now create a directory called <PREFIX>5.5.16 and make_sandbox will recognize it as well as the plain 5.5.16.

$ make_sandbox --export_binaries --add_prefix=ps \
   Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz  \
   -- --sandbox_directory=msb_ps5_5_11
unpacking Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz
[…]
installing with the following parameters:
upper_directory                = /Users/gmax/sandboxes
sandbox_directory              = msb_ps5_5_11
[…]
basedir                        = $HOME/opt/mysql/ps5.5.11
tmpdir                         = 
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_ps5_5_11

After the binary export, subsequent installations will be easier:

$ make_sandbox ps5.5.11

The same commands can be used for MariaDB. At the moment, make_sandbox does not recognize other packages, but adding them should not be a big deal, provided that such packages look like MySQL. It wouldn't work with Drizzle, because it lacks the main ingredients for MySQL installation.

High Performance sandboxes

While testing parallel replication and prefetch slaves with Tungsten Replicator, I realized that I was doing too much manual fiddling with my scripts. Since I need more performant servers, I added the basic items that I need to modify to enable a faster server. Now, using the '--high_performance' option with make_sandbox, you get a server that is much better than out-of-the-box MySQL. To avoid problems with too much RAM, I am using a default of 512 MB for InnoDB, which is not enough for really demanding tests, but at least it is a good placeholder in the sandbox configuration file, should you need to modify it.

$ make_sandbox 5.1.60 -- --high_performance
[…]
innodb-flush-method=O_DIRECT ; \
innodb-log-file-size=50M ; \
innodb_buffer_pool_size=512M ; \
max_allowed_packet=48M ; \
max-connections=350 ; \
innodb-additional-mem-pool-size=50M ; \
innodb-log-buffer-size=50M ; sync_binlog=0 ; \
innodb-thread-concurrency=0 ; log-error=msandbox.err
[…]

Standalone masters and slaves

MySQL Sandbox has had the ability of creating replicated systems for years. Yet, sometimes you need a stand-alone master server that you want to use for some odd experiment. Similarly, you may want to create a slave of a specific master without having a full replication system. One case where you would like this ability is when you want to try replicating between servers of different versions.

$ make_sandbox 5.1.57 -- --master
[…]
my_clause                      = server-id=5157 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_1_57

$ make_sandbox 5.5.10 -- --slaveof='master_port=5157' 
[…]
my_clause                      = server-id=5510 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_5_10


$ ~/sandboxes/msb_5_1_57/use -e 'show master status'
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+

$ ~/sandboxes/msb_5_5_10/use -e 'show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 5157
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysql_sandbox5510-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 420
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5157

You can download MySQL::Sandbox from either launchpad or CPAN.

How to submit a good database bug report

2011-12-10T22:17:00.001+01:00

When an open source project becomes popular, bug reports start flocking in. This is both good and bad news for the project developers. The good news is that someone is using the product, and they are finding ways of breaking it that we didn't think of. The bad news is that most of the times the reporters assume that the developers have super human powers, and that they will find what's wrong by the simple mentioning that a given feature is not working as expected. Unfortunately, it doesn't work that way. An effective bug report should have enough information that the ones in charge will be able to reproduce it and examine in lab conditions to find the problem. When dealing with databases and database tools, there are several cases, from simple to complex. Let's cover them in order.

Installation issues

This is often a straightforward case of lack of functionality. When a tool does not install what it is supposed to, it is a show stopper, and a solution must be found. In this case, it's often enough to state the environment where the installation is happening (operating system, version of the tool, version of core components such as the MySQL database used) and the command used to start the installation. The error message could be an expected failure, when the installation procedure checks for requirements and fails if they are not met. For example: "Missing Ruby interpreter". The message tells (or suggests) you what to do. Filing a bug report on an expected failure is a waste of time. You should install the missing part and try again. Even if the message is about an unexpected failure (e.g. you get a stack trace from Ruby or Java), usually the first message tells you enough to be able to find a workaround. For example, if you get an exception from Ruby complaining about a missing 'curl' command, you can file a bug report to ask for the installer to check for 'curl' in the requirements, but if you install 'curl' yourself, the installation should continue.

Simple database issues

Reporting a database bug means complaining that the DBMS is not behaving as advertising by the documentation, or as common usage dictates. If it is a missing or misbehaving functionality, the best way of showing the problem is by starting with an empty DBMS, followed by the creation of the objects needed to reproduce the issue (CREATE SCHEMA, TABLE, INDEX) and by a minimal amount of data that triggers the problem. Some information about what operating system and database version was used is probably necessary to reproduce the problem consistently.

Simple database replication issues

By simple replication we mean a vanilla master/slave topology. In this scheme, data inserted in the master will eventually end up in the slave. Bugs in this category may fail to replicate the data totally or partially, or they may cause a break in the replication flow. Reproducing them is almost as easy as with simple database bugs. If you start with an empty system and manage to reproduce the error with a short sequence of commands, it should probably reproducible by a third party. Sometimes, settings in the master and the slave are essential to reproduce the problem. In MySQL, the format of binary logs, the default database engine and SQL modes can affect replication and produce different results with the same stream of SQL commands.

Complex database replication issues

The most difficult bugs to report are the ones where the error shows up only in a given topology. While MySQL native replication offers only few options to pipe data around (single, circular, hierarchical), Tungsten replicator allows a rich set of combined pipelines that can change the outcome of a data change event, depending on the originating node and the direction it took. In these case, information o how the cluster was installed becomes essential.

Concurrency issues

This is one of the most difficult bugs to report. When an error happens only because of the contemporary action of two or more threads, there is no easy way of reporting it in a way that it can be easily reproduced. Three methods are possible:

Describe the action of the first thread, then mark the change of thread and describe the actions of the second thread, continuing in this way until you reach the error point.
If you are a developer and feel comfortable with multi thread applications, write a script that reproduces the error by running several threads (Perl, Python, and Ruby offer the best environment for this kind of tests).
If the database offers a tool to write such multi-threading tests, consider using it.

Heavy load issues

This is a more complex case of the above one. Not only you need concurrency, but a lot of it happening at the same time. Reproducing this kind or error is challenging. If you have a support agreement with the provider of the database or the tool, you may let the support engineer have a look at your running environment, to find some clues. But even in this case, the support engineers or yourself need to ultimately reproduce the case in such a way that a developer can fix the problem and test the fix. There are two methods to report this problem:

Simplification: if you can reduce the concurrency to the elements that are misbehaving, the methods for concurrency issues will apply also in this case.
Enabling a query log could lead to identifying the sequence of events that have generated the error. The log should be integrated by the DDL of the objects involved in the action (schemas, tables, triggers, views, etc).

Large data issues

If your error only shows itself with large data, there is often a logistical problem, as you can't easily provide gigabytes of data, even if there privacy and security issues weren't in the way (which usually are). There are three strategies that you can use to report such bugs:

If only the size matters, then describing the kind of data used could be enough. E.g. When a table with such fields and such indexes grows beyond X GB, then the optimizer warp drive explodes. (Don't try this at home)
Create a script to generate the data that will ultimately trigger the error. This method requires both skills and an understanding of what the error is.
You may use a publicly available database to reproduce the error (for example, the Sample database with test suite.) Just mention in the bug report where to find the database, eventually how to load it if it is not simple, and then describe the steps needed to reproduce the bug after loading it.

DOs and DONTs

DO
- Search the bug repository and the mailing lists (or forums) before submitting yours. Someone may have had the same problem before you did. (Thanks, Robert Hodges, for this important reminder.)
- Put yourself in the receiver's position, and try to reproduce the problem from a clean initial state.
- If there is a workaround, mention it: it might give a good clue to the developers.
- More information is better. Anything that can improve the identification of the bug root cause will be welcome. (But don't overdo: see below)
- If you feel that a missing feature should be useful, report it as a feature request. (Even better: suggest a patch)

DON'T
- Don't report a missing feature as a bug, unless the docs say that the feature should be there.
- Don't just send the error message without the events that generated it.
- Don't include SQL commands embedded in code.
- Don't say "my application doesn't work anymore," assuming it's the database's fault. Remember The First Rule of Programming: It's Always Your Fault
- Less is better. If there is a long way and a short way of reproducing the bug, the shorter one is better. Don't send more info just for the sake of it.
- Don't tell the developers that they are retarded. This will not increase the priority given to your bug, or your credibility.

Call for participation for MySQL events - MySQL conference and FOSDEM

2011-12-02T13:00:00.000+01:00

It's that time of the year where MySQL would be speakers are called to action. As usual, the main event is the MySQL spring conference, this year hosted by Percona. The call for participation to the MySQL Conference And Expo 2012 is still open until December 5th. To submit a proposal, you should register as a speaker and then fill in the form.
There is a conference committee which is already busy evaluating the proposals that have been submitted so far. The committee is demanding (I know for a fact, since I am in it!) and therefore, if you want to submit something, be very critical with yourself and polish your proposal as if your job depended on it!
Please read an update on Percona Live MySQL Conference & Expo 2012.

Speakers in Europe have some more duty, though. This is also the time to submit talks for FOSDEM MySQL DevRoom. Thanks to Frédéric Descamps and Sergey Petrunia, we have Room H.1309 with 150 seats on Sunday 5th February 2012, all day. The deadline to submit a talk proposal is December 26th. There is no review committee. Like we did on past editions, as soon as the talks are submitted, we will ask everyone to vote on the talks via Twitter or email. More updates will come soon. Of course, participation to FOSDEM DevRoom is not limited to European speakers. There have been several brave speakers who have willingly crossed the pond to offer their services at European conferences before, and they are welcome to repeat the experience. Submit your talk proposals now.

Never say "there is no way"

2011-12-01T08:00:00.001+01:00

Reading a recent MySQL book, I saw an example of SHOW CREATE TABLE that comes with backticks (`) around the table and column names, and a comment:

Unfortunately, there is no way to remove this from generated syntax with this command.

(Emphasis mine).
Here's how it goes:

mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Of course, there is a way!

mysql> pager tr -d '`'
PAGER set to 'tr -d '`''
mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE mytest (
  id int(11) NOT NULL,
  description varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Never say "there is no way!"

Instead of "tr -d '`'", you can use "sed -e 's/`//g'", which does the same thing.

If you are running the query at the command line, you may use the pipe directly:

$ mysql -e 'show create table test.mytest\G' | tr -d '`'
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE mytest (
  id int(11) NOT NULL,
  description varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Replication stars

2011-11-04T16:58:00.000+01:00

Working with replication, you come across many topologies, some of them sound and established, some of them less so, and some of them still in the realm of the hopeless wishes. I have been working with replication for almost 10 years now, and my wish list grew quite big during this time. In the last 12 months, though, while working at Continuent, some of the topologies that I wanted to work with have moved from the cloud of wishful thinking to the firm land of things that happen. My quest for star replication starts with the most common topology. One master, many slaves.

Fig 1. Master/Slave topology

Legend

It looks like a star, with the rays extending from the master to the slaves. This is the basis of most of the replication going on mostly everywhere nowadays, and it has few surprises. Setting aside the problems related to failing over and switching between nodes, which I will examine in another post, let's move to another star.

Fig 2. Fan-in slave, or multiple sources

The multiple source replication, also known as fan-in topology, has several masters that replicate to the same slave. For years, this has been forbidden territory for me. But Tungsten Replicator allows you to create multiple source topologies easily. This is kind of uni-directional, though. I am also interested in topologies where I have more than one master, and I can retrieve data from multiple points.

Fig 3. all-to-all three nodes

Fig 4. All-to-all four nodes

Tungsten Multi-Master Installation solves this problem. It allows me to create topologies where every node replicates to every other node. Looking at the three-node scheme, it appears a straightforward solution. When we add one node, though, we see that the amount of network traffic grows quite a lot. The double sided arrows mean that there is a replication service at each end of the line, and two open data channels. When we move from three nodes to four, we double the replication services and the channels needed to sustain the scheme. For several months, I was content with this. I thought: it is heavy, but it works, and it's way more than what you can do with native replication, especially if you consider that you can have a practical way of preventing conflicts using Shard Filters. But that was not enough. Something kept gnawing at me, and from time to time I experimented with Tungsten Replicator huge flexibility to create new topologies. But the star kept eluding me. Until … Until, guess what? a customer asked for it. The problem suddenly ceased to be a personal whim, and it became a business opportunity. Instead of looking at the issue in the idle way I often think about technology, I went at it with practical determination. What failed when I was experimenting in my free time was that either the pieces did not glue together the way I wanted, or I got an endless loop. Tungsten Replicator has a set of components that are conceptually simple. You deploy a pipeline between two points, open the tap, and data starts flowing in one direction. Even with multiple masters replication, the principle is the same. You deploy many pipes, and each one has one purpose only.

Fig 5. All-masters star topology

In the star topology, however, you need to open more taps, but not too many, as you need to avoid the data looping around. The recipe, as it turned out, is to create a set of bi-directional replication systems, where you enable the central node slave services to get changes only from a specific master, and the slave services on the peripheral nodes to accept changes from any master. It was as simple as that. There are, of course, benefits and drawbacks with a star topology, compared to a all-replicate-to-all design. In the star topology, we create a single point of failure. If the central node fails, replication stops, and the central node needs to be replaced. Instead, the all-to-all design has no weaknesses. Its abundance of connections makes sure that, if a node fails, the system continues working without any intervention. There is no need for fail-over.

Fig 6. extending an all-to-all topology

Fig 7. Extending a star topology

However, there is a huge benefit in the node management. If you need to add a new node, it costs two services and two connections, while the same operation in the all-to-all replication costs 8 services and 8 connections. With the implementation of this topology, a new challenge has arisen. While conflict prevention by sharding is still possible, this is not the kind of scenario where you want to apply it. We have another conflict prevention mechanism in mind, and this new topology is a good occasion make it happen. YMMV. I like the additional choice. There are cases where a all-replicate-to-all topology is still the best option, and there are cases where a star topology is more advisable.

Tungsten Replicator and MySQL Sandbox at Percona Live London 2011

2011-10-18T23:52:00.001+02:00

I will be a speaker at Percona Live - London 2011, and I am looking forward to the event, which is packed with great content. A whopping 40 session of MySQL content, plus 3 keynotes and 14 tutorials. It's enough to keep every MySQL enthusiast busy.Continuent speakers will be particularly busy, as between me and Robert Hodges, we will be on stage four times on Tuesday, October 25th.

Robert, at 10:15am: Teaching an Old Dog New Tricks: Tungsten Enterprise Clusters for MySQL, Bishopsgate Suite
Robert, at 1:30pm: MySQL Parallel Replication in 5 Minutes or Less, Newgate Suite
me, at 2:30pm: MySQL Replication outside the box : multiple masters, fan-in, parallel apply, Ludgate Suite
me, at 4:30pm: MySQL Sandbox: a framework for productive laziness,Ludgate Suite

This event feels good from the beginning. There are plenty of participants, many names from all over the MySQL community, covering large and small companies, experienced speakers, well known names in the MySQL engineering arena, and a wealth of topics that will make me feel sorry for not being able to attend them all. It's the usual dilemma that attendees have at this kind of conferences. Not so much at Oracle Open World 2011, where there weren't that many MySQL sessions to choose from, although it was great for networking.

Our talks

Robert will open the dances with Teaching an Old Dog New Tricks: Tungsten Enterprise Clusters for MySQL, a talk about Tungsten Enterprise, my company's commercial product, which is a professional managing tool for demanding companies.Robert, again in the afternoon, with one of the most amazing features of our open source product, Tungsten Replicator: MySQL Parallel Replication in 5 Minutes or Less. This is a feature for large replication systems where the slave can't cope with large data streams, due to the singled-thread MySQL slave. This talk will show how easy is it to plug Tungsten Replicator to a lagging slave, start parallel replication until the lag has been zeroed, and then hand over the control to the native replication again.Then it will be my turn, with a general presentation about Tungsten Replicator, the open source product. I like the idea of calling it MySQL Replication outside the box : multiple masters, fan-in, parallel apply. The reasoning is that MySQL replication, although wildly successful in the web economy of the last decade, it is also constrained by several limits, which Tungsten, acting outside the boundaries, sets free. This will be a quick intro to Tungsten and its new user-friendly installation, with a few demos.Finally, a classic presentation with some new content, on MySQL Sandbox: a framework for productive laziness. The news is that MySQL Sandbox now supports Percona and MariaDB builds. Again, some demos will be shown, with old and new features mixed together.

Upgrading Tungsten Replicator: as easy as ...

2011-09-22T22:37:00.001+02:00

When I talked about the usability improvements of Tungsten Replicator, I did not mention the procedure for upgrading.I was reminded about it by a question in the TR mailing list, and since the question was very relevant, I updated the Tungsten Cookbook with some quick upgrading instructions.A quick upgrading procedure is as important as the installer. Since we release software quite often, either because we have scheduled features to release or because of bug fixes, users want to apply a new release to an existing installation without much fuss. You can do the upgrade with a very quick and painless procedure.Let's suppose that you have installed one Tungsten Replicator cluster using this command:


#
# using tungsten-replicator 2.0.4
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
./tools/tungsten-installer \
  --master-slave \
  --master-host=r1 \
  --datasource-user=tungsten \
  --datasource-password=secret \
  --service-name=dragon \
  --home-directory=$TUNGSTEN_HOME \
  --cluster-hosts=r1,r2,r3,r4 \
  --start-and-report

If you want to upgrade to the very latest Tungsten Replicator 2.0.5, build 321, this is what you need to do.

Get the latest tarball, and expand it;
Stop the replicator;
Run the update command (this will also restart the replicator)
Check that the replicator is running again.

The actual upgrade command is in bold in the following script.


#
# using tungsten-replicator 2.0.5-321 (get it from bit.ly/tr20_builds) 
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
HOSTS=(r1 r2 r3 r4)
for HOST in ${HOSTS[*]} 
do 
   ssh $HOST $TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/replicator stop 
   ./tools/update --host=$HOST --user=tungsten --release-directory=$TUNGSTEN_HOME -q 
   $$TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/trepctl -host $HOST services
done

One benefit of this procedure, in addition to being brief and effective, is that the previous binaries are preserved.Before the upgrade, you will see:


$ ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.4
/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4

The 'tungsten' directory is a symlink to the actual binaries inside the 'releases' directory.After the upgrade, the same directory looks like this:


ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.5-321

/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:06 tungsten-replicator-2.0.5-321

If you did some manual change to the files in 2.0.4, you will be able to retrieve them. Upgrading from earlier versions of Tungsten Replicator is not as smooth. Since we changed the installation format, it has become incompatible from previous versions. Clusters running TR 2.0.3 need to be reinstalled manually. The next upgrade, though, will be much faster!

My three MySQL sessions at OOW 2011 - and much more

2011-09-22T12:25:00.001+02:00

Oracle Open World 2011 is approaching. MySQL is very well represented.Sheeri has put together a simple table of all the MySQL sessions at OOW, which is more handy than the Oracle schedule.I will be speaking in three sessions on Sunday, October 2^nd.

Sunday, 9am MySQL: Don't Be a Rookie Forever—Be in Command (Line)I have given this talk before, as a tutorial at the UC in 2010 and at FrOSCon one month ago. It is one of the most rewarding sessions ever. The attendee were very interested. This will be a short version of the tutorial.
Sunday, 10:15am >MySQL: Jailbreaking MySQL Replication.This is related to my job at Continuent. It will a showcase of Tungsten Replicator, with quick examples of how to build replication clusters with multiple masters, multiple sources, chained clusters, parallel replication.
Sunday, 2:45pm Bridging MySQL and Oracle Databases with Open Source Replication.In this session, together with Robert Hodges, I will show how you can replicate from MySQL to Oracle and PostgreSQl using Tungsten Replicator.

There are 47 MySQL sessions in total. You can see them in Technocation summary or get the Oracle focus on mysql pdf.There are huge expo halls at OOW. Among them, there is also MySQL. The MySQL Community booth, manned by volunteers, is at Moscone West, Level 2 Lobby. Other MySQL booths are listed in the Technocation summary.On the social side, Oracle ACEs will have a dinner on Sunday evening, and MySQL Oracle ACEs will have another gathering on Monday evening.On Tuesday, October 4th, there is a MySQL Community reception. It's free. You don't need a OOW pass to attend, but registration is required.

Chaining Replication Clusters

2011-09-19T02:16:00.001+02:00

MySQL built-in replication includes a concept called relay slave, which allows you to create hierarchical database clusters.You can do the same thing with Tungsten, and this can be done in more than one way.Let's start with two distinct clusters. We can follow the recipes in the Tungsten Cookbook to install a master / slave cluster in three separate hosts and a Tungsten sandbox containing another master/slave cluster.Now, we want to make the master in the sandbox a slave of the master in the first cluster, as illustrated in the figure below.Notice that the recipe works in exactly the same way for two distinct clusters on separate hosts. We are using one sandbox to minimize the number of hosts involved.To install the bridge between the two cluster, you go to the directory where Tungsten was installed for the master in the sandbox, and run ./tools/configure-service. The purpose of this command is to create a second service in the master, a service that will act as a slave, and fetch data from the other cluster.To do so, we need to provide some information to the service installer, the most important of which are:

the local-service-name, a.k.a. who's the boss, will tell the replicator that this service will live with the 'tsandbox' service, which is the local master.
the role tells the replicator that this service will fetch data;
the master-thl-host is the address where to find a master capable of feeding data to this new slave;
the master-thl-port and thl-port options make sure that the service uses one port for its own dispatching and another one to get data from the master.


cd $HOME/tsb2/db1
./tungsten/tools/configure-service -C \
  --local-service-name=tsandbox \
  --thl-port=12111 \
  --role=slave \
  --service-type=remote \
  --master-thl-host=r1 \
  --master-thl-port=2112 \
  --datasource=127_0_0_1 \
  --svc-start \  
  dragon

After this connection, every change in the first cluster master will be replicated to all its slaves, one of which happens to be a master, which will then distribute the same data to all its slaves. So we have a cascade hierarchical replication cluster, similar to what we can have with MySQL native replication.But Tungsten can do something more than that. In MySQL replication, you need to enable a slave to become a relay-slave. In Tungsten, you don't need to do it.Using a very similar command, I can connect to a slave of the first cluster, instead of the master, and the final result will be exactly the same.


cd $HOME/tsb2/db1
./tungsten/tools/configure-service -C \
  --local-service-name=tsandbox 
  --thl-port=12111 \
  --role=slave \
  --service-type=remote \
  --master-thl-host=r3 \
  --master-thl-port=2112 \
  --datasource=127_0_0_1 \
  --svc-start \  
  dragon

In my presentations, I call this feature "slave with an attitude". Thanks to Tungsten global transaction ID, a slave can request data to any host. Since the data is not labeled in terms of log files and position (as it is in MySQL), but in terms of sequence numbers, a slave ch ask any server for a given sequence number, and that number identifies a transaction unequivocally.

Quick recipes for database cluster building

2011-09-16T23:33:00.001+02:00

One lesson learned in more than two decades working in this industry is that most of the IT professionals are impatient, want to achieve results immediately, and, most importantly, they don't read documentation. Much as the average geek is happy to answer many requests with a dismissive RTFM, the same geeks are not as diligent when it comes to learning about new or updated technologies.For this reason, there is a kind of documentation that is very much appreciated by busy and impatient professionals: cookbooks. And I am not talking about food. Geeks are not known for being cooks ⁽¹⁾ and they like fast food. I am talking about collection of technical recipes, short articles where a problem is briefly stated, and a direct solution is shown.Working with Tungsten Replicator, I am constantly amazed at all the things you can do with it, and at the same time, I am amazed at how so few people read the documentation. Since I want more users to be aware of the goodies, and being aware of the geeks' aversion to regular docs, I have started putting together a Tungsten Replicator Cookbook, where users can quickly find the recipe to build their cluster of choice.The problem is stated in one short paragraph, and the solution is outlined with code in the shortest possible way. Can't get any lazier than this! Or maybe you can, but I haven't reached that level yet. I hope I have found a good balance.Some of the recipes that the cookbook offers are:

Install a master / slave cluster
Install a master slave directory with customized parameters
Install more than one Tungsten Replicator in one host
Install a direct slave with parallel replication
Taking over replication from a MySQL slave in direct mode
Install bi-directional replication
Install bi-directional replication with additional slave
Install a three masters replication
Install a four masters replication
Modify one or more properties with the installer
Add one slave to an existing master

In addition to the cookbook, we have inaugurated a sample of another popular literary genre, namely Troubleshooting recipes.When things go wrong (and they usually do when you are dealing with something new, you want a quick answer to your problem. These troubleshooting items are aimed at making such quick answer readily available. Both projects are moving targets. We will adjust as the project grows. Contributions and comments are welcome. If you have suggestions on how to improve these documents, you can use the mailing list.⁽¹⁾ With some notable exception, I must say. I have a reputation as a good cook. But then, I represent a minority in so many ways.

Welcome, MySQL commercial extensions

2011-09-16T10:01:00.002+02:00

I saw yesterday that MySQL has finally done the right thing, and announced new commercial extensions.
What this means is that paying customers receive something more than users who get the community edition for free.
Believe it or not, when I was working in the community team at MySQL, I was already an advocate of this solution. You may see a contradiction, but there isn't. I would like to explain how this works.

An open source product needs to be developed. And the developers need to get paid. Ergo, the company needs to make money from that product if it wants to continue developing it. Either that, or the company needs to sell something else to pay the bills. (Let's not get into the argument that a pure open source project with universal participation is better, faster, or more marvelous: MySQL was never that, not with Oracle, not with Sun, and not when it was an independent company. If you want a extra-super-ultra open project, go with Drizzle. With MySQL, we need to continue reasoning with the raw material at hand.)
When MySQL was scaling its business, it realized that many customers were not willing to pay for support and consulting alone. To expand the business beyond the simple offer of services, the company created MySQL Network, which soon evolved into MySQL Enterprise, with the addition of the MySQL Monitoring tools and a fast upgrade plan. This was a good proposal for small to medium customers, but not as good for customers with large installations. When you deploy thousands of MySQL servers, you really don't want to upgrade every month. Anyway, for some time, the value proposition from MySQL was that the community users would get one release twice a year, and the paying customers would get one every month.
As a community manager, I strongly objected to that formula, not only because it hurts the community, but also because it hurts customers. When the release is exposed to millions of free users before it goes to the paying customers, chances are that serious bugs are discovered by the community and fixed in due time, before it hurts a high profile customer and needs to be fixed in a hurry at higher cost. One of the main values of MySQL is that it's that its large community adoption and feedback increases stability. Fortunately, I was not the only one who believed that larger distribution is valuable for customers, and the decision was reversed at the end of 2008.
In that period, I and other employees recommended a different value proposition for our customers. Instead of selling fast upgrade plans (which become a liability), MySQL could develop some reserved features that would be given only to paying customers.
There are two problems with reserved features, though: you need to develop them internally. You can't start them in the open, asking the community to test them for bugs, and then give them only to customers when they are ready (There was a faux pas in that direction in early 2008, but it was promptly retracted). These features must be developed as closed source, and tested only internally. The second problem is that MySQL had little internal QA manpower when this discussion arose.
There was another issue, namely that the code base for the next intended version (the fabled 6.0) was brittle. After 2 years in alpha stage, there was little to show for the effort. In parallel to the Oracle acquisition, two important things happened: version 6 was abandoned, and a new effort was started, using the more stable version 5.x as a code base, and a new developing model was launched, based on milestones and robustness.
This new foundation, combined with the injection of experienced QA personnel from the ranks of Sun and Oracle, made the project ready to offer reserved features to customers, while continuing the development of a lot more features for the community edition.
From a community standpoint, I welcome the commercial extensions. It means that the MySQL project will get more revenues, and be able to sustain the public release more easily. In my opinion it's the logical evolution of the project, and it's what MySQL should have done already years ago if it had had the manpower.
There are already detractors who see this release as a sign of the apocalypse. They probably want to see only this one feature in the commercial arena, dismissing the dozens of new features released to the general public under Oracle stewardship. I refuse to enroll in the chorus of critics who judge Oracle on prejudice. I am so far satisfied with what Oracle has done with MySQL. My opinion is based on facts, and since the facts are in favor of the community, I am pleased to say so. If future facts will make me change my opinion, I will say it. But now, I want to say thank you to the MySQL team at Oracle!

The happiness of failing installations

2011-09-08T18:38:00.001+02:00

When you set-up the same software several times (for you or for your customers), you want that software to install quickly and reliably, and you are generally happy when everything works as expected.
In this context, a failing installation is when the installation process exits unexpectedly, and you are left with an error message and the prospect of looking at the manual to find out what was it.

A failing installation is unpleasant, you'd say, and I concur. But do you know what's more unpleasant than a failing installation? It's an installation that succeeds, only to fail silently the first time you try using the application.

Looking at this enhanced definition, it is no surprise that I assert to find happiness in failure. And I have practical reasons for my claim. When I first tried Tungsten Replicator installation, it succeeded. And to my chagrin, the application did not work. I had to dig the reason for not working from the logs, and from that reason I had to figure out what I had done wrong. For example, the log might say "file not found mysql-bin.000003", and from that piece of information I had to figure out that I forgot to make the binary logs directory group readable, so that the 'tungsten' user could see the logs.
But a "successful" installation with later failure often meant that a clean shut down was not possible, and then I had to become an expert at cleaning up messy installations.
The next installation may get past the failure point, and possibly fail (again silently) for a different reason. Sometimes, I had to install four or five times until I get to the working and stable point. And then I'd install on another server, and I made a different mistake (or I forgot to apply the cure for a known mistake) and the stream of successful installations with hidden failures continued for a while.

With the above reminiscences, I am very happy to report that now you can install Tungsten Replicator with the near assurance that when something goes wrong, the installation does not start, and you are given a clear list of what was wrong.
The installer runs a long list of validation probes, and it doesn't stop at the first validation failure. It will try its best to tell you what you should do to reach a satisfactory installation, giving you a detailed list of everything that doesn't match up.
Not only that: the installer checks the requirements on all the servers in your intended cluster, and the installation does not start anywhere until you meet all the requirements in all the servers.

That's why, when my installation fails, I feel very happy, knowing that I won't have to clean up a messy server, and when I fix the problem that made the installation fail, my application will most certainly work.

Tungsten Replicator 2.0.4 released: usability and power

2011-09-07T00:00:00.001+02:00

It has been a bumpy ride, with dozens of issues opened and resolved, but we finally feel that Tungsten Replicator 2.0.4 is ready for prime time.There have been quite a lot of changes. Most notably, the replicator is much faster, especially when it comes to parallel replication, and it is much easier to install, thanks to its new integrated installer, which can validate all the requirements to install the replicator, and suggest remedies when the requirements aren't met. This new installer is so good, in fact, that calling it installer is an insult. It is a legitimate cluster builder, able to install a full fledged cluster from a central location.

Probably equally important, we have caught up with the documentation, and now you can install several replication topologies following detailed instructions from the docs. You will find both HTML and PDF guides, with the steps to install straight master/slave systems, or direct slave takeover, or bi-directional replication.The binaries are available in the project's Downloads page. Later on, you will find the most updated (and possibly less bug-infested) binaries in our build server list.The Release_Notes list all the issues that have been closed since we released 2.0.3. The advanced users will especially appreciate an innovation introduced in the installer, which now allows users to define one or more of --property=key=value. Using this option wisely, you can now customize the replication properties straight at the start. What used to require several commands and a restart of the replicator right after the installation, now flows smoothly and quickly with one single command.With this release, Tungsten Replicator is closer to become a tool for mass consumption. The old installation method (which we have deprecated and renamed, to discourage anyone from using it) required time, constant attention, and it was unforgiving. The new one will let you make your mistakes freely. If something is amiss anywhere in all the servers where you are installing, it won't install and it will tell you what went wrong. This is probably my favorite feature, because it allows Tungsten to be used by less experienced users.Now it's up to the users. We have no illusion that the product is bug free, and we want to hear from users who try it and report on Issues.

Finding tables without primary keys

2011-09-04T23:34:00.001+02:00

I was checking a third party server, and I needed to find if there were tables without primary keys. This is important to know, not only because the lack of primary keys affects performance and data accuracy in general, but also because in row-based replication performance can degrade beyond belief when updating tables without primary keys.Anyway, I did not remember off the bat any method to get this information from a server with thousands of tables, and thus I went to find a solution on my own.My first instinct called for using the COLUMNS table from the INFORMATIOn_SCHEMA, and so I came up with this query, where I sum the number of columns that are inside either a PRIMARY or UNIQUE key and filter only the ones where such sum is zero (i.e. no primary or unique keys):


select 
    table_schema,table_name 
from  
    information_schema.columns  
group by 
    table_schema,table_name   
having 
    sum(if(column_key in ('PRI','UNI'), 1,0)) = 0;

This query got the job done, and it was quite quick as well.Then, since I was chatting with Sheeri Cabral about other matters, I asked her if she could come up with an alternative solution. She suggested a LEFT JOIN between the information_schema.tables and information_schema.statistics, which I translated into this query:


select 
    t.table_schema, t.table_name 
from 
    information_schema.tables  t 
    left join information_schema. statistics s 
       on t.table_schema=s.table_schema and t.table_name=s.table_name 
       and s.non_unique=0 
where 
    s.table_name is null;

This query works on the principle that it removes from the tables list all the ones for which there is no corresponding table in the statistics table.This query also works. Using both queries in a relatively empty server did not show any significant difference. But since I knew that I had to use this method on a very busy server, with a lot of tables, I quickly created 1,000 databases, each containing 5 tables, two of which did not have any primary or unique key.Now came the first surprise.The query with GROUP BY took about 0.5 seconds, while the one using LEFT JOIN used 11 seconds.I was about to congratulate myself for my acumen, when I realized that, in addition to schema and table names, I also needed the table engine.For the second query, that is not a problem. Adding the engine to the columns list works OK, and the query runs in 11 seconds like before.The first query, though, can't list the engine. There is no 'engine' in the COLUMNS table. So I needed a JOIN. Thus my query became


select 
    t.table_schema,t.table_name,engine 
from 
    information_schema.tables t 
    inner join information_schema .columns c  
        on t.table_schema=c.table_schema and t.table_name=c.table_name 
group by 
    t.table_schema,t.table_name   
having 
    sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

Guess what? This query ran in 17 seconds (!). So much for my instinct!Joins without keys are not efficient in MySQL, and tables in the information schema are no exception.If anyone has a more efficient method of getting a list of tables without primary key (the list including schema name, table name, and engine), I am curious to know.

Primary keys from experience

2011-09-02T10:40:00.000+02:00

From time to time I see articles in defense of natural primary keys against surrogate keys.I don't take an immovable stand on either side, as I have seen good cases for both. In general, I like the idea of a natural primary key, when I see one that it is really natural. Quite often, though, a natural primary key has proved itself faulty. And most of the times, when such faults happened, it was because of limited understanding of the data. I like to describe this kind of misinformation as data analysis culture clash.When choosing a natural primary key, one should consider which element, or group of elements, are unique in a given set. Not only that, they must be immutable, at least within that set.For example, in a group of people, we may assume that a combination of name, surname, date and place of birth is a good natural primary key. Well, no. It isn't, for several reasons. Read on for a few real cases.If we rule out the above combination, perhaps we could use the Social Security Number as a valid key? Not really.The fact is that the above assumptions work well if we consider only people from the same country in current times. If we extend our data definition to include people from different countries, or historical records, then the assumption collapses.A practical case: The birth place. This is a fair assumption. In combination with other elements (e.g.: date of birth, name, and surname) it can provide good basis for unique and immutable records. If you consider people in the United States today, you are right. Even in the UK, or Italy, or France. But try applying this method to places with recent political changes due to war or revolutions, and suddenly the name of the town may suddenly change. What was before VictoryBurg in Oppresslandia is now known as Heroes City in Freelandia. And this happens now, in the 21st century. If your set includes historical data, these occurrences may become frightfully frequent.Speaking of historical times, if you are dealing with really old records, you may want to consider that dates are less than immutable. The way we count days in most Western countries is called the Gregorian calendar, which was adopted by a handful of countries in 1582. Dates before October 5, 1582 and after October 14, 1582 are recorded using two different calendars. It is inconvenient, but if you know that you may avoid wrong date calculations. Simple, isn't it? Not really. If your records include data from different countries, you will have to take into account when this calendar was adopted. Just to give a few examples, England adopted the Gregorian calendar in 1752, Japan in 1873, Russia in 1918, Turkey in 1926. When dealing with people from different countries, you may be tempted to use citizenship as an immutable property. That may work, if you consider citizenship at birth and are prepared to keep the names of not currently existing states. If, instead, you get the citizenship from the employee's passport, you may incur in one or more of the following cases (all happened in practice during my work with an international organization):

Some employees came to work with the passport they had before the political change, and now that the country has split, they have different passports, depending on which side of the new border they live.
Some employees have parents from different countries and are entitled to more than one passport. They came to work initially with one passport, and came again a few year later with a different one because of different benefits.
Some employees started working with a given citizenship, and then they got a different one because of marriage, political asylum, or other legal means.
Some employees became stateless. The equivalent of NULL in a table field.

Coming to the Social Security Number, the assumption of uniqueness fails for the same reason that citizenship does. It's even more frequent, because of people working abroad and paying their taxes in a foreign country, so they had two or more SSN or equivalent to show. But this element failed for another reason, i.e. because it is not immutable. It is supposed to be, but in some cases it happens that, due to clerical mistakes, the SSN issued in a given country is wrong, and needs to be changed. It happens in Italy, where your SSN (called "codice fiscale", or fiscal code) must match your name, surname, place, and date of birth. If any of these elements was wrong when the code was generated, the code needs to be done again. It's a painful process that requires a court order, but the result is that the item is not immutable.Other elements that I have seen used wrongly for primary keys, either standalone or as key components, are: telephone numbers (they can change, and after being changed they can be assigned to other people), email addresses (they are unique, but they can easily change, or they can be abandoned when the person changes provider, or company, or both), gender (it can change), surname (it can change, legally, for male and female, depending on country and conditions).Summing up, a sane amount of skepticism should be used when considering if an element can be used in a primary key. Depending on the environment, the element can be safe or it can become a nightmare when the database grows from a neighborhood business to an international venture.

CodeBits - An event of competitive innovation

2011-08-15T21:36:00.001+02:00

It was my pleasure and privilege to attend Codebits in 2009. As Roland Bouman says, its talk choice method is based on public voting, and therefore everyone cha have contribute to the schedule.But that is not the main reason for attending this extraordinary event. It is not just a conference. It's an innovation fest. For 1 and 1/2 days, it's a conference, where the speakers are encouraged to bring to their audience the most innovative and inspiring talks. In the afternoon of the second day, the event becomes a competition, where the teams that have registered will have 24 hours to bring a project to completion, and they have to start and finish within the allotted time. The project can be anything, and I have seen quite a lot of exciting stuff rolling live in the huge pavilion: I could hardly ignore robotics, as these little mechanical smurfs were running all over the place and you would have to be careful not to squash them when you walked.There was plenty of occasions for planning of great projects, together with attempts at improving social relations, and mixing up with big brother.There were projects based on 3D printing, and less broad projects like all-seasons keyboards.A very popular session, followed by practical workshops was lock picking. I attended one of them, learned how to pick simple and less simple locks, and I brought home some lockpicking tools.On a more technical level, I was there with Lenz Grimmer and Kai Seidler, we spoke about MySQL and other cool things, and we had lots of fun for three days.Besides the teams hacking away at their projects, there were several teams showcasing technology that had been developed by winners of the previous years, such as 3D television and intelligent phone networks. In short, This was an inspiring event, which I can warmly recommend.

Usability improvements in Tungsten Replicator 2.0.4

2011-08-11T18:19:00.002+02:00

If you love a software product, you should try to improve it, and not be afraid of criticizing it. This principle has guided me with MySQL (where I have submitted many usability bugs, and discussed interface with developers for years), and it proves true for Tungsten Replicator as well. When I started working at Continuent, while I was impressed by the technology, I found the installation procedure and the product logs quite discouraging. I would almost say disturbing. Fortunately, my colleagues have agreed on my usability focus, and we can enjoy some tangible improvements. I have already mentioned the new installation procedure, which requires just one command to install a full master/slave cluster. I would like to show how you can use the new installer to deploy a multiple source replication topology like the following: The first step is to install one master in each node. I can run the commands from node #4, which is the one that will eventually receive the updates from the remote masters, and where I need to install the slave services:

TUNGSTEN_BASE=$HOME/newinst

SERVICES=(alpha bravo charlie delta)

REPLICATOR=$TUNGSTEN_BASE/tungsten/tungsten-replicator/bin/replicator



for N in 1 2 3 4

do

    INDEX=$(($N-1))



  ./tools/tungsten-installer \

    --master-slave \

    --master-host=qa.r$N.continuent.com \

    --datasource-user=tungsten \

    --datasource-password=secret \

    --service-name=${SERVICES[$INDEX]} \

    --home-directory=$TUNGSTEN_BASE \

    --cluster-hosts=qa.r$N.continuent.com \

    --start-and-report

done

The above loop will install a master (remotely or locally) in the four servers. Then I need to create the slave services. To do it, I use the updated configure-service in the tools directory.

TUNGSTEN_TOOLS=$TUNGSTEN_BASE/tungsten/tools

COMMON_OPTIONS='-C -q 

    --local-service-name=delta 

    --role=slave 

    --service-type=remote 

    --allow-bidi-unsafe=true 

    --datasource=qa_r4_continuent_com' 



$TUNGSTEN_TOOLS/configure-service $COMMON_OPTIONS --master-host=qa.r1.continuent.com  alpha 

$TUNGSTEN_TOOLS/configure-service $COMMON_OPTIONS --master-host=qa.r2.continuent.com  bravo

$TUNGSTEN_TOOLS/configure-service $COMMON_OPTIONS --master-host=qa.r3.continuent.com  charlie 



$TUNGSTEN_BASE/tungsten/tungsten-replicator/bin/replicator restart

$TUNGSTEN_BASE/tungsten/tungsten-replicator/bin/trepctl services

These commands create the slave services locally in Delta. After restarting the replicator, a simple test will be creating something different in each master, and check that the data has replicated to the single slave. The latest improvement in matter of usability is the simplification of the replicator logs. Until a few days ago, if you had an error in the replicator, you would get a long list of not exactly helpful stuff. For example, if I create a table in a slave, and then create the same table in the master, I will break replication. The extended log would produce something like this:

INFO   | jvm 1    | 2011/08/11 18:10:52 | 2011-08-11 18:10:52,216 [tsandbox - q-to-dbms-0] ERROR pipeline.SingleThreadStageTask Event application failed: seqno=1 fragno=0 message=java.sql.SQLException: Statement failed on slave but succeeded on master

INFO   | jvm 1    | 2011/08/11 18:10:52 | 2011-08-11 18:10:52,217 [tsandbox - Event dispatcher thread] ERROR management.OpenReplicatorManager Received error notification, shutting down services: Event application failed: seqno=1 fragno=0 message=java.sql.SQLException: Statement failed on slave but succeeded on master

INFO   | jvm 1    | 2011/08/11 18:10:52 | com.continuent.tungsten.replicator.applier.ApplierException: java.sql.SQLException: Statement failed on slave but succeeded on master

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at com.continuent.tungsten.replicator.applier.MySQLDrizzleApplier.applyStatementData(MySQLDrizzleApplier.java:183)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at com.continuent.tungsten.replicator.applier.JdbcApplier.apply(JdbcApplier.java:1233)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at com.continuent.tungsten.replicator.applier.ApplierWrapper.apply(ApplierWrapper.java:101)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask.runTask(SingleThreadStageTask.java:498)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask.run(SingleThreadStageTask.java:155)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at java.lang.Thread.run(Unknown Source)

INFO   | jvm 1    | 2011/08/11 18:10:52 | Caused by: java.sql.SQLException: Statement failed on slave but succeeded on master

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at com.continuent.tungsten.replicator.applier.MySQLDrizzleApplier.applyStatementData(MySQLDrizzleApplier.java:139)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       ... 5 more

INFO   | jvm 1    | 2011/08/11 18:10:52 | Caused by: java.sql.SQLSyntaxErrorException: Table 't1' already exists

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at org.drizzle.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:78)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at org.drizzle.jdbc.DrizzleStatement.executeBatch(DrizzleStatement.java:930)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at com.continuent.tungsten.replicator.applier.MySQLDrizzleApplier.applyStatementData(MySQLDrizzleApplier.java:125)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       ... 5 more

INFO   | jvm 1    | 2011/08/11 18:10:52 | Caused by: org.drizzle.jdbc.internal.common.QueryException: Table 't1' already exists

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at org.drizzle.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:500)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at org.drizzle.jdbc.internal.mysql.MySQLProtocol.executeBatch(MySQLProtocol.java:546)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       at org.drizzle.jdbc.DrizzleStatement.executeBatch(DrizzleStatement.java:917)

INFO   | jvm 1    | 2011/08/11 18:10:52 |       ... 6 more

INFO   | jvm 1    | 2011/08/11 18:10:52 | 2011-08-11 18:10:52,218 [tsandbox - Event dispatcher thread] WARN  management.OpenReplicatorManager Performing emergency service shutdown

INFO   | jvm 1    | 2011/08/11 18:10:52 | 2011-08-11 18:10:52,219 [tsandbox - Event dispatcher thread] INFO  pipeline.Pipeline Shutting down pipeline: slave

INFO   | jvm 1    | 2011/08/11 18:10:52 | 2011-08-11 18:10:52,219 [tsandbox - q-to-dbms-0] INFO  pipeline.SingleThreadStageTask Terminating processing for stage task thread

INFO   | jvm 1    | 2011/08/11 18:10:52 | 2011-08-11 18:10:52,219 [tsandbox - q-to-dbms-0] INFO  pipeline.SingleThreadStageTask Last successfully processed event prior to termination: seqno=0 eventid=mysql-bin.000002:0000000000000426;20

Did you see the reason for the error? No? Neither did I. I would need to open the THL, look for event #1, and determine what it was. Instead, the new user.log looks like this:

2011-08-11 18:10:52,216 ERROR Received error notification: Event application failed: seqno=1 fragno=0 message=java.sql.SQLException: Statement failed on slave but succeeded on master

Caused by : java.sql.SQLException: Statement failed on slave but succeeded on master

Caused by : Statement failed on slave but succeeded on master

Caused by : Table 't1' already exists

Caused by : Table 't1' already exists

2011-08-11 18:10:54,721 INFO  State changed ONLINE -> OFFLINE:ERROR

2011-08-11 18:10:54,721 WARN  Received irrelevant event for current state: state=OFFLINE:ERROR event=OfflineNotification

That's much better. It is not perfect yet, but it will be soon. Right now, it tells me what is wrong without forcing me to go hunting for it amid hundreds of stack trace lines. Give it a try, using the latest replicator build.

Call for disclosure on MySQL Conference 2012

2011-08-10T14:25:00.002+02:00

Percona has announced Percona Live MySQL Conference and Expo 2012. Kudos for their vision and entrepreneurship. I have seen comments praising their commitment to the community and their willingness to filling a void. I have to dot a few i's and cross some t's on this matter.

That was not the only game in town.

By the end of June, there were strong clues that O'Reilly was not going to organize a conference. The question of who could fill the void started to pop up. The MySQL Council started exploring the options for a community-driven conference to replace the missing one. The general plan was along the lines of "let's see who is in, and eventually run a conference without the big organizer. If nobody steps up, the IOUG can offer a venue in Las Vegas for an independent MySQL conference". The plan required general consensus among the major players, and therefore we started asking around about availability and participation. Percona did not answer our requests. They delayed the meeting, and in the meantime we continued preparing for the plan B of a conference in Vegas. Then some of us received a message from Percona, pre-announcing a conference in Santa Clara. No offer to gather a broad participation from other entities. No sign of wanting to do a neutral event, i.e. an event not tied to a single company.

Some background

That was puzzling, because I recall vividly how Baron Schwartz and Peter Zaitzev advocated strongly in favor of an independent conference, not so long ago:

The conference is organized and owned by MySQL, not the users. It isn’t a community event. It isn’t about you and me first and foremost. It’s about a company trying to successfully build a business, and other companies paying to be sponsors and show their products in the expo hall.
Baron Schwartz, April 23, 2008.

I would like to see the conference which is focused on the product users interests rather than business interests of any particular company (or personal interests of small group of people), I would like it to be affordable so more people can attend and I’d like to see it open so everyone is invited to contribute and process is as open as possible.
Peter Zaitzev, April 23, 2008.

A call to disclosure

I understand the business motivation to organize a conference with your company name in the title, while at the same time leveraging the wide MySQL community. However, if I have to judge by the organization of previous Percona Live events, I don't see any of the benefits that were advocated three years ago. I see a business conference that is inspired to the same principles that Percona was criticizing in 2008. What is it then? If it is supposed to be a community conference, let's call it "MySQL Conference" and ask for broad participation. There are plenty of people in the community who are willing to help and make the event a success, not only for the benefit of Percona, but for the global benefit of everyone in the ecosystem, including Oracle, the IOUG, and every company with a business related to MySQL. If it is not a community conference, let's state it clearly, so that people can set their expectations accordingly.

Unintended consequences

Someone may think it's a good thing to have a MySQL conference without Oracle participation but I am sure most will agree that it is not desirable. Much as I admire Percona's technical merits, if I go to the conference I want to hear from a wide range of participants. Specifically, I would like to know what's in the pipeline, and I want to hear that from the engineers in the MySQL team, i.e. from Oracle. I doubt that Oracle would send engineers and VPs to talk to a conference that is named after a competitor, and that may be true for other entities, which I (and many others) would like to hear from.

In short

Is this the conference of Baron's and Peter's earlier dreams or is it the fulfillment of their current business strategy?
Please, let the community know.

Disclaimer

The opinions in this post are my own. My employer does not censor my writings and gives me full freedom of expression, but my opinion does not necessarily match that of my company.

Less known facts about MySQL user grants

2011-06-22T13:04:00.004+02:00

Reading MySQL security: inconsistencies I remembered a few related experiments that I did several years ago when I was studying for the MySQL certification. The first fact that came to mind is about the clause "WITH GRANT OPTION", which can only be given on the full set of options, not on a single grant. For example

GRANT INSERT,DELETE,UPDATE on world.* to myuser identified by 'mypass';
GRANT SELECT on world.* to myuser identified by 'mypass' WITH GRANT OPTION;
show grants for myuser\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'
*************************** 2. row ***************************
Grants for myuser@%: GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.* TO 'myuser'@'%' WITH GRANT OPTION

If you are surprised about the "WITH GRANT OPTION" clause applying to all grants instead of only applying to the SELECT, you forgot to consider how the grants are stored. All the grants for a given user (and a user is the combination of a name and a provenience) are stored in a single record in the mysql.user table. The GRANT OPTION is a column in that record. It is either set or not. You can't assign this option for only one attribute in the same record. It's either all the flagged grants or nothing. If you want to assign the "with grant option" on a single column, you must change either the provenience or the name of the user (thus opening another record). But also this addition may not be enough to reach your goal, as you can see in the next section. The other fact that came to mind about the "WITH GRANT OPTION" clause is that, in the examples given, it is ineffective. I dare say illusory. Let's start. As root user, we create this user:

root> grant all on granted.* to grantee identified by 'happyuser' with grant option;

The granted database exists, and now we have an user that can modify it, and, we think, delegate some functions to someone else.

grantee> grant select on granted.* to delegated identified by 'happy';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
grantee> create user delegated;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

Right. I can't create a new user, but only transfer my superpowers to someone else. I will ask root to create the user, and then I will give it another try.

root>  create user delegated;
Query OK, 0 rows affected (0.00 sec)

grantee> grant select on granted.* to delegated identified by 'happy';
ERROR 1044 (42000): Access denied for user 'grantee'@'%' to database 'mysql'

Ouch! Since the grant tables are in the 'mysql' database, I don't have access. I will ask root to give me access to the mysql 'user' and 'db' tables.

root>  grant insert on mysql.user to grantee ;
Query OK, 0 rows affected (0.00 sec)
root>  grant insert on mysql.db to grantee ;
Query OK, 0 rows affected (0.00 sec)

grantee> grant select on granted.* to delegated identified by 'happy';
ERROR 1044 (42000): Access denied for user 'grantee'@'%' to database 'mysql'

Not good. I tried then to get SELECT,INSERT,UPDATE,DELETE for all the grant tables inside 'mysql'. Still, I could not exercise my grant options. Finally, the only solution was to get privilegs on the whole mysql database.

root> grant insert,select,delete,update on mysql.* to grantee;
Query OK, 0 rows affected (0.00 sec)

grantee> grant select on granted.* to delegated identified by 'happy';
Query OK, 0 rows affected (0.00 sec)

At last, I can grant something to someone.
But wait! Now that I can modify the 'mysql' database ...perhaps I could ...

grantee> update user set Select_priv ='Y',
 Insert_priv ='Y', Update_priv ='Y', Delete_priv ='Y',
 Create_priv ='Y', Drop_priv ='Y', Reload_priv ='Y',
 Shutdown_priv ='Y', Process_priv ='Y', File_priv ='Y',
 Grant_priv ='Y', References_priv ='Y', Index_priv ='Y',
 Alter_priv ='Y', Show_db_priv ='Y', Super_priv ='Y',
 Create_tmp_table_priv ='Y', Lock_tables_priv ='Y', Execute_priv ='Y',
 Repl_slave_priv ='Y', Repl_client_priv ='Y', Create_view_priv ='Y',
 Show_view_priv ='Y', Create_routine_priv ='Y', Alter_routine_priv ='Y',
 Create_user_priv ='Y', Event_priv ='Y', Trigger_priv ='Y',
 Create_tablespace_priv ='Y' where user = 'grantee';

This does not enhance my current grants, because I don't have the SUPER privilege (yet), but I can wait until the server restarts or until someone issues a 'flush privileges'. An then I will have full access to the server. Obviously, this situation is not what the DBA had in mind when the user 'grantee' was created.

Update The habit of always seeing the password set as integral part of the GRANT command has made me err on judgment.
As noted in one of the comments, the "grantee" user could have granted privileges to "delegated" without assigning a password. In this case,"grantee" does not need separate grants to the mysql database, which were apparently needed only if you wanted to set the password with the GRANT command.
All the above post is a miscalculation. The additional grants are not needed, provided that you don't include a password clause in your GRANT command.

Introducing the Tungsten-toolbox

2011-06-20T14:20:00.001+02:00

After the public home for Tungsten Replicator, we needed another place where to host complementary tools. We discussed the pros and cons of hosting these tools in the same place where we publish Tungsten, but in the end we decided that it's more practical to have a separate project, where we can publish tools related to database replication, no matter if they are dedicated to Tungsten or if they can work with other replication systems.So, here it is. We have now Tungsten Toolbox, a site where we will collect our tools and accept contributions from others.

We have already a few tools that, after being cleaned up, will find their way to this repository. The one that was more or less ready for prime time is the Tungsten Sandbox, a tool that installs more than one Tungsten instance with a backend database in the same host. It is, as you can imagine, based on the MySQL Sandbox and it works reasonably well.All the tools in this toolbox are released under the New BSD License. What can you expect to see in the near future?We have a few ideas already:

A binary log analyzer
A tool that changes properties on-the-fly
A JSON API for Tungsten
Sandboxes and deployers for complex topologies (multiple masters, fan-in)
Sandboxes and deployers for direct slaves
Deployers for a mix of MySQL native and Tungsten replication
A PostgreSQL sandbox

We are, of course, open to contributions. If you have a tool that is useful for database replication and want to release it under a BSD license, feel free to propose it in the Google Group discussion on Tungsten Replicator.

What happened to MySQL Forge?

2011-06-14T11:43:00.005+02:00

Update
Soon after I posted this article, the Forge came back online! Thanks!

MySQL Forge has been offline for two days now. (2011-06-14) No sign of acknowledgement of this problem from the MySQL team. What is happening? For those not well acquainted with MySQL Forge, here are the facts. The MySQL Forge is a site that was intended to contain all community contributions. The reality did not follow the plans very closely, and some sections of the forge ended up with less contents than what should be useful. However, there are a few sections of the forge that are extremely useful to users:

The Wiki, which is full of irreplaceable documentation, such as the MySQL internals, description of preview features, slides and recordings of MySQL University, manual of many tools (such as the Random Query Generator, and more
The worklogs, or the blueprints for most MySQL features, past, present, and future
The tools/snippets, which is a collection of community contributed scripts. Not many, but most of them of high quality and quite useful.

I know that there was a lot of spam in the forge, but I really hope that it was taken off line on purpose. If its maintenance is a problem, I am sure we can find plenty of volunteers in the community that want to host the wiki and the rest of the useful stuff. Hello? Anybody there at Oracle/MySQL? Please give us back the forge. Thanks.

Getting started with Tungsten Replicator and Tungsten Sandbox

2011-06-09T15:54:00.003+02:00

We have been busy, at Continuent. In addition to our usual work with high performance replication, we have addressed usability issues, since we know that a hard-to-use problem, no matter how powerful, has low adoption. Thus, is with some personal satisfaction that I can announce the release of Tungsten Replicator 2.0.3, which comes with several huge improvements in matter of user friendliness. The new installation procedure is so user friendly, in fact, that I was able to build a sophisticated tungsten-sandbox with a 150-line shell script. (The corresponding features for MySQL Sandbox required 4,500 lines of Perl).

Enough self celebration, though. Let's get started, as the title of this post suggests, with the practical steps.

Requirements

Before we begin, there are a few requirements to meet.

You need to be on a Unix-like operating system. Our main choice is Linux. If you want to test on Mac OSX, it works, but we won't recommend it.
Java JRE must be installed. And it must be the original one, not the Open JDK. Update: The requirement against OpenJDK has been lifted. It works fine in my tests.
Ruby 1.8 must be installed. This is mainly needed during the installation phase only, but it is required nonetheless.
The user account that will install and run Tungsten must have ssh access to the other hosts involved in the cluster
The above mentioned user must have sudo access. This is only needed if you want to use Tungsten Replicator to run backups that involve root access (like xtrabackup). We may lift this requirement later, but for now you need to enable it, at least during the installation, and remove the access when you are done.
This user must also have read access to MySQL binary logs. Usually you achieve this by making sure that the binary logs are readable by users belonging to the "mysql" group, and by adding such group to your user.
There must be a MySQL users for Tungsten replication. This user must have full access to the database server, with grant option.
The MySQL server must have binary logging enabled.
If you have MySQL native replication running, you must stop it.

Getting the code and install

The code is released in the downloads section of Tungsten's home. The current recommended version is 2.0.3, but if you like to be really up to date, we also publish a list of recent builds from our build server, which you can use to have a go at the replicator. For this simple installation, I will use four servers from our server farm. The servers are named R1, R2, R3, and R4. The first good news of the new installation process is this: you need to install in one server only!. More details follow. First off, create a directory where you want to install. Use a non-root account. Just make sure that it's the same user in all the servers, and that such user can access the directory where you want to install. I am going to call this directory planet.

cd $HOME
for N in 1 2 3 4 ; do ssh r$N mkdir planet ; done
cd planet 
wget http://tungsten-replicator.googlecode.com/files/tungsten-replicator-2.0.3.tar.gz
tar -xzf tungsten-replicator-2.0.3.tar.gz
cd tungsten-replicator-2.0.3

I have already a MySQL user named tungsten with password "mypwd" (but it can be anything you like, as long as it has the required privileges). Now we have all the components. If you have read the Tungsten documentation, please ignore the ./configure script. That is left for compatibility reasons, and will be deprecated soon. Instead, to install the cluster of our 4 servers, let's do the following:

export TUNGSTEN_BASE=$HOME/planet

./tools/tungsten-installer \
    --master-slave \
    --master-host=r1 \
    --datasource-user=tungsten \
    --datasource-password=mypwd \
    --service-name=dragon \
    --home-directory=$TUNGSTEN_BASE \
    --cluster-hosts=r1,r2,r3,r4 \
    --start

Some comment on this command: --master-slave is the installation mode (see below for more info). --service-name can be anything you want. --home-directory is where all the installation sub directories will go. --cluster-hosts is the list of servers you want to install, and finally, --master-host is the host that will be installed as a master, while all the others will be slaves of that one. If you have followed the instructions carefully, the installer will bring up the Tungsten cluster without any fuss, Unix style. If you hate silent installations, you can get the full monty by adding some options:

./tools/tungsten-installer \
    --verbose \
    --master-slave \
    --master-host=r1 \
    --datasource-user=tungsten \
    --datasource-password=mypwd \
    --service-name=dragon \
    --home-directory=$TUNGSTEN_BASE \
    --cluster-hosts=r1,r2,r3,r4 \
    --start-and-report

If you run the installer in verbose mode, you will see an extremely long list of validation checks that the installed does on your current servers and on the ones that are listed in the --cluster-hosts option. If everything went well, you will find the following directories in $HOME/planet (for all servers in your cluster):

configs, containing the configuration file created by the installer. This file describes your cluster
releases, containing the Tungsten binaries.
thl, containing Tungsten's Transaction History Logs. These logs are like MySQL binary logs, but with much more metadata, including a global transaction ID, which is missing in MySQL native replication.
relay, which should be empty, unless you install in "direct" mode (see below.)
tungsten, which is a symlink to the Tungsten directory inside releases.

In addition to the above mentioned directories, Tungsten Replicator creates a database for each service. Since we have only one service in this topology, you will find a database named "tungsten_dragon". (If you have called your service "bunny", you will instead find "tungsten_bunny"). Inside this database there is the replication metadata necessary for making the servers fault tolerant. Only a small amount of data is kept on that database. It's roughly corresponding to what you get from the .info files in MySQL native replication. To test that the system is OK, let's find our tools. The first one is trepctl, which, among other things, can give us an overview of the running services.

cd $HOME/planet
./tungsten/tungsten-replicator/bin/trepctl services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 1.152
role            : slave
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

./tungsten/tungsten-replicator/bin/trepctl -host r1 services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 0.936
role            : master
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

mysql -h r1 -e 'create schema if not exists test'
mysql -h r1 -e 'create table test.t1 (i int)'

./tungsten/tungsten-replicator/bin/trepctl services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 2
appliedLatency  : 0.155
role            : slave
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

./tungsten/tungsten-replicator/bin/trepctl -host r1 services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 2
appliedLatency  : 0.044
role            : master
serviceName     : dragon
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

After the installation, trepctl reported the last applied sequence number (appliedLastSeqno) as 0. Following the execution of two commands in the master, such number became 2. If you want to know more of what was happening, you can use the thl command. This corresponds roughly to using mysqlbinlog with MySQL native replication logs.

/tungsten/tungsten-replicator/bin/thl -service dragon list |less
SEQ# = 1 / FRAG# = 0 (last frag)
- TIME = 2011-06-09 14:51:23.0
- EVENTID = 000002:0000000000000514;197609
- SOURCEID = qa.r1.continuent.com
- STATUS = COMPLETED(2)
- METADATA = [mysql_server_id=10;service=dragon;shard=test]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, createOrDropDB = , autocommit = 1, sql_auto_is_null = 1, foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, collation_connection = 8, collation_server = 8]
- SQL(0) = create schema if not exists test /* ___SERVICE___ = [dragon] */
SEQ# = 2 / FRAG# = 0 (last frag)
- TIME = 2011-06-09 14:51:30.0
- EVENTID = 000002:0000000000000601;197610
- SOURCEID = qa.r1.continuent.com
- STATUS = COMPLETED(2)
- METADATA = [mysql_server_id=10;service=dragon;shard=test]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- OPTIONS = [##charset = ISO8859_1, autocommit = 1, sql_auto_is_null = 1, foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, collation_connection = 8, collation_server = 8]
- SCHEMA = 
- SQL(0) = create table test.t1 (i int) /* ___SERVICE___ = [dragon] */

Once we are satisfied that replication is working, we can clean up the cluster and try other installation experiments. To clean up a cluster, you need to do the following:

stop the replicator in all servers.
for N in 1 2 3 4; do $PWD/tungsten/tungsten-replicator/bin/replicator stop; done
remove the thl files from all servers.
remove the tungsten_SERVICE_NAME database from all mysql servers
run a "reset master" in the master database
remove the directories created by the installer in all servers

Installation types

The procedure described above was, until a few months ago, the only thing you could do with Tungsten. Now you can broaden your horizons with a wider range of possibilities.

Master/slave is of course the main option, and it's the one that you have seen in the previous section. This method gives you the full set of Tungsten features and performance. It is the recommended method for production use and for benchmarking. In this scenario, the Tungsten replicator on the master will extract transactions from the binary log, transfer them to the THL, and share it with the slaves. The slaves will read from the THL and apply the transactions to the database. There are a few steps more in between, but for the sake of brevity I will skip them You can have a look at Robert Hodges blog for more info.
Slave "direct" is the alternative that you can use in production, and it's been designed to satisfy users who only want some particular benefits on the slave side, and don't care about global transaction IDs. If you are looking at parallel apply, this is probably a setup that you want to try. In this scenario, there is no replicator on the master. The slave pulls data remotely from the binary logs, copies them locally, and extracts data to the THL. Here's an example of how to start a slave-direct system:

./tools/tungsten-installer \
    --direct \
    --master-host=r1 \
    --slave-host=r4 \
    --master-user=tungsten \
    --slave-user=tungsten \
    --master-password=secret \
    --slave-password=secret \
    --service-name=Castor \
    --thl-port=21124 \
    --rmi-port=10104 \
    --channels=5 \
    --home-directory=$TUNGSTEN_BASE \
    --start-and-report

If your purpose is testing Tungsten, probably the Tungsten Sandbox is what you should try. This system is based on MySQL Sandbox, a framework that lets you install more than one MySQL server in the same host. Building on top of MySQL Sandbox, and leveraging the new flexibility in Tungsten installer, tungsten-sandbox allows you to build a master/slave system inside a single host. Let's give it a try. You need to have MySQL Sandbox installed, and at least one MySQL tarball expanded under $HOME/opt/mysql/X.X.XX (where X.X.XX is the MySQL version, such as 5.5.12).

cd $HOME/planet
mkdir sb
cd tungsten-replicator-2.0.3
wget http://tungsten-replicator.googlecode.com/files/tungsten-sandbox
./tungsten-sandbox -h
USAGE: ./tungsten-sandbox [flags] args
flags:
  -n,--nodes:  how many nodes to install (default: 3)
  -m,--mysql_version:  which MySQL version to use (default: '5.1.56')
  -t,--tungsten_base:  where to install the sandbox (default: '/home/tungsten/tsb2')
  -d,--group_dir:  sandbox group directory name (default: 'tr_dbs')
  -s,--service:  how the service is named (default: 'tsandbox')
  -P,--base_port:  port base for MySQL sandbox nodes (default: 710)
  -l,--thl_port:  port for the THL service (default: 1211)
  -r,--rmi_port:  port for the RMI service (default: 1010)
  -v,--[no]version:  show Tungsten sandbox version (default: false)
  -h,--[no]help:  show Tungsten sandbox help (default: false)

In my server, I have already expanded MySQL 5.5.10, and I want to install inside $HOME/tsb. So, here is what I do:

./tungsten-sandbox -m 5.5.10 -t ~/tsb

This command installs three instances of MySQL under $HOME/sandboxes and three of Tungsten under $HOME/tsb. Inside this directory, in addition to the running instances, we find some more goodies:

clear_all send_kill_all start_all status_all stop_all use_all : symlinks to the corresponding commands in MySQL sandbox
db1 db2 db3: these are the tungsten instances.
n1 n2 n3: quick links to access each MySQL node
replicator_all trepctl_all: utilities that run "replicator" or "trepctl" for each node with the arguments provided on the command line

Additionally, there are a few scripts inside each Tungsten instance in the sandbox:

trepctl: a link to the deep down trepctl
thl: a link to the thl utility
show_log: a quick way of showing the replicator log

Since this is a tool for testing, removing it is going to be easy.

~/tsb/replicator_all stop
~/tsb/clear_all
rm -rf ~/tsb/*

A final method of installing is a sandbox with ths slave-direct method. There is no dedicated script for this method, but thanks to the new installer, you can get the job done quite easily:

export NODE_OPTIONS='-c innodb_flush_log_at_trx_commit=2 -c max_allowed_packet=48M'
make_multiple_sandbox --group_directory=tr_dbs --sandbox_base_port=7100 5.5.10

TUNGSTEN_BASE=$HOME/tinstall/tsb/
./tools/tungsten-installer \
    --direct \
    --master-host=127.0.0.1 \
    --master-port=7101 \
    --slave-host=db2 \
    --slave-port=7102 \
    --master-user=root \
    --slave-user=root \
    --master-password=msandbox \
    --slave-password=msandbox \
    --master-log-directory=$HOME/sandboxes/tr_dbs/node1/data \
    --service-name=Castor \
    --thl-port=12112 \
    --channels=5 \
    --rmi-port=20000 \
    --home-directory=$TUNGSTEN_BASE \

./tools/tungsten-installer \
    --direct \
    --master-host=127.0.0.1 \
    --master-port=7101 \
    --slave-host=db3 \
    --slave-port=7103 \
    --master-user=root \
    --slave-user=root \
    --master-password=msandbox \
    --slave-password=msandbox \
    --master-log-directory=$HOME/sandboxes/tr_dbs/node1/data \
    --service-name=Pollux \
    --thl-port=22112 \
    --channels=1 \
    --rmi-port=20000 \
    --home-directory=$TUNGSTEN_BASE \
    --relay-directory=$TUNGSTEN_BASE/relay --start-and-report

This script creates two services (Castor and Pollux), with only one instance of Tungsten replicator, with all the servers (MySQL and Tungsten ones) in the same host.

Conclusions

There should be much more to say, but I will leave it for the coming days. In the meantime, I encourage everyone to try the new Tungsten and submit bug reports when things don't work as expected. As always, happy hacking!
P.S. Today at 10am PT there is a webinar on this very topic!

The price of safe data - Benchmarking semi synchronous replication

2011-05-17T23:00:00.002+02:00

Some time ago I wrote about MySQL 5.5 semi-synchronous replication. Since then, I have wanted to benchmark the overhead of semi-synchronous replication with a decent server. Now the occasion presented itself, thanks to some related business that I had to benchmark, and thus I did a few simple runs with and without semi-synchronous replication enabled, to see the impact of this feature on performance. If you haven't read the article on semi-synchronous replication, the bottom line is that, with this feature enabled, the master waits until at least one slave has acknowledged receipt for the data before returning a positive result to the client. This means that for each commit there are two network calls between master and slave. My gut feeling was that this feature would be costly in terms of query response time, although I was not prepared to such a big impact as I found out in my test. I needed a substantial set of data, and I got it by exporting the employees table from the employees test database, using one INSERT per record. Thus, I had about 300,000 records, which are a fair amount for this kind of test. Had I sent the records in a big multiple insert chunk of 10,000 records each, I would have had only 30 commits, which would not have been easy to measure. So, here goes.

regular replication

$ time mysql < employees.sql 

real 0m27.997s
user 0m1.394s
sys 0m1.046s

semi-synchronous replication

$ time mysql < employees.sql 
real 1m24.277s
user 0m3.842s
sys 0m6.270s

Semi-synchronous replication was three times slower than regular replication. The test was taken using one master in one host and one slave in two more hosts. The measurements were the same if I had only one or both slaves enabled. Using row-based replication instead of statement-based did not make any substantial impact. Now my question is: who would be prepared to accept such a performance impact for the sake of more data safety? Data is important, but response time to customers is also important. Your mileage may vary. I know many customers who would think twice before accepting this onerous trade off. I am curious to know what experience others have had with this feature, and how much performance they are willing to sacrifice for safety.

Open Database Camp 2011 opens today!

2011-05-06T11:10:00.004+02:00

The Open Database Camp 2011 opens today with the Welcome Party, starting today at 7pm CEST. The party (with good Italian food and drinks) is open to all the ones who have registered in the Attendees list.
By car you have to reach Pula, take Via Nora (Nora Street), than Via Sant'Efisio (Sant'Efisio Street), until the end, directly to the party location.
Organisers will also make a bus available on Friday 6 May, leaving from Pula Hotels (Nora Club Hotel - Villa Madau - Baia Di Nora - Is Molas - Marin Hotel - Is Morus Hotel) around 18:30 and reaching Nora. From Nora back to Pula Hotels a bus will leave around 21:00 and 22:00.

The conference itself will start on Saturday, May 7th, at 9am. Travel arrangements to reach the venue are listed in the conference wiki (wiki: Travel).
There will be a bus collecting participants from the hotels at 8:30am and the same bus from the conference venue (Sardegna Ricerche) back to the hotel in the evening.

Sessions

Open Database camp is an Un-conference. As with the previous editions, the schedule will be decided on the spot, on Saturday. You can list your intended sessions and your wishes in the Sessions page.

Logistics

You will need an ID for your wifi access. (Sorry, it's a law requirement)^(*). If you want your username and password, you should collect it at the reception as soon as possible.

Customized meetings

There will be room for 1:1 meetings between attendees, if you like to do so. You will find a board with the names of all attendees and their affiliation, and you can easily schedule a meeting with them.

^(*) The law has expired but after having put the fear of the state into every internet provider, the lawmakers have not said how the new regulations should be applied. Regretfully, we have still to live with the old rules.

Introducing the Flying Clusters, and more than MySQL replication

2011-05-02T23:40:00.001+02:00

My Colleague Linas Virbalas has just crossed the boundary between real and virtual and has started a blog, titled Flying Clusters.
Linas is a gifted developer who is taking care of the special projects. One of such projects is replication between MySQL and PostgreSQL, which works quite well.

Another project, which has just started, is about providing PostgreSQL with Advanced Logical Replication using Tungsten replicator. As you probably know, recent versions of PostgreSQL can do physical replication, which has its pros and cons. With this project, PostgreSQL users can also have the choice of using logical replication. Not only that: since Tungsten Replicator already supports MySQL, cross-DBMS replication clusters are not far away.
We are also more ambitious, and we are exploring ways of replicating to NoSQL entities. We will start with MongoDB at a dedicated SQL to NoSQL Replication Hackathon, where we will attempt the creation of n applier for MongoDB during the Open Database Camp conference.

Replication : different points of view

2011-04-28T08:34:00.001+02:00

The following quotes are the first sentences in the replication chapter of two similar books. Both are admin cookbooks. One is for PostgreSQL, one for MySQL.

Replication isn't magic, though it can be pretty cool. It's even cooler when it works, and that's what this chapter is all about.
Replication requires understanding, effort, and patience. There are a significant number of points to get right. My emphasis here is on providing simple approaches to get you started, and some clear best practices on operational robustness

PostgreSQL 9 Admin Cookbook

Replication is an interesting feature of MySQL that can be used for a variety of purposes. It can help to balance server load across multiple machines, ease backups, provide a workaround for the lack of fulltext search capabilities in InnoDB, and much more.

MySQL Admin Cookbook

The PostgreSQL quote warns of a dangerous, difficult, and unexplored path, while the MySQL one is the almost bored remark of someone whose biggest problem is to list how many good things you can do. I guess that being exposed to a given technology for longer time changes one's perception.

Open Database camp 2011 - Travel logistics, and don't forget the party

2011-04-27T13:24:00.000+02:00

The Open Database Camp 2011 is near. In 9 days, the welcome party starts, and then the conference itself gets going.
If you are coming earlier than Friday, May 6th, you can either use public transportation or book a private seat with a volunteer in the car pooling page. Please help the organizers: post your arrival and departure dates and times, so we may be able to help you even outside the official conference days.
About the conference itself, as everyone should know, it's a un-conference, where the talks will be decided on the spot. But you can book ideas and topics in the sessions page.
Since we will have many participants from Italy, there will be dedicated sessions in Italian in addition to the ones in English, which is the official language of the conference.

Replication metadata in MySQL 5.6.2

2011-04-17T23:24:00.003+02:00

The default storage engine is InnoDB, or is it not?

When MySQL 5.5 went GA, the biggest piece of news was that the default storage engine is now InnoDB. Good news, and hope for a better future, as InnoDB is the most reliable storage engine available for MySQL.

Therefore the expectation is that MySQL 5.6 follows in its steps, and we should see less and less of MyISAM in the database.
The privileges tables, however, are still MyISAM. I was not really expecting to see them disappear so quickly, as I have seen how much work it has been for Drizzle to get rid of them, and even them had to keep MyISAM alive for temporary tables.
However, I was surprised to see that the new tables for replication metadata, the ones that replace the files master.info and relay_log.info are MyISAM by default.
The manual says:

In order for replication to be crash-safe, the slave_master_info and slave_relay_log_info tables must each use a transactional storage engine. By default, both of these tables use MyISAM; this means that, prior to starting replication, you must change both of these tables to use a transaction storage engine if you wish for replication to be crash-safe. You can do this by means of the appropriate ALTER TABLE ... ENGINE=... statements. You should not attempt to change the storage engine used by either of these tables while replication is actually running.

The funny thing is that the manual does not mention InnoDB explicitly, as if there were many transactional engines coming with the official MySQL.

Tables instead of files.

Anyway, I went ahead and tried the new metadata features. In short, the new version allows you to store the data that once were in master.info and relay_log.info in two tables with similar names, located under the mysql schema.

First of all, I changed the storage engine, as suggested by the docs. Actually, the docs are still a bit scarce about this feature. The best instructions are the ones found in Mats Kindahl.

# in the slave
ALTER TABLE mysql.slave_master_info ENGINE = InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE = InnoDB;

# in the slave configuration file
relay-log-info-repository=TABLE
master-info-repository=TABLE

After this operation, I initialized the two slaves, one of which has the new table info, and the other one has still the old files info, for comparison.

# slave 1 (with table info)
show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 12027
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 114
               Relay_Log_File: mysql_sandbox12028-relay-bin.000002
                Relay_Log_Pos: 267
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[...]

select * from slave_master_info\G
*************************** 1. row ***************************
              Master_id: 101
        Number_of_lines: 20
        Master_log_name: 
         Master_log_pos: 4
                   Host: 
              User_name: 
          User_password: 
                   Port: 3306
          Connect_retry: 60
            Enabled_ssl: 0
                 Ssl_ca: 
             Ssl_capath: 
               Ssl_cert: 
             Ssl_cipher: 
                Ssl_key: 
Ssl_verify_servert_cert: 0
              Heartbeat: 1800
                   Bind: 
     Ignored_server_ids: 0
                   Uuid: 
            Retry_count: 86400


# Slave 2
cat node2/data/master.info 
20
mysql-bin.000001
114
127.0.0.1
rsandbox
rsandbox
12027
60
0





0
1800.000

0
6cb60e24-68e7-11e0-9eec-6c626da07446
86400

Hmmm. Not good. Definitely not good.
Now, according to Mats article, the slave_master_info table is updated every time a slave starts. But this is not the case.
Apparently, you need to restart it at least once more, to get an update.

# slave 1
stop slave; 
start slave;
select * from slave_master_info\G
*************************** 1. row ***************************
              Master_id: 101
        Number_of_lines: 20
        Master_log_name: mysql-bin.000001
         Master_log_pos: 114
                   Host: 127.0.0.1
              User_name: rsandbox
          User_password: rsandbox
                   Port: 12027
          Connect_retry: 60
            Enabled_ssl: 0
                 Ssl_ca: 
             Ssl_capath: 
               Ssl_cert: 
             Ssl_cipher: 
                Ssl_key: 
Ssl_verify_servert_cert: 0
              Heartbeat: 1800
                   Bind: 
     Ignored_server_ids: 0
                   Uuid: 6cb60e24-68e7-11e0-9eec-6c626da07446
            Retry_count: 86400

This lack of update is the default by design. The reasoning is that if you update the table at every transaction, you are slowing down replication beyond acceptable levels. However, it must be noted that the update of the table is way less than the updates of the file.

You can force the slave_master_info and slave_relay_log_info tables to update at every transaction, by setting sync_master_info and sync_relay_log_info. Indeed, with this addition, the table is updated at every transaction.
Therefore the choice is between crash unsafe and fast (with the *.info files) and crash safe and very slow (with the tables).

Usability issues

Let's mention the good news first. This addition is very welcome, because it allows monitoring tools to be implemented directly in SQL. The main difficulty about this problem is that the only metadata available until MySQL 5.5 is "SHOW SLAVE STATUS", which has no related Information_Schema or Performance_Schema table. Thus, getting the status values into a SQL variables is not feasible without external tools. This metadata is no replacement for SHOW SLAVE STATUS (that worklog is still struggling with a slow implementation) but there is enough overlapping that a simple monitoring tool could be created with SQL, stored routines and the event scheduler.

Now, for the bad news:
This implementation leaves me baffled for several reasons.
The lack of updates by default is the biggest concern. There is no option of automatic updates every second, same as synch_binlog. It's all or nothing.

The choice of implementation is not pleasant either. Users would expect the table-based recording to mimic the behavior of the file-based recording, i.e. when replication is started, the table is created, and after a "reset slave' the table is removed. But this does not happen. The table is truncated, and if you remove it, it won't be created when you restart replication.

What's worse, this table can't be dumped with locks. MySQL complains if you attempt to do that.

./s1 -e 'stop slave'

mysqldump mysql slave_master_info
-- MySQL dump 10.13  Distrib 5.6.2-m5, for linux2.6 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version 5.6.2-m5-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1721: You can't use locks with rpl info tables. when doing LOCK TABLES

This makes more difficult the operation of provisioning a slave from a backup. I would expect that, having stopped the slave, I could backup the table, possibly together with the rest of the database. Maybe MySQL has a hidden clever way of exporting this data, but if that exists, so far it has escaped me.

Update: You can use the following command to dump this table.

mysqldump --master-data mysql slave_master_info

However, a simple

mysqldump --master-data mysql

does not include the *_info tables. (Bug#60902)

Another problem is maintenance. If I want to clean up the InnoDB table space, the usual recipe is to dump everything, stop the server, remove the ib* files, restart the server, and then reload the data.
That has worked very well so far, because there were no innodb tables in the mysql database. Now, however, if we attempt to perform the above operation, we get an error when InnoDB comes online, because it won't find an internal reference to the innodb tables, whose .frm files (and possibly .ibd files) are still dangling around under the mysql folder.

Incidentally, I can note that Tungsten Replicator uses a similar approach (replication metadata is stored in a table, which is updated at every commit), and yet it does not suffer from any of the drawbacks mentioned here. The replication metadata tables are stored in a regular schema, which can be dumped just fine to provision a new slave. The additional commits are not a problem, since Tungsten uses the technique of block commits, where it commits together all the transactions that can be safely grouped.
The safety of the slave thus depends on the value of --innodb-flush-log-at-trx-commit, not on additional trade off decisions.

More to come.

This article covers just a portions of the replication improvements in 5.6. There is much more to see and test.
Specifically, I want to test the performance impact of the metadata tables, and also the performance of the multi-threaded slave prototype against regular replication and Tungsten. I will get around to it shortly. Stay tuned.

Pewter for Tungsten - Thanks, MySQL community!

2011-04-17T18:22:00.000+02:00

After the opening keynote at the MySQL Conference, there was the usual ceremony of the MySQL community awards. Since Oracle declined to continue in the MySQL AB tradition of awarding the contributions from the community, the community itself has taken over.
I was pleasantly surprised to find my name among the recipients, and even more about the prize awarded to my company's product, the Tungsten Replicator.
The surprise comes because we have been making noise about this product for only a few months, after we finalized our plans to split the company products between open source and enterprise. Apparently, it has been the right kind of noise, and the community has been able to see that Tungsten is a tangible contribution to the MySQL ecosystem.

Now that the MySQL Conference's frenzy is over, we can continue making Tungsten even better than it is today. As probably everyone knows, we have found a generous sponsor (which should remain unnamed for now, but to whom we are very grateful) that is paying for implementing and strengthening the features that will make Tungsten 2.0 production ready in the near future.
If users want to contribute with their feedback, they can download the binaries (and the full source code) from the new Tungsten home http://tungsten-replicator.org, and report issues in the same site. See Settling in at code.google.com for more information.
More technical info will follow soon.
For now, I just wanted to say Thank you, MySQL community!

Have you missed the MySQL Conference? Come to OpenDbCamp!

2011-04-15T03:00:00.002+02:00

The MySQL Conference is over. There have been many new developments, and the ones who have missed it will probably want to get a summary of the excitement, possibly from the people who have contributed to shaping the news.
The Open Database Camp will give users an opportunity to catch up. Especially to open source users in Europe.
Come and share the fun. There will be talks on MySQL, PostgreSQL, several NoSQL products, and a bunch of other cool stuff.

Refactored again: poor man's MySQL replicator monitor

2011-04-07T14:39:00.002+02:00

I saw that both Haidong Ji and Geert VanderKelen have proposed a Python monitor for MySQL replication, calling it the "poor man's version".
See Poor man’s MySQL replication monitoring and Geert's Refactored: Poor man’s MySQL replication monitoring.
Having Python in your server doesn't really qualify as "poor man". In many cases it's a luxury, and thus, here's my shot at the problem, using a Bash shell script.
Unlike its Python-based competition, this version also checks that the slave is replicating from the intended master, and that it is not lagging behind.

#!/bin/bash

USERNAME=msandbox
PASSWORD=msandbox
EXPECTED_MASTER_HOST=127.0.0.1
EXPECTED_MASTER_PORT=27371

SLAVE_HOST=127.0.0.1
SLAVE_PORT=27372

MYSQL="mysql -u $USERNAME -p$PASSWORD "
MASTER="$MYSQL -h $EXPECTED_MASTER_HOST -P $EXPECTED_MASTER_PORT"
SLAVE="$MYSQL -h $SLAVE_HOST -P $SLAVE_PORT"

$MASTER -e 'SHOW MASTER STATUS\G' > mstatus
$SLAVE -e 'SHOW SLAVE STATUS\G' > sstatus

function extract_value {
    FILENAME=$1
    VAR=$2
    grep -w $VAR $FILENAME | awk '{print $2}'
}

Master_Binlog=$(extract_value mstatus File )
Master_Position=$(extract_value mstatus Position )

Master_Host=$(extract_value sstatus Master_Host)
Master_Port=$(extract_value sstatus Master_Port)
Master_Log_File=$(extract_value sstatus Master_Log_File)
Read_Master_Log_Pos=$(extract_value sstatus Read_Master_Log_Pos)
Slave_IO_Running=$(extract_value sstatus Slave_IO_Running)
Slave_SQL_Running=$(extract_value sstatus Slave_SQL_Running)

ERROR_COUNT=0
if [ "$Master_Host" != "$EXPECTED_MASTER_HOST" ]
then
    ERRORS[$ERROR_COUNT]="the slave is not replicating from the host that it is supposed to"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Master_Port" != "$EXPECTED_MASTER_PORT" ]
then
    ERRORS[$ERROR_COUNT]="the slave is not replicating from the host that it is supposed to"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Master_Binlog" != "$Master_Log_File" ]
then
    ERRORS[$ERROR_COUNT]="master binlog ($Master_Binlog) and Master_Log_File ($Master_Log_File) differ"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

POS_DIFFERENCE=$(echo ${Master_Position}-$Read_Master_Log_Pos|bc)

if [ $POS_DIFFERENCE -gt 1000 ]
then
    ERRORS[$ERROR_COUNT]="The slave is lagging behind of $POS_DIFFERENCE"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Slave_IO_Running" == "No" ]
then
    ERRORS[$ERROR_COUNT]="Replication is stopped"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Slave_SQL_Running" == "No" ]
then
    ERRORS[$ERROR_COUNT]="Replication (SQL) is stopped"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ $ERROR_COUNT -gt 0 ]
then
    EMAIL=myname@gmail.com
    SUBJECT="ERRORS in replication"
    BODY=''
    CNT=0
    while [ "$CNT" != "$ERROR_COUNT" ]
    do
        BODY="$BODY ${ERRORS[$CNT]}"
        CNT=$(($CNT+1))
    done
    echo $SUBJECT
    echo $BODY
    echo $BODY | mail -s "$SUBJECT" $EMAIL
else
    echo "Replication OK"
    printf "file: %s at %'d\n" $Master_Log_File  $Read_Master_Log_Pos
fi

Make your voice heard. Tell Oracle and the MySQL Council what bugs you

2011-04-04T19:29:00.000+02:00

The MySQL Council has not being idle. We have addressed the bugs database concerns, and we are continuing our dialog.
To do a better job, we would like to hear more from the community. Unlike other established user groups, MySQL does not have a world wide organization for its users. The council exists on a voluntary basis, and we are seeking support from the rest of you. Please let your voice heard. There are three main channels for this:

A MySQL Council survey
A set of questions that will be answered during the keynote at the MySQL Conference
Talk to a council member

Survey

The MySQL Council survey is an attempt to collect, understand, and eventually prioritize what the MySQL community feels, and act on their suggestions. It is a very short survey. We don't want your opinion on everything from barbecue sauce recipes to brain surgery. We need only a few lines about what bothers you the most with MySQL and how we can help. And of course, if you wamt to help, we really want to hear about that.

Questions for the keynote

Let's ask Oracle directly. What are the biggest issues that you have? You can submit your questions for the keynote, and Tomas Ulin will answer them on stage at the MySQL conference
Here is your shot at practicing people power: think of the most pressing questions that you would see Tomas Ulin addressing, and submit them as soon as possible.

Talk to a council member

Council members are all well known people in the community. Most of us will be at the MySQL Conference or at Collaborate 11, or both.
If you want to ask me questions about the council, or MySQL, or community matters, I will do my best to answer them, or to seek an answer if I don't know it myself.

I will be at the San Francisco MySQL User Group on April 7th.
Then I will be at the MySQL conference Monday and Tuesday. Oh, and there is the Community Dinner West on Monday evening!
On Wednesday, I will be at Collaborate 11.
The other council members will be around as well. If you don't feel like filling the survey and prefer person-to-person communication, come see us, and let's talk!

Open Database camp 2011 - Opportunities for sponsors, culture, and more

2011-04-04T11:13:00.000+02:00

The Open Database Camp 2011 is barely one month away.

Sponsorship

Many thanks to all the sponsors! We very much appreciate your support.
Speaking of what, here is some important information for sponsors: The venue owners, Sardegna Ricerche, has given us the availabilkity of an ample hall for sponsors, where they can showcase their products and services.
Each sponsor will have a desk, and a double panel sized cm 195 x 75 (6.3 x 2.4 feet).

Culture, fun, and more

The Science park is something unique that geeks may want to visit. It is one of the biggest research centers in Europe, and the owners have graciously organized a guided tour before and after the conference.
Near the conference there is Nora, an archeological site that alone is worth the trip for a visit. You can see it during the welcome party on Friday (if you show up before sunset, that is), or you can visit on your own after the conference.

To give you an idea of what expects you, here is a promotional video of Sardegna Ricerche.

The event should attract many local open source enthusiasts, with varying degrees of knowledge about open database. To meet their curiosity, there will be a parallel beginners track, with introductory sessions to open databases. SQL and noSQL fans, get ready to evangelize your beloved products. There will be many people eager to listen!

MySQL replication for demanding users

2011-03-31T00:54:00.002+02:00

I have been working with MySQL replication for quite a while. I have dealt with simple replication setups and I have experimented with complex ones. Five years ago I wrote an article about advanced MySQL replication, which was mostly a dream on what you could do with imagination and skill, but the matter from that article is still not even remotely ready for production. Yet, since that article, I have been approached by dozens of people who wanted to know how to make the multiple master dream become reality. To all of them, I had to say, "sorry, this is just a proof of concept.Come back in a few years, it may become possible". It still isn't.
Despite its latest great technological advance, MySQL native replication is is very poor of topologies. What you can do with MySQL native replication is master-to-slave (which also includes relayed slaves), master-to-master, and circular replication.

Of these, circular replication is the closest thing to multiple masters that you can get with MySQL native replication, without the addition of third party services.
Circular replication is tricky to set up, although not unreasonably so. It works. With some patience and precision, you can build a cluster of a few nodes in circular replication. With luck, you can get them to work properly, without loops and with the data flowing to all the servers. Your luck runs out the moment one of the servers fails, or replication breaks down for whatsoever reason. Then you see that circular replication is actually more complicated than what it looks on the surface, and it is also quite brittle. That doesn't mean that circular replication is not used in production. It is. I have known several people who use it successfully, although nobody is really happy about it.
In addition to its fragility, circular replication is slow. If you insert data into master A, it has to travel across three nodes before reaching master D.
Another topology that seems to be very popular is the multiple source scheme. It is the opposite of master/slave. Instead of having one master that sends data to many slaves, it is many masters that send data to one slave. Despite its popularity, this topology is yet unimplemented with MySQL native replication. The best you can do to simulate the desired outcome is to do round-robin replication with cron.
With this background, it is no surprise that I was thrilled at the idea of working for a company that has made these dreams become reality. Tungsten replicator allows users to have real multiple masters topologies, and even the much coveted multiple source topology is now within the users grasp.
Compared to MySQL replication, the drawback of using Tungsten is that you need to deal with bigger complexity. It's only natural. With so many more features, there come more pieces to take care of.
An interesting point about multiple masters is the matter of conflict resolution. Asynchronous replication convenience and robustness are countered by lack of means to deal with conflicts. This difficulty has been used many times as the reason for not implementing multiple source replication in MySQL. I have my own ideas on this issue. I am aware of the risks, but if I were allowed to do multiple master replication, I would be glad to take charge of the risks. Updating different databases, or different tables in separate masters is one way of defining a conflict-free scenario where multiple masters or multiple sources could be used safely. If only we could ...
My colleague Robert Hodges has posted some interesting aspects in his blog. The bottom line is that we focus on empowering users with advanced replication features. Conflict resolution can wait. I am sure many users would love to have the problem of how to avoid conflicts if the more demanding problem of how to replicate from many places to one cluster could be solved. The good news is that some sort of conflict detection (and possibly resolution) are possible even now, without slowing down the operations and without complicating our lives unnecessarily. For example, a simple conflict that could be avoided using Tungsten filters is the one resulting in a master that is updating tables that it was not supposed to do. In a scenario where multiple source replication works on the assumption that each master updates a given subset of the data, we can easily detect and eventually reject offending updates. It is not much, but in many practical cases it would be the difference between having robust multiple source replication and doing data load and consolidation manually.

Anyway, back to the present day with very much real multi-master replication available for everyone. To alleviate the fear of the unknown, we are organizing webinars on a regular basis, where we cover the theoretical points and give practical demos of how to use the new features.
If you are a demanding user, this upcoming webinar is for you: MySQL Multi-Master and Multi-Source Replication With Tungsten. Tomorrow, March 31^st, 2011, at 10am PDT.

How fast is parallel replication? See it live today

2011-03-17T13:18:00.003+01:00

I talked about parallel replication last month. Since then, there has been a considerable interest for this feature. As far as I know, Tungsten's is the only implementation of this much coveted feature, so I can only compare with MySQL native replication.
The most compelling question is "how fast is it?"
That's a tricky one. The answer is the same that I give when someone asks me "how fast is MySQL". I always say: it depends.
Running replication in a single thread is sometimes slower than the operations in the master. Many users complain that the single thread can't keep up with the master, and the slave lags behind. True. There is, however, a hidden benefit of single threaded replication: it requires less resources. There is no contention for writing on disk, no need to worry about several users blocking a table. You need to contend with the users that want to read the tables, but the lone writer has an easy job, albeit a hard one.
When we introduce parallel replication, the easy job fades away, and we are faced with the problem: how do I allow several writers to do the work of one? It's a nice problem to have. MySQL native replication does not allow parallel apply, but with Tungsten you can start tackling the issue of allowing several parallel threads to update the system at once. Therefore, this is the same problem that you have on a server where several users are allowed to write at once. If the server has sufficient resources, the operations will be fast. If it doesn't, the operations will lag behind.
Another aspect of the question is "what kind of queries?" If your database is well established and set in stone, and you mostly UPDATEs, the replication performance will depend on how well your server is tuned for concurrent writes. If you run ALTER TABLE statements on a daily basis, your queries will queue up after that ALTER TABLE no matter what. And if you have only INSERT and DELETE queries, parallel replication will probably depend on how fast is your server.
Ultimately, I can tell you that I have seen or experienced directly a wide range of repeatable results. I know cases where parallel replication is three times as fast as native replication. These cases usually involve huge amounts of binary logs, like in the case when your slave needs to be taken off-line for a few hours or even days and then it tries to catch up. Other cases that can be reproduced with a minimal amount of sample data show parallel replication as being 30% to 50% faster. ANd then there are cases when your server is so poor on resources or the load is so unevenly distributed that parallel replication is as fast as native replication. I would say that these cases are easily cured by beefing up the server.
If you want to see a demo of how this replication works, you can join this webinar:
Zoom, Zoom, Zoom! MySQL Parallel Replication With Tungsten Replicator 2.0.
You can tell from the title that we are quite excited about the product that we are building.

Quick benchmarking trick

2011-03-15T01:27:00.001+01:00

I have been doing quite a lot of benchmarking recently.
I needed to find a safe way of measuring the time spend by the database doing a long task, like catching up on a huge backlog of accumulated replication updates. The problem with measuring this event is that I can record when it starts, but I can't easily detect when it finishes. My initial approach was to monitor the database and count the tables rows to see when the task was done, but I ended up affecting the task performance with my additional queries. So I thought of another method.
Since I had control on what was sent from the master to the slave, I used the following:
The initial time is calculated as the minimum creation time of the databases that I know are created during the exercise. Let's say that I had 5 databases named from db1 to db5:

set @START = (select min(create_time) from information_schema.tables where table_schema like "db%")

Then, to make sure that I catch the exact moment that the task is finished, I added to the master a command for each database:

create table db1.last_table (i int);
create table db2.last_table (i int);
create table db3.last_table (i int);
create table db4.last_table (i int);
create table db5.last_table (i int);

To know if the task is done, I query the database as follows:

select count(*) from information_schema.tables where table_schema like "db%" and table_name="last_table";

If the count is less than 5 (the number of databases that were in my binary logs), I wait more.
Finally, when the count matches the expected one, I get the end time:

set @END = (select max(create_time) from information_schema.tables where table_schema like "db%" and table_name="last_table"');

Now I have two values, @START, and @END

select timediff(@END,@START) as elapsed;
+----------+
| elapsed  |
+----------+
| 00:09:44 |
+----------+

It does not matter if I query the database immediately, or hour after coming back from my errands. Using the table creation times makes sure that I get a clean start and finish time.
I put all the above in a script, and I can check the elapsed time without fear of mistakes.

A cool terminal tip for Mac users

2011-03-13T22:20:00.000+01:00

If you use a Mac, and you are dealing with many similar tasks at once, like examining many database servers in different terminals, you may like this one.
I have been using iTerm 2 for a while, and my handling of parallel tasks has improved a lot. (No, I am not talking about Parallel replication, although I have applied this trick while testing that technology as well.)
iTerm2 has some cool features, and probably the most striking one is split panes. That alone would be a good reason for giving iTerm2 a try. But the one that I use the most, often in combination with Split Panes, is called Send Input to all tabs.
Here is how it works.
Let's say I need to use 4 servers at once, and perform a non-repeating operation in all of them.
So I open a separate window and I split the screen into 5 panes. I connect to each server in the first four panes, and I open a vim instance in the fifth.
With that done, I enable the magic option.

A word of caution. This option sends the input to all the open tabs in your current window. If you don't want this to happen, do as I do, and open a separate window. Then make sure that all tabs, and eventually split panes, are supposed to receive your input. The application asks you for confirmation.

After that, whatever I type on one pane will be mirrored on all the panes. So I will see the commands running on my four servers, and being logged in a text file in the fifth one. All with just single command, I have all servers under control at once:

Replication and sandbox talks on the road - San Francisco, Santa Clara, Orlando

2011-03-13T15:22:00.000+01:00

In a few weeks, I will be on the road, for an intense set of presentations in the USA.

San Francisco, April 7th

I will start the tour at the San Francisco MySQL User Group. On April 7, at 6pm I will talk about Advanced MySQL replication for the masses. This talk will explore topics such as bi-directional replication, multiple sources, parallel replication, seamless failover, with the help of Tungsten replicator.

Santa Clara, April 11, 12

The MySQL conference starts on Monday, April 11th, with several three-hour tutorials.

April 11, 9:00amMySQL Replication Advanced Techniques. This will cover MySQL replication beyond the basics. Everything you can do with replication, including, tips, tricks, and gotchas, plus some really advanced things that you can achieve with third party tools.
On tutorial day, at 7pm, there will be the 2011 MySQL community dinner (west), organized by Sarah Novotny and probably attended by the best MySQLers in town. If you want to get the latest gossip and socialize with the top MySQL geeks, this is the place to be.
April 12, 11:55am The Art of Sandboxing - Reducing Complex Systems to Manageable Boxes. After the State of the Dolphin, here's a chance of learning a few practical tips on how to build respectful systems.

Robert Hodges and Edward Archibald will also be presenting at the MySQL Conference.

April 11, 1:30pm Learn how to cure MySQL replication deprivation with Tungsten!
April 13, 2pmPreparing for the Big Oops: How to Build Disaster Recovery Sites for MySQL
TBD Build your own PaaS for MySQL with Tungsten Enterprise

On Tuesday evening I will fly to Orlando, to attend (part of) Collaborate11.

Orlando, April 13

April 13, 1pm Dealing with large data with MySQL partitioning and replication. This talk will explain how to combine replication and partitioning to achieve performance, without any external tool.
April 13, 4pmMySQL Sandbox : a toolkit for productive laziness. One of my favorite subjects. How to use the MySQL Sandbox to achieve quick results with minimal effort

The MySQL Council addresses the public bug database issue

2011-03-08T10:29:00.000+01:00

When I announced the MySQL Council, I said that among its roles there is that of being a bridge between the MySQL community and Oracle.
It has come the time where we put this role to good use. Recently, there have been some concerns about the MySQL bugs database, which could be summarized in Mark Callaghan's post Where have the bugs gone?.
The gist of the concerns is that there has been a change in the bugs handling, although we don't know what was changed and how. In short, there has been a total lack of communication. The MySQL Council has addressed the concerns about the public bug database in a recent meeting, and has taken several steps, like approaching Oracle directly, and releasing a summary of the concerns in its site.

The MySQL Council members have been discussing the decision by Oracle, to reduce the importance of the public MySQL bug database for providing input and direction of product updates and direction. The Council would also like to work with Oracle to promote communication around the status of the database access to the broader community so members will understand what to expect moving forward.

Without communication around the use and changes relating to the public bug database, there have been concerns in the community raised about duplicate bug tracking, bug numbers in commits not being visible to the public, difficulty in offering patches into the MySQL server, and the generalized decreased transparency in the evolution and remediation of the MySQL server and associated products.

The IOUG (Independent Oracle User Group) is supporting the MySQL Council in its efforts to raise questions and query direction from Oracle. The MySQL Council will be meeting with Oracle stakeholders to discuss options for keeping appropriate portions of the database active as well as communicating status and future actions to the broader community.

We don't know the outcome yet. But we'll surely post an update as soon as we hear it.

implementing table quotas in MySQL

2011-03-07T10:43:00.002+01:00

I have just seen Limiting table disk quota in MySQL by Shlomi Noach, and I could not resist.
You can actually implement a disk quota using an updatable view with the CHECK OPTION.
Instead of giving the user access to the table, you give access to the view (at least for inserting, see the caveat at the end), and you will get a genuine MySQL error when the limit is reached.

drop table if exists logs;
create table logs (t mediumtext) engine=innodb;

drop function if exists exceeded_logs_quota ;
create function exceeded_logs_quota() 
returns boolean
deterministic
return (
    select CASE 
           WHEN (DATA_LENGTH + INDEX_LENGTH) > (25*1024) 
           THEN TRUE ELSE FALSE 
           END
    FROM 
        information_schema.tables 
    WHERE 
        table_schema=schema() 
        and table_name='logs'
    );

create or replace view logsview as 
    SELECT * FROM logs 
    WHERE NOT exceeded_logs_quota()
    WITH CHECK OPTION;

Here's a test run:

mysql [localhost] {msandbox} (test) > insert into logsview values ('a');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select exceeded_logs_quota();
+-----------------------+
| exceeded_logs_quota() |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into logsview values (repeat('a', (25 * 1024) - 1));
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select exceeded_logs_quota();
+-----------------------+
| exceeded_logs_quota() |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into logsview values ('b');
ERROR 1369 (HY000): CHECK OPTION failed 'test.logsview'

You will need to twist the limit to adapt to InnoDB habits of allocating pages rather than bytes, but if you measure the limit in MB the method should work fine.

CAVEAT: You should give your users separate privileges: SELECT on logs, and INSERT on logsview. The view will only return records while exceeded_logs_quota() returns false.

mysql [localhost] {msandbox} (test) > select exceeded_logs_quota();
+-----------------------+
| exceeded_logs_quota() |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select count(*) from logsview;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql [localhost] {msandbox} (test) > select count(*) from logs;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

Advanced replication for the masses - Part III - Replication topologies

2011-03-06T18:44:00.000+01:00

After part I: the basics, and part II: parallel apply, we deal now with some more mundane topic, or how to deploy replication services in a way that they fit our business, covering from the basic master/slave to the most advanced multi-source scheme.

Master/slave

The most common topology is master/slave. One master, many slaves. This topology is equivalent to MySQL native replication. The differences are in the additional features. Tungsten supports seamless failover and parallel replication in all topologies.

Figure 1. Tungsten master/slave replication
Unlike MySQL, and unlike previous versions of Tungsten, the implementation of this topology uses a dedicated service for the master, and deploys a corresponding service on each slave. In MySQL, and old Tungsten, there is just a general purpose pipeline that connects from master to slave. In Tungsten Replicator, instead, you need to define a service explicitly. While this requirement looks like overkill for such a simple topology, it will be clear that it helps defining advanced topologies.

Relayed replication

The first time that I saw this topology with Tungsten, it was by mistake. I wanted to connect host Delta with host Alpha, but by a combination of bad cut-and-paste and mindlessness, I connected the slave Delta to a seemingly non-existent master Bravo (figure 2).

Figure 2. Tungsten master/slave replication with relay
The funny thing is that I did not realize the error, because the test that I wrote to certify that what I inserted in the master was then usable in all the slaves, worked without a problem. Thus I learned another feature of Tungsten: every slave is also a relay slave, without need of any additional setup. It does not matter if the slave is using binary logs, or if it has enabled the logs-slave-updates option. Those requirements are for MySQL native replication. Tungsten slave replicator services can detect a request from another slave, and act as a relay slave if necessary.

Bi-directional replication

Here is where people used to MySQL replication start to be surprised. To set a master-to-master topology, we deploy two services for each host. The first host sets a master service named alpha, and a remote slave service named bravo. The second host does the opposite: a local master service named bravo, and a remote slave service named alpha. (Figure 3)

Figure 3. Tungsten bi-directional replication
Whatever is updated on host alpha is processed and sent to remote slaves by the master replicator service alpha. Whatever is updated in host bravo is processed by the master replicator service bravo and dispatched to its audience.
This system works like MySQL master-to-master replication. There is no conflict resolution handling. The prerequisite, for this and the following topologies, is that you know what you are doing and leverage the replication system within its limitations.

Multiple site replication

This is an extension of bi-directional replication. For each master, there is one or more slaves. Figure 4 shows a typical implementation. You have a main site in one location, and want to have a backup system in another location. If disaster strikes in your main location, you are ready to switch over to the alternate site with minimum delay.

Figure 4. Tungsten multiple site replication
The reliability and robustness of this scheme depends on your applications. Nothing prevents you from writing to both masters at once. And if you keep the tasks logically separated (e.g. master alpha writes on database products, while master bravo writes on database employees) nothing bad will happen. But if your applications update the same records, you can suffer either a duplicate key error or silent loss of changes when a master writes on top of an already updated record.
This topology is frequently used in combination with a virtual IP provider, a system that shields both clusters from the application's view, and let it connect through an IP that is associated to the active master. There are many such systems, including Linux HA and Tungsten Enterprise. But that is beyond the scope of this post.

Multiple source replication

This topology is extremely popular, at least judging from the many times that it has been requested to me. So strangely popular, in fact, because it does not exist, at least as far as MySQL replication is concerned. One of the limits of MySQL native replication is that every slave can have only one master.
This topology has been explained to me in many ways. One of the most common is this. You have a company that has a headquarters in a town, say New York. That company has stores in many cities, and the management would like to get the data from each store to the headquarters, in real time. This is the opposite of the master/slave topology, and no matter how creative you become, you can't get MySQL to do it.
Using Tungsten, you can implement this topology fairly easily. (Figure 5)

Figure 5. Tungsten multiple source replication
Each server deploys a master replicator service. The host in headquarters deploys one slave service for each of the remote masters.
Of course, there is no conflict resolution handling. If you r remote masters don't behave within the limits that you want them to have, you will get in trouble, and replication may stop. However, if you know what you're doing and set the system properly, at least you can achieve the goal of getting this reverse replication scheme working smoothly.

Hands on

To help the early adopters, I have put together a set of scripts that deploy easily any of the topologies mentioned in this post with just a few actions.
What you need is 4 hosts (four copies of the virtual machine mentioned in part I will do), and the Tungsten deployer package that you will get from the Tungsten downloads page.
Once you have the four server, unpack the deployer scripts on a chosen directory in all servers, making sure that the directory is the same in all four. Inside the package, there is a README file with a detailed list of instructions. So detailed, in fact, that I won't copy it here because it would make this post too long.

I will just show a sample run:

$ ./set_multi_source.sh 
QA1
installation OK

NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 1.243
role            : master
serviceName     : alpha
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

QA2
installation OK

NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 1.264
role            : master
serviceName     : bravo
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

QA3
installation OK

NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 0.812
role            : master
serviceName     : charlie
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

QA4
installation OK

Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 29.521
role            : slave
serviceName     : alpha
serviceType     : local
started         : true
state           : ONLINE
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 20.123
role            : slave
serviceName     : bravo
serviceType     : remote
started         : true
state           : ONLINE
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 12.726
role            : slave
serviceName     : charlie
serviceType     : remote
started         : true
state           : ONLINE
Finished services command...

$ ./test_flow_multi_source.sh 
inserting 100 into each of the three masters. Please wait
Retrieving data from the masters
QA1
+----+-----+------+
| t  | c   | s    |
+----+-----+------+
| t1 | 100 | 5050 |
+----+-----+------+

QA2
+----+-----+------+
| t  | c   | s    |
+----+-----+------+
| t2 | 100 | 5050 |
+----+-----+------+

QA3
+----+-----+------+
| t  | c   | s    |
+----+-----+------+
| t3 | 100 | 5050 |
+----+-----+------+

Retrieving data from the slave
QA4
+----+-----+------+
| t  | c   | s    |
+----+-----+------+
| t1 | 100 | 5050 |
| t2 | 100 | 5050 |
| t3 | 100 | 5050 |
+----+-----+------+

Happy hacking!

A hidden options file trick

2011-03-05T20:32:00.001+01:00

I was listening today to the OurSQL Episode 36: It's Not Our (De)fault! Part 1. As usual, Sheeri and Sarah are very informational and entertaining while explaining the innards of MySQL and their best practices.
Being a DBA oriented show, there was an omission in this podcast. There was no mention of custom groups that you can have for your my.cnf. This is mostly useful for developers. If your application requires some specific settings, instead of using a separated configuration file, you can use a different group, and then instruct your client applications to use that group.
By default, all client applications read the "[client]" group.
But you can tell your client to read a group that you can call whatever you like.
For example, with this configuration file,

[client]
user=common_user
password=common_password

[logrotation]
user=log_rotation_daemon
password=specific_password

You can have a Perl script that takes care of your particular log rotation needs. Instead of the normal credentials, it will use the ones listed in the [logrotation] group.

use strict;
use warnings;
use DBI;

my $dsn =   "DBI:mysql:test;"
            . "mysql_read_default_group=logrotation;"
            . "mysql_read_default_file=$ENV{HOME}/./my.cnf";
my $dbh = DBI->connect($dsn);

Notice that, for this option to work, the [logrotation] group must come after the [client] group, or the directives in the [client] group will override the ones in [logrotation]. That's why, in the options file, you find the directives for [mysqldump] at the bottom of the file.

So far, so good. This was a trick for developers, and probably many developers know it already. But there is another, related trick, that can be used by non-developers as well.
If you knew about these customized groups, you may have realized that you can't use them with the mysql standard command line client. Or, to say it better, there is no clearly documented way of doing so. There is, in fact, a cute trick that you can use.
Let's say that, from time to time, you want to use a different prompt, but you don't want to edit your $HOME/.my.cnf to change it. You just want your prompt to be there in the option file, and be able to recall it when the situation calls for it.
The mysql internal help does not tell anything about groups. However, a careful search of the manual gives this cryptic entry:

--defaults-group-suffix=suffix, -g suffix

In addition to the groups named on the command line, read groups that have the given suffix.

When I found it, I stared at this puzzling statement for a while. I could not understand which are the groups that are named in the command line.
Eventually, I figured out why there is a group-suffix and not simply a group. It means that if you add a suffix to a default group name, and you tell mysql to look for this suffix, then you will be able to use the appropriate group on demand.
For example, this options file will not work.

# wrong
[pinocchio]
prompt='I have a long nose  =======> '

[master]
prompt='master [\h] {\u} (\d) > '

[slave]
prompt='slave [\h] {\u} (\d) > '

But this one will work:

[mysqlpinocchio]
prompt='I have a long nose  =======> '

[mysqlmaster]
prompt='master [\h] {\u} (\d) > '

[mysqlslave]
prompt='slave [\h] {\u} (\d) > '

Here is a test run:

$ mysql --defaults-group-suffix=pinocchio
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.1.54 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

I have a long nose  =======>

The meaning of the suffix part is that mysql will read the default groups (which are [client], and [mysql]), and it will also read any groups that are named "mysqlSUFFIX" or "clientSUFFIX". I have named the group "mysqlpinocchio" and therefore it has been used. It would have worked the same if I had called it "clientpinocchio".

beware of the log

2011-03-02T16:25:00.000+01:00

The MySQL general log is one of my favorite features for a quick debug. I especially like the ability of starting and stopping it on demand, which was introduced in MySQL 5.1.
However, using the general log has its drawbacks.
Today I was debugging a nasty bug that results from two statements that should be applied sequentially, but that were instead concurrent. These kind of problems are hard to cope with, as they are intermittent. Sometimes all goes well, and you get the expected result. And then, sometimes the statements fly on different directions and I stare at the screen, trying to understand where did they stray.
After some try-and-fail, I decided to enable the general log just before the offending statements, and to turn it down immediately after. Guess what? With the general log on, the test never failed. What was an intermittently failing test became an always succeeding test.
What happened is that the general log delayed the query execution just enough for the following statement to arrive when it was expected.
In the end, the bug had to be unveiled using white box techniques.
Moral of the story: using a general log alters the status of the server. If you use it, be prepared to deal with its side effects.

Advanced replication for the masses - Part II - Parallel replication

2011-02-25T11:09:00.000+01:00

I hope you liked the first part of this series of lessons. And I really hope that you have followed the instructions and got your little replication cluster up and working.

If you haven't done that, thinking that you would spare your energies for more juicy matters, I have news for you. What I explained in the previous part is exactly what you need to do to set up parallel replication. With just a tiny additional detail.
For the sake of the diligent readers who have followed the instructions with the first lessons, I won't repeat them, but I'll invite you to set the environment as explained in the first part.
Once you have a cluster up and running, and you can confirm that replication is indeed working with Tungsten, you can remove all with the clear_cluster.sh script, and launch again the set_installation.sh script, with the tiny detail we have mentioned above.
The astute readers may have noticed that the installation script contains these lines:

...
MORE_OPTIONS=$1
./configure-service --create --role=master $MORE_OPTIONS logos1
./tungsten-replicator/bin/trepctl -service logos1 start

./configure-service --create --role=slave --master-host=QA2 \
    --service-type=remote $MORE_OPTIONS logos2
...

This means that you can start set_replication.sh with one additional option, which will be passed to the creation of the Tungsten service. Without further suspense, the addition that you need is --channels=5.
Yep. It was that easy.

./set_replication.sh --channels=5

This little addition will start your Tungsten replicator, apparently in the same way it did before. But there is a substantial difference. While the data is funneled from the master to the slaves in the usual way, the applier splits the data by database. You can see the difference as soon as you send some data through the pipeline.

#master
mysql -h tungsten1 -e 'create schema mydb1'
mysql -h tungsten1 -e 'create schema mydb2'
mysql -h tungsten1 -e 'create schema mydb3'
mysql -h tungsten1 -e 'create table mydb1.t1 (i int)'
mysql -h tungsten1 -e 'create table mydb2.t1 (i int)'
mysql -h tungsten1 -e 'create table mydb3.t1 (i int)'
mysql -h tungsten1 -e 'select seqno,source_id,eventid  from tungsten_logos.trep_commit_seqno'
+-------+-----------+----------------------------+
| seqno | source_id | eventid                    |
+-------+-----------+----------------------------+
|     6 | tungsten1 | 000002:0000000000000939;43 |
+-------+-----------+----------------------------+

Everything under control. The master has sent 6 events through the pipeline. Now, let's see what the slave has to say:

# slave
mysql -h tungsten2 -e 'select seqno,source_id,eventid  from tungsten_logos.trep_commit_seqno'
+-------+-----------+----------------------------+
| seqno | source_id | eventid                    |
+-------+-----------+----------------------------+
|     0 | tungsten1 | 000002:0000000000000426;34 |
|     0 | tungsten1 | 000002:0000000000000426;34 |
|     4 | tungsten1 | 000002:0000000000000763;41 |
|     5 | tungsten1 | 000002:0000000000000851;42 |
|     6 | tungsten1 | 000002:0000000000000939;43 |
+-------+-----------+----------------------------+

Notice, at first sight, that there are five rows instead of one. Each row is a channel. Since the master has used three databases, you see three channels occupied, each one showing the latest sequence that was applied. Now, if we do something to database mydb2, we should see one of these channels change, while the others stay still.

# master
mysql -h tungsten1 -e 'insert into mydb2.t1 values (1)'
mysql -h tungsten1 -e 'insert into mydb2.t1 values (2)'

# slave
mysql -h tungsten2 -e 'select seqno,source_id,eventid  from tungsten_logos.trep_commit_seqno'
+-------+-----------+----------------------------+
| seqno | source_id | eventid                    |
+-------+-----------+----------------------------+
|     0 | tungsten1 | 000002:0000000000000426;34 |
|     0 | tungsten1 | 000002:0000000000000426;34 |
|     4 | tungsten1 | 000002:0000000000000763;41 |
|     8 | tungsten1 | 000002:0000000000001124;45 |
|     6 | tungsten1 | 000002:0000000000000939;43 |
+-------+-----------+----------------------------+

The channel used by mydb2 had previously applied the sequence number 5. The latest sequence number was previously 6, used in another channel. After two more events in this database, the sequence number has jumped to 8.
The eventID has also changed. The first part of the eventID is the binary log number (as in mysql-bin.000002), the second is the log position (1124), and the third one is the session ID (45).
Enough of peeking over the replicator's shoulder. There are more tools that let you inspect the status of the operations.
We have seen trepctl services, which keeps some of its usefulness also with parallel replication. In the master, it says:

trepctl -host tungsten1 services
NAME              VALUE
----              -----
appliedLastSeqno: 8
appliedLatency  : 0.834
role            : master
serviceName     : logos
serviceType     : local
started         : true
state           : ONLINE

Which is mostly all we need to know.
Since the slave has more than one channel, though, we need more specialized information on that side of the applier. For this reason, we use a more specialized view. We may start with trepctl status, which has information that is roughly equivalent to "SHOW SLAVE STATUS" in MySQL native replication.

trepctl -host tungsten2 status 
NAME                     VALUE
----                     -----
appliedLastEventId     : 000002:0000000000000426;34
appliedLastSeqno       : 0
appliedLatency         : 0.846
clusterName            : 
currentEventId         : NONE
currentTimeMillis      : 1298626724016
dataServerHost         : tungsten2
extensions             : 
host                   : null
latestEpochNumber      : 0
masterConnectUri       : thl://tungsten1:2112/
masterListenUri        : thl://tungsten2:2112/
maximumStoredSeqNo     : 8
minimumStoredSeqNo     : 0
offlineRequests        : NONE
pendingError           : NONE
pendingErrorCode       : NONE
pendingErrorEventId    : NONE
pendingErrorSeqno      : -1
pendingExceptionMessage: NONE
resourcePrecedence     : 99
rmiPort                : -1
role                   : slave
seqnoType              : java.lang.Long
serviceName            : logos
serviceType            : local
simpleServiceName      : logos
siteName               : default
sourceId               : tungsten2
state                  : ONLINE
timeInStateSeconds     : 3483.836
uptimeSeconds          : 3489.47

Also this command, which is perfectly useful in single channel replication, lacks the kind of detail that we are after. Tungsten 2.0 introduces two variations of this command, with more detailed metadata.

trepctl -host tungsten2 status -name tasks
Processing status command (tasks)...
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000001305;46
appliedLastSeqno  : 8
appliedLatency    : 0.84
cancelled         : false
eventCount        : 9
stage             : remote-to-thl
taskId            : 0
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000001305;46
appliedLastSeqno  : 8
appliedLatency    : 0.841
cancelled         : false
eventCount        : 9
stage             : thl-to-q
taskId            : 0
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000000426;34
appliedLastSeqno  : 0
appliedLatency    : 8.422
cancelled         : false
eventCount        : 2
stage             : q-to-dbms
taskId            : 0
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000000426;34
appliedLastSeqno  : 0
appliedLatency    : 8.424
cancelled         : false
eventCount        : 1
stage             : q-to-dbms
taskId            : 1
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000000763;41
appliedLastSeqno  : 4
appliedLatency    : 0.242
cancelled         : false
eventCount        : 3
stage             : q-to-dbms
taskId            : 2
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000001305;46
appliedLastSeqno  : 8
appliedLatency    : 0.846
cancelled         : false
eventCount        : 5
stage             : q-to-dbms
taskId            : 3
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000000939;43
appliedLastSeqno  : 6
appliedLatency    : 0.296
cancelled         : false
eventCount        : 3
stage             : q-to-dbms
taskId            : 4

The -name tasks command gives you a list of the latest tasks that were happening.
This is probably more information that you want to know about, but in case of troubleshooting it may become a blessing. Let's follow for a moment what's going on to appliedLastSeqno 8. You will find three tasks with this sequance number. The first one has stage "remote-to-thl", which is the stage where the transaction is transported from the master to the Transaction History List (THL, which is Tungsten lingo to what you may also call a relay log.). The second task that mentions appliedLastSeqno 8 is in stage "thl-to-q", which is the phase where a transaction is assigned to a given shard. The third occurrence happens in stage "q-to-dbms", which is where the transaction is executed in the slave.
For a different view of what is going on, you may use trepctl status -name shards. A Shard, in this context, is the criteria used to split the transactions across channels. By default, it happens by database. We will inspect its mechanics more closely in another post. For now, let's have a look at what shards we have in our slave:

trepctl -host tungsten2 status -name shards
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000000763;41
appliedLastSeqno  : 4
appliedLatency    : 0.0
eventCount        : 2
shardId           : mydb1
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000001305;46
appliedLastSeqno  : 8
appliedLatency    : 0.0
eventCount        : 4
shardId           : mydb2
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000000939;43
appliedLastSeqno  : 6
appliedLatency    : 0.0
eventCount        : 2
shardId           : mydb3
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: 000002:0000000000000426;34
appliedLastSeqno  : 0
appliedLatency    : 0.0
eventCount        : 6
shardId           : tungsten_logos
stage             : q-to-dbms

You may read the information quite easily. Each shard tells you by which key it was identified (shardID), and this is the same as the database name. The appliedLastSeqno and stage we have met already. The appliedLatency is roughly equivalent to MySQL's seconds behind master, but more granular than that. And eventCount tells you how many transactions went through this shard.
If you are the adventurous type, you may have a look at the THL itself, and get a glimpse of how the replication and the parallelism works.
In the slave, type the following

# slave
thl -service logos list |less

Then look for "SEQ#" and you will find the global transaction IDs, or look for "shard=", and you will see the split by database.

More goodies will come next week. Until then, happy hacking!

Advanced replication for the masses - Part I - Getting started with Tungsten Replicator

2011-02-22T18:47:00.002+01:00

MySQL DBAs and developers: oil your fingers and get ready to experience a new dimension of data replication. I am pleased to announce that Continuent has just released Tungsten Replicator 2.0, an open source data replication engine that can replace MySQL native replication with a set of advanced features.
A note about the source code. The current version of Tungsten Replicator available in the web site is free to use, but it is not yet the open source version. We need a few weeks more to extract the code from the enterprise tree and make a new build. But we did not want to delay the user experience. So everything that is in this build will come with the source code in a short while. In the meantime, enjoy what is available there and have as much fun as we are having.

Why you will want to install Tungsten Replicator 2.0

Tungsten Replicator has a real cool list of features. I am sure that most MySQL DBAs would find something in that list that makes their mouth water in expectation.
Among my favorite features, there is one that looks so innocently non-important that you may be tempted to dismiss it. I am talking about global transaction ID, which is paramount in helping the DBA in switching from master to slave in case of failure or maintenance. I will show an example of a seamless failover in this article.
More things to get excited about: Tungsten allows multiple master replication, i.e. one slave receiving data from several sources, and parallel replication, meaning that a slave can apply changes from the master using many parallel threads. I will talk about all of those features in my blog. But to get to that point, I will need to start by covering the basic installation first. Since Tungsten is much more powerful than MySQL native replication, it also comes with greater complexity. We are working at reducing such complexity. In the meantime, you can start with the instructions that come in this post.

Getting ready

You will need at least two servers, with Java 1.6, Ruby 1.8, and MySQL 5.1 installed.
You may use your own virtual machines, or spare servers, or you can use a pre-defined VMWare image that you can use with VMware player (or VMware Fusion on Mac).
The following instructions refer to the pre-configured VM. You may skip the initial steps if you are using your own servers.

download a pre-configured image
https://files.continuent.com.s3.amazonaws.com/Tungsten_MySQL_CentOS_5_5_VMWare_Image.7z
Warning: it's 1.5 GB, and it expands to 5.5 GB
Expand the VM
Make a copy of the VM. Change the directory names so that you will refer to them as tungsten_vm1 and tungsten_vm2
launch both VMs
Connect to each VM. User names and password for root are in a .doc file within the VM directory.
Change the hostname of the VMs to tungsten1 and tungsten2 (don't forget to modify /etc/sysconfig/network to make the name sticky)
Update /etc/hosts/ with the IP address and hostname of both servers
Switch to the tungsten user
```
su - tungsten
```
Create a directory $HOME/replicator

Get the Tungsten package into that directory

cd replicator
wget https://s3.amazonaws.com/releases.continuent.com/tungsten-replicator-2.0.0.tar.gz

Get the setup scripts from Tungsten Replicator home .

wget http://tungsten-replicator.googlecode.com/files/simple_install_master_slave.tar.gz

unpack the scripts in $HOME/replicator

I know this was a long list, but it is not terribly difficult. More difficult would be setting all the above manually. As it is today, all you need to do is running the "set_replication.sh" script and Tungsten will come alive to your server in less than one minute.
To do things properly, you will need to do the same operations on both servers. So, assuming that you have done everything in tungsten1, you can easily mirror the operations to tungsten2. The virtual machines come with an already installed public SSH key that makes your installation life easier.

# in tungsten1
cd $HOME/replicator
ssh tungsten2 mkdir replicator
scp simple_install_master_slave.tar.gz tungsten2:$PWD
scp tungsten-replicator-2.0.0.tar.gz tungsten2:$PWD
ssh tungsten2 'cd replicator; tar -xzf simple_install_master_slave.tar.gz '

Now that you have the same set of files in both machines, you can trust the wisdom of the installation files and run:

# tungsten1
./set_replication.sh
ssh tungsten2 $PWD/set_replication.sh

This will start the Tungsten replicator in both servers.

Cleaning up

The sample scripts come with one that is dedicated to cleaning up. There is a "clear_cluster.sh" script that will remove all test data from the database, sweep the tungsten directory away, leaving your system ready to start afresh. As this is a testing environment, this strategy is not so bad. But be aware of the potentially destructive nature of this script, and don't use it in a production environment.

Under the hood

Tungsten replicator is a complex piece of software, and it's easy to get lost. So here are a few tips on how to get your bearings.
You will find a log file under $HOME/replicator/tungsten/tungsten-replicator/logs/.
This is quite a noisy log, which is supposed to give the developers all information about what's going on in case of a failure. For newcomers, it is quite intimidating, but we are working at making it easier to read. (Be aware that you may find some references to "tungsten-enterprise" in the logs. Don't let this fact deter you. We are working at splitting the former name associations from the packages, and eventually you will only find references to modules named "tungsten-replicator-something" in the logs.)
At the end of the installation, you should have seen a line inviting you to modify your path to get the replication tools available at your fingertips. Most notable is trepctl, the Tungsten Replicator ConTroL.
Using this tool, you can get some information about the replicator status, and perform administrative tasks. A glimpse at the Tungsten Replicator Guide 2.0 will give you an idea of what you can do.
For now, suffices to say that you can use trepctl to get the state of the replicator.
Try, for example, the following:


$ trepctl -host tungsten1 services
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 0.933
role            : master
serviceName     : logos
serviceType     : local
started         : true
state           : ONLINE

$ trepctl -host tungsten2 services
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 0.966
role            : slave
serviceName     : logos
serviceType     : local
started         : true
state           : ONLINE

The most important things here are the "state" field, and the "appliedLastSeqno", which is the global transaction ID that we have mentioned before.
If you create or modify something in the master and issue this command again, you will see that the appliedLastSeqno will increment.
You can get some of this information from the MySQL database, where Tungsten keeps a table with the latest status. You may say that this table is roughly equivalent, at least in principle, to the information in SHOW SLAVE STATUS available with native replication.


$ mysql -h tungsten1 -u tungsten -psecret \
    -e 'select * from tungsten_logos.trep_commit_seqno\G'
*************************** 1. row ***************************
        task_id: 0
          seqno: 0
         fragno: 0
      last_frag: 1
      source_id: tungsten1
   epoch_number: 0
        eventid: 000002:0000000000000416;102
applied_latency: 0

What is this "tungsten_logos' database? It is the database that Tungsten creates for each service that was installed. In this case, 'logos' is the service name contained in this sample installation. If you modify the scripts in both servers, and replace 'logos' with 'ripe_mango', you will see that Tungsten creates a 'tungsten_ripe_mango' database, with the same kind of information.

The basic principle to acquire before moving to more complex topics is that replication in Tungsten is a collection of services. While the native MySQL replication is a simple pipeline from master to slave, without deviations, Tungsten implements several pipelines, which you can use one by one or in combination. It looks more complex than necessary, but in reality it makes your planning of complex topologies much easier. Instead of making basic replication more complex, Tungsten adopt the principle of deploying the appropriate pipeline or pipelines for the task.
I leave to Robert Hodges, CEO and main architect of Tungsten, the task of explaining the nuts and bolts.

A sample of Tungsten power: switching from master to slave

It is probably too much information already for a blog post, but I would like to leave you with the feeling that you are dealing with an extremely powerful tool.
The instructions below will perform a seamless switch between the master and the slave.
Please follow these steps, but make sure there is no traffic hitting the old master during this time, or you may experience consistency issues:


#first, we tell both servers to stop replicating
$ trepctl -service logos -host tungsten2 offline
$ trepctl -service logos -host tungsten1 offline

# Now that they are offline, we tell each server its new role
# tungsten2 becomes the new master
$ trepctl -service logos -host tungsten2 setrole -role master 

# and then we tell tungsten1 that it's going to be a slave,
# listening to tungsten2 for data
$ trepctl -service logos -host tungsten1 setrole -role slave -uri thl://tungsten2

# now we put both servers online with the new instructions
$ trepctl -service logos -host tungsten2 online
$ trepctl -service logos -host tungsten1 online

# and we check that indeed they are both online with the new roles.
$ trepctl -host tungsten1 services
$ trepctl -host tungsten2 services

After this set of instructions, tungsten2 is the master, and if we write to it, we will see the changes replicating to tungsten1.

That's it for today. In the next articles, we will take a look at parallel replication.

We want to hear from you

We have released Tungsten Replicator as open source because we believe this will improve the quality of our product. We are looking for bug reports, cooperation, suggestions, patches, and anything that can make the product better. You can report bugs at the project home.
We are particularly eager to hear about user experience. We are aware that the user interface can be better, and we need some input on this matter from interested users.

A note about security

What is described in this article is for testing purposes only. Please use the virtual machines that were mentioned in this article behind a firewall. The VM was designed with friendliness in mind, but as it is, it's far from secure.

How to detect if a MySQL server is an active replication slave

2011-02-14T12:10:00.000+01:00

Sometimes you know for sure. And sometimes you wonder: Is this server part of a replication system? And, most specifically, is it an active slave?
The completeness of the answer depends on how much visibility you have on the server.
If you can ask the DBA, and possibly have access to the server data directory and configuration file, you can get a satisfactory answer. But if your access is limited to SQL access, things get a bit more complicated.
If you have the SUPER or REPLICATION_CLIENT privilege, then it's easy, at least in the surface.
SHOW SLAVE STATUS will tell you if the slave is running. An empty set means that the server was not configured as a slave.
The answer is not absolute, though. You need to read the output of SHOW SLAVE STATUS to understand if replication is under way.
For example, what is the difference between these two listings?


## listing 1
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: QA1
                  Master_User: tungsten_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: QA2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
## Listing 2
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: QA1
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: QA2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 125
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

They look almost the same, and yet the similarity is deceiving. The first listing is what you get immediately after a call to CHANGE MASTER TO. If you run START SLAVE at this point, replication will start flowing.
The second listing is what you get immediately after a call to RESET SLAVE. The crucial difference is that RESET SLAVE removes the two .info files containing replication credentials and positions. A call to START SLAVE in this scenario will only get you an error, as the slave does not know where and how to connect.
So, in this case, SQL visibility does only tell you that the server is not receiving replication date, and that it was at least once configured as a slave. The telltale detail is the user name ("test") that should give you a hint of something fishy going on. Unless you have called your user "test", in which case you were asking for trouble. I would say that this situation is a bug. RESET SLAVE should remove every memory of the slave configuration, and instead it keeps only the host name. Although it is not clear in this particular example, it also forgets the master connection port.

Now, if your purpose was to set replication with different coordinates, the good news is that in both cases a well formed call ^(*) to CHANGE MASTER TO will do what you expect, i.e. it will establish the credentials to the master, so that a further invocation of START SLAVE will let replication data flow.

^(*) By "well formed" I mean a call that includes host, port, username, password, binary log file and position, and eventually all the information that you need to get the slave at work.

Webinar: Percona and Continuent on backup and replication with huge data

2011-02-08T23:36:00.000+01:00

On Thursday, February 10, at 10am PST, there is a free webinar about Managing Big Data with Percona Server, XtraBackup and Tungsten. Quoting from the announcement:

Big data is a big problem for growing SaaS businesses and large web applications. In this webinar, we'll teach you how to set up Percona Server, XtraBackup, and Tungsten to manage Terabyte+ databases and scale to millions of transactions a day. We'll discuss the latest features for high transaction performance like InnoDB buffer pool dump/restore and HandlerSocket, our favorite tricks for backup, restore, and provisioning of large data sets, and how to replicate scalably and safely using Tungsten Replicator with parallel apply.

The presenters are representatives of both Percona and Continuent,
Vadim Tkachenko, Percona Co-Founder & CTO,
Robert Hodges, Continuent CEO
Edward Archibald, Continuent CTO

The event will showcase Xtrabackup and Tungsten features in an interesting combined view.
The event is free, but registration is required.

Evolution of MySQL metadata

2011-02-07T19:00:00.003+01:00

I was looking at the latest MySQL versions, and I happened to notice that there has been a great increment in the number of metadata tables, both in the information_schema and performance_schema databases. So I made a simple count of both schemas in the various versions, and draw a graph. The advance looks straightforward.

version	Information_schema	performance_schema
5.0.92	17	0
5.1.54	28	0
5.1.54 with innodb plugin	35	0
5.5.8	37	17
5.6.2	48	23

The difference between 5.0 and 5.6 is staggering. We came from 17 to 71 metadata total tables. A stacked bar chart helps visualize the changes.

I noticed, BTW, that MySQL 5.0.92, which is not in active support, was released without the binaries for Mac OSX. If this kind of edition is limited to the versions in extended support, that's fine with me. I hope the habit does not contaminate the regular builds.

Open Database Camp - Accommodation and Sponsoring

2011-02-03T12:13:00.000+01:00

The Open Database Camp 2011 is shaping up nicely.
The logistics is being defined and local and international volunteers are showing up for help. (Thanks, folks!)
If you want to start booking, there is a list of hotels in the Accommodation page.
And don't forget to sign up in the Attendees list.
Local travel information will be released as soon as we finish cranking up the plan.
Open Database camp is free, but we still have expenses to get the job done.
We need both official sponsors and personal donations. No minimum amount required. You can donate painlessly online through the nonprofit organization Technocation. (Thanks!)
Please see the Sponsors page for more info.

A first look at delayed replication in MySQL 5.6

2011-01-30T20:09:00.001+01:00

If you like fresh features, you should not miss this one. MySQL 5.6.2 includes, among other improvements, the implementation of Time delayed replication, a feature that lets you tell the slave not to apply changes from the master immediately, but to wait N seconds.

The feature is documented in WL#344. (There was a manual online as well together with the binaries for MySQL 5.6.0, but they were removed after a few days for a good reason. I am confident that both the manual and some binaries will eventually show up soon).
Since as of today there are no binaries for MySQL 5.6.x, you need to get the code and compile it yourself. Just get the code from https://code.launchpad.net/mysql-server and compile it using the instructions in building MySQL 5.5 with cmake.
To get a taste of this new feature, the quickest way is to set up replication using the binaries that you have built and MySQL Sandbox.

make_replication_sandbox  mysql-5.6.2-m5-osx10.6-.tar.gz 
# the file name may change, depending on the operating system you are using

Soon you will have one master and two slaves in $HOME/sandboxes/rsandbox_5_6_2.
What you have to do is connect to one of the slaves and enter these commands:


STOP SLAVE;
change master to master_delay=60;
START SLAVE;

Let's say that you did this to slave #2.
Now whatever you do in the master will be replicated immediately in slave #1, but it will executed with 60 seconds delay in slave #2.
To be clear, the IO_THREADs of both slaves keep getting data from the master as fast as they can, same as they did until version 5.5, but slave #2 will hold the SQL_THREAD for the defined amount of seconds.
This new state is visible in the output of the SHOW SLAVE STATUS command, which lists this information after you do something in the master like creating a table or inserting data:


               SQL_Delay: 60
     SQL_Remaining_Delay: 43
 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

The main purpose of delayed replication is to protect the server against human mistakes. If I accidentally drop a table, the statement is instantly replicated to all the slaves, but it is not executed to the delayed slaves.


$ ./m -e 'drop table test.t1 '
$ ./use_all 'show tables from test'
# master  
# server: 1: 
# server: 2: 
Tables_in_test
t1

The table is gone in the master, and it is gone in the regular slave, but it is still there in the delayed slave. And if I detect the problem before the delayed statement gets executed (a delay time longer than 60 seconds would be advisable in this case, 3600=1 hour, seems healthier), then I may be able to recover the data.

I notice en passant that there is much more than delayed replication going on in MySQL 5.6. For example, the information_schema tables related to InnoDB have increased from 7 to 18:


 show tables from information_schema like 'innodb%';
+----------------------------------------+
| Tables_in_information_schema (innodb%) |
+----------------------------------------+
| INNODB_CMPMEM                          |
| INNODB_TRX                             |
| INNODB_BUFFER_PAGE                     | *
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  | *
| INNODB_CMP                             |
| INNODB_SYS_COLUMNS                     | *
| INNODB_CMPMEM_RESET                    |
| INNODB_SYS_FOREIGN_COLS                | *
| INNODB_BUFFER_PAGE_LRU                 | * 
| INNODB_BUFFER_POOL_STATS               | *
| INNODB_CMP_RESET                       |
| INNODB_SYS_FOREIGN                     | *
| INNODB_METRICS                         | *
| INNODB_SYS_INDEXES                     | *
| INNODB_LOCKS                           |
| INNODB_SYS_FIELDS                      | *
| INNODB_SYS_TABLES                      | *
+----------------------------------------+
18 rows in set (0.00 sec)
# (*) new tables marked with a star

What they do and how to play with them will be matter for some more investigation.

The MySQL Council is up and running. We want to hear from you!

2011-01-28T21:01:00.000+01:00

The Independent Oracle User Group (IOUG) has formed the MySQL Council, with the purpose of addressing the interests and needs of MySQL users.

The current Council members are:

Sarah Novotny, Blue Gecko, Council Chair
Sheeri Cabral, PalominoDB
Bradley Kuszmaul, Tokutek
Giuseppe Maxia, Continuent
Rob Wultsch, GoDaddy.com
Matt Yonkovit, Percona

The IOUG is a well established and respected institution, with more than 20,000 database professionals registered as its members. In the long history of Oracle, the IOUG has always played a role in the aftermath of many acquisitions, extending a friendly hand to the newcomers in the family. I have spent much time with IOUG people, and I was impressed by their enthusiasm and professionalism. My impression of the IOUG is that it is a friendly group of people with the common goal of improving the experience of being an Oracle user.
The IOUG is not looking for assimilation. While Oracle business strategy calls for full integration, the IOUG recognizes that users come in different sizes and shapes, and they cannot be forced into the institution. Instead, the IOUG want to understand the newcomers and offer their services to help out towards the common goals. The IOUG people also know that many old Oracle users are also MySQL users. By facilitating the integration of the MySQL community they are serving the needs of a wider population than the traditional set of MySQL users.

The MySQL community has never been organized as a whole. There id nothing comparable to the massive presence of the Oracle user group. MySQL users are mostly isolated. When they convene into social entities, they identify themselves either by town boundaries or by being member of some group that has some interest in MySQL (Linux distributions, PHP developers, CMS framework users). There is no unified view of what users represent or want, and a difficult dialog between the user base and the company that produces the software.
With Oracle, this lack of unity is yet another obstacle in the path to a good understanding. Oracle is used to talk to a large entity representing its users, and it has not the patience or the skills to deal with such a distributed presence as the MySQL community. For this reason I believe that the MySQL Council is a good idea. It provides a tool for the will of the MySQL community to be conveyed to the company, using the IOUG, one of the channels that are familiar to Oracle, and that are more likely to reach the decision makers.

The main goal of the council should be to mend fences. There is much to be done. The culture of traditional Oracle users and that of MySQL users are different and sometimes hard to reconcile. Oracle strategies that were tuned towards pleasing their traditional customers may not suit the needs of the newcomers. All this needs to be addressed, and the MySQL Council could be the first step to the solution.

For this reason, the council members want to hear from the community. What are the main pain points and issues that you want addressed? What is Oracle doing right with MySQL? Where could it improve?

If you know one or more of us, please contact us by email. Or write a blog post about the issues to address. Or comment in our blogs. We want to hear from you. We feel that we must represent the larger MySQL community and bridge the gaps between the company and the user base.

The roles of the MySQL Council is not only to be a diplomatic channel of communication between users and company. It will also help with the diffusion of MySQL culture among Oracle users, with articles, conferences, meetings, and whatever the imagination provides to achieve the goal of spreading the word.

Also for this goal we welcome your input. Make your voice heard. It's time to boost the technical writing in Planet MySQL and in the rest of the net. Let MySQL be known!

Joining the Oracle ACE program

2011-01-25T16:20:00.000+01:00

A few days ago I received an invitation to join the Oracle ACE program, which is a group of strong community enthusiasts and advocate of Oracle products.
Since I have been a vocal member of the MySQL community for years, I welcome this acknowledgment as well as I appreciated being nominated MySQL Community Contributor of the Year in 2006. Unlike that award, which came from inside the MySQL company, the Oracle ACE nomination came from my peers in the community, to whom I address my thanks and appreciation.
The nomination comes from the community, but the title is granted by the company, as recognition for good work. Therefore, thanks also to my former colleagues at Oracle who have approved my current status.

For the ones who are curious, being an Oracle ACE does not mean becoming an employee, or having obligations towards Oracle. There is not even an obligation to be kind towards the company, meaning that Oracle ACEs can be as outspoken as they feel they should. So, there is no restraint on what I can tell in public, and no expectations of unconditional support for Oracle policies.
What you can expect is for me to be my usual self, the guy who is enthusiast about cool technology, no matter where it comes from, and critical or appreciative depending on merit.
Now, back to hacking!

Pitfalls of monitoring MySQL table activity with stored routines

2011-01-22T21:58:00.003+01:00

A friend of mine needed a quick method to monitor a table that was frequently updated. There were several procedures writing to the table several times per second. He needed a quick way of determining how many updates per second the table was getting.
The table has only one row, which includes a counter that is increased at each operation. Therefore, the simple plan was:

Let's get the counter from the table;
Allow N seconds to pass;
Get the counter again;
The difference between the second counter and the first counter, divided by the number of seconds gives the updates per second.

The plan makes sense, and if you run the above commands manually, you get what you want.

However, my friend wanted the update ratio to be a single operation, say like:


SELECT update_ratio();

He went to make a simple function, following the four steps described above.


delimiter //
drop function if exists update_ratio //
create function update_ratio()
RETURNS INT 
begin
     declare sleep_wait int default 5;
     declare start int default 1;
     declare finish int default 1;

     set start = (select counter from mytable);
     do sleep(sleep_wait);
     set finish = (select counter from mytable);
     return (finish-start)/sleep_wait;
end $$
delimiter ;

It seems OK. The function runs without errors, but it always returns zero.
Mystery! Running the statements manually gives always a sensible result. Using triggers to monitor the table shows that indeed it is updated many times per second, but the function returns always zero.
More puzzling is the fact that if we convert the function to a procedure, it gives the wanted result.

The solution to the mystery is found in the MySQL online manual

A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log.

In other words, it means that all tables referenced in a stored functions are locked when the function starts. Therefore the external procedures that were updating the table will have to wait until the function's end before updating. When the function reads from the table, it gets always the same record counter, because no updates were happening in the meantime. That's why the second read is the same as the first one, and the result is zero.

What should you do then?
One option is to convert the function into a procedure:


delimiter //
drop procedure if exists show_update_ratio //
create procedure show_update_ratio()
begin
     declare sleep_wait int default 5;
     declare start int default 1;
     declare finish int default 1;
    
     select counter into start from mytable;
     do sleep(sleep_wait);
     select counter into finish from mytable;
     SET @UPS := (finish-start)/sleep_wait;
end //

That gets the job done. If you want to get the result into a variable, you can do it with two statements.


call show_update_ratio();
select @UPS;

If you don't change the last SET into a SELECT and just display the value.

Another option is using several SQL commands from your application. Also in this case, make sure that you are NOT wrapping this code inside a transaction, or you will get the same result in both queries

# WRONG!
     set autocommit=0;
     BEGIN;
     select counter into @start from mytable;
     set @start = start;
     do sleep(5);
     select counter into @finish from mytable;
     select (@finish - @start) / 5 as UPS;

If you go for this solution (or even the stored procedure), make sure that you are either using autocommit, or commit after each query if you must use a transaction.

Continuent is hiring - Support and QA engineers wanted

2011-01-10T13:03:00.000+01:00

Continuent is hiring. The business is growing, the opportunities are piling up nicely, and we need to beef up the team with the addition of some new professionals.
The mist urgent posts to fill are a
QA Engineer and a Support Engineer, both experts of their specific trades and of database clustering.
We are looking at the matter without borders. Although it would be preferable to find candidates in the US, and in the West Coast in particular, we are really looking for the best people in the market, regardless of their location.
Both jobs are challenging, they are both MySQL related, and both require experience with QA and support respectively, in addition to development background.
If you are a super star in either QA or support, contact resumes AT continuent DOT com. Also, feel free to contact me, should you need further information.

Announcing the Open Database Camp - Sardinia, May 2011

2011-01-06T00:35:00.000+01:00

I have been traveling to many conferences in the last 10 years, and many times I have been asked to organize an event in my native land, Sardinia. After delaying the inevitable for long time, here I can announce it. The Open Database Camp 2011 will take place in Sardinia, hosted by the Sardinia Technology Park, a local scientific and business institution with international links.
Mark your calendars: the Open Database Camp will be held in Sardinia on May 6-7-8, 2011.
I have already confirmed the venue, and I will have full cooperation from Sardegna Ricerche about the conference logistics. I will meet the organizers on January 27th to get in touch with nearby hotels and restaurants and negotiate rates.
The place is a beautiful and modern compound, built in the middle of a forest. About 40 Km from Cagliari and its airport. There is a public bus service to reach the venue, and there will be an integrative bus during the conference.
The place is a few kilometers from the sea resort of Pula, near the archeological beauty of Nora.

If you want to start booking your flights, look for connections to Cagliari Elmas.

There are cheap direct flights from several European airports with EasyJet, Ryanair, TUIFly, Air Berlin, and probably a few more.
For example, you can fly to Cagliari from Paris, Frankfurt, Berlin, Cologne, Munich, Stuttgart, London, Edinburgh, Brussels, Madrid, Barcelona, Seville, Valencia, Venice, Rome, Milan, Turin, Basel, Geneva, Krakow, and probably more by the time you come.
If you book now, you should be able to get a good price.

The weather in Sardinia is mild. May is almost summertime. If you live in cold places like the North of the USA, Canada, Scandinavia, May in Sardinia is definitely warmer.
More logistics information will come.

Why Open Database Camp, and not Open SQL Camp like before?
The Open SQL Camp tradition has evolved since its inception in 2008. It has now become a gathering of database professionals and enthusiasts, not necessarily identifiable with the SQL constraint.
So, the conference welcomes everyone who deals with open databases, regardless of the languages used to interface them.

Stay tuned for more info. In the meantime, you can discuss this matter in the opensqlcamp Google Group.

Looking for a hack - Passing comment-like info through the binary log

2010-12-20T23:20:00.000+01:00

I am facing an interesting problem. I need to mark somehow a statement in such a way that the comment is preserved through the binary log.
I don't have control on how the statement is generated or using which client software. For the sake of example, let's say that I need to mark a CREATE PROCEDURE statement in such a way that, if I extract the query from the binary log and apply it to another server, the information is still available.

Background

Normally, 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 clever trick by Roland Bouman that allowed users to bypass this limitation. You could use a qualified comment such as /*!999999 This is my test */, but unfortunately it only works in MySQL 5.0.x, while MySQL 5.1 strips everything down, even if the comment is a legitimate keyword.


create procedure p9 (j int) insert /*!50000 INTO */ t1 values (j) ;
Query OK, 0 rows affected (0.00 sec)

 show create procedure p9\G
*************************** 1. row ***************************
           Procedure: p9
            sql_mode: 
    Create Procedure: CREATE DEFINER=`msandbox`@`%` PROCEDURE `p9`(j int)
insert  INTO  t1 values (j)
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Other tricks

Something else that I have tried: I can inject a query before or after the one that I need to monitor.

create table if not exists comments (t varchar(100)) engine=blackhole;
 
update comments set t='the next statement is what I need';
create procedure p1(i int) insert into t1 values (j);
update comments set t='the previous statement is what I need';

This approach does possibly introduce some overhead.

Or I can add a comment clause in the statement.

create procedure p1(i int) comment="this is what I need" insert into t1 values (j);

This approach requires parsing the SQL, and dealing with artistic indentation and usage of other options in the query. And if I need to deal with commands that don't support the "comment" option, I am back to square one.

Advice wanted

So far, the only method that works almost always is the blackhole trick ⁽¹⁾
I would like to know if there is any method of introducing a piece of information related to a given statement, in such a way that the comment survives after one of the following:

The binary log is converted to queries and passed to a MySQL client that applies the stream of queries to another server.
The binary log is associated with another master, and then passed to a slave through regular replication.

In both cases, I need to find the original information in the last server's binary log.

As a related matter, I know that MySQL, in regular replication, passes some information across binary logs, and that information is the server-id. If I set an intermediate server as relay slave, the server-id of the original master is associated with the query recorder in the binary log of every slave. I don't know if I can use this information for my purposes, but I would like to know how does the replication process maintain the server ID across servers.

Maybe it's too late for me and I can't see an obvious solution. I will appreciate any suggestion. Thanks in advance

⁽¹⁾ If the blackhole is disabled, the method fails, or introduce unacceptable overhead.

Some hidden goods in MySQL 5.5

2010-12-16T12:22:00.000+01:00

The announcement of MySQL 5.5 released as GA has outlined the improvements in this version, which indeed has enough good new features to excite most any user.
There are two additions, though, that were lost in the noise of the bigger features, and I would like to spend a few words for each of them.

The first addition is something that users of stored routines have been waiting for since MySQL 5.0. No, it is not SIGNAL and its close associate RESIGNAL, which have been publicized enough. I am talking about the stored routine parameters, for which now there is a dedicated table in the information_schema.
Let's see an example, with a simple procedure that uses three parameters.


drop procedure if exists add_to_date ;
create procedure add_to_date(in d date, in i int, out nd date)
deterministic
    set nd = d + interval i day;

This works as expected in both 5.1 and 5.5. (Never mind that it's redundant. I know it. It's only for the sake of keeping the example short).


 call add_to_date('2010-12-15',10,@new_date);
Query OK, 0 rows affected (0.00 sec)

 select @new_date;
+------------+
| @new_date  |
+------------+
| 2010-12-25 |
+------------+
1 row in set (0.00 sec)

The difference starts to show when you want to deal with this procedure programmatically. If you need to find out which parameters are expected by this procedure, your only option in MySQL 5.1 is parsing the result of SHOW CREATE PROCEDURE add_to_date. Not terribly difficult in any scripting language, but a hassle in SQL.
In MySQL 5.5, instead, you can easily get the routine parameters with a simple query:


 select parameter_name, parameter_mode,data_type from information_schema. parameters where specific_schema='test' and specific_name= 'add_to_date' order by ordinal_position;
+----------------+----------------+-----------+
| parameter_name | parameter_mode | data_type |
+----------------+----------------+-----------+
| d              | IN             | date      |
| i              | IN             | int       |
| nd             | OUT            | date      |
+----------------+----------------+-----------+
3 rows in set (0.00 sec)

Speaking of the information_Schema, there are more goodies that were not emphasized enough. The Innodb engine that you find in the server is the evolution of the InnoDB plugin that ships with MySQL 5.1. Only that it is now built-in. What many people forget to mention is that the plugin (and thus the current InnoDB engine in 5.5) comes provided with its own InnoDB-specific instrumentation tables in the information_schema.


show tables like 'innodb%';
+----------------------------------------+
| Tables_in_information_schema (innodb%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+
7 rows in set (0.00 sec)

This is the same set of tables that you may have seen if you have worked with the InnoDB plugin in 5.1. In short, you can get a lot of the info that you used to look at in the output of SHOW ENGINE INNODB STATUS. For more information, you should look at what the InnoDB plugin manual says on this topic.
I don't know if the tables can replace the SHOW ENGINE INNODB STATUS. Perhaps someone can comment on this issue and provide more information?

Speaking at the O'Reilly MySQL Conference - April 2011

2010-12-09T11:30:00.000+01:00

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.

Excluding databases from mysqldump

2010-12-06T06:30:00.002+01:00

A question that came up during the MySQL track at the UKOUG conference in Birmingham was "Can I exclude only a few databases from mysqldump? Let's say that I have 50 databases, and I want to dump all of them, except a few."
As many know, mysqldump has an option to ignore specific tables. SO if you have 1,000 tables in a databases, you can tell mysqldump to dump all the tables except a few ones.
There is no corresponding option to exclude one or more databases.
However, if you know your command line tools, the solution is easy:
First, we get the list of all databases:

mysql -B -N -e 'show databases'
information_schema
employees
five
four
mysql
one
performance_schema
six
test
three
two

-B forces batch mode (no dashes box around the data), while -N gets the result without the headers.
Now, let's say that we want to exclude databases four, five, and six. And since we want to avoid unpleasant side effects, also information_schema and performance_schema.
Thus, we pipe the previous data through a filter. I use Perl, but sed or grep could get the job done.

mysql -B -N -e 'show databases' | \
  perl -ne 'print unless /\b(?:four|five|six|_schema)\b/' 
employees
mysql
one
test
three
two

Now that we have the list of databases that we need, we can tell mysqldump to backup the databases from such list. All we need is converting the vertical list into a horizontal one using xargs

mysql -B -N -e 'show databases' | \
  perl -ne 'print unless /\b(?:four|five|six|_schema)\b/' \
  xargs echo mysqldump -B 
mysqldump -B employees mysql one test three two

That's it. The last line is the resulting command. Once you are sure that it is what you want, remove the "echo" after xargs, and the command will be executed.

Update: Thanks to Shantanu, who pointed that the regexp does not filter properly. So I added the boundary checks (\b) to make my words match the result.

My picks for PGDay-EU 2010

2010-12-03T19:26:00.000+01:00

On Sunday I will be in Stuttgart with the double purpose of attending the annual European PostrgreSQL conference and the technical meeting of my company that will be held after the normal proceedings of PGDay-EU.
For the first time in several years I am attending a conference where I am not a speaker. In my previous job I did not have much opportunity to attend PostgreSQL meetings, and I welcome this opportunity. The schedule is quite interesting, and I have made my personal picks:

Monday:
- 09:45 - 10:45 Keynote: Back To The Future of Open Source.
  Simon Phipps has always an interesting way of making a topic interesting. Looking forward to hearing what he has to say.
- 11:10 - 12:00 Play chess against PostgreSQL (and get beaten).
  For a chess enthusiast and database professional, this talk is going to be doubly interesting!
- 12:10 - 13:00 Managing PostgreSQL Replication.
  In my new job, this topic is going to be paramount.
- 14:00 - 14:50 Liberating Your Data From MySQL: Cross-Database Replication to the Rescue!.
  This will be a presentation of Tungsten cross DBMS replication, therefore interesting for many personal reasons!
- 15:20 - 16:10 Concurrency & PostgreSQL.
  Undoubtedly a topic that will become quite useful when dealing with PG replication issues that I will be facing in my job.
Tuesday:
- 14:10 - 15:00 PostgreSQL extension's development.
  I know a lot about extensions in MySQl. It's time to start learning similar about PG!
- 15:30 - 16:20 Closing keynote: PostgreSQL's Time to Shine: The most disruptive force in open source since Linux
  This sounds like a juicy strategic talk.

Who's afraid of MySQL forks?

2010-12-03T10:32:00.001+01:00

There is much talk about MySQL forks and how they are going to replace MySQL, or take over MySQL user base, or become more powerful/profitable/popular/you-name-it than MySQL itself.
Let's clear some air on this topic. There is more about forks than meets the eye, especially if you think about a few obvious facts.
What's a fork? According to Wikipedia

a project fork happens when developers take a legal copy of source code from one software package and start independent development on it, creating a distinct piece of software.

By this definition, when someone who doesn't work at the MySQL project distributes a package that is based on MySQL code but differs from the original, it's a fork.
Why am I approaching the issue from this angle? Because, apart from Windows users, who mostly download MySQL from the official site, the majority of users get MySQL through a Linux distribution or some other project. And most of the time such packages are different from the ones built by the MySQL team. There is nothing wrong with that. The differences are sometimes minimal packaging changes done to adapt MySQL to the specific distribution, and sometimes they are a cherry-picking application of patches to an old version that needs to be maintained so that the package is unlike any other MySQL version that you may find in the wild. Even if the version is the same, depending on the distribution and the age of the server, the code beneath could be wildly different from the official versions.
Thus, it turns out that many users, possibly the majority, are using a MySQL fork, albeit a very minor one.
But when people talk about forks, they often refer to three main projects:

The Percona distribution. This is a collection of a few distinct patches in the server, coupled with a fork of the InnoDB plugin, named XtraDB, and an independent tool for backup (XtraBackup). This fork has a solid business background. Every patch has been developed to meet user requests, and the engineers at Percona maintain them appropriately.
Then we have the MariaDB fork, which is a series of changes to the MySQL core, motivated by the desire of the developers to build a rich set of feature enhancements while being backward compatible to the main distribution. The business model is thus a fast track of new features and bug fixes to customers.
And then there is Drizzle, which has even less business traction than MariaDB, but a very well defined goal of creating a lightweight database by re-engineering a bare bones stripped down version of MySQL that is now very distant from its origins.

What I said in the above descriptions is just the synopsis of what these three forks are. In recent mythology, it is fabled that, if MySQL ceases to exist (because it goes bankrupt, or Oracle kills it, or a major accident happens to the project, whatever) users can replace MySQL with one fork, and live happily ever after.
Not so fast. There is something that few people take into account when listening to this too often repeated tale.
What most observers miss is that the forks' original code (with the exception of Drizzle) is very marginal. The bulk of the distribution is still the code produced by the MySQL team, which is merged at every minor release, and integrated with the patches produced by Percona and MariaDB. So, while technically they are forks of MySQL, they can't live independently from the official MySQL distribution. Both Percona and MariaDB don't have the manpower to maintain the server by handling the huge amount of bugs that the MySQL team is fixing every month.
There is also a matter of skill set. Percona has talented InnoDB experts, while MariaDB has mostly core server experts (and some are among the top ones, I may add). They could complement each other, although it seems that cooperation between the two projects is not as good as it used to be. (Could be my personal impression.)
The bottom line, though, is if both projects are able to survive should the main project become unavailable. I am not suggesting that Oracle wants to make MySQL scarce. On the contrary, all the information at my disposal suggest that Oracle will keep MySQL publicly available for long time.
This state of affair seems to indicate that Drizzle is, instead, a true fork that does not depend on MySQL health. To some extent, this is true. However, the main storage engine in Drizzle is InnoDB. Therefore, at least today, Drizzle is as dependent on Oracle as Percona and MariaDB.
What would happen tomorrow, if the disaster depicted by doomsday advocates comes true and MySQL actually disappears? I don't honestly know, but I would love to have a public commitment from the major players, about what they are prepared to do in terms of maintaining that huge chunk of code that today they take from Oracle releases on a monthly basis.
This is all matter of thought for MySQL users.

About adoption of the forks today, I have seen five types of arguments in favor of a MySQL fork:

I need the feature provided by Percona or MariaDB, or I need a quick bug fix that I can't get from the slow roadmap at Oracle. I trust that this handful of people are able to maintain that little code that differs from MySQL and matters to me. So I don't care if they don't have 100 developers on the task.
Given Oracle's track record in other Open Source projects, I don't trust them to deliver MySQL according to FOSS principles, so let's go for true Open Source alternatives.
Most MySQL developers have now left Oracle, and so the forks have more chances of being higher quality.
Cool! MariaDB/Percona has a bunch of features more than MySQL. It must be better. Let's use it.
I like new technology. Let's plunge into them!

Argument #1 is a solid business backed reason for adopting some software. The risk is often well calculated, especially if the evaluation can be backed by performance and functional tests.
Argument #2 is frivolous, as it mixes subjective feelings into business matters. And so is argument #4. Yet, these two types of advocacy are quite popular and spread much faster than the more reasonable approach seen at #1.
Argument #3 is debatable. MySQL developers at Oracle outnumber all forks easily. The idea that the departure of a few core developers can alter the system in such a way that the whole project crumble has been already negated by facts: MySQL 5.5 is an excellent release, with enthusiastic appreciation from power users. While I agree that top MySQL talents work at the forks, I consider the MySQL team to be still in excellent shape.
Argument #5 is reasonable, if it is followed by cool judgment and backed by facts. I am one who is always ready to try new solutions, and love experimenting with cool technology. But adoption is different from proof of concept. I am happy to see that Drizzle can replace MySQL in some applications, but would I trust it in its present beta stage? Certainly not. So, I am happy to test, but I trust my valuable data to more stable solutions.

What's for you, the final user? My personal advice is: don't adopt blindly because of some enthusiastic advertising. But test the product thoroughly, and if it fits your needs, by all means, go for it. But if you don't have a specific reason, I recommend staying with the official branch, because, despite the change in affiliation, there is still a well experienced team behind it.

Dispelling some unintentional MySQL FUD

2010-11-28T15:24:00.000+01:00

There are three types of FUD: the first and more genuine is (#1) the intentional spreading of falsehood, mostly to gain some marketing advantage over a competing product. While I despise this practice, I understand it.
Then there is (#2) FUD spread by ignorance, when the originators are so blindly enraged by their hatred for a product that they don't care about getting the facts straight.
And finally, there is a third kind, not less dangerous, which is (#3) the spreading of FUD with good intentions, when the authors believe that they have the facts straight and they want to help.

I have recently come across two examples of unintentional FUD. For different reasons, my comments to these public cases did not get through, and then I have to say something about that here in my blog.

MySQL is not ACID complaint

This surprising piece of news came in the blog of a company that calls itself the remote DBA experts.
The claim is this: if I insert a record in a table and then issue a ROLLBACK command, the record is not rolled back.
Anyone who has a minimal knowledge of MySQL knows about InnoDB tables (luckily for the poster, InnoDB is default in MySQL 5.5.6, which he was testing) and autocommit.
Reading through the example, one sees that the poster did not know about this piece of information. In MySQL, autocommit is ON by default. So if you want to rollback a record, you need to deactivate it. This is not optimal, and it can be debated, but if you read the docs, you don't claim something that is simply the result of your lack of knowledge. MySQL has shortcomings, but being unable to rollback a record is not one of them. Hence, this is FUD type #2.
Why I am writing all this here and not as a comment in that blog? Because I did post a comment, on November 23rd, but as of today, it has not been approved yet. The same is true for comments posted by other more knowledgeable people.

MySQL licenses. When it's free and when you need to pay for one.

This article is well intentioned. MySQL Licenses: The Do's and Don'ts of Open Source, or What's All the Fuss About? is a well thought piece, with practical examples, to help users decide what to do with MySQL licensing, i.e. when they need to pay and when they don't. Unfortunately, the article contains some unintentional confusion, and therefore leaves the readers with more wrong ideas than they had before.
I left a long comment on that blog, but for some unfathomable reason it was reduced to a tiny piece, and thus the need for explaining the matter here again.
The poster says this:

I make commercial software, which needs to have MySQL installed. My customers can use my commercial software, for which they do need to buy a license, in combination with the MySQL database engine, for which they don't need to pay. Because the MySQL engine is not embedded in my commercial software and I don't redistribute MySQL together with my software, I don't need a commercial license for MySQL and neither do my customers.

I am afraid that this wishful information is not correct. The GPL FAQ states it clearly:

If a library is released under the GPL (not the LGPL), does that mean that any program which uses it has to be under the GPL or a GPL-compatible license?
Yes, because the program as it is actually run includes the library.

Another quote:

However... as long as I have no desire to sell the embedded MySQL source code commercially, I can let the GPL license apply.

Also this is not true. The GPL does not regulate commercial transactions. It only deals with distribution of software. If I want to distribute a public domain but GPL-incompatible software linked to a GPL application or library, I am violating the GPL, even if I don't charge anything.

Another source of disinformation is "If you decide to pay for a MySQL license, you don't actually pay for the software."
This is also incorrect. Oracle sells two kind of things with MySQL. One thing is a subscription to services (MySQL Enterprise). If you buy this, you are not getting a license (unless you ask for it explicitly) but an agreement about services for a given periods.
The other thing that Oracle sells is licenses. They can do it because they own the source code, and they can decide to release it either as GPL (which is what you download from the MySQL site) or with a commercial license. If you ask for a license, you will most definitely get one. You can also get a license together with a subscription, if you are so inclined, but that doesn't mean that you aren't buying a license.
The important thing to understand to put the matter in perspective, is that the above information about licensing was still true before 2008, when MySQL was owned by MySQL AB, and it is still true today. Oracle, despite all the preemptive accusations of being ill intentioned, has not changed the rules of the game.

A world of ebooks

2010-11-28T13:15:00.000+01:00

I am a bibliophile, or, to say it in plain English, a book lover. I have been collecting books since I was in first grade. I read books at high speed, which is both a blessing and a curse. A blessing, because I can squeeze useful information out of a book very quickly, and that's useful for my job, and for some of my hobbies. A curse, because when I travel one book is usually not enough to keep me busy for the whole travel, and I need to carry or buy more, with negative effects on the weight of my luggage and my on my back. Ten years ago I had a brief but intense experience with electronic books in a Palm hand held device. It didn't last long, though. The quality of ebooks and readers in that period was less than optimal, and I have left the matter rest for a while.
In the meantime, I kept collecting electronic books, mostly PDF editions of technical books that I keep in my laptop for quick reference. Reading them from cover to cover, though, is not a pleasant experience in a laptop. Ditto for reading fiction or essays. The laptop screen is not comfortable for such exercise.
Then, last year, I bough an ebook reader.

That changed the whole business. Reading ebooks became very similar to reading paper books. The size of the screen and the ability of increasing font size makes your reading a pleasure. As for my back and luggage problems, that's solved hands down. The weight of the device is the same, no matter if I carry one ebook or one hundred.
Suddenly, the dozen of ebooks that I had kept idle in my laptop sprang to life, and I was able to read them like a paper book, easily, comfortably, and with pleasure.
I started buying more ebooks, both of fiction and of technical matters. The latter are especially welcome. Whenever I travel to conferences, I am tempted to buy some useful book, and then I regret when it burdens my backpack during the trip home, and fights for room on my overcrowded book shelves. No more of this. Now, when I visit a book booth at a conference, I simply take note of the interesting titles, and then I buy the ebooks at the publisher's site directly. If there is no ebook, I can easily convince me that the book is not really needed.
A few months ago there was some new development. My ebook reader's screen was faulty. It was showing a few unwanted lines at the bottom and the top of the screen, making it difficult to read menus. No big deal. I sent it to the manufacturer, which replaced the screen for free. The only trouble was that the replacement took three months! During that period, I experienced reading ebooks (to which I was by then addicted) with my Android phone, using a wonderful application named Aldiko. The user friendliness of this app more than compensated for the smaller screen size, and I was able to read technical and fiction books with little problem. But I was missing the big screen. So the delay of the back shipment was partially responsible for the lowering of my defenses, when I entered an Apple store and I couldn't leave without a new iPad.
I felt guilty for a while, but the guilt disappeared in a matter of hours, when I loaded all my ebooks in the iPad, and saw what a difference a bigger and colorful screen does. Compared to the six inches of my ebook reader, the iPad is huge, and the reading is even easier and more pleasurable. I was hooked.
Since then, my personal library of ebooks has grown rapidly. I have bought 90 (yes, ninety) books from O'Reilly, including many that I had already bought on paper, and now I am giving away to friends and libraries.
I need to spend a few words of praise for O'Reilly. In the jungle of book publishing, O'Reilly is the best and more user friendly publisher available. The quality of its books is excellent, the choice of catalog vast and modern, the service impeccable. There are other publishers that offer comparable quality (e.g. the Pragmatic bookshelf or Manning) but not the same rich catalog, or a similarly vast catalog (e.g. Packt Publishing) but not the same quality.
If I have to note any negative points about O'Reilly, is that there is no wish list in their shop. So, for now, I am restricting my wishes to my list on Amazon.

How MySQL Workbench breaks itself

2010-11-21T17:32:00.001+01:00

Once upon a time, there was a policy in MySQL not to add new features after the beta stage.
To my surprise, MySQL Workbench 5.2.30 introduces a new feature, the query formatter. I gave it a try. The results are not extremely encouraging. Granted, it's a plugin and not a feature in the core application, but nonetheless one would expect something more stable in a GA release, especially since the plugin features are displayed in the main menu, and unless you have read the announcement, you couldn't easily tell the core from the plugins.
This is what I have got in just a few minutes:

Bug #58356: beautify function fails on CREATE TABLE
Bug #58357: beutify function erases statement on CREATE INDEX
Bug #58358: query formatter fails on selected query
Bug #58359: query formatter indentation fails on partially selected query
Bug #58360 query formatter converts non-keywords to uppercase
Bug #58361 Query formatter mangles query when CASE operator is used

MySQL Workbench is a great product. I would like it to be more solid. New features, even as a plugin, should be more carefully released that this one.

Update. 4 days after my submission, the WB team has fixed all 6 of them. Kudos!

How to create a private cloud in your laptop

2010-11-19T15:19:00.001+01:00

Everybody is moving to cloud architectures, although not all agree on what cloud computing is. In my limited understanding, and for the purpose of my work, cloud computing is a quick and versatile availability of virtual machines.
Now, if my purpose was deploying these machines, a private cloud in one host (namely, my laptop) would not make sense. But to create a flexible testing environment, it works very well.
Users of virtual machines software such as VMWare or VirtualBox may ask what's new here. You can create many virtual machines and use them within the same host.
True, but creating a new virtual machine in one minute without duplication of resources is not so easy. This is what this article covers. More specifically, it covers how to clone virtual machines efficiently with VMWare Fusion on a Mac OSX laptop.

I have been a VMWare user for more than 10 years, mostly on Linux, where I used VMWare Workstation. One of the features that I liked on that application was the ability of cloning virtual machines, where the clones share the disk with the original VM. VMWare Fusion does not have a cloning feature, but there are ways of achieving the same result.
If you copy a virtual machine and then open it with Fusion, it will ask you if you have copied it, and after your answer, it will use the new virtual machine independently from the first one. The only problem is that you will have twice as much disk space occupied. So, in addition to the space that will eventually got eaten up in your limited laptop, the duplication process is slow (copying 10 GB does not happen instantly). The method shown here will instead allow you to clone a virtual machine in less than one minute.

Part I - How to clone a virtual machine

I have got this recipe from HOWTO: Manual Linked Cloning in Fusion, a year old tutorial that I have integrated with updated images.

Do it once - Create a virtual machine with everything you need.

This is a once only step. Install a virtual machine as usual. In my case, I used Ubuntu server 10.10. When I said with everything you need I mean making sure that everything you think it will be necessary should be installed in this base VM. If you don't, you may end up installing the same thing in every cloned virtual machine.
What I did was updating the software, then installing the build-essential package and the VMWare tools, and then enabling the "partner" repository to install the original Sun Java packages instead of the default OpenJDK. Next, I downloaded the source code for MySQL 5.5 and made sure that I had all the software necessary to compile and build the database.
Finally, I updated the CPAN with my favorite modules, stopped the virtual machine, and I was ready for the next step.
What I had in my hands was a working virtual machine. In order to make it clonable, I will go through some basic steps.

Step 1. Copy the VM disks

I create a new directory under the same path where the normal virtual machines are. I called it ub_srv_10_base, and I copied the .vmdk files into this new directory.

Step 2. Modify the base disk to refer to fixed paths

In each virtual machine, the disk that has the same name as the VM (without numerals) is a sort of index of the real files containing the data. This file needs to be edited for further usage. Originally, it looked like this:

# Disk DescriptorFile
version=1
encoding="UTF-8"
CID=f88ac433
parentCID=ffffffff
isNativeSnapshot="no"
createType="twoGbMaxExtentSparse"

# Extent description
RW 4192256 SPARSE "ubuntu_server_10-s001.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s002.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s003.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s004.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s005.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s006.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s007.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s008.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s009.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s010.vmdk"
RW 20480 SPARSE   "ubuntu_server_10-s011.vmdk"

# The Disk Data Base 
#DDB

ddb.toolsVersion = "8323"
ddb.virtualHWVersion = "7"
ddb.longContentID = "d14a2f23de35287969b8eaebf88ac433"
ddb.uuid = "60 00 C2 98 72 3b 6e 76-ff 05 b7 ff 8a 07 6e 92"
ddb.geometry.cylinders = "2610"
ddb.geometry.heads = "255"
ddb.geometry.sectors = "63"
ddb.adapterType = "lsilogic"

To make it usable, I added a relative path to the file names, and removed the UUID.

# Disk DescriptorFile
version=1
encoding="UTF-8"
CID=f88ac433
parentCID=ffffffff
isNativeSnapshot="no"
createType="twoGbMaxExtentSparse"

# Extent description
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s001.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s002.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s003.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s004.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s005.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s006.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s007.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s008.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s009.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s010.vmdk"
RW 20480 SPARSE   "../ub_srv_10_base/ubuntu_server_10-s011.vmdk"

# The Disk Data Base 
#DDB

ddb.toolsVersion = "8323"
ddb.virtualHWVersion = "7"
ddb.longContentID = "d14a2f23de35287969b8eaebf88ac433"
# ddb.uuid = "60 00 C2 98 72 3b 6e 76-ff 05 b7 ff 8a 07 6e 92"
ddb.geometry.cylinders = "2610"
ddb.geometry.heads = "255"
ddb.geometry.sectors = "63"
ddb.adapterType = "lsilogic"

Now the file is ready to be used by other virtual machines. Notice that the files in this directory do not make a virtual machine. Only the storage part is here.

Step 3. Make the disks read-only

We need to make sure that the base disks are not modified. So, we remove the "w" attribute from all the files in the base directory.


$ chmod a-w ub_srv_base/*s0*

Step 4. Remove the original virtual machine

Make a copy if you want. And actually having a backup of the base disk directory is a very good idea. Compress it and save it to a good location. But remove the virtual machine from Fusion. This will avoid confusion later on.

Do it many times - Create a clone

This process looks frightfully long, especially if you count the images below, but in reality it's very quick and painless. Once you get familiar with it, you will be cloning virtual machines in no time at all.

Back in VMWare Fusion, select "create a new Virtual Machine".
Make sure that you don't have the Ubuntu CD in your read DVD player. Click on "continue without disk".

Ignore the top options, and select "create a custom virtual machine".

Select the same operating system that was used for your base VM.

When the summary shows up, choose "Customize settings".

Save the VM with a sensible name. Since you will be creating many of the same kind, choose a pattern that you can recognize. In this case, I used the base name with an additional letter at the end to identify the server.

The disk is your main concern. The default settings want to create a 20 GB disk. Simply remove it.

When asked if you want to get rid of the disk completely, say yes, "move it to the trash".

Now, Very important, before you go to the next step. Use the command line, or a GUI, and copy the index .vmdk file from the base directory to the new virtual machine. I call it nd.vmdk (nd=new disk) but you can call it whatever you want. make sure that this file is not write protected.

Add another disk. Here's the first trick part. By default, Fusion wants to recreate the same disk that you have just removed. But notice that there is a drop down menu on the right side of the panel. Click on "choose existing disk".

Here you select the file that you have copied from the base directory. And now another tricky point. Make sure that you click on "Share this virtual disk ...", otherwise VMWare will make a copy of your original disk files.

You can now change other parameters in the virtual machine, such as the amount of RAM and how many processors you want to use for it. I recommend unchecking the "connected" box next to the CDROM, to avoid a warning when more than a cloned VM work at the same time. You can always enable the CD later if you need it.

Now Don't switch on your cloned virtual machine just yet. If you do, you get an incomprehensible message, where the VM complains about not being able to access the new disk file, while in reality it can't access the disks referred by that index file. This is where the precaution of write protecting the files comes handy. If you hadn't done that, the VM would access (and eventually modify) the virtual disk files, and possibly corrupt them. Instead, you need another step before having a functional clone.

You need to create a snapshot. Once you have done that, the VM will write to the snapshot files all the deltas between your read-only disks and your VM final status.

You can call the snapshot whatever you like.

Finally, you can run the virtual machine. If other virtual machines are running, it may warn you that some devices might not be available. Ignore this warning unless you know for sure that there is a unique resource that should not be shared (usually, there isn't).

Your virtual machine is ready to run. If you need to create three identical servers to simulate a cluster, and the original VM has 4GB of occupied storage, the operation won't cost you 16 GB, but just a few dozen MB:

$ du -sh ub_*/
 21M ub_srv_10_10a.vmwarevm/
 22M ub_srv_10_10b.vmwarevm/
 21M ub_srv_10_10c.vmwarevm/
3.9G ub_srv_10_base/

Part II - Use your virtual machines from the command line

Although VMWare Fusion is handy for creating virtual machines, and for using GUI-based operating systems, it is less than desirable when you have several virtual machines with only text interface, and you want to use them from the command line, the same way you would do in most real life administration or QA operations.

No need for further hacking, in this case. You can manage your virtual machines from the command line, using an utility called vmrun, which is located under /Library/Application Support/VMware Fusion/.
You can read the full manual in a lengthy PDF document that is available online ( Using vmrun to Control Virtual Machines), but here's the short story.
vmrun can start and stop a virtual machine. You just need to use it when the VMWare Fusion application is closed. For example:

$ vmrun start $HOME/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx nogui
2010-11-19 14:58:49 no printer configured or none available
2010-11-19 14:58:49 adaptor daemon booted
2010-11-19 14:58:49 connector "vmlocal" booted

As part of my preparation of the virtual machine, I created a simple script that runs ifconfig and filters out the virtual machine IP to a file in the user's home.
Using this information, I can then run the following commands:

$ vmrun -gu qa -gp SECRETPWD runProgramInGuest \
   $HOME/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx \
   /home/qa/bin/get_ip

$ vmrun -gu qa -gp SECRETPWD copyFileFromGuestToHost \
   $HOME/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx \
   /home/qa/myip ./server_a_ip

$ cat server_a_ip 
192.168.235.144

This is just a simple example of what you can do. Once you have the IP address (which the DHCP server could change), you can connect to your virtual machine via ssh and do what you need. When you have finished, you can switch off the VM, again without need of using the GUI:

$ vmrun stop ~/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx 2010-11-19 15:11:25 adaptor daemon shut down
2010-11-19 15:11:25 connector "vmlocal" shut down

Happy (cloud) hacking!

Filtering binary logs with MySQL Sandbox and replication

2010-11-12T14:55:00.000+01:00

A few days ago, a friend of mine asked me if I knew of a way of filtering a bunch of binary logs, to extract only statements related to a single table. The task was about filtering a few hundred binary log files.

It's a tricky problem. Even with my experience with regular expressions, I knew that using a script to extract statements related to a single table was going to be a nasty business.
However, I know of an entity that can filter by table name efficiently, and that's the MySQL replication system. So I suggested using replication to a sandbox with a replicate-wild-do-table statement to get the job done.
My friend was skeptical and did not want to go that way. I was busy writing an article for an Italian magazine and did not follow up immediately. But today, with the article safely in the editor's hands, I did a quick test, and guess what? It works!

Here is a step-by-step procedure to do it. I started with a server built with MySQL Sandbox, using MySQL 5.5.7. I used the employees test database to create a large enough binlog, and soon I had a database containing 160 MB of data and a binary log of about the same size.
Then I decided that I wanted to filter the binlog, to get only statements about the employees table. Thus, I issued this command:


$ make_sandbox 5.5.7 --sandbox_port=6000 \
  --sandbox_directory=trans_repl \
  -c log-slave-update \
  -c replicate-wild-do-table=employees.employees \
  -c log-bin=mysql-bin \
  -c server-id=101

The "-c" option transfers its argument to the sandbox configuration file.
At the end of this operation, I had one server with the same version of the server that I had filled with the employee database. The server is ready to filter replicated streams, accepting only commands that affect the table 'employees' within the database 'employees'.

The second step was to create an empty database in the second server, with the Innodb tables converted to BlackHole (to avoid wasting unnecessary space).

Inside the first sandbox, I did this:


$ ./my sqldump -B --no-data employees \
  | perl -pe 's/=innodb/=blackhole/i' \
  | ~/sandboxes/trans_repl/use

Combining the flexibility of the sandbox with some command line skills, the operation requires just one command.
Before starting the replication, I needed to avoid re-creating the tables, or my blackhole trick would have been useless. So I looked at the binary log, and found where the CREATE TABLE statements ended:


$ ./my sqlbinlog ./data/mysql-bin.000001 |less
BEGIN
/*!*/;
# at 3057
#101112  9:48:45 server id 1  end_log_pos 3364  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1289551725/*!*/;
INSERT INTO `departments` VALUES ('d001','Marketing'),('d002','Finance'),('d003','Human Resources'),('d004','Production'),('d005','Development'),('d006','Quality Management'),('d007','Sales'),('d008','Research'),('d009','Customer Service')/*!*/;

Armed with this knowledge, I logged in the second server and did the following:


FLUSH BINARY LOGS; # to get a binlog with only the statements that I need

CHANGE MASTER TO
master_host='127.0.0.1',
master_port=5570,
master_user='msandbox',
master_password='msandbox',
master_log_file='mysql-bin.000001',
master_log_pos=3057;    # this is the position after all the CREATE TABLE
                        # statements in the master
START SLAVE;

After a few seconds, I issued a "SHOW SLAVE STATUS". All was OK.
I flushed the logs again and inspected the second binary log. As expected, it contained only the statements related to the employees table.
Total cost of the operation: 5 minutes. Way quicker than writing this report!

Speaking at the UK Oracle User Group conference - Birmingham

2010-11-11T23:17:00.002+01:00

Back to the conference circuit after some rest.
On December 1st I will be speaking under my new affiliation at Continuent in the MySQL track at the UKOUG conference. My topic is MySQL - Features for the enterprise and it will basically cover the main features of MySQL 5.5.
This conference is the largest Oracle related event in Europe, and it is organized by users for other users. This year for the first time the conference hosts a MySQL dedicated track.
It is a sort of epidemic. Most of the important Oracle events have now some MySQL content, or a full track. I see this as a great opportunity for both classic MySQL and Oracle users to meet each other and find common business ground for the future.

Testing MySQL 5.5 semi-synchronous replication

2010-11-04T01:19:00.000+01:00

A few days ago I saw an article about Semi-Synchronous Replication in MySQL 5.5. It asks questions, and doesn't give answers beyond gut feeling. So I thought that I would do some practical testing of this new feature.
Before we go that way, though, let's revisit the theory.

How semi-synchronous replication works

Figure 1. A transaction with regular replication
With regular replication, you send a transaction to the master (1). When the COMMIT is received, the master executes it (2), and if successful it logs the transaction to the binary log (3). The the master answers the client request (4) with a successful result. In the meantime, the slaves replicate the record (5).
What happens if the master crashes after point #4 and before a slave has had a chance of getting the data in point #5?
The client will have a result for that transaction, but that data is lost, because it has never reached one slave.

Figure 2. A transaction with semi-synchronous replication

Let's see the same scenario with semi-synchronous replication. All is the same until point #3. Then, things change. The master does not return to the client. Instead, it alerts the slave that a transaction is available (4). The slave gets it and stores it to the relay log (5). Without further action, the slave tells the master that the transaction was received (6) and only then the master returns the result to the client (7).
What is the benefit? If the master crashes, the transaction is lost. but the client does not get the false information that the transaction was stored. Instead, if the master crashes before it returns the result to the client, the client gets an error, and knows that that transaction needs to be reworked when a new master is back.

Semi-synchronous replication in practice

Now, the practicalities.
How do you tell if semi-synchronous replication is working? If you leave the default timeout of 10 seconds, you have an immediate clue that something is wrong when a query takes too long to return. Investigation is possible by looking at the GLOBAL STATUS variables.
Rpl_semi_sync_master_status tells you if the master is ready for semi-synchronous replication.
Rpl_semi_sync_master_yes_tx is the number of positive transactions that were delivered using semi-synchronous replication.
Rpl_semi_sync_master_no_tx is the number of failed attempts at delivering a transaction via semi-synchronous replication. When that happens, Rpl_semi_sync_master_status becomes "OFF", and you need investigating.

The important thing to understand about this feature is that semi-synchronous replication does not guarantee that your transaction is executed in the slave. It will only tell you that the data has been transferred to the slave relay log. It can still happen that the transaction fails to execute on the slave (which could be either a bug in your application or a bug in MySQL replication). But this is not a cluster. Don't expect a two-phase commit.

Testing semi-synchronous replication

If you want to test this feature without suffering too much, you can use a tarball binary and MySQL Sandbox. Once you have installed MySQL Sandbox and have downloaded the server tarball, you can install a test replication system with

make_replication_sandbox --how_many_slaves=4 /path/to/mysql-5.5.6-yourOS.tar.gz

This will create a system with 1 master and 4 slaves.
The Sandbox has a shortcut to install the plugin quickly and painlessly:

sbtool -o plugin --plugin=semisynch -s $HOME/sandboxes/rsandbox_5_5_6

Now you will have the semi-synchronous plugin installed in the master and all the slaves. For our tests, we will make a shell script, an easy task thanks to the sandbox utilities.


#!/bin/sh

echo "disabling semi-synch replication in all slaves except 1"
./s1 -e 'set global rpl_semi_sync_slave_enabled=1'
./s1 -e 'slave stop io_thread; slave start'

for E in 2 3 4
do
    ./s$E -e 'set global rpl_semi_sync_slave_enabled=0'
    ./s$E -e 'slave stop io_thread; slave start'
done

#
# this query will show the main variables that tell
# if semi-synch replication is working
#
Q1='select variable_name, variable_value'
Q2='from information_schema.global_status'
Q3='where variable_name in'
Q4='("RPL_SEMI_SYNC_MASTER_YES_TX", "RPL_SEMI_SYNC_MASTER_NO_TX")'
I_S_Q="$Q1 $Q2 $Q3 $Q4"

echo ""
echo "creating a table. it should replicate through the semi-synch"
./m -vvv -e 'create table test.t1( i int)'
./m -e "$I_S_Q"

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (1)'
./m -e "$I_S_Q"

echo ""
echo "disabling semi-synch replication in slave 1"
./s1 -e 'set global rpl_semi_sync_slave_enabled=0'
./s1 -e 'slave stop io_thread; slave start'

echo ""
echo "enabling semi-synch replication in slave 3"
./s3 -e 'set global rpl_semi_sync_slave_enabled=1'
./s3 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (2)'
./m -e "$I_S_Q"

echo ""
echo "disabling semi-synch replication in slave 3"
./s3 -e 'set global rpl_semi_sync_slave_enabled=0'
./s3 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'NO' should increase"
./m -vvv -e 'insert into test.t1 values (3)'
./m -e "$I_S_Q"

echo ""
echo "enabling semi-synch replication in slave 2"
./s2 -e 'set global rpl_semi_sync_slave_enabled=1'
./s2 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (4)'
./m -e "$I_S_Q"

This script will first disable semi-synchronous replication in all the slaves except one. Then it will create a table, and check for the telling status variables.
This should work quickly and without problems. Then it will disable the plugin on the only slave that was active, and enable another slave instead.
Inserting a record on the master will work again quickly, as the newly enabled slave will get the record immediately.
Then the slave gets disabled, and we can witness what happens. The query takes a bit longer than 10 seconds, and the status variable tells us that semi-synchronous replication has failed.
We finally enable yet another slave, and when we try a further insertion, we can see that the semi-synchronous replication has resumed.

Very important:
To enable or disable semi-synchronous replication on a slave it is not enough to set the appropriate variable. You need also to restart the slave by issuing a STOP SLAVE IO_THREAD followed by a START SLAVE commands.

Here is a sample run:

disabling semi-synch replication in all slaves except 1

creating a table. it should replicate through the semi-synch
--------------
create table test.t1( i int)
--------------

Query OK, 0 rows affected (0.87 sec)

Bye
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 1              |
+-----------------------------+----------------+

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 2              |
+-----------------------------+----------------+

disabling semi-synch replication in slave 1

enabling semi-synch replication in slave 3

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 3              |
+-----------------------------+----------------+

disabling semi-synch replication in slave 3

inserting a record. The number of 'NO' should increase
--------------
insert into test.t1 values (3)
--------------

Query OK, 1 row affected (10.12 sec)

Bye
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 1              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 3              |
+-----------------------------+----------------+

enabling semi-synch replication in slave 2

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 1              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 4              |
+-----------------------------+----------------+

Using the above steps, you should be able to use semi-synchronous replication and do some basic monitoring to make sure that it works as expected.

QA at Continuent. A serendipitous job.

2010-11-02T15:19:00.000+01:00

A few days ago, I left the MySQL team at Oracle, after more than 4 years of work dedicated mostly to the MySQL community.
Someone will probably remember that, when I joined MySQL in 2006, I started my work in the QA team. It was no coincidence. My previous work as a consultant was very much focused on database development quality, even when my customers had approached me for different reasons.
Let's be frank. I am a minority. It's not common to find someone who is passionate about QA. I am aware of being a rare bird, who likes testing and bug searching, and doing all the little steps that all together improve the overall quality of a software system.
I had been thinking about my passion lately, and I often caught myself thinking why in my job at MySQL I let things slip away so much from my ambition of tackling some hard technical problems.
I was more or less in this frame of mind when I met by chance an old acquaintance, Robert Hodges, CEO of Continuent and a fine technologist whom I respect and admire. And I was astonished to hear him asking me if I knew a good candidate for a position of QA manager for Continuent.

You can guess the rest of the story. I told him that I indeed had a candidate, and the next day we went to dinner together, and talked the most hackish and refreshing talk that I have had in years with Ed Archibald, the CTO (oh, and the steaks were great too!)
A few days after that, Robert and Ed organized a demo of Continuent technology for me, and I was hooked.

There was, indeed, an uncommon problem to solve. I had received offers for very good positions at three other companies, and one more had asked me if I wanted to consider working for them. As all the offering parts are friends, this fact did put me in a difficult position. Every offer was very intriguing and exciting, and in the end I had to take a choice, leaving one happy and three disappointed friends in the field. After careful choice, I chose Continuent, where I will start as Director of Quality Assurance on November 15^th.
Fortunately for me, the three other friends are real friends, and they took the refusal graciously. They knew from the beginning that they had competition, and they knew that they could win or lose for reasons that they could not fully control. And indeed the main reason for choosing Continuent is the challenge of developing a beautiful technology that will eventually take the world by storm. (Or so it's the general plan. It's too early to make claims, as I haven't started getting acquainted with the technology yet, but it feels good).

What now? I have some tentative plans that I need to consolidate in the near future:

Taking some vacation. I am already one day into this first part;
Getting familiar with the technology that I will help improving;
Getting acquainted with my new colleagues, for which I will have a splendid occasion at the company meeting that will happen soon in Stuttgart;
Learning some new technology, both in development and in a more strict QA field;
Setting up my new laptop, which will hopefully come before I start the new job;
Talking at conferences. I have already committed to talking at the UKOUG in Birmingham, on December 1^st, and more will come.
Resuming my blogging at full speed. I have a mental drawer full of ideas, and I only need to transfer them to the keyboard.

And probably I will also start playing with the new technologies that I have been reading about in the last months but I haven't had time to pursue. Either way, I feel refreshed and ready for the next battle.
Thanks, Continuent, for this great opportunity!

Book review: MySQL 5.1 plugin development

2010-11-01T19:51:00.004+01:00

MySQL 5.1 Plugin Development,
by Sergei Golubchik and Andrew Hutchings.
Packt Publishing, 2010.
Executive summary: Highly recommended. If you want to develop MySQL extensions, buy this book. It's a must, written by two expert professionals who probably know more than anyone else on this matter. The book is full of practical examples explained with the theoretical information necessary to make it stick.

This book fills a gap in the world of MySQL documentation. Although the MySQL docs are extensive and thorough, to the point that sometimes you wished that the writers were less verbose and stick more to the topic, when it comes to the plugin architecture, there are still more some unanswered questions. I guess that the ones who have implemented MySQL extensions so far have read the code, more than the documentation.

But, back to the point. The void is filled now, and in such a wonderful way! I have to disclose that I was the reviewer of this book, and while this fact puts me in a conflicting position when it comes to a review, it has also given me inner reason for praise, beyond the experience of a regular reader. The people who have worked with me will know that I am hard to please, and so my review was peppered with suggestions for improvements that, I admit it, made the authors' life quite difficult. I mostly complained that some of the examples proposed for each chapter were not enough useful and interesting. In short, there was not enough incentive for the reader to start coding immediately. I am glad to report that the authors were very responsive, and, rather than being annoyed by my demands, did work enthusiastically at the examples in the book until they became a true gem in this category. This book will surprise you for its practical approach, when it guides you through the examples, until you end up with a working extension.
The first chapter is probably the part that will save many hours of attempts to everyone who wants to build a plugin. In that chapter the authors explain how to build the different types of plugins in several operating systems. If you have ever tried to build a plugin, you will appreciate this chapter.
The book's body covers every type of plugin that you can create in MySQL, from the classic UDF to the more recent storage engine. For each chapter, you will have code ready to compile and try on your own, and the line-by-line explanation of what that code does, and why it was written the way it is.
Whenever the code is less than intuitive, the authors take care of it with additional theoretical and practical explanation, making sure that the reader is fully informed on the topic, and can complete the task effectively.
Summing up, this is not a book for the idle reader. It's a a practical help for the doers, the ones who want to get the job done, and need guidance through the maze of the MySQL plugin protocol.

Update. There is also a review at Slashdot.