ホームページ >データベース >mysql チュートリアル >MySQL の REDO ログと binlog の違いは何ですか?
MySQL の REDO ログと binlog の違いは何ですか?
- WBOY転載
- 2023-06-03 18:53:402085ブラウズ
まえがき
MySQL には 6 種類のログ ファイルがあります。つまり、REDO ログ (REDO ログ)、ロールバック ログ (UNDO ログ)、バイナリ ログです。 (binlog)、エラー ログ (errorlog)、スロー クエリ ログ (スロー クエリ ログ)、一般クエリ ログ (一般ログ)、およびリレー ログ (リレー ログ)。
1. REDO ログとは何ですか?
REDO ログ (REDO ログ ファイルとも呼ばれます) は、トランザクション操作の変更を記録するために使用されます。記録されるのは、データ変更後の値です。トランザクションが送信されたかどうかに関係なく記録されます。インスタンスまたはメディアに障害が発生した場合 (メディア障害)、REDO ログ ファイルが役に立ちます。データベースの電源がオフの場合、InnoDB ストレージ エンジンは REDO ログを使用して電源がオフになる直前の状態に復元し、データベースの整合性を確保します。データ。
1.1 REDO ログ ファイル名
各 InnoDB ストレージ エンジンには少なくとも 1 つの REDO ログ ファイル グループ (グループ) があり、各ファイル グループには少なくとも 2 つの REDO ログ ファイル (デフォルトの ib_logfile0 および ib_logfile1) があります。 。
- innodb_log_file_size: 各 REDO ログのサイズを指定します。デフォルト値は 48MB
- innodb_log_files_in_group: ログ ファイル グループ内の REDO ログ ファイルの数を指定します。デフォルトは 2
- innodb_log_group_home_dir: ログ ファイル グループが配置されているパスを指定します。 、デフォルト値は ./ で、mysql datadir のデータ ディレクトリを参照します ##innodb_mirrored_log_groups: ログ ミラー ファイル グループの数を指定します。デフォルトは 1 です。この関数は未実装の関数ですバージョン 5.6 で放棄され、バージョン 5.7 で削除されました。
- 次に、REDO ログ グループに関する構成を示します。
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 REDO ログのサイズを設定するにはどうすればよいですか?
REDO ログ ファイルのサイズ設定は、InnoDB ストレージ エンジンのパフォーマンスに大きな影響を与えます。
- 設定が大きすぎます
- 設定サイズを大きくすると、チェックポイントが減りますが、同時にシーケンシャルなため、 REDO ログの I/O、I/O パフォーマンスが大幅に向上しました。ただし、予期しないダウンタイムなど、データベースで予期しない問題が発生した場合は、ログを再生してコミットされたトランザクションを復元する必要があり、ログが大きい場合は、回復時間が非常に長くなります。たとえそれが受け入れられないとしても。
- 設定が小さすぎます
- ログ ファイルがいっぱいになると、innodb は自動的に別のログ ファイルに切り替え、データベース チェックポイント (これにより、Innodb キャッシュ内のダーティ ページが小規模にバッチ更新され、Innodb のパフォーマンスが大幅に低下します。
[外部リンク画像の転送に失敗しました。ソース サイトにはリーチ防止メカニズムが組み込まれている可能性があります。画像を保存して直接アップロードすることをお勧めします (img-6gU4thAZ-1584783729918)(https://cache. yisu.com/upload/information /20220215/112/979584.png)]
- どのように設定しますか?
- 公式ドキュメント「InnoDB REDO ログの最適化」の章を参照してください。
REDO ログ ファイルをバッファ プールと同じくらい大きなサイズに設定します。 InnoDB の REDO ログ ファイルがいっぱいになると、データベースのチェックポイントがトリガーされ、バッファ プール内のダーティ データがディスクに書き込まれます。 REDO ログ ファイルが小さいと、不要なディスク書き込みが多数発生する可能性があります。以前のバージョンでは、REDO ログ ファイルが大きいとリカバリに時間がかかりましたが、現在ではリカバリが速くなり、大きな REDO ログ ファイルを安心して使用できるようになりました。
ログ バッファ サイズを増やすことを検討してください。大きなログ バッファーを使用すると、トランザクションがコミットされる前に、ログをディスクに書き込むことなく、大規模なトランザクションを実行できます。したがって、多くの行を更新、挿入、または削除するトランザクションがある場合は、ログ バッファーを大きくするとディスク I/O を節約できます。ログ バッファー サイズを構成するには、innodb_log_buffer_size 構成オプションを使用します。
innodb_log_write_ahead_size パラメータの設定は、REDO ログを書き込む前のブロック サイズを示します。 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 であることが示されています。これは、操作によってデータベースが変更されなかったことを意味します。 。しかし、「...」でバイナリログイベントを表示すると、それが実際にバイナリログに記録されていることがわかります。
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是循环使用。
5 番目: Binlog をデータ回復に使用でき、マスター/スレーブ レプリケーションが確立され、異常なダウンタイムまたはメディア障害後のデータ回復に REDO ログを使用できます。
以上がMySQL の REDO ログと binlog の違いは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。