Migration from SQL Server to MySQL

I need to migrate data from SQL Server 2000 to MySQL. Currently I开发者_JAVA技巧 am using MySQL workbench. Can any one tell how to do this?

If you are searching for a tool MySQL has a tool called "MySQL Migration Toolkit" that can be used to migrate the data from SQL to mysql. But the first thing as mentioned above is to do a backup. The next thing to check would be whether there are any datatypes that cannot be converted?

Exactly, what have you tried? You can quickly migrate data from MSSQL to MySQL if they are in any of the following data file formats:

Paradox (.db) DBase (.dbf) Delimited Text (.txt) Excel (.xls) XML (.xml) MS Access Database (.mdb) ODBC

If its a one-to-one, exact same database architecture on both the new and the old servers, you might want to try using database tools meant to make this an easier process for GUI based administrators. Just go to download.com and find some software that may assist you in that migration, Navicat is a good one. The most important thing is to always BACK IT UP! BACK IT UP! BACK IT UP! Never do anything without mirroring drives and doing whatever it takes to make sure if you don't destroy any data, but if you do you'll have backup copies of it. Also how fast your machines are will be a sizable factor when it comes to migrating very large databases.

All in all you have many options to choose from, yet the most important thing is to back it up! Can't stress that enough, yeah it might seem like meaningless extra work especially on humongous database systems, but trust me, its better to be safe than sorry. Also, I always like rebooting machines prior to making database changes to them, cutting off any connection to the outside world or any processes depending or updating its data. Turning off the ODBC will do much of that for you on Windows as well, but as always better safe than sorry. Many a corruption can be avoided by simply booting the machine and having all data in memory finalized on the active database, before backing it up or appending to it.

Check out etlalchemy. It is a free, open-sourced Python tool capable of migrating between any of the following SQL databases: PostgreSQL, MySQL, Oracle, SQL Server, and SQLite.

To install: pip install etlalchemy

To run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# Migrate from SQL Server onto MySQL
src = ETLAlchemySource("mssql+pyodbc://user:passwd@DSN_NAME")
tgt = ETLAlchemyTarget("mysql://user:passwd@hostname/dbname",
                          drop_database=True)
tgt.addSource(src)
tgt.migrate()