Migrate Web system from Oracle to MySQL

Two days ago, the leader gave a live migration to the database. The original system was using the Oracle database and now it was going to be changed to MySql. At that time, I did not think much about it. The original system used the Frame Type SSI, so I thought about the following adjustments:

First database migration

Second application modification

The following describes the process in detail.

1. database migration

Simply put, it is to migrate all the data in the oracle database to MySql. This can be done using MySQLMigration Toolkit.

MySQL released a Toolkit for porting data from SQL Server or Oracle to MySQL. This toolkit allows you to easily import existing data to a MySQL database through the wizard interface, which can be quickly transplanted, including table structures and views in various databases to reduce risks. It uses proven porting policies to save costs, the integrated toolkit greatly improves efficiency and reduces the daily maintenance workload of Oracle-to-MySQL.

After MySQLMigration Toolkit is used for transplantation, the field type of the table changes (from Oracle to Mysql): VARCHAR2 → VARCHAR, DATE → DATETIME, TIMESTAMP → DATETIME, NUMBER → DECIMAL, INTEGER → DECIMAL (22, 0 ).

In the migration operation MigratedObjects step, the row size too large error is reported. Select the problematic table, click Advanced in the lower left corner, and change the column type of varchar (4000) to TEXT.

The primary key and foreign key of the table are normal. Check the table. The table index (Oraclede bitmap index) is converted into a BTree index. Comments of tables and fields are lost at the same time.

Note: during the migration, I only migrated tables and data. Some views, triggers, functions, stored procedures, and so on were manually performed.

The procedure is as follows:

1. Install MySQLMigration Toolkit. I will not introduce it in detail. There are a bunch of tools on the net. After installation is complete, I encountered the following error when opening MySQLMigration Toolkit:

Migration Toolkit an error occured during the initialation of the runtimes system. please make sure ........;

There are a lot of solutions on the Internet, but I can't try it. After a long time, I finally found a solution. The specific operation is as follows:

Create MySQLMigrationTool. cmd in the directory of mysqlmigrationtool.exe.

The edited content is

MySQLMigrationTool.exe-jvm "D: \ Program Files (x86) \ Java \ jdk1.7.0 _ 01 \ jre \ bin \ client \ jvm. dll"

Use the actual path on your machine for jvm. dll and start it directly with MySQLMigrationTool. cmd. The problem is solved.

2. migrate the database

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.

Note that the parameters must be set during Step 5. If the parameters are not set at the beginning, the data structure can be imported successfully, however, the data in many tables is not included or a lot of data is missing.

Of type Oracle Schema, Migration of type Oracle Table parameter is set to UTF-8 after no problem, the specific reason is not clear

3

Manual migration functions, views, stored procedures, and so on. If there are multiple users, they also need to be migrated. It is difficult to describe them in detail here. Find the specific operation courses online

Second

Program Adjustment

The migration of the platform is troublesome, because our framework is ssi, which is similar to a lot of SQL. xml. This is not detailed,

Note:

1

Paging modification 2 modifying the function in SQL, such as to_char. This is not difficult to modify. It requires a lot of tests.

Conclusion: the migration of the platform is not difficult, difficult, and careful. The most important task is testing.