MySQL Logs

My previous boss told us that in order to become a good system admnistrator, one must know how to parse the 'logs'.

I hate the 'logs'. As you are well aware of, there are three major logs in MySQL and those are the error logs, slow query logs, and the binary logs.

1. Error Logs

This is the simplest of all the logs and the most important for all the newbies. In case you forget anything, remember to know where the error logs are located because it will save you a lot of headaches in the feature.

2. Slow Query Logs

The infamous utility called the mysqldumpslow will help you parse your slow query logs. If you have a small database, then this can help you. But what if you have a 25 GB database with a rate of 1,000 queries per seconds? Mysqldumpslow will still be able to help you, but you may have to parse the slow query logs on your own.

You can use your favorite language, PERL, Python or even AWK to help you parse the slow query logs. Come to think about it, they are just text files. Is it easy to write a parsing script? Hmmm, it depends on your skill but it is worthwhile to learn regular expression and ways to parse these files on your own.

3. Binary Logs

When I first heard about this type of log, I hate it! This log contains all the changes done to your database. And there is a parameter in the my.cnf that controls the growth of the binary logs and it would be painful if you don't set it right.

Imagine the binary log grew to 2 GB, when you use the mysqlbinlog to convert the binary logs to text files, imagine how long it would take? Or what if you know a position in the binary log that you want to extract, how long would it take the mysqlbinlog to get it from a 2 GB file?