Well, it started as a place for middleware notes, tips and tricks, but really, this is a place for me to list out some of the general notes, etc I have for myself. If anyone else finds them useful... Great! If not, at least I have a clipboard of solutions for me...
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.
Subscribe to:
Posts (Atom)