a practical guide to migrating from oracle to mysql · 2007-04-06 · encryption transparent, rc4,...

1Copyright 2006 MySQL AB The World’s Most Popular Open Source Database

A Practical Guide to Migrating fromOracle to MySQL

Robin SchumacherDirector of Product Management, MySQL AB


Agenda

• Quick look at MySQL AB• Relationship between Oracle and MySQL• Non-technical reasons why to migrate to MySQL from Oracle• Technical contrasts between Oracle and MySQL• MySQL engine, security, and datatype migration considerations• Migration path strategy • A brief look at the MySQL Migration Toolkit• Final migration considerations• Resources for jump starting migration efforts


Overview of MySQL AB• Founded in 1995• Operations in 30 countries• 10 million product installations• 50,000 downloads each day• Over 5 million downloads of 5.0 to date• Dramatically reduces TCO of database management• Bundled by more than 100 SW and HW companies• Sold by partners such as HP, Dell, Novell, Unisys, and

others


Chosen by Successful, Modern Companies

• Embedded– ”Batteries included” database in

software applications– Network elements

• High Volume Web Sites– Dynamic content– eCommerce– Gaming & entertainment– Scale Out

• Enterprise– Data Warehousing– High-Volume Transactions– Departmental– Intranet/Extranet– Scale Out


Relationship Between MySQL and Oracle

• Innobase and Oracle valued partners of MySQL• Oracle announced as one of our “Partners of the

Year” at 2006 MySQL User’s Conference.• InnoDB one of MySQL’s Storage Engines• Business as usual


Why Migrate to MySQL?Cost Savings

Example: 10 server, 2 dual-core CPUs, 3 years support

$770K = 93%$2,596K = 98%Savings with MySQL

$830K$2,656K$60KTotal

$330K$1,056K$60K3-years Maintenance and Support

$500K$1,600K$0License

MS SQL Server Enterprise

Oracle Enterprise

MySQL Network

3 year cost comparison


Why Migrate to MySQL?High-Performance

New SpecAppServer Benchmarks (May 2006)

31%542.17Oracle 4-core (Best)BEA/HP/Itanium/RedHat

16%615.64Oracle 8-core (Best)BEA/Sun/SPARC/Solaris

-712.87MySQL 4-core (Best)SunJavaAppServer/Sun/Opteron/Solaris

Percent MySQL Better Than Oracle

JOPS(Java Ops Per Second)

Database Server / Configuration

MySQL 4 core beats Oracle 8 core…!


Why Migrate to MySQL?High-Performance

Last eWeek Database Shoot-Out

MySQL v5 33% better than v4 in TM1 benchmark and up to 20% faster in DBT2 when stored procedures used.


Why Migrate to MySQL?Strong Feature Set

Highly-Available Clustered Database

Cost-based Optimizer

Backup with Point-in-Time Recovery

Open Source

Terabyte Database Size Capable

Stored Procedures, Triggers, Functions, Cursors, Updateable Views

Replication

Robust datatype support (BLOB’s, varchar, datetime, numerics, etc.)

Advanced Indexing (Clustered, Full-Text, Hash, R-Tree)

Server-Enforced Referential Integrity

Row-Level Locking, MVCC (readers don’t block writers)

ACID Transaction Support

Partitioned Tables/Indexes

Standard Heap Tables and B-Tree Indexes

OracleMySQLFeature


Why Migrate to MySQL?Strong Feature Set

MySQL does not have all the features of Oracle but…• MySQL isn’t designed to be the most feature rich RBDMS but rather one that…

• Offers 80-90% of the features at 10% the cost of proprietary (90% TCO savings)• Provides very high-performance and rock-solid reliability• Easiest to install, setup, configure, maintain, and use• Easily co-exists with Oracle in the data center

Choose MySQL When it Makes Sense and Oracle When it Makes Sense


Why Migrate to MySQL?MySQL Network

Certified Software for high reliability and stabilityMySQL Software Update Service offers customized updates and alerts for your specific environmentOnline Knowledge Base provides rapid answers to technical questionsProduction Support around-the-clock support directly from MySQL engineersIntellectual Property Protection with comprehensive indemnificationComing Soon! New Monitoring and Advisor Service offers proactive best practice enforcement, notifications, and advice for optimizing MySQL servers


Oracle to MySQL Migration Scenarios

• Full application migrations• Partial application migrations (lookup data, web session info, etc.)• Business Intelligence migrations (data warehouse, marts, etc.)• Data archiving migrations (storing historical data, etc.) • Embedded database applications requiring smaller footprint

Web Customer Shopping, Session,

Account Information

New Transaction Data


Contrasting Oracle and MySQL

• Cost-based• Cost-based• Cost-based Optimizer

• Filegroups• Files

• Tablespaces• Datafiles

• Tablespaces• Table/Index Files• Format files

Data Storage

• TempDB (2005+)• Transaction Logs

• Undo Tablespace (9i+)• Redo Logs• Archive Logs

• InnoDB Undo Space• InnoDB Logs• Falcon Log• Binary Log

Redo/Undo Logs

• Buffer cache• SQL cache • Misc caches (lock,

connection, workspace, etc.)

• Data cache (variants)• Log buffer• Shared Pool• Java Pool • Large Pool• PGA

• MyISAM key caches• InnoDB data cache• InnoDB log cache• Dictionary cache• Falcon caches• Query Cache• User caches

Memory Caches

Microsoft SQL ServerOracleMySQLArea


Contrasting Oracle and MySQL


Migration Considerations – What Engine?

HighHighLowHighHighLowMemory cost (relative to other engines)

HighMedHighestHighMedHighBulk insert speed

YesYesYesYesYesYesReplication support

NoNoNoYesNoNoBuilt-in Cluster/High-availability support

NoYesNoNoYesNoForeign Key support

NAMedSmallestMedMedSmallStorage cost (relative to other engines)

NoNoYesNoNoYesCompressed data

NAYesNoYesYesYesIndex caches

NAYesNoYesYesNoData caches

NoYesYesNoYesYesGeospatial support

NoYesNoNoYesNoMVCC snapshot read

TableRowRowRowMVCCTableLocking granularity

NoYesNoYesYesNoTransactions

Yes64TBNoYes110TBNoStorage limits

MemoryInnoDBArchiveNDBFalconMyISAMFeature


Security and Datatype Considerations

Column (row via view)Column, RowPrivilege Granularity

Handled via loggingAudit subsystemAuditing

AES, DES, MD5 SHA1; 128/256Transparent, RC4, DES, AES, etc.Encryption

YesYesSSL Supported

On RoadmapROLESGroup Management

GRANT, REVOKE. Host consideration

GRANT, REVOKEObject Privileges Handled

Root. SYS, SYSTEM, othersBuilt-in Accounts

MySQLOracleSecurity Item

NoneBFILE

BLOBLONG RAW, BLOB

MEDIUMBLOBRAW

LONGTEXTCLOB, LONG

CHAR, VARCHAR, TEXT

CHAR, VARCHAR(2), NCHAR, NVARCHAR

MySQL DatatypeOracle Datatype

DATETIME,TIMESTAMPDATE, TIMESTAMP

DOUBLEFLOAT

DECIMALREAL, DOUBLE

DECIMAL (P,S)NUMBER (P,S), DECIMAL (P,S)

INT, BIGINT, DECIMALNUMBER, DECIMAL

MySQL DatatypeOracle Datatype


Miscellaneous Migration Considerations

Objects in Oracle that do not have a complement in MySQL include:

• TYPEs• Synonyms • Materialized Views (coming in upcoming version)• Dimensions• Sequences (MySQL uses AUTOINCREMENT columns)• Bitmap Indexes• Global Partitioned Indexes (coming soon)• Packages (although MySQL does have stored procedures)• Java Stored Procedures• DDL and Statement-Based Triggers (MySQL has Row-Based)


Migration Strategies

3 4 522

• User accounts • Entities• Relationships• Datatypes• Rules (data, etc.)

• Simple moves • Transformations• Aggregations • Data Profiling /

Cleansing

• ETL Runs• Scheduled/Recurring• Validation• Migrate Code?

• Simulation• Metric Analysis• Review

11

Document Sources

• User Accounts • Entities• Relationships• Datatypes• Rules (data, etc.)

Data Migration Lifecycle

Design Targets

Design ETL Flows

Build/RunMigrations

Performance Test

• Scripts/Manual• Data Models• Models, Reports, etc.

• Scripts/Manual• Data Models• Models, Reports, etc.

• Scripts/Vendor Supplied

• Tactical 3rd Party• Enterprise Global

• Scripts/Manual• Tactical 3rd Party• Enterprise Global

• Manual• Tactical 3rd Party• Automated


Migration Strategies – Step 1

Document Oracle Sources

1. Manual2. 3rd party Tool3. MySQL Workbench (Beta)

Best Practice – Use Models!



Define MySQL Targets

1. Manual2. 3rd party Tool3. MySQL Workbench (Beta) or MySQL Migration Toolkit

Best Practice – Use Models or Migration Toolkit!



Design ETL Flows

1. Manual via scripts2. 3rd party Tool3. MySQL Migration Toolkit

Best Practice – Use Third Party Tool or Migration Toolkit!



Build and Run Migration Routines

1. Manual via scripts2. 3rd party Tool3. MySQL Migration Toolkit

Best Practice – Use Third Party Tool or Migration Toolkit!



Performance Test

1. Manual2. 3rd party Tool3. MySQL mysqlslap utility

Best Practice – Use Third Party Tool or mysqlslap!


Migration Toolkit – How Does it Work?

1. Connect to and Reverse Engineer Oracle Source2. Connect to MySQL Target3. Select Schemas/Objects to Migrate4. Customize MySQL Target Schemas/Objects5. Transform Oracle Schemas/Objects to MySQL Schemas/Objects6. Move Data from Oracle to MySQL


Oracle-to-MySQL Object Translation

Schema Migration Toolkit Database

Table Migration Toolkit Table

Index Migration Toolkit Index

View Migration Toolkit View

Stored Procedure manual Stored

Procedure

Function manual Function

Trigger manual Trigger


Other Oracle->MySQL Migration Considerations

• For large data migrations, ensure all proper MySQL parms are set (Migration Toolkit will do automatically for you).

• Enable sql_mode option in MySQL 5.0 for maximum data integrity • Look into 3rd party management tools to manage both Oracle & MySQL• Ensure to mirror Oracle needs in MySQL (autogrowth datafiles, binary

logging for point-in-time recovery, etc.)• Ensure MySQL memory defaults are not used for demanding systems• Look into MySQL 5.0+ Instance Manager to manage multiple MySQL’s• When tuning migrated SQL code, utilize slow query log to catch bad SQL• Periodically check MySQL error log just like Oracle alert log• Look to MySQL Network for production/data center applications


Conclusion

Does it make sense to migrate from Oracle to MySQL?

• Financial? Yes! 10-20% of the cost of Oracle. • Reliability? Yes! MySQL maintains the highest levels of uptime at many

enterprise installations.• Performance? Yes! MySQL has proven track record against Oracle.• Ease of Use? Yes! MySQL is much easier to install, setup, and administer.• Safety? Yes! MySQL Network provides certified software, around-the-

clock support, and much more to provide data center-level confidence.

Choose MySQL When it Makes Sense and Oracle When it Makes Sense


Resources

• Professional Services 5-day “Migration Jumpstart”– www.mysql.com/migration

• MySQL Migration Toolkit– http://www.mysql.com/products/tools/migration-toolkit/

• MySQL Migration Central– http://www.mysql.com/why-mysql/migration/

• MySQL Migration Forums– http://fourms.mysql.com

• MySQL Case Studies– http://www.mysql.com/it-resources/case-studies/

• MySQL Whitepapers– http://www.mysql.com/why-mysql/white-papers/


A Practical Guide toMigrating from

Oracle to MySQL

Thank You!

[email protected]

a practical guide to migrating from oracle to mysql · 2007-04-06 · encryption transparent, rc4,...

Documents