MySQL 日志文件 说明

MySQL 5.5 官方文档上有关日志的分类：

 

 

By default, nologs are enabled. The following log-specific sections provide information about the server options that enable logging.

--默认情况下，没有启动任何log，可以通过如下log 选项来启动相关的log

 

By default, theserver writes files for all enabled logs in the data directory. You can forcethe server to close and reopen the log files (or in some cases switch to a newlog file) by flushing the logs. Log flushing occurs when you issue a FLUSH LOGSstatement; execute mysqladmin with a flush-logs or refresh argument; or executemysqldump with a --flush-logs or --master-data option.

--默认情况下，所有的log都会放在data directory 目录下（/var/lib/mysql）, 可以通过flush log命令，强制关闭server，然后reopen log file。 当执行flushlog命令时会触发log flush，或者通过mysqladmin命令，也可以。

 

In addition, thebinary log is flushed when its size reaches the value of the max_binlog_sizesystem variable.

--对于binary log，当log 达到max_binlog_size时也会触发flush。

 

You can controlthe general query and slow query logs during runtime. You can enable or disablelogging, or change the name of the log file. You can tell the server to writegeneral query and slow query entries to log tables, log files, or both.

       可以在db 运行时操作generalquery 和slow query log，比如启用和禁用log，改变log file 名称，可以选择general query 和slow是存放在logtables 还是log files。

 

一.设置 General Query 和 SlowQuery Log 的输出位置

MySQL Serverprovides flexible control over the destination of output to the general querylog and the slow query log. Possible destinations for log entries are log filesor the general_log and slow_log tables in the mysql database. If logging isenabled, either or both destinations can be selected.

--MySQL 对general query log 和 slow query log 的控制比较灵活，我们可以指定这2个log输出到log file 或者 输入到mysql 数据库的general_log 和 slow_log 2张日志表里。 也可以同时使用，即输出到log file 和 log table。

 

Currently,logging to tables incurs significantly more server overhead than logging tofiles. If you enable the general log or slow query log and require highestperformance, you should use file logging, not table logging.

       --如果输出到log table，可能会带来一定的性能问题，所以最好写入logfile。

 

1.1 Log control at server startup.

The --log-outputoption specifies the destination for log output, if logging is enabled. Thisoption does not in itself enable the logs. Its syntax is--log-output[=value,...]:

（1） If--log-output is given with a value, the value should be a comma-separated listof one or more of the words TABLE (log to tables), FILE (log to files), or NONE(do not log to tables or files). NONE, if present, takes precedence over anyother specifiers.

--log-output 参数可以指定的参数有：TABLE,FILE,NONE

（2） If--log-output is omitted or given without a value, the default logging destinationis FILE.

       如果启用了该参数，缺没有提供参数值，那么默认使用FILE.

 

--log-output 参数指定了log的输出位置，下面启用general log 和 slow query log。

 

The general_logsystem variable, if given, enables logging to the general query log for theselected log destinations. If specified at server startup, general_log takes anoptional argument of 1 or 0 to enable or disable the log. To specify a filename other than the  default  for file logging, set the general_log_filevariable.

设置general_log 为1 或 0 来控制启用和禁用generalquery log。

 

Similarly, theslow_query_log variable, if given, enables logging to the slow query log forthe selected destinations and setting slow_query_log_file specifies a file namefor file logging. If either log is enabled, the server opens the correspondinglog file and writes startup messages to it. However, further logging of queriesto the file does not occur unless the FILE log destination is selected.

       对应slow query log 的参数是：slow_query_log.

 

相关的查询如下：

mysql> show variables like'%general_log%';

+------------------+-------------------------+

| Variable_name    | Value                   |

+------------------+-------------------------+

| general_log      | OFF                     |

| general_log_file |/var/lib/mysql/rac2.log |

+------------------+-------------------------+

2 rows in set (0.00 sec)

 

mysql> show variables like'%slow_query_log%';

+---------------------+------------------------------+

| Variable_name       | Value                        |

+---------------------+------------------------------+

| slow_query_log      | OFF                          |

| slow_query_log_file |/var/lib/mysql/rac2-slow.log |

+---------------------+------------------------------+

2 rows in set (0.00 sec)

 

mysql> show variables like '%log_output%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output    | FILE |

+---------------+-------+

1 row in set (0.00 sec)

 

1.2 Log control at runtime.

Several systemvariables are associated with log tables and files and enable runtime controlover logging:

（1）The globallog_output system variable indicates the current logging destination. It can bemodified at runtime to change the destination.

（2）The globalgeneral_log and slow_query_log variables indicate whether the general query logand slow query log are enabled (ON) or disabled (OFF). You can set thesevariables at runtime to control whether the logs are enabled.

（3）The globalgeneral_log_file and slow_query_log_file variables indicate the names of thegeneral query log and slow query log files. You can set these variables atserver startup or at runtime to change the names of the log files.

（4）The sessionsql_log_off variable can be set to ON or OFF to disable or enable general querylogging for the current connection.

       --以上几点总结成一句话，就是可以在DB 运行时修改相关参数，这些参数在1.1 的示例中已经列出。

 

 

The use of tables for logoutput offers the following benefits:

--使用table 来存放log 有如下好处：

（1） Log entries have a standardformat. To display the current structure of the log tables, use thesestatements:

SHOW CREATETABLE mysql.general_log;

SHOW CREATETABLE mysql.slow_log;

（2） Log contents are accessiblethrough SQL statements. This enables the use of queries that select only thoselog entries that satisfy specific criteria. For example, to select log contentsassociated with a particular client (which can be useful for identifyingproblematic queries from that client), it is easier to do this using a logtable than a log file.

（3） Logs are accessible remotelythrough any client that can connect to the server and issue queries (if theclient has the appropriate log table privileges). It is not necessary to log into the server host and directly access the file system.

 

The log tableimplementation has the following characteristics:

--log table 具有如下特征：

（1）In general,the primary purpose of log tables is to provide an interface for users toobserve the runtime execution of the server, not to interfere with its runtimeexecution.

（2）CREATE TABLE,ALTER TABLE, and DROP TABLE are valid operations on a log table. For ALTERTABLE and DROP TABLE, the log table cannot be in use and must be disabled, asdescribed later.

（3） By default,the log tables use the CSV storage engine that writes data in comma-separatedvalues format. For users who have access to the .CSV files that contain logtable data, the files are easy to import into other programs such asspreadsheets that can process CSV input.

--log tables 默认使用CSV 引擎。

 

The log tablescan be altered to use the MyISAM storage engine. You cannot use ALTER TABLE toalter a log table that is in use. The log must be disabled first. No enginesother than CSV or MyISAM are legal for the log tables.

       --可以修改log table 使用MyISAM 引擎，但不能在线进行修改，需要先disable。

 

（1） To disable logging so that you canalter (or drop) a log table, you can use the following strategy. The exampleuses the general query log; the procedure for the slow query log is similar butuses the slow_log table and slow_query_log system variable.

SET @old_log_state = @@global.general_log;

SET GLOBAL general_log = 'OFF';

ALTER TABLE mysql.general_log ENGINE = MyISAM;

SET GLOBAL general_log = @old_log_state;

（2） TRUNCATE TABLE is a validoperation on a log table. It can be used to expire log entries.

（3） RENAME TABLE is a valid operationon a log table. You can atomically rename a log table (to perform log rotation,for example) using the following strategy:

USE mysql;

CREATE TABLE IFNOT EXISTS general_log2 LIKE general_log;

RENAME TABLEgeneral_log TO general_log_backup, general_log2 TO general_log;

（4） As of MySQL 5.5.7, CHECK TABLE isa valid operation on a log table.

（5） LOCK TABLES cannot be used on alog table.

（6） INSERT, DELETE, and UPDATE cannotbe used on a log table. These operations are permitted only internally to theserver itself.

（7） FLUSH TABLES WITH READ LOCK andthe state of the global read_only system variable have no effect on log tables.The server can always write to the log tables.

（8） Entries written to the log tablesare not written to the binary log and thus are not replicated to slave servers.

（9） To flush the log tables or logfiles, use FLUSH TABLES or FLUSH LOGS, respectively.

（10） Partitioning of log tables is notpermitted.

 

二. Error Log

The error logcontains information indicating when mysqld was started and stopped and alsoany critical errors that occur while the server is running. If mysqld notices atable that needs to be automatically checked or repaired, it writes a messageto the error log.

--error log 包含mysqld 启动和关闭，已经运行的任何criticalerror 信息，如果mysqld 检测到某个table 需要自动checked或者repaired，也会将信息写入log。

 

On someoperating systems, the error log contains a stack trace if mysqld dies. Thetrace can be used to determine where mysqld died. See MySQL Internals: Porting.

--在某些系统下，如果mysqld dieserror log也会包含相关的stack trace。

 

You can specifywhere mysqld writes the error log with the --log-error[=file_name] option. Ifthe option is given with no file_name value, mysqld uses the name host_name.errby default. The server creates the file in the data directory unless an absolutepath name is given to specify a different directory.

--可以通过—log-error 参数来指定error log 的位置，如果在没有指定error log，那么默认使用host_name.err. 默认存放在data directory目录下。

 

[root@rac2 lib]# ps -ef|grep mysqld

root    31402     1  0 15:21 pts/2    00:00:00 /bin/sh /usr/bin/mysqld_safe--datadir=/var/lib/mysql --pid-file=/var/lib/mysql/rac2.pid

mysql   31663 31402  0 15:22 pts/2    00:00:01 /usr/sbin/mysqld --basedir=/usr--datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/rac2.err--pid-file=/var/lib/mysql/rac2.pid --socket=/var/lib/mysql/mysql.sock--port=3306

root    32210  3735  0 20:19 pts/2    00:00:00 grep mysqld

[root@rac2 lib]#

 

如这里的：--log-error=/var/lib/mysql/rac2.err，这个是默认参数。

 

If you do notspecify --log-error, or (on Windows) if you use the --console option, errorsare written to stderr, the standard error output. Usually this is yourterminal.

       --如果没有指定error log，则errors 会写入stderr。

 

If you flush thelogs using FLUSH LOGS or mysqladmin flush-logs and mysqld is writing the errorlog to a file (for example, if it was started with the --log-error option), theeffect is version dependent:

 

（1） As of MySQL 5.5.7, the servercloses and reopens the log file. To rename the file, you can do so manuallybefore flushing. Then flushing the logs reopens a new file with the originalfile name. For example, you can rename the file and create a new one using the followingcommands:

shell> mv host_name.errhost_name.err-old

shell> mysqladmin flush-logs

shell> mv host_name.err-oldbackup-directory

       在MySQL 5.5.5  之后，可以使用如上命令进行error log的一个备份操作。 Mysqladmin 的flush-logs会重新使用原来的文件名。 所以我们只需要对原文件进行MV就可以了。 这个和Oracle 的alert log类似。

 

（2）Prior to MySQL 5.5.7, the serverrenames the current log file with the suffix -old, then creates a new empty logfile. Be aware that a second log-flushing operation thus causes the originalerror log file to be lost unless you save it under a different name.

 

No error log renaming occurs when the logs are flushed in any case if the server is notwriting to a named file.

 

If you usemysqld_safe to start mysqld, mysqld_safe arranges for mysqld to write errormessages to a log file or to syslog mysqld_safe has three error-loggingoptions, --syslog, --skip-syslog, and --log-error. The default with no logging optionsor with --skip-syslog is to use the default log file. To explicitly specify useof an error log file, specify --log-error=file_name to mysqld_safe, andmysqld_safe will arrange for mysqld to write messages to a log file. To usesyslog instead, specify the --syslog option.

 

If you specify--log-error in an option file in a section that mysqld reads, mysqld_safe alsowill find and use the option.

If mysqld_safeis used to start mysqld and mysqld dies unexpectedly, mysqld_safe notices thatit needs to restart mysqld and writes a restarted mysqld message to the errorlog.

The--log-warnings option or log_warnings system variable can be used to controlwarning logging to the error log. The default value is enabled (1). Warninglogging can be disabled using a value of 0. If the value is greater than 1,aborted connections are written to the error log, and access-denied errors fornew connection attempts are written.

 

三. General Query Log

The generalquery log is a general record of what mysqld is doing. The server writesinformation to this log when clients connect or disconnect, and it logs eachSQL statement received from clients. The general query log can be very usefulwhen you suspect an error in a client and want to know exactly what the clientsent to mysqld.

--general query log 记录了clients的connect和disconnect，以及从client接收的每个SQL statement 。

mysqld writesstatements to the query log in the order that it receives them, which mightdiffer from the order in which they are executed.

--query log写statements的顺序和执行顺序可能不一致。

 

This loggingorder contrasts to the binary log, for which statements are written after theyare executed but before any locks are released. (Also, the query log containsall statements, whereas the binary log does not contain statements that onlyselect data.)

--general query log 和 binary log的不同在于，querylog 包含所有的statements，而binary log 不包含statement，只包含select data。

 

By default, thegeneral query log is disabled. Use --general_log[={0|1}] to specify the initialgeneral query log state explicitly. With no argument or an argument of 1,--general_log enables the log. With an argument of 0, this option disables thelog. You can use --general_log_file=file_name to specify a log file name. Youcan also use --log-output to specify the log destination (as described inSection 5.2.1, “Selecting General Query and Slow Query Log OutputDestinations”). The older options to enable the general query log, --log and-l, are deprecated.

--默认情况下，general query log是禁用的。 使用—general_log 来启用该功能。

 

If you specifyno name for the general query log file, the default name is host_name.log. Theserver creates the file in the data directory unless an absolute path name isgiven to specify a different directory.

--默认情况下，general query log 存放在data directory 目录下，默认文件名是host_name.log.

 

To control thegeneral query log at runtime, use the global general_log and general_log_filesystem variables. Set general_log to 0 (or OFF) to disable the log or to 1 (orON) to enable it. Set general_log_file to specify the name of the log file. Ifa log file already is open, it is closed and the new file is opened.

--如果在DB 运行时修改log 设置，可以通过set general_log 和 general_log_file 2个参数。

 

When the generalquery log is enabled, the server writes output to any destinations specified bythe --log-output option or log_output system variable. If you enable the log,the server opens the log file and writes startup messages to it. However,further logging of queries to the file does not occur unless the FILE logdestination is selected. If the destination is NONE, no queries are written evenif the general log is enabled. Setting the log file name has no effect onlogging if the log destination value does not contain FILE.

 

Server restartsand log flushing do not cause a new general query log file to be generated(although flushing closes and reopens it). You can rename the file and create anew one by using the following commands:

shell> mv host_name.loghost_name-old.log

shell> mysqladmin flush-logs

shell> mv host_name-old.logbackup-directory

       --和error log 一样，可以进行备份操作，然后用flush log 命令重新开始记录。

 

You can also rename the general query logfile at runtime by disabling the log:

--在runtime时，也可以通过先disable 然后enabled的方式来rename log。

 SET GLOBALgeneral_log = 'OFF';

With the log disabled, rename the log fileexternally; for example, from the command line. Then enable the log again:

SET GLOBALgeneral_log = 'ON';

 

This method works on any platform and doesnot require a server restart.

The session sql_log_off variable can be setto ON or OFF to disable or enable general query logging for the currentconnection.

The general query log should be protectedbecause logged statements might contain passwords.

 

四. Slow Query Log

The slow querylog consists of all SQL statements that took more than long_query_time secondsto execute and required at least min_examined_row_limit rows to be examined.The time to acquire the initial table locks is not counted as execution time.

--slow query log 包含所有执行时间超过long_query_time 并且需要至少min_examined_row_limitrows的SQL statements。 这个时间由initial table lock来精确的统计，而不是execution time。

 

mysqld writes astatement to the slow query log after it has been executed and after all lockshave been released, so log order might be different from execution order.

--mysqld 等statement 执行完毕，并且所有的lock 都释放之后，才将记录写入slow query log。

The defaultvalue of long_query_time is 10. The minimum value is 0, and a resolution ofmicroseconds is supported when logging to a file. However, the microsecondspart is ignored and only integer values are written when logging to tables.

--默认long_query_time 是10秒，最小值是0，当写入logfile时，支持到微妙，当写入log table时，则忽略微妙，只保留整数值。

 

By default, theslow query log is disabled. Use --slow_query_log[={0|1}] to specify the initialslow query log state explicitly. With no argument or an argument of 1,--slow_query_log enables the log. With an argument of 0, this option disablesthe log. You can use --slow_query_log_file=file_name to specify a log filename. You can also use --log-output to specify the log destination (asdescribed in Section 5.2.1, “Selecting General Query and Slow Query Log OutputDestinations”). The older option to enable the slow query log file,--log-slow-queries, is deprecated.

--默认情况下，slow query log 是禁用的，使用slow_query_log 来控制slowquery log。

 

If you specifyno 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 nameis given to specify a different directory.

--在没有指定slow query log file 名称的情况下， 默认文件名是：host_name-slow.log.默认的存放在data directory目录下。

 

To control theslow log at runtime, use the global slow_query_log and slow_query_log_filesystem variables. Set slow_query_log to 0 (or OFF) to disable the log or to 1(or ON) to enable it. Set slow_query_log_file to specify the name of the logfile. If a log file already is open, it is closed and the new file is opened.

在runtime 状态也可以通过slow_query_log和 slow_query_log_file 来修改。

 

When the slowquery log is enabled, the server writes output to any destinations specified bythe --log-output option or log_output system variable. If you enable the log,the server opens the log file and writes startup messages to it. However,further logging of queries to the file does not occur unless the FILE logdestination is selected. If the destination is NONE, no queries are written evenif the slow query log is enabled. Setting the log file name has no effect onlogging if the log destination value does not contain FILE.

 

The slow querylog can be used to find queries that take a long time to execute and aretherefore candidates for optimization. However, examining a long slow query logcan become a difficult task. To make this easier, you can process a slow query logfile using the mysqldumpslow command to summarize the queries that appear inthe log. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.

       --通过slow query log 可以来对相关的SQL 进行优化，但是直接查看log 比较困难，可以使用mysqldumpslow 命令对log 进行dump 之后在查看。

 

In MySQL 5.5,queries that do not use indexes are logged in the slow query log if the--log-queries-not-using-indexes option is specified. See Section 5.1.2, “ServerCommand Options”.

 

In MySQL 5.5,the --log-slow-admin-statements server option enables you to request logging ofslow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTERTABLE to the slow query log.

 

Queries handledby the query cache are not added to the slow query log, nor are queries thatwould not benefit from the presence of an index because the table has zero rowsor one row.

A replicationslave does not write replicated queries to the slow query log, unless it is runusing the --log-slow-slave-statements option.

 

The slow querylog should be protected because logged statements might contain passwords.

--slow query log 应该受保护，因为里面可能包含密码。

 

五.  Binary Log

The binary logcontains “events” that describe database changes such as table creationoperations or changes to table data. It also contains events for statementsthat potentially could have made changes (for example, a DELETE which matchedno rows), unless rowbased logging is used. The binary log also containsinformation about how long each statement took that updated data.

       --Binary log 包含数据库的改变信息，如创建表，修改数据等。

 

The binary log has two important purposes:

--Binary log的2个重要应用：

（1）Forreplication, the binary log is used on master replication servers as a recordof the statements to be sent to slave servers. The master server sends theevents contained in its binary log to its slaves, which execute those events tomake the same data changes that were made on the master. See Section 15.2,“Replication Implementation”.

（2） Certain datarecovery operations require use of the binary log. After a backup has beenrestored, the events in the binary log that were recorded after the backup wasmade are re-executed. These events bring databases up to date from the point ofthe backup. See Section 6.5, “Point-in-Time (Incremental) Recovery Using theBinary Log”.

 

Running a serverwith binary logging enabled makes performance slightly slower. However, thebenefits of the binary log in enabling you to set up replication and forrestore operations generally outweigh this minor performance decrement.

--启用binary log会对性能带来一定影响。

 

The binary logis not used for statements such as SELECT or SHOW that do not modify data. Ifyou want to log all statements (for example, to identify a problem query), usethe general query log.

--binary log 不记录没有修改data 的操作，如select 或show。 这些信息可以通过general query log来记录。

 

To enable thebinary log, start the server with the --log-bin[=base_name] option. If nobase_name value is given, the default name is the value of the pid-file option(which by default is the name of host machine) followed by -bin. If thebasename is given, the server writes the file in the data directory unless thebasename is given with a leading absolute path name to specify a differentdirectory.

 

mysqld appends anumeric 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 creatingan ordered series of files. The server creates a new file in the series eachtime it starts or flushes the logs. The server also creates a new binary logfile automatically after the current log's size reaches max_binlog_size. A binarylog file may become larger than max_binlog_size if you are using largetransactions because a transaction is written to the file in one piece, neversplit between files.

--当生成新的log时，mysqld 通过数字来增加binary log的文件名，每次切换都生成一个新的名称，可以手工用flush 来切换或者自动切换。自动切换受max_binlog_size 参数控制，但是binary log file 可能会大于max_binllog_size的设置，因为如果遇到一个大的事务，这个事务会全部写入binary log，不会切分到几个log 文件里。

 

To keep track ofwhich binary log files have been used, mysqld also creates a binary log indexfile that contains the names of all used binary log files. By default, this hasthe same basename as the binary log file, with the extension '.index'. You canchange 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; doingso would confuse mysqld.

--为了记录哪些binary log已经使用，mysqld 可以创建一个binarylog index file，其包含所有已经使用binary log 的文件名。 默认情况下，其文件名和binary log file一致，只是扩展名是.index. 可以使用 –log-bin-index 参数来修改，但是在mysqld 运行不能修改这个参数。

 

You can displaythe contents of binary log files with the mysqlbinlog utility. This can beuseful when you want to reprocess statements in the log for a recoveryoperation. For example, you can update a MySQL server from the binary log asfollows:

--可以通过mysqlbinlog 工具来显示binary log的内容：

shell> mysqlbinlog log_file | mysql -hserver_name

 

mysqlbinlog alsocan be used to display replication slave relay log file contents because theyare written using the same format as binary log files.

 

Binary loggingis done immediately after a statement completes but before any locks arereleased or any commit is done. This ensures that the log is logged inexecution order. Updates to nontransactional tables are stored in the binarylog immediately after execution.

--当statement 执行完毕，并且所有的锁都释放之后，或者有commit操作，那么对应的记录都会写入binary log。

 

Within anuncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that changetransactional tables such as InnoDB tables are cached until a COMMIT statementis received by the server. At that point, mysqld writes the entire transactionto the binary log before the COMMIT is executed.

--对于uncommit transaction，所有的change transactional 都会写入binarylog的cache。 从这一点上来看，即使事务没有commit，信息也会提前写入binary log。 这个和oracle的机制又很类似了。

 

Modifications tonontransactional tables cannot be rolled back. If a transaction that is rolledback includes modifications to nontransactional tables, the entire transactionis logged with a ROLLBACK statement at the end to ensure that the modificationsto those tables are replicated.

--对于nontransactioanl 的table上的修改不能进行rollback。

 

When a threadthat handles the transaction starts, it allocates a buffer of binlog_cache_sizeto buffer statements. If a statement is bigger than this, the thread opens atemporary file to store the transaction. The temporary file is deleted when thethread ends.

当处理transaction 的thread 启动之后，就会分配binlog_cache_size 的buffer。 如果statement 的值超过这个cache，那么thread会创建一个temporary file来保存这个事务，当thread 完成之后，会delete这个temporary file。

 

TheBinlog_cache_use status variable shows the number of transactions that usedthis buffer (and possibly a temporary file) for storing statements.TheBinlog_cache_disk_use status variable shows how many of those transactionsactually had to use a temporary file. These two variables can be used fortuning binlog_cache_size to a large enough value that avoids the use oftemporary files.

--可以通过binlog_cache_use 变量来查看transaction 使用buffer的情况，包括temporary file。 Binlog_cache_disk_use变量显示实际使用temporary file的大小。

 

The max_binlog_cache_size system variable (default 4GB, which is also the maximum)can be used to restrict the total size used to cache a multiple-statementtransaction. If a transaction is larger than this many bytes, it fails androlls back. The minimum value is 4096.

--max_binlog_cache_size 默认大小是4G，也是最大值，如果transaction 超过这个大小，那么将失败，并且不能roll back。 这个cache的最小值是4096.

 

If you are usingthe binary log and row based logging, concurrent inserts are converted tonormal inserts for CREATE ... SELECT or INSERT ... SELECT statement. This isdone to ensure that you can re-create an exact copy of your tables by applyingthe log during a backup operation. If you are using statement-based logging,the original statement is written to the log.

--如果使用binary log 和row based logging，对于concurrentinsert 会转换成 normal insert。 从而确保我们在re-create 数据数据时的精确。 如果使用statement-basedlogging，那么original statement 会被写入log。

 

By default, thebinary log is not synchronized to disk at each write. So if the operatingsystem or machine (not only the MySQL server) crashes, there is a chance thatthe last statements of the binary log are lost. To prevent this, you can makethe binary log be synchronized to disk after every N writes to the binary log,with the sync_binlog system variable. See Section 5.1.3, “Server SystemVariables”. 1 is the safest value for sync_binlog, but also the slowest. Evenwith sync_binlog set to 1, there is still the chance of an inconsistency betweenthe table content and binary log content in case of a crash. For example, ifyou are using InnoDB tables and the MySQL server processes a COMMIT statement,it writes the whole transaction to the binary log and then commits thistransaction into InnoDB.

--默认情况下， binary log 不是实时的将每个write 写入disk。 如果OS 或 服务器 crash， 那么可能最后一次写入binary log的statements 就可能丢失。 为了避免这种情况，可以设置binary log 在N次writesbinary log之后就同步到disk上去。 这个参数是sync_binlog. 该值设为1是最安全的， 但是也是最慢的。 即使设置为1， 也是有可能导致table content 和 binary log 内容的不一致。

 

If the servercrashes between those two operations, the transaction is rolled back by InnoDBat restart but still exists in the binary log.

--如果在2个操作进行时，server crash了，这时候transaction 会在InnoDB重启时回滚，但是记录还是会存在binary log里。 这在同步时就会有问题。

 

To resolve this,you should set --innodb_support_xa to 1. Although this option is related to thesupport of XA transactions in InnoDB, it also ensures that the binary log andInnoDB data files are synchronized.

--为了解决这个问题，可以设置—innodb_support_xa 为1. 这样就可以保证binarylog和 InnoDB data file 的同步。

 

For this optionto provide a greater degree of safety, the MySQL server should also beconfigured to synchronize the binary log and the InnoDB logs to disk at everytransaction. The InnoDB logs are synchronized by default, and sync_binlog=1 canbe used to synchronize the binary log. The effect of this option is that atrestart after a crash, after doing a rollback of transactions, the MySQL servercuts rolled back InnoDB transactions from the binary log. This ensures that thebinary log reflects the exact data of InnoDB tables, and so, that the slaveremains in synchrony with the master (not receiving a statement which has beenrolled back).

--为了更大程度的实现同步功能。MySQL server 配置在每个事务后将binary log 和 InnoDBlog 写入disk。 InnoDB log 默认是同步的，对于binary log，设置sync_binlog为1即可。 设置这个参数之后， 在每次crash之后，事务会回滚，同事MySQL 也会从binary log里清除已经rollback的InnoDB 事务。 这样就就保证了tablelog 和 Binary log 的一致，在Replication时就没有了问题。

 

If the MySQLserver discovers at crash recovery that the binary log is shorter than itshould have been, it lacks at least one successfully committed InnoDBtransaction. This should not happen if sync_binlog=1 and the disk/file systemdo an actual sync when they are requested to (some do not), so the serverprints an error message The binary log file_name is shorter than its expectedsize. In this case, this binary log is not correct and replication should berestarted from a fresh snapshot of the master's data.

--如果MySQL 检测到crash recover 的binarylog 信息不足，缺少最后一次成功的commited InnoDB 事务，那么会输出错误信息，对于这种情况，replication需要重新采集快照。这种情况在设置sync_binlog 为1时不会发生。

 

For MySQL 5.1.20and later (and MySQL 5.0.46 and later for backward compatibility), the sessionvalues of the following system variables are written to the binary log andhonored by the replication slave when parsing the binary log:

--在MySQL 5.1.20 之后，如下session 值会被写入binarylog：

(1)    sql_mode

(2)    foreign_key_checks

(3)    unique_checks

(4)    character_set_client

(5)    collation_connection

(6)    collation_database

(7)    collation_server

(8)    sql_auto_is_null

 

5.1 Binary Logging Formats

A number ofdifferent logging formats are used to record information in the binary log. Theexact format employed depends on the version of MySQL being used.

       --binary log 有几种不同的log format， 具体的格式和MySQL 版本有关。

 

There are three logging formats:

（1） Replicationcapabilities in MySQL originally were based on propagation of SQL statementsfrom master to slave. This is called statement-based logging. You can causethis format to be used by starting the server with --binlog-format=STATEMENT.

（2） In row-basedlogging, the master writes events to the binary log that indicate howindividual table rows are affected. You can cause the server to use row-basedlogging by starting it with --binlog-format=ROW.

（3） A thirdoption is also available: mixed logging. With mixed logging, statement-basedlogging is used by default, but the logging mode switches automatically torow-based in certain cases as described below. You can cause MySQL to use mixedlogging explicitly by starting mysqld with the option  --binlog-format=MIXED.

--在混合模式下，默认使用statement-based logging。 根据实际情况也会自动转成row-based 模式。

 

In MySQL 5.5,the default binary logging format is statement based.

--在MySQL 5.5中，默认的binary logging format是statement based。

 

The loggingformat can also be set or limited by the storage engine being used. This helpsto eliminate issues when replicating certain statements between a master andslave which are using different storage engines.

Withstatement-based replication, there may be issues with replicatingnondeterministic statements. In deciding whether or not a given statement issafe for statement-based replication, MySQL determines whether it can guaranteethat the statement can be replicated using statement-based logging. If MySQLcannot make this guarantee, it marks the statement as potentially unreliableand issues the warning,

STATEMENT MAY NOT BE SAFETO LOG IN STATEMENT FORMAT.

You can avoidthese issues by using MySQL's row-based replication instead.

 

5.2 Setting The Binary Log Format

In MySQL 5.5,the default binary logging format is statement based. You can select the binarylogging format explicitly by starting the MySQL server with--binlog-format=type. The supported values for type are:

（1） STATEMENT causes logging to bestatement-based.

（2） ROW causes logging to berow-based.

（3） MIXED causes logging to use mixedformat.

 

Exception：

For all MySQLCluster releases using the NDBCLUSTER storage engine, the default binary logformat is MIXED.

       注意这里的例外情况，在NDBCLUSTER 引擎下，默认使用的binary log 格式是MIXED.

 

The loggingformat also can be switched at runtime. To specify the format globally for allclients, 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';

       --可以通过命令修改globally 的信息。

 

An individualclient can control the logging format for its own statements by setting thesession value of binlog_format:

mysql> SET SESSION binlog_format ='STATEMENT';

mysql> SET SESSION binlog_format ='ROW';

mysql> SET SESSION binlog_format ='MIXED';

       --或者修改某个session 的值

 

To change theglobal or session binlog_format value, you must have the SUPER privilege.

In addition toswitching the logging format manually, a slave server may switch the format automatically.This happens when the server is running in either STATEMENT or MIXED format andencounters an event in the binary log that is written in ROW logging format. Inthat case, the slave switches to row-based replication temporarily for thatevent, and switches back to the previous format afterward.

       --修改binlog_format 需要SUPER 权限，在Replication时，修改Master的logging format，slave 也会自动switch。

 

There areseveral reasons why a client might want to set binary logging on a per-sessionbasis:

--在Session 级别设置binary logging 格式的几个原因：

（1）A sessionthat makes many small changes to the database might want to use row-basedlogging.

（2） A sessionthat performs updates that match many rows in the WHERE clause might want touse statement-based logging because it will be more efficient to log a fewstatements than many rows.

（3）Somestatements require a lot of execution time on the master, but result in just afew rows being modified. It might therefore be beneficial to replicate themusing row-based logging.

 

There are exceptions when you cannot switchthe replication format at runtime:

--不能在runtime修改的情况：

（1）From within a stored function or atrigger

（2）If the NDBCLUSTER storage engine isenabled

（3）If the session is currently inrow-based replication mode and has open temporary tables

 

Trying to switchthe format in any of these cases results in an error.

Switching thereplication format at runtime is not recommended when any temporary tablesexist, because temporary tables are logged only when using statement-basedreplication, whereas with row-based replication they are not logged. With mixedreplication, temporary tables are usually logged; exceptions happen withuser-defined functions (UDFs) and with the UUID() function.

With the binarylog format set to ROW, many changes are written to the binary log using therow-based format. Some changes, however, still use the statement-based format.Examples include all DDL (data definition language) statements such as CREATETABLE, ALTER TABLE, or DROP TABLE.

The --binlog-row-event-max-sizeoption is available for servers that are capable of row-based replication. Rowsare stored into the binary log in chunks having a size in bytes not exceedingthe value of this option. The value must be a multiple of 256. The default valueis 1024.

 

Warning：

When using statement-basedlogging for replication, it is possible for the data on the master and slave tobecome different if a statement is designed in such a way that the datamodification is nondeterministic; that is, it is left to the will of the queryoptimizer. In general, this is not a good practice even outside of replication.

 

5.3 Mixed Binary Logging Format

In all MySQL 5.5releases, when a warning is produced by the determination, a standard MySQLwarning is produced (and is available using SHOW WARNINGS). The information isalso written to the mysqld error log. Only one error for each error instanceper client connection is logged to prevent flooding the log. The log messageincludes the SQL statement that was attempted.

If a slaveserver was started with --log-warnings enabled, the slave prints messages tothe error log to provide information about its status, such as the binary logand relay log coordinates where it starts its job, when it is switching toanother relay log, when it reconnects after a disconnect, and so forth.

 

具体内容可以官网手册。

 

5.4 Logging Format for Changes to mysql Database Tables

The contents ofthe grant tables in the mysql database can be modified directly (for example,with INSERT or DELETE) or indirectly (for example, with GRANT or CREATE USER).Statements that affect mysql database tables are written to the binary log usingthe following rules:

（1） Datamanipulation statements that change data in mysql database tables directly arelogged according to the setting of the binlog_format system variable. Thispertains to statements such as INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE,SELECT, and TRUNCATE TABLE.

（2） Statementsthat change the mysql database indirectly are logged as statements regardlessof the value of binlog_format. This pertains to statements such as GRANT, REVOKE,SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER(all forms), and DROP (all forms).

 

CREATE TABLE ...SELECT is a combination of data definition and data manipulation. The CREATETABLE part is logged using statement format and the SELECT part is loggedaccording to the value of binlog_format.

 

六. Server Log Maintenance

MySQL Server cancreate a number of different log files to help you see what activity is takingplace. See Section 5.2, “MySQL Server Logs”. However, you must clean up thesefiles regularly to ensure that the logs do not take up too much disk space.

--MySQL 可以创建一些不同log 文件来帮助我们查看相关的信息，但是我们必须定时的清理这些log 信息，以防止他们占用太多的磁盘空间。

 

When using MySQLwith logging enabled, you may want to back up and remove old log files fromtime to time and tell MySQL to start logging to new files. See Section 6.2,“Database Backup Methods”.

--当启用log 后，需要经常备份和移除旧的log file，并且让MySQL 从头开始记录log。

 

On a Linux (RedHat) installation, you can use the mysql-log-rotate script for this. If youinstalled MySQL from an RPM distribution, this script should have beeninstalled automatically. You should be careful with this script if you areusing the binary log for replication.

--在Linux 平台，可以使用mysql-log-rotate脚本来实现这个功能，如果是使用RPM包来安装的mysql，那么该脚本就已经自动安装了。但是要注意，如果使用binarylog 来做replication，就要小心使用这个脚本。

 

You should notremove binary logs until you are certain that their contents have beenprocessed by all slaves. On other systems, you must install a short scriptyourself that you start from cron (or its equivalent) for handling log files.

--仅当binary log 已经被所有的slaves 处理以后才可以删除binary logs。

 

For the binarylog, you can set the expire_logs_days system variable to expire binary logfiles automatically after a given number of days (see Section 5.1.3, “ServerSystem Variables”). If you are using replication, you should set the variableno lower than the maximum number of days your slaves might lag behind themaster. To remove binary logs on demand, use the PURGE BINARY LOGS statement(see Section 12.4.1.1, “PURGE BINARY LOGS Syntax”).

--对于binary log，可以设置expire_logs_days 参数来设置过期时间。 可以使用purgebinary logs语句来清除binary logs。

 

You can forceMySQL to start using new log files by flushing the logs. Log flushing occurswhen you issue a FLUSH LOGS statement or execute a mysqladmin flush-logs,mysqladmin refresh, mysqldump --flush-logs, or mysqldump --master-data command.See Section 12.7.6.3, “FLUSH Syntax”, Section 4.5.2, “mysqladmin — Client forAdministering a MySQL Server”, and Section 4.5.4, “mysqldump — A DatabaseBackup Program”.

In addition, thebinary log is flushed when its size reaches the value of the max_binlog_sizesystem variable.

       --可以通过flush log来强制MySQL 使用newlog files。

 

As of MySQL5.5.3, FLUSH LOGS supports optional modifiers to enable selective flushing ofindividual logs (for example, FLUSH BINARY LOGS).

 

A log-flushing operationdoes the following:

--在一下情况下执行log-flushing操作：

（1） If general query logging or slowquery logging to a log file is enabled, the server closes and reopens thegeneral query log file or slow query log file.

（2） If binary logging is enabled, theserver closes the current binary log file and opens a new log file with thenext sequence number.

（3） If the server was started with the--log-error option to cause the error log to be written to a file, the resultof a log-flushing operation is version dependent:

（4） As of MySQL 5.5.7, the servercloses and reopens the log file.

（5） Prior to MySQL 5.5.7, the serverrenames the current log file with the suffix -old, then creates a new empty logfile. The server creates a new binary log file when you flush the logs.However, it just closes and reopens the general and slow query log files. Tocause new files to be created on Unix, rename the current logs before flushingthem. At flush time, the server opens new logs with the original names. Forexample, if the general and slow query logs are named mysql.log andmysql-slow.log, you can use a series of commands like this:

shell> cd mysql-data-directory

shell> mv mysql.log mysql.old

shell> mv mysql-slow.log mysql-slow.old

shell> mysqladmin flush-logs

 

At this point,you can make a backup of mysql.old and mysql-slow.old and then remove them from disk.

A similarstrategy can be used to back up the error log file, if there is one, exceptthat, on Windows, you cannot rename the error log file while the server has itopen before MySQL 5.5.7. To rename the error log file, a stop and restart canbe avoided by flushing the logs to cause the server to rename the current logfile with the suffix -old and create a new empty error log file. For furtherinformation, see Section 5.2.2, “The Error Log”.

 

You can rename the general query log or slow query log at runtime by disabling the log:

SET GLOBAL general_log = 'OFF';

SET GLOBAL slow_query_log = 'OFF';

With the logs disabled, rename the logfiles externally; for example, from the command line. Then enable the logsagain:

SET GLOBAL general_log = 'ON';

SET GLOBAL slow_query_log = 'ON';

This method works on any platform and doesnot require a server restart.

 

 

小结：

日志直接性能损耗数据库系统中最为昂贵的IO 资源，在默认情况下，系统仅打开错误日志，关闭了其他所有日志，以达到尽可能减少IO 损耗提高系统性能的目的。

但在实际应用场景中，都至少需要打开二进制日志，因为这是MySQL 很多存储引擎进行增量备份的基础，也是MySQL 实现复制的基本条件。有时候为了进一步的性能优化，定位执行较慢的SQL 语句，很多系统也会打开慢查询日志来记录执行时间超过特定数值的SQL 语句。

一般情况下，在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将MySQL 中执行的每一条Query 都记录到日志中，会该系统带来比较大的IO 负担，而带来的实际效益却并不是非常大。

一般只有在开发测试环境中，为了定位某些功能具体使用了哪些SQL 语句的时候，才会在短时间段内打开该日志来做相应的分析。所以，在MySQL 系统中，会对性能产生影响的MySQL日志（不包括各存储引擎自己的日志）主要是Binlog 。

 

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Blog:     http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email:   [e-mail]

Skype: tianlesoftware