Skip to content

Personal tools
You are here: Home » Of Interest » Articles of Interest » MySQL for the Oracle DBA
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 2416

MySQL for the Oracle DBA

by Sean Hull


You’re an Oracle DBA, and suddenly, as if it fell from the sky, a MySQL database is now in your lap, and it requires care and feeding. What to do? Take heart: Given your Oracle experience, you’ve probably learned many of the basic concepts you’ll need to know for MySQL already, and they will, with careful attention, transfer to the open source world.

I’ve organized this article by separating out some of the fundamentals you’ll need to know when moving from Oracle to MySQL, so you can immediately focus on the areas with which you need help. And, if you have questions about tuning, backups, or moving data in MySQL, you’ll find the answers to all of those questions, too.

What Oracle DBAs Probably Don’t Know

The first thing that Oracle DBAs might not know about MySQL is that it is fast.

That’s right; believe it or not, MySQL is blazingly fast, due, in part, to its limited use of transactions. Unfortunately, this also somewhat limits applications by requiring them to implement such protections in some cases. But MySQL’s speed is also due to a smaller code path, since it is a smaller piece of software than Oracle.

Something else that Oracle DBAs might not know about MySQL is that it has many features in common with Oracle. In version 4.1, MySQL supports subqueries and derived tables. That means some sophisticated queries besides basic SELECTs are supported. And, you can expect more additions to VIEWs and JOINs in version 5.x as well (more on that later in this article).

Finally, MySQL has a really nice and well-rounded set of datatypes; this is in contrast to Oracle, which sometimes tries to force DBAs to do everything with VARCHAR, BLOB, and NUMBER. Some of the datasets that MySQL implements include INT with SMALL, MED, and BIG variants; FLOAT, and DOUBLE. Think of “C” datatypes in which you have the CHAR and VARCHAR strings, just as in Oracle; CHAR is fixed length, and VARCHAR is variable. You also have large object datatypes like BLOB, and TEXT, as well as TIME, DATE, and related datatypes. Two others that are good to have are ENUM for enumerated datatypes, and SET; these two datatypes differ only in the maximum number of members in each, at 64K and 64, respectively.

High Availability Features

MySQL does support clustering and it is being used in production, although the features still seem quite restrictive. For example, MySQL is limited to an 8K row length, with a maximum of 1600 objects. Foreign keys are ignored, and savepoints and associated rollbacks are ignored in ways similar to MyISAM tables. Durability goes out the window, as there is no guarantee that logs are flushed and written to disk, and only the READ_COMMITED isolation level is available.

High-availability features I would recommend include MySQL’s replication. This is mainly implemented as MASTER->SLAVE, so many of the transactional issues you have with clustering are eliminated. Thus, you achieve a good high-availability backup of your database in the event of catastrophe on the main database.

Coming Soon

Currently, MySQL offers UNION in V.4.x and LEFT and RIGHT OUTER JOIN in V.5.x. And while MySQL V.5.x already offers much more than does V.4.x, V.5.x will soon also offer features like Oracle’s CONNECT BY for hierarchical queries, ANY, EVERY, and SUM GROUP BY functions, full view functionality, as well as MINUS, INTERSECT, and FULL OUTER JOIN. All in all, there’s a lot to look forward to.

Existing Limitations

MySQL does have some limitations that you should keep in mind if you’re putting it into place for a production application. The following features are not available as of the latest stable version, V.4.1, but have all been added in V.5.x (however, at the end of this article, I’ll mention some other features that Oracle users might expect, but are not currently available in MySQL).

You won’t find triggers and stored procedures, for starters. That means you won’t be storing compiled code in the database. In Oracle, we know that this tight integration of data and code can give amazing performance boosts, especially with features like bulk binding, so we look forward to how they are implemented in V.5.x of MySQL. In terms of datatypes, VARCHAR is limited to 256 bytes in V.4.1, and trailing whitespace is stripped. Both these problems are fixed in V.5.x. Also, you’ll note that in V.4.1, cursors are absent. This is related to MySQL’s limited perspective on transactions, and Atomicity, Consistency, Isolation, and Durability (ACID).

In terms of table sizes, in MySQL, you are theoretically limited to the size of a datafile. By default, many Linux distributions might peg this number at around 2g, because the default ext2 filesystem has that limit. With ext3 (the successor to the ext2 filesystem), you can have 2TB datafiles. However, MySQL documentation recommends using the ALTER TABLE command to set max_rows and avg_row_length for large tables. You can also startup with the --big-tables option (note that the maximum rowsize is 64K).

Yes, there are some limitations, and for some native Oracle DBAs, this may take some getting used to; however, for a large number of applications, you may do quite well.

Anatomy of a MySQL Database

In Oracle, you learn your way around as a database by exploring its physical layout. You need to understand, and know where to find, the redolog files, control files, datafiles, configuration files (init.ora), and logfiles alert.log. With that in mind, we’ll discuss all of those things in MySQL, and show you how to start and stop your database.

The Files

By default, databases are stored in /var/lib/mysql and each has its own directory. The starter database that was installed with the RPM is called “mysql,” and the tables and indexes associated with it are found in /var/lib/mysql/mysql

You can probably guess that the MYD files are MySQL datafiles, and MYI files are MySQL index files.

You’ll also see a corresponding .frm file for each table. These store the data dictionary information so MySQL knows what the structure of the table is, and how to access the binary MYD and MYI files.

Periodically, you should also check file integrity in MySQL. If you have the misfortune of storing data on an area of disk that is bad, MySQL provides you with a couple of diagnostic/repair utilities to choose from. You can use the CHECK TABLE syntax from the MySQL command line, or from the OS, issue “myisamchk” commands. I prefer “myisamchk” commands, and you can run this automatically from cron and dump errors to a log file. Use a command like this:

$ myisamchk -rq /var/lib/mysql/mysql/*.MYI

In the previous example, “r” is for repair, and “q” is for quick. Note that there are MyISAM and ISAM tables; this example works on MyISAM files; there is also an “isamchk” utility for ISAM tables.

Startup / Shutdown

Normally, you should start up and shut down MySQL using the UNIX init scripts installed with the RPM. The following would be your primary commands:

$ /etc/rc.d/init.d/mysql start
$ /etc/rc.d/init.d/mysql stop
$ /etc/rc.d/init.d/mysql status

Be sure there is a symlink to the mysql script in the directory for your runlevel (for instance, /etc/rc.d/rc3.d/S64mysql) so that MySQL will start up when the server comes up, automatically.

Config Files

The main config file you’ll need to look at is the /etc/my.cnf file. To get started, you can set some system variables in MySQL this way:


Note that you can also set them while MySQL is running, either globally or for the session. However, you must use the my.cnf file if you want those settings to be persistent when the server is restarted. MySQL does not include a default my.cnf file, and, in fact, such a default is not necessary: All the defaults for a running database are already resident in memory when the application starts. Use the my.cnf file only if you want to change one of these defaults. The variables that can be set are explained in detail in the documentation, and the previous example shows you just how to set one.


The my.cnf file mentioned in the previous section can also be used to specify where your logfiles go. Add the following options to start logging errors, slow sql queries that take greater than 10 seconds to run, and a general log as well.


Note: Be sure permissions are set correctly. If you have trouble, check permissions on the directory where you’re trying to store logfiles to be sure mysql can write a file there.

There is also something called an update log, which will alert you to every transaction that updates a MySQL table. This can be helpful for auditing, for example.

Session + Global Variables

As mentioned in the config files section, the my.cnf file can be used to set system variables. But there are many times when you just want to set some variable while the server is up, either globally or for just a session. Here’s how you do that:

$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.12-standard-

Type "help;" or "\h" for help. Type "\c" to clear the buffer.

mysql> set global connect_timeout=15;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.connect_timeout;
| @@global.connect_timeout |
|                       15 |
1 row in set (0.00 sec)

There are many variables you can set from query_cache_size that are akin to Oracle’s shared_pool_size, to max_connections, long_query_time. Consult the documentation to find the specific variable you need.


Normally, when you use the mysql client program, you specify a username as follows (assume your database name is “sean_db”):

$ mysql sean_db -u sean

If you forget your password, especially the root password, the best way to remedy the situation is to stop the database, edit the start script, and comment out this line:

$ bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file

Next, make a copy of the line, and add the option “--skip-grant-tables” without the comment character, and run the start script. You’ll then be able to login without a password, and change passwords to whatever you like. Be sure to stop the database again, uncomment the old line without the “--skip-grant-tables” option, and start up again.

Quick Install Guide

One of the things I have always loved about MySQL is how I can install it in about five minutes. In this, MySQL is miles ahead of Oracle. You can install the database, client apps, and header files with three simple Linux packages or RPM files. These files include the binaries or source code of software, but also details about versions and dependencies, so using the RPM program to install will automatically check dependencies, and tell you if you need to install other libraries or software beforehand.

What’s more, the database RPM will actually create a basic database and start up the MySQL processes. Believe it or not, that five-minute estimate I mention includes the download time. Of course, building out more complex applications, and downloading associated libraries will add more time; Oracle installs a whole host of included software components such as Apache, which you would have to install separately with MySQL.

OK, how do you get started? I recommend using version 4.1.12, the most stable version of V.4.x. For those who want to live on the bleeding edge, V.5.x is available, but in general, real stability comes with V.4.1.

Select a mirror, and scroll down to the Linux x86 RPM downloads section (or the section for your processor, if you’re not on x86). You should get one file from the “Server” section, one file from the “Client programs” section, and one file from the “Libraries and header files” section. You can use lynx or links to navigate the download site if you’re on a remote server and only have command line access — a real time + headache saver, since this is very often how you need to install software.

First, install the development libraries. These are required if you’re writing perl scripts, or PHP or other programs that talk to MySQL:

$ rpm -Uvh MySQL-devel-4.1.12-1.i386.rpm
warning: MySQL-devel-4.1.12-1.i386.rpm: V3 DSA signature: NOKEY,
key ID 5072e1f5
########################################### [100%]
########################################### [100%]

Next install the server:

$ rpm -Uvh MySQL-server-4.1.12-1.i386.rpm
warning: MySQL-server-4.1.12-1.i386.rpm: V3 DSA signature:
NOKEY, key ID 5072e1f5
########################################### [100%]
Giving mysqld a couple of seconds to exit nicely
########################################### [100%]
050712 17:46:56 [Warning] Asked for 196608 thread stack, but got
050712 17:46:56 [Warning] Asked for 196608 thread stack, but got
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h tegan password 'new-password'
See the manual for more instructions.

NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not
be able to use the new GRANT command!

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
Support MySQL by buying support/licenses at

Note: If you get an error “/usr/sbin/mysqld: Can’t read dir of '/root/tmp/' (Errcode: 13),” you can usually fix this by setting your tmpdir as follows:

$ export TMPDIR=/tmp

Then, rerun the rpm install with --force option.

Lastly, install the client programs:

$ rpm -Uvh MySQL-client-4.1.12-1.i386.rpm 
warning: MySQL-client-4.1.12-1.i386.rpm: V3 DSA signature: NOKEY,
key ID 5072e1f5
########################################### [100%]
########################################### [100%]

It’s pretty easy! Believe it or not, you already have a database in which to login and create tables and queries.

In Depth

So there you have it: A brief discussion of MySQL for the Oracle DBA, how to get started in the Open Source world. In future articles, I’ll be covering some of the following areas in depth:

      • Creating MYSQL schema owners
      • Creating MYSQL data objects (Tables, Indexes, Datatypes, etc.)
      • Loading data.
      • APIs and Libraries
      • Moving/Migrating MySQL data to or from Oracle.
      • Backup/restore/testing
      • Tuning + optimization, benchmarking MYSQL


Sean Hull is founder and senior consultant at Heavyweight Internet Group in New York. He has worked as a database consultant and expert for ten years, in areas of Oracle, and in various open source technologies. He is the author of “Oracle and Open Source” on O’Reilly & Associates, as well as various open source database tools, scripts, and applications. He has also written articles for print publications and Web sites such as Oracle Technology Network, and is a regular contributor to the Oracle-L international Oracle email list, and other online forums. He resides in Manhattan,and can be reached at

Contributors : Sean Hull
Last modified 2005-11-09 12:53 PM

Remarks !

Posted by Radical at 2006-02-06 03:58 AM
> MySQL is blazingly fast, due to its limited use of transactions
Kinda wrong. MySQL does not have support for transactions in MyISAM tables... only for InnoDB and BDB tables...
So it's not a "limited use of transactions" it just dosen't have them... on MyISAM tables.

> ...VIEWs and JOINs in version 5.x
Are my eyes decieving me... JOINs... let me point you in MySQLs manual chapter "13 SQL Statement Syntax - JOIN Syntax" you will see there all JOINs MySQL supports: [INNER | CROSS] JOIN, STRAIGHT_JOIN, [NATURAL] [[LEFT || RIGHT] [OUTER]] JOIN...
A JOIN that is not supported is FULL OUTER JOIN.

If you ment VIEWs and UNIONs that not ok because UNIONs are available from MySQL 4.0.0

If you ment VIEWs and STORED PROCEDUREs and CURSORS that is OK !

> Two others that are good to have are ENUM for enumerated datatypes, and SET;
> these two datatypes differ only in the maximum number of members in each,
> at 64K and 64, respectively.
This is completly wrong.
The ENUM type something like an enumeration of possible values can indeed have 65,535 different values (and is recorded into 1 byte if you have less than 255 possible values OR 2 bytes if more).
You are wright about a SET being able to have only 64 possible values (if the SET size is N this one is being stored in (N+7)/8 rounded up to 1, 2, 3, 4, or 8).

If the main part of an affirmation is wrong... then I being a little radical I say the whole affirmations is wrong... the only left thing to discuss are your saying that "differ only in the maximum number of members"... this was the main affirmation... and is wrong.

In the ENUM type you can choose 1 value from the list of possible values...

BUT in the SET type you can choose for any combination betweeen the list of posssible values.
Lets suppose we a field like:
`enum_months` ENUM('1','2','3','4','5','6','7','8','9','10','11','12') NOT NULL
Possible values a row may take are:
When it comes to SET you can have:
`set_months` SET('1','2','3','4','5','6','7','8','9','10','11','12') NOT NULL
Possible values a row may take are as the ENUM type:
But this column can also take:
For short:
ENUM - 65,535 diff. values - 1/2 bytes - 1 distinct value
SET - 64 diff. values - (N+7)/8 rounded up to 1, 2, 3, 4, or 8 - any combination from the value list in that order

> Foreign keys are ignored, and savepoints and
> associated rollbacks are ignored in ways similar to MyISAM tables.
Foreign keys are available on InnoDB engine since MySQL 3.x and savepoints and associated rollbacks are availabe on MySQL 4.X with InnoDB table type
On MyISAM table for sure you cannot talk about savepoints... because those tables have no clue about what a transacction is !!!
Foreign keys are scheduled for implementation in MySQL 5.1

> only the READ_COMMITED isolation level is available
Please read about isolation levels in "13 SQL Statement Syntax - SET TRANSACTION Syntax":
The default isolation level for InnoDB is "REPEATABLE READ".
Before MySQL 3.23.50 SET TRANSACTION did nothing.
Before 4.0.5 only REPEATABLE READ and SERIALIZABLE were there.

> This is related to MySQL’s limited perspective on transactions, and Atomicity, Consistency, Isolation, and Durability (ACID).
At this phrase one must add "on non-transactional tables like MyISAM".

One must keep in mind that MySQL has several table types: ISAM (old deprecated), MyISAM(default no-transactions), MERGE(collection of MyISAM tables seen as one), MEMORY or HEAP(in memory table), InnoDB (transactions - row locking), BDB or BerkeleyDB (transactions - page locking), FEDERATED (remote tables found on another MySQL server), ARCHIVE (only INSERT and SELECT, nothing else), CSV (stores data in text files using comma-separated-values format, but does not support indexes) , NDBCLUSTER (Clustered, fault-tolerant, memory-based tables) and BLACKHOLE (Storage engine designed to act as null storage)

The majority of users do not even take into account any other table type... taking the default MyISAM as the only one...
Comeing from Oracle I sugest InnoDB with file_per_table set to 1 !!! (Oracle bought recently 'InnoBase Oy' the company behind InnoDB storage engine)

Yes... as Sean said it's simple to install and maintain a MySQL server but if the need for it is growing so will grow your need of information... you will have a lot to read... the Reference Manual is about 1600 pages !!!

Read on !
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone