asked on

MySQL ibdata1 file is pretty big: recommend stackexchange post?

Hi,

I have an earlier related posting but different. The /var/lib/mysql/ibdata1 file is pretty big:
781056 /var/lib/mysql/ibdata1

I read this posting to on how to possibly resolve it:
http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql

Is this recommended? I'd like to avoid deleting any databases though.

Father God bless<><

-------
I ran this to see my large files.

du -a /var | sort -n -r | head -n 10

3048880 /var
1396040 /var/log
1381472 /var/lib
1140736 /var/log/btmp
928720 /var/lib/mysql
781056 /var/lib/mysql/ibdata1
210048 /var/log/chkservd.log
202580 /var/cpanel
118796 /var/cpanel/perl
118784 /var/cpanel/perl/easy
root@ip-184-168-116-73 [/var/lib/mysql]# cd /var/lib/mysql
root@ip-184-168-116-73 [/var/lib/mysql]# ls -al
total 901172
drwxr-x--x 15 mysql mysql 4096 Nov 15 17:43 ./
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 17:49 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 13 12:21 ib_logfile1
-rw-rw---- 1 mysql mysql 799014912 Nov 15 17:49 ibdata1
-rw-rw---- 1 mysql mysql 499659 Jul 30 16:04 ip-184-168-116-73-slow.log
-rw-rw---- 1 mysql mysql 895837 Nov 15 17:49 ip-184-168-116-73.ip.secureserver.net.err
-rw-rw---- 1 mysql mysql 5 Nov 15 17:38 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 16436304 Nov 15 15:00 mysql-bin.000049
-rw-rw---- 1 mysql mysql 4079207 Nov 15 17:38 mysql-bin.000050
-rw-rw---- 1 mysql mysql 178659 Nov 15 17:49 mysql-bin.000051
-rw-rw---- 1 mysql mysql 57 Nov 15 17:42 mysql-bin.index
srwxrwxrwx 1 mysql mysql 0 Nov 15 17:38 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/
drwx------ 2 mysql mysql 4096 Jul 31 02:37 whmxfer/

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Victor Kimura

ASKER

How do I find out how much space is in that 781MB file? Is there a MySQL command that I can use. All the databases are in that file though so I would assume (I don't know if it's safe to assume though) that the 781MB file is composed mostly of my entire databases aggregated together. But is there some command line that I enter to quickly find out?

Dave Baldwin

I don't know yet. There should be something but I haven't found it yet.

SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Victor Kimura

ASKER

Hmmm...I guess Danny Beckett's solution and Max Webster's reply is probably a good option. Max says the config option in the my.cnf file:

If you use the configuration option innodb_file_per_table, you create multiple tablespaces. That is, MySQL creates separate files for each table instead of one shared file. These separate files a stored in the directory of the database, and they are deleted when you delete this database. This should remove the need to shrink/purge ibdata files in your case

I guess this has to be done manually every so often then?

SOLUTION

Julian Parker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Victor Kimura

ASKER

Ok, thank you, fellas. You were helpful. =)