MySQL-相关日志

已于 2024-06-09 22:03:42 修改
阅读量866 收藏 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-connector-java-8.0.26.jar
12-18
此外，压缩包中还包含了一个名为"mysql-connector-java-8.0.26.jar.txt"的文本文件，这可能是MySQL Connector/J的使用说明、API文档或发布日志，提供了关于这个驱动的更详细信息，如如何配置、已知问题和解决办法等...
MYSQL进阶-mysql日志-查询日志
weixin_42784569的博客
01-12 2695
msql查询日志
mysql-connector-java-8.0.30连接器
10-30
MySQL Connector/J 8.0.30 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java ...同时，关注MySQL官方文档和更新日志，以了解新版本中可能引入的变化和改进，有助于确保应用的稳定性和最佳性能。
mysql-connector-java-8.0.25.jar
12-18
mysql-connector-java-8.0.25.jar.txt文件可能是包含有关驱动使用方法、配置示例或版本变更日志的文档。阅读这个文件可以帮助开发者更好地理解和使用这个驱动程序。 总的来说，MySQL Connector/J 8.0.25是Java开发...
MySQL-概述-安装配置 mysql-8.0.31-winx64
10-27
3. **日志和数据文件**：MySQL的数据文件默认位于安装目录下的data子目录，日志文件（如错误日志、慢查询日志）也在同一个目录。 4. **备份与恢复**：MySQL支持多种备份策略，如mysqldump用于逻辑备份，xtrabackup...
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 & NaviCat 安装及配置教程（Windows）【安装】
绯樱殇雪的博客
07-15 844
MySQL & NaviCat 安装及配置教程
来聊一聊MySQL InnoDB的LSN
神技圈子的博客
07-15 716
详细讲解了MySQL中的LSN作用已经实现方式，这部分对于从事数据库内核开发来说非常重要
MySQL中的内置函数
qhy850716的博客
07-15 454
一、时间函数 语法 函数 说明 current_date() 日期 current_time() 时间 current_timestamp() 时间戳 now() 当前时间 date(date_time) 取出时间中的日期 date_add(date, interval value_type) 时间相加，单位year, day, minute, second date_sub(date, interval value_type)
记录些MySQL题集（3）
安静的软件工程师
07-15 783
记录些MySQL题集（3）
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 451
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
【MySQL】根据binlog日志获取回滚sql的一个开发思路
weixin_45385457的博客
07-19 219
不同客户端之间会交替追加在 binlog 中，需要通过 👆的 binlog 匹配流程来控制匹配。行，需要再次寻找 thread_id 相同的行，匹配到后执行上一个流程。在 binlog 中的线程 ID 记录为 thread_id。一个线程执行多个 sql 回滚到同一个文件可能带来的问题。对应 binlog 日志中的 thread_id=指定 mysql 客户端 开始时间和结束时间。打开 mysql 客户端 开始时间。关闭 mysql 客户端 结束时间。先匹配 thread_id 相同的。
Mysql----内置函数
m0_74979625的博客
07-18 203
Mysql--内置函数
使用 Vue3、Node.js、MySQL、Electron 和 Express 实现用户登录、文章管理和截屏功能
liyananweb的博客
07-15 710
通过本文，我们详细介绍了如何使用 Vue3、Node.js、MySQL、Electron 和 Express 实现一个完整的用户登录、文章管理和截屏功能的应用。希望这篇文章能为你提供有价值的参考，帮助你更好地理解和实现前后端分离的应用开发。如果你有任何问题或建议，欢迎在评论区留言讨论。
mysql链接错误，unblock with ‘mysqladmin flush-hosts‘“
java之书-资源
07-16 279
message from server: "Host '172.16.39.238' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'".
MySQL基本语法规则 By 尚硅谷
最新发布
qq_62678419的博客
07-19 414
主要是一些Mysql数据库的语法
记录些MySQL题集（8）
安静的软件工程师
07-16 1114
记录些MySQL题集（8）
使用Docker 实现 MySQL 循环复制（一）
m0_60511809的博客
07-18 885
复制是 MySQL 的一项非常重要的技术，它使服务器能够将更改从一个实例复制到另一个实例，复制技术被广泛应用在水平横向扩展、商业智能和分析、地理数据分布、高可用性与容灾等关键性场景中。在某些地区或网络环境下，直接从官方 Docker Hub 下载镜像可能会出现速度慢、不稳定甚至无法连接等问题。典型的复制拓扑是主/从属服务器的一对多关系，高级复杂复制拓扑有双向复制、循环复制以及多源复制等。本次实验将在一台虚拟机中使用 Docker 容器构建三个 MySQL 循环复制拓扑。
基于SpringBoot+MySQL的租房项目+文档
欧阳小白闯天涯
07-11 1034
在当今快节奏的生活中，房屋租赁市场的需求与日俱增。为了有效管理租房流程、提升用户体验，本文基于SpringBoot+MySQL技术栈，设计并实现了一套全方位的租房系统。该系统融合了经纪人、维修员、管理员和用户四个角色，各自拥有独特的功能模块，包括房源管理、维修处理、员工用户管理、统计分析等。经纪人角色能够方便地添加房源、管理自己的房源、处理房源出租事务，并通过系统提供的月出租量统计功能进行业绩分析与优化。其次，维修员可以及时响应待处理的房源报修请求，有效地管理已处理的房源维修记录。
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、付费专栏及课程。

余额充值