0

I've MySQL installed on my Ubuntu server. Because I would like to clean up my users they're currently active in MySQL, I would like to check first which users - and from which host - are connecting to the MySQL server.

The only thing I need is: which host is connecting and which username is used.

I tried to perform this task with tcpdump, but with no luck so far. It looks easy to get the whole query, but I do not get the above mentioned information. The server is heavily used, if there is no other option I can enable the general query log, but if it is possible I would like to prevent this option.

Does anyone have a tip for me on how I can perform this task?

Sai Chandini Routhu
  • 309
  • 1
  • 8
Tim
  • 1
  • 1

1 Answers1

1
  1. list currently active sessions

In your mysql prompt, you may typ the following for displaying currently active sessions:

mysql> show processlist;

Or this mysql query (which return the same info):

mysql> select * from information_schema.processlist;

And you may only get the {user,host} pair like that:

mysql> select User,Host from information_schema.processlist;
  1. log and display query history.

Turn on mysql query log

set global log_output = 'table';
set global general_log = 'on';

See all the executed queries

select * from mysql.general_log;

See the timestamp, the host_user and the related query:

select event_time,user_host,argument from mysql.general_log;

Hope it helps.

petitradisgris
  • 146
  • 5
  • With "show processlist" I can only see the currrent logged in sessions. However, users they do a small query will not pop up in this list. I've to monitor the server a couple of days and after that time I would like to have a list with hosts and users. For that reason I thought tcpdump is a good solution, but I don't get the host/user extracted from a tcpdump file. – Tim Jun 14 '22 at 13:31
  • I updated my answer including a part 2. I think it match your need better. – petitradisgris Jun 14 '22 at 13:49