SQL command to display history of queries

Question

I would like to display my executed sql command history in my MYSQL Query Browser. What is the sql statement for displaying history?

sounds like a repost of this question: http://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql — lazarus, Oct 19 '11 at 07:44
Possible duplicate of [How to show the last queries executed on MySQL?](https://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql) — Siraj Alam, Apr 30 '18 at 09:32

score 210 · Answer 1 · edited Jun 10 '14 at 06:36

210

try

 cat ~/.mysql_history

this will show you all mysql commands ran on the system

edited Jun 10 '14 at 06:36

Baby Groot

4,637
39
52
71

answered Jun 10 '14 at 06:18

Maysam Torabi

3,672
2
28
31

2

well not *all*, suppression occurs for some that contain strings like "password". – mckenzm May 15 '15 at 18:44
1

It's also not usually up-to-date with your open MySQL CLI so you're likely to have to `exit` MySQL then read the file. – Joel Mellon May 25 '16 at 17:44
3

This only shows commands executed by the user that is logged in. – Christia Dec 21 '16 at 23:37
3

How to do this in windowss – ßiansor Å. Ålmerol Aug 02 '18 at 06:40
It is there a possibility that other commands are suppressed or that they are unordered? And, does the log update it self in real time? – Drubio Feb 13 '19 at 11:48
This is great for ones run by a user, but what about ones run by other programs running, such as by PHP scripts on a webserver? – cazort Oct 12 '21 at 14:55
This is only for MySQL clients I believe. If any application running the query on MySQL that is not showing here. What is the option to get that? – Sounak Saha Jun 22 '22 at 13:45

score 40 · Answer 2 · edited Aug 26 '19 at 13:28

40

For MySQL > 5.1.11 or MariaDB

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
Take a look at the table mysql.general_log

If you want to output to a log file:

SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "/path/to/your/logfile.log"
SET GLOBAL general_log = 'ON';

As mentioned by jeffmjack in comments, these settings will be forgetting before next session unless you edit the configuration files (e.g. edit /etc/mysql/my.cnf, then restart to apply changes).

Now, if you'd like you can tail -f /var/log/mysql/mysql.log

More info here: Server System Variables

edited Aug 26 '19 at 13:28

Skippy le Grand Gourou

6,976
4
60
76

answered Sep 28 '13 at 02:07

GTodorov

1,993
21
24

4

Just some clarification to the above: The copy-paste commands here are for a single session of MySQL, e.g. you can enter these from the interactive MySQL command line. If you want them to be a permanent feature of your MySQL instance, you need to put those commands in /etc/mysql/my.cnf and restart the MySQL service. – jeffmjack Feb 06 '19 at 14:24
Also note that logging will stop if `/path/to/your/logfile.log` is deleted, until you execute `SET GLOBAL general_log_file = "/path/to/your/logfile.log";` again (or maybe restart the session if you edited the configuration ?). – Skippy le Grand Gourou Aug 26 '19 at 13:33
for further clarification of @jeffmjack 's answer, the format of the commands in `/etc/mysql/my.cnf` is of the form `general_log=on` – Kai Carver Jun 15 '22 at 11:24

score 38 · Answer 3 · answered Apr 07 '19 at 10:03

38

You 'll find it there

~/.mysql_history

You 'll make it readable (without the escapes) like this:

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

answered Apr 07 '19 at 10:03

DimiDak

4,820
2
26
32

score 22 · Answer 4 · edited Oct 21 '15 at 07:28

22

(Linux) Open your Terminal ctrl+alt+t run the command

 cat ~/.mysql_history

you will get all the previous mysql query history enjoy :)

edited Oct 21 '15 at 07:28

NooBskie

3,761
31
51

answered Oct 21 '15 at 06:52

Vamsidhar Muggulla

622
7
19

score 2 · Answer 5 · edited Nov 02 '17 at 17:29

2

Look at ~/.myslgui/query-browser/history.xml here you can find the last queries made with mysql_query_browser (some days old)

edited Nov 02 '17 at 17:29

Blackcoat77

1,574
1
21
31

answered Jan 26 '15 at 05:13

user4493696

21
1

score 2 · Answer 6 · answered Mar 01 '21 at 17:02

2

@GoYun.Info answer but with python 3

cat ~/.mysql_history | python3 -c "import sys; print(''.join([l.encode('utf-8').decode('unicode-escape') for l in sys.stdin]))"

answered Mar 01 '21 at 17:02

Sérgio

6,966
1
48
53

score 0 · Answer 7 · answered May 10 '20 at 00:08

You can see the history from ~/.mysql_history. However the content of the file is encoded by wctomb. To view the content:

shell> cat ~/.mysql_history | python2.7 -c "import sys; print(''.join([l.decode('unicode-escape') for l in sys.stdin]))"

Source:Check MySQL query history from command line

score -1 · Answer 8 · edited May 23 '17 at 11:47

You can look at the query cache: http://www.databasejournal.com/features/mysql/article.php/3110171/MySQLs-Query-Cache.htm but it might not give you access to the actual queries and will be very hit-and-miss if it did work (subtle pun intended)

But MySQL Query Browser very likely maintains its own list of queries that it runs, outside of the MySQL engine. You would have to do the same in your app.

Edit: see dan m's comment leading to this: How to show the last queries executed on MySQL? looks sound.

SQL command to display history of queries

8 Answers8