MySQL Server Logs

10.1  The Error Log 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. You can specify where mysqld writes the error log with the --log-error[=file_name] option. If the option is given with no file_name value, mysqld uses the name host_name.err by default. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell> mv host_name.err host_name.err-old shell> mysqladmin flush-logs shell> mv host_name.err-old backup-directory The --log-warnings option or log_warnings system variable can be used to control warning logging to the error log. The default value is enabled (1). Warning logging can be disabled using a value of 0. If the value is greater than 1, aborted connections are written to the error log.   10.2  The 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. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld. Control the general query log at server startup as follows:

• Before 5.1.6, the general query log destination is always a file. To enable the log, start mysqld with the –log[=file_name].
• As of MySQL 5.1.6, the destination can be a file or a table, or both. Start mysqld with the –log[=file_name] and optionally use --log-output to specify the log destination.

  --log-output This option determines the destination for general query and slow query log output. The option value can be given as one or more of the words TABLE, FILE, or NONE. If the option is given without a value, the default is FILE. TABLE select logging to the general_log and slow_log tables in the mysql database as a destination. FILE selects logging to log files as a destination. NONE disables logging. This option selects log output destinations, but does not enable log output.

• As of MySQL 5.1.12, as an alternative to --log or -l, use --general_log[={0|1}] to specify the initial general query log state.
• As of MySQL 5.1.12, use --general_log[={0|1}] to enable or disable the general query log, and optionally --general_log_file=file_name to specify a log file name. The --log option is deprecated.

If you specify no name for the general query log file, the default name is host_name.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. From  MySQL 5.1.12 we can control the general query log at runtime. Use the global general_log and general_log_file system variables. Set general_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set general_log_file to specify the name of the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell> mv host_name.log host_name-old.log shell> mysqladmin flush-logs shell> mv host_name-old.log backup-directory The session sql_log_off variable can be set to ON or OFF to disable or enable general query logging for the current connection.   10.3  The Binary Log 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. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

• For replication purpose.
• Data recovery operations.

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 is not used for statements such as SELECT or SHOW that do not modify data. The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported.

1. statement-based logging (causes logging to be statement-based)
2. row-based logging (causes logging to be row-based) (5.1.5)
3. mixed-base logging (causes logging to use mixed format) (5.1.8)

Statement-Based Logging: Propogates all the SQL statements which are executed in the server. Row-Based Logging: writes events to the binary log that indicate how individual table rows are affected. Mixed-Logging: With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases.

Setting The Binary Log Format

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable: mysql> SET GLOBAL binlog_format = 'STATEMENT'; mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED'; An individual client can control the logging format for its own statements by setting the session value of binlog_format: mysql> SET SESSION binlog_format = 'STATEMENT'; mysql> SET SESSION binlog_format = 'ROW'; mysql> SET SESSION binlog_format = 'MIXED';   To change the global binlog_format value, you must have the SUPER privilege.

Binary Log Options and Variables

--log-bin[=base_name] Enable binary logging. The server logs all statements that change data to the binary log.   binlog_format (5.1.5) This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED.   --log-bin-index[=file_name] The index file for binary log file names. If you omit the file name, and if you did not specify one with --log-bin, MySQL uses host_name-bin.index as the file name.   --binlog-do-db=db_name Logs only the specific database changes.   Statement-based logging: Only those statements are written to the binary log where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not cause cross-database statements such as UPDATE some_db.some_table SET foo='bar' to be logged while a different database (or no database) is selected.   Row-based logging:  Logging is restricted to database db_name. Only changes to tables belonging to db_name are logged; the default database has no effect on this. Suppose that the server is started with --binlog-do-db=sales and row-based logging is in effect, and then the following statements are executed: USE prices; UPDATE sales.february SET amount=amount+100; The changes to the february table in the sales database are logged in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued.   --binlog-ignore-db=db_name   Statement-based logging:  Tells the server to not log any statement where the default database (that is, the one selected by USE) is db_name.   Row-based format:  Tells the server not to log updates to any tables in the database db_name. The current database has no effect.   --binlog_cache_size The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled.   --max_binlog_size If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs. The minimum value is 4096 bytes. The maximum and default value is 1GB.   --sync_binlog If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time. 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 binary log. However, But it is also the slowest choice. mysqld appends a numeric extension to the binary log basename to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size. A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files. To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same basename as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.   Deleting Binary Logs: You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. mysql> RESET MASTER; mysql> PURGE BINARY|MASTER LOGS TO ‘<filename>’; A client that has the SUPER privilege can disable binary logging of its own statements by using a SET sql_log_bin=0 statement. You can display the contents of binary log files with the mysqlbinlog utility. mysql> mysqlbinlog <filename> > log.txt;   Options:   --database=db_name, -d db_name This option causes mysqlbinlog to output entries from the binary log (local log only) that occur while db_name is been selected as the default database by USE.   --host=host_name, -h host_name --password[=password], -p[password] --port=port_num, -P port_num --socket=path, -S path   --result-file=name, -r name Direct output to the given file.   --start-datetime=datetime Start reading the binary log at the first event having a timestamp equal to or later than the datetime argument. The datetime value is relative to the local time zone on the machine where you run mysqlbinlog. Shell > mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003 --stop-datetime=datetime Stop reading the binary log at the first event having a timestamp equal to or later than the datetime argument. This option is useful for point-in-time recovery.   --start-position=N, -j N Start reading the binary log at the first event having a position equal to or greater than N. This option applies to the first log file named on the command line. This option is useful for point-in-time recovery.   --stop-position=N Stop reading the binary log at the first event having a position equal to or greater than N. This option applies to the last log file named on the command line. This option is useful for point-in-time recovery.   10.4  The Slow Query Log The slow query log consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. 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 be different from execution order. To enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option.   --long_query_time (min=1, default=10) If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If you are using the --log-slow-queries option, the query is logged to the slow query log file.

• Before 5.1.6, the slow query log destination is always a file.
• As of MySQL 5.1.6, the destination can be a file or a table.
• As of MySQL 5.1.12, as an alternative to --log-slow-queries, use --slow_query_log[={0|1}] to specify the initial slow query log state. In this case, the default slow query log file name is used.
• As of MySQL 5.1.29, use --slow_query_log[={0|1}] to enable or disable the slow query log, and optionally --slow_query_log_file=file_name to specify a log file name. The --log-slow-queries option is deprecated.

If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server T-SQL Training In Hyderabad