暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

mysql8.0.x清理binlog日志

原创 jieguo 2024-01-23
174

磁盘空间紧张,需要清理binlog日志腾出空间。
1.修改保留时间为1天
2.清理指定时间的binlog日志

操作记录参考:

[root@lnpg mysql_data]# df -hT
文件系统                类型      容量  已用  可用 已用% 挂载点
devtmpfs                devtmpfs  7.9G     0  7.9G    0% /dev
tmpfs                   tmpfs     7.9G  5.5M  7.9G    1% /dev/shm
tmpfs                   tmpfs     7.9G   42M  7.8G    1% /run
tmpfs                   tmpfs     7.9G     0  7.9G    0% /sys/fs/cgroup
/dev/mapper/centos-root xfs        50G   19G   32G   38% /
/dev/sda1               xfs      1014M  264M  751M   26% /boot
/dev/mapper/centos-home xfs       2.0T  1.8T  285G   87% /db
tmpfs                   tmpfs     1.6G     0  1.6G    0% /run/user/0

[root@lnpg mysql_data]# mysql -uroot -ppwdxxxxx -h192.168.207.143 -A -D mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 253
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| log_bin                                | ON                                     |
| log_bin_basename                       | /db/mysql_data/binlog                  |
| log_bin_index                          | /db/mysql_data/binlog.index            |
| log_bin_trust_function_creators        | OFF                                    |
| log_bin_use_v1_row_events              | OFF                                    |
| log_error                              | /var/log/mysqld.log                    |
| log_error_services                     | log_filter_internal; log_sink_internal |
| log_error_suppression_list             |                                        |
| log_error_verbosity                    | 2                                      |
| log_output                             | FILE                                   |
| log_queries_not_using_indexes          | OFF                                    |
| log_raw                                | OFF                                    |
| log_replica_updates                    | ON                                     |
| log_slave_updates                      | ON                                     |
| log_slow_admin_statements              | OFF                                    |
| log_slow_extra                         | OFF                                    |
| log_slow_replica_statements            | OFF                                    |
| log_slow_slave_statements              | OFF                                    |
| log_statements_unsafe_for_binlog       | ON                                     |
| log_throttle_queries_not_using_indexes | 0                                      |
| log_timestamps                         | UTC                                    |
+----------------------------------------+----------------------------------------+
21 rows in set (3.05 sec)

mysql> show binary logs;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000004 |        180 | No        |
| binlog.000005 |     579310 | No        |
| binlog.000006 |        180 | No        |
| binlog.000007 | 1073973950 | No        |
| binlog.000008 | 1073947085 | No        |
| binlog.000009 | 1073818339 | No        |
| binlog.000010 | 1073800015 | No        |
| binlog.000011 | 1074184386 | No        |
| binlog.000012 | 1073760598 | No        |
| binlog.000013 | 1073909042 | No        |
| binlog.000014 | 1074038896 | No        |
| binlog.000015 | 1073913217 | No        |
| binlog.000016 | 1073841097 | No        |
| binlog.000017 | 1073874992 | No        |
| binlog.000018 | 1073821676 | No        |
| binlog.000019 | 1073860343 | No        |
| binlog.000020 | 1074006607 | No        |
| binlog.000021 | 1074039559 | No        |
| binlog.000022 | 1073870047 | No        |
| binlog.000023 | 1074001224 | No        |
| binlog.000024 | 1073798016 | No        |
| binlog.000025 | 1074002045 | No        |
| binlog.000026 | 1073842234 | No        |
| binlog.000027 | 1073744309 | No        |
| binlog.000028 | 1074160922 | No        |
| binlog.000029 | 1073991972 | No        |
| binlog.000030 | 1073857172 | No        |
| binlog.000031 | 1074004161 | No        |
| binlog.000032 | 1073955659 | No        |
| binlog.000033 | 1074176022 | No        |
| binlog.000034 | 1073889195 | No        |
| binlog.000035 | 1073816817 | No        |
| binlog.000036 | 1074232040 | No        |
| binlog.000037 | 1073792627 | No        |
| binlog.000038 | 1074251009 | No        |
| binlog.000039 | 1074049676 | No        |
| binlog.000040 | 1074154543 | No        |
| binlog.000041 | 1073967061 | No        |
| binlog.000042 | 1074020033 | No        |
| binlog.000043 | 1074128060 | No        |
| binlog.000044 | 1073749417 | No        |
| binlog.000045 | 1073874642 | No        |
| binlog.000046 | 1073863340 | No        |
| binlog.000047 | 1073946252 | No        |
| binlog.000048 | 1073899132 | No        |
| binlog.000049 | 1074165909 | No        |
| binlog.000050 | 1074025902 | No        |
| binlog.000051 | 1073742689 | No        |
| binlog.000052 | 1073880656 | No        |
| binlog.000053 | 1073744239 | No        |
| binlog.000054 | 1073956885 | No        |
| binlog.000055 | 1073989820 | No        |
| binlog.000056 | 1073810389 | No        |
| binlog.000057 | 1074092009 | No        |
| binlog.000058 | 1073833212 | No        |
| binlog.000059 | 1074127051 | No        |
| binlog.000060 | 1074407058 | No        |
| binlog.000061 | 1073871454 | No        |
| binlog.000062 | 1073873388 | No        |
| binlog.000063 | 1074333492 | No        |
| binlog.000064 | 1073996767 | No        |
| binlog.000065 | 1073846781 | No        |
| binlog.000066 | 1073916079 | No        |
| binlog.000067 | 1073911128 | No        |
| binlog.000068 | 1073880354 | No        |
| binlog.000069 | 1073889334 | No        |
| binlog.000070 | 1073974620 | No        |
| binlog.000071 | 1073855701 | No        |
| binlog.000072 | 1073908243 | No        |
| binlog.000073 | 1074013672 | No        |
| binlog.000074 | 1074279402 | No        |
| binlog.000075 | 1073968407 | No        |
| binlog.000076 | 1073900881 | No        |
| binlog.000077 | 1073802096 | No        |
| binlog.000078 | 1073824330 | No        |
| binlog.000079 | 1074284890 | No        |
| binlog.000080 | 1073845308 | No        |
| binlog.000081 | 1073814335 | No        |
| binlog.000082 | 1073760393 | No        |
| binlog.000083 | 1074128904 | No        |
| binlog.000084 | 1074118821 | No        |
| binlog.000085 | 1073789029 | No        |
| binlog.000086 | 1073819849 | No        |
| binlog.000087 | 1073835075 | No        |
| binlog.000088 | 1074205084 | No        |
| binlog.000089 | 1073787519 | No        |
| binlog.000090 | 1073843860 | No        |
| binlog.000091 | 1074002741 | No        |
| binlog.000092 | 1073953295 | No        |
| binlog.000093 | 1073775871 | No        |
| binlog.000094 | 1073971659 | No        |
| binlog.000095 | 1073785191 | No        |
| binlog.000096 | 1073774641 | No        |
| binlog.000097 | 1074061993 | No        |
| binlog.000098 | 1073938426 | No        |
| binlog.000099 | 1073895605 | No        |
| binlog.000100 | 1073897220 | No        |
| binlog.000101 | 1073943405 | No        |
| binlog.000102 | 1073890807 | No        |
| binlog.000103 | 1073765362 | No        |
| binlog.000104 | 1073908260 | No        |
| binlog.000105 | 1073924760 | No        |
| binlog.000106 | 1074031607 | No        |
| binlog.000107 | 1073885807 | No        |
| binlog.000108 | 1073869935 | No        |
| binlog.000109 | 1073905998 | No        |
| binlog.000110 | 1073768903 | No        |
| binlog.000111 | 1073780330 | No        |
| binlog.000112 | 1073821234 | No        |
| binlog.000113 | 1073788654 | No        |
| binlog.000114 | 1073903145 | No        |
| binlog.000115 | 1073759742 | No        |
| binlog.000116 | 1073864994 | No        |
| binlog.000117 | 1073750829 | No        |
| binlog.000118 | 1073742621 | No        |
| binlog.000119 | 1074188059 | No        |
| binlog.000120 | 1073809617 | No        |
| binlog.000121 | 1074199200 | No        |
| binlog.000122 | 1073750276 | No        |
| binlog.000123 | 1073761396 | No        |
| binlog.000124 | 1087674665 | No        |
| binlog.000125 | 1093104833 | No        |
| binlog.000126 | 1073801052 | No        |
| binlog.000127 | 1073784017 | No        |
| binlog.000128 | 1094595872 | No        |
| binlog.000129 | 1091312074 | No        |
| binlog.000130 | 1073799932 | No        |
| binlog.000131 | 1073766404 | No        |
| binlog.000132 | 1073836576 | No        |
| binlog.000133 | 1074153445 | No        |
| binlog.000134 | 1073916835 | No        |
| binlog.000135 | 1073991596 | No        |
| binlog.000136 | 1073843153 | No        |
| binlog.000137 | 1074393041 | No        |
| binlog.000138 | 1074137454 | No        |
| binlog.000139 | 1074219507 | No        |
| binlog.000140 | 1073858902 | No        |
| binlog.000141 | 1074272712 | No        |
| binlog.000142 | 1073749158 | No        |
| binlog.000143 | 1073920956 | No        |
| binlog.000144 | 1074377929 | No        |
| binlog.000145 | 1073790505 | No        |
| binlog.000146 | 1073829834 | No        |
| binlog.000147 | 1073760891 | No        |
| binlog.000148 | 1073846464 | No        |
| binlog.000149 | 1073790773 | No        |
| binlog.000150 | 1073857034 | No        |
| binlog.000151 | 1073770549 | No        |
| binlog.000152 | 1073866757 | No        |
| binlog.000153 | 1073808696 | No        |
| binlog.000154 | 1074099402 | No        |
| binlog.000155 | 1074068449 | No        |
| binlog.000156 | 1074025955 | No        |
| binlog.000157 | 1073776453 | No        |
| binlog.000158 | 1074012516 | No        |
| binlog.000159 | 1073963950 | No        |
| binlog.000160 | 1073857432 | No        |
| binlog.000161 | 1073856072 | No        |
| binlog.000162 | 1073832076 | No        |
| binlog.000163 | 1073960674 | No        |
| binlog.000164 | 1073793977 | No        |
| binlog.000165 | 1073885185 | No        |
| binlog.000166 | 1073749421 | No        |
| binlog.000167 | 1073918058 | No        |
| binlog.000168 | 1074038469 | No        |
| binlog.000169 | 1074050567 | No        |
| binlog.000170 | 1073758579 | No        |
| binlog.000171 | 1073894286 | No        |
| binlog.000172 | 1074081587 | No        |
| binlog.000173 | 1073816889 | No        |
| binlog.000174 | 1073744066 | No        |
| binlog.000175 | 1073802160 | No        |
| binlog.000176 | 1074083754 | No        |
| binlog.000177 | 1073811625 | No        |
| binlog.000178 | 1073885177 | No        |
| binlog.000179 | 1074055192 | No        |
| binlog.000180 | 1073995734 | No        |
| binlog.000181 | 1074122600 | No        |
| binlog.000182 | 1073978101 | No        |
| binlog.000183 | 1073898541 | No        |
| binlog.000184 | 1073919440 | No        |
| binlog.000185 | 1073817356 | No        |
| binlog.000186 | 1073797888 | No        |
| binlog.000187 | 1073778848 | No        |
| binlog.000188 | 1073948589 | No        |
| binlog.000189 | 1073885393 | No        |
| binlog.000190 | 1073906702 | No        |
| binlog.000191 | 1073821883 | No        |
| binlog.000192 | 1073805713 | No        |
| binlog.000193 | 1073901970 | No        |
| binlog.000194 | 1073922136 | No        |
| binlog.000195 | 1073900465 | No        |
| binlog.000196 | 1073821761 | No        |
| binlog.000197 | 1073744213 | No        |
| binlog.000198 | 1073891555 | No        |
| binlog.000199 | 1073780618 | No        |
| binlog.000200 | 1073965654 | No        |
| binlog.000201 | 1073910071 | No        |
| binlog.000202 | 1073829274 | No        |
| binlog.000203 | 1073824315 | No        |
| binlog.000204 | 1073788455 | No        |
| binlog.000205 | 1073961847 | No        |
| binlog.000206 | 1073872491 | No        |
| binlog.000207 | 1073848167 | No        |
| binlog.000208 | 1073942976 | No        |
| binlog.000209 | 1073868994 | No        |
| binlog.000210 | 1073810385 | No        |
| binlog.000211 | 1073804405 | No        |
| binlog.000212 | 1073912363 | No        |
| binlog.000213 | 1074077390 | No        |
| binlog.000214 | 1073756644 | No        |
| binlog.000215 | 1073921261 | No        |
| binlog.000216 | 1073809393 | No        |
| binlog.000217 | 1073945674 | No        |
| binlog.000218 | 1073812055 | No        |
| binlog.000219 | 1073935199 | No        |
| binlog.000220 | 1073798503 | No        |
| binlog.000221 | 1073745486 | No        |
| binlog.000222 | 1073961254 | No        |
| binlog.000223 | 1073788546 | No        |
| binlog.000224 | 1073945176 | No        |
| binlog.000225 | 1073908985 | No        |
| binlog.000226 | 1073781553 | No        |
| binlog.000227 | 1073765222 | No        |
| binlog.000228 | 1073756658 | No        |
| binlog.000229 | 1073934617 | No        |
| binlog.000230 | 1073909295 | No        |
| binlog.000231 | 1073812114 | No        |
| binlog.000232 | 1073829811 | No        |
| binlog.000233 | 1073748065 | No        |
| binlog.000234 | 1073758104 | No        |
| binlog.000235 | 1073877351 | No        |
| binlog.000236 | 1073845194 | No        |
| binlog.000237 | 1073836134 | No        |
| binlog.000238 | 1073785412 | No        |
| binlog.000239 | 1073913638 | No        |
| binlog.000240 | 1073803816 | No        |
| binlog.000241 | 1073828072 | No        |
| binlog.000242 | 1073941573 | No        |
| binlog.000243 | 1073833042 | No        |
| binlog.000244 | 1073815328 | No        |
| binlog.000245 | 1073834179 | No        |
| binlog.000246 | 1073995217 | No        |
| binlog.000247 | 1073780083 | No        |
| binlog.000248 | 1073959519 | No        |
| binlog.000249 | 1073810798 | No        |
| binlog.000250 | 1073806802 | No        |
| binlog.000251 | 1073759625 | No        |
| binlog.000252 | 1073944545 | No        |
| binlog.000253 | 1073944776 | No        |
| binlog.000254 | 1073959771 | No        |
| binlog.000255 | 1073990906 | No        |
| binlog.000256 | 1073756094 | No        |
| binlog.000257 | 1073875780 | No        |
| binlog.000258 | 1073978551 | No        |
| binlog.000259 | 1073890810 | No        |
| binlog.000260 | 1073898594 | No        |
| binlog.000261 | 1073752738 | No        |
| binlog.000262 | 1073826288 | No        |
| binlog.000263 | 1073771345 | No        |
| binlog.000264 | 1073975381 | No        |
| binlog.000265 | 1073785886 | No        |
| binlog.000266 | 1073907808 | No        |
| binlog.000267 | 1073787166 | No        |
| binlog.000268 | 1073756287 | No        |
| binlog.000269 | 1074035455 | No        |
| binlog.000270 | 1073768689 | No        |
| binlog.000271 | 1073747944 | No        |
| binlog.000272 | 1073988813 | No        |
| binlog.000273 | 1073981220 | No        |
| binlog.000274 | 1073812350 | No        |
| binlog.000275 | 1073870065 | No        |
| binlog.000276 | 1073746787 | No        |
| binlog.000277 | 1073905342 | No        |
| binlog.000278 | 1073743448 | No        |
| binlog.000279 | 1073968885 | No        |
| binlog.000280 | 1074011889 | No        |
| binlog.000281 | 1073771418 | No        |
| binlog.000282 | 1073746871 | No        |
| binlog.000283 | 1073849563 | No        |
| binlog.000284 | 1073744280 | No        |
| binlog.000285 | 1074014365 | No        |
| binlog.000286 | 1073784314 | No        |
| binlog.000287 | 1073811199 | No        |
| binlog.000288 | 1073951096 | No        |
| binlog.000289 | 1073992587 | No        |
| binlog.000290 | 1073804840 | No        |
| binlog.000291 | 1073794607 | No        |
| binlog.000292 | 1073752474 | No        |
| binlog.000293 | 1073772131 | No        |
| binlog.000294 | 1073960809 | No        |
| binlog.000295 | 1073873173 | No        |
| binlog.000296 | 1073855145 | No        |
| binlog.000297 | 1073887607 | No        |
| binlog.000298 | 1073907979 | No        |
| binlog.000299 | 1073799610 | No        |
| binlog.000300 | 1073807497 | No        |
| binlog.000301 | 1073949777 | No        |
| binlog.000302 | 1073792731 | No        |
| binlog.000303 | 1073831366 | No        |
| binlog.000304 | 1073914902 | No        |
| binlog.000305 | 1073742644 | No        |
| binlog.000306 | 1073842191 | No        |
| binlog.000307 | 1073811498 | No        |
| binlog.000308 | 1073953498 | No        |
| binlog.000309 | 1073771522 | No        |
| binlog.000310 | 1073782914 | No        |
| binlog.000311 | 1073802332 | No        |
| binlog.000312 | 1073753487 | No        |
| binlog.000313 | 1073891858 | No        |
| binlog.000314 | 1073859269 | No        |
| binlog.000315 | 1073878545 | No        |
| binlog.000316 | 1073748236 | No        |
| binlog.000317 | 1073864038 | No        |
| binlog.000318 | 1073872859 | No        |
| binlog.000319 | 1073763625 | No        |
| binlog.000320 | 1073801654 | No        |
| binlog.000321 | 1073890496 | No        |
| binlog.000322 | 1073894175 | No        |
| binlog.000323 | 1073856365 | No        |
| binlog.000324 | 1073888529 | No        |
| binlog.000325 | 1073937399 | No        |
| binlog.000326 | 1073831814 | No        |
| binlog.000327 | 1073763071 | No        |
| binlog.000328 | 1073761146 | No        |
| binlog.000329 | 1073788622 | No        |
| binlog.000330 | 1073995451 | No        |
| binlog.000331 | 1073872363 | No        |
| binlog.000332 | 1073889317 | No        |
| binlog.000333 | 1073875456 | No        |
| binlog.000334 | 1074009128 | No        |
| binlog.000335 | 1073869992 | No        |
| binlog.000336 | 1074119306 | No        |
| binlog.000337 | 1073865865 | No        |
| binlog.000338 | 1074146787 | No        |
| binlog.000339 | 1073863060 | No        |
| binlog.000340 | 1073858435 | No        |
| binlog.000341 | 1073796632 | No        |
| binlog.000342 | 1073791206 | No        |
| binlog.000343 | 1073817163 | No        |
| binlog.000344 | 1073870372 | No        |
| binlog.000345 | 1074061181 | No        |
| binlog.000346 | 1074047988 | No        |
| binlog.000347 | 1074147551 | No        |
| binlog.000348 | 1074054176 | No        |
| binlog.000349 | 1073766631 | No        |
| binlog.000350 | 1074022101 | No        |
| binlog.000351 | 1073789733 | No        |
| binlog.000352 | 1073866233 | No        |
| binlog.000353 | 1073877467 | No        |
| binlog.000354 | 1073971966 | No        |
| binlog.000355 | 1073759599 | No        |
| binlog.000356 | 1074083250 | No        |
| binlog.000357 | 1074001799 | No        |
| binlog.000358 | 1073816756 | No        |
| binlog.000359 | 1073779999 | No        |
| binlog.000360 | 1074009911 | No        |
| binlog.000361 | 1073986982 | No        |
| binlog.000362 | 1074079255 | No        |
| binlog.000363 | 1074075820 | No        |
| binlog.000364 | 1073830311 | No        |
| binlog.000365 | 1074025846 | No        |
| binlog.000366 |   70448306 | No        |
+---------------+------------+-----------+
363 rows in set (2.38 sec)

mysql> show variables like '%expire%';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| binlog_expire_logs_auto_purge  | ON      |
| binlog_expire_logs_seconds     | 2592000 |
| disconnect_on_expired_password | ON      |
| expire_logs_days               | 0       |
+--------------------------------+---------+
4 rows in set (0.01 sec)

mysql> set global binlog_expire_logs_seconds=60*60*24;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| binlog_expire_logs_auto_purge  | ON    |
| binlog_expire_logs_seconds     | 86400 |
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 0     |
+--------------------------------+-------+
4 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.35 sec)

mysql> show binary logs;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000336 | 1074119306 | No        |
| binlog.000337 | 1073865865 | No        |
| binlog.000338 | 1074146787 | No        |
| binlog.000339 | 1073863060 | No        |
| binlog.000340 | 1073858435 | No        |
| binlog.000341 | 1073796632 | No        |
| binlog.000342 | 1073791206 | No        |
| binlog.000343 | 1073817163 | No        |
| binlog.000344 | 1073870372 | No        |
| binlog.000345 | 1074061181 | No        |
| binlog.000346 | 1074047988 | No        |
| binlog.000347 | 1074147551 | No        |
| binlog.000348 | 1074054176 | No        |
| binlog.000349 | 1073766631 | No        |
| binlog.000350 | 1074022101 | No        |
| binlog.000351 | 1073789733 | No        |
| binlog.000352 | 1073866233 | No        |
| binlog.000353 | 1073877467 | No        |
| binlog.000354 | 1073971966 | No        |
| binlog.000355 | 1073759599 | No        |
| binlog.000356 | 1074083250 | No        |
| binlog.000357 | 1074001799 | No        |
| binlog.000358 | 1073816756 | No        |
| binlog.000359 | 1073779999 | No        |
| binlog.000360 | 1074009911 | No        |
| binlog.000361 | 1073986982 | No        |
| binlog.000362 | 1074079255 | No        |
| binlog.000363 | 1074075820 | No        |
| binlog.000364 | 1073830311 | No        |
| binlog.000365 | 1074025846 | No        |
| binlog.000366 |   85594712 | No        |
| binlog.000367 |    1575547 | No        |
+---------------+------------+-----------+
32 rows in set (0.00 sec)

mysql> PURGE BINARY LOGS BEFORE '2024-01-23 08:00:00';
Query OK, 0 rows affected (0.06 sec)

mysql> show binary logs;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000364 | 1073830311 | No        |
| binlog.000365 | 1074025846 | No        |
| binlog.000366 |   85594712 | No        |
| binlog.000367 |   15192483 | No        |
+---------------+------------+-----------+
4 rows in set (0.00 sec)

mysql> exit
Bye
[root@lnpg mysql_data]# df -hT
文件系统                类型      容量  已用  可用 已用% 挂载点
devtmpfs                devtmpfs  7.9G     0  7.9G    0% /dev
tmpfs                   tmpfs     7.9G  5.5M  7.9G    1% /dev/shm
tmpfs                   tmpfs     7.9G   42M  7.8G    1% /run
tmpfs                   tmpfs     7.9G     0  7.9G    0% /sys/fs/cgroup
/dev/mapper/centos-root xfs        50G   19G   32G   38% /
/dev/sda1               xfs      1014M  264M  751M   26% /boot
/dev/mapper/centos-home xfs       2.0T  1.4T  642G   69% /db
tmpfs                   tmpfs     1.6G     0  1.6G    0% /run/user/0
overlay                 overlay   2.0T  1.4T  642G   69% 
[root@lnpg mysql_data]# 
腾出了300多G空间

清理binlog的常用参考命令:

1.登录mysql数据库,mysql -uroot –p

2.查看binlog是否开启,log_bin的状态为ON,表明binlog日志开启
show variables like 'log_%';

3.查看binlog日志文件大小,单位bytes
show binary logs;

4.删除binlog日志文件,切勿删除正在使用的binlog
所以删除旧的即可,不要全删。
不要轻易手动去删除binlog,
会导致binlog.index和真实存在的binlog不匹配,
使用PURGE,根据自己需求改写下面命令
//删除1天前的binlog日志文件
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
//将指定时间之前的binlog清掉
PURGE BINARY LOGS BEFORE '2023-01-23 09:00:00';
//将bin.000088之前的binlog清掉:
PURGE BINARY LOGS TO 'bin.000088';

5.设置binlog的过期时间 
(5-1)mysql 8.0版本
binlog失效日期参数为 binlog_expire_logs_seconds,
默认2592000(30天)过期,可以设置 604800(7天)
(5-2)mysql 5.7版本
expire_logs_days 默认值为0,表示从不删除。  

//显示binlog的过期时间
show variables like '%expire%';
//设置过期时间为1天
set global binlog_expire_logs_seconds=60*60*24;
//手动刷新日志
flush logs;
或
flush binary logs;

相关参考:https://blog.csdn.net/qq_20466211/article/details/130585591

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论