Solved

Huge disk space usage mysql

Posted on 2014-11-15
15
359 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
LINUX backups with VEEAM 8 84
winscp where are logs stored 3 36
MySQL Backup Strategy 15 28
Upgrade BIOS / EUFI at Scale 4 38
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

832 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