08-日志

1. 错误日志

1.作用

排错.MySQL 启动和工作过程状态,警告,错误.

2.配置

默认开启.存储位置, datadir/hostname.err
log_error=/data/mysql/data_3306/mysql.log

3.查看

tail -f /data/mysql/data_3306/mysql.log

2. 二进制日志

1.作用

数据恢复,主从复制.记录的是所有变化类的操作(DDL,DML,DCL).

2.配置

[root@db01 backup]# cat /etc/my.cnf
[mysqld]
server_id=6 
log_bin=/data/mysql/binlog_3306/mysql-bin

3.binlog 对于不同语句的记录格式

• 非事务型语句: DDL DCL
  以statement语句模式记录
• 事务型语句(insert ,update ,delete )
  begin;
  A ;
  commit;
  只记录提交的事务.
  记录格式,三种:
  产看方式：mysql> select @@binlog_format;
  RBR : ROW模式, 记录数据行的变化. 记录够准确.
  SBR : statement模式 ,以语句方式记录DML语句.会出现记录偏差
  MBR : Mixed 模式,混合模式.
• 内容格式
  event:
  以event事件的方式记录所有的变化.binlog的最小记录单元为事件.
  DDL DCL ,每条语句就是一个事件.
  DML ====> begin; A; ...... commit;一个事务由多个事件构成.
  event的结构:
  起始点:position
  事件内容.
  结束点: end_position
  上一个event的结束点就是下个event的起始点.是连续记录,不能出现断点.

4.查看二进制日志

• 简单查看:

查看所有binlog日志
    mysql> show binary logs;
创建一个新binlog日志
    mysql> flush logs;
查看当前binlog日志
    mysql> show master status ;

• 事件查看:

查看当前binlog日志
show master status ;
查看此日志记录的内容
show binlog events in 'mysql-bin.000004';
mysql -e "show binlog events in 'mysql-bin.000004'" |grep -C 5 "create"

• 内容查看:

mysqlbinlog  /data/mysql/binlog_3306/mysql-bin.000004 |grep -v "SET"
mysqlbinlog  --base64-output=decode-rows -vvv /data/mysql/binlog_3306/mysql-bin.000004 |grep -v "SET"

5.使用二进制日志进行数据恢复

步骤:
1. 截取日志
起点: --start-position=980
终点: --stop-position=2083

2. 恢复日志
   source /tmp/bin.sql

6.模拟故障并binlog恢复

• 模拟数据

mysql> create database oldboy;
mysql> use oldboy
Database changed
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t1 values(2);
mysql> commit;
mysql> insert into t1 values(3);
mysql> commit;
mysql> drop database oldboy;

• 截取日志

起点:

| mysql-bin.000004 |  980 | Query          |         6 |        1080 | create database oldboy       

终点:

| mysql-bin.000004 | 2083 | Query          |         6 |        2181 | drop database oldboy         

使用mysqlbinlog截取
mysqlbinlog --start-position=980 --stop-position=2083 /data/mysql/binlog_3306/mysql-bin.000004>/tmp/bin.sql

• 恢复日志

mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;

7.日志滚动

mysql> flush logs;

重启数据库

select @@max_binlog_size;

8.binlog的删除

• 自动删除

mysql> select @@expire_logs_days;

根据全备周期,至少全备周期+1,企业要求至少2*全备周期+1

• 手工删除
  PURGE BINARY LOGS TO 'mysql-bin.000004';
  PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
  (3) 全部清空
  reset master;

扩展-基于GTID的日志记录

• GTID 介绍
  5.6 版本的新特性,5.7进行了增强,建议开启.
  全局事务ID号.
  特点: 对于每一个事务,都会有一个唯一性的编号.
  GTID具备幂等性,执行过的GTID的事务会自动跳过.
• 格式
  server_uuid:TID

mysql> create database xtz;
Query OK, 1 row affected (0.01 sec)

mysql> show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 |      310 |              |                  | 411148c1-26bf-11ea-a420-000c298780da:1 |
+------------------+----------+--------------+------------------+----------------------------------------+

• 开启GTID功能

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
gtid-mode=on
enforce-gtid-consistency=true

• 基于GTID的数据恢复

1. 模拟数据和故障:

create database bgx;
use bgx
create table t1 (id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
drop database bgx;

2. 恢复步骤:

(1)截取日志
起点:

| mysql-bin.000002 |  310 | Gtid           |         6 |         375 | SET @@SESSION.GTID_NEXT= '411148c1-26bf-11ea-a420-000c298780da:2' |
| mysql-bin.000002 |  375 | Query          |         6 |         466 | create database bgx   

终点:

| mysql-bin.000002 | 1380 | Gtid           |         6 |        1445 | SET @@SESSION.GTID_NEXT= '411148c1-26bf-11ea-a420-000c298780da:7' |
| mysql-bin.000002 | 1445 | Query          |         6 |        1534 | drop database bgx   

执行命令截取日志：
mysqlbinlog --skip-gtids --include-gtids='411148c1-26bf-11ea-a420-000c298780da:2-6' /data/mysql/binlog_3306/mysql-bin.000002 >/tmp/gtid.sql

2. 恢复日志

set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;

注意: 开启GTID,可以有两种方式截取日志.但是都要在截取是加入--skip-gtids.
面试可以用的故障案例一则: gtid模式下的binlog截取及恢复.

3. 慢日志

• 记录MySQL中较慢语句的日志.排查数据库语句问题的日志.
• 配置

    mysql> show variables like '%slow%';
    mysql> select @@long_query_time;
    mysql> select @@log_queries_not_using_indexes;

    slow_query_log=ON 
    slow_query_log_file=/data/mysql/data_3306/db01-slow.log
    long_query_time=0.1
    log_queries_not_using_indexes=1

• 模拟慢语句

mysql> select k1 ,count(k2) from t100w  group by k1 order by count(k2) desc  limit 10;
mysql> select a.k1 ,b.k2 ,b.dt from (select k1,k2 from t100w) a join (select k2,dt from t100w) b on a.k2=b.k2 order by a.k1 desc limit 10;

• 分析慢日志

抓取慢日志
mysqldumpslow -s c -t 3 db01-slow.log
--select--> 看执行计划 ---> 索引 ----> 语句

数据库慢:
1. 应急性的 ------> show full processlist;
2. 间歇性 ------> slow_log