Pruning MySQL tables
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.
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.
ASKER
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.
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.
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
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!