MySQL日志和监控

1 Binlog日志

1.1 Binlog日志说明

Binlog日志包含描述数据库修改的语句，如create table、update等数据变更的语句，不会记录类似select、show等不修改数据的语句 。
Binlog日志的作用主要有以下2个：

• 复制：主库的变更先写入Binlog日志，然后传到从库进行回放。
• 恢复：当误操作后，可以先把全量备份的Binlog日志导入到某个新的实例中，然后通过全量备份时间点到误操作中间的Binlog日志解析出所有事务，并在新实例中执行这些事务，达到恢复到误操作前的状态。

1.2 Binlog日志操作

1.2.1 开启Binlog日志功能

如果要开启Binlog日志，就需要在mysql配置文件的[mysqld]模块中加上如下语句，该配置表示Binlog日志的存放路径为/data/mysql/logs/，文件名为mysql-bin后接Binlog日志的序列号

log-bin="/data/mysql/logs/mysql-bin"

1.2.2 关闭Binlog日志功能

如果要关闭Binlog日志，就需要在mysql配置文件中加上如下语句

skip_log_bin

或者

disable_log_bin

如果只是想关闭当前会话的Binlog日志，则可以通过以下命令动态的修改

set sql_log_bin = 'OFF';

1.2.3 设置Binlog日志格式

Binlog日志可以设置为以下3种格式。

日志格式	说明	优点	缺点
statement	记录的是SQL语句，不需要 记录每行数据的变化	日志量少，节约IO，性能高	在主从复制中可能会导致主从数据不一致，如果使用了不确定 函数，类似UUID()函数等
row	记录的是每行数据修改前后的值	主从数据基本一致，支持闪回	日志量多
mixed	以上两种格式的混合	日志量少，节约IO，性能高，解决了statement格式部分数据不一致的情况	不支持闪回，部分高可用架构 不支持该格式，不方便将数据同步到其他类型的数据库

如果要设置Binlog日志格式，可以在mysql配置文件的[mysqld]模块中加上如下语句

binlog_format=row

除了在配置文件中配置外，还可以通过以下命令动态的修改Binlog日志格式

-- 全局级别修改
set global binlog_format = 'row';

-- 会话级别修改
set session binlog_format = 'statement';

1.2.4 列表Binlog日志文件

通过以下命令可以查看MySQL实例当前记录了哪些Binlog日志文件

show binary logs;

命令示例：
以下命令示例显示，当前只有一个名称为mysql-bin.000001的Binlog日志文件，且最新的位点为988，并且该Binlog日志文件未加密。

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       988 | No        |
+------------------+-----------+-----------+
1 row in set (0.00 sec)

mysql>

1.2.5 查看Binlog日志事件

查看Binlog日志事件之前，需要先通过以下命令查看当前Binlog日志的位点

show master status\G;

然后再通过以下命令查看从某个具体位点之后的Binlog日志事件

-- 从mysql-bin.000001文件中查看792位点开始的5条Binlog日志事件
show binlog events in 'mysql-bin.000001' from 792 limit 5\G;

命令示例：
以下命令示例中，首先查看到当前Binlog日志的位点为792，然后创建了一张名为t_test的数据表，然后再查到当前Binlog日志的位点为988，最后再查看Binlog日志文件mysql-bin.000001中从位点792开始的所有的记录。

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

mysql>
mysql> create table t_test(id int);
Query OK, 0 rows affected (0.01 sec)

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

mysql>
mysql> show binlog events in 'mysql-bin.000001' from 792\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 792
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 869
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 869
 Event_type: Query
  Server_id: 1
End_log_pos: 988
       Info: use `member`; create table t_test(id int) /* xid=47 */
2 rows in set (0.00 sec)

mysql>

命令示例：
以下命令示例中，首先查看到当前Binlog日志的位点为1389，然后往t_member表中插入一行数据，然后再查到当前Binlog日志的位点为1740，最后再查看Binlog日志文件mysql-bin.000001中从位点1389开始的所有的记录。

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

mysql> insert into `t_member` values (1, '202209040001', '李小明', '18934528794', '23423423@qq.com');
Query OK, 1 row affected (0.00 sec)

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

mysql> show binlog events in 'mysql-bin.000001' from 1389\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 1389
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 1468
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 2. row **********************