bin mysql_MySQL的log_bin和sql_log_bin 的区别

最新推荐文章于 2021-02-18 08:15:09 发布

大菲哥艺术留学

最新推荐文章于 2021-02-18 08:15:09 发布

阅读量242

点赞数

文章标签： bin mysql

本文链接：https://blog.csdn.net/weixin_35133280/article/details/113376188

版权

利用二进制还原数据库的时候，突然有点纠结，log_bin和sql_log_bin有什么区别呢？行吧，搜搜，结合自己的经验，简单说一下。

log_bin:二进制日志。

在 mysql 启动时，通过命令行或配置文件决定是否开启 binlog，而 log_bin 这个变量仅仅是报告当前 binlog 系统的状态(打开与否)。若你想要关闭 binlog，你可以通过修改 sql_log_bin 并把原来的连接 kill 掉，也可以修改 log_bin，然后重启 mysql，后者更彻底，缺点就是需要重启。

在配置文件或命令行中开启 binlog 时，可以为 log_bin 指定值(如–log_bin=mysql.bin)，作为 binlog 文件的前缀名。

例如你UPDATE一个表，或者DELETE一些数据，即使该语句没有匹配的数据，这个命令也会存储到日志文件中，还包括每个语句执行的时间，也会记录进去的。

说白了，就是把把在mysql里面执行的语句，记录在二进制日志里面而已。

那么这些二进制日志有什么作用，你知道吗？

1：数据恢复

如果你的数据库出问题了，而你之前有过备份，那么可以看日志文件，找出是哪个命令导致你的数据库出问题了，想办法挽回损失。而且，你也可以利用二进制日志来还原你误操作的数据库。不过具体怎么操作还需要学的哟！

2：主从服务器之间同步数据

主服务器上所有的操作都在记录日志中，从服务器可以根据该日志来进行，以确保两个同步。因此，我们经常做的mysql-salva也是利用master的二进制日志来和master数据一致的。

二进制日志一般都不小，要是想关闭怎么办呢？

1：只有一个mysql服务器，那么可以简单的注释掉这个选项就行了。

vim /etc/my.cnf把里面的 log-bin 这一行注释掉，重启mysql服务即可。

那么sql_log_bin是什么东东？

sql_log_bin 是一个动态变量，修改该变量时，可以只对当前会话生效(Session)，也可以是全局的(Global)，当全局修改这个变量时，只会对新的会话生效(这意味当对当前会话也不会生效)，因此一般全局修改了这个变量后，都要把原来的所有连接 kill 掉。

用处：

当还原数据库的时候，如果不关闭二进制日志，那么你还原的过程仍然会记录在二进制日志里面，不仅浪费资源，那么增加了磁盘的容量，还没有必要(特别是利用二进制还原数据库的时候)所以一般还原的时候会选择关闭二进制日志，可以通过修改配置文件，重启关闭二进制日志。也可以动态命令关闭sql_log_bin,然后导入数据库。

------------------------------------------------------------------------------------------------------------------------

二进制日志

1、二进制日志(binary log)介绍

二进制日志(binary log)：记录数据库里的数据被修改。

(insert,update,delete,create,drop,alter)的相关语句；

作用：增量数据恢复和主从复制；

2、二进制日志(binary log)调整

1 mysql> show variables like '%log_bin%';2 +---------------------------------+---------------------------------------+

3 | Variable_name | Value |

4 +---------------------------------+---------------------------------------+

5 | log_bin | ON |记录binlog开关

6 | log_bin_basename | /usr/local/mysql/data/mysql-bin |

7 | log_bin_index | /usr/local/mysql/data/mysql-bin.index |

8 | log_bin_trust_function_creators | OFF |

9 | log_bin_use_v1_row_events | OFF |

10 | sql_log_bin | ON |为OFF时，临时不记录binlog开关(增量恢复)某个时间点某些语句不记录binlog

11 +---------------------------------+---------------------------------------+

修改为临时不记录binlog，之后在进行数据操作，操作便不会记录到到二进制日志中。

1 mysql> set session sql_log_bin = 0;2 Query OK, 0 rows affected (0.00sec)3

4 mysql> show variables like '%log_bin%';5 +---------------------------------+---------------------------------------+

6 | Variable_name | Value |

7 +---------------------------------+---------------------------------------+

8 | log_bin | ON |

9 | log_bin_basename | /usr/local/mysql/data/mysql-bin |

10 | log_bin_index | /usr/local/mysql/data/mysql-bin.index |

11 | log_bin_trust_function_creators | OFF |

12 | log_bin_use_v1_row_events | OFF |

13 | sql_log_bin | OFF |

14 +---------------------------------+---------------------------------------+

查看日志

1 [root@021rjsh216086s data]# mysqlbinlog -compact mysql-bin.000044

2 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;3 /*!40019 SET @@session.max_insert_delayed_threads=0*/;4 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;5 DELIMITER /*!*/;6 # at 4

7 #180117 16:29:53 server id 1 end_log_pos 120 CRC32 0xe9eec1e7 Start: binlog v 4, server v 5.6.38-log created 180117 16:29:53

8 # Warning: this binlog is either in use or was notclosed properly.9 BINLOG '

10 gQlfWg8BAAAAdAAAAHgAAAABAAQANS42LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAefB12 7uk=13 '/*!*/;14 # at 120

15 #180117 16:30:29 server id 1 end_log_pos 199 CRC32 0xdf86372d Query thread_id=3 exec_time=0 error_code=0

16 SET TIMESTAMP=1516177829/*!*/;17 SET @@session.pseudo_thread_id=3/*!*/;18 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;19 SET @@session.sql_mode=1075838976/*!*/;20 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;21 /*!\C utf8*//*!*/;22 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;23 SET @@session.lc_time_names=0/*!*/;24 SET @@session.collation_database=DEFAULT/*!*/;25 BEGIN

26 /*!*/;27 # at 199

28 #180117 16:30:29 server id 1 end_log_pos 310 CRC32 0xc0c60628 Query thread_id=3 exec_time=0 error_code=0

29 use `test`/*!*/;30 SET TIMESTAMP=1516177829/*!*/;31 insert into class values (8,'咳咳') --这就是刚insert的数据，但是临时不记录binlog后，之后的insert语句就没进来32 /*!*/;33 # at 310

34 #180117 16:30:29 server id 1 end_log_pos 341 CRC32 0x8af81f01 Xid = 46

35 COMMIT/*!*/;36 DELIMITER ;37 # End of log file

38 ROLLBACK /*added by mysqlbinlog*/;39 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;40 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

二进制日志的三种模式

语句级：Statement (默认)，包含的是原始的sql语句。

行级：Row，包含的是行更改的信息。

混合级：Mixed，前两种模式的结合，Mysql根据实际开销情况选择到底使用哪种模式。

1 mysql> show variables like '%binlog_format%';2 +---------------+-----------+

3 | Variable_name | Value |

4 +---------------+-----------+

5 | binlog_format | STATEMENT | 默认是语句级

6 +---------------+-----------+

7 1 row in set (0.00sec)8

9 mysql> set global binlog_format = 'ROW'; 将模式修改为行级10 Query OK, 0 rows affected (0.00sec)11

12 mysql> show variables like '%binlog_format%'; 必须退出，重新登录mysql，才能看到变成ROW13 +---------------+-----------+

14 | Variable_name | Value |

15 +---------------+-----------+

16 | binlog_format | ROW |

17 +---------------+-----------+

18 1 row in set (0.00 sec)

之后插入一条语句 insert into class values (10,'行级');，再看二进制日志的内容

1 [root@021rjsh216086s data]# mysqlbinlog --base64-output='decode-rows' -v mysql-bin.000046 行级为row的必须用这种方式查

7 #180117 17:17:17 server id 1 end_log_pos 120 CRC32 0xc178a01f Start: binlog v 4, server v 5.6.38-log created 180117 17:17:17

8 # Warning: this binlog is either in use or was notclosed properly.9 # at 120

10 #180117 17:17:32 server id 1 end_log_pos 192 CRC32 0x850ebcdb Query thread_id=13 exec_time=0 error_code=0

11 SET TIMESTAMP=1516180652/*!*/;12 SET @@session.pseudo_thread_id=13/*!*/;13 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;14 SET @@session.sql_mode=1075838976/*!*/;15 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;16 /*!\C utf8*//*!*/;17 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;18 SET @@session.lc_time_names=0/*!*/;19 SET @@session.collation_database=DEFAULT/*!*/;20 BEGIN

21 /*!*/;22 # at 192

23 #180117 17:17:32 server id 1 end_log_pos 243 CRC32 0x6ab562be Table_map: `test`.`class` mapped to number 72

24 # at 243

25 #180117 17:17:32 server id 1 end_log_pos 290 CRC32 0x1371869f Write_rows: table id 72flags: STMT_END_F26 ### INSERT INTO`test`.`class`27 ### SET

28 ### @1=10

29 ### @2='行级'

30 # at 290

31 #180117 17:17:32 server id 1 end_log_pos 321 CRC32 0x90e5a3ef Xid = 82

32 COMMIT/*!*/;33 DELIMITER ;34 # End of log file

35 ROLLBACK /*added by mysqlbinlog*/;36 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;37 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

大菲哥艺术留学

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
bin mysql_MySQL的log_bin和sql_log_bin 的区别

利用二进制还原数据库的时候，突然有点纠结，log_bin和sql_log_bin有什么区别呢？行吧，搜搜，结合自己的经验，简单说一下。log_bin:二进制日志。在 mysql 启动时，通过命令行或配置文件决定是否开启 binlog，而 log_bin 这个变量仅仅是报告当前 binlog 系统的状态(打开与否)。若你想要关闭 binlog，你可以通过修改 sql_log_bin 并把原来的连接 ...
复制链接

扫一扫