Mysql remote connections logging

Question

I need to allow remote connections to my mysql server running on latest stable CentOS.

I don't like this idea at all so I want at least to collect as much relevant information as possible. To be exact, I need (want) following:

• [datetime] 1.2.3.4 connected
• [datetime] 3.4.5.6 wrong password

According to mysql manual, there's either logging everything (not acceptable performance overhead) or nothing that seems relevant to me (and what would help us trace attacker/irresponsible administrator).

I thought about grepping and erasing this log every N minutes, but this just doesn't seems right.

Answer 1

There is actually a way to log all connections into a mysql itself:

Assumptions: The name of the database we will use to store this information will be called admin.

1. Create the table to store the connection information in. You don't have to include the unique key if you don't want to.

   CREATE TABLE admin.connections (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, connect_time DATETIME NOT NULL, user_host VARCHAR(50) NOT NULL, connection_id INT UNSIGNED NOT NULL, UNIQUE INDEX idx_connect_time_user_host (connect_time, user_host));

2. Set the init-connect variable. This is a string to be executed for each client that connects. Details here.

   SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user_host, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

3. Check permissions of all of your users to ensure that they can insert a record into the admin.connections table.

4. Login as a user without global privileges and a row should be inserted in the admin.connections table. Note that the init-connect system variable does not work on users with global privileges. For better or for worse, all of us DBAs know what that means.

5. Watch your connection table grow and flourish. You've just started a new pet (project).

Source: http://mysqlhints.blogspot.com.es/2011/01/how-to-log-user-connections-in-mysql.html

Now you have to expect your users are responsible, they might start flooding your table with data, but then your dealing with someone with malicious intent. To be safe I'd use a remote location that watches this table and keeps a copy of it.