mysql日志管理

mysql日志管理

1.mysql日志分类

日志文件	选项	文件名	程序
错误日志	--log-error	host_name.err	N/A
常规日志	--general_log	general_log、host_name.log	N/A
慢速查询日志	--slow_query_log、--long_query_time	host_name-slow. log、slow_log	mysqldumpslow
二进制日志	--log-bin、--expire-logs-days	host_name-bin.000001	mysqlbinlog
审计日志	--audit_log、--audit_log_file	audit.log	N/A

2.mysql错语日志配置

作用：记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志
配置方法:

[mysqld]
log-error=/data/mysql/mysql.log

查看配置方式：

mysql> show variables like '%log%error%';

3.mysql常规日志

作用：记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启
配置方法

[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log

查看配置方式：

show variables like '%gen%';

4.二进制日志

二进制日志主要记录已提交的数据记录，以event的形式记录到二进制文件中
二进制记录格式：
　　row：行模式，即数据行的变化过程，上图中Age=19修改成 Age=20的过程事件。
　　statement：语句模式，上图中将update语句进行记录。
　　mixed：以上两者的混合模式
binlog的作用：用于备份、恢复与复制

4.1 二进制日志管理

1、开启二进制日志

set sql_log_bin=0    #在会话级别修改为临时关闭
vi /etc/my.cnf
log-bin=/data/mysql/mysql-bin  #在全局打开binlog
binlog_format=row #设置二进制日志记录格式为row
sync_binlog=1     #sync_binlog参数来控制数据库的binlog刷到磁盘上去。sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

2、设置二进制日志记录格式（建议是ROW）：

配置文件中修改：
binlog-format=ROW
命令行修改
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

3、查看binlog设置

show variables like '%binlog%';

4、查看二进制文件数量

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1473 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       143 |
| mysql-bin.000005 |       143 |
| mysql-bin.000006 |       120 |
+------------------+-----------+
6 rows in set (0.00 sec)

5、查看正在使用的二进制日志文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6、查看二进制日志文件内容

[root@db02 mysql]# mysqlbinlog  --base64-output=decode-rows -v mysql-bin.000006
在数据库上查看
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 |         1 |         120 | Server ver: 5.6.42-log, Binlog ver: 4 |
| mysql-bin.000002 | 120 | Stop        |         1 |         143 |                                       |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

7、刷新二进制日志

mysql> flush logs;

8、截取二进制日志用做恢复数据

mysqlbinlog --start-position=xx  --stop-position=xx >a.sql
例子：通过截取binlog恢复损坏数据
创建数据库
mysql> create database oldboy;
Query OK, 1 row affected (0.00 sec)
mysql> use oldboy
Database changed
创建表
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
插入内容
mysql> insert into t1 values(1,'zhang');
Query OK, 1 row affected (0.01 sec)
删除数据库
mysql> drop database oldboy;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| world              |
+--------------------+
6 rows in set (0.00 sec)
查看二进制文件，找出需工截取的部分
[root@db02 mysql]# mysqlbinlog  --base64-output=decode-rows -v mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190425 13:59:25 server id 1  end_log_pos 120 CRC32 0xc3b4c8bc     Start: binlog v 4, server v 5.6.42-log creat
ed 190425 13:59:25 at startup# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 120  ============>可以从此处进行截取
#190425 13:59:25 server id 1  end_log_pos 220 CRC32 0x5b44b064     Query    thread_id=4    exec_time=746    erro
r_code=0SET TIMESTAMP=1556171965/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autoc
ommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database oldboy
/*!*/;
# at 220
#190425 13:59:25 server id 1  end_log_pos 338 CRC32 0xd98d520a     Query    thread_id=4    exec_time=772    erro
r_code=0use `oldboy`/*!*/;
SET TIMESTAMP=1556171965/*!*/;
create table t1(id int,name varchar(20))
/*!*/;
# at 338
#190425 13:59:25 server id 1  end_log_pos 412 CRC32 0x269d55e6     Query    thread_id=4    exec_time=790    erro
r_code=0SET TIMESTAMP=1556171965/*!*/;
BEGIN
/*!*/;
# at 412
#190425 13:59:25 server id 1  end_log_pos 462 CRC32 0xe9e307c9     Table_map: `oldboy`.`t1` mapped to number 71
# at 462
#190425 13:59:25 server id 1  end_log_pos 508 CRC32 0x05abce7f     Write_rows: table id 71 flags: STMT_END_F
### INSERT INTO `oldboy`.`t1`
### SET
###   @1=1
###   @2='zhang'
# at 508
#190425 13:59:25 server id 1  end_log_pos 539 CRC32 0xee83af99     Xid = 116
COMMIT/*!*/;   ==================>到此结束
# at 539
#190425 13:59:25 server id 1  end_log_pos 637 CRC32 0xd3a1140b     Query    thread_id=4    exec_time=846    erro
r_code=0SET TIMESTAMP=1556171965/*!*/;
drop database oldboy
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
将截取的内容写入到mysql.sql中
[root@db02 mysql]# mysqlbinlog  --start-position=120 --stop-position=539 mysql-bin.000001 > /tmp/mysql.sql
进行恢复
mysql> source /tmp/mysql.sql
此时发现数据已经恢复过来
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oldboy             |
| performance_schema |
| test               |
| testdb             |
| world              |
+--------------------+
7 rows in set (0.00 sec)

9.二进制日志文件删除
默认情况下，不会删除旧的日志文件

根据存在时间删除日志
SET GLOBAL expire_logs_days = 7;
或者
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
根据文件名删除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';

5.慢日志管理

　　慢日志是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
　　通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的 I

5.1 慢日志配置

long_query_time：  设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log ：  指定是否开启慢查询日志
slow_query_log_file：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引
配置案例：
在配置文件里添加，注意：slow_query_log_file路径一定要先创建好，然后给mysql用户权限，再重启mysql服务即可生效
[mysqld]
slow_query_log=1
slow_query_log_file=/data/slow/slow.log
long_query_time=0.5
log_queries_not_using_indexes

5.2 处理慢日志

mysqldumpslow命令
mysqldumpslow -s c -t 10 /data/slow/slow.log  这会输出记录次数最多的10条SQL语句，其中：
-s:是表示按照何种方式排序
c、t、l、r:分别是按照记录次数、时间、查询时间、返回的记录数来排序
ac、at、al、ar：表示相应的倒叙；
-t：是top n的意思，即为返回前面多少条的数据；