MySQL Server has several logs that can help you find out what activity is taking place.
- By Default , No Logs are enabled in MySQL.
- By default, the server writes files for all enabled logs in the data directory.
- Logs can be flush by Issuing FLUSH LOGS statement.
- Binary log is flushed when its size reaches the value of the max_binlog_size system variable.
- General query log and slow query log can be enable and disable during run time. We can also tell server to write general query and slow query entries to log tables, log files or both.
- Relay log is only used on replication servers, to hold the data changes from the master server that must also be made on slave.
General Query Log
- The general query log is a general record of what mysqld is doing.
- The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
- Can be very useful when you suspect an error in a client and want to know exactly what the client
sent to mysqld.
- mysqld writes statements to the query log in the order that it receives them, which might differ from the
order in which they are executed. This logging order is in contrast with that of the binary log, for which
statements are written after they are executed but before any locks are released.
- If the general query log file is enabled but no name is specified, the default name is host_name.log and
the server creates the file in the same directory where it creates the PID file. If a name is given, the server
creates the file in the data directory unless an absolute path name is given to specify a different directory.
- Enable general query log:
SET global general_log = 1;
SET global log_output = ‘table’;
2. View general query log:
Select * from mysql.general_log;
3. Disable general query log:
SET global general_log = 0;
- The error log contains information indicating when mysqld was started and stopped and also any critical
errors that occur while the server is running.
- If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
- On some Operating systems, the error log contains a stack trace if mysqld dies. The trace can be used to
determine where mysqld died.
- If mysqld_safe is used to start mysqld and mysqld dies unexpectedly, mysqld_safe notices this, restarts mysqld, and writes a restarted mysqld message to the error log.
- At runtime, log_error system variable indicates the error log file name if error output is written to a file.
- If you specify –log-error in an option file in a [mysqld], [server], or [mysqld_safe] section,
mysqld_safe will find and use the option.