Category Archives: Database Migrations

Oracle DBAs… You Know You Want MySQL!

If you’re an Oracle DBA or developer and considering migrating an application over to MySQL you probably have a lot of questions. Here’s a five minute summary of what you should know.

Looking to hire a top flight MySQL DBA? Check out our MySQL interview guide.

What is truly delicious

o a command line that’s decades ahead of SQL*Plus
o fast & easy install via yum or apt-get package managers
- saves hassles, headaches, and standarded on linux distribution
o a very active open-source community with tons of roll-your-own solutions
o myriad of replication topologies with endless use cases

Flashback technology

Although MySQL doesn’t provide this by default, a slave set to lag behind by an hour or more can serve this function quite nicely. Perfect DIY solution.

Auditing

Out of the box, MySQL doesn’t have great auditing facilities. However the binary log can be of great assistance here. Use the mysqlbinlog tool to get human readable data and filter for the information you need with your favorite Unix tools such as sed, awk, sort and so forth.

MySQL also includes a general query log which may also be useful for such auditing jobs.

Users & Security

Logins are authenticated by hostname & username. So user@localhost has a distinct set of permissions from user@remoteserver. This can be very confusing so make every effort to create users & grants consistently. Also use –skip-name-resolve option to avoid DNS lookups. These can easily cause scalability problems.

Stored procedures

They’re there in MySQL, but not robust. Use sparingly. They can easily break replication, and cause serious performance and scalability problems. They also make your deployments more complicated – for example mysqldump requires the –routines flag to capture them.

SQL Queries & the cost based optimizer

MySQL uses a cost based optimizer like Oracle’s however it is much less mature. Don’t expect the same level of smarts. Some queries will need special attention.

o there’s no hash join, only nested loops
o you can use one index per table – no index merging
o correlated subqueries with IN() lists don’t optimize well
o not much control over stats collection & freshness

Partitioning

You can partition large tables by range, key or hash. However managing to get your queries just right to do partition pruning may be a trick. What’s more all the added operational benefits you get in Oracle may be limited. You can drop partitions quickly, however reorganizing them will be painfully slow.

Materialized views

MySQL doesn’t offer them per se, but check out Justin Swanhart’s Flexviews for a solution that may give you what you need.

Replication

Oracle standby database is a change based technology. Although MySQL is beginning to experiment with this using row-based replication, the vast majority of deployments are statement based.

Since slaves are hot or read-only, they can be used to horizontally scale your applications read capacity, serve as live backup, makeshift flashback or a logging apply server. Here’s how you can setup replication without downtime using hotbackups.

Be vigilant about performing checksums against your slave instances. This will alert you to any drift or other inconsistencies across your tables or data. We wrote a howto bulletproof replication with checksums.

Clustering

Don’t expect to find something like Oracle RAC. You won’t. For any typical web-facing database needs, MySQL NDB Cluster is most likely not what you’re looking for.

Consider MySQL master-master replication where one is active and one is passive. Configured as circular replication, all changes propagate to all nodes. It’s crucial you only ever write to one node, however. Use the inactive node to do online schema changes, test upgrades or other availability sensitive operations.

Lastly you may try DRBD which is a Linux filesystem level solution. Keep in mind your inactive database will be cold. You won’t experience any replication consistency issues, but upon promotion to master, you may experience slow performance until the caches are warmed up.

A few miscellaneous surprises

o many ALTER functions are blocking operations, so can’t be done online
o replication can be troublesome to manage & drift out of sync silently
o default MyISAM tables can corrupt easily and lose data – use Innodb instead
o complex many table joins & certain subqueries do not perform well
o there is a lot less instrumentation around the optimizer & db engine

Read this far, grab our newsletter!

Oracle Announces Paid MySQL Add-ons

 Oracle starts charging for MySQL Add-ons

Exciting news, Oracle just announced commercial MySQL extensions that they’ll be offering paid extensions to the core MySQL free product.

To be sure, this has raised waves of concern among the community, but on the whole I suspect it will be a good thing for MySQL.  This brings more commercial addons to the table, which only increases the options for customers.  Many will continue to use the core database product only, and avoid license hassles while others will surely embark on a hybrid approach if it solves their everyday business problems. Continue reading

3 Biggest MySQL Migration Surprises

3 ways your MySQL migration project can shake you up

Once a development or operations team gets over the hurdle of open-source, and start to feel comfortable with the way software works outside of the enterprise world, they will likely start to settle in and feel comfortable.  Best not to get too cushy though for there are more surprises hiding around the corner.  Here are a few of the biggest ones. Continue reading

The New Commodity Hardware Craze aka Cloud Computing

Does anyone remember 15 years ago when the dot-com boom was just starting?  A lot of companies were running on Sun.  Sun was the best hardware you could buy for the price.  It was reliable and a lot of engineers had experience with the operating system, SunOS a flavor of Unix.

Yet suddenly companies were switching to cheap crappy hardware.  The stuff failed more often, had lower quality control, and cheaper and slower buses.  Despite all of that, cutting edge firms and startups were moving to commodity hardware in droves.  Why was it so? Continue reading

7 Ways to Troubleshoot MySQL

MySQL databases are great work horses of the internet.  They back tons of modern websites, from blogs and checkout carts, to huge sites like Facebook.  But these technologies don’t run themselves.  When you’re faced with a system that is slowing down, you’ll need the right tools to diagnose and troubleshoot the problem.  MySQL has a huge community following and that means scores of great tools for your toolbox. Here are 7 ways to troubleshoot MySQL. Continue reading

Migration to MySQL – What is it and why is it important?

MySQL is a relational database that backs many internet websites and enterprise applications.  Like all enterprise software, it has a whole complement of features which are well documented, such as data types, storage engines, transactional behaviors and so forth.  It also has a set of processes, many of which involve how software operates on Linux servers, such as how it gets installed, where binaries and libraries will get placed, where to find logfiles, and how to move directories and set permissions.  Thirdly it is important to understand the culture, in this case Unix-based, forum discussions and community contributions as an open-source project.

MySQL can do much of the workhorse kind of stuff you see in databases like Oracle or SQL Server, but sometimes it achieves those goals in very different ways.  For instance there are many open-source projects that support and surround the database, such as mysqltuner an analysis script, innotop a unix top-like utility for monitoring on-going activity in the database, and maatkit a whole suite of tools that build on and expand the features already present in the MySQL database.

Some Limitations in MySQL

  1. Complex queries and subqueries specifically can be problematic in MySQL.  If you’re used to writing huge queries in Oracle, and having the CBO figure everything out for you, you’ll be in for a surprise with MySQL.  Keep your queries simple, proper columns indexed and avoid complex joins where possible.  The EXPLAIN facility is available to you and at your disposal.  Use it!
  2. Vertical Scalability problems – primarily addressed in 5.5, the latest version of MySQL, previously the database did not scale well on greater than four processor boxes.  SMP or Symmetric Multiprocessing servers were less common 10-15 years ago when MySQL was in it’s infancy, and development is slowly catching up with the big iron of today.
  3. There is no flashback table, tablespace or database that you might find in other databases such as Oracle.  You can achieve the same thing with point-in-time recovery, so keep regular backups of your database, and also backup the transaction logs.
  4. MySQL can do JOINs, but only with the nested loops algorithm.  It can’t do sort merge join or hash join.
  5. MyISAM is the default table type and storage engine.  It is not crash safe and not transactional.  On new installations it’s recommended that you change this to InnoDB and use InnoDB for most if not all of your tables.  It’s very reliable and very fast!
  6. There is a query cache, but it caches result sets not query plans!  It also has some performance issues and shows some erratic behavior on larger SMP boxes.  Query plans are cached on a session basis, but when a session is closed and reopened, MySQL must reparse and reexecute that query.
  7. MySQL does not have a facility like Oracle’s Real Application Clusters.  It does have NDB Cluster which is an all-in-memory clustering solution.  Despite it’s promise, it tends to have very serious performance problems with any type of join, and is mainly good for single table index-based lookups.  If managed well it can increase availability but will probably reduce performance.
  8. MySQL’s default replication solution is statement based.  Although it is easy to setup, it breaks almost as easily, sometimes with resolvable errors, and sometimes silently.  Consider row-based replication, and definitely make use of Maatkit’s mk-table-checksum and mk-table-sync tools.  Also be sure to do thorough and regular monitoring of your replication setup.
  9. There are no in-built materialized views or snapshots in MySQL.  There is an open-source project called Flexviews by Justin Swanhart that provides this facility to the MySQL community.
  10. MySQL provides stored procedures, triggers and functions as a regular feature to the database.  However I would use them with caution.  They are very difficult to edit, troubleshoot and diagnose when they are causing troubles.  Also as with the query plan caching, stored procedures are cached at the session level, so they can be expensive to execute over and over again in different areas of your application.  They can cause real performance problems.
  11. There is no in-built mechanism for auditing that you find in relational databases such as Oracle 11g.
  12. Only b-tree indexes are supported, no bitmap indexes, index-organized tables, clustered indexes or other more exotic index types.
  13. ALTER TABLE is generally a locking and blocking operation.  For example if you add a new column or change a columns data type, the entire table will be locked for the duration of the operation.  This will be a surprise coming from the Oracle world where these type of operations can routinely be done online.

MySQL’s Strengths Are Numerous

  1. Install with an RPM using Yum or Aptget.  Fast & simple!
  2. Works great in the cloud, using MySQL Community distro, Percona distro, or Amazon’s own RDS solution.
  3. Comes out-of-the-box with an excellent command line shell providing all sorts of features and power that are constant frustrations on the Oracle side.  Command history, standard input/output redirection support, a full compliment of features and options, and easy autologin with a user level my.cnf file which fits in nicely with the global settings as well.
  4. A simpler mechanism to serve unique id columns with the auto-increment data type.  Although Oracle’s sequence method is extremely scalable, for many many developers it is troublesome and confusing.
  5. Good support of the LIMIT clause allowing an easier method for developers to fetch a subset of data.
  6. A huge community of users, forums, and support in third party applications such as monitoring (Nagios etc…) as well as metrics collection (Munin, Cacti, OpenNMS, Ganglia etc.)
  7. Great visibility of system variables with SHOW VARIABLES.  Many can be changed dynamically as well, just like Oracle.
  8. Great visibility of internal system state with SHOW PROCESSLIST.
  9. System counters for all sorts of internal instrumentation data using SHOW STATUS and SHOW INNODB STATUS.  Ultimately it is not as comprehensive as Oracle’s own data dictionary and millions of instrumentation counts.  However Oracle could take a huge page out of the MySQL book in terms of usability.  The obfuscation of Oracle’s internal kernel state makes it all but unusable by most.
  10. innotop, the utility much like the unix TOP facility that all Unix & Linux folks love, it provides instant visibility into what queries are running, what work is being done, and what is blocking.  Oracle could really take a page from this playbook, as this tool is so invaluable.
  11. The incredible Maatkit, a veritable goldmine of great community contributed powertools.  Query analyzers, profilers, log tools, replication tools, data archiver, a find facility, and a whole lot more!

Sean Hull discusses further on Quora – What considerations are important when migrating to MySQL?

Database Migration – What is it and why is it important?

Migration in the context of enterprise and web-based applications means moving from one platform to another.  Database Migrations are particularly complicated as you have all the challenges of changing your software platform, where some old features are missing, or behave differently and some new features are available and you’d like to take advantage of those.

In the world of databases, some developers try to build database independent applications, especially using ORMs (object relational mappers).  On the surface this seems like a great option, build your application to use only standard components and features, and then you can easily move to a different platform when requirements dictate.  Unfortunately things are not quite that simple.

Database independent applications necessarily shoot for the lowest common denominator of all of your database platforms, thus lowering the bar on what high-performance features you might take advantage of on the platform you are currently using.

Here are some scenarios:

  1. Building an application which needs to support multiple database backends for customer sites
  2. Building an application in dev and test for proof of concept.  May port to an alternate database in the future.
  3. Don’t want to be locked into one vendor, but have plans for only one platform currently.

These are all good reasons to think about features, and database platforms from the outset.

For situation #1, you need to be most serious about cross-platform compatibility from the start.  Build modules for each database platform, with platform specific code isolated in that module.  If the particular feature you want to use is available only on one of the two platforms, the alternate platform will have to include its implementation of that feature in the database specific module.  Also by isolating all database specific interactions to one module, you have also put boundaries around that code.  If you choose to support another database platform in the future, you merely need to rewrite that database interaction module.

For situation #2, you would use a similar tactic, but won’t necessarily have to implement all the routines in that module for the alternate platform.  Just keep those features, and differences in mind during the development phase.  Where possible document those differences, and comment code liberally.  This will go a long way towards preparing you if you do decide to go for a different database backend.

In situation #3, this may be more of a philosophical concern at this stage.  Don’t get overly dragged down by this, as it’s hypothetical at this stage.  Sometimes developers labor under this concern from previous bad experiences migrating to a new database platform.  But to some degree this is the nature of the beast.  Database platforms include a myriad of different features, datatypes, storage methods, and coding languages.  In many ways this is where their power lies.

Sean Hull discusses on Quora: What is a database migration and why is it important?

Migrating MySQL to Oracle

This article is from 2006.  MySQL has come a long way since then. MySQL 5.5 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.  Here’s a high level mysql feature guide.

What really separates the two technologies is cultural.  MySQL, rooted in the open source tradition is much more do-it-yourself, leaning towards roll your own solutions in many cases. Meanwhile Oracle provides named and proven paths to solve specific problems.

You might also check out: Migrating MySQL to Oracle Guide which is a larger multi-part series & work in progress.

For some basics What is a Database Migration?

Lastly these may be helpful – Migration to MySQL – Limitations, Anomalies, Considerations & Strengths and also Oracle to MySQL Migration Considerations

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. Here’s a more in depth feature comparison of MySQL and 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 privilege control, called virtual private database and although we don’t see it used a lot in Oracle deployments, MySQL lacks this feature as well.

MySQL does not have hotbackups which have been an integral part of Oracle for years. (There are hotbackups now – 2012 – in MySQL here’s a howto on rebuilding replication using hotbackups guide) 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. There is the federated storage engine, but reports are that it’s rather buggy. DB Links 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.

Updated note: In 5.5 and newer versions of MySQL there are no database size limitations. Also with Innodb you can use global tablespaces or one tablespace per table depending on your configuration. With most databases sitting on RAID or SAN these days, you’re getting pretty much the same deal with both MySQL & Oracle storage-wise.

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 and download page.

On the other side, here’s the MySQL 5.5 Reference Guide.

Made it this far? Grab our newsletter.