sql_log_bin
表示当前会话是否记录 bin log,默认值on。
打开和关闭sql_log_bin:
SET sql_log_bin = {OFF|ON}
当sql_log_bin关闭后,主库服务器上的改动不记录bin log,不会复制到从库。
全局的sql_log_bin是只读的,不能修改。
如果是基于GTID复制,当关闭sql_log_bin后,任何修改,不会有GTID,也不会记录到bin log。
下面做下测试:
首先查看log file, log pos, 以及 gtid set:
>show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000055 | 1206 | | |a027439b-1d81-11ea-8d1f-002226a4a516:1-118441|
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
接着,关闭sql_log_bin:
>set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
>select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
插入新数据:
> insert into xxxxx
再次查看log file, log pos, 以及 gtid set:
>show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000055 | 1206 | | |a027439b-1d81-11ea-8d1f-002226a4a516:1-118441|
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到,当关闭sql_log_bin后,如果插入新数据,bin log和gtid set都没有变化。