首页 > 数据库 >MySQL（二十二）其他数据库日志（二）bin log二进制日志

MySQL（二十二）其他数据库日志（二）bin log二进制日志

时间：2023-05-09 20:34:50浏览次数：49  
标签：bin binlog log No binlog.000016 mysql 日志

MySQL（二十二）其他数据库日志（二）bin log二进制日志

bin log二进制日志

  • bin log 即 binary log，二进制日志，也叫做变更日志

  • 它记录所有更新数据的DDL和DML语句，但是不包含没有修改数据的语句（如Select、show等），以事件的形式记录保存在二进制文件中

  • 可以用于主从服务器之间的数据同步复制以及服务器遇到故障的时候数据的无损失恢复

    如果想记录所有的语句（如为了识别有问题的查询），可以使用通用查询日志

​ 可以说MySQL数据库的数据备份、主备、主主、主从都离不开bin log，需要依靠bin log来同步数据保持数据的一致性。

image-20230509161208954
1 查看与配置默认的情况
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

  • log_bin：是否开启binlog

    这时候在这里修改是改不了的，因为log_bin是只读变量

    mysql> set global log_bin = 1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

  • log_bin_basename：存放路径

  • log_bin_index：存放路径下的文件，index表示创建的索引

  • log_bin_trust_function_creators：是否信任函数，因为函数的多次执行可能会导致主从数据不一致

  • sql_log_bin：是否将更新sql写入log

​ 因此需要修改配置文件的方式进行修改：

vi /etc/my.cnf

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# bin log的文件名，设置之后就会开启binlog
log-bin=ryuu-bin
# bin log的文件过期时间
binlog_expire_logs_seconds=6000
# binlog的大小
max_binlog_size=100M
...

​ wq保存完成后重启mysql服务

systemctl restart mysqld

​ 查看路径下的bin_log文件：

  • 可以发现每个bin log有时很大一个有时很小，这是由于每次mysql启动都会生成一个bin log
  • 数据库文件最好不要与mysql数据文件放在一个磁盘下，防止数据库文件所在磁盘发生故障的时候，无法使用bin log进行数据恢复
bash-4.4# cd /var/lib/mysql
bash-4.4# ls -l
total 335696
-rw-r----- 1 mysql mysql    196608 May  9 06:53 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql   8585216 May  1 08:31 '#ib_16384_1.dblwr'
drwxr-x--- 2 mysql mysql      4096 May  1 08:23 '#innodb_redo'
drwxr-x--- 2 mysql mysql       187 Apr 13 08:08 '#innodb_temp'
-rw-r----- 1 mysql mysql       362 May  9 06:54  89d64201156e.log
-rw-r----- 1 mysql mysql       378 May  9 06:53  89d64201156e.log.bak
drwxr-x--- 2 mysql mysql       231 Apr  5 09:25  atguigudb
drwxr-x--- 2 mysql mysql       304 May  8 06:17  atguigudb1
-rw-r----- 1 mysql mysql        56 Dec  3 02:47  auto.cnf
-rw-r----- 1 mysql mysql 141593294 Apr 13 08:08  binlog.000014
-rw-r----- 1 mysql mysql  69542211 May  9 06:53  binlog.000015
-rw-r----- 1 mysql mysql       157 May  9 06:53  binlog.000016
2 查看bin log文件
2.1 mysqlbinlog

​ 查看当前bin log文件

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000014 | 141593294 | No        |
| binlog.000015 |  69542211 | No        |
| binlog.000016 |       157 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

​ 插入两条数据，再次执行发现文件变大了

mysql> insert into student1 values(18, "Jerry", "yiban");
Query OK, 1 row affected (0.00 sec)

mysql> update student1 set name = 'Tom' where stu_no = 20;
Query OK, 1 row affected (0.00 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000014 | 141593294 | No        |
| binlog.000015 |  69542211 | No        |
| binlog.000016 |       809 | No        |
+---------------+-----------+-----------+

​ 使用mysqlbinlog工具可以查看bin log文件的伪代码sql，我这里docker里面显示没有mysqlbinlog就不演示了，该命令的参数

  • -v：将二进制进行转换

image-20230509170632527

2.2 show binlog events in 'binlog.index'
show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count];
  • in 'log_name'：指定查询的bin log文件名（不指定就是第一个bin log）
  • from pos：从哪个pos起始点开始查（不指定就从第一个）
  • limit [offset,]：偏移量
  • row_count：查询总条数
mysql> show binlog events in 'binlog.000016';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000016 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.31, Binlog ver: 4    |
| binlog.000016 | 126 | Previous_gtids |         1 |         157 |                                      |
| binlog.000016 | 157 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000016 | 236 | Query          |         1 |         317 | BEGIN                                |
| binlog.000016 | 317 | Table_map      |         1 |         386 | table_id: 176 (atguigudb1.student1)  |
| binlog.000016 | 386 | Write_rows     |         1 |         438 | table_id: 176 flags: STMT_END_F      |
| binlog.000016 | 438 | Xid            |         1 |         469 | COMMIT /* xid=16851864 */            |
| binlog.000016 | 469 | Anonymous_Gtid |         1 |         548 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000016 | 548 | Query          |         1 |         638 | BEGIN                                |
| binlog.000016 | 638 | Table_map      |         1 |         707 | table_id: 176 (atguigudb1.student1)  |
| binlog.000016 | 707 | Update_rows    |         1 |         778 | table_id: 176 flags: STMT_END_F      |
| binlog.000016 | 778 | Xid            |         1 |         809 | COMMIT /* xid=16851867 */            |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
12 rows in set (0.00 sec)
3 使用日志恢复数据

​ 先插入一些数据

mysql> insert into student1(stu_no, name, class) values(21, 'aaa', '1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student1(stu_no, name, class) values(22, 'bbb', '2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student1(stu_no, name, class) values(23, 'ccc', '3');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student1;
+--------+----------+--------+
| stu_no | name     | class  |
+--------+----------+--------+
|      1 | zhangsan | yiban  |
|      3 | lisi     | erban  |
|      6 | liu      | liuban |
|      7 | 7        | 7      |
|      8 | wangwu   | erban  |
|     15 | zhaoliu  | erban  |
|     18 | Jerry    | yiban  |
|     20 | Tom      | sanban |
|     21 | aaa      | 1      |
|     22 | bbb      | 2      |
|     23 | ccc      | 3      |
+--------+----------+--------+
11 rows in set (0.00 sec)

​ 再执行几个更新操作：

mysql> delete from student where stu_no = 21;
Query OK, 0 rows affected (0.47 sec)

mysql> update student1 set name = 'aaa' where id = 22;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> update student1 set name = 'aaa' where stu_no = 22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

​ 模拟一次误操作：

mysql> delete from student1 where stu_no > 20;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from student1;
+--------+----------+--------+
| stu_no | name     | class  |
+--------+----------+--------+
|      1 | zhangsan | yiban  |
|      3 | lisi     | erban  |
|      6 | liu      | liuban |
|      7 | 7        | 7      |
|      8 | wangwu   | erban  |
|     15 | zhaoliu  | erban  |
|     18 | Jerry    | yiban  |
|     20 | Tom      | sanban |
+--------+----------+--------+
8 rows in set (0.00 sec)

​ 查看目前的bin log文件，一般情况下要再创建一个bin log，以防止使用的过程同时也在向其中写入数据

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000014 | 141593294 | No        |
| binlog.000015 |  69542211 | No        |
| binlog.000016 |  12054127 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000014 | 141593294 | No        |
| binlog.000015 |  69542211 | No        |
| binlog.000016 |  12054171 | No        |
| binlog.000017 |       157 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

​ 查看要恢复使用的bin log内容：

mysql> show binlog events in 'binlog.000016';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000016 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.31, Binlog ver: 4    |
| binlog.000016 | 126 | Previous_gtids |         1 |         157 |                                      |
| binlog.000016 | 157 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000016 | 236 | Query          |         1 |         317 | BEGIN                                |
| binlog.000016 | 317 | Table_map      |         1 |         386 | table_id: 176 (atguigudb1.student1)  |
| binlog.000016 | 386 | Write_rows     |         1 |         438 | table_id: 176 flags: STMT_END_F      |
| binlog.000016 | 438 | Xid            |         1 |         469 | COMMIT /* xid=16851864 */            |
| binlog.000016 | 469 | Anonymous_Gtid |         1 |         548 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000016 | 548 | Query          |         1 |         638 | BEGIN                                |
...

​ 这里就不演示了

image-20230509185108626
3.1 基于show binlog events的pos进行恢复
/usr/bin/mysqlbinlog --start-position=884 --stop-position=1729 --database=atguigudb1 /var/log/binlog.000016 | /usr/bin/mysql -uroot -pxxx -v atguigudb1
  • /usr/bin/mysqlbinlog：mysqlbinlog指令地址
  • --start-position、--stop-position：要恢复的开始结束pos（从binlog enents得知）
  • --database：数据库
  • /usr/bin/mysql -uroot -pxxx -v atguigudb1：需要登录数据库并选择数据库
3.2 基于mysqlbinlog的时间戳恢复

​ 开始结束时间通过命令mysqlbinlog查看：

image-20230509185938268 
/usr/bin/mysqlbinlog --start-datetime=xxx --stop-datetime=xxx --database=atguigudb1 /var/log/binlog.000016 | /usr/bin/mysql -uroot -pxxx -v atguigudb1
4 删除bin log日志

​ MySQL的二进制文件可以配置自动删除，同时也可以进行安全地手动删除。

4.1 Purge Master Logs：删除指定日志
purge master | binary logs to '指定文件名'; # 不包括该文件
purge master | binary logs before '指定日期'; # 不包括该日期

测试：

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000014 | 141593294 | No        |
| binlog.000015 |  69542211 | No        |
| binlog.000016 |  12054171 | No        |
| binlog.000017 |       201 | No        |
| binlog.000018 |       201 | No        |
| binlog.000019 |       201 | No        |
| binlog.000020 |       157 | No        |
+---------------+-----------+-----------+
7 rows in set (0.00 sec)

mysql> purge master to binlog.000017;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to binlog.000017' at line 1
mysql> purge master to 'binlog.000017';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to 'binlog.000017'' at line 1
mysql> purge master logs to 'binlog.000017';
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000017 |       201 | No        |
| binlog.000018 |       201 | No        |
| binlog.000019 |       201 | No        |
| binlog.000020 |       157 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
4.2 Reset master：删除全部bin log

测试：

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000017 |       201 | No        |
| binlog.000018 |       201 | No        |
| binlog.000019 |       201 | No        |
| binlog.000020 |       157 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       157 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
5 其他场景

​ 二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是遇到数据量大、数据库和表很多（分库分表）的场景，利用二进制日志进行数据恢复是很有挑战性的，因为起始位置并不好管理。

​ 一个有效的解决方案就是配置主从数据库服务器甚至是一主多从的架构，把二进制日志文件的内容通过中继日志同步到从服务器中，这样就能够有效避免数据库故障导致的数据异常等问题。

6 bin log的写入机制
  • 事务执行的过程中先把日志写入到binlog cache中，事务提交后再将binlog cache中的内容一次性写入到bin log file中（这是因为一个事务的bin log不能被拆开，无论这个事务有多大，也要确保一次性写入，所以系统为每一个线程都在内存中分配一块binlog cache）
  • 可以通过binlog_cache_size参数控制单个线程binlog cache的大小，如果存储内容超过了这个值，就要暂存到磁盘（Swap）。binlog的刷盘流程整体如下：
image-20230509194342788

上图的write和redo log的刷盘一样，是将日志写入操作系统的文件缓存，然后由操作系统决定什么时候写入磁盘文件

  • write和fsync的时机可以由参数sync_binlog控制，默认为0。表示每次提交事务都只执行write，由操作系统决定什么时候写入磁盘文件。但是如果操作系统宕机，会导致page cache的binlog丢失

    image-20230509195436648

  • 为了安全起见，可以将sync_binlog设置为1，表示每次提交事务都进行write和fsync操作，这么做性能多少会收到影响

  • 还可以设置sync_binlog为N（N>1），表示每次提交事务都write，但是积攒N个事务才进行fsync

    image-20230509195644943

标签：bin,binlog,log,No,binlog.000016,mysql,日志
From： https://www.cnblogs.com/tod4/p/17386179.html

相关文章

  • MySQL（二十二）其他数据库日志（一）通用查询日志和错误日志
    MySQL（二十二）其他数据库日志（一）通用查询日志和错误日志1MySQL支持的日志1.1日志类型慢查询日志：记录执行时间超过long_query_time的所有查询，方便我们对查询进行优化通用查询日志：记录所有连接的起始和终止时间，以及连接发送给数据库的所有指令，对复原操作的实际场景、发现问题......
  • yolov5训练栏杆检测模型日志记录
    yolov5训练栏杆检测模型日志记录 (wind_2021)H:\PytorchProject\yolov5_train_xinru_2023050901>(wind_2021)H:\PytorchProject\yolov5_train_xinru_2023050901>(wind_2021)H:\PytorchProject\yolov5_train_xinru_2023050901>(wind_2021)H:\PytorchProject\yol......
  • KingbaseES数据库运维案例之---permission denied to create "sys_catalog.xxx"
    ​KingbaseES数据库运维案例之---permissiondeniedtocreate"sys_catalog.bdsj_bdgl_test"案例说明：在KingbaseES数据库kingbase.conf修改了search_path='"$user",sys_catalog'后，在数据库下执行创建对象操作，出现以下故障。适用版本：KingbaseESV8R6一、问题现象如下所示......
  • 单例模型中懒汉模型，饿汉模型，枚举与日志等级
    1.单例模型将构造器私有化，控制创建对象的个数懒汉模型时间换空间符合单线程多线程不可以synchronized同步锁使单例可以一直用但效率不行单例模型一般使用在一台机器Asynchronized异步锁饿汉模型空间换时间可以解决安全利用JVM利用static只初始化一次......
  • logstash之grok
    nginx匹配示例nginx日志格式'$remote_user[$time_local]$http_x_Forwarded_for$remote_addr$request$status$upstream_status''$http_x_forwarded_for''$upstream_addr'......
  • weblogic 相关概念
    计算机服务器部署https://blog.csdn.net/cunfu/article/details/117738439https://blog.csdn.net/suixinfeixiangfei/article/details/121595225?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-4-121595225-blog-118541751.23......
  • MySQL笔记之文件和日志
    一、存储文件1、存放位置MySQL数据库会在data目录下，以数据库为名，为每一个数据库建立文件夹，用来存储数据库中的表文件数据。不同的数据库引擎，每个表的扩展名也不一样，例如：MyISAM用“.MYD”作为扩展名，Innodb用“.ibd”等。 2、FRM表结构信息文件无论是哪种存储引擎，创建表之......
  • linux定时备份日志文件脚本，重启jar简易脚本
     1.编写脚本文件saveLogs.sh （备份文件到指定位置重命名，然后情况文件继续写入）  cp/opt/zcgl/zcgl.log/opt/zcgl/logs/zcgl-`date+%Y%m%d`.log&  sleep2  >zcgl.log2. 定时脚本crontab 打开设置：crontab-e 输入内容：5923***sh/opt/zcgl/saveLogs.......
  • MySQL的redolog和binlog有哪些区别？
    redolog是innodb生成的日志，主要为了保证数据的可靠性；binlog是MySQLserver层面上生成的日志，主要用于pointintime恢复和主从复制。undolog主要用于事务的回滚和一致性快照读(MVCC多版本并发控制)。MySQL中的redolog、binlog的功能很像，那么有什么区别？区别如下：red......
  • Android logcat: Unexpected EOF! 解决办法
     【问题表现】无论使用控制台adb（ adblogcat|grep"SilentPlayerManager"）还是使用AndroidStudio的logcat，都提示： logcat:UnexpectedEOF!Thismeansthateitherthedeviceshutdown,logdcrashed,orthisinstanceoflogcatwasunabletoreadlogmessagesas......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99