Logging connections in MySQL doesn't work

Question

I am trying to log all the connection attempts in my MySQL server (don't worry, it's a low traffic one). Here is my my.cnf file:

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid general_log=1 general_log_file=/var/log/mysqld.log 

But, after restarting, it doesn't work. All I see in the log when there's a connection to the database is:

[ERROR] Invalid (old?) table or database name '.pki' 

The server version is 5.1.73. What am I doing wrong? (Also, despite this being a low traffic server, is there any performance advantage in enabling logging to a SQL table instead of to a file?)

Answer 1

I also had this requirement previously and followed the step below to log the connections. Hope this helps you.

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);

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).