• 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 可视化代码下载

  • 相关阅读:
    Jessica's Reading Problem POJ
    FatMouse and Cheese HDU
    How many ways HDU
    Humble Numbers HDU
    Doing Homework again
    Stacks of Flapjacks UVA
    Party Games UVA
    24. 两两交换链表中的节点
    面试题 03.04. 化栈为队
    999. 可以被一步捕获的棋子数
  • 原文地址:https://www.cnblogs.com/fangdecheng/p/10060660.html
Copyright © 2020-2023  润新知