All MySQL Server log files are written to the data directory by default.
There are five types of MySQL Server log files.
1) Error log
2) General query log
3) Binary log
4) Relay log
5) Slow query log
http://dev.mysql.com/doc/refman/5.6/en/server-logs.html
Before you go further, you must understand certain terms used.
Server System variables
The MySQL Server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. There are 2 types of Server System variables.
• Global variables – Affects the overall operation of the server. When the server starts, it initializes all global variables to their default values. These defaults can be changed by options specified on the command line or in an option file.
• Session Variables – The server also maintains a set of session variables for each client that connects. The client’s session variables are initialized at connect time using the current values of the corresponding global variables.
NOTE: A given system variable can have both a Global value and a Session value.
Server Options
These are program options. When a MySQL Server starts, you can specify these options in an option file or on the command line.
Dynamic Variables
Variables that can be changed dynamically while the server is running by means of the SET statement. This enables you to modify operation of the server without having to stop and restart it. This can include both Server System variables and Server Options.
Static variables
Variables that cannot be changed while the server is running. This can include both Server System variables and Server Options.
NOTE:
Below you are provided with Global variables that can be used to control different types of logging. These variables can be set in the option file /etc/my.cnf. You must set them in required section, depending on what program you are using to start the MySQL Server. MySQL Server is started using any of the programs mysql.server, mysqld_safe or mysqld. Each program looks for options in configuration file, from different sections. View them below.
mysql.server – Reads options from sections [mysqld], [mysql.server]
mysqld_safe – Reads options from sections [mysqld], [server], [mysqld_safe], [safe_mysqld]
mysqld – Reads options from sections [mysqld], [server]
1) Error log
Problems encountered on starting, running, or stopping mysqld.
ENABLED by default.
The error log files have a name that follows the syntax HOSTNAME.err.
example: localhost.localdomain.err
It contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. For example, if mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log. On some OSes, the error log contains a stack trace if mysqld dies. The trace can be used to determine where mysqld died.
It can be controlled by 2 Global variables.
log-error[=FILENAME/PATH_TO_FILENAME] TYPE: Server Option, Static The value can be a filename OR location of the error log including filename. If there is no argument, error message is written to STDOUT. log-warnings[=NUMERIC_VALUE] TYPE: Server Option, Dynamic ENABLED by default. Default value is 1, also made effective by no argument. Can be disabled by a value of 0. For 32-bit platform, allowable range is from 0 to 4294967295. For 64-bit platfrom, allowable range is from 0 to 18446744073709547520. If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. It produces warnings across a spectrum of server activities. It results in the server to print more messages to the error log about what it is doing. Regarding to replication, server generates warnings that it succeeded in reconnecting after a network/connection failure, and informs you as to how each slave thread started.
http://dev.mysql.com/doc/refman/5.6/en/error-log.html
2) General query log
Established client connections and statements received from clients
DISABLED by default.
The log files have a name that follows the syntax HOSTNAME.log.
example: localhost.localdomain.log
This 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. It 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 contrasts to the binary log, for which statements are written after they are executed but before any locks are released. Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.
It can be controlled by 3 Global variables.
general-log[={0,1}]
TYPE: Server System Variable, Dynamic
Default value is 0, also made effective by no argument. Can be enabled by a value of 1. Decides whether the general query log is enabled. This value is depend on 2 other variables general_log_file and log-output.
general_log_file[=FILENAME]
TYPE: Server System Variable, Dynamic
Default value follows the syntax HOSTNAME.log, also made effective by no argument. You can specify an alternate filename. Name of the general query log file.
log-output=PATH_TO_FILE/TABLE/NONE
TYPE: Server System Variable, Dynamic
Default value is PATH_TO_FILE. If a value for TABLE is given, logs are written to it. NONE disables any logging. Defines the destination for general & slow query log output.
http://dev.mysql.com/doc/refman/5.6/en/query-log.html
3) Binary log
Statements that change data.
ENABLED by default.
The log files have a name that follow the syntax HOSTNAME-bin.nnnnnn, along with an index file that has filename syntax HOSTNAME-bin.index.
example: localhost.localdomain-bin.000001, localhost.localdomain-bin.index
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. But it is not used for statements such as SELECT or SHOW that do not modify data. It also contains information about how long each statement took that updated data.
Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order. You can display the contents of binary log files with the mysqlbinlog utility. You can also reprocess the statements in the log for a recovery operation.
The binary log has 2 important purposes.
• For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.
• Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
There are 3 formats supported by Binary logs.
• Statement based logging
Master writes events to the binary log on SQL statements. This is the default binary logging statement. With statement-based replication, there may be issues with replicating non-deterministic statements. If MySQL cannot guarantee that the statement can be replicated using statement-based logging, it marks the statement as potentially unreliable and issues the warning, “Statement may not be safe to log in statement format”.
• Row based logging
Master writes events to the binary log that indicate how individual table rows are affected.
• Mixed base logging
Statement-based logging is used by default, but the logging mode switches automatically to row-based under the following conditions.
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement. The binary log should be protected because logged statements might contain passwords.
It can be controlled by 19 Global variables as shown at http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html.
http://dev.mysql.com/doc/refman/5.6/en/binary-log.html
4) Relay log
Data changes received from a replication master server.
ENABLED by default, If replication is ENABLED.
The log files have a name that follow the syntax HOSTNAME-relay-bin.nnnnnn, along with an index file that has filename syntax HOSTNAME-relay-bin.index.
example: localhost.localdomain-relay-bin.000001, localhost.localdomain-relay-bin.index
There is also a relay log information file that is named as relay-log.info.
The relay log is used only on slave replication servers, to hold data changes from the master server that must also be made on the slave. It is just like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files. They have the same format as binary log files and can be read using mysqlbinlog.
If you are using replication, you should not delete old binary log files on the master until you are sure that no slave still needs to use them.
It can be controlled by 11 Global variables.
relay-log=FILENAME TYPE: Replication Slave Option, Static Specifies the basename for relay log. This value is also used as the basename for the relay log index file, unless a different value for it specified using relay-log-index option. relay-log-index=FILENAME TYPE: Replication Slave Option, Static Specifies the basename for relay log index file. This value is also used for the relay log, unless a different value for it specified using relay-log option. relay-log-info-file[=FILENAME] TYPE: Replication Slave Option Default name is relay-log.info, also made effective by no argument. But you can specify an alternate filename. Defines the name to use for the file in which the slave records information about the relay logs. master-info-file[=FILENAME] TYPE: Replication Slave Option Default name is master.info, also made effective by no argument. Defines the name to use for the file in which the slave records information about the master. log-slave-updates TYPE: Replication Slave Option, Static Tells the slave to log the updates performed by its SQL thread to its own binary log. Normally, a slave does not log to its own binary log any updates that are received from a master server. It can be particularly useful when you want to setup a chain of replication servers. log-warnings[=NUMERIC] TYPE: Server System Variable, Server Option, Dynamic Default value is 1, also made effective by no argument. Can be disabled by a value of 0. For 32-bit platform, allowable range is from 0 to 4294967295. For 64-bit platfrom allowable range is 0 to 18446744073709547520. If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. Regarding to replication, server generates warnings that it succeeded in reconnecting after a network/connection failure, and informs you as to how each slave thread started. The effects of this option are not limited to replication. It produces warnings across a spectrum of server activities. It results in the server to print more messages to the error log about what it is doing. max_relay_log_size[=SIZE] TYPE: Server System Variable, Dynamic Default size is 1GB, also made effective by no argument. Allowable range is from 0 to 1073741824. Defines the size at which the server rotates relay log files automatically. relay_log_purge={0,1} TYPE: Server System Variable, Dynamic Default value is 1, also made effective by no argument. Decides whether to allow automatic purging of relay logs as soon as they are no longer needed. relay-log-recovery={0,1} TYPE: Replication Slave Option, Static Default value is 0, also made effective by no argument. Decides whether to allow automatic relay log recovery immediately following server startup, which means that the replication slave discards all unprocessed relay logs and retrieves them from the replication master. This should be used following a crash on the replication slave to ensure that no possibly corrupted relay logs are processed. relay_log_space_limit[=SIZE] TYPE: Replication Slave Option, Static Default value is 0 which means 'no limit', also made effective by no argument. Defines the total size in bytes of all relay logs on the slave. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note the following points. • There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs because not doing so would cause a deadlock. • Do not set this value to less than 2 times the value of max_relay_log_size OR max_binlog_size(if max_relay_log_size=0). In that case I/O thread waits for free space because, the specified value has exceeded but SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to ignore the specified value temporarily. sync_relay_log[=NUMERIC] TYPE: Replication Slave Option, Dynamic Default value is 0, also made effective by no argument. Can be enabled by a value of 1 or greater. If enabled, MySQL Server synchronizes its relay log to disk (using fdatasync()) after writes to the relay log. For value of 0, the server relies on the operating system to flush the relay log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the relay log. However, it is also the slowest choice. There is one write to the relay log per statement if autocommit is enabled, and one write per transaction otherwise. sync_relay_log_info[=NUMERIC] TYPE: Replication Slave Option, Dynamic Default value is 0, also made effective by no argument. Can be enabled by a value of 1 or greater. If enabled, MySQL Server synchronizes its relay-log.info file to disk (using fdatasync()) after writes to that file. For value of 0, the server relies on the operating system to flush the relay log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the relay log. However, it is also the slowest choice. There is one write to the relay log per statement if autocommit is enabled, and one write per transaction otherwise.
http://dev.mysql.com/doc/refman/5.6/en/slave-logs-relaylog.html
5) Slow query log
Queries that took more than long_query_time seconds to execute.
DISABLED by default.
The log files have a name that follows the syntax HOSTNAME-slow.log.
example: localhost.localdomain-slow.log
The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. It can be used to find queries that take a long time to execute and can be used for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize it.
mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order. Also, the slow query log should be protected because logged statements might contain passwords.
It can be controlled by 9 Global variables.
slow-query-log[={0,1}]
TYPE: Server System Variable, Dynamic
Default value is 0, also made effective by no argument. Can be enabled by a value of 1. Decides whether to enable or disable slow query log.
slow_query_log_file[=FILENAME]
TYPE: Server System Variable, Dynamic
Default filename follows the syntax HOSTNAME-slow.log, also made effective by no argument. But you can specify an alternate filename. Name of the slow query log file.
log-output=PATH_TO_FILE/TABLE/NONE
TYPE: Server System Variable, Dynamic
Default value is PATH_TO_FILE. If a value for TABLE is given, logs are written to it. NONE disables any logging. Defines the destination for general & slow query log output.
log-slow-admin-statements[={OFF,ON}]
TYPE: Server Option
Default value is OFF, also made effective by no argument. Can be enabled by the value ON. Include slow administrative statements in the statements written to the slow query log. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE.
log-queries-not-using-indexes[={OFF,ON}]
TYPE: Server Option, Dynamic
Default value is OFF, also made effective by no argument. Can be enabled by the value ON. Decides whether queries that do not use indexes are logged to the slow query log.
log-slow-slave-statements[={OFF,ON}]
TYPE: Replication Slave Option
Default value is OFF, also made effective by no argument. Can be enabled by the value ON. Decides whether to log queries that have taken more than long_query_time seconds to execute on the slave.
log-short-format[={FALSE,TRUE}]
TYPE: Server Option
Default value is FALSE, also made effective by no argument. Can be enabled by the value TRUE. Decides whether to log less information to the binary log and slow query log, if they have been activated.
long_query_time[=NUMERIC]
TYPE: Server System Variable, Dynamic
Default value is 10, also made effective by no argument. Minimum value is 0. Time in seconds to execute an SQL statement. Time is measured in real time, not CPU time. The time to acquire the initial locks is not counted as execution time. If a query takes longer than this many seconds, the server increments the Slow_queries status variable.
Time can also be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.
min-examined-row-limit[=NUMERIC]
TYPE: Server System Variable, Dynamic
Default value is 0, also made effective by no argument. For 32-bit platform, range is 0 to 4294967295. For 64-bit platform, range is 0 to 18446744073709547520. Minimum number of rows to examine. Queries that examine fewer than this number of rows are not logged to the slow query log.
http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
You may go back to the following section.
Installing MySQL Server
