Solved

logging in mysql

Posted on 2008-10-24
6
414 Views
Last Modified: 2012-05-05
I'm trying to generate a record of SQL statements executed against a mysql database, similar to Profiler in SQL Server.

I'm using this:
mysqld -lc:\mysql.log

I execute the query, stop the server via mysqld -stop, and view the log.  But the log doesn't contain my queries - it only has this:
mysqld, Version: 5.0.67-community-log (MySQL Community Edition (GPL)). started w
ith:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument

Any suggestions?
0
Comment
Question by:hennessym
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Expert Comment

by:javiermorquecho
ID: 22800785
I think that the log is only for the functionallity of mysqld.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22801174
Hi,

I just tried above logging at my end & it seems to be working for me..

Starting MySQL

E:\MySQLMaster\bin>mysqld-nt --defaults-file=../my-small.ini --log=c:/general.qu
ery.log

Done some queries..Error log..


TCP Port: 0, Named Pipe: (null)
Time                 Id Command    Argument
081025  6:15:52            1 Connect     root@localhost on
                  2 Connect     root@localhost on
                  2 Query       Set names 'utf8'
081025  6:15:53            1 Query       Set names 'utf8'
                  1 Query       set sql_mode=''
                  1 Query       show databases
                  3 Connect     root@localhost on
                  3 Query       Set names 'utf8'
                  3 Query       show databases
                  3 Query       show tables from `information_schema`
                  3 Query       show create table `information_schema`.`CHARACTER_SETS`
                  3 Query       show create table `information_schema`.`COLLATIONS`
                  3 Query       show create table `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY`
                  3 Query       show create table `information_schema`.`COLUMNS`
                  3 Query       show create table `information_schema`.`COLUMN_PRIVILEGES`
                  3 Query       show create table `information_schema`.`KEY_COLUMN_USAGE`
                  3 Query       show create table `information_schema`.`ROUTINES`
                  3 Query       show create table `information_schema`.`SCHEMATA`
                  3 Query       show create table `information_schema`.`SCHEMA_PRIVILEGES`
                  3 Query       show create table `information_schema`.`STATISTICS`
                  3 Query       show create table `information_schema`.`TABLES`
                  3 Query       show create table `information_schema`.`TABLE_CONSTRAINTS`
                  3 Query       show create table `information_schema`.`TABLE_PRIVILEGES`
                  3 Query       show create table `information_schema`.`TRIGGERS`
                  3 Query       show create table `information_schema`.`USER_PRIVILEGES`
                  3 Query       show create table `information_schema`.`VIEWS`
                  3 Query       show tables from `mysql`
                  3 Query       show create table `mysql`.`columns_priv`
                  3 Query       show create table `mysql`.`db`
                  3 Query       show create table `mysql`.`func`
                  3 Query       show create table `mysql`.`help_category`
                  3 Query       show create table `mysql`.`help_keyword`
                  3 Query       show create table `mysql`.`help_relation`
                  3 Query       show create table `mysql`.`help_topic`
                  3 Query       show create table `mysql`.`host`
                  3 Query       show create table `mysql`.`proc`
                  3 Query       show create table `mysql`.`procs_priv`
                  3 Query       show create table `mysql`.`tables_priv`
                  3 Query       show create table `mysql`.`time_zone`
                  3 Query       show create table `mysql`.`time_zone_leap_second`
                  3 Query       show create table `mysql`.`time_zone_name`
                  3 Query       show create table `mysql`.`time_zone_transition`
                  3 Query       show create table `mysql`.`time_zone_transition_type`
                  3 Query       show create table `mysql`.`user`
                  3 Query       show tables from `test`
                  3 Query       show create table `test`.`gameslog`
                  3 Query       show create table `test`.`tablename`
                  3 Query       show create table `test`.`test1`
081025  6:15:54            3 Query       select db, name, type, param_list from mysql.proc
                  3 Query       show create procedure `test`.`spStockStatusReportForPeriod`
                  3 Quit      
                  1 Query       use `test`
081025  6:15:55            1 Query       show table status from `test` where engine is not NULL
081025  6:16:07            1 Query       select * from gamelog
081025  6:16:12            1 Query       select * from gameslog
081025  6:16:55            4 Connect     root@localhost on
                  4 Shutdown  



try running & see available options..

mysqld  --verbose --help

0
 
LVL 1

Author Comment

by:hennessym
ID: 22801691
Thanks for the reply, ushastry.

mysqladmin variables -root -p revealed the log was turned off.  I was able to turn it on via MySQL Administrator, but do you know to do this from the command line?

I'm sure I could find this via mysqld --verbose --help, but there's much more output than what will fit in my buffer, and I can't see the relevant section!
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 26

Expert Comment

by:Umesh
ID: 22860256
What kind of details/info you want to see? Pls can you brief on this.. also note that enabling general log on production server is not recommended..as its eats up lots of space.. initially you may want to have it but later it would create space issues.
0
 
LVL 1

Author Comment

by:hennessym
ID: 22868306
Thanks for the warning about not enabling the general log on a production server.  FWIW, this is a development server.

I was able to enable general logging from mySQL administrator, but for future reference was wondering how to enable it from the command line.

0
 
LVL 26

Accepted Solution

by:
Umesh earned 125 total points
ID: 22868467
Trust me, this is a very simple task. To enable the general query log, just add the --log option at the command-line when starting the server daemon (mysqld). Otherwise what you can do is add it to the configuration file for MySQL but remember one thing...while adding you should not include any of the  leading dashes as shown below.......

[mysqld]
log = /var/logs/mysql/queries.log

After this option is added to the mysqld group, the daemon will need to be restarted for it to take effect. When MySQL starts up again, a simple text file will be created in the directory given.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 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