1) If general mysql logging is enabled then we can check the queries in the log file or table based what we have mentioned in the config. Check what is enabled with the following command
mysql> show variables like 'general_log%';
mysql> show variables like 'log_output%';
If we need query history in table then
Execute SET GLOBAL log_output = 'TABLE';
Execute SET GLOBAL general_log = 'ON';
Take a look at the table mysql.general_log
If you prefer to output to a file:
SET GLOBAL log_output = "FILE"; which is set by default.
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';
2) We can also check the queries in the .mysql_history file cat ~/.mysql_history
Maybe you could find that out by looking at the query log.
If mysql binlog is enabled you can check the commands ran by user by executing following command in linux console by browsing to mysql binlog directory
mysqlbinlog binlog.000001 > /tmp/statements.sql
enabling
[mysqld]
log = /var/log/mysql/mysql.log
or general log will have an effect on performance of mysql
After reading Paul's answer, I went on digging for more information on https://dev.mysql.com/doc/refman/5.7/en/query-log.html
I found a really useful code by a person. Here's the summary of the context.
(Note: The following code is not mine)
This script is an example to keep the table clean which will help you to reduce your table size. As after a day, there will be about 180k queries of log. ( in a file, it would be 30MB per day)
You need to add an additional column (event_unix) and then you can use this script to keep the log clean... it will update the timestamp into a Unix-timestamp, delete the logs older than 1 day and then update the event_time into Timestamp from event_unix... sounds a bit confusing, but it's working great.
Commands for the new column:
SET GLOBAL general_log = 'OFF';
RENAME TABLE general_log TO general_log_temp;
ALTER TABLE `general_log_temp`
ADD COLUMN `event_unix` int(10) NOT NULL AFTER `event_time`;
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 'ON';
Cleanup script:
SET GLOBAL general_log = 'OFF';
RENAME TABLE general_log TO general_log_temp;
UPDATE general_log_temp SET event_unix = UNIX_TIMESTAMP(event_time);
DELETE FROM `general_log_temp` WHERE `event_unix` < UNIX_TIMESTAMP(NOW()) - 86400;
UPDATE general_log_temp SET event_time = FROM_UNIXTIME(event_unix);
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 'ON';
Credit goes to Sebastian Kaiser (Original writer of the code).
Hope someone will find it useful as I did.
You can enable a general query log for that sort of diagnostic. Generally you don't log all SELECT queries on a production server though, it's a performance killer.
Edit your MySQL config, e.g. /etc/mysql/my.cnf - look for, or add, a line like this
[mysqld]
log = /var/log/mysql/mysql.log
Restart mysql to pick up that change, now you can
tail -f /var/log/mysql/mysql.log
Hey presto, you can watch the queries as they come in.
If you don't feel like changing your MySQL configuration you could use an SQL profiler like "Neor Profile SQL" http://www.profilesql.com .
SELECT * FROM mysql.general_log WHERE command_type ='Query' LIMIT total;
You can look at the following in linux
cd /root
ls -al
vi .mysql_history It may help
You can do the flowing thing for monitoring mysql query logs.
Open mysql configuration file my.cnf
sudo nano /etc/mysql/my.cnf
Search following lines under a [mysqld] heading and uncomment these lines to enable log
general_log_file = /var/log/mysql/mysql.log
general_log = 1
Restart your mysql server for reflect changes
sudo service mysql start
Monitor mysql server log with following command in terminal
tail -f /var/log/mysql/mysql.log
Source: Stackoverflow.com