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!

  • Patrick Hurley

    Great article, thanks. If this has prompted any Oracle DBAs to investigate MySQL for the first time, this article I created should help: http://patrickhurley.wordpress.com/mysql-for-oracle-dba/

  • Pingback: Log Buffer #278, A Carnival of the Vanities for DBAs | The Pythian Blog

  • hullsean

    Thx @6ba284eb496d1c59dc5d9ce79c5e02bd:disqus That’s a great howto!

  • Pingback: Migrating MySQL to Oracle

  • http://twitter.com/wwwtedw Ted Wennmark

    Great article!
    Since MySQL 5.5 that was released over 1.5 year ago the default table is not MyISAM but rather InnoDB so no need to be worried about this.

    • hullsean

      Hi Ted, thx for the comment. True enough in 5.5 MyISAM is default. I
      do encounter a large number of customers that are not on 5.5 however,
      some are even on 5.0. So speaking broadly here, I do still see MyISAM
      as the default table type in many deployments.

  • Daniël van Eeden
    • hullsean

      True enough, though there are so many cases where it doesn’t happen, I rarely see them in explain plans.