Use MySQLMigrationToolkit to quickly import Oracle Data to MySQL

Use MySQL Migration Toolkit to quickly import Oracle Data to MySQL
Let's talk nonsense first.
I have recently learned some database knowledge. I have been familiar with Oracle and MySQL before, and recently I am very popular with MongoDB non-relational databases. So I will study it together and find a difference in comparative learning, first, let's talk about my database version and visualization tools.
Oracle10G-PL/SQL Route 9
MySQL5.5.29-MySQL Workbench6.0
MongoDB2.4.9 (32bit Max 2G)-robotium 0.8.4
In order to maintain data consistency, some data in the existing Oracle is transplanted to MySQL. Baidu later found that MySQL Migration Toolkit is good, and now we will write the steps to share with you
1. Install MySQL Migration Toolkit
Log on to http://dev.mysql.com/downloads/gui-tools/5.0.htmlto download MySQL GUI Tools (which is actually a MySQL management tool), including MySQL Migration Toolkit, which is installed all the way to next.
2. The ojdbc14.jar package needs to be loaded during the first running.

Run MySQL Migration Toolkit, choose "Next" to "Source Database", and select Oracle Database Server in the Database System. If you use it for the first time, you are advised to load the driver ojdbc14.jar, then restart MySQL Migration Toolkit.

3. After the driver is loaded, it will become the following form on the Source Database interface. Fill in the connection information of the Oracle Database and press "Next" to continue.

4. 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.

5. Press "Next" after passing the Connecting to Server test, go to Source Schemata Selection, click the database for data migration, and then press "Next" to continue.

6. After the Reverse Engineering test is passed, press "Next". In the Object Type Selection, click the Detailed selection button and select a table not to be migrated from the list on the left, after the table is placed 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.

7. 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.

8. After passing the Migration test, Go to Manual Editing, where you can 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.

9. In Object Creation Options, select the structure of the data table stored on the local disk and press "Next" to continue.

10. After Creating the structure of all tables through Creating Objects, there is no data in the table. Press "Next" to continue.

11. "next" all the way to Data Mapping Options, select the local disk to store the Data in the Data table, and press "Next" to continue.

12. After creating all the Data in the table through Bulk Data Transfer, press "Next" to continue.

13. The data conversion information is displayed in summary. You can save the information as a file and press "Finish.

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. Its default value is 1048576 (1 M). You can use the following statement to query its value in the database: show VARIABLES like '% max_allowed_packet % '; to modify this parameter, find my. INI file, in my. add a line in the 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.
14. use MySQL Workbench to import data
Click Data Import/Restore to first Import the table structure and then Import the table Data to complete database migration.