At the end of Part One, we were at the point where MySQL was ready to be
installed. In part two of this series, we will go through some
quick setup steps, create a user with some privileges, and load
data - using several methods - into a MySQL database. Once the
setup of the MySQL database is complete, we will be ready to start
using Migration Workbench.
Starting and Using MySQL
MySQL (the company) provides user documentation
in the Docs directory of where you installed MySQL (the product).
Assuming you used the default installation target (on Windows) of
C:\mysql, the HTML-based documentation, using a table of contents
similar to what you see when looking at Oracle's list of books, is
located at C:\mysql\Docs. The MySQL Tutorial
book published by MySQL Press is a condensed version of the
After installing MySQL, create a configuration
file (my.ini, located in C:\Windows) using the following
parameters (or just cut and paste what is shown below):
# turn on binary logging and slow query logging
# InnoDB config
# This is the basic config as suggested in the book
innodb_data_file_path = ibdata1:10M:autoextend
# Set buffer pool size to
# 50-80% of computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
# Set the log file size to about
# 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
# Set flush_log_at_trx_commit
# to 0 if you can afford losing
# some last transactions
Assuming you are in c:\mysql\bin, install MySQL
as a service using :
c:\mysql\bin> mysqld-max --install
Once you see that the service has been created,
you can start MySQL using the Services control panel or by issuing
the net start service_name (net start
mysql) command at the command prompt.
To connect as "root" privileged user much like
sys and system), at the command prompt enter
c:\mysql\bin> mysql -u root
Once connected (you'll see a "mysql>" prompt),
set a password for root using
mysql> set password for root@localhost=password('admin');
I used "admin" for its obviously hard to guess
and highly secure value; you can use whatever you want. Log out of
MySQL using a "\q" and then log back in using a slightly different
format (add a "-p" parameter). You will be prompted for root's
After logging in (without specifying a database
name as a parameter), you are connected, but to what? MySQL
installs with two databases: mysql and test. The MySQL database is
analogous to Oracle's data dictionary, and the test database is
like the seed database Oracle Universal Installer creates, but
test has nothing in it. To select a database for use, use "use
database_name" at the MySQL prompt.
To see what is in the MySQL database, let's
"use" it and issue a "show tables;" command as root.
Note that some commands (actually, most of
them) require a semicolon. It seems that it would be more
consistent to allow "show tables" without a semicolon given that
"use database_name" does not require it. To make things simple,
end every command (except the ones beginning with a backslash)
with a semicolon.
The next three steps will delete anonymous
accounts, create a database named "OMWB" and create a "tiger
identified by scott" user with certain privileges on the OMWB
database. Try not to be overwhelmed by the complexity of the
username and password combination I will be using in this example.
If you use the MySQL Tutorial book's example
for creating a user, some of the privileges shown in the example
grant statement (create temporary tables, lock tables, and show
databases) do not work unless you're using version 4 of MySQL. The
following code can be copied into a command prompt window (Hello
Microsoft, why can't we just call it a DOS window?):
delete from user where user='';
delete from db where user='';
create database omwb;
grant create, delete, index,
insert, select, update
to tiger identified by 'scott';
If you need (or want) to re-run the create
database statement, just add "drop database if exists OMWB;"
before the create statement.
Getting data into a MySQL
As an Oracle wizard, no doubt you are
intimately familiar with how to create tables and insert data.
There is very little difference when using MySQL. Three of the
biggest differences are:
To make a MySQL table more like Oracle,
specify the type of table at the end of a create table
statement. The type to specify is "InnoDB" and is specified
with a "type=InnoDB" clause at the end of the create table
Your choice of datatypes is quite similar,
but note that Oracle's VARCHAR2 is MySQL's VARCHAR, and that
number datatypes are slightly different. If you just want a
whole number, use "int" and for decimal type numbers, use
decimal(L,P) where L is the length and P is the precision.
MySQL's date format may cause a problem for
you as it uses a YYYY-MM-DD format. In a way, that is actually
a lot more convenient as there is no doubt as to whether or
not you are using day/month or month/day.
If you like SQL*Loader, you will like MySQL's
data loading and outputting via an INFILE and OUTFILE. Even
before getting to Oracle Migration Workbench, you have the means
to transfer data, even if it is just one table at a time.
I have taken the Scott schema and arranged it
into a MySQL suitable format. The root user will create the
tables and perform the "load data infile" to populate the "msemp"
table. The example uses the same table names but with an "ms"
placed in front (msemp, msdept, and so on). You can choose how
the data is delimited in the infile (comma separated or tab
delimited, for example). I used tab delimited to make the data
easier to read (plus that is the default), but the CSV version
works just as well.
You can create the comma separated version of
the infile by using
||comm||','||deptno "MySQL data load example"
The tab delimited version can be created in
same manner, or you can use the following (I cut off the some
letters so the columns line up nicely):
The command syntax to load data using an
load data infile 'c:\\mysql\\load_msemp_table.txt'
into table msemp
ignore 3 lines;
The "ignore 3 lines;" takes care of the two
comment lines and the blank line before the data.
Data from the scott.emp table used in
the MySQL tiger.msemp table example
7369 SMITH CLERK 7902 1980-12-17 800 NULL 20
7499 ALLEN SALESMA 7698 1981-02-20 1600 300 30
7521 WARD SALESMA 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 NULL 20
7654 MARTIN SALESMA 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450 NULL 10
7788 SCOTT ANALYST 7566 1987-07-13 3000 NULL 20
7839 KING PRESIDE NULL 1981-11-17 5000 NULL 10
7844 TURNER SALESMA 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 1100 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950 NULL 30
7902 FORD ANALYST 7566 1981-12-03 3000 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300 NULL 10
What follows are the commands to create the
tables and use the infile to load the msemp table. Create a data
infile and reference where shown below, using the backslash to
escape the Windows directory path backslash separator.
create table msdept
deptno decimal(2) not null primary key,
create table msemp
empno int not null auto_increment primary key,
hiredate date not null,
deptno int not null references msdept(deptno)
create table msbonus
create table mssalgrade
load data infile 'c:\\mysql\\load_msemp_table.txt'
into table msemp
ignore 3 lines;
The end result in MySQL, using "show tables;"
and "select * from msemp;" is:
Connect as tiger and load the remaining
insert into msdept values
insert into mssalgrade values (1,700,1200);
insert into mssalgrade values (2,1201,1400);
insert into mssalgrade values (3,1401,2000);
insert into mssalgrade values (4,2001,3000);
insert into mssalgrade values (5,3001,9999);
If you want to see MySQL's "interpretation"
of the OMWB database schema, use the following at a DOS prompt
and then view the resulting file:
c:\mysql\bin> mysqldump -u root -padmin --opt omwb > omwb_dumpfile.sql
This should remind you of how you can do the
same thing using Oracle's export utility. This concludes the
setup phase of MySQL. Take note of the fact that Migration
Workbench connects to your MySQL database as the user "root." In
a way, the MySQL user named tiger wasn't necessary, but creating
him parallels the privileged user versus schema owner concept in
Before leaving MySQL, issue a "\s" at the
MySQL prompt and note the port number shown (TCP port of 3306
shown below). Migration Workbench will use that port number to
establish a connection between your Oracle and MySQL databases.
Starting Oracle Migration Workbench
First of all, become familiar with the MySQL
reference guide that installs with OMWB. You will find this in
<base directory where you installed it>\Omwb\docs\mysql\toc.htm,
or you can look for this at OTN:
Release 10.1.0 supports 4.x, but elsewhere
(e.g., the survey you fill out before downloading OMWB), Oracle
says Migration Workbench does not support 4.x (4.0 and 4.1).
That's why we're using a 3.23 release for this series.
It appears that the MySQL datatypes used in the
OMWB database will successfully translate to Oracle. MySQL integer
(INT) data translates to Oracle Number(10,0), which may be a
consideration when using large numbers for identification/ID
Prior to using Migration Workbench (we will be
using the "Oracle" option), create a user in your Oracle target
database with at least the CONNECT and RESOURCE privileges. In my
database, I created tiger/scott.
Elsewhere in Oracle's documentation, you are
directed to grant a laundry list of privileges to your user. Cut
and paste the code below (using "tiger"). With all of those
privileges, why not just grant DBA to this user? About the only
thing this user cannot perform is import/export full database.
GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE,
ALTER TABLESPACE, ALTER ANY TRIGGER, COMMENT ANY TABLE,
CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY TRIGGER,
CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY SEQUENCE,
DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER,
DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE,
UPDATE ANY TABLE TO tiger;
Start Migration Workbench using the OMWB MS-DOS
batch file found in (for example) c:\omwb\bin and enter your
Select Yes to create the repository.
If you do not already have the plugin, you will
need to download two items. The first is Oracle's plugin for 3.23,
and the other is a JDBC-related file from MySQL (the links for
both are on the Migration Workbench download page). If you do not
have one or more plugins installed, you will see the message shown
Unzip or extract the file from MySQL (named
mysql-connector-java-3.0.15-ga). Navigate to the
mysql-connector-java-3.0.15-ga directory and send the com folder
to a zipped file name mysql-connector-java. Then (it's almost
over), copy or move that zipped file to your OMWB drivers
After placing the MySQL jar file in the OMWB
plugins directory, and copying the zipped mysql-connector-java
file to the drivers directory, the next time you start OMWB, you
If your plugin installation was successful, you
will be directed to a Capture Wizard window.
Enter the password for root. I used localhost
for my server, confirmed the 3306 port number from before.
Select the OMWB, (or whatever you named it),
database. Note that the MySQL database does not appear in the list
of available databases.
Accept the data mappings as shown.
Select Yes and click Next.
Confirm your settings and click Finish.
If all goes well, you will see numerous
information messages pass by and a final results window.
Click OK to dismiss the results window, and OK
to dismiss the status window. Then click Yes to create the
The next window is a bit confusing (it looks
like you are starting over), but the subsequent windows now deal
with the Oracle database.
Instead of tiger (to ensure sufficient
privileges), I logged in using the system account.
The wizard detects this is the first time, so
the database will be configured.
Select Yes, that's why we're doing this in the
Everything is already selected, and that is
what we will accept.
Confirm and click Finish.
There is one error and one warning. What
We will investigate the error and warning in
the next article. Did the MySQL OMWB database and its data get
migrated? Let's see.
The migration process took care of the
different DATA data type format. The migrated tables belong to an
Oracle user named root. Change root's password to something you
know and connect as root and view the data in the migrated tables.
What happened to Tiger? Tiger became the
repository owner, not the schema owner. More on that in the next
This part of the Migration Workbench series has
given you enough information to create a MySQL database, import
and insert data into it, and migrate it to an Oracle database.
That is not a bad first attempt at using this tool, plus you now
know enough to find your way around a MySQL database. In the next
part, I will go into more detail about what is available in the
Oracle Migration Workbench console and start the groundwork for
using SQL Server in the same manner as we did with MySQL.