How to migrate data from an Informix database to MySQL

Question

We have to migrate data from an Informix database to a MySQL database.

Help me how we can achieve it?

What steps or commands I have to use it? If you have any document or reference link that would be very much helpful.

There is a lot of tools which can be found in Internet by `informix to mysql migration tool` searching string. — Akina, Jun 09 '21 at 11:58
The primary tool for exporting an Informix database is `dbexport`; it produces a specific format, usually with pipe symbols `|` separating fields. It includes the schema information. An alternative is to manually create external tables (`CREATE EXTERNAL TABLE …`) with `USING(FORMAT 'DB2', DELIMITER ',', DATAFILES(…))` to create CSV formatted data. This is fiddlier. The `dbschema` program can create the schema. See IBM's Informix documentation for [CREATE EXTERNAL TABLE](https://www.ibm.com/docs/en/informix-servers/14.10?topic=statements-create-external-table-statement). _[…continued…]_ — Jonathan Leffler, Jun 10 '21 at 13:56
_[…continuation…]_ There are some problems with the documentation which I'm going to be reporting as errata — notably, no mention of `FORMAT 'DB2'`. See also [How do I convert key-value paired list into table with columns using `awk`](https://stackoverflow.com/q/51104731/15168) and [How to convert `.txt` files to `.xls` files using Informix 4GL?](https://stackoverflow.com/q/19649368/15168), both of which feature a script for converting from Informix UNLOAD format to CSV format. — Jonathan Leffler, Jun 10 '21 at 14:00

score 1 · Accepted Answer · answered Jun 13 '21 at 10:42

Step1: login to Informix and run the unload command. It will create a backup file named "file1" at current location under export directory.

UNLOAD to 'export/file1' SELECT * FROM db1.table1

Step2: Create table1 in MySQL Database.

Step3: Login to MySQL Database and set the global variable.

mysql> SET GLOBAL local_infile=1;
mysql> quit

Step4: Login to MySQL Database using below command

mysql --local-infile=1 -u root -p

Step5: Load the data into MySQL Databse using LOAD DATA command

LOAD DATA LOCAL INFILE '/export/file1'
INTO TABLE table1
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n';

1 Answers1