Solved

how to shrink MySQL DB

Posted on 2008-06-11
6
2,740 Views
Last Modified: 2008-09-18
I have a MySQL database running on Windows 2003.  It is rapidly occupying space on my server.  What is the best way to manage MySQL in order to purge data in order to reclaim space.  It should be noted that this MySQL is running InnoDB inside and therefore the standard 14 day purging of data does not reclaim any space since it puts all tables into one table space.  Thank you in advance
0
Comment
Question by:newtontech4
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
jojuez earned 250 total points
ID: 21769271
You may need to truncate your BIN logs. Are you backing up this server?
0
 
LVL 57

Expert Comment

by:giltjr
ID: 21769370
Can you identify what is exactly using the space?  Is it logs or is it the actual database/tablespace files?

If it is the actual tablespace files you should be able to reclaim space by doing reorgs regularly.
0
 

Author Comment

by:newtontech4
ID: 21769974
Hi giltjr - ibdata1 is growing the most.  I think this is due to the fact that InnoDB is being used.   This is a MySQL in my lab so i have not developed a back-up process yet.
0
 
LVL 57

Assisted Solution

by:giltjr
giltjr earned 250 total points
ID: 21772535
O.K.  Do a reorg is your  best bet for reclaiming space.  This is assuming that data is being purged/deleted.  If no data is being purged/delete, only added, then there is nothing you can do.  It will continue to get bigger and bigger.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Google Play app store not being blocked from smartphones 4 31
update joined tables 2 30
sql_mode 1 18
Looking for open port with Telnet 5 20
Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

912 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

23 Experts available now in Live!

Get 1:1 Help Now