MySQL-相关日志

已于 2024-06-09 22:03:42 修改
阅读量860 收藏 25
点赞数 27
分类专栏： MySQL 文章标签： mysql
于 2024-06-08 18:59:09 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/badBoy_badMan/article/details/139482174
版权

官方文档

1、MySQL支持的日志

MySQL有不同类型日志文件，用来存储不同类型的日志，分别为 二进制日志、错误日志、通用查询日志、慢查询日志、中继日志、数据定义语句日志

  • 慢查询日志：记录所有执行时间超过 long_query_time的所有查询，方便优化
  • 通用查询日志：记录所有连接的起始时间和中止时间，以及连接发送给数据库服务器的所有指令，对复原操作的实际场景、发现问题、甚至对数据库操作的审计有很大帮助
  • 错误日志：记录MySQL服务的启动，运行、中止MySQL服务时出现的问题，方便了解服务器的状态，进而对服务器进行维护
  • 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复
  • 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作
  • 数据定义语句日志：记录数据定义语句执行的元数据操作

缺点：

  • 日志功能会降低MySQL数据库的性能。MySQL服务器会花费很多时间记录日志
  • 日志功能会占用大量的磁盘空间。对于用户量非常大，操作非常平凡的数据库，日志文件需要存储空间设置比数据库文件需要的存储空间还要大。

2、慢查询日志（slow query log）

见MySQL-性能分析

3、通用查询日志（general query log）

通用查询日志用来 记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和结束时间，发送给MySQL服务器的所有SQL指令等。当数据库发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助准确定位问题

3.1、查看当前状态

  • 通过SQL语法 show variables like 'general%'; 如下所示
mysql> show variables like 'general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |  # 通用查询日志是处于关闭状态
| general_log_file | /var/lib/mysql/localhost.log |  # 通用查询日志文件名称为：localhost.log
+------------------+------------------------------+
2 rows in set (1.14 sec)
  • general_log 参数说明：默认关闭状态，一旦开启记录通用查询日志，MySQL会记录所有连接起止操作和相关SQL操作，消耗系统资源并占用磁盘空间。
  • 通用查询日志文件为 localhost.log，存储路径为 /var/lib/mysql/ ,默认也是数据路径。

3.2、启动日志的方式

  • 方式一：永久性，通过修改 my.cnf 配置文件来设置。在[mysqld] 组下加入log 选项，并重启MySQL服务，如下所示：
[root@localhost ~]# vim /etc/my.cnf
general_log=on
general_log_file=/var/lib/mysql/general-test.log
[root@localhost ~]# systemctl restart mysqld.service
  • 方式二：临时性方式，如下所示：
    • set global general_log=on;
    • set global general_log_file=‘filePath/fileName’;

3.3、查看日志信息

[root@localhost mysql]# vim general-test.log

/usr/sbin/mysqld, Version: 8.0.25 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2024-06-05T13:45:48.097757Z         8 Connect   root@localhost on  using Socket
2024-06-05T13:45:48.098777Z         8 Query     show variables like 'general%'
2024-06-05T13:45:57.610846Z         8 Quit
2024-06-05T13:46:06.682806Z         9 Connect   root@localhost on  using Socket
2024-06-05T13:46:06.682857Z         9 Connect   Access denied for user 'root'@'localhost' (using password: YES)
2024-06-05T13:46:17.784997Z        10 Connect   root@localhost on  using Socket
2024-06-05T13:46:17.785202Z        10 Query     select @@version_comment limit 1
2024-06-05T13:46:20.808524Z        10 Query     show variables like 'general%'
~

3.4、删除\刷新日志

  • 手动删除日志： 执行 show variables like ‘general_log%’; 手动删除返回日志信息
  • 刷新日志：mysqladmin -uroot -p flush-logs;
[root@localhost mysql]# mysqladmin -uroot -p flush-logs

4、错误日志

错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行、停止过程中的诊断信息，包括 错误、警告、提示等
通过错误日志可以查看系统的运行状态，便于及时发现故障，修复故障。如果MySQL服务 出现异常，错误日志是发现问题、解决故障的首选

4.1、启动日志

  • MySQL中错误日志功能是默认开启的，无法被禁止。默认情况下错误日志存储在MySQL数据库的数据文件下，默认名称为：mysqld.log。如需指定文件名，则需要在my.cnf中配置以下信息，并重启MySQL服务器
log-error=[path(日志文件所在目录路径)/fileName(日志文件名称)]

4.2、查看日志

mysql> show variables like 'log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
4 rows in set (0.00 sec)

4.3、删除\刷新日志

  • 删除操作
[root@localhost mysql]# rm -f /var/log/mysqld.log
  • 重名文件
[root@localhost mysql]# mv /var/log/mysqld.log /var/log/mysqld.log.bak
  • 重建日志
[root@localhost mysql]# install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
[root@localhost mysql]# mysqladmin -uroot -p flush-logs

5、二进制日志 ( bin log )

binlog 即 binary log ，二进制日志文件，也称为变更日志（update log）。记录数据库中所有执行的DDL 和 DML 等数据库更新事件的语句，但不包含没有修改任何数据的语句。
他以事件形式记录并保存在二进制文件中。通过这些信息可以再现数据更新操作的全过程

5.1、应用场景

  • 数据恢复：如果MySQL意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了那些修改，然后根据二进制日志文件中的记录来恢复数据库服务器
  • 数据复制：由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据的一致目的

MySQL数据备份、主备、主从、主主都离不开 bin log，需要依靠binlog来同步数据，保证数据的一致性

5.2、查看默认情况

  • 通过执行 show variables like '%log_bin%'; 获取信息，如下所示：
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.94 sec)
  • log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件。
  • log_bin_index：是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
  • log_bin_trust_function_creators：限制存储过程，用于主从复制，而存储函数有可能导致主从的数据不一致。当开启二进制日志后，需要限制存储函数的创建、修改、调用
  • log_bin_use_v1_row_events：只读系统变量
[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# ll
总用量 188940
-rw-r-----. 1 mysql mysql      156 5月   7 06:56 binlog.000010
-rw-r-----. 1 mysql mysql      156 5月   8 07:09 binlog.000011
-rw-r-----. 1 mysql mysql      156 5月   9 06:56 binlog.000012
-rw-r-----. 1 mysql mysql      156 5月  13 07:19 binlog.000013
-rw-r-----. 1 mysql mysql      609 5月  15 05:36 binlog.000014
-rw-r-----. 1 mysql mysql     2461 5月  22 06:15 binlog.000015
-rw-r-----. 1 mysql mysql      156 5月  23 06:34 binlog.000016
-rw-r-----. 1 mysql mysql      453 5月  27 06:03 binlog.000017
-rw-r-----. 1 mysql mysql     1190 5月  28 06:09 binlog.000018
-rw-r-----. 1 mysql mysql      156 5月  29 05:48 binlog.000019
-rw-r-----. 1 mysql mysql      156 5月  30 08:23 binlog.000020
-rw-r-----. 1 mysql mysql      381 6月   1 05:37 binlog.000021
-rw-r-----. 1 mysql mysql      156 6月   4 08:01 binlog.000022
-rw-r-----. 1 mysql mysql      156 6月   5 06:00 binlog.000023
-rw-r-----. 1 mysql mysql      179 6月   5 06:39 binlog.000024
-rw-r-----. 1 mysql mysql      179 6月   5 06:45 binlog.000025
-rw-r-----. 1 mysql mysql      200 6月   5 06:54 binlog.000026
-rw-r-----. 1 mysql mysql      156 6月   6 06:45 binlog.000027
-rw-r-----. 1 mysql mysql      156 6月   6 06:45 binlog.000028
-rw-r-----. 1 mysql mysql      304 6月   6 06:45 binlog.index

5.3、日志参数设置

  • 方式一：修改my.cnf配置文件
[root@localhost mysql]# vim /etc/my.cnf

[mysqld]
#
log-bin=rqtanc-bin
binlog_expire_logs_seconds=3600
max_binlog_size=10M

如果想改变日志文件的目录和名称，新建的文件夹需要使用mysql用户，使用以下命令

chown -R -v mysql:mysql binlog
  • 方式二：修改会话级变量
mysql> set sql_log_bin=0;

5.4、查看日志

MySQL重启一次，rqtanc-bin.000001 日志文件后缀文件默认加1，即日志文件的数量与服务重启的次数相同；如果日志长度超过了max_binlog_size的设定值就会创建一个新的日志文件。

  • 查看当前的二进制文件大小，通过执行 show binary logs;命令，如下所示：
mysql> show binary logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| rqtanc-bin.000001 |       156 | No        |
+-------------------+-----------+-----------+
1 row in set (0.11 sec)
  • 通过 mysqlbinlog命令工具，查看binlog日志：
[root@localhost mysql]# mysqlbinlog -v /rqtanc-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File '/rqtanc-bin.000001' not found (OS errno 2 - No such file or directory)
ERROR: Could not open log file
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  • mysql> show binlg events [ in 'log_name' ] [ from pos ] [ limit [ offset , ] row_rount ]
    • in ‘log_name’：指定查询的binlog文件名
    • from pos：指定从哪个pos起始点开始查起
    • limit [ offset , ]：偏移量
    • row_count：查询条数
mysql> show binlog events;
+-------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+-------------------+-----+----------------+-----------+-------------+-----------------------------------+
| rqtanc-bin.000001 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.25, Binlog ver: 4 |
| rqtanc-bin.000001 | 125 | Previous_gtids |         1 |         156 |                                   |
+-------------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.19 sec)

5.5、使用binlog恢复数据

如果MySQL启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQL binnlog工具从指定的时间点开始直到现在或另外一个事件点的日志中恢复数据

  • 语法：mysqlbinlog [ option ] filename | mysql -uuserName -pPassword;
    • filename：日志文件名
    • option：可选项，比较重要的两对参数是 --start-datetime、--stop-datetime 和 --start-position、--stop-position
      • –start-date 和 --end-date：可以指定恢复恢复数据库的起始时间点和结束时间点
      • –start-position 和 --stop-position ：可以指定恢复数据库的起始位置和结束位置
  • 数据模拟及实现数据恢复如下所示：
#查询日志文件
mysql> show binary logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| rqtanc-bin.000001 |       156 | No        |
| rqtanc-bin.000002 |       204 | No        |
| rqtanc-bin.000003 |       204 | No        |
| rqtanc-bin.000004 |       156 | No        |
+-------------------+-----------+-----------+
4 rows in set (0.00 sec)
#查询相关表
mysql> select * from rqtanc;
Empty set (0.00 sec)
#插入数据
mysql> insert into rqtanc values('1','rqtanc'),('2','rqtanc1'),('3','rqtanc3');
Query OK, 3 rows affected (0.37 sec)
Records: 3  Duplicates: 0  Warnings: 0
#查询数据
mysql> select * from rqtanc;
+----+---------+
| id | name    |
+----+---------+
|  1 | rqtanc  |
|  2 | rqtanc1 |
|  3 | rqtanc3 |
+----+---------+
3 rows in set (0.00 sec)
#模拟误删除操作
mysql> delete from rqtanc where id = '3';
Query OK, 1 row affected (0.11 sec)
#查询数据
mysql> select * from rqtanc;
+----+---------+
| id | name    |
+----+---------+
|  1 | rqtanc  |
|  2 | rqtanc1 |
+----+---------+
2 rows in set (0.00 sec)
#查询binlog日志
mysql> show binary logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| rqtanc-bin.000001 |       156 | No        |
| rqtanc-bin.000002 |       204 | No        |
| rqtanc-bin.000003 |       204 | No        |
| rqtanc-bin.000004 |       767 | No        |
+-------------------+-----------+-----------+
4 rows in set (0.00 sec)
#刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)
#再次查询binlog日志，有新日志文件生成
mysql> show binary logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| rqtanc-bin.000001 |       156 | No        |
| rqtanc-bin.000002 |       204 | No        |
| rqtanc-bin.000003 |       204 | No        |
| rqtanc-bin.000004 |       815 | No        |
| rqtanc-bin.000005 |       156 | No        |
+-------------------+-----------+-----------+
5 rows in set (0.00 sec)
  • 方式一：根据起始位置恢复数据
#查看binlog日志详情
mysql> show binlog events in 'rqtanc-bin.000004';
+-------------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------+
| rqtanc-bin.000004 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.25, Binlog ver: 4    |
| rqtanc-bin.000004 | 125 | Previous_gtids |         1 |         156 |                                      |
| rqtanc-bin.000004 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| rqtanc-bin.000004 | 235 | Query          |         1 |         310 | BEGIN                                |
| rqtanc-bin.000004 | 310 | Table_map      |         1 |         370 | table_id: 90 (test.rqtanc)           |
| rqtanc-bin.000004 | 370 | Write_rows     |         1 |         443 | table_id: 90 flags: STMT_END_F       |
| rqtanc-bin.000004 | 443 | Xid            |         1 |         474 | COMMIT /* xid=18 */                  |
| rqtanc-bin.000004 | 474 | Anonymous_Gtid |         1 |         553 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| rqtanc-bin.000004 | 553 | Query          |         1 |         628 | BEGIN                                |
| rqtanc-bin.000004 | 628 | Table_map      |         1 |         688 | table_id: 90 (test.rqtanc)           |
| rqtanc-bin.000004 | 688 | Delete_rows    |         1 |         736 | table_id: 90 flags: STMT_END_F       |
| rqtanc-bin.000004 | 736 | Xid            |         1 |         767 | COMMIT /* xid=20 */                  |
| rqtanc-bin.000004 | 767 | Rotate         |         1 |         815 | rqtanc-bin.000005;pos=4              |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------+
13 rows in set (0.05 sec)

# 根据起始位置恢复数据
[root@localhost ~]# /usr/bin/mysqlbinlog --start-position=235 --stop-position=553 --database=test /var/lib/mysql/rqtanc-bin.000004 | /usr/bin/mysql -uroot -p123456 -v test
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------

--------------
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
--------------

--------------
BINLOG '
6hhkZg8BAAAAeQAAAH0AAAAAAAQAOC4wLjI1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBU7Qrqg==
'
--------------

--------------
SET TIMESTAMP=1717836114
--------------

--------------
SET @@session.pseudo_thread_id=8
--------------

--------------
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1
--------------

--------------
SET @@session.sql_mode=1168113696
--------------

--------------
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1
--------------

--------------
/*!*/
--------------

--------------
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255
--------------

--------------
SET @@session.lc_time_names=0
--------------

--------------
SET @@session.collation_database=DEFAULT
--------------

--------------
/*!80011 SET @@session.default_collation_for_utf8mb4=255*/
--------------

--------------
BEGIN
--------------

--------------
BINLOG '
UhlkZhMBAAAAPAAAAHIBAAAAAFoAAAAAAAEABHRlc3QABnJxdGFuYwACAw8CyAACAQEAAgP8/wA7
OiP7
UhlkZh4BAAAASQAAALsBAAAAAFoAAAAAAAEAAgAC/wABAAAABnJxdGFuYwACAAAAB3JxdGFuYzEA
AwAAAAdycXRhbmMzWeEi3Q==
'
--------------

--------------
COMMIT
--------------

--------------
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*/
--------------

--------------
/*!80001 SET @@session.original_commit_timestamp=1717836158602573*/
--------------

--------------
/*!80014 SET @@session.original_server_version=80025*/
--------------

--------------
/*!80014 SET @@session.immediate_server_version=80025*/
--------------

--------------
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
--------------

--------------
BEGIN
--------------

--------------
ROLLBACK
--------------

--------------
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'
--------------

--------------
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
--------------

--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
--------------

mysql> select * from rqtanc;
+----+---------+
| id | name    |
+----+---------+
|  1 | rqtanc  |
|  2 | rqtanc1 |
|  3 | rqtanc3 |
+----+---------+
3 rows in set (0.01 sec)
  • 方式二：根据起始时间恢复数据
#查询日志详情信息
[root@localhost ~]# mysqlbinlog  -v '/var/lib/rqtanc-bin.000004'
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File '/var/lib/rqtanc-bin.000004' not found (OS errno 2 - No such file or directory)
ERROR: Could not open log file
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost ~]# mysqlbinlog -v '/var/lib/mysql/rqtanc-bin.000004'
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240608  1:40:10 server id 1  end_log_pos 125 CRC32 0xaa2bb453  Start: binlog v 4, server v 8.0.25 created 240608  1:40:10
BINLOG '
6hhkZg8BAAAAeQAAAH0AAAAAAAQAOC4wLjI1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBU7Qrqg==
'/*!*/;
# at 125
#240608  1:40:10 server id 1  end_log_pos 156 CRC32 0x70ab425d  Previous-GTIDs
# [empty]
# at 156
#240608  1:41:54 server id 1  end_log_pos 235 CRC32 0x977456ff  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes    original_committed_timestamp=1717836114823630      immediate_commit_timestamp=1717836114823630     transaction_length=318
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1717836114823630 (2024-06-08 01:41:54.823630 PDT)
# immediate_commit_timestamp=1717836114823630 (2024-06-08 01:41:54.823630 PDT)
/*!80001 SET @@session.original_commit_timestamp=1717836114823630*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 235
#240608  1:41:54 server id 1  end_log_pos 310 CRC32 0x4aa4c758  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1717836114/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 310
#240608  1:41:54 server id 1  end_log_pos 370 CRC32 0xfb233a3b  Table_map: `test`.`rqtanc` mapped to number 90
# at 370
#240608  1:41:54 server id 1  end_log_pos 443 CRC32 0xdd22e159  Write_rows: table id 90 flags: STMT_END_F

BINLOG '
UhlkZhMBAAAAPAAAAHIBAAAAAFoAAAAAAAEABHRlc3QABnJxdGFuYwACAw8CyAACAQEAAgP8/wA7
OiP7
UhlkZh4BAAAASQAAALsBAAAAAFoAAAAAAAEAAgAC/wABAAAABnJxdGFuYwACAAAAB3JxdGFuYzEA
AwAAAAdycXRhbmMzWeEi3Q==
'/*!*/;
### INSERT INTO `test`.`rqtanc`
### SET
###   @1=1
###   @2='rqtanc'
### INSERT INTO `test`.`rqtanc`
### SET
###   @1=2
###   @2='rqtanc1'
### INSERT INTO `test`.`rqtanc`
### SET
###   @1=3
###   @2='rqtanc3'
# at 443
#240608  1:41:54 server id 1  end_log_pos 474 CRC32 0xe09b76a8  Xid = 18
COMMIT/*!*/;
# at 474
#240608  1:42:38 server id 1  end_log_pos 553 CRC32 0x1abc857d  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes    original_committed_timestamp=1717836158602573      immediate_commit_timestamp=1717836158602573     transaction_length=293
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1717836158602573 (2024-06-08 01:42:38.602573 PDT)
# immediate_commit_timestamp=1717836158602573 (2024-06-08 01:42:38.602573 PDT)
/*!80001 SET @@session.original_commit_timestamp=1717836158602573*//*!*/;
/*!80014 SET @@session.original_server_version=80025*//*!*/;
/*!80014 SET @@session.immediate_server_version=80025*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 553
#240608  1:42:38 server id 1  end_log_pos 628 CRC32 0x0f1f2339  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1717836158/*!*/;
BEGIN
/*!*/;
# at 628
#240608  1:42:38 server id 1  end_log_pos 688 CRC32 0x46e9db54  Table_map: `test`.`rqtanc` mapped to number 90
# at 688
#240608  1:42:38 server id 1  end_log_pos 736 CRC32 0x25fe86d4  Delete_rows: table id 90 flags: STMT_END_F

BINLOG '
fhlkZhMBAAAAPAAAALACAAAAAFoAAAAAAAEABHRlc3QABnJxdGFuYwACAw8CyAACAQEAAgP8/wBU
2+lG
fhlkZiABAAAAMAAAAOACAAAAAFoAAAAAAAEAAgAC/wADAAAAB3JxdGFuYzPUhv4l
'/*!*/;
### DELETE FROM `test`.`rqtanc`
### WHERE
###   @1=3
###   @2='rqtanc3'
# at 736
#240608  1:42:38 server id 1  end_log_pos 767 CRC32 0xcdd3ee31  Xid = 20
COMMIT/*!*/;
# at 767
#240608  1:45:55 server id 1  end_log_pos 815 CRC32 0x09dfc5a8  Rotate to rqtanc-bin.000005  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

#根据起始时间恢复数据
[root@localhost ~]# /usr/bin/mysqlbinlog --start-datetime='2024-06-08 01:41:54' --stop-datetime='2024-06-08 01:42:37' --database=test /var/lib/mysql/rqtanc-bin.000004 | /usr/bin/mysql -uroot -p123456 -v test
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------

--------------
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
--------------

--------------
BINLOG '
6hhkZg8BAAAAeQAAAH0AAAAAAAQAOC4wLjI1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBU7Qrqg==
'
--------------

--------------
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*/
--------------

--------------
/*!80001 SET @@session.original_commit_timestamp=1717836114823630*/
--------------

--------------
/*!80014 SET @@session.original_server_version=80025*/
--------------

--------------
/*!80014 SET @@session.immediate_server_version=80025*/
--------------

--------------
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
--------------

--------------
SET TIMESTAMP=1717836114
--------------

--------------
SET @@session.pseudo_thread_id=8
--------------

--------------
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1
--------------

--------------
SET @@session.sql_mode=1168113696
--------------

--------------
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1
--------------

--------------
/*!*/
--------------

--------------
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255
--------------

--------------
SET @@session.lc_time_names=0
--------------

--------------
SET @@session.collation_database=DEFAULT
--------------

--------------
/*!80011 SET @@session.default_collation_for_utf8mb4=255*/
--------------

--------------
BEGIN
--------------

--------------
BINLOG '
UhlkZhMBAAAAPAAAAHIBAAAAAFoAAAAAAAEABHRlc3QABnJxdGFuYwACAw8CyAACAQEAAgP8/wA7
OiP7
UhlkZh4BAAAASQAAALsBAAAAAFoAAAAAAAEAAgAC/wABAAAABnJxdGFuYwACAAAAB3JxdGFuYzEA
AwAAAAdycXRhbmMzWeEi3Q==
'
--------------

--------------
COMMIT
--------------

--------------
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'
--------------

--------------
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
--------------

--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
--------------

5.6、删除二进制日志文件

MySQL二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制的方法。如：PURGE MASTER LOGS 只删除指定部分的二进制日志文件，RESET MASTER 删除所有的二进制日志文件

  • PURGE MASTER LOGS：删除指定日志文件。语法如下所示
    • PURGE { MASTER | BINARY } LOGS TO ‘指定日志文件名’;
    • PURGE { MASTER | BINARY } LOGS BEFORE ‘指定日期’
  • RESET MASTER：删除所有二进制文件。
    • 删除后MySQL会重新创建二进制日志文件，新的文件拓展名重新从000001开始编号。（不推荐）

5.7、BinLog写入机制

事务执行过程中，先把日志写入到 binlog cache，事务提交时，再把binlog cache 写入到binlog文件中。而一个事务的binlog并不能被拆开，无论这个事务多大也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

  • 通过设置 binlog_cache_size 参数控制单个线程 binlog cache大小，如果存储内容超过了这个参数则需暂存到磁盘（Swap）中。
mysql> show variables like  '%binlog_cache_size%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 32768                |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
2 rows in set (1.88 sec)
  • binlog日志刷盘流程如下：
    在这里插入图片描述
  • write 和 fsync的时机，可以通过设置 sync_binlog 的参数控制，默认 0。如下所示：
mysql> show variables like  '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (2.38 sec)

  • sync_binlog的参数值为 0 时，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。性能得到提升，但是宕机后，page cache 中的binlog信息会丢失，如下所示：
    在这里插入图片描述

  • sync_binlog的参数值设置为 1，表示每次提交事务都会执行 fsync，就和 redo log 刷盘流程一样。或将参数值设置为N(N>1)，表示每次提交事务都write，但累积N个事务后再fsync。如图所示：

在这里插入图片描述

5.8、binlog格式设置

  • statement格式：基于SQL语句的复制（statement-based replication，SBR）
    • 优点：
      • 不需要记录每一行的变化，减少了binlog日志量，文件大小
      • 包含所有数据库更改信息，可以审核数据库的安全等情况
      • 用于实时还原，而不仅仅用于复制
      • 主从版本可以不一样，从服务器可以比主服务器版本高
    • 缺点：
      • 不是所有update语句都能被复制，尤其是包含不确定操作的时候
    • 使用以下函数的语句也无法被复制：load_file、uuid()、user()、found_rows()、system()等（除非启动时启用了–sysdate-is-now选项）
      • insert ··· select 会产生比RBR更多的行级锁
      • 复制需要进行全表扫描（where 语句中没有用到索引）的update语句时，需要比RBR请求更多行级锁
      • 对于一些复杂语句，在从服务器上的资源消耗情况更严重，而RBR模式下只会对哪个发生变化的记录产生影响。
      • 执行复杂语句出错的话会小号更多资源
      • 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
  • row模式：基于行的复制（row-based replication，RBR）
    • 优点：
      • 任何情况下都可以被复制，这对复制来说时最安全可靠的。
      • 多数情况下，从服务器上的表如果有主键的话，复制相对快一些
      • 复制以下几种语句时的行锁更少，insert ··· select、包含auto_increment字段的insert、没有附带条件或者并没有修改很多记录的update或delete语句
      • 执行insert、update、delete语句时 锁更少
      • 从服务器上采用 多线程 来执行复制成为可能
    • 缺点：
      • binlog相对SBR相对较大
      • 复杂的回滚时binlog中会包含大量的数据
      • 主服务器上执行update语句时，所有发生变化的记录都会写到binlog中，而SBR只会写一次，这会导致频繁发生binlog的并发问题
      • 无法从binlog中看到复制了什么语句
  • mixed模式： 混合模式复制（mixed-based replication，MBR）
    • 一般的语句修改使用statement格式保存binlog，如一些函数。statement无法完成主从复制的操作则采用row格式保存binlog
    • MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在statement和row模式之间选择一种。

6、中继日志（relay log）

中继日志只在主从服务器架构的从服务器上存在。从服务器为了和主服务器保持一致，要从主服务器读取二进制日志的内容，并将读取的内容写入到 本地日志文件中（即 中继日志）。
从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。
主从服务搭建完成后，中继日志默认保存在从服务器的数据目录下，文件格式为： 从服务器名 -relay-bin.序号。索引文件为：-relay-bin.index，用来定位当前正在使用的中继日志。

6.1、查看中继日志

  • 中继日志与二进制日志格式相同，使用 mysqlbinlog 工具进行查看
mysql> show variables like 'relay_log%';
+---------------------------+------------------------------------------+
| Variable_name             | Value                                    |
+---------------------------+------------------------------------------+
| relay_log                 | localhost-relay-bin                      |
| relay_log_basename        | /var/lib/mysql/localhost-relay-bin       |
| relay_log_index           | /var/lib/mysql/localhost-relay-bin.index |
| relay_log_info_file       | relay-log.info                           |
| relay_log_info_repository | TABLE                                    |
| relay_log_purge           | ON                                       |
| relay_log_recovery        | OFF                                      |
| relay_log_space_limit     | 0                                        |
+---------------------------+------------------------------------------+
8 rows in set (0.18 sec)

6.2、恢复典型错误

如果服务器宕机，有时为了系统恢复需要重新安装操作系统，可能会导致 服务器名称 与之前 并不一致。而中继日志是包含从服务器名称的。
在上述情况下，可能导致恢复从服务器的时候，无法从宕机前的中继日志里读取数据。
解决方案： 将从服务器名称 修改为 以前的名称

我姓谭却弹不死你
关注
  • 27
    点赞
  • 踩
  • 25
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql关闭bin-log_mysql关闭与删除bin-log日志详解
weixin_31301881的博客
01-19 2840
mysql中如果我们开启bin-log日志全在mysql目录发现大量的mysql-bin.000001,mysql-bin.000002等这些文章，如果多了会发现占很大的空间，下面我来介绍mysql-bin日志关闭与删除方法。关闭bin-log日志当开启bin-log日志时，会生成很多mysql-bin.0000*类似的文件，而且可能会占用很大的硬盘空间。对于自己的测试机器或硬盘比较紧张的vps，...
audit-plugin-mysql-5.7-1.1.7 for Linux
06-12
mysql 5.7安全审计插件 Linux X86-X64通用版本 audit-plugin-mysql-5.7-1.1.7 for Linux 等保开启审计插件,貌似官网找不到,发出来...缺点：日志信息比较大，对性能影响大。 优点：对每一时刻每一用户的操作都有记录。
MYSQL进阶-mysql日志-查询日志
weixin_42784569的博客
01-12 2685
msql查询日志
Mysql - 日志
lpfstudy的博客
04-25 3523
重做日志（redo log）是数据库系统中一种记录数据修改操作的日志，它记录了所有的事务提交操作，以便在数据库发生故障时，能够恢复数据库的一致性。当数据库崩溃或者发生意外关机等异常情况时，通过重做日志可以恢复数据库的数据。回滚日志（undo log）是一种记录数据修改操作的日志，它记录了事务执行中所有的变更操作，以便在事务回滚时能够撤销这些操作。当事务执行失败或者被回滚时，通过回滚日志可以将事务执行前的数据状态恢复。回滚日志也可以用于实现多版本并发控制（MVCC）等功能。
Mysql-日志介绍 && 日志配置
qq_43259860的博客
01-26 2424
binlog记录了数据库所有的ddl语句和dml语句，但不包括select和show语句内容因为不是更新操作（这些日志由General query log 来记录），语句以事件的形式保存，描述了数据的变更顺序，binlog还包括每个更新语句的执行时间信息。general query log 默认关闭，由于通用查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响MySQL的性能的。log_warnings 为0， 表示不记录告警信息。
Mysql-慢日志详解
Rain's blog
05-30 4264
Mysql-慢日志详解 mysql慢日志是什么？ 慢查询日志由 long_query_time 执行时间超过几秒钟并且至少 min_examined_row_limit 需要检查行的 SQL 语句组成 。慢查询日志可用于查找需要很长时间执行的查询，因此可以进行优化。但是，检查长而缓慢的查询日志可能是一项耗时的任务； mysql long_query_time: 默认值是 10 秒 ； 查看慢日志是否开启？ 登录mysql终端查看慢日志是否开启：show variables like '%quer%';
Mysql 开启bin-log日志
qq_41737353的博客
09-01 2404
Mysql 开启bin-log日志
MYSQL--- 日志类相关问题
刘小雨的博客
06-16 3303
常用的mysql 日志有哪些？我们在什么情况下使用这些日志？ 如何通过日志来审计用户活动？知识点： 【注】 bin_log： 主服务器上必须有； relay_log: 可看成主服务器上的二进制日志在从服务器上的副本；......
【Mysql】《Mysql的日志文件mysql-bin占满了空间》处理方法
11-24 933
可以看到除了data目录是数据的存放位置，最大的是我们的log文件，进入log目录下查看里面的内容，发现是mysql的日志文件占满了空间。如上图，我们的mydata目录满了，该目录是mysql的存放目录，需要进行处理。第二步：采用数据库连接工具，或者登录mysql，执行命令进行处理。第一步：使用命令查看那个文件夹占用空间最大。安静等待他执行完毕即可。
MySQL bin-log日志恢复数据
Lzcsfg的博客
06-10 1013
在 MySQL 中，二进制日志（Binary Log, bin-log）是用于记录所有更改数据库数据的 SQL 语句的日志文件。开启和管理二进制日志可以用于数据恢复、主从复制等场景。
mysql-binlog-connector-java：MySQL二进制日志连接器
02-26
mysql-binlog-connector-java 注意：该存储库不再维护。 我建议迁移到 。 MySQL Binary Log连接器。 最初，项目是作为的分支开始的，但最终作为完整的重写而结束。 主要差异/特征： 自动binlog文件名/位置| GTID...
mysql-connector-java-8.0.29.jar
04-27
MySQL JDBC驱动包
mysql-binlog-connector-java-0.21.0.jar
08-15
sqlserver日志分析jar包
mysql清除log-bin日志的方法
09-10
MySQL的`log-bin`日志，也称为二进制日志（Binary Log），是数据库管理系统中用于记录所有改变数据库状态的非DDL（Data Definition Language）操作的重要组件。它记录了数据库中的INSERT、UPDATE、DELETE等操作，以...
MySQL-索引：聚集索引、覆盖索引、组合索引、前缀索引、唯一索引
睡不醒的每天
06-25 596
这意味着表中的行是按id的升序物理存储的，并且每个叶子节点包含了完整的行数据（包括username, email, created_at）。当你执行如下查询时，同样以上述的idx_username_email为例，它是一个基于username和email两个列创建的组合索引。在users表中，id列已经是一个主键，自动带有唯一约束。不仅包括查询列，还包括结果集中所需的其他列（如在SELECT列表中的列）。，无需回表获取其他列数据，这就是一个覆盖索引的例子。在数据库中，聚集索引决定了表数据行的物理存储顺序。
MySQL中ALTER LOGFILE GROUP 语句详解
u011565038的博客
06-25 694
在 MySQL 的 InnoDB 存储引擎中，ALTER LOGFILE GROUP语句用于修改重做日志组（redo log group）的配置。重做日志是 InnoDB 用来保证事务持久性的一个关键组件，它们用于在系统崩溃后恢复数据。
MySQL 5.x和8.0区别
qq984676583的博客
06-25 388
在8.0版本中，索引可以被“隐藏”和“显示”。：8.0大幅改进了对JSON的支持，添加了其基于路径查询参数从JSON字段中抽取数据的JSON_EXTRACT()函数，以及用于数据分别组合到JSON数据和对象中的JSON_ARRAYAGG()和JSON_OBJECTAGG()聚合函数。：InnoDB现在支持表DDL的原子性，也就是InnoDB表上的DDL也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现DDL时部分成功的问题，此外还支持crash-safe特性，元数据存储在单个事务数据字典中。
2024最新！将mysql的数据导入到Solr
陆漫漫的博客
06-27 848
并添加如下内容，这里需要注意，下面的配置是你所在的数据位置连接密码和账号，不要直接复制过去不更改。solr-8.11.3\server\solr\configsets_default下的conf文件夹复制到我们创建的core路径下面。我准备相关资料时候，在许多教程中都涉及到需要先将solr的jar包放到web项目下去。移动到solr-8.11.3\server\solr-webapp\webapp\WEB-INF\lib。当我们将上面步骤完成后，我们这个时候需要将Solr项目进行重启。
44 mysql batch insert 的实现
最新发布
970655147的专栏
06-28 241
执行 sql 如下 “insert into tz_test_02 (`field1`, `field2`) values ("你好0", "没意思0"), ("你好1", "没意思1"), ("你好2", "没意思2"), ("你好3", "没意思3"), ("你好4", "没意思4"), ("你好5", "没意思5"), ("你好6", "没意思6"), ("你好7", "没意思7"), ("你好8", "没意思8"), ("你好9", "没意思9");tz_test_02 的数据列表拷贝如下。
mysql 通过bin-log日志恢复数据
05-26
MySQL的binlog日志是用来记录数据库操作的二进制日志，可以用来进行数据恢复。下面是通过binlog日志进行数据恢复的步骤： 1. 确认要恢复的时间点 首先需要确认要恢复的时间点，即要恢复到哪个时间点的数据。可以通过查看binlog日志文件来确定最后一次备份的时间点以及之后的操作。 2. 导出binlog日志 使用mysqlbinlog命令可以导出指定时间范围内的binlog日志。例如： ``` mysqlbinlog --start-datetime="2022-01-01 00:00:00" --stop-datetime="2022-01-02 00:00:00" /var/lib/mysql/binlog.000001 > /tmp/mysqlbinlog.sql ``` 上面的命令导出了2022年1月1日0点到1月2日0点之间的binlog日志，并将结果保存到了/tmp/mysqlbinlog.sql文件中。 3. 创建新的数据库实例 在另一台机器上或者在同一台机器上使用不同的端口创建一个新的MySQL实例，用于恢复数据。 4. 导入数据 将导出的binlog日志文件导入到新的MySQL实例中，可以使用以下命令： ``` mysql -u username -p < /tmp/mysqlbinlog.sql ``` 5. 验证数据 完成数据导入后，需要验证数据是否正确恢复。可以连接到新的MySQL实例，查询相关数据是否正确。 注意：在使用binlog日志恢复数据时，需要保证binlog日志文件没有被清除或者过期，否则可能无法完全恢复数据。同时，在恢复数据之前需要备份好数据，以免因操作失误导致数据丢失。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值