试验环境，MySQL主主复制
主库10.72.16.112
从库10.72.16.50
一直有个疑问，利用sql_log_bin=0可以临时在客户端停止将操作记入binlog中，如果使用source命令导入批量SQL会有同样的效果吗。今天有事就测试了一把
对hch库的tb1表进行试验，原数据情况如下，主从数据都是一致的
主库
root@localhost:mysql.sock [hch]>select * from tb1;
+----+-------+
| id | cname |
+----+-------+
| | php |
| | java |
| | go |
| | test |
| | test1 |
| | test2 |
| | test3 |
| | test4 |
| | test5 |
| | test6 |
| | test7 |
| | test8 |
+----+-------+
rows in set (0.10 sec) 从库
mysql> select * from hch.tb1;
+----+-------+
| id | cname |
+----+-------+
| 1 | php |
| 2 | java |
| 3 | go |
| 4 | test |
| 5 | test1 |
| 6 | test2 |
| 7 | test3 |
| 8 | test4 |
| 9 | test5 |
| 10 | test6 |
| 11 | test7 |
| 12 | test8 |
+----+-------+
12 rows in set (0.01 sec)
编写sql文件
insert into tb1(cname) values('test3');
insert into tb1(cname) values('test4');
insert into tb1(cname) values('test5');
insert into tb1(cname) values('test6');
insert into tb1(cname) values('test7');
insert into tb1(cname) values('test8');
在主库上做试验
# 临时关闭sql_log_bin参数
root@localhost:mysql.sock [hch]>show variables like "sql_log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.39 sec) root@localhost:mysql.sock [hch]>set sql_log_bin=;
Query OK, rows affected (0.00 sec) root@localhost:mysql.sock [hch]>show variables like "sql_log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) # 执行导入sql命令
root@localhost:mysql.sock [hch]>source sql.sh;
Query OK, row affected (0.08 sec) Query OK, row affected (0.03 sec) Query OK, row affected (0.06 sec) Query OK, row affected (0.00 sec) Query OK, row affected (0.00 sec) Query OK, row affected (0.03 sec)
对比主从之间的对比结果
# 主库查询执行结果
root@localhost:mysql.sock [hch]>select * from tb1;
+----+-------+
| id | cname |
+----+-------+
| | php |
| | java |
| | go |
| | test |
| | test1 |
| | test2 |
| | test3 |
| | test4 |
| | test5 |
| | test6 |
| | test7 |
| | test8 |
| | test3 |
| | test4 |
| | test5 |
| | test6 |
| | test7 |
| | test8 |
+----+-------+
rows in set (0.00 sec) # 从库执行查询结果
mysql> select * from hch.tb1;
+----+-------+
| id | cname |
+----+-------+
| | php |
| | java |
| | go |
| | test |
| | test1 |
| | test2 |
| | test3 |
| | test4 |
| | test5 |
| | test6 |
| | test7 |
| | test8 |
+----+-------+
rows in set (0.11 sec)
主从同步状态查询
主库查询
root@localhost:mysql.sock [(none)]>show slave status\G;
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.72.16.50
Master_User: root
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin.
Read_Master_Log_Pos:
Relay_Log_File: mysql-relay-bin-503306.000004
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: b7fae014-c3af-11e8-89c1-000c29bf3444
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:-,
387faba7-a070-11e8--000c29466957:-,
671bac73-a032-11e8-a493-000c29bf3444:-,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
a68949d2-a06c-11e8-bba3-000c29bf3444:,
ee639e4e-358a-11e7-87fa-000c29466957:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.03 sec) 从库查询
mysql> show slave status\G;
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.72.16.112
Master_User: root
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-bin-1123306.000004
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 387faba7-a070-11e8--000c29466957
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 387faba7-a070-11e8--000c29466957:-
Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:-,
387faba7-a070-11e8--000c29466957:-,
671bac73-a032-11e8-a493-000c29bf3444:-,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
a68949d2-a06c-11e8-bba3-000c29bf3444:,
ee639e4e-358a-11e7-87fa-000c29466957:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.02 sec) ERROR:
No query specified
结论，可以看出设置sql_log_bin=0，用source命令导入SQL也可以不记入binlog中。
欢迎转载，请注明出处！