Solved

enable mysql slow log

Posted on 2014-03-23
8
978 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 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39948786
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
ID: 39948809
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 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39948857
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:rgb192
ID: 39948938
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39948964
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
ID: 39949135
[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 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 39949180
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
ID: 39949361
[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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mySQL. SQL query. Substitute for Numeric key word. 3 61
Selecting specific rows 3 53
myqsl update statement on phpMyAdmin 8 30
change database name 2 31
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…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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