JIYIK CN >

This article highlights various ways to get a history of MySQL commands we have executed in Windows and Linux.

MySQL Command History

We will use Windows and Linux (Ubuntu 20.04) operating systems to understand different ways to get MySQL command history.

For Windows operating systems, we will use MySQL Workbench and the Windows command line (also known as cmd and command prompt) to access the history of MySQL commands in the form of files and tables.

For Linux operating systems, we will use the Linux shell (terminal) to access the MySQL command history.

Get command history in MySQL using Windows command line

Here, we will explore three ways to get command history in MySQL.

Get the history of all MySQL commands in a tabular format

1. Open a Windows command line and go to the MySQL bin folder which is located by default at C:\Program Files\MySQL\MySQL Server 8.0\bin.
2. Log in to MySQL using your login credentials.
3. Run the following two queries to turn on the MySQL query log and prepare the history of MySQL commands in a tabular form.

   SET GLOBAL log_output = 'table';
   SET GLOBAL general_log = 'on';
   

4. Execute the following query to get the history of executed MySQL commands as output.

   SELECT a.*, CONVERT(a.argument USING utf8) FROM mysql.general_log a;
   

   In the query above, we use convert()the getHex method to convert the hexadecimal value of the parameter field to text. You can also get the parameter attribute value as a hexadecimal value using MySQL 5.7 or later.

Since we have limited space on the command line, we select specific fields as follows:

SELECT a.event_time,a.user_host, a.thread_id, a.server_id,
CONVERT(a.argument USING utf8) AS arugment
FROM mysql.general_log a LIMIT 10;

Output:

Get the history of all MySQL commands in a file

This method is very similar to the previous one. We just need to execute the following query after logging into MySQL.

SET GLOBAL log_output = 'file';
SET GLOBAL general_log_file = "/yourPath/logfile.log";
SET GLOBAL general_log = 'on';

Now, find the file at the specified path and open it to see all the details. Typically, the filename.log file looks like the following.

Output:

Get the history of the last MySQL commands executed in the specified session

We can also find the last executed MySQL command by using the following query for a specific session only. We do this here for root and we only select two fields due to limited space on the command line.

SELECT a.event_time,CONVERT(a.argument USING utf8) AS arugment
FROM mysql.general_log a WHERE user_host LIKE 'root%'
ORDER BY a.event_time DESC LIMIT 1;

Output:

Getting MySQL command history using MySQL Workbench on Windows

We have three methods here as well to retrieve the history of MySQL commands. These methods are the same as we discussed using Windows command line. But this time, we will do it on MySQL Workbench.

Get MySQL command history in table format

We execute the following query to get the history of MySQL commands in tabular form. Here, the convert() method converts the blob value of the parameter field to text.

If you are using MySQL 5.7 or higher, you can also get the value of a parameter property as a blob in Workbench.

SET GLOBAL log_output = 'table';
SET GLOBAL general_log = 'on';
SELECT a.*, CONVERT(a.argument USING utf8) FROM mysql.general_log a;

Output:

Instead of writing a query, we can use the History Output option (highlighted in the screenshot below) to get a history of commands.

Get MySQL command history in one or multiple files

We can get all the history in a file in a specified path.

SET GLOBAL log_output = 'file';
SET GLOBAL general_log_file = "/yourPath/logfile.log";
SET GLOBAL general_log = 'on';

We can also go to the C:\Users\DELL\AppData\Roaming\MySQL\Workbench\sql_history location to see all the individual files with MySQL command history.

Get MySQL command history of last executed query

SELECT a.*,CONVERT(a.argument USING utf8) AS arugment
FROM mysql.general_log a WHERE user_host LIKE 'root%'
ORDER BY a.event_time DESC LIMIT 1;

Output:

Get MySQL command history using Ubuntu terminal

Run the following command while logged in as superuser.

cat ~/.mysql_history

Output:

You can run the following command to eliminate the spaces and make it more readable.

sed "s/\\\040/ /g" < ~/.mysql_history

Output: