MySQL日志管理

MySQL日志简介

错误日志

默认是否开启：开启

默认路径及文件名：

• 源码和二进制安装：datadir/$hostname.err
• yum安装：/var/log/mysql.log

是否可以修改：可以

作用：查看MySQL启动时的报错找[Error]

如何修改：

## 查看错误日志路径 mysql> show variables like 'log_error'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | log_error | ./db01.err | +---------------+------------+ ## 修改错误日志路径 [root@db01 ~]# vim /etc/my.cnf [mysqld] log_error=/tmp/log.txt

常规日志

默认是否开启：否

默认路径及文件名：datadir/$hostname.log

是否可以修改：可以

作用：记录MySQL的常规操作

如何修改：

## 查看常规日志是否开启和路径 mysql> show variables like '%gen%'; +------------------+----------------------------------+ | Variable_name | Value | +------------------+----------------------------------+ | general_log | OFF | | general_log_file | /application/mysql/data/db01.log | +------------------+----------------------------------+ 2 rows in set (0.00 sec) ### 常规日志开启(在企业中是不会开启该日志) [root@db01 ~]# vim /etc/my.cnf [mysqld] general_log=1 general_log_file=/tmp/gen.lo

二进制日志（binlog）

默认是否开启：否

## 查看二进制日志是否开启 mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec)

默认路径及文件名：datadir/xxx.000001

是否可以修改：可以

作用：

• 记录已结束的DML事务语句，并拆分为多个事件（event）来进行记录
• 记录所有DDL、DCL等语句
• 总之，二进制日志会记录所有对数据库、表发生修改的操作

如何修改：

## 以下修改方式为 mysql5.6 [root@db02 ~]# vim /etc/my.cnf [mysqld] log-bin=mysql-bin [root@db02 ~]# vim /etc/my.cnf [mysqld] log-bin=/application/mysql/data/mysql-bin [root@db02 ~]# vim /etc/my.cnf [mysqld] log-bin=/tmp/zls-bin ## MySQL5.7修改方式 MySQL5.7想要开binlog必须配置 server_id server_id=1 log-bin=mysql-bin mysql-bin.index：MySQL二进制日志binlog的索引文件，有几个binlog会记录几个binlog

二进制日志的工作模式

• statment：语句模式（MySQL5.6默认的工作模式）

mysql> show variables like 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 将所有的语句，记录在binlog中 优点：通俗易懂，占用磁盘空间小 缺点：不严谨

• row：行级模式（MySQL5.7的默认工作模式）

mysql[(none)]> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 将所有的语句及变化过程，记录在binlog中 优点：严谨 缺点：不易懂、占用磁盘空间大

• mixed：混合模式

语句模式和行级模式的混合模式 自己判断，什么时候只记录语句，什么时候记录语句和变化过程 没人用

工作模式如何修改

[root@db02 data]# vim /etc/my.cnf [mysqld] log-bin=/application/mysql/data/mysql-bin binlog_format=row

如何查看二进制日志

## 查看默认语句模式 [root@db01 ~]# mysqlbinlog /application/mysql/data/mysql-bin.000001 ## 查看行级模式 [root@db01 ~]# mysqlbinlog -vvv --base64-output=decode-row /application/mysql/data/mysql-bin.000001 ## 库内查看当前有几个binlog日志及大小 mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 120 | +------------------+-----------+ ## 库内查看binlog日志的事件 mysql> mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.50-log, Binlog ver: 4 | +------------------+-----+-------------+-----------+-------------+---------------------------------------+

事件介绍

• 在binlog中最小的记录单元为event
• 一个事务会被拆分成多个事件（event）

事件的特性

• 每个event都有一个开始位置（start position）和结束位置（stop position）。
• 所谓的位置就是event对整个二进制的文件的相对位置。
• 对于一个二进制日志中，前120个position是文件格式信息预留空间。

## mysql5.6 在MySQL5.6中，一个新的binlog起始位置点是120,120是系统信息预留空间 其实120，就是该文件的大小 143是空binlog，里面没有任何SQL语句执行 ## mysql5.7 在MySQL5.7中，一个新的binlog起始位置点是154,154是系统信息预留空间 其实154，就是该文件的大小 177是空binlog，里面没有任何SQL语句执行

二进制日志数据故障恢复

## 查看binlog信息 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ ## 创建binlog数据库 mysql> create database binlog; 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 | 220 | | | | +------------------+----------+--------------+------------------+-------------------+ ## 创建一张表 mysql> use binlog Database changed mysql> create table tb1(id int); Query OK, 0 rows affected (0.13 sec) ## 查看位置点 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 322 | | | | +------------------+----------+--------------+------------------+-------------------+ ## 插入数据 mysql> insert into tb1 values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 547 | | | | +------------------+----------+--------------+------------------+-------------------+ mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> update tb1 set id=10 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb1; +------+ | id | +------+ | 10 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 770 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> delete from tb1 where id=2; Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) 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> select * from tb1; +------+ | id | +------+ | 10 | | 3 | +------+ ## 删除表 mysql> drop table tb1; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1108 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ## 删除库 mysql> drop database binlog; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1197 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

如何恢复

## 1.查看binlog，找到起始位置点和结束位置点 [root@db01 ~]# mysqlbinlog -vvv --base64-output=decode-row /application/mysql/data/mysql-bin.000001 起始点:120 结束点:853 ## 2.截取binlog [root@db01 ~]# mysqlbinlog --start-position=120 --stop-position=853 /application/mysql/data/mysql-bin.000001 >/tmp/1.sql ## 3.导入截取出来的数据 [root@db01 ~]# mysql </tmp/1.sql -uroot -p Enter password: ## 4.查询数据 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | binlog | | linux50 | | mysql | | performance_schema | | test | | world | | www | +--------------------+ 8 rows in set (0.00 sec) mysql> use binlog Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_binlog | +------------------+ | tb1 | +------------------+ 1 row in set (0.00 sec) mysql> select * from tb1; +------+ | id | +------+ | 10 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)

此类方法存在问题

• 如果误删除一年之前创建的数据

解决方案： 全量备份，配合binlog的增量部分

• 用户使用数据是穿插使用的，binlog也不是一个库一个库记录的

解决方案： 只需要过滤出，被删除的数据库相关binlog中的sql语句 使用-d指定数据库截取binlog [root@db01 data]# mysqlbinlog -d(指定) tb1(表名) -vvv --base64-output=decode-row mysql-bin.000001

刷新binlog

# 1.重启数据库会自动刷新binlog # 2.当binlog大小达到1G的时候，会自动刷新出下一个binlog # 3.手动执行 flush logs; # 4.使用mysqladmin flush-log [root@db04 data]# mysqladmin flush-log # 5.使用mysqldump做备份时，可以刷新binlog [root@db02 data]# mysqldump -A -F > /tmp/full.sql

删除binlog

原则： 在存储能力范围内，能保留多少binlog就保留多少binlog ## 1.根据存在时间删除日志 # 临时生效 mysql> set global expire_logs_days = 7; Query OK, 0 rows affected (0.01 sec) # 永久生效(不建议使用) [root@db01 data]# vim /etc/my.cnf [mysqld] expire_logs_days = 7 ## 2.删除指定时间段binlog purge binary logs before now() - interval 3 day; ## 3.指定binlog名字删除，之前的binlog都删除 purge binary logs to 'mysql-bin.000010'; mysql[(none)]> purge binary logs to 'mysql-bin.000007'; ## 4.重置binlog，删除所有binlog mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 120 | +------------------+-----------+ 1 row in set (0.00 sec)

慢日志（慢查询日志）

默认是否开启：否

mysql> show variables like 'slow%'; +---------------------+---------------------------------------+ | Variable_name | Value | +---------------------+---------------------------------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /application/mysql/data/db01-slow.log | +---------------------+---------------------------------------+ 3 rows in set (0.00 sec)

默认路径及文件名：datadir/$hostname-slow.log

是否可以修改：可以

作用：

• 记录执行的比较慢的SQL语句

如何修改：

[root@db01 ~]# vim /etc/my.cnf [mysqld] # 指定是否开启慢查询日志 slow_query_log = 1 # 指定慢日志文件存放位置（默认在data） slow_query_log_file=/application/mysql/data/slow.log # 设定慢查询的阀值(默认10s) long_query_time=0.05 # 不使用索引的SQL语句是否记录到慢查询日志 log_queries_not_using_indexes # 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志 min_examined_row_limit=1000（鸡肋）

查看慢查询日志

[root@db01 ~]# mysqldumpslow -s：指定如何排序 c：按照记录次数 t：按照时间排序 r：按照返回记录排序 l：按照查询时间排序 ac：按照记录次数 倒序排序 at：按照时间排序 倒序排序 ar：按照返回记录 倒序排序 al：按照查询时间 倒序排序 -t：top N -g：指定正则表达式 [root@db01 ~]# mysqldumpslow -s ar /application/mysql/data/slow.log ## 第三方percona公司提供 percona-tookit pt-query-digest 慢查询日志名字