Turning on MySQL general logging

Sometimes you want to see what queries are hitting the server. It turns out that turning on MySQL logging is as easy as adding two options to the server config:

mysql.server start --general_log=1 --general_log_file=mysqllog.txt

Afterwards, you can see the contents of the log file:

$ cat /usr/local/var/mysql/mysqllog.txt
/usr/local/Cellar/mysql/5.7.9/bin/mysqld, Version: 5.7.9-log (Homebrew). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
2015-11-20T08:35:40.881750Z    2 Connect    root@localhost on  using Socket
2015-11-20T08:35:40.881971Z    2 Query    select @@version_comment limit 1
2015-11-20T08:35:45.181450Z    2 Query    show databases
2015-11-20T08:35:51.492888Z    2 Query    SELECT DATABASE()
2015-11-20T08:35:51.494023Z    2 Init DB    mysql
2015-11-20T08:35:51.495439Z    2 Query    show databases
2015-11-20T08:35:51.495893Z    2 Query    show tables
2015-11-20T08:35:51.496365Z    2 Field List    columns_priv
2015-11-20T08:35:51.496621Z    2 Field List    db
2015-11-20T08:35:51.496997Z    2 Field List    event
2015-11-20T08:35:51.497403Z    2 Field List    func
2015-11-20T08:35:51.497583Z    2 Field List    general_log
2015-11-20T08:35:51.503601Z    2 Field List    help_category
2015-11-20T08:35:51.505020Z    2 Field List    help_keyword
2015-11-20T08:35:51.506380Z    2 Field List    help_relation
2015-11-20T08:35:51.507734Z    2 Field List    help_topic
2015-11-20T08:35:51.509058Z    2 Field List    innodb_index_stats
2015-11-20T08:35:51.511149Z    2 Field List    innodb_table_stats
2015-11-20T08:35:51.512539Z    2 Field List    ndb_binlog_index
2015-11-20T08:35:51.513863Z    2 Field List    plugin
2015-11-20T08:35:51.514021Z    2 Field List    proc
2015-11-20T08:35:51.515487Z    2 Field List    procs_priv
2015-11-20T08:35:51.515698Z    2 Field List    proxies_priv
2015-11-20T08:35:51.515898Z    2 Field List    servers
2015-11-20T08:35:51.516114Z    2 Field List    slave_master_info
2015-11-20T08:35:51.519151Z    2 Field List    slave_relay_log_info
2015-11-20T08:35:51.521693Z    2 Field List    slave_worker_info
2015-11-20T08:35:51.524316Z    2 Field List    slow_log
2015-11-20T08:35:51.530419Z    2 Field List    tables_priv
2015-11-20T08:35:51.530690Z    2 Field List    time_zone
2015-11-20T08:35:51.530885Z    2 Field List    time_zone_leap_second
2015-11-20T08:35:51.531070Z    2 Field List    time_zone_name
2015-11-20T08:35:51.531213Z    2 Field List    time_zone_transition
2015-11-20T08:35:51.531398Z    2 Field List    time_zone_transition_type
2015-11-20T08:35:51.531641Z    2 Field List    user
2015-11-20T08:35:55.758983Z    2 Query    show tables
2015-11-20T08:36:00.748956Z    2 Query    select * from user
2015-11-20T08:36:12.604808Z    2 Query    select Host,User,Password from user
2015-11-20T08:36:15.348311Z    2 Quit

NOTE: I have no idea how turning this on affects server performance, so, please don’t turn it on in production, especially in a high volume environment.

NOTE2: Also, disk space, this has the potential to flood your disk.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.