How to get SQL Query history in MySQL

MySQL Database Query which is executed or performed in a MySQL Command Console can be retrieved or can be recorded. In this article, the description is involving on how to get SQL Query history command which has already executed or performed previously. The query which is executed in a MySQL Command Console actually recorded or being written in a file named ‘.mysql_history’. It is located in the home directory of each user logged.

In other means, if there is a user named ‘myuser’ logged in into a certain login session represented with a bash prompt display and furthermore if there is a command executed in the bash prompt for logging in into MySQL Database Server, as soon as an SQL query command is being executed or performed, it will be recorded or written in a file named ‘.mysql_history’ which is located in its home directory.

For an example, below is the session of an user named ‘myuser’ and the user itself is accessing MySQL Command Console as shown below :

myuser@hostname:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show status;
+-----------------------+-------+-----------+
| Variable_name         | Value |           |
+-----------------------+-------+-----------+
| Aborted_clients       | 0     |           |
| ...                   |       |           |
| Tc_log_max_pages_used | 0     |           | 
| Tc_log_page_size      | 0     |           | 
| Tc_log_page_waits     | 0     |           |
|...                    |       |           |
+-----------------------+-------+-----------+
358 rows in set (0,06 sec)
mysql> quit
Bye
myuser@hostname:~$

After executing a certain query, just check the file named ‘.mysql_history’ in the home folder or directory of the user as shown below :

myuser@hostname:~$ ls -al | grep .mysql_history
-rw------- 1 myuser myuser 34449 Jul 16 17:52 .mysql_history
myuser@hostname:~$

So, to prove whether or not the last executed SQL query is being recorded or it has already written in the file. Just look at the content of the file with any text editor available or provided in the operating system as shown below :

myuser@hostname:~$ vim .mysql_history
myuser@hostname:~$ 

Below is the content in the file which is presenting the last SQL query executed in MySQL Command Console :

...
show\040status;
quit
~