I’m also planning to change to MySQL from MS SQL for my blog. If possible it would be better if you explain the process that you have followed for this transition.

Thanks,
Akbar

@Akbar:

Oh, I wouldn’t look to me for guidance there It was a long, painful, arduous process.

I recently switched to a Mac, so I’ve got MySQL on it. That sort of motivated me to try and gradually move away from SQL Server. Nothing against SQL Server, but for something like this blog… there’s no reason not to use a free and readily available solution.

So my situation was… my blog entries were in a SQL Server database up on a VPS. I wanted to convert them to MySQL here on my local machine.

I’ve still got a PC running, so using Enterprise Manager I connected to the remote SQL Server instance, and tried exporting the data to plain text files (to be imported into MySQL). Unfortunately, on a couple of tables that had NTEXT datatypes, the export failed. I spent a while trying to figure out how to get around that, but every avenue I looked into ended up with the same unhappy result.

Since all I really needed to do was one database (the one powering the blog), I ended up going the brute force route and simply writing a cfm page that did a SELECT * on each table, then looped over the result and did an INSERT into an empty table in a newly created database on the remote MySQL database.

Not particularly elegant, but it got the job done.

I’m sure there has to be a better way. If anyone has any suggestions, please feel free to post them. I may be done with migrating the blog over, but you never know when the need to do something like this will arise again.

@Akbar:

I’ve zipped the .cfm file that I used and added it to this entry (you should see a ‘download’ link by the ‘comments’, ‘print’, etc).

Again, I stress that this isn’t an elegant solution nor is it one that I’m particularly proud of. But if you’re using BlogCFC, and another, more elegant method doesn’t come up, this might save you a little bit of time. Obviously, edit as needed.

Thanks you so much for the immediate response. I’m using blogCFC for my blog as well i thought of changing it to MySQL and I will give it try coming weekend . Again thanks for the script.

I’m getting "Internal Server Error" while clicking the download.

d’oH! Looking into it…

@Akbar – should be working now. For some reason, the file name (which was blogExport.cfm.zip) didn’t sit well with something on the server.

Renaming it to blogExport.zip seems to have fixed it.

Nice move Charlie. I’m all about MySQL these days. I still use MS SQL in a large enterprise environment at work but that’s more because that’s "what we know." MySQL can handle enterprise apps these days too and I hope to dip more into using MySQL in a HA environment.

Migrations like this will gather speed one would hope. After a career of MS tunnel vision, I find myself seeping back to the LAMP stack from which I came….

Many thanks

http://www.cridgeautos.co.uk/index.html
http://www.cridgeautos.co.uk/your+tyres.html

the MySQl Migration toolkit is a GUI tool that makes this kind of switch over much easier.

http://dev.mysql.com/downloads/gui-tools/5.0.html

Charlie, you should have sent me a ping I could have saved you some sweat and tears.

I , like Charlie, am looking for work preferably in Jacksonville, FL.

http://www.linkedin.com/in/johnlyons

@John:

I looked into that tool. I was trying to migrate the SQL Server database to MySQL on my local machine (running OS X). The MySQL Migration Toolkit is only available for Windows right now.

Altho, not sure why I didn’t just migrate to the instance of MySQL running on the VPS and then export down to my local machine. I’ll chalk it up to the fact that it was probably very late at night