Solved

logging in mysql

Posted on 2008-10-24
6
387 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql Encryption with PHP 8 103
how to get top 5 assets which having more tickets raised using sql 5 48
mcrypt_create_iv() is deprecated 4 166
How to count in a table in php 22 36
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

803 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