asked on

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?

javiermorquecho

I think that the log is only for the functionallity of mysqld.

Umesh

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

hennessym

ASKER

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!

Umesh

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.

hennessym

ASKER

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.

ASKER CERTIFIED SOLUTION

Umesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial