Christian Little

Long time for me not posting…yeah I tend to suck at that. But hey, if you’ve been reading this blog for a while you should be use to the fact that I only post a few times a week at best. Anyways, let’s get to the point – I’m going to teach you some basic MySQL skills to diagnose problems in case your database has problems. Specifically, I’m going to focus on four things you should be able to do if you have a MySQL database:

1. Process List Management
2. db.err File
3. my.ini Configuration
4. Console Booting

Process List Management
This skill is mostly to deal with a single problem – too many demands being placed on your server. But the cause of these excessive queries can be from several bigger problems (i.e. zombie processes, bad programming, hackers, etc). Knowing basic process list management in MySQL takes you one step closer to fixing performance issues.

One thing most people don’t know is that you can actually see who is connected to your MySQL database, how long they’ve been on, and what they are currently doing. All this is accomplished though the show processlist command, and you can do it in any visual interface (such as phpMyAdmin), or through the console. This is a sample output:

What this table shows you is an ID for everybody connected to your database, their hostname, their current status (which is usually Sleep or Query), and the time they’ve been connected to the database. If you scroll to the right (not shown in the image above), one of the last columns shown is their current commend (so if they just executed a query you’ll see the exact query they have requested).

So what you should take away from this is that if your queries are running extremely slow and/or mysql is unresponsive even though you know it’s running, try connecting and running the show processlist command. You might find that you’ve got a zombie process (a command that was executed and is now roaming free). If this is the case, simply type kill 12345 where 12345 is the process ID. Worst case scenario is you disconnect a user from your database (which really isn’t a big deal, most software is built to connect/reconnect every time page loads), so you don’t risk losing anything with this command.

db.err File
The db.err file is a sort of general error log for MySQL. It can give you some good insights into why MySQL is failing to startup, or why it might be shutting down. It is a very general buffer, so don’t expect a huge amount of information but it can point you in the right direction.

The .err file is named after your server, so if your server is named “Bob”, the error file will be called “Bob.err”. It is usually located in the \MySQL\Data\ subfolder, wherever you’ve installed MySQL. You can open the file with a simple text editor (just don’t change anything in there!).

NOTE: When you setup MySQL you can change the location and name of this file.

One of the first things you’ll see is something like this:

D:\MySQL\bin\mysqld-nt: ready for connections.
Version: ‘4.0.18-nt-log’ socket: ” port: 3306

That is the standard log record when MySQL first starts up and if that’s the last file in your log (scroll to the very bottom), and you know MySQL started without error.

But what’s the fun in it working properly? Here’s a really messed up one that I had to deal with yesterday:

D:\MySQL\bin\mysqld-nt: ready for connections.
Version: ‘4.0.18-nt-log’ socket: ” port: 3306
081022 14:43:12 D:\MySQL\bin\mysqld-nt: Normal shutdown

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17967 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17940 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17932 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17906 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17564 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17548 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17540 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17506 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 17130 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 14523 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 14335 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 14325 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Forcing close of thread 14307 user: ‘{removed}’

081022 14:43:13 D:\MySQL\bin\mysqld-nt: Shutdown Complete

If you see something like this at the end of you log file, you have a major problem. MySQL shut down and kicked everybody off. Unless you purposely shut down MySQL, then this is probably a surprise to you. If you are lucky, MySQL might throw out a few clues in this error file about why it shut down (doesn’t always happen though).

So what can you use this for? You can get a rough idea of how frequently MySQL starts/stops, and get a few clues about how to keep it up and stable.

my.ini File
I’d like to point out that you should never touch this file unless you have a major problem with MySQL. It is the very core of your setup, and you could easily mess up your entire installation by changing any one setting.

I’m only mentioning this file because it’s good to know about, but I highly advise against editing it yourself unless you are very competent with MySQL. The my.ini file is stored in a completely different folder than where the rest of MySQL get’s installed (it’ll usually tell you where during the installation). If you don’t know where exactly it is, then just do a search for “my.ini” on your computer (Windows search can find it really fast).

On Windows-based machines, my.ini by default gets put into the C:\Windows folder (but again, this can easily be changed or different for any given machine).

my.ini contains all the configuration options for MySQL. There are dozens of options in this file, including the port number, hardware configuration, and many other sensitive pieces of information that can completely break your computer if you aren’t careful.

Here is a small snippet of the configuration data stored in this file:

If you believe MySQL has a configuration error, then my.ini is the file to check. Go through every line in it and check the documentation on it before you change anything – you really don’t want to screw this up.

Console Booting
No, I’m not talking about kicking your server with your boot…although most of us want to at some point or another.

Console booting refers to not loading MySQL as a service, but rather directly through a single session (just like you would a normal program on your computer). All you have to do is use your favorite console (for Windows this would be Start -> All Programs -> Accessories -> Command Prompt). Then you navigate to your MySQL folder by typing: cd /MySQL.

One there, simply type: mysqld –console

What that does is try to load MySQL as an individual program, rather than a service. As soon as you disconnect from it, MySQL is no longer running.

So why is this useful? Well, console booting gives you way more information on why MySQL won’t start. The .err log mentioned above only contains a few pieces of data, but if you console boot, MySQL will actually scream at you with the precise error as to why it won’t startup.

And if it doesn’t, then you’ll just end up inside MySQL (which you can just close the console window to stop if you want). It’s risk-free, and it’s the easiest way to debug startup errors with MySQL.

So There You Have It!

I’ve just equipped you with four MySQL-saving skills and it only took you about five minutes to learn them. Isn’t system administration interesting?

If all of this stuff is new to you, then I would highly recommend trying all four items above right now. It won’t hurt your machine at all, and you’ll start to get a basic idea of ways to babysit your database in case something bad happens.

Related Blogs

• Diagnosing MySQL Problems with MySQL Profiler
• Getting MySQL Status Values with mysqlreport
• Recovering from a mysql meltdown – I love that post title!