1

I would like to view the following columns in the query output in RDS MySQL. Could you help me to write sql query against the right system tables to view all the sql queries (from one specific db or all db ) executed by one specific database user or all users ?

The columns that I am trying to fetch are - database user name database name tablename, sql query id, sql query text, query start time query end time

For example, I executed select count(*) and then trying to see the list of comamnds that I had executed, so, I tried to query them using the system table "INFORMATION_SCHEMA.PROCESSLIST" but i coudldn't find the same. Please guide/correct me.

I connect the instance using "testuser" credentail and execute the following

  create database testdb;
    use testdb;
    create table testdb.table01 as select * from testdb01.Persons;
    
    select count(*) from testdb.table01;
    
    SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='testuser';
    
    228336  testuser    10.xx.xxx.xxx:50881     Sleep   14      
    228337  testuser    10.xx.xxx.xxx:50882 testdb  Query   0   executing   SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='testuser'
     LIMIT 0, 1000

Thank you,

Kind regards, sk

user3258784
  • 1,987
  • 6
  • 24
  • 28
  • Kindly re-format the information you have provided, it is impossible to understand. Also provide the queries you have tried. – Yash Laddha Jul 12 '22 at 16:52
  • It looks like you're looking for a site where you can hire someone who write this query for you. This is not that site. On this site, we expect you to show us your best attempt at writing the query. Then explain at what point(s) in that query you aren't getting the result you expect. You will need to provide sample data, your table schema so we know how the tables are related and the data types you're working with, and an example of the desired output. – devlin carnate Jul 12 '22 at 17:12
  • Hi David, My apologize, I have edited my main post with necessary details and commands that i executed. I request you to guide me. Thank you very much. – user3258784 Jul 12 '22 at 20:37

1 Answers1

0

The INFORMATION_SCHEMA.PROCESSLIST is a monitor for all the corrently active queries on your DB, but if they are fast to execute you most likely never see them in that table

You could try to use the MySQL integrated logging procedure insted by logging all the queries within the mysql.general_log table and filter the user_host column

Refere to this question Log all queries in mysql

Keep in mind that this table can grow in size very quickly if not frequently cleared, also it can cause some performance issues due to resource consumption

Ivan Bertola
  • 301
  • 1
  • 3
  • 12