4

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?

  • Do you have binary logging enabled ??? – RolandoMySQLDBA Jul 14 '15 at 14:13
  • I'm not sure (sorry, not a DBA). I see a bunch of mysql-bin.* files. Are those binary logs? – gwg Jul 14 '15 at 14:14
  • Yes, those are binary logs. What folder are the binary logs in ? – RolandoMySQLDBA Jul 14 '15 at 14:15
  • How many binary logs are there ? Is the DB Server a Master in some replication topology ? – RolandoMySQLDBA Jul 14 '15 at 14:17
  • /var/lib/mysql. – gwg Jul 14 '15 at 14:17
3

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.

  • 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 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? – gwg 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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