Use MySQL migration toolkit to quickly import Oracle Data to MySQL

Use MySQL migration toolkit to quickly import Oracle Data to MySQL

 

Recently, I have a project to import data from the original Oracle database to MySQL. after trial, I found that MySQL migration toolkit in MySQL GUI tools can easily and quickly import Oracle Data to MySQL, especially for clob and BLOB data. The following describes how to use MySQL migration toolkit. This software can be downloaded at http://dev.mysql.com/downloads/gui-tools/5.0.html.

 

1. Run MySQL migration toolkit, choose "Next" to "source database", and select Oracle database server in database system. If you use it for the first time, you will be advised to load the driver ojdbc14.jar.

 

After the driver is loaded, the interface will become the following form, fill in the connection information of the Oracle database, and press "Next" to continue.

 

 

2. In target database, the default database system is MySQL server. In connection parameters, enter the connection information of the MySQL database and press "Next" to continue.

 

 

3. Perform a database connection test. After the test is passed, press "Next" to source schemata selection, click schemata for data migration, and then press "Next" to continue.

 

4. In object type selection, click the detailed selection button, select a table not to be migrated in the list on the left, and put it in the list on the right, all the remaining tables in the list on the left will be migrated. After the selection, press "Next" to continue.

 

5. In the migration of Type Oracle schema of object mapping, if you want to set parameters, click Set parameter. If the default database table is utf8, select multilanguage. If the default database table is GBK, select user defined and enter charset = GBK, collation = gbk_general_ci below. In the migration of Type Oracle table, set the parameter. If the default database table is utf8, select Data Consistency/multilanguage. If the default database table is GBK, select user defined and enter addautoincrement = Yes below, charset = GBK, collation = gbk_general_ci, engine = InnoDB. After the selection, press "Next" to continue.

 

6. Click manual editing to modify the table creation script. Due to the differences in syntax rules between Oracle and MySQL, you usually need to adjust the script data type and default value. For example, Oracle usually sets the default value of sysdate for timestamp data, but it cannot be identified in MySQL. Select Show all objects in the filter, select the table to be modified in migrated objects, and click advanced in the lower left corner to edit the script. After the modification, click the apply Changes button on the Right To save the modification and press "Next" to continue.

 

7. Next, if no error is prompted, You can import the data in the specified table to MySQL. If an error message is displayed, return to manual editing to modify the script again.

 

You need to add that an exception may occur when importing large data volumes, especially clob data: "packets larger than max_allowed_packet are not allowed ". This is because the MySQL database has a system parameter max_allowed_packet. The default value is 1048576 (1 m). You can use the following statement to query the value in the database:

Show variables like '% max_allowed_packet % ';

To modify this parameter, find the my. ini file in the MySQL folder and add a line in the my. ini file [mysqld:

Max_allowed_packet= 16777216

Restart MySQL to import data of no more than 16 Mb. Of course, this value can be adjusted as needed.