mysql-binlog日志分析查询

接上一篇博客，聊聊 binglog
mysql 数据中数据恢复与主从同步，通过二进制日志 binlog 实现

Binlog记录日志方式

• statement
• row
• mixed

查看

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_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: 是否开启了二进制文件，是一个二进制文件名, 生成的 binglog 文件以 log_bin 值为前缀的 xxxx-000001的文件。必须在 /etc/my.cfg 中配置才生效，默认关闭
• binlog_format: 二进制日志的记录方式，可以设置为statement、row、mixed
• max_binlog_size: 设置单个二进制日志文件的最大大小，以字节为单位，超过此值大小，则二进制日志文件会自动滚动

statement

记录对数据库做出修改的语句,select 除外

• 修改配置

vim /etc/my.cnf

server-id=123
binlog_format=statement
log_bin=ikang_binlog
max_binlog_size=500m

配置文件修改后重启

• 查看配置

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /var/lib/mysql/ikang_binlog       |
| log_bin_index                   | /var/lib/mysql/ikang_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)
mysql> show variables like '%binlog_f%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)

日志格式：statement

• 查看日志

# 插入两条数据
# 查看日志
mysql> show binlog events in 'ikang_binlog.000001';
+---------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name            | Pos | Event_type     | Server_id | End_log_pos | Info                                                   |
+---------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| ikang_binlog.000001 |   4 | Format_desc    |       123 |         123 | Server ver: 5.7.28-log, Binlog ver: 4                  |
| ikang_binlog.000001 | 123 | Previous_gtids |       123 |         154 |                                                        |
| ikang_binlog.000001 | 154 | Anonymous_Gtid |       123 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| ikang_binlog.000001 | 219 | Query          |       123 |         306 | BEGIN                                                  |
| ikang_binlog.000001 | 306 | Query          |       123 |         426 | use `ikang_db`; insert into ad_role values(10, 'vipp') |
| ikang_binlog.000001 | 426 | Xid            |       123 |         457 | COMMIT /* xid=14 */                                    |
| ikang_binlog.000001 | 457 | Anonymous_Gtid |       123 |         522 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| ikang_binlog.000001 | 522 | Query          |       123 |         609 | BEGIN                                                  |
| ikang_binlog.000001 | 609 | Query          |       123 |         729 | use `ikang_db`; insert into ad_role values(12, 'vipp') |
| ikang_binlog.000001 | 729 | Xid            |       123 |         760 | COMMIT /* xid=16 */                                    |
+---------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> show binary logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| ikang_binlog.000001 |      1073 |
+---------------------+-----------+
1 row in set (0.00 sec)

mysql> show master logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| ikang_binlog.000001 |      1073 |
+---------------------+-----------+
1 row in set (0.00 sec)
mysql> 

# 删除数据，日志文件大写改变
mysql> delete from ad_role where id=10;
Query OK, 1 row affected (0.01 sec)

mysql> show master logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| ikang_binlog.000001 |      1369 |
+---------------------+-----------+
1 row in set (0.00 sec)

• 当前二进制文件查看和位置

mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| ikang_binlog.000001 |     1369 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 日志滚动
ine 1
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| ikang_binlog.000002 |      154 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

滚动重新生成二进制文件，binlog 同名索引文件记录了日志信息

关闭日之后，不会生成记录

row

过去的历史值和现在的新值

• 修改配置重启

mysql> show variables like '%binlog_f%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

• 插入语句查看结果

mysqlbinlog --base64-output=decode-rows -vv ikang_binlog.000003;

日志记录了，insert 操作，日志看起来比较乱，有章可循，以 #at 开头 COMMIT/*!*/; 结尾，一次完整记录。

• 日志分析

日志里有起始和结束地址、时间。可进行查询

# 按照时间查询
mysqlbinlog --base64-output=decode-rows --start-datetime='2020-06-19 23:20' --stop-datetime='2020-06-19 23:25' -vv ikang_binlog.000003; 
# 按位置查询
mysqlbinlog --start-position 1119 --stop-position 2223 ikang_binlog.000003;

• 日志其他操作