CentOS MySQL日志管理

MySQL日志管理MySQL日志简介

错误日志

**默认是否开启：**开启

默认路径及文件名：

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

**是否可以修改：**可以

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

如何修改：

**默认是否开启：**否

**默认路径及文件名：**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=1general_log_file=/tmp/gen.lo二进制日志（binlog）

**默认是否开启：**否

**默认路径及文件名：**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_idserver_id=1log-bin=mysql-binmysql-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-binbinlog_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 binlogDatabase changedmysql> 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: 0mysql> 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: 0mysql> 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 -pEnter 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 binlogReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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.删除指定时间段binlogpurge 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，删除所有binlogmysql> 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-tookitpt-query-digest 慢查询日志名字【文章原创作者盐城网站设计 yancheng.html 提供，感恩】快乐要懂得分享，才能加倍的快乐

相关文章：

你感兴趣的文章：

标签云：