LOGGING

LOGGING

                MySQL has many different types of Log Files. This log files is activated when you use and configure my.ini  as follows:

log-error=[name].log

log=[name].log

log-bin=[name].log

log-slow-queries=[name].log

1. The error log this is the place to look for problems with starting, running, or stopping MySQL.

2. The query logs all connections and executed queries are logged here.

3. The binary update log All SQL statements that change data are stored.

4. The slow query log all queries that took more than long_query_time to execute, or that didn't make use of any indexes, are logged here.

The Error Log

                MySQL error log is called mysql.err on windows and it is located in the data directory where your MySQL where found, usually in (C:\mysql\bin).

It contains startup and shutdown information and any critical errors that occur while running. It will log if the server died and was automatically restarted or if MySQL notices that a table needs to be automatically checked or repaired. The log may also contain a stack trace when MySQL dies. A sample error log follows:

120709 14:51:14 [Note] Plugin 'FEDERATED' is disabled.

120709 14:51:14  InnoDB: Initializing buffer pool, size = 8.0M

120709 14:51:14  InnoDB: Completed initialization of buffer pool

120709 14:51:14  InnoDB: Started; log sequence number 0 44233

120709 14:51:14 [Note] Event Scheduler: Loaded 0 events

120709 14:51:14 [Note] mysqld: ready for connections.

Version: '5.1.63-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

120709 15:02:44 [Note] mysqld: Normal shutdown

The Binary Log

                The binary update logs contain all the SQL statements that update the database, as well as how long the query took to execute and a timestamp of when the query was processed. Statements are logged in the same order as they are executed (after the query is complete but before transactions are completed or locks removed). Updates that have not yet been committed are placed in a cache first.

The binary update log is also useful for restoring backups and for when you are replicating a slave database from a master.

Binary update logs start with an extension 001. The binary update index file contains a list of all the binary logs used to date. A sample could be as follows:

.\mcbin.000001

.\mcbin.000002

.\mcbin.000003

You can delete all the unused binary update logs with RESET MASTER:

mysql> RESET MASTER;

Query OK, 0 rows affected (0.00 sec)

View using a text  editor:

þbinbúO   f   j    

5.1.63-community-log                              búO8

   S ­€úO   S   ½           @          std

mcdo create database mcdoé€úO   Q             @          std

mcdo drop database mcdoIúO   S   a         

@          std

mcdo create database mcdo‚úOt   

But you can see this using mysqlbinlog:

C:\Program Files\MySQL\data>..\bin\mysqlbinlog mcbin.000003

The Slow Query Log

                All SQL statements that take longer to execute than long_query_time are logged.

A sample slow query log follows:

mysqld, Version: 5.1.63-community-log (MySQL Community Server (GPL)). started with:

TCP Port: 0, Named Pipe: (null)

Time                 Id Command    Argument

mysqld, Version: 5.1.63-community-log (MySQL Community Server (GPL)). started with:

TCP Port: 0, Named Pipe: (null)

Time                 Id Command    Argument

mysqld,

The General Query Log

General query log established a connection between clients.

                --log[=file_name] or -l [file_name]

A sample general query log follows:

Query   create database pogi

120709 15:12:14                    1 Query            create database pogi

120709 15:12:33                    1 Query            show databases

120709 15:12:49                    1 Query            create database pogi

120709 15:12:56                    1 Quit