1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1549 |
| mysql-bin.000002 |       905 |
| mysql-bin.000003 |      1280 |
| mysql-bin.000004 |      2442 |
| mysql-bin.000005 |      5458 |
| mysql-bin.000006 |       233 |
| mysql-bin.000007 |      4492 |
| mysql-bin.000008 | 925667205 |
| mysql-bin.000009 |  25178685 |
+------------------+-----------+

1
2
3
4
5
6

-- 删除指定日志序号之前的所有二进制日志
PURGE MASTER LOGS TO 'mysql-bin.000003';
-- 删除指定绝对日期之前的所有二进制日志
PURGE MASTER LOGS BEFORE '2020-05-01 00:00:00';
-- 删除指定相对日期之前的所有二进制日志
PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

1
2
3
4
5
6

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+

1
2

-- 设置binlog7天后自动清理
mysql> set global expire_logs_days=7;

1
2
3
4
5
6
7
8

-- 查询共享表空间ibdata1,业务表相关个文件大小（不包含redo）
SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'File_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC;

-- 查询不包含redo类日志的其他文件总大小
SELECT sum(concat(TOTAL_EXTENTS,'M')) as 'File_size' FROM INFORMATION_SCHEMA.FILES;

-- 看是否慢日志比较多,也会占用磁盘
select count(*) from mysql.slow_log;