0

I'm having a issue that my tables are being dropped, and I don't know how/where.

But enabling the general logs create 2 million records in less than 10 minutes and this is one of my least busy hours.

I want to log drop queries for at least 24 hours... what's the best way to handle this?

7
  • 1
    ??? Your accounts have DROP TABLE permissions? revoke them! Only one special administtrative account should have such permissions. If another account needs in such operation it must call according stored procedure with SECURITY=DEFINER. And this SP must log this deletion - who and when.
    – Akina
    Feb 8, 2022 at 10:19
  • I suspect it's a sql injection in the website. So this wouldn't help much. They would just run a delete loop if I have safe updates and no drop permissions
    – Freedo
    Feb 8, 2022 at 17:43
  • So this wouldn't help much. None injection can jump out of the account permissions. If an account have no DROP TABLE privilege then none injection can drop the table. They would just run a delete loop What I can say... heal your code. Treate the cause of the disease, not the symptoms.
    – Akina
    Feb 8, 2022 at 17:50
  • First I need to know where the statements are coming from
    – Freedo
    Feb 8, 2022 at 18:02
  • This is not the first. Moreover, this is not a job, this is a result of a job. Firstly you must detect then deletion and collect all possible data about it.
    – Akina
    Feb 8, 2022 at 18:46

2 Answers 2

2

You can install the MariaDB Audit Plugin and configure it to log only DDL statements (CREATE, ALTER, DROP, RENAME and TRUNCATE), and enable it, by running:

INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_events = 'QUERY_DDL';
SET GLOBAL server_audit_logging = ON;

Alternatively, this can all be done in the appropriate option file, although that requires a restart to take effect:

[mysqld]
plugin_load_add = server_audit
server_audit_events = QUERY_DDL
server_audit_logging = ON

While that's not logging exclusively DROP TABLE statements, it should be a smaller log file than what you get with the general log.

The logs produced will have the format described on this documentation page, e.g.:

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],DROP,[database],[object],
4
  • I thought this plugin was paid. I seem to have it running and doing what it should, but I had to run SET GLOBAL server_audit_logging=ON;. Thank you I love this place!! Does this plugin generate less load than the general log ?
    – Freedo
    Feb 8, 2022 at 23:58
  • @Freedo Ah yes, good point. Yes, it should be considerable less load since it's creating less log messages
    – dbdemon
    Feb 9, 2022 at 4:16
  • Wouldn't most of the load be in inspecting every query for the keywords?
    – Freedo
    Feb 9, 2022 at 8:18
  • @Freedo I don't know the implementation details of this, but I would assume the overhead of filtering out the relevant queries is small compared to everything else going on inside the MariaDB server. This article says it adds a small overhead. Personally, I wouldn't worry about it, unless you're already experiencing slowness with your MariaDB server before installing this. BTW, I updated my answer to include server_audit_logging=ON - thanks again for pointing that out!
    – dbdemon
    Feb 9, 2022 at 11:22
1

There is no simple way to do it using the general log:

  • Have the general log on

  • Have a cron job that runs every few minutes:

    • Rotate the general log
    • grep the previous log for DROP, appending to a file of just DROPs.
    • delete the previous log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.