MySQL日志

2022-06-26 14:35:11

MySQL日志

 

 

1.日志

1.1错误日志

  • 记录MySQL启动及工作过程中，状态、报错、警告。

设置错误日志

# vim /etc/my.cnf
log_error=/var/log/mysqld.log   //自定义或放/data/3306/data/mysqld-error.log
# cd /data/
# mkdir -p 3306/data
# chown -R mysql.mysql /data/
# systemctl restart mysqld
# mysql -uroot -p
> select @@log_error;       //查看路径

　　

1.2二进制配置查看

1.2.1二进制日志作用

  • 数据恢复必备的日志

  • 主从复制依赖的日志

1.2.2MySQL二进制日志设置

# vim /etc/my.cnf
server_id=6
log_gin=/data/3306/data/binlog/mysql-bin

　　

  • server_id是5.7之后开二进制日志必须添加的参数

  • log_gin=打开二进制功能

  • /data/3306/binlog/指定存放路径

  • mysql-bin文件名前缀

# cd /data/3306/data
# mkdir -p binlog
# systemctl restart mysqld
# ls binlog/
mysql-bin.000001    mysql-bin.index

  • mysql-bin是在配置文件配置的前缀

  • 000001 MySQL每次重启，重新生成新的

1.3二进制日志内容

一、日志内容

  • 除了查询类的语句，都会记录，即所有数据库变更类的语句。

  • 记录语句种类

    • DDL（数据定义语言）：create、drop

    • DCL（数据控制语言）：grant、revoke

    • DML（数据操作语言）：insert、updata、delete

  • 不同语句的记录格式说明

    • DDL、DCL直接以语句（statement）方式记录

    • DML语句有三种模式：SBR、RBR、MBR

> select @@binlog_format;
ROW

  • statement--->SBR：做什么记录什么，即SQL语句

  • row------------>RBR：记录数据行的变化（默认模式，推荐）

  • mixed-------->MBR：自动判断记录模式

  • SBR和RBR的区别

区别项 SBR RBR（默认、推荐）
记录内容 SQL语句 记录数据行的变化
可读性 较强 差
日志量 小 大
日志记录准确性 数据误差 没有误差 
//同select @@binlog_format;      查看指定的
> show variables like ‘%binlog%‘;
...
binlog_format           ROW

二、二进制日志三种模式的区别

  • 一、Statement 基于语句模式

    • MySQL中的复制功能最初是基于从主机到从机的SQL语句的传播。这被称为基于语句的日志记录。您可以通过启动服务器来使用此格式 –binlog-format=STATEMENT。

    • 优点：不需要记录每一行的变化，减少binlog日志量，节省了I/O以及存储资源，提高性能。

    • 缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能加入，使mysql，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易。在statement level下，目前已经发现的就有不少情况会造成mysql的赋值问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。

    如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。

  • 二、Row 基于行模式

    • 在基于行的日志记录中，主服务器将事件写入二进制日志，以指示单个表行如何受到影响。因此，表总是使用主键来确保行可以被有效识别，这一点很重要。您可以通过启动该服务器来使服务器使用基于行的日志记录 –binlog-format=ROW。

    • 优点：所有的语句都可以复制，不记录执行的sql语句的上下文相关的信息，仅需记录那一条记录被修改成什么了

    • 缺点：binlog打了很多，复杂的回滚时binlog中会包含大量的数据；主服务器上执行update语句时，所有发生变化的记录都会写到binlog中；比如有这样一条update语句：update product set ownner_member_id=‘d‘ where owner_member_id=‘a‘，执行之后，日志中记录的不是这条update语句所对应的事件（mysql是以事件的形式来记录bin-log日志），而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大。

  • 三、Mixed 混合模式（row与statement结合）

    实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会一statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

三、二进制日志事件

  • 二进制日志内容以事件（binlog events）为最小记录单元

  • 对于DDL和DCL，一个语句就是一个事件

  • 对于DML（标准的事务语句），只记录已提交的事物的DML语句

begin:      事件1
a           事件2
b           事件3
commit:     事件4
事件的构成（为了截取日志）
# mysqlbinlog mysql-bin.000001      //查看二进制日志内容
# at 4          //事件开始的位置（position）
...end_log_pos 123  //事件结束的位置（position）
#190308 10:05:03    //事件发生的事件
create database test    //事件内容

　　

二进制日志的基础查看
//查看二进制日志的配置信息
> show variables like ‘%log_bin%‘;
+---------------------------------+-------------+
| Variable_name                   | Value       |
+---------------------------------+-------------+
| log_bin                         | ON          |
| log_bin_basename                | /var/log/mysql/mysql-bin          |
| log_bin_index                   | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF         |
| log_bin_use_v1_row_events       | OFF         |
| sql_log_bin                     | ON          |
+---------------------------------+-------------+

  • log_bin 开启二进制日志的开关

  • log_bin_basename 位置

  • sql_log_bin 临时开启或关闭二进制日志的小开关

二进制日志的基本信息

> show binary logs;		//查看二进制日志pos点
| Log_name      	| File_size 	|
+---------------	+-----------	+
| binlog.000001 	|    177 	    |
| binlog.000002 	|    333     	|

> show master status;		//查看当前使用的日志

> show binlog events;
> show binlog events in ‘mysql-bin.000001‘;
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info
| mysql-bin.000001 |   4 | Format_desc | 	3		| 120 | Server ver: 5.7.29-log, Binlog ver: 4 
| mysql-bin.000001 | 363 | Query       | 	3 		| 440 | 
| mysql-bin.000001 | 440 | Query       | 	3 		| 519 | flush privileges

　　

二进制日志内容的查看和截取

# mysqlbinlog --start-position=154 --stop-position=219 mysql-bin.000002 >test.sql
# mysqlbinlog test.sql

  • mysqlbinlog --help查看参数

  • --start-position开始截取pod点

  • --stop-position结束截取pod点

1.4传统二进制日志数据还原

实例

//创建库、表
> create database binlog charset utf8mb4;
> use binlog;
> create table t1(id int) engine=innodb charset=utf8mb4;
> insert into t1 balues(1),(2),(3);
> commit;
> insert into t1 balues(11),(12),(13);
> commit;
> update t1 set id=10 where id>10;
> drop database binlog;

　　

数据恢复

> show master status;	//查看当前使用二进制日志
> show binlog events in ‘mysql-bin.000002‘
找到建库点和删库点
# mysqlbinlog --start-position=157 --stop-position=1572 mysql-bin.000002 > binlog.sql
> set sql_log_bin=0;	//临时关闭当前会话的binlog记录
> source /data/3306/data/binlog/binlog.sql
> set sql_log_bin=1;	//打开当前会话的binlog记录

　　

1.5Gtid二进制日志数据恢复

1.5.1基于Gtid的二进制日志管理

  • gtid（Global Transaction ID）简介

    • 全局唯一的事物编号

    • 幂等性

    • GtID包括两部分：Server_uuid:，Tx_id:每个事件做相应的编号

配置文件

> show variables like ‘%gtid%‘;
...
gtid_mode			OFF		//需要开启

# vim /etc/my.cnf	//添加
gtid_mode=on
enforce_gtid_consistency=true	//强制执行GTID一致性
log_slave_updates=1		//开启主从复制中从库binlog与主库保持一直
# systemctl restart mysqld
只有开启log_slave_updates，从库binlog才会记录主库同步的操作日志，并统一GTID信息。

> show master status;		//此时Executed_Gtid_Set还没有值
> create database test2;
> show master status;	//Executed_Gtid_Set出现值
事物id
> show binlog events in ‘mysql-bin.000003‘;
出现一个事物id

 

1.5.2基于gtid截取日志

  • 对于DDL和DCL一个操作就是一个GTID。

  • 对于DML，一个完整的事物就是已给GTID。

  • --include-gtids=包含

  • --exclude-gtids=排除

  • --skip-gtids=跳过gtid的幂等性机制的检查，即截取日志的时候不带有gtid的信息

--include-gtids 截取指定的gtid

--exclude-gtids 排除指定的gtid

--skip-gtids 跳过gtid的幂等性机制的检查，即截取日志的时候不带有gtid的信息

  • 截取1-3号事物

# mysqlbinlog --include-gtids=‘5c351518-78ec-11e7-8e7a-005056a610c3:1-3‘ /data/binlog/mysql-bin.000001 >/data/gtid.sql

　　

  • 截取1-10gtid事物，跳过6号和8号事物

# mysqlbinlog 
--include-gtids=‘5c351518-78ec-11e7-8e7a-005056a610c3:1-10‘ 
--exclude-gtids=‘5c351518-78ec-11e7-8e7a-005056a610c3:6,5c351518-78ec-11e7-8e7a-005056a610c3:8‘ /data/binlog/mysql-bin.000001 >/data/gtid.sql
实例
//创建库、表
> create database gtid charset utf8mb4;
> use gtid;
> create table t1(id int) engine=innodb charset=utf8mb4;
> insert into t1 balues(1),(2),(3);
> insert into t1 balues(11),(12),(13);
> drop database gtid;
> show master status\G
************** 1. row *************
             File: mysql-bin.000003
         Position: 1306
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-6
建库在2，删库在6（不能选6）

> show binlog events in ‘mysql-bin.000003‘;
...

　　

数据恢复

> mysqlbinlog --skip-gtids --include-gtids=‘3E11FA47-71CA-11E1-9E33-C80AA9429562:2-5‘ mysql-bin.000003 >gtid.sql;
> set sql_log_bin=0;
> source /data/3306/data/binlog/gtid.sql
> set sql_log_bin=0;

　　

1.6慢日志

  • 记录运行较慢的语句记录slowlog中

  • 功能是辅助优化的工具日志

  • 应激性的慢可以通过show processlist进行监控

  • 一段时间的慢可以进行slow记录、统计

慢日志配置

> show variables like ‘%slow_query%‘;	//查看是否开启
slow_query_log			OFF
slow_query_log_file		/var/lib/mysql/mysql-slow.log
//重新或新开一个会话才能看到修改值

> select @@log_query_time;	//查看触发日志阈值
> show global variables like ‘%long_query_time%‘;
> show variables like ‘%long_query_time%‘;

// 开启 记录没有使用索引查询语句
> show variables like ‘%log_queries_not_using_indexes%‘;

　　

临时开启

//临时开启，默认未开启，开启会影响性能，mysql重启会失效
set global slow_query_log = 1;

//设置阈值
set global long_query_time=3;

　　

永久开启

# vim /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/data/3306/data/zhangsan-slow.log
long_query_time=0.1		//默认配置10秒钟
log_queries_not_using_indexes=1

# systemctl restart mysqld

　　

模拟超时

# cd /data/3306/data/
# cat zhangsan-slow.log		//目前日志还没有记录
# mysql -p
> select sleep(4);

//查询有没有日志
> show global status like ‘%slow_queries%‘;

　　

慢日志分析工具mysqldumpslow

  • -s 按照那种方式排序

    • c：访问计数

    • l：锁定时间

    • r：返回记录

    • t：查询时间

    • al：平均锁定时间

    • ar：平均访问记录数

    • at：平均查询时间

  • -t 是top n的意思，返回前面多少条数据。

  • -g 可以跟上正则匹配模式，大小写不敏感。

  • mysqldumpslow --help

//得到返回记录集的10个SQL
# mysqldumpslow	-s r -t 10 /data/3306/data/lisi-slow.log

//得到访问次数最多的10个SQL
# mysqldumpslow	-s c -t 10 /data/3306/data/lisi-slow.log

//得到按照时间排序的前10条里面含有左连接的查询语句
# mysqldumpslow	-s t -t 10 -g "LEFT JOIN" /data/3306/data/lisi-slow.log

//结合| more使用，防止爆屏情况
# mysqldumpslow	-s r -t 10 /data/3306/data/lisi-slow.log

　　

 

> mysqlbinlog --skip-gtids --include-gtids=‘3E11FA47-71CA-11E1-9E33-C80AA9429562:2-5‘ mysql-bin.000003 >gtid.sql;
> set sql_log_bin=0;
> source /data/3306/data/binlog/gtid.sql
> set sql_log_bin=0;

MySQL日志

上一篇：Mysql面试常见知识点总结(一)


下一篇：如何使用RMAN安全的备份数据库