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 **********************