1.错误日志

1.1 作用

首先需要明确的是, 错误日志中记录的未必都是错误日志,在Mysql的错误日志中,存储以下日志信息

  1. 服务器启动和关闭过程中的信息
  2. 服务器运行过程中的错误信息
  3. 事件调度器运行一个事件时产生的信息
  4. 在从服务器上启动从服务器进程时产生的信息

1.2 默认位置

  1. 错误日志默认位置：/mysql/data/$hostname.err
  2. 错误日志默认文件名称：$hostname.err

1.3 错误日志配置：

  1. #开启方式：
  2. 默认就是开启的:  /mysql/data/$hostname.err
  3. #编辑配置文件
  4. [root@db01 ~]# vim /etc/my.cnf
  5. [mysqld]
  6. log_error=/usr/local/mysql/data/$hostname.err
  7. log_warnings = 1|0 #是否记录warnings信息到错误日志中
  8. #查看方式：
  9. mysql> show variables like 'log_error';

2.一般查询日志

2.1 作用

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

2.2 默认位置

/usr/local/mysql/data/$hostnamel.log

2.3 一般查询日志配置

  1. #MySQL安装完之后默认不开启一般查询日志配置
  2. #编辑配置文件
  3. [root@db01 ~]# vim /etc/my.cnf
  4. [mysqld]
  5. general_log=on
  6. general_log_file=/application/mysql/data/$hostnamel.log
  7. #查看方式
  8. mysql> show variables like '%gen%';

3.二进制日志

3.1 作用

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。

binlog作用主要有：

数据恢复：如果我拥有数据库搭建开始所有的二进制日志，那么我可以通过mysqlbinlog工具把数据恢复到任意时刻
数据的备份恢复
数据的复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的

3.2 二进制日志的模式

row：行模式，日志中会记录每一行数据被修改的过程
statement：语句模式，每一条会修改数据的sql都会记录到日志中（默认模式）。
mixed：以上两者的混合模式。
企业推荐使用row模式

row行模式：

日志中会记录每一行数据被修改的形式，然后在slave端再对相同的数据进行修改

优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改所以row level的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题

缺点：row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，不太以被读懂，会产生大量的日志内容。

statement语句模式：

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行

优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要在Master上锁执行的语句的细节，以及执行语句的上下文的信息。

缺点：由于只记录语句，所以，在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。

mixed自动模式：

在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

3.3 二进制日志开启方式

  1. [root@db01 ~]# vim /etc/my.cnf
  2. [mysqld]
  3. log_bin=ON
  4. log_bin_basename=/var/local/mysql/data/mysql-bin
  5. log_bin_index=/var/local/mysql/data/mysql-bin.index
  6. #binlog_format=row    #指定二进制日志模式
  7. #server_id=1    #在mysql5.7中开启binlog必须要加上server-id。
  8. 第一个参数是打开binlog日志
  9. 第二个参数是binlog日志的基本文件名，后面会追加标识来表示每一个文件
  10. 第三个参数指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录

当然也有一种简单的配置，一个参数就可以搞定
log-bin=/var/local/mysql/data/mysql-bin
这一个参数的作用和上面三个的作用是相同的，mysql会根据这个配置自动设置log_bin为on状态，自动设置log_bin_index文件为你指定的文件名后跟.index

3.4 二进制日志查看

  1. #物理查看
  2. [root@db01 data]# ll /app/mysql/data/
  3. -rw-rw---- 1 mysql mysql      285 Mar  6  2017 mysql-bin.000001
  4. mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001
  5. #命令行查看
  6. 查看所有的二进制文件
  7. mysql> show binary logs;
  8. 查看当前正在使用的二进制文件
  9. mysql> show master status;
  10. #查看binlog事件
  11. mysql> show binlog events in 'mysql-bin.000001';

事件介绍

1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）

事件（event）特性

1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。

3.5 二进制日志的刷新和删除

刷新binlog日志
1）flush logs;
2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）1G

删除二进制日志
1）原则
在存储能力范围内，能多保留则多保留
基于上一次全备前的可以选择删除

删除方式

1.根据存在时间删除日志

  1. 自动清理
  2. #临时生效
  3. set global expire_logs_days=7;
  4. #永久生效
  5. [root@db01 data]# vim /etc/my.cnf
  6. [mysqld]
  7. expire_logs_days = 7

手动清理：

2.使用purge命令删除

  1. PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3.根据文件名删除

  1. PURGE BINARY LOGS TO 'mysql-bin.000001';
  1. 注意:不要手工 rm binlog文件
  2. 1. my.cnf binlog关闭掉,启动数据库
  3. 2.把数据库关闭,开启binlog,启动数据库
  4. 删除所有binlog,并从000001开始重新记录日志

4.全部删除

  1. mysql> reset master; #主从关系中，主库执行此操作，主从环境必崩

3.6 row模式下二进制日志分析及数据恢复

  1. #查看binlog信息
  2. mysql> show master status;
  3. #创建一个binlog库
  4. mysql> create database binlog;
  5. #使用binlog库
  6. mysql> use binlog
  7. #创建binglog_table表
  8. mysql> create table binlog_table(id int);
  9. #查看binlog信息
  10. mysql> show master status;
  11. #插入数据1
  12. mysql> insert into binlog_table values(1);
  13. #查看binlog信息
  14. mysql> show master status;
  15. #提交
  16. mysql> commit;
  17. #查看binlog信息
  18. mysql> show master status;
  19. #插入数据2
  20. mysql> insert into binlog_table values(2);
  21. #插入数据3
  22. mysql> insert into binlog_table values(3);
  23. #查看binlog信息
  24. mysql> show master status;
  25. #提交
  26. mysql> commit;
  27. #删除数据1
  28. mysql> delete from binlog_table where id=1;
  29. #查看binlog信息
  30. mysql> show master status;
  31. #提交
  32. mysql> commit;
  33. #更改数据2为22
  34. mysql> update binlog_table set id=22 where id=2;
  35. #查看binlog
  36. mysql> show master status;
  37. #提交
  38. mysql> commit;
  39. #查看binlog信息
  40. mysql> show master status;
  41. #查看数据
  42. mysql> select * from binlog_table;
  43. #删表
  44. mysql> drop table binlog_table;
  45. #删库
  46. mysql> drop database binlog;

恢复数据到delect之前

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000013';
#使用mysqlbinlog来查看
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013|grep -v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013
### UPDATE `binlog`.`binlog_table`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
#分析
update binlog.binlog_table
set
@1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22
where
@1=2  --------->@1表示binlog_table中的第一列,集合表结构就是id=2
#结果
update binlog.binlog_table set id=22 where id=2;
#截取二进制日志
查看二进制日志后，发现删除语句开始位置是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013 > binlog.sql
#临时关闭binlog
mysql> set sql_log_bin=0;
#执行sql文件
mysql> source /tmp/binlog.sql
#查看删除的库
mysql> show databases;
#进binlog库
mysql> use binlog
#查看删除的表
mysql> show tables;
#查看表中内容
mysql> select * from binlog_table;

思考，存在问题：

数据库或表被误删除的是很久之前创建的（一年前） 如果基于binlog全量恢复，成本很高 1）可以用备份恢复+短时间内二进制日志，恢复到故障之前 2）非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback 3）延时从库 如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到 想一个办法过滤出来？ 1）grep？ 其他过滤方案？ 1）-d 参数接库名

模拟数据

#为了让大家更清晰看到新的操作
#刷新一个新的binlog
mysql> flush logs;
#创建db1、db2两个库
mysql> create database db1;
mysql> create database db2;

#库db1操作
mysql> use db1
#创建t1表
mysql> create table t1(id int);
#插入5条数据
mysql> insert into t1 values(1),(2),(3),(4),(5);
#提交
mysql> commit;

#库db2操作
mysql> use db2
#创建t2表
mysql> create table t2(id int);
#插入3条数据
mysql> insert into t2 values(1),(2),(3);
#提交
mysql> commit;

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000014';
#查看db1的操作
[root@db01 data]# mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000014

4.慢查询日志

慢查询日志在调优过程中使用的比较多，其中主要记录的是执行较慢的操作，具体时间可通过参数进行设置。

4.1 作用

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

4.2 默认位置

mysql/data/$hostname-slow.log

4.3 慢查询日志开启方式

（默认没有开启）

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志，可选值为0和1，默认为0。
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/app/mysql/data/slow.log
#设定慢查询的阀值(默认10s)，时间长度，单位毫秒，超过此时间的操作将会被记录至日志。
long_query_time=0.5
#不使用索引的慢查询日志是否记录到索引
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志（鸡肋）
min_examined_row_limit=100

4.4 分析慢查询日志

使用mysqldumpslow命令来分析慢查询日志

输出记录次数最多的10条SQL语句
$PATH/mysqldumpslow -s c -t 10 /database/mysql/slow-log

参数说明:
 -s:
 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
 -t:
 是top n的意思，即为返回前面多少条的数据；
 -g:
 后边可以写一个正则匹配模式，大小写不敏感的；

例：

#得到返回记录集最多的10个查询
$PATH/mysqldumpslow -s r -t 10 /database/mysql/slow-log#得到按照时间排序的前10条里面含有左连接的查询语句$PATH/mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log

4.5 模拟慢查询日志语句

#进入world库
mysql> use world
#查看表
mysql> show tables
#将city表中所有内容加到t1表中
mysql> create table t1 select * from city;
#查看t1的表结构
mysql> desc t1;
#将t1表所有内容插入到t1表中（多插入几次）
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
#提交
mysql> commit;
#删除t1表中id>2000的数据
mysql> delete from t1 where id>2000;
#查看慢日志
[root@db01 ~]# cat /application/mysql/data/mysql-db01