?
Solved

logging in mysql

Posted on 2008-10-24
6
Medium Priority
?
424 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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