折腾：
【未解决】尝试解决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
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.
此处最后一个是： mysql-bin.000387
所以去试试
[root@crifan mysql]# mysql -u root -p Enter password: ERROR 1040 (HY000): Too many connections
结果报错了。
重启Vultr 然后再去操作
登录后台
结果 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的文件有多少个，每个有多大。
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挂了崩溃了，可以恢复
恢复到你的指定的时间的状态
所以，保存的越久，好处是可以恢复到越久。
expire_log_days是新的写法
BEFORE (PURGE BINARY LOGS BEFORE now() - INTERVAL 1 HOUR;
去看看
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';
即可。