MySQL伺服器配置

1.        伺服器配置

1.1.        MySQL 配置選項

在呼叫伺服器（或客戶機）時可以在命令列上指定啟動選項，也可以在配置檔案中指定啟動選項。 MySQL 客戶機程式會在啟動時查詢配置檔案，並使用相應的選項。

預設情況下，伺服器在執行時使用其配置變數的預編譯值。但是，如果預設值不適合環境，則可新增執行時選項，讓伺服器使用其他值來執行以下操作：

Ø   指定重要的目錄和檔案的位置

Ø   控制伺服器寫入的日誌檔案

Ø   覆蓋伺服器與效能相關的變數的內建值（即，控制最大同時連線數以及緩衝區和快取記憶體的大小）

Ø   在伺服器啟動時啟用或禁用預編譯的儲存引擎

通過使用命令列選項或配置檔案，或者使用兩者的組合，可以指定伺服器啟動時的執行時選項（以更改其配置和行為）。命令列選項優先於配置檔案中的任何設定。要了解伺服器支援的選項，請在 shell 提示符下執行以下命令：

mysqld --verbose --help

注： 上述命令提供資訊。該命令不會啟動 MySQL 伺服器。

1)        使用配置檔案的原因？

通過命令列呼叫伺服器時，可以指定 --help 選項列出的任何伺服器選項。但是，將它們在配置檔案中列出會更有用，原因如下：

A.      將選項放在檔案中後，不需要每次啟動伺服器時都在命令列上指定選項。對於複雜的選項（如用於配置 InnoDB 表空間的選項），這樣做更加方便，並且更不容易出錯。

B.       如果所有伺服器選項都在一個配置檔案中，則可概覽伺服器的配置情況。

MySQL 程式可以訪問多個配置檔案中的選項。程式會查詢每個標準配置檔案，並讀取任何存在的配置檔案。未發現指定檔案時，不會發生錯誤。

要使用某個配置檔案，可使用編輯器以純文字檔案形式建立該檔案。要建立或修改某個配置檔案，必須擁有該檔案的寫入許可權。客戶機程式僅需要讀取訪問許可權。

2)        配置檔案組

配置檔案中的選項按組進行組織，每個組前面有一個為組命名的 [group-name] 行。通常，組名稱是選項組適用的程式的類別或名稱。選項組示例包括：

[client] ：用於指定適用於所有客戶機程式的選項。 [client] 組的一個常見用途是指定連線引數，因為在一般情況下，不管使用什麼客戶機程式，都要建立到同一個伺服器的連線。

[mysql] 和 [mysqldump] ：分別用於指定適用於 mysql 和 mysqldump 客戶機的選項。此外，也可以單獨指定其他客戶機選項。

[server] ：用於指定同時適用於 mysqld 和 mysqld_safe 伺服器程式的選項。

[mysqld] 、 [mysqld-5.6] 和 [mysqld_safe] ：用於指定適用於不同伺服器版本或啟動方法的選項。

3)        編寫配置檔案

配置檔案中組的簡短示例：

[client]

host = myhost.example.com

compress

[mysql]

show-warnings

要建立或修改某個配置檔案，終端使用者必須擁有該檔案的寫入許可權。伺服器本身僅需要讀取訪問許可權；伺服器讀取配置檔案，但不建立或修改配置檔案。在配置檔案中寫入一個選項：

使用長選項格式（像命令列上使用的那樣），但省略前導短劃線。

如果某個選項取值，則允許在等號兩則加空格 ( = ) 。此規則不適用於在命令列上指定的選項。

示例中，請注意以下方面：

[client] ：此組中的選項適用於所有標準客戶機。

- host ：指定伺服器主機名

- compress ：指示客戶機 / 伺服器協議對通過網路傳送的通訊使用壓縮

[mysql] ：此組中的選項僅適用於 mysql 客戶機。

- show-warnings ：指示 MySQL 在每條語句後顯示任何當前警告

mysql 客戶機同時使用 [client] 和 [mysql] 組中的選項，因此將使用顯示的全部三個選項。

4)        配置檔案位置

MySQL 伺服器會在標準位置中查詢檔案。 Linux 和 Windows 的標準檔案不同，在 Linux 中，使用 my.cnf 檔案。在 Windows 中，使用 my.ini 檔案。

可以使用以下選項檢視配置檔案查詢位置和組：

shell> mysql --help

標準配置檔案如下：

Linux ： 檔案 /etc/my.cnf 用作所有使用者都使用的全域性配置檔案。可以在使用者的主目錄中建立特定於使用者的配置檔案 .my.cnf 。如果設定了 MYSQL_HOME 環境變數，則將搜尋 $MYSQL_HOME/my.cnf 檔案。

Windows ： 程式按以下順序查詢配置檔案： Windows C:\ 目錄下的 my.ini 和 my.cnf ，然後是 C:\Windows （或 C:\WinNT ）目錄。但是，因為 Windows 安裝嚮導將配置檔案放在目錄 C:\Program Files\MySQL\MySQL Server <version number> 中，因此在 Windows 中，伺服器還會搜尋此目錄。

MySQL 命令列程式會在 MySQL 安裝目錄中搜尋配置檔案。

5)        配置檔案中的啟動選項

要在配置檔案中指定伺服器選項，請在 [mysqld] 或 [server] 組下指示特定選項。

日誌記錄 ： 可以通過啟用所需日誌的型別為伺服器啟用日誌記錄。以下選項可啟用常規查詢日誌、二進位制日誌和慢速查詢日誌：

general_log

log-bin

slow_query_log

預設儲存引擎 ： 可以使用 --default-storage-engine 選項指定不同於 InnoDB 的預設儲存引擎。

系統變數 ： 可以通過設定伺服器系統變數值來定製伺服器。例如，要增加允許的最大連線數，並增加 InnoDB 緩衝池數的預設值，可設定以下變數：

max_connections=200

innodb_buffer_pool_instances=4

共享記憶體 ： 在 Windows 上預設不啟用。可以使用 shared-memory 選項來啟用命名管道支援。

命名管道 ： 要啟用命名管道支援，請使用 enable-named-pipe 選項。

6)        樣例配置檔案

MySQL 安裝提供了一個樣例配置檔案： my-default.cnf 。 Linux 對於 RPM 安裝，樣例配置檔案在 /usr/share/mysql 中；對於 TAR 檔案安裝，樣例檔案在 MySQL 安裝目錄下的 share 目錄中。 Windows ： 配置檔案位於 MySQL 安裝目錄 (my.ini) 中。

安裝過程將此檔案複製到 /etc/my.cnf 。如果已存在該檔案，則將其複製到 /etc/my-new.cnf 。在該檔案中指定新選項可替換、新增或忽略標準選項。必須是命令列上的第一個選項：

--defaults-file=<file_name>

--defaults-extra-file=<file_name>

--no-defaults

在更改任何預設選項之前，請確保充分了解相應選項對伺服器操作的影響。例如，有些設定會啟用特定的日誌檔案，或者會改變記憶體緩衝區的大小。

--defaults-file=<file_name> ：使用指定位置的配置檔案。

--defaults-extra-file=<file_name> ：使用指定位置的其他配置檔案。

--no-defaults ：忽略所有配置檔案。

例如，要僅使用 /etc/my-opts.cnf 檔案而忽略標準配置檔案，可以像下面這樣呼叫程式：

shell> mysql --defaults-file=/etc/my-opts.cnf

如果多次指定一個選項（不管是在同一個配置檔案中，還是在多個配置檔案中），則最後出現的選項值優先。

有關使用配置檔案的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/option-files.html 。

7)        顯示配置檔案中的選項

通過使用 --print-defaults 選項執行 mysql 客戶機或者使用 my_print_defaults 實用程式，可以檢視讀取特定選項組的程式所使用的選項。輸出由各種選項組成，每個選項佔一行，其形式與命令列上指定選項時相同。此輸出會因為配置檔案設定而有差異。

my_print_defaults 接受以下選項：

--help, -? ：顯示幫助資訊並退出。

--config-file=<file_name> 、 --defaults-file=<file_name> 、

-c <file_name> ：僅讀取指定的配置檔案。

--debug=<debug_options> 、 -# <debug_options> ：寫入除錯日誌。

--defaults-extra-file=<file_name> 、 --extra-file=<file_name> 、 -e <file_name> ：在全域性配置檔案之後，但在使用者配置檔案之前（在 Linux 上），讀取此配置檔案。

--defaults-group-suffix=<suffix> 、 -g <suffix> ：讀取具有此字尾的組。

--no-defaults 、 -n ：返回一個空字串。

--verbose 、 -v ：詳細模式。輸出有關程式行為的更多資訊。

--version 、 -V ： 顯示版本資訊並退出。

通過命令列按組顯示選項。 [mysql] 和 [client] 組示例：

shell> my_print_defaults mysql client

--user=myusername

--password=secret

--host=localhost

--port=3306

--character-set-server=latin1

或者（對於相同的輸出）：

mysql --print-defaults mysql client

 

8)        遮蔽驗證選項

建議不要使用 mysql -uroot -poracle 形式在命令列上指定口令。為方便起見，可以將口令放在 [client] 選項組中，但口令以純文字方式儲存，對配置檔案有讀取訪問許可權的任何人都能輕易地看到。利用 mysql_config_editor 實用程式，可以將驗證憑證儲存在加密的登入檔案 .mylogin.cnf 中。在 Linux 和 UNIX 上，該檔案位置是當前使用者的主目錄，在 Windows 上是 %APPDATA%\MySQL 目錄。 MySQL 客戶機程式以後可以讀取該檔案以獲取用於連線到 MySQL 伺服器的驗證憑證。加密方法是可逆的，因此不應假設憑證對任何有檔案讀取許可權的人都是安全的。相反，該功能使得避免使用純文字憑證變得更容易。

.mylogin.cnf 登入檔案的未加密格式由選項組組成，類似於其他配置檔案。 .mylogin.cnf 中的每個選項組稱為“登入路徑” ，僅允許一組有限的選項：主機、使用者和口令。可將登入路徑視為一組值，可以指示伺服器主機以及用於伺服器驗證的憑證。

下面是一個示例：

[admin]

user = root

password = oracle

host = 127.0.0.1

 

shell>mysql --login-path=admin

 

9)        登入路徑

建立登入路徑：

mysql_config_editor --login-path=<login-path> --user=<user> --password --host=<hostname>

以純文字格式檢視單個登入路徑：

mysql_config_editor print --login-path=<login-path>

以純文字格式檢視所有登入路徑：

mysql_config_editor print --all

刪除登入路徑：

mysql_config_editor remove --login-path=<login-path>

預設登入路徑名稱為 client ，被所有標準客戶機讀取

如果呼叫 mysql_config_editor 時不使用 --login-path 選項，則將使用 [client] 登入路徑。預設情況下，所有標準客戶機都使用此登入路徑。

例如，以下命令將建立一個所有標準客戶機都使用的 [client] 登入路徑：

shell> mysql_config_editor set --user=root --password

Enter password: oracle

呼叫標準客戶機時不提供命令列引數或配置檔案將導致讀取 .mylogin.cnf 檔案中的 [client] 登入路徑以及任何配置檔案中的 [client] 選項組。例如，以下輸出顯示了呼叫 mysql 客戶機而不提供任何選項的結果（已執行前述命令）：

shell> mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

...

 

1.2.        伺服器系統變數

MySQL 伺服器維護了許多伺服器系統變數，這些變數可指示伺服器的配置情況。在 shell 下使用 mysqld --verbose --help 命令可以檢視伺服器根據其內部編譯的預設值使用的值，以及伺服器讀取的任何配置檔案， shell 下使用 mysqld --no-defaults --verbose --help 命令可以檢視伺服器根據其內部編譯的預設值使用的值，忽略任何配置檔案中的設定：

每個系統變數都有預設值，通過執行以下任一操作，可以在伺服器啟動時設定變數：在命令列上使用選項或者使用配置檔案；系統變數值可以在表示式中引用，也可以檢視伺服器使用的系統變數值。

         在 mysql 客戶機內部，使用此命令可以僅檢視變數值，沒有其他啟動選項：

SHOW GLOBAL VARIABLES;

有關伺服器系統變數的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html 。

 

1)        動態系統變數

MySQL 維護了兩種包含系統變數的作用域。 GLOBAL 變數影響伺服器的整體操作。 SESSION 變數影響其對單個客戶機連線的操作。變數存在於任一作用域中，也可同時存在於兩個作用域中。

變數及其作用域的示例包括：

僅全域性 ： key_buffer_size 、 query_cache_size

全域性和會話 ： sort_buffer_size 、 max_join_size

僅會話 ： timestamp 、 error_count

在更改變數值時，適用以下幾點：

設定會話變數不需要任何特殊許可權，但客戶機只能更改自己的會話變數，不能更改其他任何客戶機的會話變數。

LOCAL 和 @@local 是 SESSION 和 @@session 的同義詞。

如果不指定 GLOBAL 或 SESSION ，則當會話變數存在時， SET 將更改會話變數；會話變數不存在時，將產生錯誤。

2)        顯示動態系統變數

列出所有可用變數及其值：

SHOW [GLOBAL|SESSION] VARIABLES;

列出特定的變數值：

mysql> SHOW VARIABLES LIKE 'bulk%';

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

| Variable_name | Value |

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

| bulk_insert_buffer_size | 8388608 |

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

設定一個新值，然後列出：

mysql> SET bulk_insert_buffer_size=4000000;

mysql> SHOW VARIABLES LIKE 'bulk%';

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

| Variable_name | Value |

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

| bulk_insert_buffer_size | 4000000 |

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

SHOW VARIABLE 會話示例使用 'bulk%' 查詢用於設定插入緩衝區大小幻燈片中的變數，但是，可以使用整個變數名稱來輸入任何變數，也可輸入部分變數名稱和百分比符號 (%) 萬用字元。

必須按如下方式設定特定的變數型別：

²   對具有字串型別（如 CHAR 或 VARCHAR ）的變數使用字串值。

²   對具有數值型別（如 INT 或 DECIMAL ）的變數使用數值。

²   將具有布林（ BOOL 或 BOOLEAN ）型別的變數設定為 0 、 1 、 ON 或 OFF 。（如果在命令列上或在配置檔案中設定布林變數，則使用數值。）

²   將列舉型別的變數設定為可用的變數值之一，但也可將其設定為與所需列舉值對應的數字。對於列舉的伺服器變數，第一個列舉值對應於 0 。這不同於 ENUM 列，後者的第一個列舉值對應於 1 。

有關動態伺服器變數的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/dynamic-system-variables.html 。

3)        結構化系統變數

MySQL 支援一種結構化變數型別，該變數型別可以指定控制 key buffer 操作的引數。 key buffer 結構化變數具有以下元件：

key_buffer_size

key_cache_block_size

key_cache_division_limit

key_cache_age_threshold

要引用結構化變數例項的元件，可使用複合名稱：

instance_name.component_name format

示例：

hot_cache.key_buffer_size

hot_cache.key_cache_block_size

cold_cache.key_cache_block_size

有關結構化系統變數的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/structured-system-variables.html 。

4)        伺服器狀態變數

使用 SHOW STATUS 語句評估系統執行狀況。

從兩種類別中選擇（類似於動態變數）：

LOCAL 是 SESSION 的同義詞。如果沒有修飾符，則預設值為 SESSION 。

mysql> SHOW GLOBAL STATUS;

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

| Variable_name | Value |

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

| Aborted_clients | 0 |

| Aborted_connects | 0 |

| Binlog_cache_disk_use | 0 |

| Bytes_received | 169 |

| Bytes_sent | 331 |

| Com_admin_commands | 0 |

...

有些狀態變數只有一個全域性值。對於這些狀態變數， GLOBAL 和 SESSION 的值是相同的。

有關伺服器狀態變數的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html 。

1.3.        SQL 模式

通過設定 SQL 模式配置多種伺服器操作特徵，指定 MySQL 對接受輸入資料的寬容度，設定與其他資料庫系統的相容性，控制查詢處理（啟用或禁用與 SQL 符合性相關的行為），覆蓋 SQL 的“空” 預設模式（空模式不啟用限制或符合性行為）。

SQL 模式由控制查詢處理某些方面的可選值組成。設定了相應的 SQL 模式後，客戶機就可以對以下專案進行某種程度的控制：

²   輸入資料： SQL 模式可用於指示伺服器對接受輸入資料的寬容度。

²   標準 SQL 符合性： SQL 模式可用於啟用或禁用與標準 SQL 符合性相關的行為。

²   相容性： SQL 模式可用於改進與其他資料庫系統的相容性。

預設的 SQL 模式為 NO_ENGINE_SUBSTITUTION ，預設的配置檔案會新增 STRICT_TRANS_TABLES 。

 

1)        設定 SQL 模式

²   通過命令列在啟動時設定：

shell> mysqld --sql-mode=<mode_value>

²   在配置檔案內設定：

[mysqld]

sql-mode=IGNORE_SPACE

²   SET 語句

在 mysql 中，在啟動之後：

SET [SESSION|GLOBAL] sql_mode=<mode_value>

示例：

使用單個模式值設定 SQL 模式：

SET sql_mode = ANSI_QUOTES;

SET sql_mode = 'TRADITIONAL';

使用多個模式名稱設定 SQL 模式：

sql_mode = 'IGNORE_SPACE,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION';

使用 SELECT 語句檢查當前的 sql_mode 設定：

SELECT @@sql_mode;

清除當前 SQL 模式：

SET sql_mode=''

單個客戶機可按自己的要求配置 SQL 模式，但也可使用 --sql-mode 選項設定伺服器啟動時的預設 SQL 模式。如果伺服器的執行模式對接受無效資料或建立 MySQL 使用者帳戶比較慎重，則可能需要執行此操作。

如果沒有修飾符，則 SET 將更改會話 SQL 模式。呼叫 SET 語句時可以帶一個空字串，也可以帶一個或多個模式名稱（用逗號分隔）。如果值為空或者包含多個模式名稱，則必須將值放在引號中。如果值包含一個模式名稱，則引號可有可無。 SQL 模式值不區分大小寫。

2)        常用 SQL 模式

STRICT_TRANS_TABLES 、 STRICT_ALL_TABLES ：沒有這些模式， MySQL 將接受缺少、超出範圍或格式不正確的值。啟用 STRICT_TRANS_TABLES 時將為事務表設定“嚴格模式” ；也可在預設的 my.cnf 檔案中啟用。啟用 STRICT_ALL_TABLES 時將為所有表設定嚴格模式。

TRADITIONAL ： 啟用此 SQL 模式可對輸入資料值施加類似於其他資料庫伺服器的限制。在此模式下，使用 GRANT 語句可建立要求指定口令的使用者。

IGNORE_SPACE ：預設情況下，必須呼叫函式名稱與後接括號間沒有空格的函式。啟用此模式後，允許存在此類空格，並使函式名稱成為保留字。

ERROR_FOR_DIVISION_BY_ZERO ：預設情況下，除數為零時將產生結果 NULL 。在啟用此模式的情況下插入資料時，除數為零將導致出現警告，在嚴格模式下將出現錯誤。

ANSI ：使用此組合模式將使 MySQL 伺服器變得更加“類似於 ANSI ”。即，此模式支援的行為更像標準 SQL ，如 ANSI_QUOTES 和 PIPES_AS_CONCAT 。

NO_ENGINE_SUBSTITUTION ：如果在建立或更改表時指定了不可用的儲存引擎，除非啟用了此模式，否則 MySQL 將替換預設儲存引擎。這是預設的 SQL 模式。

 

1.4.        日誌檔案

MySQL 用不同的日誌檔案來記錄伺服器活動，記錄有關伺服器執行的 SQL 語句的資訊：

²   錯誤日誌： 與啟動、關閉和異常情況有關的診斷資訊

²   常規查詢日誌： 伺服器從客戶機收到的所有語句

²   慢速查詢日誌： 需要長時間執行的查詢

²   二進位制日誌： 修改資料的語句

²   審計日誌： 企業版基於策略的審計      

這些日誌可用於評估伺服器的操作狀態、用於崩潰後的資料恢復、用於複製、用於幫助確定執行速度慢的查詢以及用於安全和法規遵從性。在預設情況下，這些日誌都未啟用（ Windows 中的錯誤日誌除外）。必須瞭解的是，日誌檔案（特別是常規查詢日誌）的大小可能會增長到相當大。

有關日誌檔案的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/server-logs.html 和

http://dev.mysql.com/doc/refman/5.6/en/mysql-enterprise-audit.html

 

1)        日誌檔案用法列表

伺服器在資料目錄中建立所有日誌檔案；在未設定其他路徑名的情況下，伺服器會將檔名設定為當前主機名。通過在啟動伺服器時使用對應的選項（在命令列上，或者配置檔案中（不帶字首 -- ）），可以啟用這些日誌檔案。

錯誤日誌 ： 設定 --log-error=<file_name> 以將錯誤記錄到指定檔案中。 mysqld_safe 指令碼可建立錯誤日誌，並在啟動伺服器時將其輸出重定向到該錯誤日誌。

常規查詢日誌 ： 設定 --general_log_file=<file_name> 以記錄查詢。全域性的 general_log 和 general_log_file 伺服器變數可提供對常規查詢日誌的執行時控制。將 general_log 設定為 0 （或 OFF ）禁用該日誌，設定為 1 （或 ON ）啟用該日誌。

慢速查詢日誌 ： 設定 --slow_query_log_file=<file_name> 以提供對慢速查詢日誌的執行時控制。將 slow_query_log 設定為 0 禁用該日誌，設定為 1 啟用該日誌。如果日誌檔案已開啟，則將其關閉，然後開啟新檔案。

二進位制日誌 ： 設定 --log-bin 以啟用二進位制日誌記錄。伺服器使用該選項值作為基名；在建立新日誌檔案時，在基名上新增一個升序數字字尾。這些日誌檔案以二進位制格式（而不是文字格式）儲存。

審計日誌 ： 審計日誌是作為企業版外掛提供的；載入該日誌後，可用於將事件記錄到 audit_log_file 選項指定的檔案中。審計過程會不斷寫入審計日誌，直到將該外掛刪除，或者通過 audit_log_policy=NONE 選項設定關閉審計。在伺服器啟動時使用 audit_log=FORCE_PLUS_PERMANENT 作為選項，可以防止刪除該外掛。

 

2)        二進位制日誌記錄

二進位制日誌包含描述資料庫更改（如建立資料庫或更改表資料）的“事件” 。二進位制日誌還包含可能做出更改的語句的事件（例如，沒有匹配行的 DELETE ）。該日誌還包含有關每條更新語句所用時間的資訊。二進位制日誌有兩個重要用途：複製和資料恢復。

MySQL 使用日誌傳送複製解決方案。使用日誌傳送系統時，可以將主系統上發生的所有資料更改儲存在二進位制日誌中，然後通過從系統檢索這些資料更改，並根據接收到的這些日誌檔案執行更改。可以實時下載日誌檔案並執行內容；即，只要生成日誌檔案事件，就將其傳送到連線的從系統供執行。由於網路傳播存在延遲，從系統可能需要幾秒到幾分鐘（最壞的情況）時間來接收更新。在理想的情況下，延遲會在一秒以內。

發生以下事件之一時，二進位制日誌會輪轉：

l   重新啟動 MySQL 伺服器。

l   達到允許的最大大小 (max_binlog_size) 。

l   發出了 FLUSH LOGS SQL 命令。

二進位制日誌獨立於儲存引擎。不管使用的儲存引擎是哪個（即 InnoDB 或 MyISAM ）， MySQL 複製都會工作。

 

3)        二進位制日誌記錄格式

基於語句的二進位制日誌記錄：

l   包含實際 SQL 語句

l   包括 DDL （ CREATE 、 DROP 等）和 DML （ UPDATE 、 DELETE 等）語句

l   相對較小的檔案儲存磁碟空間和網路頻寬

l   並非所有複製的語句都會在遠端計算機上正確重放

l   要求主系統和從系統上覆制的表和列完全相同（或者符合多個限制條件）

基於行的二進位制日誌記錄：

l   指示對單個錶行的影響情況

l   正確重放所有語句，即使對於在使用基於語句的日誌記錄時未正確複製的功能導致的更改也是如此

按如下方式設定格式：

SET [GLOBAL|SESSION] BINLOG_FORMAT=[row|statement|mixed|default];

注： 使用 mixed 選項可讓 MySQL 選取最適合單個事件的格式。 MySQL 通常會使用基於語句的二進位制日誌，但在需要時可恢復到基於行的複製。

 

4)        查詢二進位制日誌檔案

使用 SHOW BINARY LOGS 語句可完成以下任務：

# 列出當前的日誌檔案和檔案大小

mysql> SHOW BINARY LOGS;

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

| Log_name | File_size |

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

| binlog.000015 | 724935 |

| binlog.000016 | 733481 |

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

使用 SHOW MASTER STATUS 語句可完成以下任務：

# 顯示下一個事件的主狀態

mysql> SHOW MASTER STATUS;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| binlog.000016 | 733481 | world_innodb | manual,mysql |

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

語句以描述資料庫修改的“事件” 形式儲存在二進位制日誌中。要確定事件，必須獲得二進位制日誌檔名稱和位元組偏移（即位置）。例如，日誌檔案 binlog.000016 、位置 733481 可確定幻燈片中的最後一個事件。

SHOW MASTER STATUS 命令需要 SUPER 或 REPLICATION CLIENT 許可權。

 

5)        檢視二進位制日誌內容

二進位制日誌是無法使用普通文字檢視器檢視的，日誌以緊湊二進位制格式儲存。使用 mysqlbinlog 實用程式可以將二進位制日誌轉換成 SQL 文字格式，在標準輸出中檢視資料：

shell> mysqlbinlog host-bin.000001 host-bin.000002

也可以將輸出重定向到 more ：

shell> mysqlbinlog host-bin.000001 | more

通過命令列執行 mysqlbinlog 程式，將要檢視的日誌指定為引數。此程式有多個選項，提供諸如根據時間或檔案偏移提取事件之類的功能。可以檢視所有帶 --help 標誌的選項。在 mysqlbinlog 輸出中，事件前面會有提供相關資訊的頭註釋：

# at 141

#100309 9:28:36 server id 123 end_log_pos 245

Query thread_id=3350 exec_time=11 error_code=0

如果使用基於語句的日誌記錄建立二進位制日誌，然後執行 mysqlbinlog --database=test 建立可讀檔案，則伺服器將過濾掉不與 test 資料庫關聯的語句：

INSERT INTO test.t1 (i) VALUES(100);

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

| USE test; |

| INSERT INTO test.t1 (i) VALUES(101); |

| INSERT INTO t1 (i) VALUES(102); |

| INSERT INTO db2.t2 (j) VALUES(201); |

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

USE db2;

INSERT INTO db2.t2 (j) VALUES(202);

因為沒有預設資料庫，因此不包括第一條 INSERT 語句。輸出的是跟在 USE test 後面的三條 INSERT 語句，而不是跟在 USE db2 後面的 INSERT 語句。

 

6)        刪除二進位制日誌

預設情況下，不會自動刪除舊的日誌檔案。

A.        根據存在時間刪除日誌：

SET GLOBAL expire_logs_days = 7;

…或者…

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

B.        根據檔名刪除日誌：

PURGE BINARY LOGS TO 'mysql-bin.000010';

C.        要在二進位制日誌輪轉過程中自動刪除存在時間多於指定天數的任何二進位制日誌，可使用 expire_logs_days 設定。

也可以在配置檔案中配置 expire_logs_days ：

[mysqld]

expire_logs_days=7

 

7)        配置企業審計

企業審計通過使用 audit_log 伺服器外掛實現，該功能隨企業版本訂閱提供。企業審計啟用後，在日誌檔案中生成一個伺服器活動審計記錄，內容取決於策略，可能包括：

²   在系統上發生的錯誤的記錄

²   客戶機連線和斷開連線的時間

²   客戶機在連線期間執行的操作

²   客戶機訪問的資料庫和表

A.        安裝 audit_log 外掛

要安裝 audit_log 外掛，請使用 INSTALL PLUGIN 語法，如以下示例中所示：

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

或者，也可以在伺服器啟動時設定 plugin-load 選項：

[mysqld]

plugin-load=audit_log.so

預設情況下，裝入該外掛就會啟用日誌記錄。將選項 audit-log 設定為 OFF 可禁用日誌記錄。要防止在執行時刪除該外掛，可設定以下選項：

audit-log=FORCE_PLUS_PERMANENT

         日誌檔案命名為 audit.log ，預設情況下位於伺服器資料目錄中。要更改該檔案的名稱或位置，可在伺服器啟動時設定 audit_log_file 系統變數。

 

B.        日誌記錄策略

基於策略的日誌記錄通過 audit_log_policy 選項設定，提供的日誌記錄選項有 ALL 、 NONE 、 LOGINS 或 QUERIES ，預設為 ALL ；

要平衡遵從性和效能，可使用 audit_log_strategy 選項在 SYNCHRONOUS 、 ASYNCHRONOUS 、 SEMISYNCHRONOUS 和 PERFORMANCE 之間進行選擇。如果將 audit_log_rotate_on_size 設定為某個大於 0 的數字，則當日志檔案大小超出了該數量的 4 KB 資料塊大小時，將輪轉日誌檔案。

有關配置企業審計的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin-options-variables.html

 

C.        審計日誌檔案

審計日誌檔案以 XML 形式寫入，採用 UTF-8 編碼。根元素為 <AUDIT> 。根元素的結束標記 </AUDIT> 在該外掛終止時寫入。在審計日誌外掛處於活動狀態期間，檔案中沒有該標記。每個審計條目就是一個 <AUDIT_RECORD /> 元素：

<AUDIT_RECORD TIMESTAMP="2012-10-12T09:35:15"

NAME="Connect" CONNECTION_ID="4" STATUS="0"

USER="root" PRIV_USER="root" OS_LOGIN="" PROXY_USER=""

HOST="localhost" IP="127.0.0.1" DB=""/>

<AUDIT_RECORD TIMESTAMP="2012-10-12T09:38:33"

NAME="Query" CONNECTION_ID="4" STATUS="0"

SQLTEXT="INSERT INTO tbl VALUES(1, 2)"/>

 

每個審計記錄的 TIMESTAMP 採用 UTC 格式。

NAME 屬性代表事件型別。例如， “ Connect ” 表示登入事件， “ Quit ” 表示客戶機斷開連線， “ Shutdown ” 表示伺服器關閉。 “ Audit ” 和“ NoAudit ” 表示審計開始和停止的點。

STATUS 屬性提供命令狀態。這與 MySQL 命令 SHOW ERRORS 顯示的 Code 值相同。有些屬性僅在特定的事件型別中出現。例如， “ Connect ” 事件包括諸如 HOST 、 DB 、 IP 和 USER 之類的屬性， “ Query ” 事件包括 SQLTEXT 屬性。

有關企業審計日誌檔案的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/audit-log-file.html

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16976507/viewspace-2648751/，如需轉載，請註明出處，否則將追究法律責任。