Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
There is an existing database that was created in Informix. I would like to export it and then import it into MySQL.
I tried to export it using dbschema and it created the dbase.sql file and several table00123.unl files for the tables.
I tried to import the .sql file into MySQL and it didn't like the syntax of the files that Informix exported.
I just wanted to know the best way to export/dump/output an Informix database that can be imported into MySQL.
You first need to convert that output file nearest to MySQL syntax and convert the data type according to MySQL. You could user perl or sed scripts for first time and then use these techniques for future use. Once you have prepared the files of table creation then generate dynamic insert statements from informix and use them in MySQL for loading data.
Regards, Nawaz
There's a good post here. I suggest that you spend some time studying this page (I'm assuinmg that you are familiar with Informix). As the first post suggests, you'll have a lot more trouble if you are migrating stored procedures - frequently there isn't a 1-to-1 correspondance between logical constructs as there can be between different datatypes.
As for foreign keys, I'd leave those till after you've moved the data - you can always issue ALTER TABLE statements - or dump your (FOREIGN KEY-less) database and put those FOREIGN KEY statements in the CREATE TABLE part of the scripts (better IMHO).
If you're having particular issues, get back to us here. Oh, and BTW, I would carefully document each step if I were you - if you have to do it once, you may have to do it a few times. You might like to look at this - (never used it myself, although a friend has had good things to say about Talend).
[EDIT]
In addition to what @NawazSohail has said about MySQL Workbench (a great tool for any work with MySQL, especially if you're not a great fan of the command line), you could take a look here and this post in particular.
I agree with Nawaz. WorkBench migration tool works very well, at least between MS-SQL and Mysql. Another solution is to use a Modeling tool like PowerDesigner or Embarcadero Data Architect Studio. Reverse engineer your schema from INformix, then switch the schema to Mysql. You can then generate the script and apply it to Mysql. For data export, you'll need to do some conversion (especially for dates).
