Mysql日志管理

已于 2022-06-07 17:24:16 修改
阅读量283 收藏
点赞数
分类专栏： 数据库 文章标签： mysql优化 mysql
于 2021-05-10 11:44:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41320292/article/details/116591802
版权

日志管理（包括mysqlbinlog工具使用）

主从复制

​ author->xiaokai

一、日志管理

mysql日志种类(都是在my.cnf中配置)

错误日志

二进制日志（BINLOG日志）

查询日志

慢查询日志

1.错误日志
  • 记录mysqld启动、停止、故障以及任何严重错误的相关信息
  • log-error=[…] 指明错误日志打印路径和默认文件名
    • 例: log-error=/var/log/mysqld.log
2.二进制日志(BINLOG日志)
  • 以时间"事件"形式,描述数据的更改过程,包括所有的DDL和DML.不包括查询语句
  • log-bin=[…] 指定路径和文件名,如果没有指定,默认日志打印路径为datadir路径,文件名为: 主机名-bin

  1. BINLOG三种日志文件格式:

    ​ 设置时: binlog-format=“xxx”(xxx是以下任意一种)

    • STATEMENT
      • 原理: 复制每一条完整的sql语句,保留在日志中,slave主从复制时执行每一句sql
      • 优点: 日志记录清晰易读、日志量少、对I/O影响较小
      • 缺点： 在某些情况下slave的日志会出错
    • ROW
      • 原理：将数据库中所有变更表中每一行发生的变化记录下来，而不是记录sql语句，slave主从复制时执行每一行的变化语句（不是sql语句，还没弄明白怎么执行）
      • 优点：会记录每一行数据的变化细节，不会出现某些情况下无法复制的情况
      • 缺点：日志量很大，对I/O影响较大
      • 一般项目中为了保证数据的完整性使用此方式，之后再对BINLOG日志大小进行优化
    • MIXED
      • 混合了STATEMENT和ROW格式的日志打印（MySQL默认使用这种）
      • 优缺点：尽可能利用前两种格式的优点，但是这种方式也不能保证数据的完整性综上所述，statement应该基本被遗弃，ROW优先级高于MIXED

  2. 二进制日志的读取

    • myslqbinlog日志管理工具(专用于二进制文件)

      语法: mysqlbinlog [参数] logfile1 logfile2 …

      参数意义
      -d (databaose)指定数据库,只列出指定数据库相关操作
      -o (offset=#)忽略掉日志文件中的前n个语句操作
      -r (result-file)输出的文本格式日志输出到指定文件
      -s (short-form)显示简单格式,省略掉一些信息
      –set-charset输出文本格式时第一行加上set names char-name
      装载数据时是十分有效的
      –start-datetime指定日期间隔内的日志(开始时间)
      –stop-datetime指定日期间隔内的日志(结束时间)
      –start-position指定位置间隔内的所有日志(开始位置)
      –stop-position指定位置间隔内的所有日志(结束位置)
      -vv --base64-output=DECODE-ROWS有些是乱码,通过base64解码查看要清晰很多

    举例:

    ​ mysqlbinlog mysql-bin.000009 -d ccs -->只显示对数据库ccs的操作

     mysqlbinlog mysql-bin.000009 -o 3			-->忽略掉前三个语句操作

 mysqlbinlog mysql-bin.000009 -o 3 -r /home/resultfile	

 					-->忽略掉前三个语句,输出到/home/下的resultfile中

 mysqlbinlog mysql-bin.000009 -d ccs -s		-->简单显示 ,会过滤掉一些内容

 mysqlbinlog mysql-bin.000009 --start-datetime='2018/12/30 05:00:05' --			stop-datetime='2019/01/30 16:00:00'     			-->查这段时间内的日志(开始结束可以只写一个)

 mysqlbinlog mysql-bin.000009 --start-position=4 --stop-position=196

 										-->只显示第4行到第196行

  3. 二进制日志的删除

    1. 方式一: 执行"reset master"

      ​ 删除所有BINLOG日志,新日志编号从"000001"开始.

    2. 方式二: 执行"purge master logs to '文件名.000000n"

      ​ 删除00000n编号之前的所有BINLOG日志

    3. 方式三: 执行"purge master logs before 'yyyy-mm-dd hh:mm:ss"

      ​ 删除日期为yyyy-mm-dd hh:mm:ss之前的所有BINLOG日志

    4. 方式四: my.cnf中设置参数explire_log_days=#

      ​ 此参数的意义是设置日志的过期天数,过了指定的天数后日志将会被自动删除,这样有利于减少DBA管理日志的工作量.

      • 演示步骤
        • 查看删除前日志: system ls ltr localhost-bin.*
        • 在my.cnf的[mysqld]中加入"explire_logs_day=3",之后重启服务
        • 系统时间改为3天以后: date -s ‘20190127 14:00:00’
        • 触发日志文件更新: mysqladmin flush-log
        • 查看BINLOG日志符合定义的日期过期时间就都会被删除

      同时my.cnf中还可以通过设置其他选项对BINLOG日志进行更小粒度的管理:

      • binlog-do-db=db_name

        ​ 指定哪一些数据库更新需要被记录到BINLOG中,如果BINLOG需要记录多个库,则需要写多条,不能用逗号隔开,如:是binlog-do-db=db1换行binlog-do-db=db2而不是一行中binlog-do-db=db1,db2

      • binlog-ignore-db=db_name

        ​ 指定哪一些数据库更新不需要被记录到BINLOG中,如果BINLOG不需要记录多个库,则需要写多条,不能用逗号隔开

      • innodb-safe-binlog

        ​ 此选项经常和sync-binlog=N(每写N次日志同步磁盘)一起配合使用,使得事务在日志中的记录更加安全.(这个还不明白,需要看前面的sync-binlog=#是啥意思)

      • SET SQL_LOG_BIN=0

        ​ 具有SUPER权限的客户端可以通过此语句禁止将自己的语句记入二进制记录.

        ​ 这个选项在某些情况下是有用的,但是日志记录不完整可能导致主从同步数据不一致.

3.查询日志

​ 查询日志记录了客户端的所有语句,而二进制日志不包含只查询的语句(二进制里边只有修改语句)

​ 查询日志为纯文本,可以直接cat.vim.tail查看

  1. 输出形式，配置：log-output[=value…]

    • value值可以是TABLE、FILE、NONE的一个或多个组合，中间用逗号隔开，分别表示存储在表、文件、不保存在表和文件中，优先级none最高

    • 如果不设置此参数，日志默认保存在文件中

    • mysql中,查询日志对应的表是general_log,慢查询日志对应的表是slow_log

    • 但是,5.7版本之后,输出到表比输出到文件占用系统资源更多,所以建议使用文件记录

  2. 输出文件名路径，配置：general_log[={0|1}] 和 general_log_file=file_name

    • 前者控制是否启用日志,后者控制日志文件的路径
    • 前者1或者不带值表示启用查询日志;0代表不启用,这个配置要是没有,默认查询日志输出默认关闭
    • 如果没有指定general_log_file,也没有显示设置log-output,日志默认将写入datadir指定路径下,默认文件名为 主机名.log
    • 这两个参数都是global类型,可以在系统启动时或者系统运行时进行动态修改
    • 如果想在session级别控制日志是否被记录,通过在session中设置参数sql_log_off为on或者off控制
4.慢查询日志

​ 比较重要,可以 查看执行时间较长的语句,针对其语句进行优化

​ 慢查询日志记录了所有执行时间超过参数long_query_time(单位:秒,默认为10秒)设置值并且扫描记录数不小于min_examined_row_limit的所有SQL语句的日志(注意: 获得表锁时间不算执行时间),这两个参数在mysql内部执行命令设置,具体看小标题2,也可以在mysql配置文件my.cnf中直接配置

​ 默认有两种语句不会别记录: 管理语句和不适用索引的查询语句

​ 管理语句: alter、table、analyze table、check table、create index、drop index、optimize table和repair table。

​ 如果要监控这类语句，分别通过log-slow-admin-statements和log_queries_not_using_indexes进行控制

  1. 文件位置和格式

    慢查询日志默认是关闭的.

    • 文件位置

      • slow_query_log[={0|1}] ,这儿跟查询日志是一样的,不指定值或指定为1都会打开慢查询日志
      • slow_query_log_file[=file_name],指定慢查询日志路径,如果没有给定file_name的值,日志默认将写入datadir指定路径下,默认文件名为 主机名-slow.log

    • 文件格式

      • log-output[=value…],这个配置和查询日志一样,看上面

        注意: 如果输出到表,时间只能精确到秒;日志文件可以精确到毫秒

  2. 日志读取

    • 查询和修改long_query_time值

      ​ 查询: show variables like ‘long%’; 默认是10

      ​ 修改成秒级: set long_query_time=2;

      ​ 修改成微秒级: set global long_query_time=0.01;(这个命令好像不生效,得把global去掉)

    • 查看日志输出方式

      ​ 查询: show variables like ‘%output%’;

  3. mysqldumpslow日志管理工具(专用于慢查询日志)

    ​ 对于sql语句一致,只有变量不同的语句,mysqldumpslow会自动视为同一个语句进行统计,变量值用N代替

    ​ 使用方式: mysqldumpslow 文件名

5.mysqlsla简介

​ mysqlsla是分析mysql日志的工具,可以分析查询日志、慢查询日志（包括微秒日志）、二进制日志和具有固定格式的自定义日志。虽然mysql本身自带了一些日志工具，比如mysqlbinlog分析二进制日志、mysqlslow分析慢查询日志等，但是这些工具工具比较单一，所以一般选用mysqlsla工具。

  1. mysqlsla安装

    • 如果没有git，需要先安装git：yum install git

    • 安装命令：

      说明: 在opt目录下创建一个mysqlMonitor文件夹对工具统一管理;git下载一个hackmysql.com文件夹;笔记的同级目录提供一个mysqlsla压缩包,需要把它解压放到mysalMonitor文件夹下.

      cd /usr/local/src
git clone https://github.com/daniel-nichter/hackmysql.com.git
cp -Rf hackmysql.com /opt/mysqlMonitor
cd /opt/mysqlMonitor/mysqlsla
perl Makefile.PL
make && make install
#安装其他依赖包
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-DBI

  2. mysqlsla使用

    • 解析查询日志和慢查询日志:

      mysqlsla --log-type slow LOG

      mysqlsla --log-type general LOG

    • 解析二进制日志,需要先通过mysqlbinlog进行转换

      mysqlbinlog LOG | mysqlsla --log-type binary -

    • 解析微妙日志

      mysqlsla --log-type msl LOG

    • 解析用户自定义日志

      mysqlsla --log-type udl --udl-format FILE

    • 注意: --log-type可以省略,但解析二进制日志时不可以省,因为二进制日志需要mysqlbinlog先解析

  3. mysqlsla其他使用参数

    • –statement-file(-sf) CONDITIONS 过滤语句类型

      CONDITIONS 前面可以加"+“,”-",表示报表中"仅显示"和"仅去掉"后面 的语句类型

      比如:statement-filter=+UPDATE,+INSERTE 表示结果中只显示update和insert语句

      statement-filter=-UPDATE,-INSERTE 表示结果显示除了update和insert的语句

    • -ex 用来在报表中显示执行计划

    • –sort t_sum/c_sum 报表排序方式

      慢查询日志和微秒日志默认是按照总执行事假t_sum排序,其他默认按照执行顺序c_sum排序.

    • –grep PATTERN 匹配sql语句中的特定字符串,比如: --grep ‘count’,则结果只显示sql语句中包含’count’的语句相关内容

  4. mysqlsla输出格式说明

    #mysqlsla输出格式说明:
queries total: 总查询次数,unique: 去重后的sql数量
Sorted by: 输出报表的内容排序
Count : sql的执行次数及占总的slow log数量的百分比
Time : 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比
95% of Time : 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间
Lock Time : 等待锁的时间
95% of Lock : 95%的慢sql等待锁时间
Rows sent : 结果行统计数量, 包括平均, 最小, 最大数量
Rows examined : 扫描的行数量
Database : 属于哪个数据库
Users : 哪个用户,IP,占到所有用户执行的sql百分比
Query abstract: 抽象后的sql语句
Query sample : sql样例语句

二、主从复制

slave会从master读取binlog来进行数据同步

1. mysql主从复制原理(过程)
  1. master将改变记录到二进制日志(binlog),这些记录过程叫做二进制日志事件,binary log events;
  2. slave将master的binary log events拷贝到他的中继日志(relay log);
  3. slave重做中继日志中的事件,将改变应用到自己的数据库中.mysql主从复制时异步且串行化的.
2.主从复制规则
  1. 每个slave只能有一个master;
  2. 每个slave只能有一个唯一的服务器ID;
  3. 每个master可以有多个slave.
3.主从复制的三种方式
  1. binlog_format=Statement 基于SQL语句的复制
  2. binlog_format=Row 基于行的复制
  3. binlog_format=Mixed 混合复制模式
3.复制的最大问题

​ 延时,特别是多级复制时延迟更大

4.一主一从常见配置

  1. mysql版本一致且以后台服务运行,两边ping得通

  2. 主从都配置在[mysqld]节点下,都是小写

  3. 主机的my.ini/my.cnf配置文件

    1. 主服务器唯一ID(必须)

      ​ server-id=1

    2. 启用二进制日志(必须)

      ​ log-bin=自己本地路径/mysqlbin

    3. 启用错误日志(可选)

      ​ log-err=自己本地路径/mysqlerr

    4. 根目录,道理相当于java的JAVA_HOME(可选)

      ​ basedir=“自己的本地路径”

    5. 临时目录(可选)

      ​ tmpdir=“自己的本地路径”

    6. 数据目录(可选)

      ​ datadir=“自己的本地路径/Data/”

    7. 主机读写都可以(可选)

      ​ read-only=0

    8. 设置不要复制的数据库(可选)

      ​ binlog-ignore-db=…,…,…

    9. 设置需要复制的数据库(可选)

      ​ binlog-do-db=…,…,…

  4. 从机的my.ini/my.cnf配置文件

    1. 从服务器唯一ID(必须)

      ​ server-id=2

    2. 启用二进制日志(可选)

      ​ log-bin=mysql-bin

  5. 因为修改过配置文件,需要主机和重启重启mysql服务

    ​ service mysqld restart

  6. 主机从机都关闭防火墙

  7. 在主机上建立账户并授权slave

    1. GRANT REPLICATION SLAVE ON *.* TO ‘用户名’@‘从机ip地址’ IDENTIFIED BY ‘用户名对应的密码’;

    2. flush privileges;

    3. 查询master状态

      ​ show master status;

      ​ 记录下File和Position的值

    4. 执行完此步骤后不要再操作主服务器mysql,防止主服务器状态值变化

  8. 在从机上配置需要复制的主机

    1. CHANGE MASTER TO MASTER_HOST=‘主机的IP地址’,

      MASTER_USER=‘用户名’,

      MASTER_PASSWORD=‘用户名的密码’,

      MASTER_LOG_FILE=‘mysqlbin.具体数字’,MASTER_LOG_POS=主机上面保存的节点位置;

    2. 启动从服务器复制功能

      start slave;

    3. 查看是否 配置成功

      命令: show slave status\G

      ​ Slave_IO_Running:Yes

      ​ Slave_SQL_Running:Yes

      ​ 这两条要是都是yes就说明配置成功!

  9. 如何停止从服务复制功能

    ​ stop slave;

5.主从复制的恢复步骤
#案例-恢复的思想-以双主复制为例
1、主stop slave;>flush tables with read lock;
2、主dump数据库，清从的数据库,主数据库导入（保证主从数据一致)
3、主 reset slave;reset mstaer;show master status\G;
4、从 stop slave;reset slave;reset mstaer;show master status\G;
5、主：mysql>change master to
   master_host='192.168.90.17',master_user='replicate',master_password='password',
   master_log_file=' mysql-bin.000001 ',master_log_pos=154;
   从：mysql>change master to
   master_host='192.168.90.16',master_user='replicate',master_password='password',
   master_log_file=' mysql-bin.000001 ',master_log_pos=154;
6、主 start slave;show slave status\G;
   从 start slave;show slave status\G;
7、主>unlock tables;
6.主从复制的三种架构
  • 一主多从复制架构
  • 多级复制架构
  • 双主复制架构
7.异步复制和半同步复制
瓜娃子希望世界和平
关注
专栏目录
MySQL 日志管理、备份与恢复（图文详解）
愿你成为自己喜欢的模样，不抱怨，不将就，有自由，有光芒！
01-29 2370
文章目录MySQL 日志管理、备份与恢复准备工作MySQL日志管理一、MySQL常用日志类型及开启二、查看日志状态MySQL备份与恢复一、数据备份的重要性二、数据库备份的分类1、从物理与逻辑的角度，备份可分为2、从数据库的备份策略角度，备份可分为三、常见的备份方法四、MySQL完全备份与恢复MySQL 完全备份完全备份恢复MySQL 增量备份与恢复MySQL 增量备份at 302at 430at 461at 526at 609at 742MySQL 增量备份恢复at 302at 430at 461at 52
MYSQL日志管理
m0_53122419的博客
05-22 89
MySQL日志管理 在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据； MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情： 错误日志：记录启动、运行或停止时出现的问题 查询日志：记录建立的客户端连接和执行的语句 二进制日志：记录所有更改数据的语句 主要用于复制和即时点恢复 慢日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询 事务日志：记录InnoDB等支持事
MySQL 日志管理
m0_60379130的博客
06-12 388
默认情况下，所有日志创建于mysqld数据目录中。rpm安装的日志是在 /var/lib/mysql其他方式安装的为自定义路径。备份之前需要打开慢查询日志，指定慢查询路径，指定慢查询时间。打开二进制日志，同时指定id.打开之后会有记录数据 （每一种安装方式下的安装路径不一致，具体以查询为准。） 永久更改，须在配置中更改，如下：在配置文件中增加以下三行信息： 再重启服务：没有开时再配置文件中开启： 重启之后再次连接,显示已开启。多久之后慢查询呢？ 更改慢查询时间： myusql5.6版本之上的版本一定要先配
MySQL学习[5] ——MySQL日志
最新发布
shenfenxihuan的博客
08-14 4896
除了InnoDB存储引擎层生成的undo log和redo log，MySQL在完成一条更新操作后，Server层还会生成一条binlog，事务提交时会将该事务执行过程中产生的binlog统一写入binlog 文件。最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。
MySQL 基础---日志管理
mazaiting的博客
10-12 107
MySQL软件中的日志操作: 日志的含义 启动日志文件 查看日志文件 停止日志文件 删除日志文件 支持的日志文件 二进制日志: 该日志文件会以二进制形式记录数据库的各种操作，但是却不记录查询语句。 错误日志: 该日志文件会记录MySQL服务器启动、关闭和运行时出错等信息。 通用查询日志: 该日志记录MySQL服务器的启动和关闭信息、客户端的连接信...
MySQL日志管理
十五十六
02-13 552
错误日志 记录MySQL数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志 配置方式 [mysqld] log_error=err.log 然后重启会报错 [root@localhost data]# mysqld 2019-01-18T22:18:00.613170Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is ...
MySQL日志管理详解
09-10
MySQL日志管理是数据库系统维护和优化的重要环节，它提供了对服务器运行状态的详细记录，帮助管理员监控数据库健康状况、追踪错误、优化性能以及实现数据恢复。MySQL中有六种主要的日志类型，每种都有其特定的功能和...
MySQL日志管理详细讲解
04-04
MySQL日志管理详细讲解 MySQL日志管理是数据库管理员的必备技能之一。日志文件记录了数据库的各种操作和错误信息，对于数据库的维护和错误排查起着至关重要的作用。本文将详细讲解 MySQL 日志管理的概念、种类、...
mysql日志管理_MySQL日志管理
weixin_39632467的博客
02-28 97
MySQL日志管理错误日志log_error#作用记录错误,警告,状态#查看方式默认: DATADIR/hostname.err找日志[ERROR]的上下文#自定义配置[root@mysql data]# vim /etc/my.cnflog_error=/tmp/mysql.log重启生效#查看数据库日志路径mysql> select @@log_error;二进制日志作用:SQL层日志,...
mysql日志管理_Mysql的日志管理
weixin_32742673的博客
01-28 215
1.简介日志文件记录着mysql数据库运行期间发生的变化，如：mysql数据库的客户端连接状况、sql语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志进行数据恢复；也可以通过日志文件分析数据、优化查询等。Mysql日志管理机制比较完善，它包含了以下几种常见的日志文件、分别为：错误日志(-log-err)、查询日志(-log)、二进制日志(-lo...
mysql日志管理
huangbaokang的博客
07-29 438
简介 mysql支持4种类型的日志，分别为二进制日志、错误日志、通用查询日志和慢查询日志。 1、二进制日志 该日志的内容为二进制，使用普通的文本编辑器打开文件，将会是乱码，需要使用mysql对应的命令mysqlbinlog进行查看。 查看二进制日志文件 mysqlbinlog hbk-bin.000002 设置暂时停止或开启binlog set sql_log_bin=0 #暂停 set s...
Mysql 日志管理
有生之年
08-15 360
Mysql 日志管理 日志分类 错误日志 ：启动，停止，关闭失败报错，rpm安装日志位置 /var/log/mysqld.log 通用查询日志：所有的查询都加下 二进制日志：实现备份，增量备份。只记录改变数据，除了select。 中继日志：读取服务器binlog,在本地回放。保持一致。 slow log：慢查询日志，指导调优，定义某一个查询语句，定义超时时间，通过日志提供调优建议给开发...
MySQL日志管理：错误日志、查询日志与慢查询日志
"本文主要介绍了MySQL日志管理的各个方面，包括错误日志、查询日志、慢查询日志、二进制日志、中继日志、事务日志和滚动日志。错误日志用于记录服务器运行错误、启动/停止信息以及复制进程信息。查询日志记录所有非...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

瓜娃子希望世界和平

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值