Oracle Migration Workbench - Part Two
November 10, 2004
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 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 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.