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: tianlesoftware@gmail.com
Skype: tianlesoftware
-------加群需要在備註說明Oracle表空間和資料檔案的關係，否則拒絕申請----
DBA1 群：62697716(滿); DBA2 群：62697977(滿) DBA3 群：62697850(滿)
DBA 超級群：63306533(滿); DBA4 群：83829929(滿) DBA5群： 142216823(滿)
DBA6 群：158654907(滿) DBA7 群：69087192(滿) DBA8 群：172855474
DBA 超級群2：151508914 DBA9群：102954821 聊天 群：40132017(滿)