MySQL日志管理
一、MySQL日志
错误日志： --log-error 文件名 host_name.err
常规日志： --general-log 文件名 host_name.log
慢查询日志： --slow_query_log 文件名 host_name-slow.log 程序：mysqldumpslow
--long_query_log
二进制日志 --log-bin 文件名： host_name-bin.0000001 程序：mysqlbinlog
审计日志 --audit_log 文件名：audit.log
1.1错误日志
作用：就是排查MySQL运行过程中出现的故障；
错误日志默认开启;
存放路径：datadir
[root@#localhost data]# ls
auto.cnf care4 ibdata1 ibtmp1 #localhost.pid school
care2 employtest ib_logfile0 #localhost.localdomain.err mysql sys
care3 ib_buffer_pool ib_logfile1 localhost.localdomain.err performance_schema worknode
日志名称：localhost.localdomain.err
定制存放路径：在my.cnf中 mysqld下配置 log_error=/data/error.log 目录必须有mysql权限
1.2二进制日志（binlog）
作用：主从要依赖二进制日志；
数据恢复要依赖二进制日志；
配置(默认没有开启):
server_id=1~65535
log_bin=/data/mysql/data/mysql-bin /data/mysql/data/是存放目录，要有mysql：mysql权限 mysql-bin是文件名
在my.cnf中配置
[mysqld] server_id=3306 log_bin=/data/mysql/data/mysql-bin
1.2.1二进制日志记录的内容
记录数据库所有的变更类的日志，DCL，DML，DDL；
DDL，DCL 以语句的方式原样记录；
DML 记录已经提交的事务；
DML记录日志的格式包括：statement，row，mixed，通过binlog-format=row参数控制，这个参数只能控制DML语句的格式。
statement ：SBR 语句模式记录日志
ROW ：RBR 行模式，记录行的变化
MIXED ：MBR 混合模式
‘ SBR和RBR的对比：
SBR可读性比较强，对应范围操作，日志量比较小，但是有可能不准确，可能会出现记录不准确的情况，比如日志记录了now（），等恢复数据时候会出错；
RBR可读性弱，记录之日量大，不会出现记录错误；
1.2.2二进制记录的单元
最小单元是事件；
DDL每一个语句就是一个事件；
DML；一个完整的事务
1.2.3二进制日志的管理
查看二进制日志的存放位置
mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | log_bin | ON | | log_bin_basename | /data/mysql/data/mysql-bin | | log_bin_index | /data/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------------------+ 6 rows in set (0.03 sec)
查看二进制日志
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 307 | +------------------+-----------+ 1 row in set (0.01 sec)
刷新日志：
mysql> flush logs; Query OK, 0 rows affected (0.03 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 354 | | mysql-bin.000002 | 154 | +------------------+-----------+ 2 rows in set (0.00 sec)
查看正在使用的二进制日志
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
查看二进制日志中执行的DDL语句
mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.16-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 6 | 319 | create database binlog | | mysql-bin.000002 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 384 | Query | 6 | 484 | use `binlog`; create table t(id int) | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 6 rows in set (0.00 sec)
查看二进制日志文件内容：从一个at到下一个at就是一个event
[root@#localhost data]# mysqlbinlog mysql-bin.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #220720 16:33:49 server id 6 end_log_pos 123 CRC32 0x9da4e6f9 Start: binlog v 4, server v 5.7.16-log created 220720 16:33:49 # Warning: this binlog is either in use or was not closed properly. BINLOG ' 7b3XYg8GAAAAdwAAAHsAAAABAAQANS43LjE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AfnmpJ0= '/*!*/;
查看详细内容：
[root@#localhost data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
截取二进制日志
[root@#localhost data]# mysqlbinlog --start-position=219 --stop-position=319 mysql-bin.000002
1.2.4二进制日志恢复数据
创建实验数据：
mysql> create database he; Query OK, 1 row affected (0.01 sec) mysql> use he; Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.04 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.03 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> drop database he; Query OK, 1 row affected (0.12 sec)
查看使用的是哪个日志，查看恢复日志的开始位置
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1195 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000002'; +------------------+------+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.16-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 6 | 319 | create database binlog | | mysql-bin.000002 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 384 | Query | 6 | 484 | use `binlog`; create table t(id int) | | mysql-bin.000002 | 484 | Anonymous_Gtid | 6 | 549 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 549 | Query | 6 | 637 | create database he | | mysql-bin.000002 | 637 | Anonymous_Gtid | 6 | 702 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 702 | Query | 6 | 795 | use `he`; create table t1(id int) | | mysql-bin.000002 | 795 | Anonymous_Gtid | 6 | 860 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 860 | Query | 6 | 930 | BEGIN | | mysql-bin.000002 | 930 | Table_map | 6 | 973 | table_id: 108 (he.t1) | | mysql-bin.000002 | 973 | Write_rows | 6 | 1013 | table_id: 108 flags: STMT_END_F | | mysql-bin.000002 | 1013 | Xid | 6 | 1044 | COMMIT /* xid=24 */ | | mysql-bin.000002 | 1044 | Anonymous_Gtid | 6 | 1109 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 1109 | Query | 6 | 1195 | drop database he | +------------------+------+----------------+-----------+-------------+---------------------------------------+ 17 rows in set (0.00 sec)
导出有效的日志：
[root@#localhost data]# mysqlbinlog --start-position=549 --stop-position=1109 mysql-bin.000002>/tmp/he.sql
开始恢复数据
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source /tmp/he.sql Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) 将二进制日志开启 mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec)
1.2.5 二进制日志的gtid模式
对于binlog中的每一个事务，都会生成一个GTID号码；DDL，DCL一个事件就是一个事务，就会生成一个GTID号，DML语句从begin到commit是一个事务，生成一个GTID号，
GTID的组成：server_uudi:TID TID是一个自增长的数字，从1开始
[root@#localhost data]# cat auto.cnf [auto] server-uuid=a99a829e-fd98-11ec-9933-000c29d9caa0
GTID的配置和开启
[root@#localhost data]# vi /etc/my.conf gtid-mode=on enforce-gtid-consistency=true
查看；
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec) mysql> create database gttid; Query OK, 1 row affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000003 | 316 | | | a99a829e-fd98-11ec-9933-000c29d9caa0:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec)
新增一个事务就会生成一个新的GTID
mysql> use gttid Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.05 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000003 | 480 | | | a99a829e-fd98-11ec-9933-000c29d9caa0:1-2 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
基于GTID的数据恢复：
drop database gttid; mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.16-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'a99a829e-fd98-11ec-9933-000c29d9caa0:1' | | mysql-bin.000003 | 219 | Query | 6 | 316 | create database gttid | | mysql-bin.000003 | 316 | Gtid | 6 | 381 | SET @@SESSION.GTID_NEXT= 'a99a829e-fd98-11ec-9933-000c29d9caa0:2' | | mysql-bin.000003 | 381 | Query | 6 | 480 | use `gttid`; create table t1(id int) | | mysql-bin.000003 | 480 | Gtid | 6 | 545 | SET @@SESSION.GTID_NEXT= 'a99a829e-fd98-11ec-9933-000c29d9caa0:3' | | mysql-bin.000003 | 545 | Query | 6 | 640 | drop database gttid | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 8 rows in set (0.00 sec)
导出日志： --skip-gtids 跳过之前的已经产生的gtid号，不检查原来的生成的gitd号，恢复时候生成新的gtid号，不然恢复报错
[root@#localhost data]# mysqlbinlog --skip-gtids --include-gtids='a99a829e-fd98-11ec-9933-000c29d9caa0:1-2' mysql-bin.000003>/tmp/gttid.sql
恢复数据：
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.01 sec) mysql> source /tmp/gttid2.sql mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec)
1.3慢日志slow-log
记录运行较慢的语句；
1.3.1配置开启慢日志记录
#开关
slow_query_log=1
#存放位置
slow_query_log_file=/data/mysql/data/slow.log
#时间设定
long_query_time=0.2
#没有走索引也记录
log_queries_not_using_indexes
vi /etc/my.cnf slow_query_log=1 slow_query_log_file=/data/mysql/data/slow.log long_query_time=0.2 log_queries_not_using_indexes
1.3.2慢日志分析
[root@#localhost data]# mysqldumpslow -s c -t 10 /data/mysql/data/slow.log
二、重做日志 Redo log
任何事务的操作都会记录redo日志，InnoDB引擎独有，用于恢复数据库到宕机的位置。
Redo log 记录的是新数据的备份。
在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。
当系统崩溃时候，虽然数据没有持久化，但是RedoLog已经持久化。
系统可以根据RedoLog的内容，将说有的数据恢复到最新的状态。
数据库崩溃以后：
先要做前滚，redo log+旧的数据块
在做回滚（undo、rollback）
保护做了修改有没有刷到磁盘的脏页。
2.1 前滚
rolling forward（前滚）
数据库启动时候，然后通过 online redologs 中的重做日志，重现实例崩溃前对数据库的修改操作。在恢复过程中对已经提交的事务，但尚未写入数据文件的那部分数据全部写入数据文件。
2.2 回滚
rolling back
rolling forward之后，虽然已经提交的修改操作更改的数据都已经被写入数据文件，但是在实例崩溃时候，部分未提交的事务操作数据也被写入到数据文件，这些事务必须被撤销。
2.3 redo log重做日志的由来
InnoDB有buffer pool（简称bp） 是数据库页面的缓存，对InnoDB的任何修改都会首先在bp的page上进行，然后这样的页面将被标记为dirty并被放到专门的flush list上，后续将由master thread 或专门的刷脏线程阶段性的将这些页面写入磁盘（disk or ssd）。
这样的好处是避免每次写操作都是操作磁盘导致大量的随机IO，阶段性的刷脏也也可以将多次对页面的修改merge成一次的IO操作，同时一步写入也降低了访问的时延。
然而，如果在dirty page还未刷入磁盘时候，server非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。
为了避免上述问题，Innodb将所有的对页面的修改操作写入一个专门的文件，并在数据库启动时候从此文件进行恢复操作，这个文件就是redo log file。
这样的技术推迟了bp页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时候的时延。带来的问题是额外的写redo log操作的开销，以及数据库启动时候恢复曹组所需的时间。
2.4 如何写redo log 重做日志
redo log info > redo log buffer > redo log files
redo log buffer:
1、日志会先写到 redo log buffer ，根据指定条件刷新到redo log file
2、由log block 组成
3、每个log block 512字节，所以不需要double write，因为每次刷新都是原子的
重做日志都是以512字节进行存储的
即：重做日志缓冲，重做日志文件都是以块block的方式进行保存，统称为重做日志块redo log block 大小：512字节。
2.5 日志块的组成
日志块头 log blockheader 12 字节
日志本身 492字节
日志块尾 log block tailer 8字节
2.6 log buffer 往log file里面写redo log的触发机制
01、每1S 写一次；
02、大于log buffer 空间的1/2的时候，默认是8M；
03、commit的时候；
04、log buffer到了1M的时候
05、WAL日志先写机制，当后台脏也往磁盘写的时候，先要把日志写过来。WAL：磁盘上的数据是比较旧的，而日志文件是比较新的。日志比最小的数据还要新。
redo文件在系统的位置
[root@localhost ~]# cd /mysql/data/ -rw-r----- 1 mysql mysql 536870912 10月 9 21:31 ib_logfile0 -rw-r----- 1 mysql mysql 536870912 10月 4 17:17 ib_logfile1
06、MySQL里边的redo日志只能做崩溃恢复，只针对innodb表起作用。
07、redo里边记录的是日志写入，里面有个很重要的概念叫做lsn
2.7 checkpoint
1、缩短数据库的恢复时间；
2、缓冲池不够用时候，将脏页刷到磁盘；
3、重做日志不可用时候，刷新脏页。
当数据库发生宕机时候，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘，数据库只需要对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。
2.8 checkpoint种类
sharp checkpoint 当MySQL正常关闭的时候，需要将所有的脏页都刷新；
fuzzy checkpoint 为了考虑数据库的性能，MySQL按照一定算法刷新部分脏页。
三、日志序列号 LSN
3.1 日志介绍
show engine innodb status\G Log sequence number 2642018 ---redo log buffer 的lsn，存放在redo log buffer中 Log flushed up to 2642018 ---redo log file的lsn，存放redo log file 中的lsn Pages flushed up to 2642018 ---数据刷盘data page 的lsn Last checkpoint at 2642009 ---checkpooint，存放在redo log file里面的第一个文件的头部
3.12 日志的整个生命周期
创建日志：事务创建一条日志，写入redo log buffer 中；
日志刷盘：日志从缓存中写到磁盘的日志文件中；
数据刷盘：日志对应的脏页数据写而入磁盘中的数据文件；
写checkpoint：日志被当着checkpoint写入日志文件中；
3.3 重做日志的配置
mysql> show variables like '%innodb_log%'; +-----------------------------+-----------+ | Variable_name | Value | +-----------------------------+-----------+ | innodb_log_buffer_size | 16777216 | | innodb_log_checksums | ON | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 536870912 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_log_write_ahead_size | 8192 | +-----------------------------+-----------+ 7 rows in set (0.01 sec)
可以配置在my.cnf
innodb_log_file_size=512M innodb_log_file_in_group=2 innodb_log_buffer_size=16M
四、undo log日志
undo用于回滚，undo也回产生redo，undo除了回滚，还用于并发控制；
4.1查看参数
mysql> show variables like '%undo%'; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | innodb_max_undo_log_size | 1073741824 | | innodb_undo_directory | ./ | | innodb_undo_log_truncate | OFF | -----------当undo表空间慢的时候是否自动清理 | innodb_undo_logs | 128 | ----------8.0版本后参数被改为innodb_rollback_sgements | innodb_undo_tablespaces | 0 | +--------------------------+------------+ 5 rows in set (0.01 sec)
浙公网安备 33010602011771号