Note on Mar 15, 2010…
This article was actually written around 2006 timeframe. Some of the comments regarding missing features in MySQL are no longer valid in 2010. MySQL 5.1 is very robust and feature rich, matching Oracle in many different areas including datatypes, stored procedures and functions, high availability solutions, ACID compliance and MVCC, hotbackups, cold backups and dumps, full text and other index options, materialized views and much more. To generalize, Oracle remains ahead in terms of it’s cost based optimizer, and complex query optimization, as well as high concurrency, and utilizing large SMP boxes. It also remains ahead in areas of clustering. However by far the biggest difference between the two technologies is cultural. MySQL rooted in the open source tradition is much more do-it-yourself and roll your own, while Oracle provides many named and proven paths to solve specific problems.
INTRODUCTION
MySQL is a powerful database platform for many simple web-based applications. Some of it’s power and speed comes from it’s simplicity. MySQL is actually a database without proper transactions. What this means in terms of speed is dramatic. However it also means you cannot rollback an update which encounters problems halfway through, and other sessions in the database will immediately see changes. There are many dramatic ramifications of this, as we’ll discuss later. Lastly there are limitations on dataset size. Oracle can obviously handle tables of a terabyte and larger. However since MySQL implements a table as one file, filesize limits as well as internal data organization, and indexing can become major limitations as tables grow to the millions of rows and higher.
When you begin to hit these limitations, whether in your application complexity, or the size of your data, you may consider moving your data and application to the Oracle Database. There you will have a rich set of features both on the programming side with stored procedures, views, materialized views, triggers, and so on. You will also have support for tables and indexes of virtually limitless size, full transaction support, and even sophisticated High Availability features such as Advanced Replication, Data Guard, and even clustering with Oracle’s Real Application Clusters technology.
With all these enticing features, and robustness, you’re eager to jump into Oracle. But don’t move so fast. There is a temendous amount of planning involved with both moving your data, and porting and testing your application on the new platform. Add to that Oracle licensing, and you’ll need some time to get there.
MySQL vs Oracle – feature comparisons
MySQL is a database fit for some types of applications. These tend to be smaller applications, or those which integrate applications with less sophisticated needs than those running Oracle on the backend.
It makes sense at this point to go through a feature comparison, and see what features MySQL shares with Oracle.
MySQL shares with Oracle good support for database access including ODBC and JDBC drivers, as well as access libraries for Perl, Python and PHP. MySQL and Oracle both support binary large objects, character, numeric, and date datatypes. They both have primary and unique keys, and as of 4.x with InnoDB, MySQL has foreign keys, and transactions including READ UNCOMMITED, READ COMMITED, REPEATABLE READ, and SERIALIZABLE. Both databases have sophisticated language and character set support. MySQL can do table locking, and recently improved to include row-level locking. What’s more if you don’t need transactions, MyISAM tables are extremely fast. MySQL also includes a good data dump utility which you’ll see in action below when we migrate to Oracle. And lastly both databases of course include good b-tree indexes, which no database could be without.
There are, however, quite a number of features we find in Oracle as standard, which remain missing in MySQL. Until recently that included row-level locking, true transactions, and subqueries although as of 4.x those seem to be present. However, those have been present, and core technologies in Oracle for years, with very stable and solid implementation, you’re sure to achieve dramatic performance on tpc benchmarks. Views are still absent in MySQL, though they may be around the corner with subqueries available now.
Of course a lot of the high end Oracle features remain completely absent from MySQL, and may never be added. Features such as parallel query, and partitioned tables, which include a whole host of special features such as the ability to take one partition offline without impacting queries on the rest of the table. The indexing on partition tables is sophisticated too, allowing partition elimination, and range scans on indexes of specific partitions. There are other large database features such as special functions for star queries. Oracle has terabyte databases in production, so this fact speaks for itself.
MySQL still has a somewhat limited set of index types. For instance Oracle has reverse key, bitmap, and function based indexes, as well as index organized tables. These are all very powerful features for developers who are trying squeeze that last bit of performance out of complex SQL queries against large tables. Although MySQL does provide some index statistic collection, Oracle provides the full set of statistics, including histograms, and makes great use of it inside the Cost Based Optimizer. These statistics allow Oracle to better determine the best method of getting the data for your query and putting it together for you with the least use of resources in tems of memory cache, and disk access. This is really key for a database. When running large multi-user applications all of which are vying for data in different tables, you want to load just the data you need, and nothing more. Avoiding full table scans by
using the proper index, and using various indexes in a join operation to pull fewer rows from the underlying tables means less disk I/O, which other processes are fighting for, and less use of cache, leaving more for other processes.
MySQL still does not have privilege groups, called ROLES in Oracle.
Oracle can also provide column level
MySQL does not have hotbackups which have been an integral part of Oracle for years. In addition, Oracle’s RMAN has become a sophisticated piece of software, and grown to be very stable, providing block level backups so only the data that changed can be included in subsequent backups. This makes nightly backups
smaller overall. It also aids tremendously during recovery, providing a lot of automation, and assistence, during those times when you need it most. MySQL’s method is to dump data, and further if you want to guarentee a point in time dump of your data, you have to lock all the tables in the database, potentially slowing down your application tremendously. Lastly MySQL does not have automatic or point in time recovery, a real limitation on heavy use production databases.
MySQL also has some limitations on row sizes. MyISAM tables for instance, can have a maximum 64k of data per row, and InnoDB tables 8k per row. This does not include BLOB and TEXT types.
MySQL does not include database links such as those found in Oracle allowing you to query a table from an external database inside a query in a local database. This can be useful for moving data between databases, and is key to implementing
advanced replication in Oracle.
Lastly, MySQL has had some database size limitations. In 3.22 it could only access 4GB of data, however, as of 3.23 the only limitation has been with your operating system, and the size of files it can handle. On Linux with LFS or RaiserFS, this limitation is effectively eliminated. However, Oracle still has incredibly sophisticated
storage cababilities, allowing virtually unlimited datafiles to be associated with a tablespace, and thus virtually limitless sized tables.
Migration preparation
So you’ve seen what you can do with Oracle, and management has invested in licensing, and you’re now ready to get things setup in your development environment.
Now is the time to really get up to speed with Oracle. This goes for both Database Administration knowledge, as well as developer and programmer knowledge. The latter requires that you know a lot about Oracle’s features, in particular those which are relevant to your application. The former requires you understanding DBA roles, managing database files, memory structures, backups, and so on and so forth.
Thomas Kyte’s books are really excellent, and highly recommended. Check out “Expert One on One” on Wrox Press, and his newer title, “Effective Oracle by Design” which is on Oracle Press. He also has a website, http://asktom.oracle.com.
Also check out Kevin Loney + Marlene Therault’s DBA Handbook on Oracle Press. Of course don’t forget to read the Oracle docs, which are quite good. Start with the concepts manual for the version of Oracle you plan to go with.
In planning a migration the first thing you should do is take a look at the number, and types of tables in your database. Do that in MySQL as follows:
SQL> show table status
+——+——–+———+————+
| Name | Engine | Version | Row_format |
+——+——–+———+————+
| t | InnoDB | 9 | Fixed |
| u | MyISAM | 9 | Fixed |
+——+——–+———+————+
2 rows in set (0.05 sec)
This output is truncated, but serves as a useful example. You will see the tables, types, and a lot of other information about the tables you will be moving.
You’ll next want to review the datatypes of your various tables. CHAR in MySQL maps to CHAR in Oracle, VARCHAR to VARCHAR2, and the various Large Object types to RAW or BLOB in Oracle. DATE, DATETIME, and TIME map to Oracle’s DATE datatype, while TIMESTAMP and YEAR map to NUMBER. Lastly all of the various INT datatypes in MySQL map to NUMBER in Oracle and FLOAT, DOUBLE, REAL, and DECIMAL all map to FLOAT.
To get information about the you can use the ‘describe’ SQL command much like Oracle’s own describe:
mysql> describe t;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| s | char(60) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
Another way to get useful descriptions of tables is to use the mysqldump command. Here ‘test’ is the name of the database, and ‘t’ is the name of the table. Leave the table name off and the output will include all the tables in that database.
$ mysqldump test t
--
-- Table structure for table `t`
--
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int(11) NOT NULL auto_increment,
`s` char(60) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
There’s actually quite a bit more output, and depending on the version of MySQL you may see additional comment lines etc. You’ll probably want to redirect the output to a file. Do so as follows:
$ mysqldump test t > t_table.mysql
$ mysqldump test > test_db.mysql
You will also want to get a sense of which columns are indexed to be sure that they get indexed in your Oracle database. Here is an example of how to list the indexes on a table:
mysql> show index from t;
+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+
| t | 0 | PRIMARY | 1 | id |
+-------+------------+----------+--------------+-------------+
1 row in set (0.04 sec)
An enumerated datatype is one where you define a static set of values up front. Oracle does not currently have such a datatype. The closest equivalent is a VARCHAR2 of sufficient size to hold all the various types. Migration Workbench will
do just that. If you want Oracle to enforce your set of values add a check constraint on that column.
Lastly, if you’re experiencing serious performance problems in MySQL, take a look at the slow query log. MySQL can be configured to dump queries which do not execute within a certain number of seconds, to a log file for your review. You can then use the EXPLAIN facility, a much simplified version of the facility found in Oracle, to tune queries for better execution path, possibly requiring a new index on a certain column or an index rebuild. In many instances restructuring a query can be of substantial
benefit.
What’s more many of these skills of tuning and optimizing queries will translate directly to Oracle. Queries are the lifeblood of your application. Bad ones can kill overall database performance by filling the cache with useless blocks of data and pushing out previously cached, and important data. What’s more inefficient queries cause more disk I/O which impacts the overall performance of your database. These issues hold true for all databases, and getting proficient with them now will
bring you up to speed faster with Oracle as you migrate.
Moving your data between MySQL and Oracle
At this point we’re still presuming that you will be moving your data by hand. This is not because we are gluttons for punishment. It is more to illustrate important points. Doing things by hand goes over each and every detail of the process so you understand it. You’ll need to when things go wrong, as they inevitably will. So we’re discussing moving the schema, and then the data by hand for all tables, however you may end up following the instructions below for using the Oracle Migration Workbench, and then only doing one or two special tables by hand. Or you may decide to use Migration Workbench to build scripts for you as a starting point, and then agressively edit those for your particular needs. All these options are valid.
So at this point you need to dump data. If you want to load data with Oracle’s SQL*Loader, an easy format to start with is CSV or Comma Separated Values file.
To dump one table named ‘t’ from database named ‘test’ use this bit of code. Note that we’ve broken things up into multiple lines to easily illustrate what’s happening with all those messy SED commands. You’re welcome to modify them for your needs but this works as-is. Note that ^V requires you to type ctrl-V and requires you to type ^I ctrl-I. Read your editor manual for details on inserting control characters into a file.
#!/bin/bash
# 1. get all rows from table t of database test
# 2. add double quote at beginning
# 3. replace tabs with ","
# 4. add double quote at end
# 5. remove header line
echo 'select * from t;' | mysql test \
| sed -e 's/^/\"/' \
| sed -e 's/^V^I/\"\,\"/' \
| sed -e 's/$/\"/' \
| tail -n +2
Now is your chance to really put all those Oracle skills to work. You want to have CREATE TABLE statements to build each table, and scripts are an excellent way to get you going. They also self-document the process.
Here is an example of how to precreate the above very simple table in Oracle. Edit a file called t.sql and include these lines:
create table T (
id NUMBER(10,0) primary key,
s CHAR(60));
Save the file, and then fire up sqlplus and do:
SQL> @t.sql
Table created.
SQL> desc t;
Name Null? Type
------------------------------------ -------- -----------------------
ID NOT NULL NUMBER(10)
S CHAR(60)
Now use SQL*Loader to load the CSV data you created earlier. To do that you’ll need to create a control file. This tells SQL*Loader exactly what to do. Edit a file t.ctl and put this in the file:
LOAD DATA
REPLACE
INTO TABLE t
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\""
TRAILING NULLCOLS
(id INTEGER EXTERNAL, s CHAR)
Once you’re done, save the file, and execute the following:
$ sqlldr your_username control=t.ctl data=t.dat log=t.log bad=t.bad
This should load your data into the table t that you created earlier. Check the log and bad files for details, and errors.
As with the Oracle Migration documentation, and any good documentation really, we’ll emphasize and reemphasize the need and importance of testing. You cannot assume that your script does things right, nor can you assume that the script Oracle’s Migration Workbench created will do things right. There are an infinite number of anomalies for any dataset, and testing your application is the only way you
can verify you are in good shape.
What’s more you also need to verify that your data is correct. Suppose you have a banking application, and you are moving customer data from MySQL to Oracle. Suppose further you have records of monthly deposits and withdrawls against that account. You move the data from MySQL into Oracle, and the web or client based frontend is up and running again, after extensive porting, and testing. Does this guarentee that all the data is correct? Not at all. It means the right fields are in the right places, and probably the datatypes are correct. Imagine that that customer had a very high balance, and when moving to Oracle the field size was too small, and perhaps when the data was loaded, the inserted value failed, or was set to a default value like 0. Obviously you don’t want to find out when that customer comes calling. You want to look through the log files when the data is loaded, and then run some verification software against the db to compare values in the old db and the new db, or to calculate checksums such as running through deposits and withdrawls to make sure the current balances check out. This is really the most important step in the process and can’t be overstated.
Migration Workbench is Oracle’s recommended solution
Oracle’s Migration Workbench is a Java-based GUI tool which runs on various Windows versions, and comes complete with wizards to help you perform your migration. The process is divided into three steps.
First you capture your target database. This is done with a series of plugins which support various databases including of course MySQL. One plugin is available to handle 3.22 and 3.23 of MySQL and another one handles 4.x versions of MySQL. Capturing the source database is the same as the process we describe above manually of looking at your tables in mysql, and the columns, and indexes you are using. This is practical and feasible for a small number of tables, however, with hundreds or even thousands of tables, Oracle Migration Workbench becomes more and more of a
necessity.
Second, you migrate the source model to Oracle. This is the process where the Migration Workbench precreates all tables found in the source database, including columns of equivalent datatypes. We describe mappings of MySQL to Oracle datatypes above. Note that Oracle does not have ENUM or an enumerated datatype per se, but it can still migrate this data, and does so to VARCHAR2 in Oracle.
The third and last step is the review the scripts that the Migration Workbench has created, make any changes or modifications and then run them to move your data from your source MySQL database into your new Oracle database.
One thing that is important to remember about a migration is that it will take a lot more time, and end up being a lot more complicated than you expected. I liked this about the documentation. They make it clear from the beginning that planning will be a tremendous help to you in estimating time, and delivering successfully within budget. The documentation is also very thorough in it’s coverage of MySQL datatypes, and how they translate to Oracle datatypes, as described earlier in this article. And of course there is a strong emphasis on testing. The Migration Workbench provides customizable scripts which both document actions to be performed and provide a way for you to get your fingers into the works.
Keep in mind while using the Migration Workbench that it is NOT all or nothing. You can use the Migration Workbench, and then edit the scripts to leave certain tables alone, or you can migrate them all, then drop the few you want to do by hand using the methods we describe above. Ultimately a mix of the two will probably serve your needs best, as there is always some amount of manual intervention you want to perform for certain tables.
A migration between two databases is not a trivial undertaking. You have a lot of data, and an application which rely on it all being in the right format, with the right relationships. Moving to a new database, with a larger feature set, slightly different syntax, and different ways of doing things takes time and attention, but in the end you’ll be up and running on a sophisticated, scalable, world class database platform.
Oracle has a great set of resources on OTN devoted to migrating to
Oracle. In particular there is the Migration Technology Center
Oracle’s Migration Workbench documentation page
MySQL (3.22, 3.23, 4.x) Reference Guide
