Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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, 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 imo 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.

share|improve this answer
    
That's not a problem, we have large user database but we have pairs (ip,name)... But if I'm reading you correctly there's no way how to detect bruteforce connections attacks, is it? –  Vyktor Jul 23 '12 at 19:57
    
Is there a way to execute a command on a connection's disconnect? I'd like to have the connection duration as well. –  Kris Kumler Sep 20 '12 at 14:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.