MySQL Server

What is it?

MySQL is an open source relational database, filling the same niche that Oracle or DB2 might for a larger group. We use and generate gigabytes of data in our computational experiments. MySQL stores and collates this data using effecient structures (like BTrees) to allow rapid querying for specific subsets of this data. In general, it's used where people need to share data, use data from several locations, or most importantly where the size of data makes a flat file or RAM storage impracticial. Data is accessed via TCP/IP connections, using the SQL query language. MySQL comes with a command line client, but there are also graphical clients and APIs in many language to manage retrieval. You can google for SQL tutorials, or use my primer here. I also have a partial primer for accessing MySQL from python (link). I highly recommend the free mysqlcc as a free program for administration. The official MySQL manual is useful for both users and administrators of MySQL.

Where does it live?

The server itself resides on the following tree (parts shown):

/net/fugu/usr0/tools/mysql/ -- documentation is here
- bin/ -- clients and administrative programs
- lib/ -- libraries used by clients and APIs (linker path must be pointed sometimes)
- include/ -- header files for clients and APIs (include path must be pointed sometimes)
- data/ -- soft link to the data directory

Actual data lives in /net/fugu/usr0/db/mysql_data/.

NOTE: An older version of the database lives on motif. This has occaisionally proved useful, but is not up to date and can prove confusing sometimes, especially with soft links or applications which are configured pointing to the wrong location. Most of this should be long fixed, but be aware.

Standard Operating Procedures

Most of the responses here use tools from mysql's bin directory. Many of these tools require a "-p" modifier to prompt you for a password, depending how you set it up. Add it yourself!

Installation Notes
- Like other 3rd party apps, build on a redhat 7.0 machine (like motif) if you must reinstall. Otherwise, libraries specific to RH9 will be used.
- Some client apps or APIs will need libraries or headers from MySQL. You may have to put those in the path (see tree above) when building or running them.
Shutdown the DB
If you need to fix corrupted tables, or wish to upgrade to a new version of MySQL, this may be necessary.
1. Run mysqladmin shutdown
Restart the DB
If you've shutdown the DB as above, follow these instructions to restart it. The reason you run mysqld_safe instead of mysqld directly is that the database must run as its own user ("mysql"). This script wraps the process of starting as that user.
1. cd to /net/fugu/usr0/mysql
2. Run bin/msysqld_safe
Kill Runaway Queries
So you just joined 3 tables with no "ON [condition]" clause? No problem.
1. Run mysqladmin processlist and identify the offending query ID.
2. Run mysqladmin kill id,id,id... to finish the job.
3. If the query updated tables, you may want to OPTIMIZE them manually.
Fix Corrupted Tables
This can be a tricky problem to notice, since it can appear as bad data without causing an error. I haven't had problems since the 4.0 upgrade, but if you run into them try this.
1. Fire up mysql and run OPTIMIZE TABLE [name]. The optimizer does some repair routines.
2. Try mysqlcheck in the bin directory.
3. If all else fails, contact the help desk and restore the files in the data directory. Data will be lost, but hey.

back home
bobsedge@andrew.cmu.edu