You are here

Migration from other databases to MySQL or MariaDB

Database migration tools

A generic database migration tool to MySQL and MariaDB is the MySQL Workbench.

An alternative is the old MySQL Migration Tool Kit.

Recommended by MariaDB Foundation: Sqlines.

Adabas D to MariaDB migration

Project migrating an Adabas D database to MariaDB is in progress. More information are expected before end of 2015.

Oracle to MySQL or MariaDB migration

Data Migration Tools

Exasol to Galera Cluster for MySQL migration

Exasol is a shared in-memory column store for data analysis.

  • Data was extracted by the Exasol GUI as .CSV file.
  • Structure was extracted by the Exasol GUI as DDL commands and adapted manually because we failed to use the MySQL Workbench.
  • Data was loaded with the LOAD DATA INFILE command (20 Gbyte in about 70 minutes).
    LOAD DATA INFILE '/tmp/oli.csv'
    IGNORE INTO TABLE test.test
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY "\n"
    (id, @data, ts, @d1, @d2)
    SET
    data = IF(@data='', NULL, @data),
    d1 = IF(@d1='', NULL, @d1),
    d2 = IF(@d2='', NULL, @d2)
    ;

Problems:

  • MySQL Workbench on Linux requires the iODBC driver manager, Exasol provides an ODBC driver for unixODBC. On Max OS X Exasol supports iODBC, so we tried there...
  • Exasol TIMESTAMP should be converted to MySQL DATETIME.
  • Exasol TIMESTAMP starts with 1900-00-00 00:00:00 and ends with 9999-12-31.
  • Exasol seems NOT to be a Generic SQL92 Compliant RDBMS. We used Generic RDBMS instead in the MySQL Workbench.
  • Then we got the following MySQL Workbench error retrieving data. A bug at MySQL was filed (bug #77808).
    Starting...
    Connect to source DBMS...
    - Connecting to source...
    Connect to source DBMS done
    Check target DBMS connection...
    - Connecting to target...
    Reconnecting to Mysql@10.0.0.140:3306...
    Connection restablished
    Check target DBMS connection done
    Retrieve schema list from source....
    - Checking connection...
    - Fetching catalog names...
    Traceback (most recent call last):
      File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/db_generic_re_grt.py", line 715, in getCatalogNames
        return GenericReverseEngineering.getCatalogNames(connection)
      File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/db_generic_re_grt.py", line 193, in getCatalogNames
        return list(set(row[0] for row in cls.get_connection(connection).cursor().tables())) 
      File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/db_generic_re_grt.py", line 193, in 
        return list(set(row[0] for row in cls.get_connection(connection).cursor().tables())) 
    pyodbc.Error: ('HY000', '[HY000] [EXASOL][EXASolution driver]Character set conversion error or output buffer to small: Illegal byte sequence column 3, cursor position 751, codepage US-ASCII. (1) (SQLGetData)')
    
    Traceback (most recent call last):
      File "/Applications/MySQLWorkbench.app/Contents/Resources/libraries/workbench/wizard_progress_page_widget.py", line 192, in thread_work
        self.func()
      File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/migration_source_selection.py", line 456, in task_fetch_schemata
        self.main.plan.migrationSource.doFetchSchemaNames(only_these_catalogs)
      File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/migration.py", line 250, in doFetchSchemaNames
        catalog_names = self.getCatalogNames()
      File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/migration.py", line 214, in getCatalogNames
        return self._rev_eng_module.getCatalogNames(self.connection)
    SystemError: Error("('HY000', '[HY000] [EXASOL][EXASolution driver]Character set conversion error or output buffer to small: Illegal byte sequence column 3, cursor position 751, codepage US-ASCII. (1) (SQLGetData)')"): error calling Python module function DbGenericRE.getCatalogNames
    ERROR: Retrieve schema list from source: Error("('HY000', '[HY000] [EXASOL][EXASolution driver]Character set conversion error or output buffer to small: Illegal byte sequence column 3, cursor position 751, codepage US-ASCII. (1) (SQLGetData)')"): error calling Python module function DbGenericRE.getCatalogNames
    Failed

  • MySQL sql_mode strict_trans_table made troubles with NULL values.

Other thoughts: Possibly the Inforbright or the InfiniDB storage engines are better suited to solve this task than a Galera Cluster for MySQL?