Oracle vs MySQL

Database specialists who intend to migrate databases from Oracle to MySQL or backward should consider all benefits and disadvantages of each DBMS before they start. This article gives an integral picture of cons and pros for Oracle and MySQL.

Installation

Oracle comes with much more complex system requirements and the installation process than MySQL. The DBMS uses special tool named Oracle Universal Installer (OUI) to handle the following installation tasks:

• explore Oracle tools and components that are installed on the system
• install new Oracle tools and components
• remove Oracle software that is no longer required

When it is required OUI may start the Oracle Database Configuration Assistant (DBCA) to install default database that contains example schemas. DBCA can also help to create and configure a customized database.

MySQL comes as a set of binary distribution files that includes the standard installation GUI wizard for Windows and compressed .tar.gz files for other platforms.

Security

Oracle database is supplied with great security features, the system provides multi-layered security including such advanced options as controls to prevent unauthorized data disclosure, detect and report on database activities and evaluate risks.

MySQL provides security system based on Access Control Lists (ACLs) for all queries and other operations that a user may attempt to perform on databases. MySQL also supports SSL-encrypted connections between servers and clients.

Backup

Oracle offers dedicated complex backup tool called Recovery Manager (RMAN). RMAN supports sophisticated backup policies and selective restores. There are two options of backup:

• shut down the database server and copying physical files (cold backup)
• backup live instance of the database using RMAN (hot backup)

MySQL offers two backup utilities: mysqldump for logical backup (SQL statements level) and Percona XtraBackup for binary backup (physical files).

Oracle advantages:

• Sophisticated security system. Unlike MySQL, Oracle supports groups or roles. There is ability to deny a privilege in Oracle, while MySQL only allows to grant
• External authentication options like LDAP, Active Directory. MySQL supports only built-in authentication
• True full-text search. In MySQL this feature is limited and only available for non-transactional storages
• Sophisticated administration tools and IDEs for debugging
• Integration with business intelligence applications

Replication

Oracle offers built-in Data Guard tool for logical and physical replication. When any update (insert/update/delete) is made to a table of primary database, the changes are written into archive log and then replicated to one or more replicas. Replica databases are in recovery phase continuously, verifying and applying changes to keep synchronization with the primary database.

MySQL replication is easy to set up but ongoing maintenance like software upgrades, schema changes, failover and recovery have often been a challenge. It does not require a third party solutions, both master-slave and multimaster replication can be done out of the box.

Oracle disadvantages:

• High price. The Oracle product can cost multiple times as much as comparable MySQL solution
• Complexity. Unlike MySQL, Oracle requires deep skills to install and maintain it due to complex engine

MySQL advantages:

• It's open source
• Low Total Cost of Ownership. The price of MySQL commercial licenses is less expensive than same configuration of Oracle. Also, MySQL has less demands to the hardware platform compared to Oracle
• Each table can have a different storage engine
• Each storage engine has particular behavior, features, and properties

MySQL disadvantages:

• Does not support user-defined types
• There are no recursive queries (before v8.0)
• "ALTER TABLE" or "CREATE TABLE" statements are non-transactional
• Does not support materialized views
• Does not support sequences
• Does not support roll-back recovery, only roll-forward is supported

If database migration is confirmed after all considerations, it is reasonable to look for dedicated software products for automating the migration process. Intelligent Converters offer two products to convert Oracle to MySQL and MySQL to Oracle handling all database objects except stored procedures and triggers.