Log only drop queries



  • 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?



  • You can https://mariadb.com/kb/en/mariadb-audit-plugin-installation/ the https://mariadb.com/kb/en/mariadb-audit-plugin-log-settings/ 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 https://mariadb.com/kb/en/mariadb-audit-plugin-log-format/ , e.g.:

    [timestamp],[serverhost],[username],[host],[connectionid],[queryid],DROP,[database],[object],
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2