Log all queries in mysql

Question

Is it possible for me to turn on audit logging on my mysql database   I basically want to monitor all queries for an hour  and dump the log to a file

User · Answer

Top answer doesn t work in mysql 5 6   Use this instead    mysqld  general log   on general log file  usr log general log   in your my cnf   my ini file  Ubuntu Debian   etc mysql my cnf Windows  c  ProgramData MySQL MySQL Server 5 x wamp  c  wamp bin mysql mysqlx y z my ini xampp  c  xampp mysql bin my ini

User · Answer

Enable the log for table  mysql gt  SET GLOBAL general log    ON   mysql gt  SET global log output    table     View log by select query  select   from mysql general log

User · Answer

Quick way to enable MySQL General Query Log without restarting   mysql gt  SET GLOBAL general log    ON   mysql gt  SET GLOBAL general log file     var www nanhe log all log     I have installed mysql through homebrew  mysql version   mysql  Ver 14 14 Distrib 5 7 15  for osx10 11  x86 64  using  EditLine wrapper

User · Answer

OS   mysql version     uname -a Darwin Raphaels-MacBook-Pro local 15 6 0 Darwin Kernel Version 15 6 0  Thu Jun 21 20 07 40 PDT 2018  root xnu-3248 73 11 1 RELEASE X86 64 x86 64    mysql --version  usr local mysql bin mysql  Ver 14 14 Distrib 5 6 23  for osx10 8  x86 64  using  EditLine wrapper   Adding logging  example  I don t think  var log     is the best path on Mac OS but that worked   sudo vi   usr local mysql-5 6 23-osx10 8-x86 64 my cnf   mysqld  general log   on general log file  var log mysql mysqld general log   Restarted Mysql  Result     sudo tail -f  var log mysql mysqld general log 181210  9 41 04    21 Connect   root localhost on employees            21 Query    mysql-connector-java-5 1 47   Revision  fe1903b1ecb4a96a917f7ed3190d80c049b1de29     SELECT    session auto increment increment AS auto increment increment    character set client AS character set client    character set connection AS character set connection    character set results AS character set results    character set server AS character set server    collation server AS collation server    collation connection AS collation connection    init connect AS init connect    interactive timeout AS interactive timeout    license AS license    lower case table names AS lower case table names    max allowed packet AS max allowed packet    net buffer length AS net buffer length    net write timeout AS net write timeout    query cache size AS query cache size    query cache type AS query cache type    sql mode AS sql mode    system time zone AS system time zone    time zone AS time zone    tx isolation AS transaction isolation    wait timeout AS wait timeout            21 Query SET NAMES latin1            21 Query SET character set results   NULL            21 Query SET autocommit 1            21 Query SELECT USER              21 Query SELECT USER   181210  9 41 10    21 Query show tables 181210  9 41 25    21 Query select count    from current dept emp

User · Answer

Quick way to enable MySQL General Query Log without restarting   mysql gt  SET GLOBAL general log    ON   mysql gt  SET GLOBAL general log file     var www nanhe log all log     I have installed mysql through homebrew  mysql version   mysql  Ver 14 14 Distrib 5 7 15  for osx10 11  x86 64  using  EditLine wrapper

User · Answer

For the record  general log and slow log were introduced in 5 1 6  http   dev mysql com doc refman 5 1 en log-destinations html  5 2 1  Selecting General Query and Slow Query Log Output Destinations As of MySQL 5 1 6  MySQL Server provides flexible control over the destination of output to the general query log and the slow query log  if those logs are enabled  Possible destinations for log entries are log files or the general log and slow log tables in the mysql database

User · Answer

Note  For mysql-5 6  this won t work  There s a solution that applies to mysql-5 6  if you scroll down or click here    If you don t want or cannot restart the MySQL server you can proceed like this on your running server    Create your log tables on the mysql database     CREATE TABLE  slow log        start time  timestamp NOT NULL DEFAULT CURRENT TIMESTAMP                            ON UPDATE CURRENT TIMESTAMP      user host  mediumtext NOT NULL      query time  time NOT NULL      lock time  time NOT NULL      rows sent  int 11  NOT NULL      rows examined  int 11  NOT NULL      db  varchar 512  NOT NULL      last insert id  int 11  NOT NULL      insert id  int 11  NOT NULL      server id  int 10  unsigned NOT NULL      sql text  mediumtext NOT NULL      thread id  bigint 21  unsigned NOT NULL     ENGINE CSV DEFAULT CHARSET utf8 COMMENT  Slow log      CREATE TABLE  general log        event time  timestamp NOT NULL DEFAULT CURRENT TIMESTAMP                           ON UPDATE CURRENT TIMESTAMP      user host  mediumtext NOT NULL      thread id  bigint 21  unsigned NOT NULL      server id  int 10  unsigned NOT NULL      command type  varchar 64  NOT NULL      argument  mediumtext NOT NULL     ENGINE CSV DEFAULT CHARSET utf8 COMMENT  General log     Enable Query logging on the database   SET global general log   1  SET global log output    table      View the log   select   from mysql general log    Disable Query logging on the database   SET global general log   0

User · Answer

Enable the log for table  mysql gt  SET GLOBAL general log    ON   mysql gt  SET global log output    table     View log by select query  select   from mysql general log

User · Answer

Start mysql with the --log option   mysqld --log log file name   or place the following in your my cnf file   log   log file name   Either one will log all queries to log file name   You can also log only slow queries using the --log-slow-queries option instead of --log   By default  queries that take 10 seconds or longer are considered slow  you can change this by setting long query time to the number of seconds a query must take to execute before being logged

User · Answer

Start mysql with the --log option   mysqld --log log file name   or place the following in your my cnf file   log   log file name   Either one will log all queries to log file name   You can also log only slow queries using the --log-slow-queries option instead of --log   By default  queries that take 10 seconds or longer are considered slow  you can change this by setting long query time to the number of seconds a query must take to execute before being logged

User · Answer

Top answer doesn t work in mysql 5 6   Use this instead    mysqld  general log   on general log file  usr log general log   in your my cnf   my ini file  Ubuntu Debian   etc mysql my cnf Windows  c  ProgramData MySQL MySQL Server 5 x wamp  c  wamp bin mysql mysqlx y z my ini xampp  c  xampp mysql bin my ini

User · Answer

Besides what I came across here  running the following was the simplest way to dump queries to a log file without restarting  SET global log output    FILE   SET global general log file   Applications MAMP logs mysql general log   SET global general log   1    can be turned off with   SET global general log   0

User · Answer

Start mysql with the --log option   mysqld --log log file name   or place the following in your my cnf file   log   log file name   Either one will log all queries to log file name   You can also log only slow queries using the --log-slow-queries option instead of --log   By default  queries that take 10 seconds or longer are considered slow  you can change this by setting long query time to the number of seconds a query must take to execute before being logged

User · Answer

Besides what I came across here  running the following was the simplest way to dump queries to a log file without restarting  SET global log output    FILE   SET global general log file   Applications MAMP logs mysql general log   SET global general log   1    can be turned off with   SET global general log   0

User · Answer

Note  For mysql-5 6  this won t work  There s a solution that applies to mysql-5 6  if you scroll down or click here    If you don t want or cannot restart the MySQL server you can proceed like this on your running server    Create your log tables on the mysql database     CREATE TABLE  slow log        start time  timestamp NOT NULL DEFAULT CURRENT TIMESTAMP                            ON UPDATE CURRENT TIMESTAMP      user host  mediumtext NOT NULL      query time  time NOT NULL      lock time  time NOT NULL      rows sent  int 11  NOT NULL      rows examined  int 11  NOT NULL      db  varchar 512  NOT NULL      last insert id  int 11  NOT NULL      insert id  int 11  NOT NULL      server id  int 10  unsigned NOT NULL      sql text  mediumtext NOT NULL      thread id  bigint 21  unsigned NOT NULL     ENGINE CSV DEFAULT CHARSET utf8 COMMENT  Slow log      CREATE TABLE  general log        event time  timestamp NOT NULL DEFAULT CURRENT TIMESTAMP                           ON UPDATE CURRENT TIMESTAMP      user host  mediumtext NOT NULL      thread id  bigint 21  unsigned NOT NULL      server id  int 10  unsigned NOT NULL      command type  varchar 64  NOT NULL      argument  mediumtext NOT NULL     ENGINE CSV DEFAULT CHARSET utf8 COMMENT  General log     Enable Query logging on the database   SET global general log   1  SET global log output    table      View the log   select   from mysql general log    Disable Query logging on the database   SET global general log   0

User · Answer

Start mysql with the --log option   mysqld --log log file name   or place the following in your my cnf file   log   log file name   Either one will log all queries to log file name   You can also log only slow queries using the --log-slow-queries option instead of --log   By default  queries that take 10 seconds or longer are considered slow  you can change this by setting long query time to the number of seconds a query must take to execute before being logged

User · Answer

OS   mysql version     uname -a Darwin Raphaels-MacBook-Pro local 15 6 0 Darwin Kernel Version 15 6 0  Thu Jun 21 20 07 40 PDT 2018  root xnu-3248 73 11 1 RELEASE X86 64 x86 64    mysql --version  usr local mysql bin mysql  Ver 14 14 Distrib 5 6 23  for osx10 8  x86 64  using  EditLine wrapper   Adding logging  example  I don t think  var log     is the best path on Mac OS but that worked   sudo vi   usr local mysql-5 6 23-osx10 8-x86 64 my cnf   mysqld  general log   on general log file  var log mysql mysqld general log   Restarted Mysql  Result     sudo tail -f  var log mysql mysqld general log 181210  9 41 04    21 Connect   root localhost on employees            21 Query    mysql-connector-java-5 1 47   Revision  fe1903b1ecb4a96a917f7ed3190d80c049b1de29     SELECT    session auto increment increment AS auto increment increment    character set client AS character set client    character set connection AS character set connection    character set results AS character set results    character set server AS character set server    collation server AS collation server    collation connection AS collation connection    init connect AS init connect    interactive timeout AS interactive timeout    license AS license    lower case table names AS lower case table names    max allowed packet AS max allowed packet    net buffer length AS net buffer length    net write timeout AS net write timeout    query cache size AS query cache size    query cache type AS query cache type    sql mode AS sql mode    system time zone AS system time zone    time zone AS time zone    tx isolation AS transaction isolation    wait timeout AS wait timeout            21 Query SET NAMES latin1            21 Query SET character set results   NULL            21 Query SET autocommit 1            21 Query SELECT USER              21 Query SELECT USER   181210  9 41 10    21 Query show tables 181210  9 41 25    21 Query select count    from current dept emp

User · Answer

For the record  general log and slow log were introduced in 5 1 6  http   dev mysql com doc refman 5 1 en log-destinations html  5 2 1  Selecting General Query and Slow Query Log Output Destinations As of MySQL 5 1 6  MySQL Server provides flexible control over the destination of output to the general query log and the slow query log  if those logs are enabled  Possible destinations for log entries are log files or the general log and slow log tables in the mysql database

[mysql] Log all queries in mysql

Examples related to mysql

Examples related to logging