0

My app crashes because there are too many connections open and mysql does not make me log in as root showing the same problem. After restarting mysql, it does not restart because of no space on disk

7
  • Show us your configuration (my.cnf). Possibly some things were set too high. How much RAM do you have? How much disk? How big is the dataset? – Rick James Mar 22 at 0:54
  • Is the ibtmp file table taking all space ? – JYOTI RAJAI Mar 22 at 9:36
  • @RickJames hello ricky, thank you for your comment. The machine has 2GB RAM and 50GB of SSD. At that moment 40 GB of ssd were taken by BIN files. With the retention policy updated (5 days max), these files take max 8 GB. – Domenico Scalera Mar 23 at 10:28
  • I will provide my cnf file this afternoon @RickJames – Domenico Scalera Mar 23 at 10:29
  • @JYOTIRAJAI how can I check ibtmp? what did you meant with ibtmp? – Domenico Scalera Mar 23 at 10:30
1

here there is a full guide on how I solved the issue:

I tried to log in as root with the command:

sudo mysql -u root -p 

But it did not work for me, mysql kept giving the error:

ERROR 1040 (HY000): Too many connections

After some searches I tried

service mysqld restart --max-connections=500

This way stopped mysqld, which wasn't able to restart because at the same time my disk was full and receiving:

 Error: 28 (No space left on device)

Then, in order to inspect on ubuntu where my space was occupied I used the command:

du -h --max-depth=1 | sort -hr

That let me know all the space was occupied by binlog files of mysql. I couldn't remove files by hand, it is not recommended procedure. But I did not have anything to remove and I was not able to start mysql to purge the binlog files. Then I run the following command which gave me enough space to restart mysql:

apt-get clean
service mysql restart || systemctl restart mysqld

At this point I was able to login to mysql and purge the binlog files with:

PURGE BINARY LOGS BEFORE '2021-03-03 22:46:26';

But this is not a long term solution. To avoid the problem in the future I did:

SET GLOBAL binlog_expire_logs_seconds = 432000; // 5 days of binlog files

I am going to improve this answer when I solve the maximum connection issue for a long term solution.

Not the answer you're looking for? Browse other questions tagged or ask your own question.