登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›MySQL 数据库日志总结(一)

MySQL 数据库日志总结(一)

决台 8 小时前
猛犸象科技工作室:网站开发，备案域名，渗透，服务器出租，DDOS/CC攻击，TG加粉引流
数据库服务日志概述介绍

任何一种数据库中，都会有各种各样的日志，记录这数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件；
主要是针对数据库server层产生的数据信息，主要用于记录和数据库服务运行本身有关的日志、以及SQL语句操作执行相关的日志；
据库服务日志常用分类

在MySQL数据库服务中，有4种不同的日志是最常用的日志类型，这些日志记录这数据库在不同方面的踪迹；
日志信息查看方法：
  1. mysql> show variables like '%log%';
  2. +------------------------------------------------+---------------------------------------------+
  3. | Variable_name                                  | Value                                       |
  4. +------------------------------------------------+---------------------------------------------+
  5. | general_log                                    | OFF                                         |
  6. | general_log_file                               | /data/3306/data/wenC-01.log                 |
  7. | log_error                                      | ./wenC-01.err                               |
  8. | log_bin                                        | ON                                          |
  9. | log_bin_basename                               | /data/3306/data/binlog                      |
  10. | log_bin_index                                  | /data/3306/data/binlog.index                |
  11. | slow_query_log                                 | OFF                                         |
  12. | slow_query_log_file                            | /data/3306/data/wenC-01-slow.log            |
  13. +------------------------------------------------+---------------------------------------------+
复制代码
常用日志信息介绍：
序号日志名称解释说明01general_log表示查询日志（通用日志），默认日志状态处于关闭，可以进行在线调整配置
作用：记录了客户端从会话连接开始，执行过的所有SQL语句信息；02log_error表示错误日志（运行日志），默认日志状态处于激活
作用：记录了数据库服务启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息；03log_bin表示二进制日志（binlog日志），默认日志状态处于激活（8.0之后）
作用：记录了所有的DDL语句和DML语句，但是不包括数据库查询语句；语句以事件的形式保存，描述了数据的更改过程，此日志对于灾难时的数据恢复起着极其重要的作用。04slow_query_log表示慢查询日志，记录了所有执行时间超过参数long_query_time设置值并且扫描记录数小于min_examined_row_limit的所有SQL语句的日志。数据库服务日志信息配置

1.分类日志信息配置：通用日志（general_log）


  • 1_1 日志信息基本配置：
  1. general_log=OFF         
  2. -- 默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开；
  3. general_log_file=/data/3306/data/wenC-01.log  
  4. -- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
  6. # 修改日志默认状态（激活日志）：
  7. mysql > set global general_log=1;
复制代码
说明：企业真实环境，由于日志记录量比较大，所以不建议打开此日志记录功能，可以在有需要时打开，支持在线配置调整；
2.分类日志信息配置：错误日志（log_error）


  • 2_1 日志信息基本配置
  1. log_error=./wenC-01.err      
  2. -- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
  4. # 修改日志存储路径（永久配置）：
  5. [root@cheng ~]# vim /etc/my.cnf
  6. log_error=/data/3306/log/wenC-01.err
  7. -- 配置文件编写完毕后，需要重启数据库服务生效
  9. # 模拟故障日志应用
  10. [root@cheng ~]# ll /data/3306/data/ibdata1
  11. -rw-r----- 1 mysql mysql 12582912 Nov 16 17:46 /data/3306/data/ibdata1
  12. [root@cheng ~]# chmod 000 /data/3306/data/ibdata1
  13. [root@cheng ~]# /etc/init.d/mysqld restart
  14. Shutting down MySQL............................... SUCCESS!
  15. Starting MySQL......................................... ERROR! The server quit without updating PID file (/data/3306/data/wenC-01.pid).
  16. [root@cheng ~]# tail -20 /data/3306/log/wenC-01.err
  17. 2022-11-21T01:20:47.735040Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
  18. 2022-11-21T01:20:47.744091Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
  19. 2022-11-21T01:20:47.744808Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
  20. 2022-11-21T01:20:47.745739Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
  21. 2022-11-21T01:20:47.746526Z 0 [ERROR] [MY-010119] [Server] Aborting
  22. -- 根据错误日志的错误提示信息，进行错误信息进行分析，从而排查故障可能出现的原因；
复制代码
说明：企业真实环境，日志处于默认激活记录状态，可以使用错误日志信息做故障诊断，记录错误信息级别为note warning error；
3.分类日志信息配置：二进制日志（log_bin）

在进行增量恢复数据时，需要先了解什么是binlog日志，此日志文件其实就是用于记录对数据库进行操作更改的语句信息的；
并且记录更改的语句信息以事件形式进行记录，但是需要注意的是查询相关的语句是不会被记录的，比如：select、show；
然而作为所有对数据库的改操作事件信息都会被记录，比如：insert、update、create、drop。。。
查看数据库binlog日志配置参数：
进入到数据库服务系统环境中，可以使用命令进行查看binlog日志功能是否开启；
  1. # 未开启binlog日志功能时，查看系统binlog功能配置参数状态
  2. mysql> show variables like '%log_bin%';
  3. +-----------------------------------------+-------+
  4. | Variable_name                           | Value |
  5. +-----------------------------------------+-------+
  6. | log_bin                                 | OFF   |
  7. | sql_log_bin                             | ON    |
  8. +-----------------------------------------+-------+
  9. 3 rows in set (0.00 sec)
  10. --- 通过以上输出信息可以看到log_bin为off状态，表示binlog日志功能尚未开启
  12. # 已开启binlog日志功能后，查看系统binlog功能配置参数状态
  13. mysql> show variables like '%log_bin%';
  14. +-----------------------------------------+-------+
  15. | Variable_name                           | Value |
  16. +-----------------------------------------+-------+
  17. | log_bin                                 | ON    |
  18. | sql_log_bin                             | ON    |
  19. +-----------------------------------------+-------+
  20. 3 rows in set (0.00 sec)
  21. --- 通过以上输出信息可以看到log_bin为on状态，表示binlog日志功能已经开启
复制代码

  • 3_1 日志信息基本配置
  1. server_id=6
  2. -- 进行主从操作时，需要进行此信息配置；
  3. log_bin=ON      
  4. -- 默认日志功能处于关闭状态
  5. log_bin_basename=/data/3306/data/binlog        
  6. -- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
  8. # 配置信息简写方式：开启数据库binlog日志记录功能
  9. [root@cheng ~]# vim /etc/my.cnf
  10. -- 激活binlog日志记录功能，需要对数据库服务配置文件进行编辑修改
  11. [mysqld]
  12. server_id=6
  13. log_bin=/data/3306/binlog/mysql-bin
  14. -- 进行binlog日志目录路径信息修改时，需要创建指定的目录并设置权限信息，最后需要重新启动数据库服务生效；
  15. 或者
  16. log_bin=binlog
  17. -- 只是设置日志名称信息，日志会自动保存到数据库服务指定的数据目录中；
  19. # 配置文件修改后需要重启数据库服务，加载配置文件改动的信息：
  20. [root@cheng ~]# /etc/init.d/mysqld restart
  21. [root@cheng ~]# ll -h /data/3306/data/binlog*
  22. -rw-rw----. 1 mysql mysql 245 6月  24 02:19 /data/3306/data/binlog.00000N
  23. -rw-rw----. 1 mysql mysql   16 6月  24 02:19 /data/3306/data/binlog.index
  24. -- 数据库服务重启后，已经可以在数据库的数据存储目录中，看到binlog日志文件的踪影
复制代码
说明：企业真实环境，日志处于默认激活记录状态，可以使用日志信息进行灾难数据恢复，以及可以用于实现主从复制；


  • 3_2 日志配置信息扩展
    参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html
  1. # 参数一：sync_binlog 表示刷新日志到磁盘策略
  2. mysql> select @@sync_binlog;
  3. +---------------------+
  4. | @@sync_binlog       |
  5. +---------------------+
  6. |                   1 |
  7. +---------------------+
  8. 1 row in set (0.00 sec)
  9. -- 在进行主从同步过程的双一标准的其中一个1的信息配置，主要是控制缓冲区里的binlog日志信息如何刷写到磁盘中；
  10. -- 此参数信息是有三种方式进行配置的：
  11. -- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中；
  12. -- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全)
  13. -- 参数信息配置N：表示每组事务提交，按照组的事务次数定义，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
  15. # 参数二：binlog_format 定义binlog日志的格式信息
  16. mysql> select @@binlog_format;
  17. +------------------------+
  18. | @@binlog_format        |
  19. +------------------------+
  20. | ROW                    |
  21. +------------------------+
  22. 1 row in set (0.00 sec)
  23. -- 在进行主从同步数据恢复时，此参数配置可能会影响数据恢复的一致性问题；
  24. -- 此参数信息是有三种方式进行配置的，确定了主从复制的级别，只针对DML语句的日志才有效；
  25. -- 参数信息配置 statement（SBR）：语句格式记录binlog；
  26. create database wenC;  -- DDL DCL语句只能使用statement 表示的就是原原本本的语句信息，即做什么就记录什么；
  27. -- 参数信息配置 row（RBR）：行格式记录binlog（默认模式）
  28. update t1 set a=10 where id<10;    -- 会记录行的变化信息，属于底层的记录信息，可能会有多个变化日志信息记录
  29. -- 参数信息配置 mixed（MBR）：混合格式记录binlog
  30.     -- 由数据库服务自行决定，是记录语句信息，还是记录行的变化信息；
复制代码
查看方式二：确认数据库binlog日志状态
  1. mysql> show binary logs;
  2. +------------------+-------------+--------------+
  3. | Log_name         | File_size   | Encrypted    |
  4. +------------------+-------------+--------------+
  5. | binlog.000001    |         156 | No           |
  6. +------------------+-------------+--------------+
  7. -- 获取数据库服务运行过程中，使用的binlog日志的情况
  9. mysql> flush logs;
  10. Query OK, 0 rows affected (0.12 sec)
  11. -- 可以执行flush刷新命令，从而生成新的binlog日志文件，类似于实现了日志切割功能；
  12. mysql> show binary logs;
  13. +------------------+-------------+--------------+
  14. | Log_name         | File_size   | Encrypted    |
  15. +------------------+-------------+--------------+
  16. | binlog.000001    | 200         | No           |
  17. | binlog.000002    | 156         | No           |
  18. +------------------+-------------+--------------+
  19. 2 rows in set (0.00 sec)
复制代码
查看方式三：查看数据库binlog日志信息
  1. mysql> create database test_binlog;
  2. Query OK, 1 row affected (0.03 sec)
  3. -- 模拟数据服务有修改操作
  4. mysql> select * from world.city limit 1;
  5. Query OK, 1 row affected (0.03 sec)
  6. -- 模拟数据服务有修改操作
  7. mysql> show binary logs;
  8. +------------------+-------------+--------------+
  9. | Log_name         | File_size   | Encrypted    |
  10. +------------------+-------------+--------------+
  11. | binlog.000001    |  200        | No           |
  12. | binlog.000002    |  362        | No           |
  13. +------------------+-------------+--------------+
  14. 2 rows in set (0.00 sec)
  15. -- 可以看到binlog日志的存储量发生了变化，但是在做查询操作时，binlog日志的存储量并未发生变化
  17. mysql> show master status;
  18. +------------------+------------+------------------+-----------------------+-------------------------+
  19. | File             | Position   | Binlog_Do_DB     | Binlog_Ignore_DB      | Executed_Gtid_Set       |
  20. +------------------+------------+------------------+-----------------------+-------------------------+
  21. | binlog.000002    |  362       |                  |                       |                         |
  22. +------------------+------------+------------------+-----------------------+-------------------------+
  23. 1 row in set (0.00 sec)
  24. -- 查看获取当前使用的binlog日志情况，以及产生的日志量字节大小；
复制代码
具体binlog事件信息：
1.png

具体binlog事件记录信息分析：
列号列信息解释说明01Log_name表示指定查看的binlog日志文件名称信息02Pos表示binlog日志事件开始的位置点，用于截取二进制日志信息标识05End_log_pos表示binlog日志事件结束的位置点，用于截取二进制日志信息标识06Info表示binlog中具体的事件内容信息查看方式四：筛选数据库binlog日志事件
  1. mysql> show binlog events in 'binlog.000002';
  2. -- binlog日志信息是以事件方式进行记录的，所以日志查看过程是查看事件信息
  3. -- 一般binlog日志的前两行，表示日志格式头信息（日志简单的描述信息）
  4. -- 一般binlog日志中的query信息，就是对数据库的操作语句，其中包含了创建数据库的语句；
复制代码
说明：在实际生产环境中，若binlog日志量比较大时，需要快速过滤关键日志事件行，可以使用以上查看日志方法；
获取数据库binlog日志记录信息异常：
进行数据库服务数据信息更改操作，随后查看binlog日志信息的变化：
  1. # 模拟生成binlog日志事件信息
  2. mysql> source ~/world.sql;
  3. mysql> drop database world;
  4. mysql> source ~/world.sql;
  6. # 获取删除数据库的事件信息：
  7. # 筛选数据库日志方式一：
  8. [root@cheng data]# mysql -e "show binlog events in 'binlog.000002'"|grep "drop database"
  9. binlog.000002        722789        Query        1        722896        drop database world /* xid=5363 */
  10. -- 获取指定事件信息产生的起点位置和终点位置信息；
  12. # 筛选数据库日志方式二：
  13. mysql> pager less
  14. -- 在数据库中定义pager功能，数据库连接会话退出即失效；
  15. mysql> show binlog events in 'binlog.000002';
  16. -- 此时查看日志事件信息具有了翻页功能
  17. /drop database
  18. | binlog.000002 |  722789 | Query          |         1 |      722896 | drop database world /* xid=5363 */
  19. mysql> pager grep "drop database"
  20. PAGER set to 'grep "drop database"'
  21. -- 表示开启数据库pager的过滤功能
  22. mysql> show binlog events in 'binlog.000002';
  23. | binlog.000002 |  722789 | Query          |         1 |      722896 | drop database world /* xid=5363 */
  24. -- 再次查看binlog事件信息时，只过滤显示删除数据库的操作事件日志
复制代码

  • 3_4 日志信息应用实战
数据库数据异常恢复（简单情况）
在实际生成环境中，可以利用binlog日志记录的信息截取，实现数据库异常情况下的数据信息恢复功能；
数据库异常恢复情况环境准备：
  1. # 进行数据库创建操作
  2. mysql> create database wenC;
  3. mysql> show databases;
  5. # 查看获取binlog日志记录信息
  6. [root@cheng ~]# mysqlbinlog /var/lib/mysql/binlog.000001
  7. mysqlbinlog: unknown variable 'default-character-set=utf8mb4'
  8. -- 由于在数据库在客户端配置文件中添加了default-character-set=utf8mb4字符编码信息，因此造成无法查看binlog
  9. [root@cheng ~]# cat /etc/my.cnf.d/client.cnf
  10. [client]
  11. #default-character-set=utf8mb4
  12. [client-mariadb]
  13. #default-character-set=utf8mb4
  14. -- 可以临时调整先将客户端的字符编码配置信息注释，
  15. [root@cheng ~]# mysqlbinlog /var/lib/mysql/binlog.000001
  16. ... 省略部分信息 ...
  17. # at 494
  18. #220624  2:35:02 server id 1  end_log_pos 579 Query        thread_id=2        exec_time=0        error_code=0
  19. SET TIMESTAMP=1656009302/*!*/;
  20. create database wenC
  21. /*!*/;
  22. ... 省略部分信息 ...
  23. -- 在binlog日志文件中，已经记录了之前的创建xiaoQ的更改操作记录信息
复制代码
数据库二进制日志信息查看方法：
  1. # 切换新的binlog日志文件做模拟数据恢复
  2. mysql> flush logs;
  3. Query OK, 0 rows affected (0.03 sec)
  4. mysql> show master status;
  5. +------------------+------------+------------------+-----------------------+-------------------------+
  6. | File             | Position   | Binlog_Do_DB     | Binlog_Ignore_DB      | Executed_Gtid_Set       |
  7. +------------------+------------+------------------+-----------------------+-------------------------+
  8. | binlog.000003    |  156       |                  |                       |                         |
  9. +------------------+------------+------------------+-----------------------+-------------------------+
  10. 1 row in set (0.01 sec)
  11. -- 确认已经刷新生成了新的binlog日志文件；
  13. # 进行基本的数据库SQL语句操作：
  14. mysql> create database bindb;
  15. mysql> use bindb;
  16. mysql> create table t1 (id int);
  17. mysql> begin;
  18. mysql> insert into t1 values(1);
  19. -- 在没有进行事务提交前，操作的事务事件信息，是不会出现在binlog事件日志中的
  20. mysql> commit;
  21. -- 对于数据库的binlog日志，只会记录事务已经提交的DML语句信息，没有提交的DML语句是不会进行记录的；
  22. -- 在日志中变化的DML语句信息是无法识别的，因为记录DML操作的语句默认是以ROW模式记录的；
复制代码
binlog日志内容中主要关注的信息：

  • 通过日志信息查看DDL操作语句信息（记录方式 SBR）
2.png


  • 通过日志信息查看DML操作语句信息（记录方式 RBR）
3.png

以上ROW模式记录的信息是加密显示，无法直接查看的，可以使用下面命令参数进行获取详细信息：
  1. [root@cheng ~]# mysqlbinlog /data/3306/data/binlog.000003
  2. -- 对于数据库binlog日志信息，是无法直接查看内容信息，需要利用相关命令工具进行查看
  3. # The proper term is pseudo_replica_mode, but we use this compatibility alias
  4. # to make the statement usable on server versions 8.0.24 and older.
  5. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  6. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  7. DELIMITER /*!*/;
  8. # at 4
  9. #221121 13:12:59 server id 1  end_log_pos 125 CRC32 0xbb7d1fd1         Start: binlog v 4, server v 8.0.26 created 221121 13:12:59
  10. # Warning: this binlog is either in use or was not closed properly.
  11. BINLOG '
  12. 2wh7Yw8BAAAAeQAAAH0AAAABAAQAOC4wLjI2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  13. AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
  14. CigB0R99uw==
  15. '/*!*/;
  16. # at 125
  17. #221121 13:12:59 server id 1  end_log_pos 156 CRC32 0x04874c92         Previous-GTIDs
  18. # [empty]
  19. -- binlog日志文件156之前的内容是可以忽略的，表示是日志文件的头格式内容信息
  20. # at 156
  21. #221121 13:16:19 server id 1  end_log_pos 233 CRC32 0xd73c14e1         Anonymous_GTID        last_committed=0        sequence_number=1        rbr_only=no        original_committed_timestamp=1669007779100380        immediate_commit_timestamp=1669007779100380        transaction_length=188
  22. # original_commit_timestamp=1669007779100380 (2022-11-21 13:16:19.100380 HKT)
  23. # immediate_commit_timestamp=1669007779100380 (2022-11-21 13:16:19.100380 HKT)
  24. /*!80001 SET @@session.original_commit_timestamp=1669007779100380*//*!*/;
  25. /*!80014 SET @@session.original_server_version=80026*//*!*/;
  26. /*!80014 SET @@session.immediate_server_version=80026*//*!*/;
  27. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  28. -- binlog日志文件已事件形式进行记录，主要关注两个at内容之间的信息，即表示的是一个事件信息；
  29. # at 233
  30. -- binlog日志中一个事件的开始，就表示上一个事件的结束，在binlog中记录的事件日志信息是连续的；
  31. #221121 13:16:19 server id 1  end_log_pos 344 CRC32 0x624986f5         Query        thread_id=11        exec_time=0        error_code=0        Xid = 10728
  32. SET TIMESTAMP=1669007779/*!*/;
  33. SET @@session.pseudo_thread_id=11/*!*/;
  34. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  35. SET @@session.sql_mode=1168113696/*!*/;
  36. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  37. /*!\C utf8mb4 *//*!*/;
  38. SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
  39. SET @@session.lc_time_names=0/*!*/;
  40. SET @@session.collation_database=DEFAULT/*!*/;
  41. /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
  42. /*!80016 SET @@session.default_table_encryption=0*//*!*/;
  43. create database bindb
  44. /*!*/;
  45. # at 344
复制代码
数据库模拟异常情况破坏操作：
  1. [root@cheng ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/data/binlog.000003
  2. -- 以上添加的参数信息，表示将DML的ROW格式语句信息，进行格式化处理输出；
  3. # at 739
  4. #221121 13:17:45 server id 1  end_log_pos 779 CRC32 0xb468b459         Write_rows: table id 101 flags: STMT_END_F
  5. ### INSERT INTO `bindb`.`t1`
  6. -- 利用DML语句做的插入语句信息就显示出来了
  7. ### SET
  8. ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
  9. -- 以上日志记录的信息，可以用命令实现，如下：
  10. mysql > insert into t1 set id=1;
  11. 等价于
  12. mysql > insert into t1 values(1);
复制代码
数据库异常情况数据恢复操作：
  1. mysql> drop database bindb;
  2. -- 模拟破坏性操作，删除数据库
复制代码
数据库数据异常恢复

情况一：日志文件被清理过，可能建库语句所在日志已经丢失

项目背景：一个数据库三年前就创建了，但是日志信息只记录一个月，这个库被误删除了；
解决方案：
A计划：最近一次全备+全备之后，误删除之前所有binlog，进行一同恢复；（全备数据+增量数据）
B计划：利用延时从库，进行数据恢复；
情况二：所需日志跨越多个文件，如何进行日志信息的截取

解决方案：
A计划：只有position号的方式，可以进行分段截取，进行分段恢复数据；
B计划：根据Datatime时间信息方式，可能会出现准确性不高的情况（因为每一秒可能有多个事件产生）；
C计划：启用GTID（全局事务ID）方式，无论跨越多少个日志文件，每个事务操作的事件ID信息都是唯一且递增的（5.6+引入）；
实践操作：
C计划：基于GTID方式对binlog进行管理（利用GTID实现日志截取）
数据库异常恢复情况环境准备：
  1. # 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
  2. mysql> show binlog events in 'binlog.000002';
  3. -- 查看截取日志信息事件区域范围
  4. [root@cheng ~]# mysqlbinlog --start-position=233 --stop-position=1162 /data/3306/data/binlog.000003 >/tmp/bin.sql
  5. -- 依据binlog日志的position号码，即可获取到想要恢复数据信息；
  7. # 根据截取的日志信息，进行数据库服务数据恢复
  8. mysql> set sql_log_bin=0;
  9. -- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；
  10. mysql> source /tmp/bin.sql
  12. # 查看确认数据信息是否恢复
  13. mysql> use bindb;
  14. mysql> show tables;
  15. mysql> select * from t1;
复制代码
数据库模拟异常情况破坏操作：
  1. # 刷新新的binlog日志进行操作
  2. mysql> flush logs;
  3. -- 生成新的binlog日志信息
  5. # 确认新的日志编号是否是连续的
  6. mysql> create database test5;
  7. mysql> show binlog events in "binlog.000004"
  8. -- 可以看出新的binlog日志文件中，记录的gtid编号信息是延续了上一个binlog日志gtid集合信息，继续连续进行记录；
  10. # 进行基本的数据库SQL语句操作：
  11. mysql> create database gtdb;
  12. mysql> use gtdb;
  13. mysql> create table t1(id int);
  14. mysql> insert into t1 values(1);
  15. mysql> commit;
  16. mysql> insert into t1 values(2);
  17. mysql> commit;
  18. mysql> insert into t1 values(3);
  19. mysql> commit;
  21. # 进行binlog事件信息查看
  22. mysql> show binlog events in 'binlog.000004';
  23. -- 可以获取以上的数据操作事件信息，
复制代码
数据库异常情况数据恢复操作：
  1. mysql> drop database gtdb;
  2. -- 模拟破坏性操作，删除数据库
复制代码

  • GTID概念介绍：
GTID（global transation id）称为全局事务（事件）ID，标识binlog日志记录的唯一性；
GTID信息的表示方式：
表现形式关键列解释说明server_uuid:Nserver_uuid表示数据库初始化启动之后，自动生成的随机数信息（唯一的）N表示第几个相关的事务或事件信息，会不断进行自增server_uuid信息查看：
  1. # 根据日志信息查看相关的事件情况（获取GTID编号范围）
  2. mysql> show binlog events in 'binlog.000004';
  4. # 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
  5. [root@cheng ~]# mysqlbinlog --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004 >/tmp/gtid.sql
  6. -- 依据binlog日志的GTID信息，即可获取到想要恢复数据信息；
  8. # 根据截取的日志信息，进行数据库服务数据恢复
  9. mysql> set sql_log_bin=0;
  10. -- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
  11. mysql> source /tmp/gtid.sql
  12. -- 默认此时报错恢复失败，因为GTID截取的日志恢复数据时，具有幂等性，由于binlog中已经记录了3-7的GTID事件信息
  13. mysql> show master status;
  14. +---------------+----------+--------------+------------------+------------------------------------------+
  15. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
  16. +---------------+----------+--------------+------------------+------------------------------------------+
  17. | binlog.000004 |     1905 |              |                  | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1-8 |
  18. +---------------+----------+--------------+------------------+------------------------------------------+
  19. 1 row in set (0.00 sec)
  20. -- 通过查看确认，核实清楚binlog中已经记录了3-7的GTID事件信息
  22. # 利用GTID日志信息恢复报错处理方式一：将系统中日志中的GTID信息清除掉（不建议）
  23. # 利用GTID日志信息恢复报错处理方式二：删除与幂等性冲突的记录信息
  24. [root@cheng ~]# mysqlbinlog --skip-gtids --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004 >/tmp/gtid.sql
  25. -- 表示跳过gtid的检查过程，即截取的日志中不再含有GTID的配置语句信息，自然解决了幂等性冲突问题；
  26. -- 开启了GTID之后，依然可以使用pos方式进行日志信息截取与恢复；
  28. # 查看确认数据信息是否恢复
  29. mysql> use gtdb;
  30. mysql> show tables;
  31. mysql> select * from t1;
  32. -- 查看test1数据库中的t1表的数据信息是否恢复
  34. # 操作扩展：可以实现排除指定gtid信息不做日志记录截取
  35. [root@cheng ~]# mysqlbinlog --exclude-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:4'  --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004
  37. # 操作扩展：跨多日志文件信息截取
  38. [root@cheng ~]# mysqlbinlog --skip-gtids --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:1-10' /data/3306/data/binlog.000001  /data/3306/data/binlog.000002 /data/3306/data/binlog.000003 >/tmp/gtid.sql
复制代码

  • GTID功能作用：
利用GTID方式管理binlog，实质上就是对于数据库的每个事务产生事件信息打上唯一标识信息（id号）；
利用GTID方式管理binlog，主要目的是处理数据库主从问题，解决主从数据库的数据一致性问题；
简单描述：标识事务的唯一性，保证日志恢复时的一致性，并且具备”幂等性”；

  • GTID功能配置：
GTID功能相关参数介绍：
  1. mysql> select @@server_uuid;
  2. +---------------------------------------------------+
  3. | @@server_uuid                                     |
  4. +---------------------------------------------------+
  5. | 7afe4f8c-5e36-11ed-b083-000c29d44f34              |
  6. +---------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8. -- 表示数据库每次初始化之后自动生成，不建议手工进行修改；
  10. [root@cheng ~]# cat /data/3306/data/auto.cnf
  11. [auto]
  12. server-uuid=7afe4f8c-5e36-11ed-b083-000c29d44f34
  13. -- 在数据库的数据目录文件中也可以查询到
复制代码
GTID功能相关参数激活：
  1. # GTID功能参数信息介绍（3个重要的配置参数）
  2. mysql> select @@gtid_mode;
  3. +-------------------+
  4. | @@gtid_mode       |
  5. +-------------------+
  6. | OFF               |
  7. +-------------------+
  8. 1 row in set (0.00 sec)
  9. -- 设置是否开启显示gtid信息功能（在5.7之后是有个匿名的gtid，是数据库系统自己维护的）
  11. mysql> select @@enforce_gtid_consistency;
  12. +-------------------------------------+
  13. | @@enforce_gtid_consistency          |
  14. +-------------------------------------+
  15. | OFF                                 |
  16. +-------------------------------------+
  17. 1 row in set (0.00 sec)
  18. -- 设置是否开启GTID强制一致性功能
  19. -- 对某些 SQL 会有限制，例如 CREATE TABLE … SELECT 必须得分成两条语句执行。
  20. -- OFF：    表示事务允许违反 GTID 一致性。
  21. -- ON：     表示事务不允许违反 GTID 一致性，有相关 SQL 会直接返回异常。
  22. -- WARN：表示事务允许违反 GTID 一致性，但会将警告信息记录到 ERROR LOG。
  24. mysql> select @@log_slave_updates;
  25. +----------------------------+
  26. | @@log_slave_updates        |
  27. +----------------------------+
  28. |      1                     |
  29. +----------------------------+
  30. 1 row in set, 1 warning (0.01 sec)
  31. -- 和配置主从有关（在8.0.26开始 推荐配置log_replica_updates替代log_slave_updates参数）
  32. -- 此参数表示从服务器从主服务器接收的更新信息，是否也会记录在从服务器本地的二进制文件中
复制代码

  • GTID信息查看：
  1. [root@cheng ~]# vim /etc/my.cnf
  2. [mysqld]
  3. gtid_mode=on
  4. enforce_gtid_consistency=1
  5. log_slave_updates=on
  6. -- 配置文件信息修改完毕后，重启数据库服务使配置生效
复制代码
情况三：如何从日志文件中恢复单库、单表、或者部分行数据信息

解决方案：
A计划：可以利用命令单独截取某个数据库的日志信息；mysqlbinlog -d world  xxx > xxxx
B计划：可以借助第三方工具实现单表或部分数据恢复；binlog2sql（python） 过滤指定表数据或过滤指定表的部分数据；
实战操作：
A计划：单库日志信息截取，企业实战过程：
数据库异常恢复情况环境准备：
  1. mysql> show master status;
  2. +------------------+-----------+-------------------+-----------------------+-------------------------+
  3. | File             | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set       |
  4. +------------------+-----------+-------------------+-----------------------+-------------------------+
  5. | binlog.000004    |    156    |                   |                       |                         |
  6. +------------------+-----------+-------------------+-----------------------+-------------------------+
  7. 1 row in set (0.03 sec)
  8. -- 在GTID功能被激活后，就会在Executed_Gtid_Set列中显示GTID集合信息；
  10. mysql> create database test3;
  11. Query OK, 1 row affected (0.08 sec)
  12. -- 模拟创建数据库，产生新的事件信息
  13. mysql> show master status;
  14. +------------------+----------+--------------+------------------+----------------------------------------+
  15. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
  16. +------------------+----------+--------------+------------------+----------------------------------------+
  17. | binlog.000004    |      344 |              |                  | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1 |
  18. +------------------+----------+--------------+------------------+----------------------------------------+
  19. 1 row in set (0.01 sec)
  20. -- GTID信息随着新的事件产生，随之发生变化
  21. mysql> create database test4;
  22. Query OK, 1 row affected (0.03 sec)
  23. -- 模拟创建数据库，产生新的事件信息
  24. mysql> show master status;
  25. +---------------+----------+--------------+------------------+------------------------------------------+
  26. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
  27. +---------------+----------+--------------+------------------+------------------------------------------+
  28. | binlog.000004 |      532 |              |                  | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1-2 |
  29. +---------------+----------+--------------+------------------+------------------------------------------+
  30. 1 row in set (0.00 sec)
  31. -- GTID信息随着新的事件产生，随之发生变化
  33. mysql> show binlog events in 'binlog.000004';
  34. +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
  35. | Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
  36. +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
  37. | binlog.000004 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.26, Binlog ver: 4                                 |
  38. | binlog.000004 | 125 | Previous_gtids |         1 |         156 |                                                                   |
  39. | binlog.000004 | 156 | Gtid           |         1 |         233 | SET @@SESSION.GTID_NEXT= '7afe4f8c-5e36-11ed-b083-000c29d44f34:1' |
  40. | binlog.000004 | 233 | Query          |         1 |         344 | create database test3 /* xid=6 */                                 |
  41. | binlog.000004 | 344 | Gtid           |         1 |         421 | SET @@SESSION.GTID_NEXT= '7afe4f8c-5e36-11ed-b083-000c29d44f34:2' |
  42. | binlog.000004 | 421 | Query          |         1 |         532 | create database test4 /* xid=8 */                                 |
  43. +---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
  44. 6 rows in set (0.00 sec)
  45. -- 在每个数据库操作事件之前，会显示GTID的唯一标识信息
复制代码
数据库模拟异常情况破坏操作：
  1. # 查看获取当前binlog日志状态信息
  2. mysql > show master status;
  3. +------------------+-----------+-------------------+-----------------------+-------------------------+
  4. | File             | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set       |
  5. +------------------+-----------+-------------------+-----------------------+-------------------------+
  6. | binlog.000003    |      1269 |                   |                       |                         |
  7. +------------------+-----------+-------------------+-----------------------+-------------------------+
  9. # 进行基本的数据库SQL语句操作：
  10. mysql> create database test1;
  11. mysql> create table t1 (id int);
  12. mysql> insert into t1 values(1);
  13. mysql> insert into t1 values(2);
  14. mysql> commit;
  15. mysql> select * from t1;
  16. +------+
  17. | id     |
  18. +------+
  19. |      1 |
  20. |      2 |
  21. +------+
  22. 2 rows in set (0.00 sec)
  23. -- 创建了一个test1数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
  24. mysql> create database test2;
  25. mysql> use test2;
  26. mysql> create table t2 (id int);
  27. mysql> insert into t2 values(1);
  28. mysql> insert into t2 values(2);
  29. mysql> commit;
  30. -- 创建了一个test2数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
  31. mysql> use test1;
  32. mysql> insert into t1 values(3);
  33. mysql> insert into t1 values(4);
  34. mysql> use test2;
  35. mysql> insert into t2 values(3);
  36. mysql> insert into t2 values(4);
  37. mysql> commit;
  38. mysql> select * from test1.t1;
  39. mysql> select * from test2.t2;
  40. -- 通过操作不同的数据库，以及不同的数据表，实现binlog日志事件信息的交叉
复制代码
数据库异常情况数据恢复操作：
  1. mysql> drop database test1;
  2. -- 模拟破坏性操作，删除数据库
复制代码
B计划：可以借助第三方工具实现单表或部分数据恢复；
利用binlog2sql工具可以处理上面的企业需求，此软件是利用python语言开发的，主要用来处理binlog日志信息；
从软件应用方面来说主要包含两个核心功能：

  • 可以友好的展示或者管理二进制日志信息（binlog），进而可以过滤出单独表的信息，甚至表中指定行的信息；
  • 可以快速的实现DML操作语句的闪回功能，即实现通过日志信息翻转方式，进行数据信息的恢复；
说明：binlog2sql工具是模拟了一个从库，进行日志信息分析，需要保证数据库服务启动状态，且不支持离线方式分析日志内容；
数据库异常恢复情况环境准备：
  1. # 根据日志信息查看相关的事件情况
  2. mysql> show binlog events in 'binlog.000003';
  4. # 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
  5. [root@cheng ~]# mysqlbinlog --start-position=1346 --stop-position=4116 -d test1 /data/3306/data/binlog.000003 >/tmp/bin.sql
  6. -- 依据binlog日志的position号码，即可获取到想要恢复数据信息，并利用-d参数导出指定数据库相关数据；
  8. # 根据截取的日志信息，进行数据库服务数据恢复
  9. mysql> set sql_log_bin=0;
  10. -- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
  11. mysql> source /tmp/bin.sql
  13. # 查看确认数据信息是否恢复
  14. mysql> use test1;
  15. mysql> show tables;
  16. mysql> select * from t1;
  17. -- 查看test1数据库中的t1表的数据信息是否恢复
  18. mysql> use test2;
  19. mysql> show tables;
  20. mysql> select * from t2;
  21. -- 查看test2数据库中的t2表的数据信息是否破坏
复制代码
数据库日志信息工具分析查看：（解析日志事件SQL）
  1. # 下载第三方日志分析工具
  2. [root@cheng ~]# cd /opt/
  3. [root@cheng ~]# git clone https://github.com/danfengcao/binlog2sql.git
  4. [root@cheng ~]# cd /opt/binlog2sql
  5. -- 此工具在mariadb中可以通过打补丁方式，进行部署安装；但是在mysql 8.0中暂时还没有集成，需要单独安装
  7. # 部署第三方工具运行环境
  8. [root@cheng ~]# yum install -y python3
  9. [root@cheng ~]# pip3 install -r requirments.txt
  10. [root@cheng ~]# pip3 show pymysql
  11. [root@cheng ~]# pip3 install --upgrade pymysql    （此步骤可以忽略）
  12. -- 以上pip3下载软件缓慢，可以优化pip3下载源
  13. -- 下载源优化方法：https://developer.aliyun.com/mirror/pypi?spm=a2c6h.13651102.0.0.3e221b11H9Q7La
  15. # 在指定数据库中创建多个数据表
  16. mysql> use test1;
  17. mysql> create table t11 (id int);
  18. mysql> insert into t11 values (1),(2);
  19. mysql> commit;
复制代码
数据库模拟异常情况破坏操作：
  1. [root@cheng binlog2sql]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='binlog.000003'
  2. INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
  3. INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
  4. INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
  5. INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
  6. [root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t11 --start-file='binlog.000003'
  7. INSERT INTO `test1`.`t11`(`id`) VALUES (1); #start 4704 end 4954 time 2022-11-21 23:47:51 gtid
  8. INSERT INTO `test1`.`t11`(`id`) VALUES (2); #start 4704 end 4954 time 2022-11-21 23:47:51 gtid
  9. -- 表的数据信息导出后，可以直接复制命令信息恢复，或者导出sql文件进行导入恢复；
复制代码
数据库日志信息工具分析查看：（解析日志事件SQL）
  1. # 在指定数据库的相应数据表中做修改操作
  2. mysql> use test1;
  3. mysql> update t1 set id=10 where id=1;
  4. mysql> commit;
  6. # 在指定数据库的相应数据表中做删除操作
  7. mysql> use test1;
  8. mysql> delete from t1 where id=3;
  9. mysql> commit;
复制代码
数据库日志信息工具回滚操作：（生成指定事件回滚语句-闪回操作）
假设在某个企业的应用场景中，有3000万行数据，占用200G的存储空间，其中误删除了10行数据信息，请问如何进行恢复数据？
  1. [root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='binlog.000003'
  2. INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
  3. INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
  4. INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
  5. INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
  6. UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
  7. DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
  9. # 只想查看删除操作信息
  10. [root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003'
  11. DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
  12. -- sql-type参数只能过滤DML类型语句信息，一般常见过滤的是 insert update delete
  14. # 只想查看修改操作信息
  15. [root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003'
  16. UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
  18. # 只想查看插入操作信息
  19. [root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003'
  20. INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
  21. INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
  22. INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
  23. INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
复制代码
-->**********   如果您认为这篇文章还不错或者有所收获，请点击右下角的【推荐】/【赞助】按钮，因为您的支持是我继续创作分享的最大动力！   **********
            作者：讲文张字
            出处：https://www.cnblogs.com/zhangwencheng
            版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出             原文链接
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
MySQL数据库日志总结

使用道具 举报

相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
决台
8 小时前

0

粉丝关注

24

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫