MySQL에서 redo 로그와 binlog의 차이점은 무엇입니까?

머리말

MySQL에는 6가지 유형의 로그 파일이 있습니다. 즉, 리두 로그(redo log), 롤백 로그(undo log), 바이너리 로그(binlog), 오류 로그(errorlog) 및 느린 쿼리 로그입니다. (느린 쿼리 로그), 일반 쿼리 로그(일반 로그), 릴레이 로그(릴레이 로그).

1. 리두로그란?

redo 로그 파일이라고도 하는 redo 로그는 트랜잭션 작업의 변경 사항을 기록하는 데 사용됩니다. 트랜잭션 제출 여부에 관계없이 데이터 수정 후의 값을 기록합니다. 인스턴스나 미디어가 실패하면(미디어 오류) 리두 로그 파일이 유용할 수 있습니다. 데이터베이스의 전원이 꺼진 경우 InnoDB 스토리지 엔진은 리두 로그를 사용하여 전원이 꺼지기 전의 순간으로 복원하여 무결성을 보장합니다. 자료.

1.1 리두 로그 파일 이름

각 InnoDB 스토리지 엔진에는 최소 1개의 리두 로그 파일 그룹(그룹)이 있고, 각 파일 그룹에는 기본 ib_logfile0 및 ib_logfile1과 같은 최소 2개의 리두 로그 파일이 있습니다.

1.2 리두 로그 매개변수에 영향을 줍니다.

• innodb_log_file_size: 각 리두 로그의 크기를 지정합니다. 기본값은 48MB입니다.

• innodb_log_files_in_group: 로그 파일 그룹의 리두 로그 파일 수를 지정합니다. 기본값은 다음과 같습니다. 2

• innodb_log_group_home_dir: 로그 파일 그룹이 있는 경로를 지정하며 기본값은 ./이며, 이는 mysql

• innodb_mirrored_log_groups의 데이터 디렉토리 datadir을 참조합니다. 로그 미러 파일 그룹 수, 기본값은 1이며, 이 기능은 구현되지 않았으며 버전 5.6에서 폐기되었고 버전 5.7에서 제거되었습니다.

다음은 리두 로그 그룹에 대한 구성을 보여줍니다.

mysql> show variables like 'innodb%log%';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
...     
| innodb_log_file_size             | 2147483648 |
| innodb_log_files_in_group        | 2          |
| innodb_log_group_home_dir        | ./         |
...
+----------------------------------+------------+
15 rows in set (0.00 sec)

1.3 리두 로그 크기를 어떻게 설정하나요?

리두 로그 파일의 크기 설정은 InnoDB 스토리지 엔진의 성능에 큰 영향을 미칩니다.

• Setting too big

설정 크기를 늘려 체크포인트를 줄이는 동시에 Redo Log의 순차 I/O로 인해 I/O 성능이 크게 향상됩니다. 그러나 예상치 못한 다운타임 등 데이터베이스에 예상치 못한 문제가 발생하면 로그를 재생하고 커밋된 트랜잭션을 복원해야 하며, 로그가 크면 복구 시간이 매우 길어집니다. 우리가 받아들일 수 없을 정도로 말이죠.

• 너무 작게 설정하세요

로그 파일이 가득 차면 innodb는 자동으로 다른 로그 파일로 전환하고 데이터베이스에서 체크포인트를 트리거합니다. 이로 인해 innodb는 더티 페이지를 캐시하게 되어 성능이 크게 저하됩니다. innodb의.

[외부 링크 이미지 전송에 실패했습니다. 원본 사이트에 리칭 방지 메커니즘이 있을 수 있습니다. 이미지를 저장하고 직접 업로드하는 것이 좋습니다(img-6gU4thAZ-1584783729918)(https://cache.yisu.com/) upload/information/20220215/112 /979584.png)]

• 어떻게 설정하나요?

공식 문서 "Optimizing InnoDB Redo Logging" 장을 참조하세요.

리두 로그 파일을 버퍼 풀만큼 크게 설정하세요. InnoDB의 리두 로그 파일이 가득 차면 데이터베이스의 체크포인트를 트리거하고 버퍼 풀의 더티 데이터를 디스크에 씁니다. 작은 리두 로그 파일로 인해 불필요한 디스크 쓰기가 많이 발생할 수 있습니다. 이전 버전에서는 대용량 리두 로그 파일로 인해 복구 시간이 길어졌지만 이제는 복구 속도가 빨라졌으며 대용량 리두 로그 파일을 안심하고 사용할 수 있습니다.

로그 버퍼 크기를 늘리는 것을 고려해보세요. 큰 로그 버퍼를 사용하면 로그를 디스크에 쓰지 않고도 트랜잭션이 커밋되기 전에 큰 트랜잭션을 실행할 수 있습니다. 따라서 많은 행을 업데이트, 삽입 또는 삭제하는 트랜잭션이 있는 경우 로그 버퍼를 더 크게 만들면 디스크 I/O를 줄일 수 있습니다. innodb_log_buffer_size 구성 옵션을 사용하여 로그 버퍼 크기를 구성하세요.

innodb_log_write_ahead_size 매개변수의 설정은 리두 로그 쓰기 전의 블록 크기를 나타냅니다. InnoDB는 ib_logfile 파일을 512바이트의 블록 형식으로 작성하지만, 파일 시스템에서는 일반적으로 4096바이트의 블록 단위를 사용합니다. 기록할 로그 파일 블록이 OS 캐시에 없는 경우 해당 4096바이트 블록을 메모리로 읽어야 하며, 그 중 512바이트를 수정한 후 해당 블록을 디스크에 다시 기록해야 합니다. 현재 파일에 기록된 오프셋이 이 값의 정수배가 아닌 경우 0을 추가하고 더 많은 데이터를 기록해야 합니다. 이런 방식으로 쓰여진 데이터가 디스크 블록 크기에 맞춰지면 읽기-수정-쓰기의 3단계 과정 없이 디스크에 직접 쓸 수 있다.

2. binlog란

Binlog는 MySQL 데이터베이스를 변경하는 모든 작업을 기록하지만 SELECT 및 SHOW와 같은 작업은 데이터 자체를 수정하지 않기 때문에 포함하지 않습니다. 작업 자체로 인해 데이터베이스가 변경되지 않으면 해당 작업도 바이너리 로그에 기록됩니다. 예:

root@localhost [(none)] 08:30:14>set binlog_format = 'STATEMENT';

root@localhost [(none)] 08:30:26>use test;
Database changed
root@localhost [test] 08:30:33>select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      138 |   14.98 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)


root@localhost [test] 08:30:53>show master status;
+----------------------+----------+--------------+------------------+--------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+----------------------+----------+--------------+------------------+--------------------------------------------+
| my3306_binlog.000052 |      471 |              |                  | e4382832-949d-11e8-97ba-080027793430:1-205 |
+----------------------+----------+--------------+------------------+--------------------------------------------+


root@localhost [test] 08:31:04>update account set acct_num=139 where amount=14;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

root@localhost [test] 08:31:35>show binlog events in 'my3306_binlog.000052';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| my3306_binlog.000052 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.23-log, Binlog ver: 4                               |
| my3306_binlog.000052 | 123 | Previous_gtids |   1003306 |         194 | e4382832-949d-11e8-97ba-080027793430:1-204                          |
| my3306_binlog.000052 | 194 | Gtid           |   1003306 |         259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:205' |
| my3306_binlog.000052 | 259 | Query          |   1003306 |         331 | BEGIN                                                               |
| my3306_binlog.000052 | 331 | Table_map      |   1003306 |         384 | table_id: 108 (test.account)                                        |
| my3306_binlog.000052 | 384 | Update_rows    |   1003306 |         440 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000052 | 440 | Xid            |   1003306 |         471 | COMMIT /* xid=14 */                                                 |
| my3306_binlog.000052 | 471 | Gtid           |   1003306 |         536 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:206' |
| my3306_binlog.000052 | 536 | Query          |   1003306 |         615 | BEGIN                                                               |
| my3306_binlog.000052 | 615 | Query          |   1003306 |         736 | use `test`; update account set acct_num=139 where amount=14         |
| my3306_binlog.000052 | 736 | Query          |   1003306 |         816 | COMMIT                                                              |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
11 rows in set (0.01 sec)

위의 예에서 볼 수 있듯이 MySQL 데이터베이스는 먼저 업데이트 작업을 수행하고 반환된 결과에는 Changed가 0으로 표시됩니다. 이는 해당 작업으로 인해 데이터베이스가 변경되지 않았음을 의미합니다. 그러나 '...'에 binlog 이벤트를 표시하면 실제로 바이너리 로그에 기록되어 있음을 알 수 있습니다.

SELECT 및 SHOW 작업을 기록하려면 쿼리 로그만 사용할 수 있습니다 --general_log[={0|1}](1은 활성화됨을 의미함)

2.1 binlog文件名

启用二进制日志，您可以使用配置参数--log-bin[=name]。如果不指定那么，则默认binlog日志文件名为主机名，后缀名为binlog的序列号，默认路劲为数据目录(datadir).你也可以指定绝对路径，如：

# cat /etc/my.cnf|grep log-bin
log-bin = /data/mysql/mysql3306/logs/my3306_binlog

# cd /data/mysql/mysql3306/logs
# ls -l
total 60
-rw-r----- 1 mysql mysql   194 Aug 15 10:04 my3306_binlog.000045
-rw-r----- 1 mysql mysql  1552 Aug 16 10:01 my3306_binlog.000046
-rw-r----- 1 mysql mysql  2953 Aug 17 09:56 my3306_binlog.000047
-rw-r----- 1 mysql mysql  1239 Aug 20 10:29 my3306_binlog.000048
-rw-r----- 1 mysql mysql   217 Aug 20 10:29 my3306_binlog.000049
-rw-r----- 1 mysql mysql 19567 Aug 21 10:24 my3306_binlog.000050
-rw-r----- 1 mysql mysql   194 Aug 22 08:01 my3306_binlog.000051
-rw-r----- 1 mysql mysql   816 Aug 22 08:31 my3306_binlog.000052
-rw-r----- 1 mysql mysql   384 Aug 22 08:01 my3306_binlog.index

2.2 影响binlog的参数

• 指定每个binlog文件的最大大小为max_binlog_size。默认值为1g，最大值1g，如果超过该值，则产生新的binlog文件，后缀名+1，并记录到.index文件。

• binlog_cache_size：使用事务表存储引擎（如innodb存储引擎）时，所有未提交的binlog日志会被记录到一个缓存中去，等事务提交时再将缓存中的binlog写入到binlog文件中。The size of the cache is determined by binlog_cache_size and the default size is 32K.。

binlog_cache_size是基于session的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要非常小心，不能设置过大。
当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓存中的日志写入一个临时文件中，因此该值又不能设的太小。
那怎么设置呢？

通过show global status命令查看binlog_cache_use,binlog_cache_disk_use的状态，以判断当前binlog_cache_size设置是否合适。

通过show global status命令查看binlog_cache_use,binlog_cache_disk_use的状态，以判断当前binlog_cache_size设置是否合适。

binlog_cache_use：记录了使用缓存写binlog次数
binlog_cache_disk_use：记录了使用临时文件写binlog次数。

示例：

root@localhost [(none)] 09:55:48>show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| binlog_cache_size | 32768   |
+-------------------+---------+
1 row in set (0.00 sec)

root@localhost [(none)] 09:53:26>show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 33553 |
+-----------------------+-------+
2 rows in set (0.00 sec)

使用缓存次数为33553，临时文件使用次数为0。说明32KB的缓存大小对于当前MySQL数据库是够用的。

• max_binlog_cache_size:如果事务需要的内存超过很多字节，则服务器会生成多于“max_binlog_cache_size”字节的存储错误所需的并发事务。最小值为4096字节，而最大值可达16EB（exabytes）。MySQL目前无法使用大于4GB的二进制日志位置，因此建议的最大值为4GB。

• "expire_logs_days" denotes the automatic deletion of binlog files created N days ago.。默认值为0，表示不自动删除，最大值99.要手动删除binlog文件，可以使用purge binary logs语句。例如：

PURGE { BINARY | MASTER } LOGS
   { TO 'log_name' | BEFORE datetime_expr }

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
PURGE BINARY LOGS BEFORE now() - interval 3 days;

• binlog_rows_query_log_events：默认为不启用，启用binlog_rows_query_log_events时，可以在binary log中记录原始的SQL语句

root@localhost [test] 08:07:52>show binlog events in 'my3306_binlog.000056';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| my3306_binlog.000056 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.23-log, Binlog ver: 4                               |
| my3306_binlog.000056 | 123 | Previous_gtids |   1003306 |         194 | e4382832-949d-11e8-97ba-080027793430:1-206                          |
| my3306_binlog.000056 | 194 | Gtid           |   1003306 |         259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:207' |
| my3306_binlog.000056 | 259 | Query          |   1003306 |         331 | BEGIN                                                               |
| my3306_binlog.000056 | 331 | Table_map      |   1003306 |         375 | table_id: 108 (test.t)                                              |
| my3306_binlog.000056 | 375 | Update_rows    |   1003306 |         421 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000056 | 421 | Xid            |   1003306 |         452 | COMMIT /* xid=16 */                                                 |
| my3306_binlog.000056 | 452 | Gtid           |   1003306 |         517 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:208' |
| my3306_binlog.000056 | 517 | Query          |   1003306 |         589 | BEGIN                                                               |
| my3306_binlog.000056 | 589 | Table_map      |   1003306 |         633 | table_id: 108 (test.t)                                              |
| my3306_binlog.000056 | 633 | Write_rows     |   1003306 |         673 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000056 | 673 | Xid            |   1003306 |         704 | COMMIT /* xid=18 */                                                 |
| my3306_binlog.000056 | 704 | Gtid           |   1003306 |         769 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:209' |
| my3306_binlog.000056 | 769 | Query          |   1003306 |         841 | BEGIN                                                               |
| my3306_binlog.000056 | 841 | Rows_query     |   1003306 |         887 | # insert into t select 3                                            |
| my3306_binlog.000056 | 887 | Table_map      |   1003306 |         931 | table_id: 108 (test.t)                                              |
| my3306_binlog.000056 | 931 | Write_rows     |   1003306 |         971 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000056 | 971 | Xid            |   1003306 |        1002 | COMMIT /* xid=24 */                                                 |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
# insert into t select 3   就是开启binlog_rows_query_log_events选项后，记录的原始SQL语句。

• sync_binlog：sync_binlog=[N]表示没写缓冲N次就同步到磁盘，如果将N设为1，即sync_binlog表示采用同步写磁盘的方式来写二进制日志，在MySQL5.7.7后，默认为1。会对数据库的IO系统带来一定影响，但可以得到最大的高可用行。

• binlog_checksum：该参数目的就是写入binlog进行校验，有两个值[crc32|none],默认为crc32

• binlog-do-db：表示需要写入日志的数据库，默认为空，表示同步所有库

• binlog-ignore-db：表示忽略写入日志的数据库，默认为空，表示同步所有库

• log-slave-update:表示从master端取得并执行的binlog，写入自己的binlog文件中，一般应用在master=>slave=>slave架构

• binlog_format：记录binlog的格式。[statement,row,mixed],在MySQL5.7.7之后，默认为row。

存储引起对binlog格式的支持情况：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-drzPlzHa-1584783729919)(https://cache.yisu.com/upload/information/20220215/112/979585.png)]

2.3 查看binlog

使用mysqlbinlog程序进行查看，例如：

[root@mysqldb1 10:58:18 /data/mysql/mysql3306/logs]
# mysqlbinlog -v --base64-output=decode-rows my3306_binlog.000052|more



/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180822  8:01:00 server id 1003306  end_log_pos 123 CRC32 0xcbe20047 	Start: binlog v 4, server v 5.7.23-log created 180822  8:01:00 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#180822  8:01:00 server id 1003306  end_log_pos 194 CRC32 0xb1bda518 	Previous-GTIDs
# e4382832-949d-11e8-97ba-080027793430:1-204
# at 194
#180822  8:10:59 server id 1003306  end_log_pos 259 CRC32 0xeced9ada 	GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:205'/*!*/;
# at 259
#180822  8:10:59 server id 1003306  end_log_pos 331 CRC32 0x6da7802a 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1534896659/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 331
#180822  8:10:59 server id 1003306  end_log_pos 384 CRC32 0xf239dd79 	Table_map: `test`.`account` mapped to number 108
# at 384
#180822  8:10:59 server id 1003306  end_log_pos 440 CRC32 0xef6460fe 	Update_rows: table id 108 flags: STMT_END_F
### UPDATE `test`.`account`
### WHERE
###   @1=137
###   @2=14.98
### SET
###   @1=138
###   @2=14.98
# at 440
#180822  8:10:59 server id 1003306  end_log_pos 471 CRC32 0x360f05d0 	Xid = 14
COMMIT/*!*/;
# at 471
#180822  8:31:35 server id 1003306  end_log_pos 536 CRC32 0x662c8f17 	GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:206'/*!*/;
# at 536
#180822  8:31:35 server id 1003306  end_log_pos 615 CRC32 0xa728a60a 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1534897895/*!*/;
BEGIN
/*!*/;
# at 615
#180822  8:31:35 server id 1003306  end_log_pos 736 CRC32 0x7513aa73 	Query	thread_id=3	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1534897895/*!*/;
update account set acct_num=139 where amount=14
/*!*/;
# at 736
#180822  8:31:35 server id 1003306  end_log_pos 816 CRC32 0x1cd7f41c 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1534897895/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3. redo log与binlog的区别

第一:redo log是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层产生的，并且二进制日志不仅仅针对INNODB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

第二：两种日志记录的内容形式不同。逻辑日志的MySQL binlog 记录的是相应的 SQL 语句。而innodb存储引擎层面的重做日志是物理日志。

二、二进制日志与记录的写入时间点不同，只有在事务提交完成后才进行一次二进制日志的写入。InnoDB存储引擎的重做日志会在事务进行中不断被写入，并不是按照事务提交的顺序进行写入。

二进制日志仅在事务提交时记录，并且对于每一个事务，仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。而对于innodb存储引擎的重做日志，由于其记录是物理操作日志，因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，其在文件中记录的顺序并非是事务开始的顺序。

第四：binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。

다섯째: Binlog는 데이터 복구에 사용할 수 있으며, 마스터-슬레이브 복제가 설정되고, Redo 로그는 비정상적인 다운타임이나 미디어 오류 후 데이터 복구에 사용할 수 있습니다.

위 내용은 MySQL에서 redo 로그와 binlog의 차이점은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!