How to Copy Data from Oracle to MySQL

This example shows how to copy a table from Oracle to MySQL. First create a table in Oracle and add some data, including some null values and a date column:

 

SQL> create table andrews_table

  2  (first_name varchar2(10),

  3   dob        date,

  4   salary     number)

  5  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Fred', '25-DEC-1980', null)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  ('Jim', null, 10000)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  (null, '01-SEP-1983', 20000)

  3  /

 

1 row created.

 

SQL> select * from andrews_table

  2  /

 

FIRST_NAME DOB           SALARY

---------- --------- ----------

Fred       25-DEC-80

Jim                       10000

           01-SEP-83      20000

 

SQL>

 

Write a query to extract the data to a file. Note the date format and the way that null values are represented as \N:

 

UNIX > cat extract.sql

set feedback off

set lines 100

set pages 0

set termout off

set trimspool on

spool extract

select nvl(first_name,'\N')||'|'||

       nvl(to_char(dob,'YYYY-MM-DD'),'\N')||'|'||

       nvl(to_char(salary),'\N')

from andrews_table

/

spool off

UNIX >

 

... and run the SQL:

 

SQL> @extract

SQL>

 

The output file will look like this:

 

UNIX > cat extract.lst

Fred|1980-12-25|\N

Jim|\N|10000

\N|1983-09-01|20000

UNIX >

 

Copy it to a machine running MySQL then you can load it into a table as follows:

 

--------------

create table andrews_table

(first_name varchar(10),

dob        date,

salary     int)

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

load data local infile 'extract.lst'

into table andrews_table

fields terminated by '|'

lines terminated by '\n'

(first_name, dob, salary)

--------------

 

Query OK, 3 rows affected (0.00 sec)

Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

 

--------------

select * from andrews_table

--------------

 

+------------+------------+--------+

| first_name | dob        | salary |

+------------+------------+--------+

| Fred       | 1980-12-25 |   NULL |

| Jim        | NULL       |  10000 |

| NULL       | 1983-09-01 |  20000 |

+------------+------------+--------+

3 rows in set (0.00 sec)

 

mysql>

MySQL db.opt File

When you create a database, you have to specify options such as the character set it will use:

 

mysql> create database andrew default charset utf8;

Query OK, 1 row affected (0.01 sec)

 

mysql>

 

These options are recorded in the db.opt file in the database directory:

 

UNIX > pwd

/usr/local/mysql/data/andrew

UNIX > cat db.opt

default-character-set=utf8

default-collation=utf8_general_ci

UNIX >

 

You can change them with the alter database command:

 

mysql> alter database andrew charset = dec8;

Query OK, 1 row affected (0.05 sec)

 

mysql>

 

... and the db.opt file will be updated:

 

UNIX > pwd

/usr/local/mysql/data/andrew

UNIX > cat db.opt

default-character-set=dec8

default-collation=dec8_swedish_ci

UNIX >

MySQL verbose Option

The verbose option tells MySQL to display SQL before running it. This is useful when using the source command to run SQL from a file. You can see what I mean in the example which follows. Here is a file containing a single SQL statement:

 

UNIX > cat count_star.sql

select count(*) from mysql.user;

UNIX >

 

When you run it from MySQL with the verbose (-v) option, the SQL appears before the output it produces:

 

UNIX > mysql -u root -p -v

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53444

Server version: 5.0.67 Source distribution

 

Reading history-file /usr/local/mysql/.mysql_history

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> source count_star.sql

--------------

select count(*) from mysql.user

--------------

 

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

mysql> exit

Writing history-file /usr/local/mysql/.mysql_history

Bye

UNIX >

Nested source Command

In an earlier post, I showed how you can run SQL commands by storing them in a file and running it with the source command. You can run nested source commands too, as shown below. Here are 3 SQL files:

 

UNIX > cat nested_source.sql

use test;

source count_star.sql

source select_database.sql

UNIX > cat count_star.sql

select count(*) from mysql.user;

UNIX > cat select_database.sql

select database();

UNIX >

 

So if you use source on nested_source.sql, it will run the SQL contained in the files count_star.sql and select_database.sql. You can see what I mean in the example below:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53432

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> source nested_source.sql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

+------------+

| database() |

+------------+

| test       |

+------------+

1 row in set (0.00 sec)

 

mysql>

MySQL source Command

The source command runs SQL commands from an operating system file. I created the file below:

 

UNIX > cat my_first_script

create database fred;

select database();

use fred;

select database();

create table freds_table

(col1 varchar(10));

desc freds_table;

drop table freds_table;

show tables;

drop database fred;

UNIX >

 

... and it produced the following output when I ran it from within MySQL:

 

mysql> source my_first_script

Query OK, 1 row affected (0.00 sec)

 

+------------+

| database() |

+------------+

| NULL       |

+------------+

1 row in set (0.00 sec)

 

Database changed

+------------+

| database() |

+------------+

| fred       |

+------------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col1  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Empty set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql>

FILE Privilege

If you create a user called andrew:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 45881

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> create user 'andrew'@'localhost'

    -> identified by 'secret_password';

Query OK, 0 rows affected (0.00 sec)

 

mysql>

 

... and that user creates a table:

 

UNIX > mysql -u andrew -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 45883

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into andrews_table

    -> values ('Noddy');

Query OK, 1 row affected (0.00 sec)

 

mysql>

 

... then tries to output its contents into a file, it fails:

 

mysql> select * from andrews_table

    -> into outfile 'contents';

ERROR 1045 (28000): Access denied for user 'andrew'@'localhost' (using password: YES)

mysql>

 

To fix this, you need to grant the file privilege to the user:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 45889

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> grant file on *.* to andrew@localhost;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

 

... then when the user tries again it works:

 

UNIX > mysql -u andrew -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 45890

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> select * from andrews_table

    -> into outfile 'contents';

Query OK, 1 row affected (0.00 sec)

 

mysql> exit

Bye

UNIX > 

 

and produces a file like this:

 

UNIX > cat contents

Noddy

UNIX >