大家都知道,MySQL的binlog,是记录数据库的变更的。那么怎么才能让数据一定会记录到binlog里呢?我觉得有以下几个参数必须要弄明白。
1、log_binShows the status of binary logging on the server, either enabled (ON) or disabled (OFF). With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. ON means that the binary log is available, OFF means that it is not in use. The --log-bin option can be used to specify a base name and location for the binary log.
2、log_slave_updatesWhether updates received by a replica server from a replication source server should be logged to the replica's own binary log.
3、sql_log_binThis variable controls whether logging to the binary log is enabled for the current session (assuming that the binary log itself is enabled). The default value is ON. To disable or enable binary logging for the current session, set the session sql_log_bin variable to OFF or ON.
上面的三个参数,是官文的原文解释,三个参数是我觉得必须要搞懂的三个参数。我自己的理解如下:
1、log_bin系统是否开启binlog日志记录,他是最基本的,是前提,后面的参数均是基于它实现。它记录的是本地的insert/updata/delete/alter 之类语句
2、log_slave_updates大家是否注意到参数中有一个slave。对应的原文释义里也存在“replication”。所以简单的讲,就是这个参数只有在slave的时候才会用到,并且如果开启它,那么它会把应用主库过来的relay log 会记录到 binlog里面。主要的使用场合是“A-B-C” 中的B。A主库,B(对于A来说是从,对于C来说是主),C从库。
3、sql_log_bin它是个session级别的参数。在log-bin开启的前提下,有些语句我们只想在主库执行而不想在从库执行,那么我就在session级别,把它关闭就好了。