In association with heise online


Drizzle completely ripped out the replication used in MySQL and implemented a new model. The new replication stores its log inside a hidden InnoDB table which is committed along with the transaction, this gives higher performance writes to the replication log. As well as this, the new replication log is stored using Google Protocol Buffer messages which are easily readable using code written in pretty much any programming language. This said the way the replication is connected up is still in a very similar master/slave scenario.

Drizzledump Automated Migration

Drizzledump is a backup tool very much like mysqldump, but it has one special extra feature: migration. It can automatically figure out that it is connecting to a MySQL server instead of a Drizzle server and will convert all data to a Drizzle compatible format on-the-fly. On top of this it can connect to a destination server, so all the schemas and data can be pumped from a MySQL server to a Drizzle server with no intermediate files.

This tool does all the conversions outlined above along with a few other minor ones, a full list can be found in its documentation. Drizzledump uses libdrizzle to connect and one thing it doesn't support is the MySQL old password format used in versions older than MySQL 4.1. This could cause a problem for users of RedHat and CentOS which turn on the old password format by default, even on more recent versions of MySQL. These passwords will need upgrading before drizzledump (or any other drizzle tool) can connect using them.

As described earlier, Drizzle only supports UTF-8 and in fact when connecting to a MySQL server will automatically set the connection to send/receive data in UTF-8 format. There is one issue for some people here and a workaround for it, but to describe why there is a quick background story.

A new user was using drizzledump to convert their data from MySQL to Drizzle, but found that, for example, the 'è' character was getting converted into 'è'. After a little bit of discussion with the user it was found that although the table was UTF-8 and the data was UTF-8 the connection was set to 'latin1'. This is due to the MySQL PHP connector which does this by default and in most cases this won't be too much of a problem, but it can be if lots of special characters are used. When drizzledump was connecting it was setting the connection to UTF-8 so the MySQL was reading the UTF-8 table data thinking it was 'latin1' and converting it to UTF-8 again. Hence the corrupted data.

As a workaround for such stories happening in future a new option was very quickly implemented called '--my-data-is-mangled'. This basically leaves the connection at its default character set and resolved the problem for the user.

So, to use drizzledump for migration simply do:

$ drizzledump --port=3306 --user=mysql-user --password --all-databases --destination-type=database --destination-host=drizzle-host --destination-port=4427

Breaking this down it is connecting to the MySQL server '' on port 3306 (port 4427 is the default for drizzledump), the username 'mysql-user' and drizzledump will prompt the user for a password. When connecting to MySQL the '--all-databases' option (also known as '-A') will retrieve all but the 'mysql', 'PERFORMANCE_SCHEMA' and 'INFORMATION_SCHEMA' databases. The interesting options are the --destination-* options. By default the type is set to 'stdout' which gives a dump as normal, but setting the type to 'database' will write the dump data straight into a Drizzle database specified by the destination-host and destination-port.

Application Changes

For the most part application changes will likely be very minor, the query syntax in Drizzle is very similar in most ways as is the parser/optimiser. For example, to convert Wordpress all that is needed is to change the queries using '0000-00-00' for the date. On another site converted, the usage of INET_ATON/INET_NTOA functions had to change to use PHP equivalents which pushes the logic down to the PHP layer rather than the database layer (arguably where it should be). The INET_* functions are planned for a later version, when a native IP address type is implemented.

Some of the query syntax has got a little bit stricter for safety purposes. For example 'SELECT * FROM t1, t2;' will error in Drizzle, this is called an implicit Cartesian join and can return every combination of rows for the tables being joined. Doing so can eat a lot of memory and disk space. If this kind of join is required it can be explicitly requested with 'SELECT * FROM t1 CROSS JOIN t2;'; alternatively adding a WHERE condition on one of the tables will also allow it to work. Additionally multi-table UPDATE and DELETE queries will error, the same thing can be achieved with sub-queries or transactions.


Although there are several things to consider when migrating from MySQL to Drizzle, in many cases the migration is very simple, especially with the use of drizzledump to automatically convert the table structures and data. Some people may miss a few features which are not in Drizzle, but just because it is gone now does not mean it is gone forever. In many cases features are being reimplemented to work with the plugin system and to be more flexible than they were in MySQL. You, the community, help shape the future for Drizzle. If you have any feedback or suggestions please feel free to get in touch. To contact the developers simply visit the #drizzle channel on Freenode or sign-up to the Drizzle mailing list.

Although there is currently not yet any fixed roadmap for the next version of Drizzle, it is possible to get an idea of some of the future features by looking at the project's Google Summer of Code wiki page. For the progress on the implementation of new features one of the best sources of information is the 'Last Week in Drizzle' blog posts.

Andrew Hutchings is a Software Developer on the Drizzle project for Rackspace and co-author of MySQL 5.1 Plugins Development. You can follow him on @LinuxJedi on twitter or on his blog

Print Version | Permalink:
  • Twitter
  • Facebook
  • submit to slashdot
  • StumbleUpon
  • submit to reddit

  • July's Community Calendar

The H Open

The H Security

The H Developer

The H Internet Toolkit