How to enable MySQL logging in order to capture all MySQL requests?

Follow

Comments

6 comments

  • Avatar
    Mark

    Hi, I just tried this on my system - first I followed the instructions in the 5.1 or higher section as I have 5.7 on Plesk Onyx and nothing seemed to be logged so I tried the at runtime instructions and got the following error:

    root@gsl-lxweb:/var/log# touch /var/log/query
    root@gsl-lxweb:/var/log# chown mysql.adm /var/log/query && chmod 640 /var/log/query
    root@gsl-lxweb:/var/log# plesk db "SET GLOBAL general_log_file = '/var/log/query'"
    root@gsl-lxweb:/var/log# plesk db "SET GLOBAL general_log = 'ON'"
    ERROR 29 (HY000) at line 1: File '/var/log/query' not found (Errcode: 13 - Permission denied)
    exit status 1

    ls -l shows the following:


    -rw-r----- 1 mysql             adm             0 Nov  7 13:09 query

    0
    Comment actions Permalink
  • Avatar
    Alexandr Redikultsev

    Hi Mark,

    I have just checked that on Debian 9 and "5.1 or higher" approach worked for me as expected. Could you provide me with your OS version? In case of Ubuntu, AppArmor might be blocking it.

    0
    Comment actions Permalink
  • Avatar
    DANIEL VENGOECHEA

    Not working on aws lightsail. All variables are changed but not the ones that enable like  slow query log AND general log

    This is what i have on my.cnf file


    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mysql.conf.d/
    [mysqld]
    general_log = on
    general_log_file=/var/log/query
    slow_query_log=1
    slow_query_log_file = /var/log/mysql-slow.log
    long_query_time = 1
    innodb_buffer_pool_size=512M
    sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    bind-address = ::ffff:127.0.0.1
    local-infile=0

    I then check on phpmyadmin on variables tab and I can confirm that all except slow query log AND general log took effect

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov (Edited )

    Hello @DANIEL,

    Could you confirm that you have restarted MySQL server? This is the step number 4. The configuration looks ok maybe configuration changes just were not applied.

    1
    Comment actions Permalink
  • Avatar
    DANIEL VENGOECHEA

    Hi @IVAN,

     

    Yes of course, i did in sudo su , service mysql restart

    Remember that all changes that I apply to the my.cnf file are taken except  general_log = on and slow_query_log=1 . Both of them remain OFF

    0
    Comment actions Permalink
  • Avatar
    Anton Maslov

    @DANIEL what is your operation system, please?

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request