[mysql] 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?

This question is related to mysql

The answer is


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


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

 cat ~/.mysql_history

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


try

 cat ~/.mysql_history

this will show you all mysql commands ran on the system


You 'll find it there

~/.mysql_history

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

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

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


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


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.