Solved

Huge disk space usage mysql

Posted on 2014-11-15
15
340 Views
Last Modified: 2014-11-15
I have some huge disk space usage problems. I guess they are logs of sorts:

 du -a /var | sort -n -r | head -n 10
 du -a /var | sort -n -r | head -n 10
51703936        /var
50038024        /var/lib
49585272        /var/lib/mysql
1394116 /var/log
1139516 /var/log/btmp
1049608 /var/lib/mysql/mysql-bin.000047
1049608 /var/lib/mysql/mysql-bin.000046
1049608 /var/lib/mysql/mysql-bin.000045
1049608 /var/lib/mysql/mysql-bin.000044
1049608 /var/lib/mysql/mysql-bin.000043

When I change directory to the mysql directory I get:

cd /var/lib/mysql
root@ip-184-168-116-73 [/var/lib/mysql]# ls -al
total 49558020
drwxr-x--x 15 mysql mysql       4096 Nov 15 14:09 ./
drwxr-xr-x 33 root  root        4096 Jul 30 15:43 ../
-rw-r--r--  1 mysql mysql       3142 Jul 30 14:39 RPM_UPGRADE_HISTORY
-rw-r--r--  1 mysql mysql        524 Jul 30 14:39 RPM_UPGRADE_MARKER-LAST
-rw-rw----  1 mysql mysql         56 Jul 30 05:29 auto.cnf
drwx------  2 mysql mysql       4096 Oct 27  2012 cphulkd/
drwx------  2 mysql mysql       4096 Nov 13 08:27 emailmar_iem/
drwx------  2 mysql mysql       4096 Aug 27  2011 emailmar_templates/
drwx------  2 mysql mysql       4096 Nov  1 05:32 eximstats/
drwx------  2 mysql mysql      12288 Mar  6  2014 horde/
-rw-rw----  1 mysql mysql   50331648 Nov 15 14:28 ib_logfile0
-rw-rw----  1 mysql mysql   50331648 Nov 13 12:21 ib_logfile1
-rw-rw----  1 mysql mysql  799014912 Nov 15 14:28 ibdata1
-rw-rw----  1 mysql mysql     499659 Jul 30 16:04 ip-184-168-116-73-slow.log
-rw-rw----  1 mysql mysql     880078 Nov 15 14:24 ip-184-168-116-73.ip.secureserver.net.err
-rw-rw----  1 mysql mysql          5 Nov 15 14:09 ip-184-168-116-73.ip.secureserver.net.pid
drwx------  2 mysql mysql       4096 Jul 13  2011 leechprotect/
drwx------  2 mysql mysql       4096 Apr  5  2012 logaholicDB_ip_184_168_116_73/
drwx------  2 mysql mysql       4096 Jul 31 01:44 modsec/
drwx--x--x  2 mysql mysql       4096 Jul 30 06:15 mysql/
-rw-rw----  1 mysql mysql 1073742058 Aug  1 21:51 mysql-bin.000001
-rw-rw----  1 mysql mysql 1073743123 Aug  4 05:30 mysql-bin.000002
-rw-rw----  1 mysql mysql 1073742028 Aug  6 12:01 mysql-bin.000003
-rw-rw----  1 mysql mysql 1073742069 Aug  8 17:21 mysql-bin.000004
-rw-rw----  1 mysql mysql 1073742024 Aug 11 00:13 mysql-bin.000005
-rw-rw----  1 mysql mysql 1073742311 Aug 13 06:13 mysql-bin.000006
-rw-rw----  1 mysql mysql 1073741987 Aug 15 10:35 mysql-bin.000007
-rw-rw----  1 mysql mysql 1073744122 Aug 17 15:31 mysql-bin.000008
-rw-rw----  1 mysql mysql 1073742440 Aug 19 21:30 mysql-bin.000009
-rw-rw----  1 mysql mysql 1073741883 Aug 22 03:58 mysql-bin.000010
-rw-rw----  1 mysql mysql 1073742111 Aug 24 10:59 mysql-bin.000011
-rw-rw----  1 mysql mysql 1073741970 Aug 26 17:37 mysql-bin.000012
-rw-rw----  1 mysql mysql 1073744574 Aug 29 00:11 mysql-bin.000013
-rw-rw----  1 mysql mysql 1073742340 Aug 31 07:32 mysql-bin.000014
-rw-rw----  1 mysql mysql 1073741909 Sep  2 14:57 mysql-bin.000015
-rw-rw----  1 mysql mysql 1073742227 Sep  4 21:53 mysql-bin.000016
-rw-rw----  1 mysql mysql 1073742127 Sep  7 05:11 mysql-bin.000017
-rw-rw----  1 mysql mysql 1073742207 Sep  9 12:32 mysql-bin.000018
-rw-rw----  1 mysql mysql 1073741900 Sep 11 21:52 mysql-bin.000019
-rw-rw----  1 mysql mysql 1073742764 Sep 14 06:34 mysql-bin.000020
-rw-rw----  1 mysql mysql 1073741989 Sep 16 16:57 mysql-bin.000021
-rw-rw----  1 mysql mysql 1073743228 Sep 18 22:46 mysql-bin.000022
-rw-rw----  1 mysql mysql  734912512 Sep 21 00:47 mysql-bin.000023
-rw-rw----  1 mysql mysql 1073743841 Sep 23 07:12 mysql-bin.000024
-rw-rw----  1 mysql mysql  487571456 Sep 24 13:31 mysql-bin.000025
-rw-rw----  1 mysql mysql 1073742868 Sep 26 22:46 mysql-bin.000026
-rw-rw----  1 mysql mysql 1073741873 Sep 29 06:34 mysql-bin.000027
-rw-rw----  1 mysql mysql 1073744374 Oct  1 14:13 mysql-bin.000028
-rw-rw----  1 mysql mysql 1073741953 Oct  3 20:25 mysql-bin.000029
-rw-rw----  1 mysql mysql 1073742043 Oct  6 05:10 mysql-bin.000030
-rw-rw----  1 mysql mysql 1073744299 Oct  8 14:36 mysql-bin.000031
-rw-rw----  1 mysql mysql 1073742182 Oct 11 00:23 mysql-bin.000032
-rw-rw----  1 mysql mysql 1073741904 Oct 13 09:52 mysql-bin.000033
-rw-rw----  1 mysql mysql 1073742225 Oct 15 18:24 mysql-bin.000034
-rw-rw----  1 mysql mysql 1073743147 Oct 17 23:47 mysql-bin.000035
-rw-rw----  1 mysql mysql 1073744411 Oct 20 06:59 mysql-bin.000036
-rw-rw----  1 mysql mysql 1073742109 Oct 22 12:54 mysql-bin.000037
-rw-rw----  1 mysql mysql 1073742611 Oct 24 17:19 mysql-bin.000038
-rw-rw----  1 mysql mysql 1073742134 Oct 26 21:38 mysql-bin.000039
-rw-rw----  1 mysql mysql 1073742072 Oct 29 01:59 mysql-bin.000040
-rw-rw----  1 mysql mysql 1073742128 Oct 31 04:15 mysql-bin.000041
-rw-rw----  1 mysql mysql 1073742189 Nov  2 08:25 mysql-bin.000042
-rw-rw----  1 mysql mysql 1073743911 Nov  4 11:40 mysql-bin.000043
-rw-rw----  1 mysql mysql 1073742816 Nov  6 15:18 mysql-bin.000044
-rw-rw----  1 mysql mysql 1073741953 Nov  8 18:43 mysql-bin.000045
-rw-rw----  1 mysql mysql 1073742913 Nov 10 22:16 mysql-bin.000046
-rw-rw----  1 mysql mysql 1073742499 Nov 13 01:48 mysql-bin.000047
-rw-rw----  1 mysql mysql  249393071 Nov 15 14:09 mysql-bin.000048
-rw-rw----  1 mysql mysql    6067698 Nov 15 14:28 mysql-bin.000049
-rw-rw----  1 mysql mysql        931 Nov 15 14:09 mysql-bin.index
srwxrwxrwx  1 mysql mysql          0 Nov 15 14:09 mysql.sock=
-rw-r--r--  1 mysql mysql          6 Jul 30 06:15 mysql_upgrade_info
drwx------  2 mysql mysql       4096 Jul 14 18:21 myultrat_questionnaire/
-rw-------  1 mysql root         953 Jul 30 15:42 nohup.out
drwx------  2 mysql mysql       4096 Jul 30 06:15 performance_schema/
drwx------  2 mysql mysql       4096 Aug  5 02:45 roundcube/

What should I do? What is safe to remove?
0
Comment
Question by:Victor Kimura
  • 9
  • 6
15 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40444903
If you don't want any logs at all then disable the log-bin entry in my.cnf and restart MySQL
To safely get rid of the ones there enter in a MySQL terminal
PURGE BINARY LOGS TO 'mysql-bin.000049';

http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

You can also rotate the logs every 7 days for example:
SET GLOBAL expire_logs_days = 7;
0
 

Author Comment

by:Victor Kimura
ID: 40444912
It's safe to delete those mysql-bin.xxxxxx files?

Where do I run that command?
SET GLOBAL expire_logs_days = 7;

In MySQL terminal?

Thank you.
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 500 total points
ID: 40444914
Don't delete them manually, use the PURGE command above
All this in from a terminal
0
 

Author Comment

by:Victor Kimura
ID: 40444920
Ok, thanks, Gary.

doing what you suggested right now. Just read that link too. =)

These files are kind of big too:
-rw-rw----  1 mysql mysql   50331648 Nov 15 14:28 ib_logfile0
-rw-rw----  1 mysql mysql   50331648 Nov 13 12:21 ib_logfile1
-rw-rw----  1 mysql mysql  799014912 Nov 15 14:28 ibdata1

ibdata1 looks big. What can I do about this one if I should do anything at all?
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 500 total points
ID: 40444921
Don't touch anything else especially ibdata1 - that is your databases!
0
 

Author Comment

by:Victor Kimura
ID: 40444977
It's saying to backup previous mysql-bin files (it's advisable). I'm having some trouble because I'm low in disk space. Can I just simply run that command without the backup? Is it ok?
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 500 total points
ID: 40444986
Yep, do you need logs going back 4 months?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Victor Kimura
ID: 40445009
not really. So I'm going to just run that command then, ok?

Is it safe to set the days to less than 7 like 3 or 4?
SET GLOBAL expire_logs_days = 4;
0
 

Author Comment

by:Victor Kimura
ID: 40445016
Also, I know not to delete the ibdata file but there is this post:
stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql

on how to shink it. Do you recommend what they are suggesting on stackexchange link?
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 500 total points
ID: 40445026
Set it to whatever days you need to go back.

Unless your ibdata file is massive there is no point messing around with it.
0
 

Author Comment

by:Victor Kimura
ID: 40445040
Ok, thanks.

-rw-rw----  1 mysql mysql  799014912 Nov 15 14:28 ibdata1

Is that normal for the ibdata file?
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 500 total points
ID: 40445047
Thats pretty big, but this is different from your original question and should be a seperate question.
0
 

Author Comment

by:Victor Kimura
ID: 40445088
Ok, haha. =) np.
0
 

Author Comment

by:Victor Kimura
ID: 40445105
0
 

Author Closing Comment

by:Victor Kimura
ID: 40445108
Thank you.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now