Removing mysql data

Problems with the Mac OS X version of XAMPP, questions, comments, and anything related.

Removing mysql data

Postby jbourland » 23. October 2011 19:58

In order to clear out some disk space in my Mac, I've dropped several old mysql databases in my XAMPP installation. I would estimate that I have about 200MB of data in my remaining dbs.

I was shocked to discover, however, that my mysql data still takes up 6.4GB of disk space (DaisyDisk screenshot attached showing most of the files and their sizes). This is a scan of the directory XAMPP/xamppfiles/var/mysql.

I'm assuming that some of these represent databases I still have, and some of them represent databases I have dropped.

Is there a safe way to remove some of these files? Is there some sort of garbage collection that should have happened automatically?

I am using an older version of: XAMPP 1.0.1. Operating system is 10.6.7.

Thanks!

Image
jbourland
 
Posts: 3
Joined: 07. June 2006 04:13

Re: Removing mysql data

Postby jbourland » 23. October 2011 21:34

Actually just found the solution, so will leave this question up for others who run into this issue.

Turns out the mysql-bin files I was looking are not storing database data — though, in a way, they technically are (I'll get back to that in a sec).

These are mysql log files. Mysql by default is set to log every query issued to the server:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Mysql starts a new log file when mysql is restarted or when a log reaches a certain size. For those of us who have had the same XAMPP installation for a while, it could mean a lot of files. I had > 100.

Because mysql logs every query, if you're regularly importing large databases (like I am), your logs can get huge quickly. Technically these logs are there for backup, and well, logging, but for all intents and purposes, they're huge sql dump files. That is, if you're importing a database from a sql file, the contents of that file are effectively stored indefinitely in your mysql logs. You can delete the original sql dump, but you are also, whether you know it or not, storing exactly the same data elsewhere [insert security concerns here].

For me, this is primarily a disk space concern. I got back ~6GB by getting rid of old logs.

Here are 2 excellent articles I found about the subject:
http://systems.takizo.com/2009/08/23/how-to-remove-mysql-binary-log/
http://legroom.net/2008/06/29/flush-and-reset-mysql-binary-logs

I followed takizo's advice for purging logs up to a certain date, which I repeat here, for simplicity:

Code: Select all
shell> mysql -u username -p
mysql> PURGE BINARY LOGS BEFORE '2011-09-01 00:00:00';


Note, this problem can be avoided altogether, apparently, by pasting this into your my.cnf and restarting mysql:

Code: Select all
expire_logs_days = 7


and, of course, set that to however many days you're comfortable with. Not enough time has passed to adequately test it myself, but both of the above sources recommend it.

I got 6GB free out of this. Not too shabby.
jbourland
 
Posts: 3
Joined: 07. June 2006 04:13

Re: Removing mysql data

Postby JonB » 24. October 2011 01:34

Thanks for doing the leg work - good job!
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Removing mysql data

Postby IamJim » 23. December 2013 05:28

THANK YOU ALSO!!

This drove me crazy...as I read somewhere to delete them, but xampp got screwed up and had to re-install

But now? I had another overflow during a command of duplicating a 3.6gig table..and the frigging temp file went somewhere?

So after that, I did

mysql > truncate table table_name;

And the table is EMPTY, still have one table with 3660mb but? ibdata is still 9 gig? And XAMPP folder asy 9.5 gig used..And the mysql forler is 9.48 GIG of this

This is CRAZY !! <smile> I just installed 2 days ago, working one large database. of 3.6 gig...and will fill up my drive with crap in no time.

This is a bug I feel for sure.

You can DO THIS::

http://forums.mysql.com/read.php?108,201578,201578

3665.00 is my DB in question...so??????????????????

So, how-what-where is the DEAD DATA please? As phpmyadmin shows only 3.6 gig also ??? :evil: :evil: :evil:
IamJim
 
Posts: 56
Joined: 27. April 2009 03:01

Re: Removing mysql data

Postby Nobbie » 23. December 2013 11:37

Maybe Keys, (Search) Indexes, backup space for Rollbacks etc. - whithout knowing even one byte from your Database it is definitely impossible to get a clue.

P.S.: Did you read and try this? http://stackoverflow.com/questions/3456 ... e-in-mysql
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: Removing mysql data

Postby Altrea » 23. December 2013 12:50

IamJim wrote:This is a bug I feel for sure.

The MySQL binary log is a documented feature.
That ibdata1 is not shrinking after deleting data is also very well known.
Not a bug, sorry-
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64


Return to XAMPP for macOS

Who is online

Users browsing this forum: No registered users and 11 guests