Solved

enable mysql slow log

Posted on 2014-03-23
8
956 Views
Last Modified: 2014-03-23
using wamp windows7 how can I enable mysql slow log

I want to see a history of all new queries.
0
Comment
Question by:rgb192
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

Locate the my.ini or my.cnf file, open it in Notepad and add the following
in the [mysqld] section

log-slow-queries = [path to the log file]

then restart the mysql service.
See further here
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

Regards,
    Tomas Helgi
0
 

Author Comment

by:rgb192
Comment Utility
log-slow-queries = [path to the log file]

is-this-path-to-log-file
the mysql I am using is mysql5.6.12

whilch file should I edit
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

Edit the my.ini under the mysql5.6.12 directory.
It seems that the slow query log is enabled for the 5.6.12 and it is writing to a table.
You should see in the my.ini file something like
log-output = TABLE
as well as
slow-query-log = 1
http://dev.mysql.com/doc/refman/5.6/en/log-destinations.html

This table can be accessed in the mysql database.
in the mysql command issue
use mysql;
describe slow_log;
select * from slow_log;

Regards,
    Tomas Helgi
0
 

Author Comment

by:rgb192
Comment Utility
I made no changes: is this correct?
 my.ini
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:\mysql\data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[wampmysqld]
port		= 3306
socket		= /tmp/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.6.12
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.6.12/data

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir		= /tmp/		
#log-update 	= /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
port=3306

Open in new window






use mysql;

describe slow_log;
output

select * from slow_log;
no output
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

In line 164 (in the [mysqld] section)
add
slow_query_log=1
log-output = TABLE

you can also have the FILE attribute and then set the logfile
log-output = TABLE, FILE
slow_query_log_file=<path and name of the file you want it to be logged to >

Save the my.ini and
restart the mysqlservice.

You should then see the slow_log table in the mysql database as I described earlier.

Regards,
    Tomas Helgi
0
 

Author Comment

by:rgb192
Comment Utility
[mysqld]
port=3306
slow_query_log=1
log-output = TABLE,FILE
slow_query_log_file=C:\wamp\bin\mysql\mysql5.6.12\data\mysql\slow_query_log_file.txt

after restart of wamp

no append to new file
and
select * from slow_log;
no output
0
 
LVL 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
Comment Utility
Hi!

All queries that takes longer than 10 seconds (by default) are logged.
You can modify that by adding the
long_query_time=<number of seconds>
to the my.ini file
see more
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_long_query_time

Regards,
    Tomas Helgi
0
 

Author Closing Comment

by:rgb192
Comment Utility
[mysqld]
port=3306
slow_query_log=1
log-output = TABLE,FILE
slow_query_log_file=C:\wamp\bin\mysql\mysql5.6.12\data\mysql\slow_query_log_file.txt
long_query_time=0



now I am getting queries from mysql workbench


thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now