User History Bash Script

In my previous blog, “Getting MySQL User Info: Limitations and Possibilities,” I described how I found a solution to obtaining a history of login information for a specific user logging into the database from different servers if the audit log plugin is not installed or an available option. Keep reading only if you are running MySQL 5.5. If you are running MySQL Enterprise 5.6, 5.7, or 8.0, save yourself the trouble and install the audit log plugin.

This blog goes into my thinking process to identify relevant information to go into the user history log, which table will give me that information, and steps to creating a script that logs user history.

Narrowing Down the Needed Information

Two queries would return the hostnames of the servers a user is connecting from. If the general log is enabled and in table format, the query below will return the event time, user host, thread ID, server ID, command type, and argument of all logged connections.

 mysql> select * from mysql.general_log where command_type = 'Connect';

You might only need some of that information. For example, the following query narrows the returned information to thread ID, user, and hostname. I kept the ID column because I wanted to differentiate between thread connections.

 mysql> select ID, USER, HOST from mysql.general_log where command_type = 'Connect';

This option is good if you do not like or care about when a thread connection is idle, but it will return every connection from every user. Not a good option if disk space is an issue.

The other option is to query the INFORMATION_SCHEMA.PROCESSLIST table for when the user is logged in. The query below will return the thread ID, user, hostname, database being used, the type of command the user was executing, the seconds the session has been connected, the state of the thread, and the value of a SELECT statement (if executed).

mysql> select * from information_schema.processlist where user = 'user_name';

Again, that’s a lot of information if you just want the hostname. The following variation narrows down the information to thread ID, user, hostname, database name, and the length of the connection time in seconds. I used this one for my bash script because I didn’t care when other users connected.

mysql> select ID, USER, HOST, DB, TIME from information_schema.processlist where user = 'user_name';

The Bash Script

In the script, I used variables for the username, file name for output, and the location of the login configuration file. As a result, the code is cleaner and easier to change without editing multiple areas of the script.

# Setting variables
userName='user_name'
fileNm=output_destination.csv
defltsXtraFlNm=hidden_config.cnf

In the previous blog, I used an IF statement to create a timestamp when the user was logged in. There is an easier, simpler way. A timestamp can be incorporated into the query itself using the functions CURRENT_DATE() and CURRENT_TIME() as followed:

mysql> select ID, USER, HOST, DB, TIME, CURRENT_DATE(), CURRENT_TIME() from information_schema.processlist where user = 'user_name';

There will only be a return if the specified user is logged in, so there is no need for an IF statement. The main part of the script will look like this:

$ mysql --defaults-extra-file=$file_destination -e "select ID, USER, HOST, DB, TIME, CURRENT_DATE(), CURRENT_TIME() from information_schema.processlist where user = `$userName`;" >> $fileNm

The output will look something like this: