23

I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the MySQL ODBC driver, and it correctly accesses both databases, but the xml files containing the specifications for type conversion did not exist and the specifications were too limited for me to correctly create them. Does anyone know either how to create the type conversion files or where to get a better tool for transferring this data?

Thomas Kejser
  • 6,218
  • 2
  • 24
  • 46
murgatroid99
  • 371
  • 1
  • 2
  • 8

6 Answers6

13

I have two suggestions:

1) I hate bringing up commercial products but there is a $49.00 tools to Migrate MSSQL to MySQL

2) Try MySQL's MSSQL Migration forums for further suggestions

UPDATE 2011-06-03 18:03 EDT

There is an old product that went EOL back in January 2010 called the MySQL Migration Toolkit. If you can get a hold of it, you can use it.

UPDATE 2011-06-03 18:06 EDT

I found the archives !!! Here is the MySQL Migration Toolkit

UPDATE 2011-06-03 18:11 EDT

Here is the MySQL Migration Toolkit Overview

UPDATE 2011-06-03 19:08 EDT

Another commerical product ($29)

UPDATE 2011-06-03 19:30 EDT

Here is a list of Freeware tools that Migrate MSSQL to MySQL.

UPDATE 2011-06-15 17:47 EDT

Get the WhitePaper (PDF) from Oracle on the Guide to Migrating Microsoft SQL Server to MySQL (Still Commercial)

UPDATE 2012-08-21 01:24 EDT

According to this MySQL WebPage, the section MySQL Workbench: Database Migration Wizard claims that the MySQL Workbench has the capability of Migrating DB Objects from SQL Server to MySQL.

Vinayagam
  • 103
  • 3
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
4

Based on Marian's suggestion, I found this answer about replicating in the other direction by setting up the MySQL server as a linked server in MS SQL Server. With MySQL set up as a linked server, I can run SQL queries on both databases at the same time, which provides exactly the functionality I needed to solve this problem.

Pang
  • 209
  • 1
  • 8
murgatroid99
  • 371
  • 1
  • 2
  • 8
3

MySQL Workbench can do this:

http://dev.mysql.com/downloads/workbench

It can be installed directly on the MS SQL Server machine (speed advantage!), which needs to be able to access your Ubuntu MySQL Server.

mit
  • 507
  • 2
  • 6
  • 12
3

Have you looked into using SSIS for this task? This is the ETL tool for SQL Server and it has lots of transformations and logic that could help you perform this task.

SQLChicken
  • 381
  • 1
  • 4
2

I recently released etlalchemy to accomplish this task. It is an open-sourced solution which allows migration between any 2 SQL databases with 4 lines of Python. Supported RDBMS's include MySQL, PostgreSQL, Oracle, SQLite and SQL Server.

This will take care of the daunting task of mapping one SQL vendor's column types, to another's. Not only will it transfer and translate schema, it will also migrate all data, indexes and constraints across databases.

To install: 

$ pip install etlalchemy

On El Capitan: pip install --ignore-installed etlalchemy

To run: 

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

mssql_db_source = ETLAlchemySource("mssql+pyodbc://username:password@DSN")

mysql_db_target = ETLAlchemyTarget("mysql://username:password@hostname/db_name", drop_database=True)
mysql_db_target.addSource(mssql_db_source)

To get more background on the origins of the project, check out this post. If you get any errors running the tool, open an issue on the github repo and I'll patch it up in less than a week!

The Aelfinn
  • 191
  • 4
2

I tried Migration toolkit to import from SQL server to MySQL. But found SQLyog Import external data good. I could schedule the import process and also do necessary mappings to import to an existing table. Download from here.

enter image description here

Jacob
  • 149
  • 2