mysql 删除二进制日志
访问量:1432
创建时间:2020-07-15
查看磁盘剩余空间,mysql的数据磁盘/data剩余不多
[root@m ~]# df -h
文件系统 容量 已用 可用 已用% 挂载点
/dev/mapper/centos-root 46G 7.0G 39G 16% /
devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs 3.9G 404M 3.6G 11% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/sda1 1014M 166M 849M 17% /boot
/dev/mapper/vgdata-data 49G 48G 1.8G 97% /data
tmpfs 798M 12K 798M 1% /run/user/42
tmpfs 798M 0 798M 0% /run/user/0
查看目前数据库的二进制日志
[root@m data]# mysql -uroot -p
Enter password:
mysql> SHOW MASTER LOGS;
+---------------+------------+
| Log_name | File_size |
+---------------+------------+
| binlog.000005 | 2303 |
| binlog.000006 | 1605 |
| binlog.000007 | 4524753 |
| binlog.000008 | 9449281 |
| binlog.000009 | 7668370 |
| binlog.000010 | 2442073 |
| binlog.000011 | 11667489 |
| binlog.000012 | 1931232 |
| binlog.000013 | 6449739 |
| binlog.000014 | 2246926 |
| binlog.000015 | 8727256 |
| binlog.000016 | 3269763 |
| binlog.000017 | 776400 |
| binlog.000018 | 16077303 |
| binlog.000019 | 7794267 |
| binlog.000020 | 1664786 |
| binlog.000021 | 1234792 |
| binlog.000022 | 608216 |
| binlog.000023 | 65084 |
| binlog.000024 | 198 |
| binlog.000025 | 4187726 |
| binlog.000026 | 3104054 |
| binlog.000027 | 5017225 |
| binlog.000028 | 3465624 |
| binlog.000029 | 5533417 |
| binlog.000030 | 2894678 |
| binlog.000031 | 4620861 |
| binlog.000032 | 2944710 |
| binlog.000033 | 9214198 |
| binlog.000034 | 77004081 |
| binlog.000035 | 25762944 |
| binlog.000036 | 29114215 |
| binlog.000037 | 118225661 |
| binlog.000038 | 56589870 |
| binlog.000039 | 18179971 |
| binlog.000040 | 11646797 |
| binlog.000041 | 15945613 |
| binlog.000042 | 12146290 |
| binlog.000043 | 16277355 |
| binlog.000044 | 13478092 |
| binlog.000045 | 17401732 |
| binlog.000046 | 14354474 |
| binlog.000047 | 16165832 |
| binlog.000048 | 11631323 |
| binlog.000049 | 16313949 |
| binlog.000050 | 11968175 |
| binlog.000051 | 17268844 |
| binlog.000052 | 10591170 |
| binlog.000053 | 13375446 |
| binlog.000054 | 9956335 |
| binlog.000055 | 13898240 |
| binlog.000056 | 10176320 |
| binlog.000057 | 13498149 |
| binlog.000058 | 10733984 |
| binlog.000059 | 15476221 |
| binlog.000060 | 8845116 |
| binlog.000061 | 18087175 |
| binlog.000062 | 11616118 |
| binlog.000063 | 18869488 |
| binlog.000064 | 12632200 |
| binlog.000065 | 16316522 |
| binlog.000066 | 13042414 |
| binlog.000067 | 17688254 |
| binlog.000068 | 13279595 |
| binlog.000069 | 19273152 |
| binlog.000070 | 15820484 |
| binlog.000071 | 20545638 |
| binlog.000072 | 14548110 |
| binlog.000073 | 15668351 |
| binlog.000074 | 12437257 |
| binlog.000075 | 1076277163 |
| binlog.000076 | 610171932 |
| binlog.000077 | 1074592069 |
| binlog.000078 | 85640179 |
| binlog.000079 | 18773017 |
| binlog.000080 | 133222268 |
| binlog.000081 | 981671871 |
| binlog.000082 | 1073788252 |
| binlog.000083 | 60172126 |
| binlog.000084 | 733984164 |
| binlog.000085 | 515708883 |
| binlog.000086 | 880127622 |
| binlog.000087 | 255775887 |
| binlog.000088 | 1074182916 |
| binlog.000089 | 1074199959 |
| binlog.000090 | 1073894729 |
| binlog.000091 | 1074173854 |
| binlog.000092 | 1074136715 |
| binlog.000093 | 1074139431 |
| binlog.000094 | 1074139787 |
| binlog.000095 | 1074138397 |
| binlog.000096 | 1074139119 |
| binlog.000097 | 1074136291 |
| binlog.000098 | 1074138263 |
| binlog.000099 | 1075198779 |
| binlog.000100 | 320460399 |
| binlog.000101 | 1074405744 |
| binlog.000102 | 470407167 |
| binlog.000103 | 1074659796 |
| binlog.000104 | 619082051 |
| binlog.000105 | 504119897 |
| binlog.000106 | 591666719 |
| binlog.000107 | 200989926 |
| binlog.000108 | 1075273881 |
| binlog.000109 | 1076009067 |
| binlog.000110 | 1075724776 |
| binlog.000111 | 458346324 |
| binlog.000112 | 240523732 |
| binlog.000113 | 1074746420 |
| binlog.000114 | 679634940 |
| binlog.000115 | 388629556 |
| binlog.000116 | 1075264482 |
| binlog.000117 | 1075665650 |
| binlog.000118 | 106778208 |
| binlog.000119 | 600673498 |
| binlog.000120 | 137884104 |
| binlog.000121 | 231662527 |
| binlog.000122 | 1074945044 |
| binlog.000123 | 1074408295 |
| binlog.000124 | 1076205988 |
| binlog.000125 | 1076123648 |
| binlog.000126 | 1076108419 |
| binlog.000127 | 1076111880 |
| binlog.000128 | 1074693437 |
| binlog.000129 | 318320821 |
| binlog.000130 | 229367677 |
| binlog.000131 | 781331492 |
| binlog.000132 | 614394434 |
| binlog.000133 | 200852700 |
+---------------+------------+
129 rows in set (0.04 sec)
删除特定文件之前的二进制日志文件(也可以用PURGE MASTER LOGS BEFORE ‘2003-04-02 22:46:26’;特定日期代替文件名来清除特定时间之前的文件)
mysql> PURGE MASTER LOGS TO 'binlog.000131';
Query OK, 0 rows affected (1.20 sec)
再次查看磁盘空间
[root@m mysql]# df -h
文件系统 容量 已用 可用 已用% 挂载点
/dev/mapper/centos-root 46G 7.0G 39G 16% /
devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs 3.9G 404M 3.6G 11% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/sda1 1014M 166M 849M 17% /boot
/dev/mapper/vgdata-data 49G 6.7G 43G 14% /data
tmpfs 798M 12K 798M 1% /run/user/42
tmpfs 798M 0 798M 0% /run/user/0
查看二进制日保存时间
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.01 sec)
设置保存时间90天
mysql> set global expire_logs_days=90;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 90 |
+------------------+-------+
1 row in set (0.01 sec)
登陆评论:
使用GITHUB登陆