Background issues
Before talking about binlog, let's review the default isolation level of mainstream relational databases. It's the default isolation level, not the isolation levels of transactions. Don't be mistaken
1. What is the default isolation level of Oracle and SQL Server and MySQL?
2. Why is the default isolation level of MySQL RR?
In fact, this problem is not very rigorous. We know that MySQL 5.5 replaced InnoDB with MyISAM as the default storage engine of MySQL, and transactions have isolation level. As soon as MyISAM does not support transactions, this problem was not established before MySQL 5.5.
Strictly speaking, it should be: why is the default isolation level of transactions in MySQL 5.5 and later versions RR, or why is the default isolation level of transactions in InnoDB RR
I believe everyone can answer question 1. The default isolation level of Oracle and SqlServer is read committed (RC), while the default isolation level of MySQL is Repeatable Read (RR)
But for question 2, I believe many small partners will hesitate: Er, This, Angang, it's been too long. My memory is not very good
The naughty little friend may start to switch the topic: you talk about binlog when you talk about binlog. What is the default isolation level? Is the default isolation level of MySQL still related to binlog?
The landlord doesn't know whether they are related or not. Let's look down together
binlog format
Binlog full name: binary log, that is, binary log, sometimes called archive log, records all operations that have been changed to MySQL database, including table structure change (CREATE, ALTER, DROP TABLE...), table data modification (INSERT, UPDATE, DELETE...), However, operations such as SELECT and SHOW are not included because they do not modify the data itself; If the change operation does not cause changes to the database, the operation will also be written to the binlog, such as
update tbl_t1 set name = 'lisi' where name = '123'; It does not cause changes to the database, but it is still recorded in the binlog
binlog has three formats: state, ROW and MIXED. At first, there is only state, and then ROW and MIXED are derived
Before MySQL 5.1.5, the format of binlog was only state. From 5.1.5, binlog in ROW format was supported. From 5.1.8, MySQL began to support binlog in MIXED format
Before MySQL 5.7.7, the default format of binlog was state. In version 5.7.7 and later, binlog_ The default value of format is ROW
Let's look at what the binlog s of the three formats look like, what are their differences, and what are their advantages and disadvantages
STATEMENT
From the first version of MySQL to the latest version 8.0 x. STATEMENT has always been strong in binlog format, but since 5.7.7, it has retreated behind the scenes and given the top position to ROW
binglog is different from the code log in our development. It contains two types of files
index file: file name index, which log files are being used, as follows
Log file: filename. 00000*
Records all operations that have changed the MySQL database
Because the binlog log file is a binary file and cannot be opened directly with a text editor, it needs to be opened with a specific tool. MySQL provides mysqlbinlog to help us view the contents of the log file
There are many optional parameters for mysqlbinlog, mysqlbinlog exe --help
mysqlbinlog.exe Ver 3.3 for Win64 at x86
Copyright (c) 2001, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumps a MySQL binary log in a format usable for viewing or for piping to
the 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 FALSE
debug-info FALSE
disable-log-bin FALSE
force-if-open TRUE
force-read FALSE
hexdump FALSE
host (No default value)
local-load (No default value)
offset 0
port 3307
read-from-remote-server FALSE
server-id 0
set-charset (No default value)
shared-memory-base-name (No default value)
short-form FALSE
socket E:/soft/mysql5.5.8/tmp/mysql.sock
start-datetime (No default value)
start-position 4
stop-datetime (No default value)
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
open-files-limit 18432These parameters are not discussed in detail. If you are interested, you can check them yourself. We focus on the contents of the log file and execute mysqlbinlog exe ../ data/mysql-bin. 000004
As you can see, the changes were made to the database
SQL is recorded in the log file in clear text. As for the advantages and disadvantages, we will compare them after reading the other two formats
ROW
In MySQL version 5.7.7 and later, the default format of binlog is ROW. Based on version 5.7.30, let's look at the content of ROW binlog
Generate database change operation first
The change operation has
binlog file currently being written by master: MySQL bin 00000 2, position from 2885 to 3929
Next, let's take a look at how to record in the log file and execute mysqlbinlog exe --start-position=2885 --stop-position=3929 ../ data/mysql-bin. 000002
It can be seen that the table structure change operation is recorded in the log file in the form of SQL in clear text (the same as state), but the table data change operation is recorded in the log file in the form of ciphertext, which is not convenient for us to read
Fortunately, mysqlbinlog provides the parameter - V ， or - vv ， to decrypt and view, and execute mysqlbinlog exe --base64-output=decode-rows -v --start-position=2885 --stop-position=3929 ../ data/mysql-bin. 000002
There is nothing to note about INSERT. Each column inserts the corresponding value
There is something to note about UPDATE. Although there is only one modified column and one condition column, what is recorded in the log is that the modified column is all columns, the condition column is all columns, and the column value is a specific value, rather than functions such as NOW() and UUID()
The table does not explicitly specify the primary key, and there is only one record that meets the update conditions. You can try: if there are multiple records that explicitly specify the primary key and meet the update conditions, see how binlog logs are recorded
DELETE is the same as UPDATE. Although there is only one condition column, all columns recorded in the log do
Compared with STATEMENT, it is more complex and contains a lot more content. Let's look at the advantages of the specific ROW
MIXED
Literally: mix, who does it mix? Who else can you mix? Intelligent hybrid STATEMENT and ROW
In most cases, binlog logs are recorded in the format of state (because the default isolation level of MySQL is RR, and few people modify the default isolation level). When the isolation level is RC mode, it is modified to ROW mode
Some special scenarios are also recorded in ROW format, so RR and RC are not distinguished
Of course, there is also a {NOW(), that is, only specific values are the most reliable. Other functions and system variables that depend on context and environment are not reliable because they will change according to context and environment
This will not show the specific log content. Interested partners will run the results by themselves
Summary of advantages and disadvantages
The three formats have been introduced. In contrast, I believe you have a certain understanding of their respective characteristics, advantages and disadvantages
One of the uses based on binlog: master-slave replication (three uses: master-slave replication, data recovery and audit). The landlord will summarize their advantages and disadvantages
MIXED's vision is good: it combines the advantages of state and ROW to produce a perfect format, but it backfires, and it will still have some problems
Compared with accuracy, performance priority will be lower (with the development of technology, hardware performance is no longer an unacceptable bottleneck), so ROW format is recommended
Relationship between MySQL binlog and its default isolation level RR
From the content of binlog format above, it seems that it has nothing to do with the default isolation level RR. Don't worry first and look down slowly
Under RC and state, each version of MySQL performs table data modification
The table engine is InnoDB, and the isolation level is RC and binlog_ Under the unified premise of format = state, let's take a look at mysql5 0.96,MySQL5.1.30,MySQL5.5.8,MySQL5.7.30 implementation of table data change operation
MySQl5.0.96 can be executed normally
MySQL5.1.30 execution error report and prompt
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
MySQL5.5.8,MySQL5.7.30 execution error report, all prompts
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
In other words, mysql5 On and after 1.30, InnoDB of RC isolation level has no effect on binlog_format is limited and cannot be state, otherwise the table data cannot be modified
MySQL 4.x series, because the official does not provide download, it is impossible to test, there are 4 For version x (or version 5.1.x before 5.1.21), you can send me a private letter. Thank you very much!
The order in which operations of different session s are recorded in binlog
Let's use two sessions to perform the update operation. Let's see how the recording order of the operation records of different sessions in the binlog determines
You can see the update tbl_rr_test set age = 20 where id = 1; Execute first, then commit, update tbl_rr_test set age = 21 where id = 2; Execute later and commit first. The log records: the first commit is recorded in the front and the last commit is recorded in the back, which is independent of the execution time point; For a single session, it is easy to understand that the execution order is the recording order; If there are multiple sessions, commit first and record first
The main database changes the database in the order of execution time, while binlog records in the order of commit. Theoretically, the example problem in MySQL Bug23051 will occur
Relationship between default isolation level RR and binlog
Let's take a look at MySQL Bug23051. It is said that in the early version of MySQL 5.1, when the isolation level is RC and the binlog format is state, there are bugs in the master-slave replication of InnoDB (fixed in 5.1.21), while 5.0 X is no problem. We ran down the example in Bug23051 on 5.0.96
You can see that InnoDB under 5.0.96 is binlog at the RC level_ When format = state, UPDATE # t1 # SET # a=11 # where # b=2; If the transaction is not committed, then UPDATE # t1 # SET # b=2 # where # b=1; The transaction will be blocked, so the data will be OK when copying from the database
Therefore, from the previous point of view, from MySQL 5 Starting from 0, InnoDB is at RC level, binlog_ When format = state, there is no bug in the master-slave replication (no problem with 5.0, there is a problem with 5.1.x before 5.1.21, but it is not officially available for download. Versions 5.1.21 and later do not support setting binlog to state under RC isolation level)
Then the relationship between binlog and the default level RR is clear, which is what brother Yan said in what transaction isolation level mysql should choose in the [original] Internet Project:
that Mysql At 5.0 Before this version, binlog Only support STATEMENT This format! This format has been submitted before reading(Read Commited)At this isolation level, master-slave replication is possible bug Yes, so Mysql Will be repeatable(Repeatable Read)As the default isolation level!
In other words, on mysql5 Before 0, RR was used as the default isolation level to avoid most master-slave replication bugs, and then it has been used all the time; Why not avoid all master-slave replication bugs, because at the RR isolation level, binlog_ Under format = state, when using system functions (NOW(), UUID(), etc.), the master-slave data will still be inconsistent
summary
1. binlog three formats
At present, there are three mainstream MySQL binlog formats: state, ROW and MIXED. Considering data accuracy, ROW format is recommended
2. binlog default format
Before 5.1.5, MySQL only supported binlog in state format. Since 5.1.5, MySQL has supported binlog_format=ROW. Before MySQL 5.7.7, the default format of binlog was state. In version 5.7.7 and later, binlog_ The default value of format is ROW
3. Master slave copy bug (InnoDB engine)
For MySQL 5.1.30 and later, binlog cannot be enabled if RC isolation level is enabled under InnoDB_ Format = of the state
RC, RR isolation level, binlog_format=MIXED, there will still be data inconsistency in master-slave replication (affected by system functions)
RR isolation level, binlog_ Format = state, there will still be data inconsistency in master-slave replication (affected by system functions)
binlog_format=ROW, regardless of the RC isolation level or RR isolation level, there will be no data inconsistency in the master-slave replication
4. Why is MySQL's default isolation level RR
In order to avoid mysql5 The master-slave replication problem in versions before 0 has been used all the time
5. Engine selection problem
Since MySQL 5.6 and later, InnoDB has done a lot of optimization, and its performance is not lower than MyISAM. It is said that there is no special reason to give up MyISAM