Importing a file dumped from MySQL with mysqldump into drizzle

As a big fan of new technology, we try to keep up to date with what’s happening in the industry. As such, I decided to start using drizzle on my development machine since they announced GA this week.
First exercise: import a file dumped from a MySQL server I don’t have access to into drizzle. Normally, you can use drizzledump on the mysql server and make it dump a drizzle compatible file. Not in this case, so I decided to sed my way through the various errors. Not pretty, and I hope that at some point we’ll have a tool that can convert a mysqldump into a drizzle compatible file, but it works for now.
Here’s what I had to do. Note that this is by no means complete or comes with any guarantees, it’s just a starting point.
# This file started by setting a SQL_MODE. That doesn't exist in 
# drizzle, so we comment it out
sed -i "s/^SET SQL_MODE/#SET SQL_MODE/g" mysqldump.sql 

# The create database statement set a default character set. 
# Everything in drizzle is UTF8, so let's lose it!
sed -i "s/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci//g" mysqldump.sql 

# The table definitions mentioned a default character set. 
# Everything in drizzle is UTF8, so let's lose it!
sed -i 's/DEFAULT CHARSET=utf8//g' mysqldump.sql 

# No MyISAM except for temporary tables, so away with it.
sed -i 's/ENGINE=MyISAM//g' mysqldump.sql 

# Invalid timestamps are not accepted in drizzle, so this should be a null 
# value. Since some of the columns in this file are actually NOT NULL defined, 
# for now I just set those dates to 1970. UGLY, but works for me. Don't do this 
# on anything that will ever go anywhere near production though!
sed -i "s/'0000-00-00/'1970-01-01/g" mysqldump.sql 

# tinyint doesn't exist anymore, so just replace with integer. Note that you'll 
# have to do this for all data types that no longer exist in drizzle
sed -i "s/tinyint(.*)/integer/g" mysqldump.sql
Hope this helps others!

Filed under: Software and tools by Walter Heck on Thursday, March 17, 2011 | Social tagging: >

3 Responses to “Importing a file dumped from MySQL with mysqldump into drizzle”

  1. Daniël van Eeden, on March 21st, 2011 at 1:30 pm Said:

    This will parse the dump multiple times, which will take a lot of time for bigger dumps. With the -e option for sed these statements can all be used at once, which will probably be much faster.

  2. Walter Heck, on March 22nd, 2011 at 7:22 am Said:

    Hi Daniel,

    thanks for the tip, that will make a decent difference on larger dumps indeed. Lucky for me this one was only 5MB so it was okay :)

    Walter

  3. Josh Koenig, on July 29th, 2011 at 8:05 pm Said:

    If you have drizzle running, you can also point drizzledump at your mysql server and it will do lots of fun conversions on the fly:

    http://docs.drizzle.org/clients/drizzledump.html#mysql-migration-using-drizzledump

Leave a Comment

 
« «