94

I'm trying to convert from a SQL Server database backup file (.bak) to MySQL. This question and answers have been very useful, and I have successfully imported the database, but am now stuck on exporting to MySQL.

The MySQL Migration Toolkit was suggested, but seems to have been replaced by the MySQL Workbench. Is it possible to use the MySQL Workbench to migrate from SQL Server in the same way that the migration tool worked?

Or is the Migration Toolkit still available somewhere?

Community
  • 1
  • 1
matkins
  • 1,991
  • 1
  • 16
  • 22
  • 3
    I've got a MS SQL database and I want to import the data in it to MYSQL to replace the original system. I've now found an old version of the Migration Toolkit but haven't quite managed to connect to the MS SQL Server yet. – matkins Oct 14 '10 at 08:19
  • Maybe this could help. http://stackoverflow.com/questions/2986404/convert-tsql-to-mysql/23088879#23088879 – Madagaga Apr 16 '14 at 07:07
  • 3
    I agree. I am still not finding anything substantially helping me to convert from mssql to mysql, despite of the fact that this topic is marked as duplicates. Mssql to Mysql is such a varying-case-by-case topic. It is quite insulting of those who marked this as duplicate. How ignorant! – Curly Jul 06 '16 at 12:21
  • 1
    May it help you : https://www.youtube.com/watch?v=9OHXCYRYjRs – Nolwennig Aug 01 '16 at 09:19

11 Answers11

54

You can use MySQL Workbench which provides a way to quickly migrate data and applications from Microsoft SQL Server to MySQL employing less time and effort.

This tool has a lot of cool features like:

  • Database migrations - enables migrations from Microsoft SQL Server, Sybase ASE and PostgreSQL.
  • Migration project management - allows migrations to be configured, copied, edited, executed and scheduled.

Read more http://www.mysql.com/products/workbench/migrate/

maxandonuts
  • 595
  • 7
  • 19
Javier Cadiz
  • 12,326
  • 11
  • 55
  • 76
  • I used this to migrate from SQL Server on my Mac. Also downloaded drivers http://www.actualtech.com/download.php and the original ODBS Adminstrator from Mac. – powder366 Jul 25 '14 at 12:23
  • 1
    @powder366, can you be more specific on how to connect your Mac to SQL Server? Thanks! – bizi Jan 17 '15 at 02:34
  • IT WORKS !!! After failing to import from PhpMyAdmin (_o'rly ?_), and trying AWS's schema conversion tool, which refused to install on both my Win7 and Win10 PC without giving any error message, I finally got a working tool ! Because I couldn't create a "connection" to a SQL-Server DB in Workbench, I thought I was missing a working ODBC driver. But I was wrong. Looks like the only place in Workbench's where you can connect to said SQL-Server is the migration dialog. So, I used HeidiSQL to test my connection outside of Workbench. Thx @Nolwennig for the video (in question's comments) – Balmipour Dec 05 '16 at 20:18
  • @Javier Cadiz, is it possible to migrate store procedure and trigger same as SQL? – er.irfankhan11 Jul 25 '17 at 09:57
  • and yes and no, able to export small DB, if you have a lot of data and e.g. bigint PK got error; and retry button in migration wizard is bugged fake in that case; – user1005462 Jul 11 '19 at 08:20
  • One thing to note for someone who will stuck as me with slow speed from Parallels VM - install both MySQL and MSSQL to same VM. In my case conversion from MSSQL running in VM and MySQL running on hosting MacOS was extremely slow - about 10 seconds to insert 100 rows with 9 text columns. – m1ld Sep 16 '21 at 08:06
25

I use sqlyog to migrate from mssql to mysql. I tried Migration toolkit and workbench but liked sqlyog for its SJA. I could schedule the import process and could do incremental import using WHERE clause.

enter image description here

Man_k
  • 322
  • 3
  • 3
8

PhpMyAdmin has a Import wizard that lets you import a MSSQL file type too.

See http://dev.mysql.com/doc/refman/5.1/en/sql-mode.html for the types of DB scripts it supports.

SnapShot
  • 5,464
  • 5
  • 42
  • 40
David d C e Freitas
  • 7,481
  • 4
  • 58
  • 67
7

if you have a MSSQL compatible SQL dump you can convert it to MySQL queries one by one using this online tool

http://burrist.com/mstomy.php

Hope it saved your time

HimalayanCoder
  • 9,630
  • 6
  • 59
  • 60
  • Be wary of using this, some flaws in logic in the replaces. Just at first glance, my tables with the lets `go` in them, like `Categories`, were changed to `Cate;ories`. Changing the regex, as it allows you, to `\bgo\b` did not work. – Regular Jo Sep 19 '19 at 16:35
6

As mentioned above, if your data contains tab characters, commas, or newlines in your data then it's going to be very hard to export and import it with CSV. Values will overflow out of the fields and you will get errors. This problem is even worse if any of your long fields contain multi-line text with newline characters in them.

My method in these cases is to use the BCP command-line utility to export the data from SQL server, then use LOAD DATA INFILE .. INTO TABLE command in MySQL to read the data file back in. BCP is one of the oldest SQL Server command line utilities (dating back to the birth of SQL server - v6.5) but it is still around and still one of the easiest and most reliable ways to get data out.

To use this technique you need to create each destination table with the same or equivalent schema in MySQL. I do that by right clicking the Database in SQL enterprise manager, then Tasks->Generate Scripts... and create a SQL script for all the tables. You must then convert the script to MySQL compatible SQL by hand (definitely the worst part of the job) and finally run the CREATE TABLE commands on the MySQL database so you have matching tables to the SQL server versions column-wise, empty and ready for data.

Then, export the data from the MS-SQL side as follows. 

bcp DatabaseName..TableName out TableName.dat -q -c -T -S ServerName -r \0 -t !\t!

(If you're using SQL Server Express, use a -S value like so: -S "ComputerName\SQLExpress")

That will create a file named TableName.dat, with fields delimited by ![tab]! and rows delimited by \0 NUL characters.

Now copy the .dat files into /tmp on the MySQL server and load on the MySQL side like so:

LOAD DATA INFILE '/tmp/TableName.dat' INTO TABLE TableName FIELDS TERMINATED BY '!\t!' LINES TERMINATED BY '\0';

Don't forget that the tables (TableName in this example) must be created already on the MySQL side.

This procedure is painfully manual when it comes to converting the SQL schema over, however it works for even the most difficult of data and because it uses flat files you never need to persuade SQL Server to talk to MySQL, or vice versa.

Professor Falken
  • 1,017
  • 11
  • 13
5

You can do this easily by using Data Loader tool. I have already done this before using this tool and found it good.

Fluffeh
  • 33,228
  • 16
  • 67
  • 80
Kevin
  • 51
  • 1
  • 1
3

It looks like you correct: The Migration Toolkit is due to be integrated with MySQL Workbench - but I do not think this has been completed yet. See the End-of-life announcement for MySQL GUI Tools (which included the Migration Toolkit):

http://www.mysql.com/support/eol-notice.html

MySQL maintain archives of the MySQL GUI Tools packages:

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

Martin
  • 9,674
  • 5
  • 36
  • 36
  • Certainly this is the actual state. Good news are that with http://dev.mysql.com/doc/migration-toolkit/en/scripted-migration.html you can do on your own. See GRT Shell and Lua scripts! – albfan May 19 '12 at 07:34
  • 1
    Link not there anymore. – powder366 Jul 25 '14 at 11:34
3

I used the below connection string on the Advanced tab of MySQL Migration Tool Kit to connect to SQL Server 2008 instance:

jdbc:jtds:sqlserver://"sql_server_ip_address":1433/<db_name>;Instance=<sqlserver_instanceName>;user=sa;password=PASSWORD;namedPipe=true;charset=utf-8;domain=

Usually the parameter has "systemName\instanceName". But in the above, do not add "systemName\" (use only InstanceName).

To check what the instanceName should be, go to services.msc and check the DisplayName of the MSSQL instance. It shows similar to MSSQL$instanceName.

Hope this help in MSSQL connectivity from mysql migration toolKit.

rajesh.sampangiramiaih
  • 41
  • 4
2

MySQL Migration Toolkit (1.1.10) still available for download from here:

http://downloads.mysql.com/archives.php?p=MySQLDeveloperSuite&v=1.1.10

Matias P.
  • 64
  • 4
  • The 1.1.x version was discontinued, but there are older versions available (the latest there is the v1.0.25): https://downloads.mysql.com/archives/migration/ – Matias P. Nov 14 '22 at 23:29
1

I had some data I had to get from mssql into mysql, had difficulty finding a solution. So what I did in the end (a bit of a long winded way to do it, but as a last resort it works) was:

  • Open the mssql database in sql server management studio express (I used 2005)
  • Open each table in turn and

  • Click the top left corner box to select whole table:

  • Copy data to clipboard (ctrl + v)

  • Open ms excel
  • Paste data from clipboard
  • Save excel file as .csv
  • Repeat the above for each table
  • You should now be able to import the data into mysql

Hope this helps

Dave
  • 49
  • 1
  • 1
    Looking at the other answers, it seems you can use MySQL Workbench to migrate. Why would you do this by hand? (For just a few tables you method can work, but if you have many tables, it is just a never ending job.) – Styxxy Feb 20 '13 at 19:46
  • really, is that what you did????? – Rafael Herscovici Mar 14 '14 at 10:31
  • This is not a migration, lol. This is a data copy paste. Migration does way more than that, and if you have 50 tables, will you do it for all of them? – daminufe Nov 27 '15 at 12:38
  • love you man... +1 for your spirit to help others, with whatever you know. some may not know about MySQL workbench, yours is foolproof method, except that it will not import structure of data. – Ramesh Pareek Nov 25 '17 at 15:33
0

downloads are no more available on the official website (http://dev.mysql.com/downloads/gui-tools/5.0.html) instead, take a look here: http://download.softagency.net/MySQL/Downloads/MySQLGUITools/

Marco C.
  • 1,282
  • 2
  • 15
  • 19