Mysql 8 清空表和删除二进制日志文件

1. 清空表（Mysql 8）

在mysql中，有两种方式清空数据表中的数据：

• delete from 数据库.数据表;
• truncate table 数据库.数据表;

（1）delete 和 truncate 区别

从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。

• DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
• DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
• DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
• DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
• DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
• DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。

（2）执行命令

// 清空 test库中 blade_log_error 表中的所有内容

mysql> DELETE FROM test.blade_log_error;

如果数据量大的话，DELETE 速度比较慢， TRUNCATE 就比较快了

// 清空 test库中 blade_log_error 表中的所有内容

mysql> TRUNCATE TABLE test.blade_log_error;

// 远程连接的方式清空表

mysql -h192.168.10.223 -P3309 -uroot -pxxxx -e"TRUNCATE TABLE `xiodi_test`.`blade_log_api`;TRUNCATE TABLE `xiodi_test`.`blade_log_error`;TRUNCATE TABLE `xiodi_test`.`blade_log_usual`;"

2. 删除二进制日志（Mysql 8）

（1）查看二进制日志文件

// 查询二进制日志文件

mysql> SHOW BINARY LOGS;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000187 | 1073756530 |
| binlog.000188 | 1073742437 |
| binlog.000189 |   79357383 |
+---------------+------------+

// 查询当前写入的二进制日志文件

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000189 | 79357383 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

（2）删除二进制日志文件

// 根据文件删除：删除该日志文件名前面的文件

mysql> PURGE BINARY LOGS TO 'binlog.000188';
mysql> SHOW BINARY LOGS;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000188 | 1073742437 |
| binlog.000189 |   79357383 |
+---------------+------------+

// 根据日期删除：删除2天前的

mysql> PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 2 DAY);

// 根据日期删除：指定日期

mysql> PURGE BINARY LOGS BEFORE '2024-03-08 00:00:00';

// 删除所有二进制日志文件

mysql> RESET MASTER;

删除所有二进制日志后，MySQL 将会重新创建新的二进制日志，新二进制日志的编号从 000001 开始。

（3）设置自动清理二进制日志

// 查看过期时间，这里是 30 天

mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_expire_logs_seconds';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+--------+

// 临时设置为 7 天

mysql> SET GLOBAL binlog_expire_logs_seconds = 604800;

// 永久配置

$ vim /etc/mysql/my.cnf
...
[mysqld]
binlog_expire_logs_seconds = 604800
...

$ systemctl restart mysqld

在 mysql 8 之前使用 expire_logs_days 字段