Today I needed to migrate a MySQL database to Oracle – with a repeatable process, as the project is currently in a development phase.
I already knew that I could use DbUnit to migrate the data, once the database schemas were migrated, so I was pleased to discover that I could use Liquibase – a project I had never heard of – to solve that problem.
I setup a ant based script to execute the tasks.
The first thing I did was to download the libraries I needed, and store then in the /lib directory of my migration project:
Before you begin, create a database property file (name it build.properties) for the databases you want to migrate from/to. db1 is the one you are migrating from, and db2, the one you want to migrate to.
db1.driver=com.mysql.jdbc.Driver db1.url=jdbc:mysql://localhost/sorucedb db1.user=user db1.password=password db2.driver=oracle.jdbc.driver.OracleDriver db2.url=jdbc:oracle:thin:@localhost:1521:targetdbsid db2.user=user db2.password=password
This ant script loads the build.properties file, and sets up the DbUnit and Liquibase tasks. We are assuming that the necessary jar files where downloaded and placed in the lib subdirectory.
<?xml version="1.0" encoding="UTF-8"?> <project name="changeme" default="all" basedir="."> <property file="${basedir}/build.properties" /> <property name="lib.dir" value="${basedir}/lib" /> <!-- Ant classpath --> <path id="tool.classpath"> <fileset dir="${lib.dir}"> <include name="*.jar"/> <include name="*.zip"/> </fileset> <pathelement location="${basedir}"/> </path> <taskdef resource="liquibasetasks.properties" classpathref="tool.classpath"/> <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask" classpathref="tool.classpath"/> <project>
The next step is to export a Liquibase changelog for the database schema you want to migrate:
<target name="export.changelog"> <generateChangeLog outputFile="${basedir}/ddl.xml" driver="${db1.driver}" url="${db1.url}" username="${db1.user}" password="${db1.password}" classpathref="tool.classpath" /> <replace file="${basedir}/ddl.xml" token="int identity" value="int"/> <replace file="${basedir}/ddl.xml" token="baseTableSchemaName='dbo' " value=""/> <replace file="${basedir}/ddl.xml" token="schemaName='dbo' " value=""/> <replace file="${basedir}/ddl.xml" token="image" value="longtext"/> </target>
When executed, it creates a file “ddl.xml”
Next I load the changelog, and generat the DDL script for the Oracle Database. The reason for doing it this way, is that I needed to update the Oracle Schema name manually in the script, as it was not the same in the source/target.
<target name="generate.targetddl"> <updateDatabase changeLogFile="${basedir}/ddl.xml" driver="${db2.driver}" url="${db2.url}" username="${db2.user}" password="${db2.password}" promptOnNonLocalDatabase="false" dropFirst="false" outputFile="${basedir}/oracle_ddl.sql" classpathref="tool.classpath"/> </target>
Note: I also removed “ON UPDATE NO ACTION;” from the end of each statement as it caused problems.
Next I used DbUnit to export the data. You will have to list all tables you need (unless you want all tables in your database), as well as take into account if you have tables without a primary key.
<target name="export.db1"> <dbunit driver="${db1.driver}" url="${db1.url}" userid="${db1.user}" password="${db1.password}"> <dbconfig> <property name="datatypeFactory" value="org.dbunit.ext.mysql.MySqlDataTypeFactory" /> </dbconfig> <export dest="${basedir}/dataexport.xml" > <table name="A_TABLENAME"/> </export> </dbunit> </target>
Note: in order to avoid foreign key problems, I exported the tables in the correct order. It might also be possible to import them with foreign key checks turned off if your database or driver supports it. For example “sessionVariables=FOREIGN_KEY_CHECKS=0” for MySQL
Finally import the data into your new database. Depending on the DbUnit Operation Type you use (or can use), you can make this a repeatable process.
<target name="import.data"> <dbunit driver="${db2.driver}" url="${db2.url}" userid="${db2.user}" password="${db2.password}" schema="${db2.user}" > <dbconfig> <property name="datatypeFactory" value="org.dbunit.ext.oracle.OracleDataTypeFactory" /> <property name="batchedStatements" value="true" /> <property name="batchSize" value="500" /> </dbconfig> <operation type="REFRESH" transaction="false" src="${basedir}/dataexport.xml"/> </dbunit> </target>
Note: This took forever for my large database at first until I realized I just needed to set a batch size so that it was not doing a commit after each insert/update
If you made it this far, you may as well follow me on LinkedIn: Follow Brian Porter