Solved

Pruning MySQL tables

Posted on 2014-04-21
5
606 Views
Last Modified: 2014-04-22
Greetings,
I've inherited a system that has run out of disk space in the /var/lib/mysql directory. I was instructed to prune a bunch of tables (about 50 or more tables). I am a newbie with MySQL and don't want to break the system.

By pruning do they mean to delete rows in each of the tables or to delete the set of tables. In either case, is there a MySQL command that can do it for all tables? All the table names that I have to prune start with the same unique  set of characters.
0
Comment
Question by:centem
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40013649
Most likely it would be to delete some (likely not all) rows in the tables.

However, instructions aren't always given in technical language so no matter what the "correct" definition is of the word "prune" you should still be clarifying the request with someone who knows what is meant. It is much better to briefly bother someone getting a clear requirement than it is to waste hours of peoples time recovering data that shouldn't have been deleted.

If you have a look at the data in the tables, you will likely get some idea of how appropriate it is to delete some of it. For example, if a history of user logins has been stored for the last 10 years, then deleting the last 5 years would probably be appropriate. There may be a reason to export the data to file (for writing to DVD or other archiving media) in case it's ever needed. Just ensure it's well labelled or it won't be useful if it is needed!
0
 

Author Comment

by:centem
ID: 40013687
TerryAtOpus,

I checked and it is to delete the rows. The tables are not required for the functionality of the app is supports. Also, I did a VM snapshot of the system in case I have to revert to it.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40014014
DELETE FROM table_name
WHERE condition

If you remove the WHERE part it will delete all rows, same as
TRUNCATE TABLE table_name

The difference is that TRUNCATE resets the auto_increment counter to 0.

HTH,
Dan
0
 

Author Comment

by:centem
ID: 40014427
Thanks Dan. Is there a way to do this for more than one table at once. I have to do this for about 50 tables.
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 40014479
Edit: Wrong window. Posted code from another question :)

On topic: You need to do them one at a time. I don't know a way to mass truncate tables.

But if you use PHPMyAdmin, Adminer, SQLYog, HeidiSQL or any MySQL manager you just click on the table, and then click on "Empty table" so 50 tables should not take more than 2 minutes.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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