binlog_format 为 ROW 的分析

墨蓝 2023-02-21 06:21 105阅读 0赞

os: centos 7.4.1708
db: mysql 8.0.20

版本

  1. # cat /etc/centos-release
  2. CentOS Linux release 7.4.1708 (Core)
  3. #
  4. #
  5. # yum list installed |grep -i mysql80
  6. mysql-community-client.x86_64 8.0.20-1.el7 @mysql80-community
  7. mysql-community-common.x86_64 8.0.20-1.el7 @mysql80-community
  8. mysql-community-devel.x86_64 8.0.20-1.el7 @mysql80-community
  9. mysql-community-libs.x86_64 8.0.20-1.el7 @mysql80-community
  10. mysql-community-libs-compat.x86_64 8.0.20-1.el7 @mysql80-community
  11. mysql-community-server.x86_64 8.0.20-1.el7 @mysql80-community
  12. mysql-community-test.x86_64 8.0.20-1.el7 @mysql80-community
  13. mysql80-community-release.noarch el7-3 installed
  14. # mysql -e "select version();"
  15. +-----------+
  16. | version() |
  17. +-----------+
  18. | 8.0.20 |
  19. +-----------+

binlog_format = ROW

  1. # vi /etc/my.cnf
  2. binlog_format = ROW
  3. # systemctl restart mysqld.service
  4. # mysql
  5. mysql> use test;
  6. mysql> show variables like 'log_bin';
  7. +---------------+-------+
  8. | Variable_name | Value |
  9. +---------------+-------+
  10. | log_bin | ON |
  11. +---------------+-------+
  12. 1 row in set (0.00 sec)
  13. mysql> show variables like 'binlog_format';
  14. +---------------+-------+
  15. | Variable_name | Value |
  16. +---------------+-------+
  17. | binlog_format | ROW |
  18. +---------------+-------+
  19. 1 row in set (0.01 sec)
  20. mysql> show master status;
  21. +---------------+----------+--------------+------------------+-------------------+
  22. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  23. +---------------+----------+--------------+------------------+-------------------+
  24. | binlog.000024 | 156 | | | |
  25. +---------------+----------+--------------+------------------+-------------------+
  26. 1 row in set (0.00 sec)
  27. mysql> insert into tmp_t0 values(1,'1','1'),(2,'2','2');
  28. Query OK, 2 rows affected (0.33 sec)
  29. Records: 2 Duplicates: 0 Warnings: 0
  30. mysql> show master status;
  31. +---------------+----------+--------------+------------------+-------------------+
  32. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  33. +---------------+----------+--------------+------------------+-------------------+
  34. | binlog.000024 | 467 | | | |
  35. +---------------+----------+--------------+------------------+-------------------+
  36. 1 row in set (0.00 sec)
  37. # /usr/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position=156 --stop-position=467 /var/lib/mysql/binlog.000024
  38. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  39. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  40. DELIMITER /*!*/;
  41. # at 125
  42. #200629 9:20:22 server id 1 end_log_pos 125 CRC32 0xb7bca599 Start: binlog v 4, server v 8.0.20 created 200629 9:20:22 at startup
  43. # Warning: this binlog is either in use or was not closed properly.
  44. ROLLBACK/*!*/;
  45. # at 156
  46. #200629 9:21:53 server id 1 end_log_pos 235 CRC32 0xd499075e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1593393713676335 immediate_commit_timestamp=1593393713676335 transaction_length=311
  47. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  48. # original_commit_timestamp=1593393713676335 (2020-06-29 09:21:53.676335 CST)
  49. # immediate_commit_timestamp=1593393713676335 (2020-06-29 09:21:53.676335 CST)
  50. /*!80001 SET @@session.original_commit_timestamp=1593393713676335*//*!*/;
  51. /*!80014 SET @@session.original_server_version=80020*//*!*/;
  52. /*!80014 SET @@session.immediate_server_version=80020*//*!*/;
  53. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  54. # at 235
  55. #200629 9:21:53 server id 1 end_log_pos 310 CRC32 0x9744271c Query thread_id=8 exec_time=0 error_code=0
  56. SET TIMESTAMP=1593393713/*!*/;
  57. SET @@session.pseudo_thread_id=8/*!*/;
  58. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  59. SET @@session.sql_mode=1168113696/*!*/;
  60. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  61. /*!\C utf8mb4 *//*!*/;
  62. SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
  63. SET @@session.lc_time_names=0/*!*/;
  64. SET @@session.collation_database=DEFAULT/*!*/;
  65. /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
  66. BEGIN
  67. /*!*/;
  68. # at 310
  69. #200629 9:21:53 server id 1 end_log_pos 371 CRC32 0xc46cb76d Table_map: `test`.`tmp_t0` mapped to number 86
  70. # at 371
  71. #200629 9:21:53 server id 1 end_log_pos 436 CRC32 0x7664abb3 Write_rows: table id 86 flags: STMT_END_F
  72. ### INSERT INTO `test`.`tmp_t0`
  73. ### SET
  74. ### @1=1
  75. ### @2='1'
  76. ### @3='1'
  77. ### INSERT INTO `test`.`tmp_t0`
  78. ### SET
  79. ### @1=2
  80. ### @2='2'
  81. ### @3='2'
  82. # at 436
  83. #200629 9:21:53 server id 1 end_log_pos 467 CRC32 0x976464f4 Xid = 12
  84. COMMIT/*!*/;
  85. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  86. DELIMITER ;
  87. # End of log file
  88. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  89. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

参考:
https://dev.mysql.com/doc/refman/8.0/en/binary-log-setting.html

发表评论

表情:
评论列表 (有 0 条评论,105人围观)

还没有评论,来说两句吧...

相关阅读