• mysq日志管理


    1.错误日志

      记录mysql数据库的一般状态信息,是我们对数据库常规报错处理的常用日志

    2.默认位置

      $mysql_home/data/

    3.开启方式:(mysql安装后默认开启)

    ##编辑配置文件

    [root@db01 ~]# vim /etc/my.cnf

    [mysqld]

    log_error=/application/mysql/data/$hostname.err

    ##查看方式

    mysql> show variables like 'log_error';

    4.一般查询日志

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

    默认位置:

    $msyql_home/data/

    开启方式:(mysql安装之后默认不开启)

    ##编辑配置文件

    [root@db01 ~]# vim /etc/my.cnf

    [mysqld]

    general_log=on

    general_log_file=/application/mysql/data/$hostnamel.log

    #查看方式

    mysql> show variables like '%gen%';

    5.二进制日志

    作用:记录一提交的DML事物语句,并拆分为多个事件(event)来进行记录

    记录所有的DDL 、DCL等语句

    总之,二进制日志会记录所有对数据库发生修改的操作

    ##二进制日志模式

      statement:语句模式,上图中将update语句进行记录(默认模式)

      row:行模式,即数据行的变化过程

      mixed:以上两者的混合模式

      企业推荐使用row模式

    statement模式:

      优点:简单明了,容易看懂,就是sql语句,记录是不需要太多的磁盘空间

      缺点:记录不够严谨

    row模式

      优点:记录更加严谨

      缺点:有可能会需要更多地磁盘空间,不太容易被读懂

    binlog的作用

      1)如果我们拥有数据库搭建开始所有的二进制日志,那么我们就可以把数据恢复到任意时刻

      2)数据的备份恢复

      3)数据的复制

    ******二进制日志管理操作实践

    开启方式

    [root@db01 data]# vim /etc/my.cnf

    [mysqld]

    log-bin=mysql-bin

    binlog_format=row

    注意在mysql5.7开启binlog必须加上server-id

    [root@db01 data]# vim /etc/my.cnf

    [mysqld]

    log-bin=mysql-bin binlog_format=row

    server_id=1

    二进制日志的操作

    #物理查看

    [root@db01 data]# ll /application/mysql/data/

    -rw-rw---- 1 mysql mysql 285 Mar 6 2017 mysql-bin.000001

    #命令行查看

    mysql> show binary logs;

    mysql> show master status;

    #查看binlog事件

    mysql> show binlog events in 'mysql-bin.000006';

    事件介绍:

      1)在binlog中最小的记录单元为event

      2)一个事物会被拆分成多个事件(event)

    事件(event)的特性

      1)每个event都有一个开始位置(start  position)和结束位置(stop  position)

      2)所谓的位置就是event对整个二进制的文件的相对位置

      3)对于一个二进制日志中,前120个position是文件格式信息预留空间

      4)mysql第一个记录的事件,都是从120开始的

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

    #查看binlog信息

    mysql> show master status;

    #创建一个binlog库

    mysql>create  database binlog;

    #使用binlog库

    mysql> use binlog

    #创建binglog_table表

    mysql> create table binlog_table(id int);

    #查看binlog信息

    mysql> show master status;

    #插入数据1

    mysql> insert into binlog_table values(1);

    #查看binlog信息

    mysql> show master status;

    #提交 mysql> commit;

    #查看binlog信息

    mysql> show master status;

    #插入数据2

    mysql> insert into binlog_table values(2);

    #插入数据3

    mysql> insert into binlog_table values(3);

    #查看binlog信息 mysql> show master status;

    #提交

    mysql> commit;

    #删除数据1

    mysql> delete from binlog_table where id=1;

    #查看binlog信息

    mysql> show master status;

    #提交

    mysql> commit;

    #更改数据2为22

    mysql> update binlog_table set id=22 where id=2;

    #查看binlog

    mysql> show master status;

    #提交 mysql> commit;

    #查看binlog信息

    mysql> show master status;

    #查看数据

    mysql> select * from binlog_table;

    #删表

    mysql> drop table binlog_table;

    #删库

    mysql> drop database binlog;

    *****恢复数据到delete之前

    #查看binlog事件

    mysql> show   binlog event  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

    查看二进制日志后,发现delete语句开始位置是858

    [root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858    

    /application/mysql/data/mysql-bin.000013            

     #临时关闭binlog

    mysql >  set sql_log_bin=0;

    #执行sql文件 my

    sql> 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是都会被截取到

    模拟数据

    #刷新一个新的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

    ###删除、刷新binlog

    ****刷新binlog

      1)flush logs

      2)重启数据库是会被刷新

      3)二进制日志上线(max_binlog_size)

    ****删除二进制日志

      原则:(1)在储存能力范围内,能保留就多保留      (2)基于上一次全备份前的可以选择删除

    删除方式  

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

    #临时生效     SET GLOBAL expire_logs_days = 7;

    #永久生效      

    [root@db01 data]# vim /etc/my.cnf

    [mysqld]

    expire_logs_days = 7

    2.使用purge命令删除

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

    3.根据文件名删除

    PURGE BINARY LOGS TO 'mysql-bin.000010';

    4.使用reset  master

    mysql> reset master;

    *******慢查询日志

    作用:

      1)是将mysql服务器中影响数据库性能的相关SQL 语句记录到日志文件中

      2)通过对这些特殊的SQL语句分析,改进以达到提高数据库性能的目的

    默认位置:

    $mysql_host/data/$hostname-slow.log

    开启方式(默认没有开启):

    [root@db01 ~]# vim /etc/my.cnf

    [mysqld]

    #指定是否开启慢查询日志

    slow_query_log = 1

    #指定慢日志文件存放位置(默认在data)

    slow_query_log_file=/application/mysql/data/slow.log

    #设定慢查询的阀值(默认10s)

    long_query_time=0.05

    #不使用索引的慢查询日志是否记录到日志

    log_queries_not_using_indexes

    #查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

    min_examined_row_limit=100(鸡肋)

    模拟慢查询语句

    #进入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

    ************使用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

    第三方推荐:

    yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm

    使用percona公司提供的pt-query-digest工具分析慢查询日志

    [root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log

    Anemometer基于pt-query-digest将MySQL慢查询可视化
    httpss://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载
    httpss://github.com/box/Anemometer 可视化代码下载

  • 相关阅读:
    Linux中$含义
    Linux文本处理之grep
    MySQL8.0.15的安装与配置---win10
    Jenkins实现自动运行jmeter脚本
    Hystrix初识
    Feign初始
    AS的Gradle下载不成功
    Linux安装一些软件
    OAuth2初识
    IDEA无法打开等奇异问题终极解决方法
  • 原文地址:https://www.cnblogs.com/fangdecheng/p/10060660.html
Copyright © 2020-2023  润新知