mysql 二进制文件总结

2024-03-01 16:40| 来源: 网络整理| 查看: 265

二进制日志（Binary Log）也可叫作变更日志（Update Log），是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。

如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启，命令如下：

从结果可以看出，二进制日志是开启的。

启动和设置二进制日志

在 MySQL 中，可以通过在配置文件中添加 log-bin 选项来开启二进制日志，格式如下:

[mysqld] log-bin=dir/[filename]

其中，dir 参数指定二进制文件的存储路径；filename 参数指定二进制文件的文件名，其形式为filename.number，number 的形式为 000001、000002 等。

每次重启 MySQL 服务后，都会生成一个新的二进制日志文件，这些日志文件的文件名中 filename部分不会改变，number 会不断递增。

如果没有 dir 和 filename 参数，二进制日志将默认存储在数据库的数据目录下，默认的文件名为hostname-bin.number，其中 hostname 表示主机名。

案例配置如下：

log-bin=mysql-bin binlog_format=mixed server-id = 1 1.二进制文件基本操作

查看二进制文件

1. 查看二进制日志文件列表

可以使用如下命令查看 MySQL 中有哪些二进制日志文件：

mysql> show binary logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000035 | 686110 | No | | mysql-bin.000036 | 96148 | No | +------------------+-----------+-----------+ 2 rows in set (0.05 sec) 2. 查看当前正在写入的二进制日志文件

show master status；命令用来查看当前的二进制日志；

二进制日志使用二进制格式存储，不能直接打开查看。如果需要查看二进制日志，使用show binlog events in ‘mysql-bin.000001’; 命令。

mysql> show binlog events in 'mysql-bin.000036'\G; *************************** 825. row *************************** Log_name: mysql-bin.000036 Pos: 95772 Event_type: Query Server_id: 1 End_log_pos: 96117 Info: use `lms_2006_users`; insert into `lms_admin_operation_log` (`user_id`, `path`, `method`, `ip`, `input`, `updated_at`, `created_at`) values (1, 'admin/products', 'GET', '127.0.0.1', '{\"_pjax\":\"#pjax-container\"}', '2020-09-24 09:52:05', '2020-09-24 09:52:05') *************************** 826. row *************************** Log_name: mysql-bin.000036 Pos: 96117 Event_type: Xid Server_id: 1 End_log_pos: 96148 Info: COMMIT /* xid=16643 */ 826 rows in set (0.01 sec) ERROR: No query specified

如果不指定查询的二进制文件，则默认为第一个二进制文件

2.删除二进制文件

二进制日志中记录着大量的信息，如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进制日志的方法很多，下面介绍几种删除二进制日志的方法。

1. 删除所有二进制日志

使用 RESET MASTER 语句可以删除的所有二进制日志，该语句的形式如下：

RESET MASTER;

2. 根据编号删除二进制日志

每个二进制日志文件后面有一个 6 位数的编号，如 000001。使用 PURGE MASTER LOGS TO 语句，可以删除指定二进制日志的编号之前的日志。该语句的基本语法形式如下：

PURGE MASTER LOGS TO 'filename.number';

该语句将删除编号小于 filename.number 的所有二进制日志下面删除 mylog.000004 之前的二进制日志，代码如下：

PURGE MASTER LOGS TO 'mylog.000004';

代码执行完后，编号为 000001、000002 和 000003 的二进制日志将被删除。

3. 根据创建时间删除二进制日志

使用 PURGE MASTER LOGS TO 语句，可以删除指定时间之前创建的二进制日志，该语句的基本语法格式如下：

PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

其中，“hh”为 24 制的小时。该语句将删除在指定时间之前创建的所有二进制日志。

下面删除 2019-12-20 15:00:00 之前创建的二进制日志，代码如下

PURGE MASTER LOGS TO '2019-12-20 15:00:00";

代码执行完后，2019-12-20 15:00:00 之前创建的所有二进制日志将被删除。

3.使用二进制文件恢复数据环境准备

创建一个用于测试的库create database mytest;

mysql> create database mytest; Query OK, 1 row affected (0.03 sec) mysql> use mytest; Database changed mysql> show tables; Empty set (0.02 sec)

创建一个用于测试的用户表并新增数据，如下：

mysql> CREATE TABLE `user` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `username` varchar(20) DEFAULT NULL, -> `age` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected, 3 warnings (0.09 sec) mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | user | +------------------+ 1 row in set (0.02 sec) mysql> insert into `user`(username,age)values('starsky',10); Query OK, 1 row affected (0.02 sec) mysql> insert into `user`(username,age)values('ShineYork',10); Query OK, 1 row affected (0.01 sec) mysql> insert into `user`(username,age)values('Will',10); Query OK, 1 row affected (0.01 sec) mysql> 删除数据或者表 mysql> select * from user; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 1 | starsky | 10 | | 2 | ShineYork | 10 | | 3 | Will | 10 | +----+-----------+------+ 3 rows in set (0.00 sec) mysql> delete from `user` where id=1; Query OK, 1 row affected (0.02 sec) mysql> delete from `user` where id=2; Query OK, 1 row affected (0.02 sec) mysql> delete from `user` where id=3; Query OK, 1 row affected (0.02 sec) mysql> drop table `user`; Query OK, 0 rows affected (0.04 sec) mysql> drop database `mytest`; Query OK, 0 rows affected (0.06 sec) 恢复数据

通过日志文件节点恢复数据

根据节点需要我们查看日志文件提供给我们的数据库创建，表创建，数据新增等时创建的语句节点，从而恢复数据。

./bin/mysqlbinlog --start-position=96625 --stop-position=97758 /www/server/data/mysql- bin.000036 | mysql -uroot -p [root@localhost mysql]# ./bin/mysqlbinlog --start-position=96148 --stop-position=97789 /www/server/data/mysql-bin.000036 | mysql -uroot -p Enter password: [root@localhost mysql]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 453 Server version: 8.0.19 Source distribution Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mytest; Database changed mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | user | +------------------+ 1 row in set (0.05 sec) mysql> select * from `user`; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 1 | starsky | 10 | | 2 | ShineYork | 10 | | 3 | Will | 10 | +----+-----------+------+ 3 rows in set (0.01 sec) mysql> 其他恢复数据命令 //根据时间恢复数据 mysqlbinlog --start-datetime='2020-09-27 22:22:22' --stop-datetime='2020-09-27 22:30:00' /www/server/data/mysql-bin.000036 | mysql -uroot -p //直接执行binlog日志 mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p

【本文地址】

公司简介

联系我们