Showing posts with label mySQL. Show all posts
Showing posts with label mySQL. Show all posts

Wednesday, January 7, 2009

Deleting All Tables in a MySQL database

I recently came into a scenario where I wanted to delete all the tables in my MySQL 5.x database, but didn't want to have to drop and recreate the database (because I didn't have access to do this and was too impatient to wait for the help...) and I didn't want to have to manually enter in 50+ tables each time I needed to clean up. I finally figured out a decent solution... I grep the output from the mysqldump command, looking for only the lines that begin with the word DROP (i.e. Drop table such-and-such) and pipe those lines back into mysql. The result is something like the following, where you fill in the username, password and database with your own information... mysqldump -u [USERNAME] --password=[PASSWORD] [DATABASE] grep ^DROP mysql -u [USERNAME] --password=[PASSWORD] [DATABASE]

Tuesday, December 2, 2008

Getting the most recent row for each id

I was working on configTracker today (a pet project to watch server configurations for changes and do alerting, reporting, etc...) and ran into a scenario where I needed to find the most recent row in a table for a given id. The table has a column called aud_ts of type timestamp. and a column call host_id of type integer. After several iterations (my SQL skills are waining after not developing for 5 or so years....) I came up with: select host_id, max(aud_ts) from myTable group by host_id; Then, I can join this table against all the others in the system on host_id and aud_ts and know I'm getting the most recent info for this server.