8

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
  • 183
  • 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
  • 182,700
  • 33
  • 317
  • 520
  • Hm, RESET_MASTER is not working: Query OK, 0 rows affected (0.09 sec) and all the binary files still exist. When I try other commands (looking at this SO http://dba.stackexchange.com/a/47048/70573), I get the error, Target log not found in binlog index. What's the safest way to clean up those binary files? – jds Jul 14 '15 at 15:10
  • This simply means that the mysql-bin.index file is out of sync with the actual files in /var/lib/mysql. You should run rm -f mysql-bin.0*. Then, try RESET MASTER; again. – RolandoMySQLDBA Jul 14 '15 at 15:12
  • 1
    If that does not work, then run cd /var/lib/mysql ; service mnysql stop ; rm -f mysql-bin.* ; service mysql start. This will regenerate a new mysql-bin.index and create mysql-bin.000001 – RolandoMySQLDBA Jul 14 '15 at 15:13