Solved

logging in mysql

Posted on 2008-10-24
6
365 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
  • 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:ushastry
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 26

Expert Comment

by:ushastry
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:
ushastry 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

14 Experts available now in Live!

Get 1:1 Help Now