Logging users to the MySQL error log

Problem

A customer recently showed up with the following problem:

With your guidelines [1] I am now able to send the MySQL error log to the syslog
and in particular to an external log server.
But I cannot see which user connects to the database in the error log.

How can I achieve this?

Idea

During night when I slept my brain worked independently on this problem and in the morning he had prepared a possible solution for it.

What came out is the following:

• We create an UDF which allows an application to write to the MySQL error log.
  See my previous article about this [2].
  
• We specify in a simple SQL query how the string should look which we want to write to the MySQL error log file.
  
• We use the init_connect [3] hook (= logon trigger) which MySQL provides to log the information to the error log.
  

How to solve it?

The UDF can be taken from [4]. Be not confused by the version number. It just worked with MySQL 5.1.42. Load the UDF according to the article into the MySQL database. Follow the little example there and if it works lets continue to the next step.

The SQL query to form the MySQL error log string looks as follows:

  mysql> SELECT CONCAT('[Security] User ', USER(), ' logged in.');

And if executed with the function:

  mysql> SELECT log_error(CONCAT('[Security] User ', USER(), ' logged in.'));

it produces the following output to the MySQL error log file:

  shell> tail -n 1 error.log
       100215 17:50:16 [Security] User oli@localhost logged in.

And now make this permanent for every user which does not have SUPER privileges:

  #
  # my.cnf
  #
  [mysqld]
  init_connect = 'SELECT log_error(CONCAT("[Security] User ", USER(), " logged in."));'

restart the database and it should work now (it could also work with just SET GLOBAL init_connect=...).

Caution

Please consider the MySQL documentation [3] and be aware of the following:

"Note that the content of init_connect is not executed for users that have the SUPER privilege."

Further I want to warn you that I have NOT tested the impact on stability and performance of this method! Please test it carefully yourself an let me know if you find something or also if it works smoothly for you.

This is part of the MySQL Auditing Package we are currently working on and we hope to finish it soon. If you are interested in this work please let us know and our MySQL consultants are happy to help you implementing your own MySQL auditing in your environment.

Literature

[1] MySQL reporting to syslog
[2] MySQL useful add-on collection using UDF
[3] MySQL documentation: init_connect
[4] UDF collection

Can you trust your backup?

Today a customer with corrupted data files showed up. When we enquired a bit more he told us that he had a broken I/O controller. This is one of the worst things which can happen to you!

The reason is the following: When a I/O controller starts to die it often does not happen immediately. The controller dies slowly producing more and more corrupt data. When you just write data without checking or reading them it can take days or even weeks until you discover the problem.

But the nasty thing is, that even your backup is infected with the corrupted data. In worst case corruption started long before your oldest still existing backup was made.

Fortunately DBMS are a bit sensitive related to data corruptions and start to complain pretty early. So please consider warning or error messages about data corruption as serious and try to find the problem immediately and solve it!

What can we do against spreading data corruption?

• Monitor logs (syslog, database error log, application log, etc.).
• Ideally do physical AND logical backups. If your database is too big for a logical restore you can redirect your logical backup to /dev/null. So you can assure that at least the data can be read and the mysqldump command should complain when you hit data corruption (does not work for index corruption!).
• Think about a backup retention policy.
• Think about 2 independent paths to recover your data (keep at least 2 good backups + all the binary logs).
• Do a check on your backuped files (myisamchk, innochecksum).
• Test your backup frequently with a restore (ideally on a daily basis)!

Unfortunately this last item is done much to rare by MySQL users and then they experience bad surprises when they have to do a restore once in an emergency!

Have you EVER tested a restore of your backup? Please do! Especially if your data size is significant bigger than your amount of RAM!

In a later article I will show you a concept to do backups and test the restores regularly. Including some positive side effects on your development process.

If you need some more information or help about backup concepts, emergency restore or data recoveries please consider our consulting services.