【免責(zé)聲明】文章僅供學(xué)習(xí)交流，觀點(diǎn)代表個(gè)人，與任何公司無(wú)關(guān)。
編輯|SQL和數(shù)據(jù)庫(kù)技術(shù)(ID:SQLplusDB)
MySQL 8.0 OCP (1Z0-908) 考點(diǎn)精析-架構(gòu)考點(diǎn)1：二進(jìn)制日志文件（Binary log）
MySQL中有多種類型的日志文件，這些日志可用于故障排除、性能調(diào)整和審計(jì)等目的，幫助找出正在發(fā)生的活動(dòng)。
常見的日志文件包括：
| 日志類型 | 寫入日志的信息 |
|---|---|
| 錯(cuò)誤日志（Error log） | 啟動(dòng)、運(yùn)行或停止mysqld時(shí)遇到的問(wèn)題 |
| 二進(jìn)制日志（Binary log） | 更改數(shù)據(jù)的語(yǔ)句（也用于復(fù)制） |
| 中繼日志（Relay log） | 從復(fù)制源服務(wù)器接收到的數(shù)據(jù)更改 |
| 通用查詢?nèi)罩荆℅eneral query log） | 已建立的客戶端連接和從客戶端接收到的語(yǔ)句 |
| 慢查詢?nèi)罩荆⊿low query log） | 執(zhí)行時(shí)間超過(guò)long_query_time秒的查詢 |
| DDL日志（元數(shù)據(jù)日志）（DDL log） | DDL語(yǔ)句執(zhí)行的元數(shù)據(jù)操作 |
MySQL二進(jìn)制日志（Binary log）
MySQL的二進(jìn)制日志（Binary log）是一種記錄所有對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行修改的操作的日志文件。
二進(jìn)制日志文件主要用于如下目的：
1.用于主從復(fù)制（replication）。
主服務(wù)器（復(fù)制源）將修改操作記錄到二進(jìn)制日志文件中，并將該文件發(fā)送到從服務(wù)器。
從服務(wù)器使用二進(jìn)制日志文件中的操作記錄來(lái)更新自己的數(shù)據(jù)庫(kù)（重現(xiàn)這些事務(wù)，進(jìn)行的相同數(shù)據(jù)更改），從而與主服務(wù)器保持同步。
2.用于數(shù)據(jù)恢復(fù)操作（point-in-time）。
將所有修改操作記錄到一個(gè)二進(jìn)制日志文件中，當(dāng)需要進(jìn)行數(shù)據(jù)恢復(fù)時(shí)，可以使用二進(jìn)制日志文件中的操作記錄來(lái)重建數(shù)據(jù)庫(kù)。
例如在備份還原后，重新執(zhí)行在備份之后記錄在二進(jìn)制日志中的事件，可以將數(shù)據(jù)庫(kù)從備份點(diǎn)更新到最新狀態(tài)。
二進(jìn)制日志文件的相關(guān)配置
通過(guò)MySQL的配置文件設(shè)置二進(jìn)制日志文件的路徑、日志格式、日志級(jí)別等，在配置二進(jìn)制日志時(shí)，需要注意日志文件的大小和保留時(shí)間，以免日志文件過(guò)大或過(guò)舊導(dǎo)致系統(tǒng)性能下降。
二進(jìn)制日志默認(rèn)為啟用狀態(tài)（即log_bin系統(tǒng)變量設(shè)置為ON）。
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
可以通過(guò)如下命令查看二進(jìn)制日志文件相關(guān)的配置參數(shù)。
mysql> show variables like '%log_bin%';
mysql> show variables like '%binlog%';
二進(jìn)制日志文件相關(guān)的配置參數(shù)輸出例：
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)
mysql> show variables like '%binlog%';
+------------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_auto_purge | ON |
| binlog_expire_logs_seconds | 2592000 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+------------------------------------------------+----------------------+
30 rows in set (0.00 sec)
二進(jìn)制日志文件的相關(guān)參數(shù)的說(shuō)明
| 變量名 | 說(shuō)明 |
|---|---|
| log_bin | 是否啟用二進(jìn)制日志記錄 ，如果設(shè)置為ON，則MySQL將記錄所有修改數(shù)據(jù)庫(kù)的語(yǔ)句到二進(jìn)制日志中。 |
| log_bin_basename | 二進(jìn)制日志文件的路徑和基本名稱，MySQL將在該名稱后面添加一個(gè)數(shù)字作為文件名的后綴。例值為/var/lib/mysql/binlog時(shí)文件名為 binlog.000037 |
| log_bin_index | 二進(jìn)制日志索引文件的的路徑和名稱。例 /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | 是否信任二進(jìn)制日志中的函數(shù)創(chuàng)建者，如果設(shè)置為OFF，則MySQL將拒絕在二進(jìn)制日志中記錄由未授權(quán)的函數(shù)創(chuàng)建者創(chuàng)建的函數(shù)。 |
| log_bin_use_v1_row_events | deprecated。是否使用v1格式的行事件記錄，如果設(shè)置為ON，則MySQL將使用v1格式的行事件記錄（5.5以前），否則將使用v2格式的行事件記錄（5.6以后）。 |
| sql_log_bin | 是否記錄所有修改數(shù)據(jù)庫(kù)的語(yǔ)句到二進(jìn)制日志中，包括非事務(wù)性語(yǔ)句。默認(rèn)開啟 |
| binlog_cache_size | 二進(jìn)制日志緩存大小，用于控制二進(jìn)制日志緩存的大小，避免過(guò)多的內(nèi)存占用。 |
| binlog_checksum | 二進(jìn)制日志校驗(yàn)方式，可以是NONE、CRC32或者SHA256，用于保證二進(jìn)制日志的完整性。 |
| binlog_direct_non_transactional_updates | 默認(rèn)關(guān)閉，是否直接將非事務(wù)性更新寫入二進(jìn)制日志，如果設(shè)置為ON，則MySQL將直接將非事務(wù)性更新寫入二進(jìn)制日志，否則將緩存這些更新，直到事務(wù)提交后再寫入二進(jìn)制日志，避免當(dāng)一個(gè)事務(wù)包含對(duì)事務(wù)性表和非事務(wù)性表的更新時(shí)，復(fù)制的不一致性問(wèn)題。 |
| binlog_encryption | 是否啟用二進(jìn)制日志加密 |
| binlog_error_action | 二進(jìn)制日志出錯(cuò)時(shí)的處理方式，可以是IGNORE_ERROR、ABORT_SERVER或者ABORT_SLAVE |
| binlog_expire_logs_auto_purge | 是否自動(dòng)清理過(guò)期的二進(jìn)制日志，如果設(shè)置為ON，則MySQL將自動(dòng)清理過(guò)期的二進(jìn)制日志。 |
| binlog_expire_logs_seconds | 二進(jìn)制日志過(guò)期時(shí)間，單位為秒 ，用于控制二進(jìn)制日志的過(guò)期時(shí)間。默認(rèn)30天。 |
| binlog_format | 二進(jìn)制日志格式，默認(rèn)值ROW，可以是STATEMENT、ROW或者M(jìn)IXED 。8.0.34開始deprecated |
| binlog_group_commit_sync_delay | 二進(jìn)制日志組提交同步延遲時(shí)間，單位為微秒 |
| binlog_group_commit_sync_no_delay_count | 二進(jìn)制日志組提交同步延遲計(jì)數(shù)器 |
| binlog_gtid_simple_recovery | 是否啟用簡(jiǎn)單的GTID恢復(fù) |
| binlog_max_flush_queue_time | deprecated。二進(jìn)制日志最大刷新隊(duì)列時(shí)間，單位為微秒 |
| binlog_order_commits | 是否按照提交順序記錄二進(jìn)制日志，如果設(shè)置為ON，則MySQL將按照提交順序記錄二進(jìn)制日志。 |
| binlog_transaction_compression | 是否啟用二進(jìn)制日志事務(wù)壓縮。默認(rèn)OFF |
| binlog_transaction_compression_level_zstd | 二進(jìn)制日志事務(wù)壓縮級(jí)別，可以是1~22之間的整數(shù) |
| binlog_transaction_dependency_history_size | 二進(jìn)制日志事務(wù)依賴歷史記錄大小，用于控制二進(jìn)制日志事務(wù)依賴歷史記錄的大小 |
| binlog_transaction_dependency_tracking | 是否啟用二進(jìn)制日志事務(wù)依賴追蹤，如果設(shè)置為ON，則MySQL將記錄事務(wù)之間的依賴關(guān)系，以便在主從復(fù)制時(shí)保證數(shù)據(jù)的一致性。 |
| innodb_api_enable_binlog | 是否啟用InnoDB API的二進(jìn)制日志記錄 |
| log_statements_unsafe_for_binlog | 是否記錄不安全的語(yǔ)句到二進(jìn)制日志中 |
| max_binlog_cache_size | 二進(jìn)制日志緩存的最大大小，用于控制二進(jìn)制日志緩存的大小，避免過(guò)多的內(nèi)存占用。 |
| max_binlog_size | 二進(jìn)制日志文件的最大大小，用于控制二進(jìn)制日志文件的大小，避免過(guò)多的磁盤占用。 |
| max_binlog_stmt_cache_size | 二進(jìn)制日志語(yǔ)句緩存的最大大小 ，用于控制二進(jìn)制日志語(yǔ)句緩存的大小，避免過(guò)多的內(nèi)存占用。 |
| sync_binlog | 二進(jìn)制日志同步方式（binlog文件刷新機(jī)制），可以是0、1或者N。 0 表示這個(gè)同步操作由操作系統(tǒng)根據(jù)自身負(fù)載自行決定binlog多久寫一次磁盤，1 表示每一條事務(wù)提交都會(huì)立即寫磁盤，n 則表示 n 個(gè)事務(wù)提交才會(huì)寫磁盤 |
參考：
17.1.6.4
Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html
二進(jìn)制日志的格式
MySQL使用多種日志格式來(lái)記錄二進(jìn)制日志中的信息：
-
基于SQL語(yǔ)句的日志格式（statement-based logging）
基于SQL語(yǔ)句的日志格式是基于從主服務(wù)器到從服務(wù)器的SQL語(yǔ)句進(jìn)行復(fù)制。每一條會(huì)修改數(shù)據(jù)的sql語(yǔ)句都會(huì)記錄到binlog中。該格式下產(chǎn)生的binlog日志量會(huì)比較少，但可能導(dǎo)致主從數(shù)據(jù)不一致。 -
基于行的日志格式（row-based logging）
在基于行的日志格式中（默認(rèn)格式），主服務(wù)器會(huì)將事件寫入二進(jìn)制日志，但不記錄每一條具體執(zhí)行的SQL語(yǔ)句，僅記錄哪條數(shù)據(jù)被修改了，以及修改前后的樣子。該格式下產(chǎn)生的binlog日志量會(huì)比較大，但優(yōu)點(diǎn)是會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)，主從復(fù)制不會(huì)出錯(cuò)。 -
混合日志格式（mixed logging）
二進(jìn)制日志也可以采用混合日志格式。默認(rèn)情況下，使用基于語(yǔ)句的日志格式，但在某些特殊情況（無(wú)法保證復(fù)制時(shí)一致性的情況）下，日志記錄模式會(huì)自動(dòng)切換為基于行的日志格式。
例如，在基于語(yǔ)句的復(fù)制中，可能存在復(fù)制不確定性語(yǔ)句的問(wèn)題。在決定是否將給定語(yǔ)句安全地用于基于語(yǔ)句的復(fù)制時(shí)，MySQL確定是否可以保證使用基于語(yǔ)句的日志格式復(fù)制該語(yǔ)句。如果MySQL無(wú)法做出此保證，則將該語(yǔ)句標(biāo)記為潛在不可靠，并發(fā)出警告，語(yǔ)句可能不安全以語(yǔ)句格式記錄。這時(shí)可以通過(guò)使用MySQL的基于行的復(fù)制來(lái)避免這些問(wèn)題。
設(shè)置二進(jìn)制日志的格式
可以通過(guò)binlog_format參數(shù)可以設(shè)置二進(jìn)制日志的格式。
全局級(jí)別的設(shè)置方法：
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
會(huì)話級(jí)別的設(shè)置方法：
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
例：
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> SET SESSION binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
參考：
5.4.4.1 Binary Logging Formats
https://dev.mysql.com/doc/refman/8.0/en/binary-log-formats.html
二進(jìn)制日志的操作
二進(jìn)制日志的操作包括如下命令：
--顯示所有可用的二進(jìn)制日志文件列表，包括文件名、文件大小和是否加密等信息。
show binary logs;
--顯示指定二進(jìn)制日志文件中的事件列表，包括日志文件名、事件類型、位置、執(zhí)行線程ID等信息。
show binlog events in '<binlog名>';
--顯示主服務(wù)器的二進(jìn)制日志狀態(tài)，包括當(dāng)前正在使用的二進(jìn)制日志文件名、當(dāng)前日志文件的位置等信息。
show master status;
--刷新二進(jìn)制日志，將當(dāng)前的二進(jìn)制日志文件關(guān)閉并創(chuàng)建一個(gè)新的二進(jìn)制日志文件。這個(gè)命令可以用于手動(dòng)控制二進(jìn)制日志文件的大小和數(shù)量，以及在備份二進(jìn)制日志文件時(shí)使用。
flush logs;
查看二進(jìn)制日志例：
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> delete from city;
Query OK, 4079 rows affected (0.13 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000037 | 201 | No |
| binlog.000038 | 201 | No |
| binlog.000039 | 201 | No |
| binlog.000040 | 201 | No |
| binlog.000041 | 134688 | No |
+---------------+-----------+-----------+
5 rows in set (0.00 sec)
mysql> show binlog events in 'binlog.000041';
+---------------+--------+----------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+--------+----------------+-----------+-------------+----------------------------------------------------+
| binlog.000041 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.33-0ubuntu0.22.04.2, Binlog ver: 4 |
| binlog.000041 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000041 | 157 | Anonymous_Gtid | 1 | 237 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000041 | 237 | Query | 1 | 313 | BEGIN |
| binlog.000041 | 313 | Table_map | 1 | 379 | table_id: 92 (world.city) |
| binlog.000041 | 379 | Delete_rows | 1 | 8580 | table_id: 92 |
| binlog.000041 | 8580 | Delete_rows | 1 | 16762 | table_id: 92 |
| binlog.000041 | 16762 | Delete_rows | 1 | 24977 | table_id: 92 |
| binlog.000041 | 24977 | Delete_rows | 1 | 33192 | table_id: 92 |
| binlog.000041 | 33192 | Delete_rows | 1 | 41377 | table_id: 92 |
| binlog.000041 | 41377 | Delete_rows | 1 | 49584 | table_id: 92 |
| binlog.000041 | 49584 | Delete_rows | 1 | 57796 | table_id: 92 |
| binlog.000041 | 57796 | Delete_rows | 1 | 66005 | table_id: 92 |
| binlog.000041 | 66005 | Delete_rows | 1 | 74215 | table_id: 92 |
| binlog.000041 | 74215 | Delete_rows | 1 | 82422 | table_id: 92 |
| binlog.000041 | 82422 | Delete_rows | 1 | 90629 | table_id: 92 |
| binlog.000041 | 90629 | Delete_rows | 1 | 98819 | table_id: 92 |
| binlog.000041 | 98819 | Delete_rows | 1 | 107031 | table_id: 92 |
| binlog.000041 | 107031 | Delete_rows | 1 | 115242 | table_id: 92 |
| binlog.000041 | 115242 | Delete_rows | 1 | 123443 | table_id: 92 |
| binlog.000041 | 123443 | Delete_rows | 1 | 131632 | table_id: 92 |
| binlog.000041 | 131632 | Delete_rows | 1 | 134657 | table_id: 92 flags: STMT_END_F |
| binlog.000041 | 134657 | Xid | 1 | 134688 | COMMIT /* xid=94 */ |
+---------------+--------+----------------+-----------+-------------+----------------------------------------------------+
23 rows in set (0.00 sec)
mysql>
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000041 | 134688 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000042 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
例題1：日志文件binary log
Choose the best answer.
You want to log only the changes made to the database objects and data on the MySQL system.
Which log will do this by default?
A) slow query log
B) binary log
C) error log
D) general query log
E) audit log
例題1 解析
Answser：B
MySQL的二進(jìn)制日志（Binary log）是一種記錄所有對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行修改的操作的日志文件。
參考
5.4 MySQL Server Logs
https://dev.mysql.com/doc/refman/8.0/en/server-logs.html文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-600440.html
17.1.6.4 Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-600440.html
到了這里，關(guān)于MySQL 8.0 OCP (1Z0-908) 考點(diǎn)精析-架構(gòu)考點(diǎn)1：二進(jìn)制日志文件（Binary log）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
