Introduction

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:

[mysqld]
set-variable=connect_timeout=10

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.

Logfiles

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.

[mysqld]
log-error=/var/lib/mysql/logs/error.log
log-slow-queries=/var/lib/mysql/logs/slow.log
long-query-time=10
log=/var/lib/mysql/logs/general.log

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-
log

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.

Authentication

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
Password:

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 mysql.com 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
Preparing...
########################################### [100%]
   1:MySQL-devel
########################################### [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
Preparing...
########################################### [100%]
Giving mysqld a couple of seconds to exit nicely
   1:MySQL-server
########################################### [100%]
050712 17:46:56 [Warning] Asked for 196608 thread stack, but got
126976
050712 17:46:56 [Warning] Asked for 196608 thread stack, but got
126976
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
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
http://www.mysql.com
Support MySQL by buying support/licenses at
https://order.mysql.com
Starting MySQL SUCCESS!
$

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
Preparing...
########################################### [100%]
   1:MySQL-client
########################################### [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 shull@iheavy.com.