A client recently asked me if I could get the hostname or IP address of every server a MySQL user was using to connect for the past 30 days. I never thought of retrieving login information retrospectively. I wasn’t sure if I could. Spoiler alert: it is only possible if the audit log plugin is installed, which is available for MySQL Enterprise 5.6, 5.7, or 8.0. (Instructions on audit log installation, configuration, and filtering information can be found in the MySQL Reference Manual.) Unfortunately, this client ran an older version of MySQL Enterprise, and the audit log plugin was not installed.
If you are running MySQL Community versions or MySQL Enterprise 5.5, your options for obtaining user information are limited. This blog covers what user data can be obtained and how a user log can be created.
MySQL User Info: Getting Basic Information
The query below will return the user’s name, host, and privileges. You will also get security and resource control specifications for the user. After MySQL 5.7, you can also determine the last time the password was changed.
mysql> select * from mysql.user where user = 'user_name';Although I got a lot of information from running this query, I didn’t get the hostname or IP address that the specific user used to connect because the user’s host was created with the wildcard %. Using the wildcard for the hostname would allow the user to login from various IP addresses. Now what?
There were two other options: 1) looking at the general log (if enabled) and 2) the process list if they were logged in. Both options have limitations. The general log is usually rotated frequently, meaning it will show login history for a day at the most. In this particular scenario, every 5 minutes. Not ideal, but the processlist only shows users that are currently logged in. Without knowing when the user would be logging in, I would have to query the processlist multiple times a day in hopes of “catching” them. Despite the limitations, assessing the information was a stepping stone to the solution.
MySQL User Info: Getting Past Login Information From The General Log
The general log will record when a client connects or disconnects to the server when enabled. The output can be a table or a file (default), which is determined by the log_output system variable. Either format will increase in size as much as the activity of the database. Therefore, this file will grow quickly if the database is very active. Hence, the reason for the rotation frequency and not being enabled by default.
The following query can be used if the general log is in table format. It will return the event time, user, hostname, thread ID, server ID, command type, and argument of all logged connections. You can read more about the general log in the MySQL Reference Manual.
mysql> select * from mysql.general_log where command_type = 'Connect';MySQL User Info: Getting Current Login Information From The Processlist Table
The PROCESSLIST is a table in the INFORMATION_SCHEMA database. You need the PROCESS privilege to access the table. The query below will return the thread ID, user, hostname, the 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). For more information, check out the MySQL Reference Manual.
mysql> select * from information_schema.processlist where user = 'user_name';This would give me the exact hostname of the server that the user was connecting from. However, the user has to be logged in at the same time. I would not get a history of previous logins. The client wanted to know the host name to which the user was connecting over time. Now what? I told the client I could not get a history but could create a cronjob and script that queried the INFORMATION_SCHEMA.PROCESSLIST every 15 minutes. It was tricky to create a script that returned specific information only when the user was logged in, which I will go into more detail in the next blog.