最新消息:20190717 VPS服务器:Vultr新加坡,WordPress主题:大前端D8,统一介绍入口:关于

【已解决】CentOS中mysql的bin的log日志文件占用太多空间

MySQL crifan 76浏览 0评论
折腾:
【未解决】尝试解决crifan.com的WordPress网站经常出错:建立数据库连接时出错
期间,发现是:mysql的bin的log,占用了太多空间,导致磁盘空间满了,没剩余可用空间了:
[root@crifan data]# du -h mysql/
1.1M    mysql/performance_schema
12M     mysql/mysql
270M    mysql/crifan_wp_db
676K    mysql/sys
17G     mysql/
好像是mysql占用很多?
[root@crifan mysql]# ls -lh
total 17G
-rw-r----- 1 mysql mysql   56 May  9  2019 auto.cnf
drwxr-x--- 2 mysql mysql 4.0K May 25 18:04 crifan_wp_db
-rw-r----- 1 mysql mysql  333 Nov 25  2019 ib_buffer_pool
-rw-r----- 1 mysql mysql  12M Jun  6 12:27 ibdata1
-rw-r----- 1 mysql mysql  32M Jun  6 12:27 ib_logfile0
-rw-r----- 1 mysql mysql  32M May  9  2019 ib_logfile1
-rw-r----- 1 mysql mysql  32M May  9  2019 ib_logfile2
-rw-r----- 1 mysql mysql  12M Jun  8 03:13 ibtmp1
drwxr-x--- 2 mysql mysql 4.0K May  9  2019 mysql
-rw-r----- 1 mysql mysql 1.1G May 31 16:45 mysql-bin.000368
-rw-r----- 1 mysql mysql 1.1G Jun  1 14:35 mysql-bin.000369
-rw-r----- 1 mysql mysql 1.1G Jun  2 14:31 mysql-bin.000370
-rw-r----- 1 mysql mysql 1.1G Jun  3 14:10 mysql-bin.000371
-rw-r----- 1 mysql mysql 527M Jun  4 01:45 mysql-bin.000372
-rw-r----- 1 mysql mysql  41M Jun  4 02:43 mysql-bin.000373
-rw-r----- 1 mysql mysql  50K Jun  4 02:43 mysql-bin.000374
-rw-r----- 1 mysql mysql 301K Jun  4 02:44 mysql-bin.000375
-rw-r----- 1 mysql mysql 106M Jun  4 04:46 mysql-bin.000376
-rw-r----- 1 mysql mysql 477K Jun  4 04:48 mysql-bin.000377
-rw-r----- 1 mysql mysql 1.8M Jun  4 04:52 mysql-bin.000378
-rw-r----- 1 mysql mysql 243K Jun  4 04:53 mysql-bin.000379
-rw-r----- 1 mysql mysql 345M Jun  4 13:10 mysql-bin.000380
-rw-r----- 1 mysql mysql 1.1G Jun  5 11:05 mysql-bin.000381
-rw-r----- 1 mysql mysql 106M Jun  5 13:01 mysql-bin.000382
-rw-r----- 1 mysql mysql 991M Jun  6 08:56 mysql-bin.000383
-rw-r----- 1 mysql mysql 142M Jun  6 12:27 mysql-bin.000384
-rw-r----- 1 mysql mysql 1.1G Jun  7 00:53 mysql-bin.000385
-rw-r----- 1 mysql mysql 1.1G Jun  7 12:53 mysql-bin.000386
-rw-r----- 1 mysql mysql 901M Jun  9 09:21 mysql-bin.000387
-rw-r----- 1 mysql mysql  380 Jun  7 12:53 mysql-bin.index
-rw-r----- 1 mysql mysql 6.1G Jun  9 09:13 mysql-error.log
-rw-r----- 1 mysql mysql    6 Jun  6 12:27 mysql.pid
-rw-r----- 1 mysql mysql  71M Jun  8 03:11 mysql-slow.log
drwxr-x--- 2 mysql mysql 4.0K May  9  2019 performance_schema
drwxr-x--- 2 mysql mysql  12K May  9  2019 sys
原来是,此处有多个bin的log日志文件很大
mysql binlog too large
MySQL – Binary Log File size growing more than the maximum limit – Database Administrators Stack Exchange
I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn’t provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter max_binlog_size = 1073741824 which is equivalent to 1GB.
replication – Is it safe to delete mysql-bin files? – Database Administrators Stack Exchange
此处最后一个是: mysql-bin.000387
所以去试试
[root@crifan mysql]# mysql -u root -p
Enter password:
ERROR 1040 (HY000): Too many connections
结果报错了。
重启Vultr 然后再去操作
登录后台
https://my.vultr.com
结果 secureCRT中ssh连接不上去了。。。
终于可以进去了。
[root@crifan ~]# cd /data/
[root@crifan data]# ll
total 12
drwxr-xr-x 6 mysql mysql 4096 Jun  9 09:29 mysql
drwxr-xr-x 2 root  root  4096 Jun  9 03:45 wwwlogs
drwxr-xr-x 5 root  root  4096 May 24  2019 wwwroot
[root@crifan data]# du -sh mysql/
17G     mysql/
[root@crifan data]# du -sh wwwlogs/
23M     wwwlogs/
[root@crifan data]# du -sh wwwroot/
17G     wwwroot/
目前是wwwroot也是17G
而mysql中17G,的确是不正常的。
[root@crifan data]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
至少此处可以登录PhpMyAdmin了:
也无法登录
无法登录 MySQL 服务器
mysqli_real_connect(): (HY000/2002): Connection refused
算了,手动删除其中一个,很旧的bing的log
另外:
-rw-r----- 1 mysql mysql 6.1G Jun  9 09:29 mysql-error.log
待会可以先删除这个erroe的log,不会有其他影响
但是先去看看最近的错误
[root@crifan mysql]# tail -n 50 mysql-error.log
2020-06-07T19:13:57.110134Z 316376 [ERROR] Got error 127 when reading table './crifan_wp_db/wp_options'
。。。
2020-06-07T19:13:57.132434Z 316376 [ERROR] Got error 127 when reading table './crifan_wp_db/wp_options'
2020-06-07T19:13:57.134697Z 316376 [ERROR] Got error 127 when reading table './crifan_wp_db/wp2020-06-09T01:29:49.013427Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-06-09T01:29:49.013456Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-06-09T01:29:49.013487Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-06-09T01:29:49.016348Z 0 [Warning] InnoDB: 1048576 bytes should have been written. Only 45056 bytes written. Retrying for the remaining bytes.
2020-06-09T01:29:49.016380Z 0 [Warning] InnoDB: Retry attempts for writing partial data failed.
2020-06-09T01:29:49.016393Z 0 [ERROR] InnoDB: Write to file ./ibtmp1failed at offset 4194304, 1048576 bytes should have been written, only 45056 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2020-06-09T01:29:49.016411Z 0 [ERROR] InnoDB: Error number 28 means 'No space left on device'
2020-06-09T01:29:49.016419Z 0 [Note] InnoDB: Some operating system error numbers are described at 
http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2020-06-09T01:29:49.016428Z 0 [ERROR] InnoDB: Could not set the file size of './ibtmp1'. Probably out of disk space
2020-06-09T01:29:49.016433Z 0 [ERROR] InnoDB: Unable to create the shared innodb_temporary
2020-06-09T01:29:49.016437Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-06-09T01:29:49.517868Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-06-09T01:29:49.517900Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-06-09T01:29:49.517921Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-06-09T01:29:49.517930Z 0 [ERROR] Failed to initialize builtin plugins.
2020-06-09T01:29:49.517937Z 0 [ERROR] Aborting


2020-06-09T01:29:49.517949Z 0 [Note] Binlog end
2020-06-09T01:29:49.518040Z 0 [Note] Shutting down plugin 'MyISAM'
2020-06-09T01:29:49.518064Z 0 [Note] Shutting down plugin 'CSV'
2020-06-09T01:29:49.518559Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
很明显,也是:没空间了。。。
所以去删除:
# rm -rf mysql-error.log
空间就空出来6G了,只占用11G了:
[root@crifan mysql]# cd ..
[root@crifan data]# du -sh mysql/
11G     mysql/
[root@crifan data]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        79G   69G  6.1G  92% /
devtmpfs        1.9G     0  1.9G   0% /dev
tmpfs           1.9G     0  1.9G   0% /dev/shm
tmpfs           1.9G   17M  1.9G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
tmpfs           379M     0  379M   0% /run/user/0
这样再去重启Vultr
[root@crifan ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        79G   67G  8.0G  90% /
devtmpfs        1.9G     0  1.9G   0% /dev
tmpfs           1.9G     0  1.9G   0% /dev/shm
tmpfs           1.9G   17M  1.9G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
tmpfs           379M     0  379M   0% /run/user/0
最后一个bin的log是:
mysql-bin.000388
尝试进去mysql
MySQL [(none)]> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.00 sec)
发现默认此处的expire_logs_days是7天
-》所以待会不改为3天,而是更短,比如1天之类的
MySQL [(none)]> SET GLOBAL expire_logs_days = 1;
Query OK, 0 rows affected (0.00 sec)


MySQL [(none)]> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 1     |
+------------------+-------+
1 row in set (0.01 sec)
以及再去清理现有的bin的log
MySQL [(none)]> PURGE BINARY LOGS TO 'mysql-bin.000388';
Query OK, 0 rows affected (0.14 sec)
退出去看看,是否自动清理了现有的bin的log的文件
[root@crifan mysql]# ls -lh
total 191M
-rw-r----- 1 mysql mysql   56 May  9  2019 auto.cnf
drwxr-x--- 2 mysql mysql 4.0K May 25 18:04 crifan_wp_db
-rw-r----- 1 mysql mysql  333 Nov 25  2019 ib_buffer_pool
-rw-r----- 1 mysql mysql  12M Jun  9 09:40 ibdata1
-rw-r----- 1 mysql mysql  32M Jun  9 09:40 ib_logfile0
-rw-r----- 1 mysql mysql  32M May  9  2019 ib_logfile1
-rw-r----- 1 mysql mysql  32M May  9  2019 ib_logfile2
-rw-r----- 1 mysql mysql  12M Jun  9 09:45 ibtmp1
drwxr-x--- 2 mysql mysql 4.0K May  9  2019 mysql
-rw-r----- 1 mysql mysql  154 Jun  9 09:40 mysql-bin.000388
-rw-r----- 1 mysql mysql   19 Jun  9 09:45 mysql-bin.index
-rw-r----- 1 mysql mysql 164K Jun  9 09:45 mysql-error.log
-rw-r----- 1 mysql mysql    5 Jun  9 09:40 mysql.pid
-rw-r----- 1 mysql mysql  71M Jun  9 09:40 mysql-slow.log
drwxr-x--- 2 mysql mysql 4.0K May  9  2019 performance_schema
drwxr-x--- 2 mysql mysql  12K May  9  2019 sys
果然自动清理了。
[root@crifan mysql]# cd ..
[root@crifan data]# ll
total 12
drwxr-xr-x 6 mysql mysql 4096 Jun  9 09:45 mysql
drwxr-xr-x 2 root  root  4096 Jun  9 03:45 wwwlogs
drwxr-xr-x 5 root  root  4096 May 24  2019 wwwroot
[root@crifan data]# du -sh mysql/
474M    mysql/
从17G变成400多M了。
以及以后的bin的log,最多保留1天 -》 或许就不会导致 bin的log文件太多,太占用空间的问题了。待过些天后确认,bin的log的文件有多少个,每个有多大。
How to debug huge binary log file increases in MySQL replication setup? – Database Administrators Stack Exchange
replication – Is it safe to delete mysql-bin files? – Database Administrators Stack Exchange
This really depends on your backup strategy. One of the main reasons to keep the binary logs around is to restore your database to a ‘point-in-time’. If your database crashes and requires restoration, you would restore the latest full backup, and then play back the binary logs starting with the position of the full backup.
此处bin 的log,是由于:
万一mysql挂了崩溃了,可以恢复
恢复到你的指定的时间的状态
所以,保存的越久,好处是可以恢复到越久。
How to set MySQL binlog retention in hours? – Database Administrators Stack Exchange
expire_log_days是新的写法
旧写法是:binlog_expire_logs_seconds
PURGE BINARY LOGS syntax
BEFORE (PURGE BINARY LOGS BEFORE now() - INTERVAL 1 HOUR;
max-binlog_size
FLUSH BINARY LOGS
去看看
MySQL [(none)]> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
1073741824=1G
算了,去改小一点,比如200M = 209715200
MySQL [(none)]> SET GLOBAL max_binlog_size = 209715200;
Query OK, 0 rows affected (0.00 sec)


MySQL [(none)]> show variables like 'max_binlog_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| max_binlog_size | 209715200 |
+-----------------+-----------+
1 row in set (0.00 sec)
好了。这样更能确保,不会太占用空间了。
【总结】
此处:
发现
/data/mysql
下面,有多个
mysql-bin.000xxx
的文件,大小很多都是1G
已知:是mysql的bin的log
问题原因:
此处mysql开启了bin的log
且用了默认的配置:默认大小是1G,最多保留7天
以至于保存了很多个bin的log
占用太多空间,导致mysql无法正常启动,和WordPress无法正常运行
解决办法:
(0)删除error的log
此处error的log有6G,需要先去删除:
cd /data/mysql
rm -rf mysql-error.log
(1)减少默认配置
登录mysql
mysql -u root -p
去配置,默认保存1天:
SET GLOBAL expire_logs_days = 1;
单个bin的log最大200M=209715200:
SET GLOBAL max_binlog_size = 209715200;
(2)删除清理已有的bin的log
建议:不要手动直接删除,而是用msyql内部purge去删除bin的log
通过:
# ls -lh
。。。
-rw-r----- 1 mysql mysql  154 Jun  9 09:40 mysql-bin.000388
得知最后一个bin的log是:
mysql-bin.000388
删除在此之前的bin的log:
PURGE BINARY LOGS TO 'mysql-bin.000388';
即可。

转载请注明:在路上 » 【已解决】CentOS中mysql的bin的log日志文件占用太多空间

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
17 queries in 0.077 seconds, using 9.40MB memory