CODEWITHSUNDEEP
mysql
surya.in
surya.in

Reputation: 1551

SQL command to display history of queries

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

Upvotes: 155

Views: 417109

Answers (8)

GoYun.Info
GoYun.Info

Reputation: 1366

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

Upvotes: 0

Sérgio
Sérgio

Reputation: 7249

@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]))"

Upvotes: 3

GTodorov
GTodorov

Reputation: 2123

For MySQL > 5.1.11 or MariaDB

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

If you want to output to a log file:

  1. SET GLOBAL log_output = "FILE";
  2. SET GLOBAL general_log_file = "/path/to/your/logfile.log"
  3. 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

Upvotes: 42

DimiDak
DimiDak

Reputation: 5601

You 'll find it there 

~/.mysql_history

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

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

Upvotes: 46

user4493696
user4493696

Reputation: 21

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

Upvotes: 2

Vamsidhar Muggulla
Vamsidhar Muggulla

Reputation: 632

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

 cat ~/.mysql_history

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

Upvotes: 22

Maysam Torabi
Maysam Torabi

Reputation: 3770

try

 cat ~/.mysql_history

this will show you all mysql commands ran on the system

Upvotes: 217

Kieren Johnstone
Kieren Johnstone

Reputation: 42003

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.

Upvotes: -1

Related Questions