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的意思，即为返回前面多少条的数据；