MySQL日志管理
MySQL日志管理
MySQL日志简介
错误日志
# 默认是开启：开启
# 默认路径及文件名：
- 源码和二进制安装：datadir/$hostname.err
- yum安装：/var/log/mysql.log
# 是否可以修改：可以
作用：查看MySQL启动时的报错找[Error]
# 修改日志路径
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log_error=/tmp/err.log
mysql> show variables like 'log_error';
常规日志
# 默认是开启：否
# 默认路径及文件名：datadir/$hostname.err
# 是否可以修改：可以
# 作用：记录MySQL的常规操作
# 修改日志路径（一般来说不会开启）
[root@db02 world]# vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/tmp/zls.log
二进制日志(binlog)
# 默认是否开启：否
root@localhost:(none)>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
# 默认日志路径及文件名：datadir/xxx.000001
# 是否可以修改：可以
# 作用：
1.记录已结束的DML事务语句，并拆分为多个事件(event)来进行记录
2.记录所有DDL、DCL等语句
3.总之，二进制日志会记录所有对数据库、表发生修改的操作
# 如何修改日志路径
# 以下修改方式为 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修改方式
MySQL想要开启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的默认工作模式）
root@localhost:(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或者（statment）
如何查看二进制日志
# 查看行级模式
[root@db01 mysql]# mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001
# 库内查看当前有几个binlog日记及大小
root@localhost:(none)>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 2479 |
+------------------+-----------+
# 库内查看binlog日志事件
root@localhost:(none)>show binlog events in 'mysql_bin.000001';
事件介绍
- 在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 |
+------------------+----------+
| mysql-bin.000004 | 154 |
+------------------+----------+
# 创建binlog数据库
mysql[(none)]> create database binlog;
# 查看位置点
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 319 |
+------------------+----------+
# 创建一张表
mysql[(none)]> use binlog
mysql[binlog]> create table tb1(id int);
# 查看位置点
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 486 |
+------------------+----------+
# 插入数据
mysql[binlog]> insert into tb1 values(1),(2),(3);
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 486 |
+------------------+----------+
mysql[binlog]> commit;
# 查看表信息
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
# 修改数据
mysql[binlog]> update tb1 set id=10 where id=1;
mysql[binlog]> commit;
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
# 查看位置信息
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1018 |
+------------------+----------+
mysql[binlog]> delete from tb1 where id=2;
mysql[binlog]> commit;
# 查看位置点
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1276 |
+------------------+----------+
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 10 |
| 3 |
+------+
## 删除表
mysql[binlog]> drop table tb1;
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1461 |
+------------------+----------+
## 删除库
mysql[binlog]> drop database binlog;
mysql[(none)]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1615 |
+------------------+----------+
如何恢复
# 1.查看binlog，找到起始位置和结束位置点
[root@db01 mysql]# mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001
起始位置点：219
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
结束位置点：1018
# 2.截取binlog
mysqlbinlog --start-position=219 --stop-position=1018 mysql_bin.000001 > /tmp/binlog.sql
# 3.导入截取数据
[root@db04 data]# mysql </tmp/binlog.sql
# 4.查询数据
mysql[binlog]> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| tb1 |
+------------------+
1 row in set (0.00 sec)
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
存在问题
如果误删除的库是10年前创建的
解决方案：
全量备份，配合binlog的增量部分
用户使用数据是穿插使用的，binlog也不是一个库一个库记录的
解决方案
只需要过滤出，被删除的数据库相关binlog中的SQL语句
使用-d指定数据库截取binlog
[root@db04 data]# mysqlbinlog -d zls2 -vvv --base64-output=decode-row mysql_bin.000001
刷新binlog
# 刷新binlog
1.重启数据库会自动刷新binlog
2.当binlog大小达到1G时，会自动刷新出下一个binlog
3.手动执行 flush logs;
4.使用mysqladmin flush-log
[root@db01 mysql]# mysqladmin -uroot -p'456' flush-log
5.使用MySQLdump做备份时，可以刷新binlog
[root@db02 data]# mysqldump -A -F > /tmp/full.sql
删除binlog
原则：
在存储能力范围内，能保留多少binlog就保留多少binlog
# 根据生存时间删除日志
# 临时生效
set global expire_logs_days = 7;
# 永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
# 删除指定时间段binlog
purge binary logs before now() - interval 3 day;
# 指定binlog名字删除，之前binlog都删除
root@localhost:(none)>purge binary logs to 'mysql_bin.000002';
# 重置binlog，删除所有binlog
mysql> reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
慢日志(慢查询日志)
# 默认是否开启：否
root@localhost:(none)>show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
# 默认路径文件名：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@db02 data]# mysqldumpslow
-s：指定如何排序
c：按照记录次数
t：按照时间排序
r：按照返回记录排序
l：按照查询时间排序
ac：按照记录次数 倒序排序
at：按照时间排序 倒序排序
ar：按照返回记录 倒序排序
al：按照查询时间 倒序排序
-t：top N
-g：指定正则表达式
[root@db02 data]# mysqldumpslow -s t -t 10 db02-slow.log
percona 慢查询工具
# percona下载地址
wget http://test.driverzeng.com/MySQL_Package/percona-toolkit-3.0.11-1.el6.x86_64.rpm
[root@db02 ~]# yum localinstall -y percona-toolkit-3.0.11-1.el6.x86_64.rpm
[root@db02 ~]# pt-query-digest /application/mysql/data/db02-slow.log
慢日志可视化界面
Anemometer基于pt-query-digest将MySQL慢查询可视化
httpss://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载
httpss://github.com/box/Anemometer 可视化代码下载
