MySQL（二十二）其他数据库日志（二）bin log二进制日志与中继日志
MySQL（二十二）其他数据库日志（二）bin log二进制日志
1 bin log二进制日志
-
bin log 即 binary log，二进制日志，也叫做变更日志
-
它记录所有更新数据的DDL和DML语句，但是不包含没有修改数据的语句（如Select、show等），以
事件的形式记录保存在二进制文件中 -
可以用于主从服务器之间的数据同步复制以及服务器遇到故障的时候数据的无损失恢复
如果想记录所有的语句（如为了识别有问题的查询），可以使用通用查询日志
可以说MySQL数据库的数据备份、主备、主主、主从都离不开bin log，需要依靠bin log来同步数据保持数据的一致性。
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：将二进制进行转换
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 |
...
这里就不演示了
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查看：
/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的刷盘流程整体如下：
上图的write和redo log的刷盘一样，是将日志写入操作系统的文件缓存，然后由操作系统决定什么时候写入磁盘文件
-
write和fsync的时机可以由参数
sync_binlog控制，默认为0。表示每次提交事务都只执行write，由操作系统决定什么时候写入磁盘文件。但是如果操作系统宕机，会导致page cache的binlog丢失
-
为了安全起见，可以将
sync_binlog设置为1，表示每次提交事务都进行write和fsync操作，这么做性能多少会收到影响 -
还可以设置
sync_binlog为N（N>1），表示每次提交事务都write，但是积攒N个事务才进行fsync
🌟7 bin log与redo log的对比
redo log是物理日志，记录的内容是“在某个页面的某偏移量上做了什么修改”，是由InnoDB存储引擎产生的，恢复是磁盘上的物理恢复bin log是逻辑日志，记录内容是原始逻辑，如“给Id=1的C字段值加一”，是由mysql Service层产生的，恢复是逻辑上的恢复- 虽然都用于保证持久化，但是侧重点不同：
redo log让InnoDB存储有了崩溃恢复的能力bin log保证了MySQL集群架构的数据唯一性
🌟8 两阶段提交
redo log和bin log提交时机导致的问题
-
在执行语句的过程中，会记录
redo log和bin log两块日志，以事务为基本单位，redo log在日志执行过程中可以不断写入，但bin log只有在提交的时候才写入，所以两者的写入时机不同
-
如果此时一个事务提交（表示
redo log完成了写入持久化），mysql宕机了导致bin log没有来得及写入，但是事务已经提交了，数据库恢复之后不会进行任何恢复操作，那么备机和从机从主机获取到bin log加载到中继日志后，就会出现主从数据不一致的现象
-
因此为了解决
bin log和redo log的逻辑一致性问题，InnoDB存储引擎采用两阶段提交方案，即将Redo log分为prepare和commit两个阶段进行提交，第二个阶段会检查bin log是否存在再写入redo log完成提交，这样的话如果数据库宕机，两个日志都不会写入成功
-
使用两阶段提交后，写入bin log发生异常也不会有影响，因为MySQL在重启后根据redo log恢复数据的时候，发现redo log还处于prepare阶段，并且没有对应的bin log文件，就会回滚该事务
2 中继日志（Relay log）
1 介绍
- 中继日志只在主从服务器架构的从服务器上存在。
- 从服务器为了和主服务器保持数据一致，要从主服务器的bin log中读取二进制内容，然后把读取的信息写入本地的日志文件即中继日志中
- 最后从服务器读取中继日志的内容，并根据中继日志的内容对服务器的数据进行更新，完成主服务器的数据同步。
- 搭建好主从服务器之后，中继日志会默认保存在数据库数据目录下面，文件名的格式是
从服务器 -relay-bin.序号，还有一个索引文件-relay-bin.序号.index，用来定位正在使用的中继文件
2 查看中继日志
中继日志和二进制日志的格式相同，因此可以使用mysqlbinlog来查看
3 恢复的典型错误
如果从服务器宕机，有时为了恢复系统需要对系统进行重装，这样就可能导致从服务器的名与之前的不同，而中继日志中是包含服务器名的，因此会导致中继日志无法使用，解决的办法就是将从服务器的主机名修改回之前的主机名。
