必知必会：Binlog 详解

Binlog 介绍

binlog 是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。

binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL 执行过的所有语句。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

Binlog 模式

binlog有三种模式：ROW（行模式）, Statement（语句模式）, Mixed（混合模式）

行模式：ROW

记录那条数据修改了，注意：记录的是这条记录的全部数据，即使只更新了一个字段，binlog里也会记录所有字段的数据

优缺点	介绍
优点	不记录 sql 语句的上下文信息，日志内容会非常清楚的记录每条数据详细的变更细节， 即使只更新了一个字段，binlog 里也会记录所有字段的数据
缺点	binlog 日志会非常大，mysql 主从同步时，会产生大量磁盘 IO

语句模式：Statement

每一条会修改数据的sql都会记录在binlog中

优缺点	介绍
优点	不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能
缺点	由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句 在执行的时候的一些相关信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同 的结果。另外 mysql 的复制,像一些特定函数功能，slave 可与 master 上要保持一致会有很多相关问题

混合模式：Mixed

在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种

查看、开启

查看 mysql 是否开启 binlog

# ON为开启、OFF为关闭，默认是关闭
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

查看 mysql 的 binlog 模式

# 三种模式，MySQL5.7中默认为：Statement
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

通过配置文件开启 binlog

[root@Fp-01 ~]# vim /etc/my.cnf 
[mysqld]
server-id=1		# 主从参数
log-bin=mysql-bin	# 开启 binlog
binlog-format=ROW	# 指定 binlog 模式

MySQL 中 binlog 相关命令

# 获取 binlog 文件日志列表
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       106 |
+------------------+-----------+
1 row in set (0.00 sec)


# 查看当前正在写入的 binlog 文件
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

# 查看 master 的 binlog 文件
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       106 |
+------------------+-----------+
1 row in set (0.00 sec)


# 查看第一个 binlog 文件内容
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)

MySQL 扩展

创建一个新的数据库会在目录中生成什么

# 创建一个新数据库
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)

# 退出
mysql> \q
Bye

# 生成一个 aaa 的目录
[root@Fp-01 ~]# ls /var/lib/mysql
aaa  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

# 进入目录，生成文件，存放的数据库的默认字符集
[root@Fp-01 ~]# ls /var/lib/mysql/aa
db.opt

总结

只有注入思想的博客才是好的博客