Oracle Migration Workbench – Part Two

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

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):


[mysqld]
# turn on binary logging and slow query logging
log-bin
log-slow-queries

# 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
innodb_flush_log_at_trx_commit=1

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 [email protected]=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 password.

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=”;
flush privileges;

create database omwb;

grant create, delete, index,
insert, select, update
on omwb.*
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.

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles