How to Get the MySQL Command History in Windows and Linux

1. MySQL Command History
2. Use the Command Line to Get the MySQL Command History in Windows
3. Use MySQL Workbench to Get the MySQL Command History in Windows
4. Use the Terminal to Get the MySQL Command History in Linux
5. Conclusion

In MySQL, having the ability to review and analyze past commands can be invaluable for debugging, troubleshooting, and understanding the evolution of your database.

This article highlights various ways to get the history of MySQL commands in Windows and Linux. Each method will be accompanied by example codes with images and a detailed explanation to ensure a thorough understanding.

MySQL Command History

In the realm of database management, having access to a comprehensive command history is indispensable. It allows you to trace your steps, debug queries, and gain insights into the evolution of your database.

MySQL, a widely used relational database management system, offers several methods to retrieve command history. Whether you’re interested in using files, querying system tables, or leveraging MySQL Workbench, this guide will equip you with the knowledge you need to effectively manage your database’s command history.

We will be using Windows and Linux (Ubuntu 20.04) operating systems to learn about the different approaches to obtaining the history of MySQL commands.

For Windows OS, we will be using the MySQL Workbench and Windows Command Line (also known as cmd and command prompt) to access the history of MySQL commands in the form of a file and a table.

In the case of Linux OS, we will be using the Linux shell (terminal) to access the MySQL command history.

Use the Command Line to Get the MySQL Command History in Windows

The Windows Command Line is a powerful tool that allows users to interact with their computer’s file system and execute various commands. When it comes to managing databases, MySQL, a popular open-source relational database management system, can be accessed and manipulated directly from the Command Line.

Here, we will explore three methods to get command history in MySQL using Windows Command Line. Before proceeding, ensure that you have MySQL installed on your Windows system and have the necessary permissions to access the MySQL command line.

Get the History of All MySQL Commands in Tabular Form

In the above query, we use the convert() method to convert the hexadecimal values of the argument field to text. You may also get the argument attribute values in hexadecimal values using MySQL version 5.7 or above.

As we have limited space on the command line, we are selecting particular fields as follows:

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

This query is used to retrieve information from the general_log table in the MySQL database. It selects specific columns (event_time, user_host, thread_id, server_id, and argument) for the first 10 rows from the table.

Output:

Get the History of All MySQL Commands in a File

This method is very similar to the previous one. We only have to execute the following queries after logging into MySQL.

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

Now, locate the file at the specified path and open it to see all the details. Usually, the filename.log file looks as follows.

Output:

Get the History of the Last Executed MySQL Command for a Specified Session

We can also find the last executed MySQL command only by using the following query for a particular session. We are doing this for root here and selecting only two fields due to limited space on the command line.

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

This query will retrieve the event_time and the converted argument for the most recent log entry where user_host starts with 'root'.

Output:

Use MySQL Workbench to Get the MySQL Command History in Windows

We also have three ways here to retrieve the history of MySQL commands. These methods are the same as we discussed by using the Windows command line. But this time, we will be executing on the MySQL Workbench.

MySQL Workbench, a graphical user interface for MySQL, provides a user-friendly way to view command history.

Get the MySQL Command History in Tabular Form

We execute the following queries to get the history of MySQL commands in tabular form. Here, the convert() method transforms the blob values of the argument field into text.

You may also get the values of the argument attribute as a blob in Workbench if you are using MySQL version 5.7 or above.

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 queries, we can use the History Output option (highlighted in the following screenshot) to get the history of commands.

Get the MySQL Command History in One File or Multiple Files

We can get all the history in one file at the specified path.

SET GLOBAL log_output = 'file';
SET GLOBAL general_log_file = "/yourPath/logfile.log"; -- specify path here
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 having the history of MySQL commands.

Get the MySQL Command History for the Last Executed Query

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

This query will retrieve all columns (a.*) from the general_log table along with the converted argument for the most recent log entry where user_host starts with 'root'.

Output:

Use the Terminal to Get the MySQL Command History in Linux

In Linux, you can interact with MySQL through the command line using the Terminal. This is a powerful way to manage your databases and execute queries directly.

To access MySQL command history, you can take advantage of the command line’s built-in features.

Use the .mysql_history File

MySQL keeps a record of executed commands in a file named .mysql_history. This file is typically located in the home directory of the user.

Run the following command after logging in as a super user.

cat ~/.mysql_history

• cat: This command is used to concatenate and display the contents of a file.
• ~: This symbol denotes the home directory of the current user.
• .mysql_history: This is the file where MySQL keeps a record of executed commands.

Executing this command will display a list of previously executed MySQL commands.

Output:

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

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

This command takes the contents of the ~/.mysql_history file, searching for occurrences of \040 (which represent spaces) and replacing them with actual spaces. The modified text is then outputted to the console.

Output:

Export Command History to a File

You can export the command history to a text file for easier access and archiving.

history | grep mysql > mysql_command_history.txt

• history: This command displays a list of previously executed commands.
• |: This symbol is used to pipe the output of one command into another.
• grep mysql: This filters the output to only include commands containing the word 'mysql'.
• >: This operator redirects the output to a file.
• mysql_command_history.txt: This is the name of the file where the command history will be saved.

Executing this command will create a text file named mysql_command_history.txt containing the MySQL command history.

Customize MySQL Command Line Options

You can customize MySQL Command Line options to include a history file.

Open your terminal and type:

nano ~/.my.cnf

Add the following lines to the file:

[mysql]
histfile=/path/to/mysql_history.txt

Save the file.

• nano: This command opens the Nano text editor.
• ~: This symbol denotes the home directory of the current user.
• /.my.cnf: This is the configuration file for MySQL client options.
• histfile=/path/to/mysql_history.txt: This line specifies the path to the history file.

With this configuration, the MySQL Command Line will save command history to the specified file.

Conclusion

In the world of MySQL database management, having access to a comprehensive command history is indispensable. It allows you to trace your steps, debug queries, and gain insights into the evolution of your database. Whether you’re working on Windows or Linux, there are various methods to retrieve and utilize the MySQL command history.

On Windows, we explored using both the Command Line and MySQL Workbench. The Command Line offers powerful tools to access the history in tabular form or files. Meanwhile, MySQL Workbench provides a user-friendly graphical interface for viewing command history.

In Linux, the Terminal is your gateway to the MySQL command line. You can access the command history through the .mysql_history file or export it to a text file for archiving.

Additionally, customizing MySQL command line options allows you to specify a history file for easier access.