One of the key features of our first beta was drizzledump’s ability to migrate from a MySQL database.  For those who don’t know about this, the inimitable Andrew Hutchings enhanced drizzledump so that one could transfer data from MySQL directly to Drizzle without needing any intermediate files.  In this example, we connect to MySQL, running on 3306 and send the data in database my_mysql_schema, tables t1,t2, and t3 into our Drizzle server (port 4427).


$ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --destination-database=my_mysql_schema --user=root my_mysql_schema t1 t2 t3;


There are several ways of using this tool.  One could:

• Transfer the data from all databases
  • $ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --all-databases --user=root  ;
• Transfer data from schema ‘my_orig_schema’ on MySQL into schema ‘my_new_schema’ on Drizzle
  • $ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --destination-database=my_new_schema --user=root my_orig_schema
• Transfer only the contents of specific schemas
  • $ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --databases --user=root my_schema1 my_schema3 my_schema5

The documentation can be found here and the blueprints for development and testing might be of interest as well.  The dev blueprint describes conversions and certain details that haven’t made it to the docs yet and the testing blueprint provides a list of bugs we’ve found. We found a number of minor, yet annoying bugs, but they have all been corrected at this time.

To return to the topic of testing – at first I had intended to use the randgen.  We already use it to test our ability to restore from drizzledump files.  While we did find some initial bugs with our tests, expressing certain things like indexes and foreign keys in a randgen grammar were proving problematic.

In the end, I created a new suite of tests for our standard test-runner.  The tests only have the following requirements:

• You have a running MySQL server
• You set two environment variables:
  • DRIZZLE_MYSQL_MIGRATE_TEST=1
  • DRIZZLE_MYSQL_MIGRATE_PORT= port where MySQL is running

Thanks to the utility dictionary plugin, we have the ability to SELECT environment variables and we use their presence to signal if we should run the test or not; if they aren’t set, the suite is skipped.  For example, we use this query in our checks:

SELECT VARIABLE_VALUE AS `TRUE` FROM DATA_DICTIONARY.ENVIRONMENTAL WHERE VARIABLE_NAME="DRIZZLE_MYSQL_MIGRATE_TEST";

When the tests run, they do the following:

• populate the MySQL server with the relevant test-bed (tables + data)
• call drizzledump to transfer the data from MySQL to Drizzle
• perform various queries to ensure the transfer worked as expected (SHOW CREATE + SELECT * generally).

To run the tests:

./test-run --suite=mysql_migrate


You can expect to see the code in trunk shortly after this is posted.

We test every MySQL data type; we also test engines, indexes, foreign keys, and other setups.  While there are likely still some bugs left to be found, I believe that the tool should work smoothly for most users.  With that said, I would like to give a special thanks to Tim Soderstrom (aka Sweetums), MySQL DBA extraordinaire and Drizzle contributor.  He’s an early-adopter and helped us find a number of bugs while experimenting with migrating his own data to Drizzle.  Thanks again, Tim!

I hope that you’ll give the tool a spin and that you’ll write up a bug report if something isn’t working.  Questions and whatnot are always welcome on IRC and the mailing lists too : )