12-Mysql的日志管理

一、mysql常见日志

二、错误日志

• 配置方法:

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

• 查看配置方式：

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

• 作用：

记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志,默认在data目录下

三、一般查询日志

• 配置方法:

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

• 查看配置方式：

show variables like '%gen%';

• 作用：

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

四、二进制日志

1. 二进制日志都记录了什么？

   已提交的数据记录，以event的形式记录到二进制文件中

2. 二进制记录格式有哪些？

   • row：行模式，即数据行的变化过程，上图中Age=19修改成 Age=20的过程事件。
   • statement：语句模式，上图中将update语句进行记录。
   • mixed：以上两者的混合模式。

3. 三总模式有什么优缺点？

row模式记录得数据修改得过程，缺点是量级比较大，优点是恢复得数据得一致性高，建议使用此模式

statement是记录事务得执行语句，优点是量级比较小，缺点是恢复数据得一致性不高，例如果语句中有函数，如以当前时间作为某个列得值，那么他在恢复的时候还是会执行同样得语句使用当前得时间，而不是我从创建语句时候得时间，系统默认得模式

mixed：以上两者的混合模式。

4. binlog的作用备份恢复、复制

4.1 binlog的查看和配置方法

1. 开启二进制日志

set sql_log_bin=0    --->在会话级别修改为临时关闭
vi /etc/my.cnf
log-bin=/data/mysql/mysql-bin  --->binlog的日志路径和打开binlog
sync_binlog=1 ---> 1表示每次事务提交都会刷新binlog日志到磁盘，0表示binglog-buffer满了才会刷新，建议是1
binlog_format=row  -->记录二进制的日志类型

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.2 binlog实时点恢复

1. 新建一个测试数据库和表，然后在表里插入数据

create database test1;

create table t1 (id int,name varchar(20));
mysql> use test1;
Database changed
mysql> 
mysql> create table t1 (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> insert into t1 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(2,'zhangsan2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3,'zhangsan4');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

2. 查看二进制日志和正在使用的二进制日志

mysql> show  binary logs; #---> 查看二进制日志

mysql> show  master status; #---> 查看正在使用的二进制日志


show binlog events in 'my-bin.000002'; #--->查看二进制日志详情
+---------------+------+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name      | Pos  | Event_type  | Server_id | End_log_pos | Info                                                   |
+---------------+------+-------------+-----------+-------------+--------------------------------------------------------+
| my-bin.000002 |    4 | Format_desc |        10 |         120 | Server ver: 5.6.36-log, Binlog ver: 4                  |
| my-bin.000002 |  120 | Query       |        10 |         192 | BEGIN                                                  |
| my-bin.000002 |  192 | Table_map   |        10 |         240 | table_id: 86 (test.t1)                                 |
| my-bin.000002 |  240 | Write_rows  |        10 |         290 | table_id: 86 flags: STMT_END_F       
            事件起始的position号码          事件结束的position号码    事件详情

3. 模拟误操作，删除表中所有所有数据

delete from t1;
commit;

4. 截取需要恢复事务的起始position号和结束的position号

mysqlbinlog  --base64-output=decode-rows -v my-bin.000002  #--->查看二进制日志的详细信息

mysqlbinlog --start-position=1170 --stop-position=1497   my-bin.000002> /tmp/binlog.sql  #---> 截取事务

source  /tmp/binlog.sql; #---> 恢复丢失的数据

4.3 二进制日志的其他操作

删除二进制：
默认情况下，不会删除旧的日志文件。
根据存在时间删除日志：

SET GLOBAL expire_logs_days = 90; --->自动删除90天以前的二进制日志
…或者…
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; --->删除现在到前三天的日志
根据文件名删除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010'; ---> 根据文件名删除

reset master; --> 清空所有二进制日志
flush logs  ---> 刷新二进制日志，生成新的日志文件

五、慢查询日志

5.1 慢日志的作用

slow-log，记录所有条件内的慢的SQL语句
优化的一种工具日志。帮我们定位问题。
是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

5.2 慢日志设置

• 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=1 #---> 开启慢日志功能
slow_query_log_file=/data/slow/slow.log  #--->慢日志存放路径
long_query_time=0.5  #--->指定慢日志的阈值
log_queries_not_using_indexes  #---> 不使用索引的被记录到慢日志

5.3 处理慢日志：

mysqldumpslow命令用于分析慢日志，帮助我们更快的找到需要优化的语句

mysqldumpslow -s c -t 10 /data/slow/slow.log ---执行次数最多的前十条语句
mysqldumpslow -s at -t 10 /data/slow/slow.log  ---查询时间最长的前十条语句

mysqldumpslow参数说明

-s
是表示按照何种方式排序
c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序。
ac、at、al、ar，表示相应的倒叙。
-t
是top n的意思，即为返回前面多少条的数据；

----------自己扩展
pt-query-diagest percona-toolkit
mysqlsla

5.4 创建模拟环境

create table city_bak select * from city;  --->创建一个和city表一样的city_bak表

insert into city_bak select * from city_bak; ---> 翻倍查询city_bak的数据

# 模拟慢日志

select sleep(5);
select * from city_bak; 
update city_bak set countrycode='chn'; --->

----------自己扩展

• pt-query-diagest percona-toolkit
• mysqlsla