logging in mysql

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?
LVL 1
hennessymAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

javiermorquechoCommented:
I think that the log is only for the functionallity of mysqld.
0
UmeshMySQL Principle Technical Support EngineerCommented:
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
hennessymAuthor Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

UmeshMySQL Principle Technical Support EngineerCommented:
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
hennessymAuthor Commented:
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
UmeshMySQL Principle Technical Support EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.