9

Overnight, our MySQL server completely filled the hard drive. Now, I cannot login via phpMyAdmin

`Cannot log in to the MySQL server`

or via the command line

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

or via mysqladmin

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'

There is one big table I can delete as a temporary fix if I can login to the server, but I can't. How can I login?

jds
  • 193
  • 1
  • 1
  • 5

1 Answers1

5

You did not need to erase the large tables. Why ???

Here is the strange thing about mysql. mysqld uses MyISAM temp tables for it internal operation. When mysqld runs out of space, it does not crash. IT FREEZES !!!

The following is my answer to 2 year old post "Site Offline" MySQL server failing to start and stop

EXCERPT

According to MySQL 5.0 Certification Study Guide

enter image description here Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.

END OF EXCERPT

All you had to do was manually delete the binary logs. Then, mysqld would start to function normally. Even deleting just 10 logs would have freed 1G and would make mysqld get going again.

You need to do the following:

Login to mysql and run

mysql> RESET MASTER;
mysql> SET GLOBAL expire_logs_days = 7;

Then, add this to my.cnf

[mysqld]
expire-logs-days = 7

Now, mysqld will automatically delete any binary log older than 7 days.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536