redo binlog 实践 Binary Logging Formats
实践
1）开启binlog
1、
vim /etc/mysql/mysql.cnf
添加
# binlog 配置
log-bin = /var/log/mysql/mysql-bin.log
expire-logs-days = 14
max-binlog-size = 500M
server-id = 1
2、重启服务，观察是否开启
service mysql status
service mysql stop
service mysql start
查看是否开启
SHOW VARIABLES LIKE '%log%';
log_bin为 ON开启
binlog_format
2）查看
在binlog文件目录
$ sudo mysqlbinlog --no-defaults --base64-output=decode-rows mysql-bin.000002 |nl
查看内容
MySQL :: MySQL 8.0 Reference Manual :: 5.4.4.1 Binary Logging Formats https://dev.mysql.com/doc/refman/8.0/en/binary-log-formats.html
Canal——Alibaba数据实时同步神器 - 王陸 - 博客园 https://www.cnblogs.com/wkfvawl/p/16029970.html
5.4.4.1 Binary Logging Formats
The server uses several logging formats to record information in the binary log:
-
Replication capabilities in MySQL originally were based on propagation of SQL statements from source to replica. This is called statement-based logging. You can cause this format to be used by starting the server with
--binlog-format=STATEMENT. -
In row-based logging (the default), the source writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with
--binlog-format=ROW. -
A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option
--binlog-format=MIXED.
The logging format can also be set or limited by the storage engine being used. This helps to eliminate issues when replicating certain statements between a source and replica which are using different storage engines.
With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.
You can avoid these issues by using MySQL's row-based replication instead.
1.2.2 Binlog 的分类
MySQL Binlog 的格式有三种，分别是 STATEMENT,MIXED,ROW。在配置文件中可以选择配置 binlog_format= statement|mixed|row。三种格式的区别：
1）statement：语句级，binlog 会记录每次一执行写操作的语句。相对 row 模式节省空间，但是可能产生不一致性，比如“update tt set create_date=now()”，如果用 binlog 日志进行恢复，由于执行时间不同可能产生的数据就不同。
- 优点：节省空间。
- 缺点：有可能造成数据不一致。
2）row：行级， binlog 会记录每次操作后每行记录的变化。
- 优点：保持数据的绝对一致性。因为不管 sql 是什么，引用了什么函数，他只记录执行后的效果。
- 缺点：占用较大空间。
3）mixed：statement 的升级版，一定程度上解决了，因为一些情况而造成的 statement模式不一致问题，默认还是 statement，在某些情况下譬如：当函数中包含 UUID() 时；包含AUTO_INCREMENT 字段的表被更新时；执行 INSERT DELAYED 语句时；用 UDF 时；会按照ROW 的方式进行处理
- 优点：节省空间，同时兼顾了一定的一致性。
- 缺点：还有些极个别情况依旧会造成不一致，另外 statement 和 mixed 对于需要对binlog 的监控的情况都不方便。
MySQL的binlog的格式及优缺点介绍_ITPUB博客 http://blog.itpub.net/15498/viewspace-2132115/
mysqlbinlog 查看binlog时报错unknown variable 'default-character-set=utf8' - CobbLiu - 博客园 https://www.cnblogs.com/cobbliu/p/4311926.html
下午在排查MySQL主从同步问题时，想从主库的binlog中找一些线索，裸的binlog文件是无法直视的，mysqlbinlog这个工具是用来查看binlog文件内容的（使用方式man mysqlbinlog查看），但是使用mysqlbinlog将binlog文件转换成人类可读的内容时却报错：
|
1
2
|
[xxx@dbhost log]$ mysqlbinlog mysql-bin.000004mysqlbinlog: unknown variable 'default-character-set=utf8' |
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。
两个方法可以解决这个问题
一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
二是用mysqlbinlog --no-defaults mysql-bin.000004 命令打开
https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html
https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html
The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. During normal operations, the redo log encodes requests to change InnoDB table data that result from SQL statements or low-level API calls. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically during initialization, and before the connections are accepted. For information about the role of the redo log in crash recovery, see Section 15.18.2, “InnoDB Recovery”.
The server's binary log consists of files containing “events” that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a slave server in a replication setup because relay logs have the same format as binary logs. The binary log and relay log are discussed further in Section 6.4.4, “The Binary Log”, and Section 17.2.4, “Replication Relay and Status Logs”.
