Question 20123 in Mysql Question posted in
The official MySQL documentation can be found here.

Log queries executed on a UI tool like phpMyAdmin – Mysql

I am trying to populate a history table in my database whenever information_schema.PROCESSLIST gets updated. I tried:

CREATE TRIGGER populate_history 
AFTER INSERT ON information_schema.PROCESSLIST
FOR EACH ROW 
  INSERT INTO myDatabase.history_changes (host, db, query)
  VALUES (new.HOST, new.DB,new.INFO);

But I am getting

Access denied for user 'root'@'localhost' to database 'information_schema'

I have already looked into this answer but it wasn’t of any help.

Can someone point me what I am doing wrong?

UPDATE : As suggested by @zozo, I am going to explain my end goal –

I want to log everything done MANUALLY by a user in the database. Our customers have access to our production database and sometimes they manually edit a row in some table which cause errors on the app. I know our main emphasis should be not allowing them the production db access, but somehow thats not possible currently and I need something to log what they do manually with the tables.

The architecture we use:

We are using AWS RDS MySQL instance. We are using PHP and nodejs to connect to it.(PHP majorly). The codebase is on a separate AWS instance.

The customers use phpMyAdmin to edit records

Tags:

2

Answers


  1. 0

    Based on the edits, and the discussion in comments, I can suggest the following solution:

    You will need to make different users for app and for the users that use phpmyadmin (can be a single one for those, but needs to be different from the app). And then log those.

    Or, if you have a different pma server than the app, you can base it on the ip of the machines (but those can change and you need to take that into account).

    And then if you want those to get into db, just parse the logs and save them (you can use a heartbeat system or a cron for this).

    Login or Signup to reply.
  2. 0

    phpMyAdmin does have some query logging built in which may be worth exploring. I do not use PMA so I have no idea how good/configurable it is.

    As an alternative to writing the general_log to a file, you can direct MySQL’s log_output to a database table and turn on the general_log:

    SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 1;

    Note: changing log_output to table applies to both the General Query Log and Slow Query Log.

    You can check the structure of the table used with:

    SHOW CREATE TABLE mysql.general_log;

    By default it is created with ENGINE=CSV and I would suggest changing the engine to MyISAM (the only alternative):

    SET @old_log_state = @@GLOBAL.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;

    Depending on your traffic, the general log can grow very quickly and if you plan to have it running all the time you will need to create some maintenance tasks, probably as scheduled events, to stop the table growing out of control. Purge all but the logs you are interested in, frequently.

    Another way of tackling this might be to use a proxy (like ProxySQL) as the middle man between PMA and your MySQL server. Then you could implement filtering in the proxy to only log the queries you are interested in.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search