22

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?

  • 1
    Have you seen this question? It's about replicating data from MySql to MSSQL. Maybe you'll find some hints there. – Marian Jun 3 '11 at 21:38
  • @Marian Thanks for the link. Using the MySQL server as a linked server in SQL Server seems to be a solution for my problem. – murgatroid99 Jun 6 '11 at 18:00
  • 1
    How much data are we talking about? 5mb? 3TB? – AaronJAnderson Feb 5 '13 at 23:40
  • I think it was GBs or 10s of GBs, but I don't really remember because this question is from a year and a half ago. – murgatroid99 Feb 6 '13 at 0:25
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.

  • 1
    I would prefer not to use any commercial products if I can avoid them. I tried using the Migration Toolkit, but I think it is incompatible with the newest version of MySQL: the generated SQL insert statements had an enormous number of syntax errors and the generated tables had no rows after running the wizard. The shareware tools also seem to be made to work with older versions of MySQL. Thanks for trying. – murgatroid99 Jun 6 '11 at 17:31
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.

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.

  • The Import/Export Wizard I mentioned using actually creates SSIS packages. The problem I had was that SSIS uses XML files to store data about how to convert between types in different database management systems, and those files did not exist for MySQL. – murgatroid99 Jun 6 '11 at 17:37
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.

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

  • SQLyog is a great tool, but the "import external data" feature is extremely slow. As in several orders of magnitude slower than MySQL Workbench. I wish SQLyog developers would fix the problem, because the interface is much more intuitive than Workbench. But unless you are only importing hundreds of rows than be prepared to wait hours or possibly days for the import to finish. – nextgentech Feb 11 '17 at 21:13
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!

0

MySQL Workbench Migration tool can help you do this task

1) To start the migration process, on the MySQL Workbench main screen, go to Database-> Migration Wizard.

2) We should check the prerequisites to confirm if we can continue the task. If everything looks fine, we can press on Start Migration.

3) In this step, we need to provide the information about the source database, in this case, SQL Server.

We’ll configure our source parameter:

Database System: Microsoft SQL Server

Connection Method: ODBC (Native)

Driver: SQL Server

Server: localhost

Username: sa

4) Now, we can check the connection by using the Test Connection button.

5) Then, we need to add the target parameters:

Connection Method: Standard (TCP/IP)

Hostname: Your_host_name

Port: 3306

Username: migration

6) And press on Test Connection to confirm the added information.

7) In the next step, MySQL Workbench will connect to our SQL Server to fetch a list of the catalogs and schemas.

8) Now, we’ll choose the Your_database_name database from the list.

We can choose how the reverse engineered schemas and object should be mapped. We’ll use Catalog.Schema.Table -> Catalog.Table option, so in our MySQL, we’ll have selected database and the current tables that we have in our SQL Server database.

9) If everything went fine, we’ll have a list of objects to be migrated.

10) In this case, we have Table Objects, View Objects and Routine Objects. We’ll only select the Table Objects because for the rest of the object we should check the corresponding MySQL equivalent code manually.

11) In this step, the objects from the source are converted into MySQL compatible objects.

12) If everything went fine, we can continue by selecting how we want to create the migrated schema in the target. We’ll use the default “Create schema in target RDBMS” option.

13) Now, let’s check the creation schema process.

14) In the next step, we can check the result of each script execution, and we can check the new database created on our MySQL Server.

15) At this point, we’ll have the database structure, but we don’t have the data yet. Now, we’ll select how we want to copy the data in the MySQL Server. We’ll use the “Online copy of table data to target RDBMS” option.

16) In the last step, we can check the migration report and finish the task.

Another easy method of converting a MS SQL DB to MySQL is using Stellar's DIY tool named Stellar Converter for Database that can directly pick database file of a specific database and provide preview of the convertible tables and after conversion will directly store the data into the specified database installed on your system. In this case you'll have to provide MS SQL database file and after conversion it will be stored in MySQL database installed in the system. You can get free version of this software from Stellar's Official Website.

protected by Community Dec 5 '16 at 18:45

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.