主页 / server / 问题 / 1171144
Accepted
Dmitriano
Asked: 2025-01-20 07:52:45 +0800 CST

MySQL8 占用大量磁盘空间

  • 772

我将 11 个数据库中的 3 个从 Ubuntu 16.04 上的 MySQL 5.7.44 迁移到了 Ubuntu 24.04 上的 MySQL 8.0.40。

我的旧机器上使用的空间：

root@localhost:/var/lib/mysql# du -ha --max-depth=1
19M     ./bot
4.0K    ./private_key.pem
1.1M    ./performance_schema
48K     ./game
79M     ./master
4.0K    ./public_key.pem
4.0K    ./client-key.pem
4.0K    ./auto.cnf
5.0G    ./beauty
0       ./debian-5.7.flag
201M    ./devnote
11M     ./mysql
4.0K    ./ca.pem
8.0K    ./test
12M     ./mike1
12M     ./ibtmp1
176K    ./phpmyadmin
4.0K    ./server-cert.pem
680K    ./sys
48M     ./ib_logfile0
4.0K    ./ca-key.pem
127M    ./mike
4.0K    ./mysql_upgrade_info
4.0K    ./ib_buffer_pool
48M     ./ib_logfile1
4.0K    ./server-key.pem
186M    ./slogpost
76M     ./ibdata1
4.0K    ./client-cert.pem
41M     ./shar
5.9G    .

我的新机器上使用的空间（新数据库是、、beauty）：sharslogpost

root@localhost:/var/lib/mysql# du -ha --max-depth=1
101M    ./#innodb_redo
101M    ./binlog.000161
101M    ./binlog.000069
101M    ./binlog.000008
101M    ./binlog.000095
101M    ./binlog.000082
101M    ./binlog.000047
4.0K    ./localhost.pid
4.0K    ./public_key.pem
101M    ./binlog.000075
36K     ./mysql
101M    ./binlog.000177
101M    ./binlog.000101
101M    ./binlog.000122
4.0K    ./server-key.pem
101M    ./binlog.000107
101M    ./binlog.000158
101M    ./binlog.000079
101M    ./binlog.000057
101M    ./binlog.000028
101M    ./binlog.000173
101M    ./binlog.000020
4.0K    ./binlog.index
101M    ./binlog.000112
101M    ./binlog.000121
101M    ./binlog.000139
101M    ./binlog.000142
101M    ./binlog.000152
101M    ./binlog.000060
101M    ./binlog.000136
101M    ./binlog.000010
101M    ./binlog.000045
101M    ./binlog.000077
101M    ./binlog.000125
101M    ./binlog.000168
101M    ./binlog.000169
101M    ./binlog.000119
101M    ./binlog.000007
101M    ./binlog.000176
101M    ./binlog.000127
101M    ./binlog.000066
4.0K    ./client-key.pem
101M    ./binlog.000123
101M    ./binlog.000099
101M    ./binlog.000143
101M    ./binlog.000118
4.0K    ./private_key.pem
101M    ./binlog.000070
101M    ./binlog.000145
101M    ./binlog.000030
4.0K    ./binlog.000002
19M     ./shar
4.0K    ./auto.cnf
101M    ./binlog.000116
101M    ./binlog.000160
101M    ./binlog.000156
101M    ./binlog.000148
101M    ./binlog.000097
101M    ./binlog.000043
101M    ./binlog.000140
101M    ./binlog.000042
101M    ./binlog.000063
101M    ./binlog.000015
101M    ./binlog.000050
0       ./debian-5.7.flag
101M    ./binlog.000003
101M    ./binlog.000180
8.2M    ./#ib_16384_1.dblwr
4.0K    ./binlog.000036
101M    ./binlog.000026
12M     ./ibtmp1
101M    ./binlog.000034
1.7M    ./performance_schema
16M     ./undo_002
101M    ./binlog.000023
101M    ./binlog.000071
101M    ./binlog.000033
32K     ./binlog.000038
101M    ./binlog.000011
76K     ./binlog.000039
101M    ./binlog.000110
101M    ./binlog.000017
101M    ./binlog.000012
101M    ./binlog.000032
101M    ./binlog.000165
101M    ./binlog.000086
101M    ./binlog.000157
183M    ./slogpost
2.3G    ./binlog.000133
101M    ./binlog.000056
101M    ./binlog.000019
101M    ./binlog.000022
2.3G    ./binlog.000183
101M    ./binlog.000073
101M    ./binlog.000138
101M    ./binlog.000009
101M    ./binlog.000067
101M    ./binlog.000181
101M    ./binlog.000013
101M    ./binlog.000162
101M    ./binlog.000088
101M    ./binlog.000120
101M    ./binlog.000054
101M    ./binlog.000064
8.6M    ./binlog.000035
101M    ./binlog.000085
101M    ./binlog.000144
101M    ./binlog.000091
101M    ./binlog.000061
101M    ./binlog.000141
4.0K    ./binlog.000135
101M    ./binlog.000109
101M    ./binlog.000103
101M    ./binlog.000132
101M    ./binlog.000094
101M    ./binlog.000151
101M    ./binlog.000076
101M    ./binlog.000149
101M    ./binlog.000104
101M    ./binlog.000163
101M    ./binlog.000027
101M    ./binlog.000114
116K    ./sys
101M    ./binlog.000182
8.0K    ./ib_buffer_pool
101M    ./binlog.000115
101M    ./binlog.000164
101M    ./binlog.000084
2.4G    ./beauty
101M    ./binlog.000016
101M    ./binlog.000153
101M    ./binlog.000172
101M    ./binlog.000041
101M    ./binlog.000174
101M    ./binlog.000100
8.0K    ./binlog.000185
101M    ./binlog.000113
101M    ./binlog.000046
101M    ./binlog.000178
101M    ./binlog.000124
101M    ./binlog.000074
101M    ./binlog.000105
101M    ./binlog.000179
28M     ./mysql.ibd
101M    ./binlog.000171
101M    ./binlog.000155
101M    ./binlog.000062
101M    ./binlog.000021
101M    ./binlog.000044
101M    ./binlog.000049
101M    ./binlog.000059
101M    ./binlog.000147
60K     ./binlog.000134
101M    ./binlog.000055
4.0K    ./binlog.000040
101M    ./binlog.000126
101M    ./binlog.000170
101M    ./binlog.000089
101M    ./binlog.000006
101M    ./binlog.000080
101M    ./binlog.000081
101M    ./binlog.000068
172K    ./binlog.000037
12M     ./ibdata1
101M    ./binlog.000146
101M    ./binlog.000031
101M    ./binlog.000129
101M    ./binlog.000150
4.0K    ./binlog.000001
804K    ./#innodb_temp
101M    ./binlog.000065
75M     ./binlog.000004
4.0K    ./ca-key.pem
101M    ./binlog.000025
101M    ./binlog.000108
4.0K    ./server-cert.pem
101M    ./binlog.000090
101M    ./binlog.000051
101M    ./binlog.000048
101M    ./binlog.000052
101M    ./binlog.000024
101M    ./binlog.000053
101M    ./binlog.000130
101M    ./binlog.000014
8.0K    ./binlog.000184
101M    ./binlog.000117
101M    ./binlog.000096
101M    ./binlog.000005
101M    ./binlog.000175
101M    ./binlog.000131
101M    ./binlog.000078
101M    ./binlog.000167
101M    ./binlog.000058
101M    ./binlog.000083
101M    ./binlog.000087
101M    ./binlog.000154
101M    ./binlog.000166
101M    ./binlog.000102
4.0K    ./client-cert.pem
101M    ./binlog.000128
4.0K    ./ca.pem
101M    ./binlog.000137
101M    ./binlog.000098
16M     ./undo_001
101M    ./binlog.000029
101M    ./binlog.000018
101M    ./binlog.000092
101M    ./binlog.000106
101M    ./binlog.000072
101M    ./binlog.000111
2.3G    ./binlog.000159
192K    ./#ib_16384_0.dblwr
101M    ./binlog.000093
27G     .

du -csh binlog* | tail -n 1
24G     total

beauty（旧机器上的数据库占用5.0G，因为它包含一些额外的数据）

新机器上的 11 个数据库占用 5.9G，但旧机器上的 3 个数据库占用 27G。

为什么这么多？

要备份数据库我只需执行以下操作：

mysqldump -u $MUSER -h $MHOST -p$MPASS $MDB --no-tablespaces | sed -e 's/DEFINER=[^*]*\*/\*/' > $DBDUMP

为了恢复我这样做：

tar -Oxzf $ARCHIVE_FILE $DBDUMP | mysql -u $MUSER -h $MHOST -p$MPASS $MDB
mysql

1 个回答

  1. Best Answer
    binlog_expire_logs_seconds = 3600
binlog_expire_logs_auto_purge = ON
max_binlog_size = 1G

    将它们放入您的my.cnf，然后重新启动 MySQL。这可能会在一小时内清除大多数文件。

    然后考虑设置

    log_bin = OFF

    并重新启动。然后运行

    PURGE BINARY LOGS BEFORE NOW();

    注意sql_log_bin“控制是否为当前会话启用二进制日志记录（假设二进制日志本身已启用）”

    阅读更多： https ：//dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html#replication-sysvars-binlog

    （我希望这个答案涵盖了上述评论中缺少的细节。）

    （dba.stackexchange.com 是讨论 MySQL/MariaDB 管理的更好的地方。）

    • 1

相关问题