绵阳网站建设开发,专业团队高端网站制作,苍南网站建设公司,设计师导航网址文章目录 MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点1#xff1a;二进制日志文件#xff08;Binary log#xff09;MySQL二进制日志#xff08;Binary log#xff09;二进制日志文件的相关配置二进制日志文件的相关参数的说明二进制日志的格式设置二进制日志的格式 二进制… 文章目录 MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点1二进制日志文件Binary logMySQL二进制日志Binary log二进制日志文件的相关配置二进制日志文件的相关参数的说明二进制日志的格式设置二进制日志的格式 二进制日志的操作例题1日志文件binary log例题1 解析 参考 【免责声明】文章仅供学习交流观点代表个人与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB)
MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点1二进制日志文件Binary log
MySQL中有多种类型的日志文件这些日志可用于故障排除、性能调整和审计等目的帮助找出正在发生的活动。
常见的日志文件包括
日志类型写入日志的信息错误日志Error log启动、运行或停止mysqld时遇到的问题二进制日志Binary log更改数据的语句也用于复制中继日志Relay log从复制源服务器接收到的数据更改通用查询日志General query log已建立的客户端连接和从客户端接收到的语句慢查询日志Slow query log执行时间超过long_query_time秒的查询DDL日志元数据日志DDL logDDL语句执行的元数据操作
MySQL二进制日志Binary log
MySQL的二进制日志Binary log是一种记录所有对MySQL数据库进行修改的操作的日志文件。
二进制日志文件主要用于如下目的
1.用于主从复制replication。
主服务器复制源将修改操作记录到二进制日志文件中并将该文件发送到从服务器。
从服务器使用二进制日志文件中的操作记录来更新自己的数据库重现这些事务进行的相同数据更改从而与主服务器保持同步。2.用于数据恢复操作point-in-time。
将所有修改操作记录到一个二进制日志文件中当需要进行数据恢复时可以使用二进制日志文件中的操作记录来重建数据库。
例如在备份还原后重新执行在备份之后记录在二进制日志中的事件可以将数据库从备份点更新到最新状态。二进制日志文件的相关配置
通过MySQL的配置文件设置二进制日志文件的路径、日志格式、日志级别等在配置二进制日志时需要注意日志文件的大小和保留时间以免日志文件过大或过旧导致系统性能下降。
二进制日志默认为启用状态即log_bin系统变量设置为ON。
mysql show variables like log_bin;
----------------------
| Variable_name | Value |
----------------------
| log_bin | ON |
----------------------
1 row in set (0.00 sec)可以通过如下命令查看二进制日志文件相关的配置参数。
mysql show variables like %log_bin%;
mysql show variables like %binlog%;二进制日志文件相关的配置参数输出例
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)
二进制日志文件的相关参数的说明
变量名说明log_bin是否启用二进制日志记录 如果设置为ON则MySQL将记录所有修改数据库的语句到二进制日志中。log_bin_basename二进制日志文件的路径和基本名称MySQL将在该名称后面添加一个数字作为文件名的后缀。例值为/var/lib/mysql/binlog时文件名为 binlog.000037log_bin_index二进制日志索引文件的的路径和名称。例 /var/lib/mysql/binlog.indexlog_bin_trust_function_creators是否信任二进制日志中的函数创建者如果设置为OFF则MySQL将拒绝在二进制日志中记录由未授权的函数创建者创建的函数。log_bin_use_v1_row_eventsdeprecated。是否使用v1格式的行事件记录如果设置为ON则MySQL将使用v1格式的行事件记录5.5以前否则将使用v2格式的行事件记录5.6以后。sql_log_bin是否记录所有修改数据库的语句到二进制日志中包括非事务性语句。默认开启binlog_cache_size二进制日志缓存大小用于控制二进制日志缓存的大小避免过多的内存占用。binlog_checksum二进制日志校验方式可以是NONE、CRC32或者SHA256用于保证二进制日志的完整性。binlog_direct_non_transactional_updates默认关闭是否直接将非事务性更新写入二进制日志如果设置为ON则MySQL将直接将非事务性更新写入二进制日志否则将缓存这些更新直到事务提交后再写入二进制日志避免当一个事务包含对事务性表和非事务性表的更新时复制的不一致性问题。binlog_encryption是否启用二进制日志加密binlog_error_action二进制日志出错时的处理方式可以是IGNORE_ERROR、ABORT_SERVER或者ABORT_SLAVEbinlog_expire_logs_auto_purge是否自动清理过期的二进制日志如果设置为ON则MySQL将自动清理过期的二进制日志。binlog_expire_logs_seconds二进制日志过期时间单位为秒 用于控制二进制日志的过期时间。默认30天。binlog_format二进制日志格式默认值ROW可以是STATEMENT、ROW或者MIXED 。8.0.34开始deprecatedbinlog_group_commit_sync_delay二进制日志组提交同步延迟时间单位为微秒binlog_group_commit_sync_no_delay_count二进制日志组提交同步延迟计数器binlog_gtid_simple_recovery是否启用简单的GTID恢复binlog_max_flush_queue_timedeprecated。二进制日志最大刷新队列时间单位为微秒binlog_order_commits是否按照提交顺序记录二进制日志如果设置为ON则MySQL将按照提交顺序记录二进制日志。binlog_transaction_compression是否启用二进制日志事务压缩。默认OFFbinlog_transaction_compression_level_zstd二进制日志事务压缩级别可以是1~22之间的整数binlog_transaction_dependency_history_size二进制日志事务依赖历史记录大小用于控制二进制日志事务依赖历史记录的大小binlog_transaction_dependency_tracking是否启用二进制日志事务依赖追踪如果设置为ON则MySQL将记录事务之间的依赖关系以便在主从复制时保证数据的一致性。innodb_api_enable_binlog是否启用InnoDB API的二进制日志记录log_statements_unsafe_for_binlog是否记录不安全的语句到二进制日志中max_binlog_cache_size二进制日志缓存的最大大小用于控制二进制日志缓存的大小避免过多的内存占用。max_binlog_size二进制日志文件的最大大小用于控制二进制日志文件的大小避免过多的磁盘占用。max_binlog_stmt_cache_size二进制日志语句缓存的最大大小 用于控制二进制日志语句缓存的大小避免过多的内存占用。sync_binlog二进制日志同步方式binlog文件刷新机制可以是0、1或者N。 0 表示这个同步操作由操作系统根据自身负载自行决定binlog多久写一次磁盘1 表示每一条事务提交都会立即写磁盘n 则表示 n 个事务提交才会写磁盘
参考 17.1.6.4 Binary Logging Options and Variables https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html
二进制日志的格式
MySQL使用多种日志格式来记录二进制日志中的信息 基于SQL语句的日志格式statement-based logging 基于SQL语句的日志格式是基于从主服务器到从服务器的SQL语句进行复制。每一条会修改数据的sql语句都会记录到binlog中。该格式下产生的binlog日志量会比较少但可能导致主从数据不一致。 基于行的日志格式row-based logging 在基于行的日志格式中默认格式主服务器会将事件写入二进制日志但不记录每一条具体执行的SQL语句仅记录哪条数据被修改了以及修改前后的样子。该格式下产生的binlog日志量会比较大但优点是会非常清楚的记录下每一行数据修改的细节主从复制不会出错。 混合日志格式mixed logging 二进制日志也可以采用混合日志格式。默认情况下使用基于语句的日志格式但在某些特殊情况无法保证复制时一致性的情况下日志记录模式会自动切换为基于行的日志格式。 例如在基于语句的复制中可能存在复制不确定性语句的问题。在决定是否将给定语句安全地用于基于语句的复制时MySQL确定是否可以保证使用基于语句的日志格式复制该语句。如果MySQL无法做出此保证则将该语句标记为潜在不可靠并发出警告语句可能不安全以语句格式记录。这时可以通过使用MySQL的基于行的复制来避免这些问题。
设置二进制日志的格式
可以通过binlog_format参数可以设置二进制日志的格式。
全局级别的设置方法
mysql SET GLOBAL binlog_format STATEMENT;
mysql SET GLOBAL binlog_format ROW;
mysql SET GLOBAL binlog_format MIXED;会话级别的设置方法
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
二进制日志的操作
二进制日志的操作包括如下命令
--显示所有可用的二进制日志文件列表包括文件名、文件大小和是否加密等信息。
show binary logs;
--显示指定二进制日志文件中的事件列表包括日志文件名、事件类型、位置、执行线程ID等信息。
show binlog events in binlog名;
--显示主服务器的二进制日志状态包括当前正在使用的二进制日志文件名、当前日志文件的位置等信息。
show master status;
--刷新二进制日志将当前的二进制日志文件关闭并创建一个新的二进制日志文件。这个命令可以用于手动控制二进制日志文件的大小和数量以及在备份二进制日志文件时使用。
flush logs;查看二进制日志例
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 /* xid94 */ |
-------------------------------------------------------------------------------------------------------------------
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 解析
AnswserB MySQL的二进制日志Binary log是一种记录所有对MySQL数据库进行修改的操作的日志文件。
参考
5.4 MySQL Server Logs https://dev.mysql.com/doc/refman/8.0/en/server-logs.html
17.1.6.4 Binary Logging Options and Variables https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html
