mysql日志默认级别_MySQL 日志之 binlog 格式 → 关于 MySQL 默认隔离级别的探讨

开心一刻

产品还没测试直接投入生产时，这尼玛...

背景问题

在讲 binlog 之前，我们先来回顾下主流关系型数据库的默认隔离级别，是默认隔离级别，不是事务有哪几种隔离级别，别会错题意了

1、Oracle、SQL Server 的默认隔离级别是什么，MySQL 的呢 ？

2、为什么 MySQL 的默认隔离级别是 RR ？

这个问题其实不太严谨，我们知道 MySQL 5.5 才将 InnoDB 代替 MyISAM 成为 MySQL 默认的存储引擎，而事务才有隔离级别一说，MyISAM 本就不支持事务，那么这个问题在 MySQL 5.5 之前根本就不成立。

严谨点来说，应该是：为什么 MySQL 5.5 及之后版本的事务默认隔离级别是 RR，或者是：为什么 InnoDB 的事务默认隔离级别是 RR

对于问题1，我相信大家都能回答的上来，Oracle，SqlServer 的默认隔离级别是 读已提交(Read Commited，简称 RC) ，而 MySQL 的默认隔离级别是 可重复读(Repeatable Read，简称 RR)

但是对于问题2，相信有很多小伙伴就会支支吾吾了：呃...，这个...，昂昂昂昂昂，太久了我记忆都不太好了...

调皮的小伙伴可能就开始岔开话题了：你讲 binlog 就讲 binlog 啦，扯什么默认隔离级别，难道 MySQL 的默认隔离级别还与 binlog 有关 ？

想知道呀？ 那得加钱

具体它俩是不是有关，楼主也不知道，我们一起往下看

binlog 格式

binlog 全称：binary log，即二进制日志，有时候也称归档日志，记录了对 MySQL 数据库执行了更改的所有操作，包括表结构变更(CREATE、ALTER、DROP TABLE…)、表数据修改(INSERT、UPDATE、DELETE...)，但不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改；若更改操作并未导致数据库变化，那么该操作也会写入 binlog，例如

create table tbl_t1(name varchar(32));insert into tbl_t1 values('zhangsan');update tbl_t1 set name = 'lisi' where name = '123';show master status\G;show binlog events in 'mysql-bin.000002'\G;

此时的：update tbl_t1 set name = 'lisi' where name = '123'; 并未引起数据库的变化，但还是被记录到了 binlog 中

binlog 的格式有三种：STATEMENT、ROW、MIXED，一开始只有 STATEMENT，后面慢慢衍生出了 ROW、MIXED

MySQL 5.1.5 之前 binlog 的格式只有 STATEMENT，5.1.5 开始支持 ROW 格式的 binlog，从 5.1.8 版本开始，MySQL 开始支持 MIXED 格式的 binlog

MySQL 5.7.7 之前，binlog 的默认格式都是 STATEMENT，在 5.7.7 及更高版本中，binlog_format 的默认值才是 ROW

三种格式的 binlog 各长什么样，它们有什么区别，各有什么优劣，我们往下看

STATEMENT

从 MySQL 第一个版本，到目前最新的 8.0.x，STATEMENT 一直坚挺在 binlog 的格式中，只是从 5.7.7 开始，它退居幕后，头把交椅给了 ROW

binglog 与我们开发中的代码日志是不一样的，它包含两类文件

索引文件：文件名.index，记录了哪些日志文件正在被使用，内容如下

日志文件：文件名.00000*

记录了对 MySQL 数据库执行了更改的所有操作

因为 binlog 的日志文件是二进制文件，不能用文本编辑器直接打开，需要用特定的工具来打开，MySQL 提供了 mysqlbinlog 来帮助我们查看日志文件内容

mysqlbinlog 可选参数很多， mysqlbinlog.exe --help

mysqlbinlog.exe Ver 3.3 for Win64 at x86Copyright (c) 2001, 2010, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Dumps a MySQL binary log in a format usable for viewing or for piping tothe mysql command line client.Usage: mysqlbinlog.exe [options] log-files -?, --help Display this help and exit. --base64-output[=name] Determine when the output statements should be base64-encoded BINLOG statements: 'never' disables it and works only for binlogs without row-based events; 'decode-rows' decodes row events into commented SQL statements if the --verbose option is also given; 'auto' prints base64 only when necessary (i.e., for row-based events and format description events); 'always' prints base64 whenever possible. 'always' is deprecated, will be removed in a future version, and should not be used in a production system. --base64-output with no 'name' argument is equivalent to --base64-output=always and is also deprecated. If no --base64-output[=name] option is given at all, the default is 'auto'. --character-sets-dir=name Directory for character set files. -d, --database=name List entries for just this database (local log only). --debug-check Check memory and open file usage at exit . --debug-info Print some debug info at exit. -D, --disable-log-bin Disable binary log. This is useful, if you enabled --to-last-log and are sending the output to the same MySQL server. This way you could avoid an endless loop. You would also like to use it when restoring after a crash to avoid duplication of the statements you already have. NOTE: you will need a SUPER privilege to use this option. -F, --force-if-open Force if binlog was not closed properly. (Defaults to on; use --skip-force-if-open to disable.) -f, --force-read Force reading unknown binlog events. -H, --hexdump Augment output with hexadecimal and ASCII event dump. -h, --host=name Get the binlog from server. -l, --local-load=name Prepare local temporary files for LOAD DATA INFILE in the specified directory. -o, --offset=# Skip the first N entries. -p, --password[=name] Password to connect to remote server. -P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). --protocol=name The protocol to use for connection (tcp, socket, pipe, memory). -R, --read-from-remote-server Read binary logs from a MySQL server. -r, --result-file=name Direct output to a given file. --server-id=# Extract only binlog entries created by the server having the given id. --set-charset=name Add 'SET NAMES character_set' to the output. --shared-memory-base-name=name Base name of shared memory. -s, --short-form Just show regular queries: no extra info and no row-based events. This is for testing only, and should not be used in production systems. If you want to suppress base64-output, consider using --base64-output=never instead. -S, --socket=name The socket file to use for connection. --start-datetime=name Start reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly). -j, --start-position=# Start reading the binlog at position N. Applies to the first binlog passed on the command line. --stop-datetime=name Stop reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly). --stop-position=# Stop reading the binlog at position N. Applies to the last binlog passed on the command line. -t, --to-last-log Requires -R. Will not stop at the end of the requested binlog but rather continue printing until the end of the last binlog of the MySQL server. If you send the output to the same MySQL server, that may lead to an endless loop. -u, --user=name Connect to the remote server as username. -v, --verbose Reconstruct SQL statements out of row events. -v -v adds comments on column data types. -V, --version Print version and exit. --open-files-limit=# Used to reserve file descriptors for use by this program.Variables (--variable-name=value)and boolean options {FALSE|TRUE} Value (after reading options)--------------------------------- ----------------------------------------base64-output (No default value)character-sets-dir (No default value)database (No default value)debug-check FALSEdebug-info FALSEdisable-log-bin FALSEforce-if-open TRUEforce-read FALSEhexdump FALSEhost (No default value)local-load (No default value)offset 0port 3307read-from-remote-server FALSEserver-id 0set-charset (No default value)shared-memory-base-name (No default value)short-form FALSEsocket .........