MySQL cleanup
I like to use my web hosting to experiment. Every so often I have to clean up tables inserted into my database by whatever the web software de jour happens to be. What follows is how I do this. There are better and more interesting ways of doing what I do, but this works.
Before we get started, there are a few things I should mention. This is not for the faint of heart. I’m not going to fill in the little details. I assume you know your way around a Linux command line, and that you are familiar enough to get the gist of what I’m doing. Also, this is mainly for me, as a reminder for the next time I have to do this. If you find it valuable too, well, good. And don’t forget to backup your database!
I’m using MySQL on Ubuntu Linux. Here are the technical specs.
$ uname -r 2.6.28.3-1-grsec $ cat /etc/lsb-release DISTRIB_ID=Ubuntu DISTRIB_RELEASE=8.04 DISTRIB_CODENAME=hardy DISTRIB_DESCRIPTION="Ubuntu 8.04.2" $ mysql -V mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
The first thing to do is get a dump of all the tables in the database. I do that using the tee command in mysql. Log into mysql
$ mysql -u <username> <database> -p
And use tee to set the output file
mysql> tee sqlout.txt Logging to file 'sqlout.txt'
Then just use the mysql command ‘show tables;’ to get a listing of all the tables in the database. My actual listing looks like this:
$ cat sqlout.txt mysql> show tables ; +--------------------------+ | Tables_in_<database> | +--------------------------+ | g2_AccessMap | | g2_AccessSubscriberMap | | g2_AlbumItem | | g2_AnimationItem | | g2_CacheMap | | g2_ChildEntity | | g2_Comment | | g2_CustomFieldMap | | g2_DataItem | | g2_Derivative | | g2_DerivativeImage | | g2_DerivativePrefsMap | | g2_DescendentCountsMap | | g2_Entity | | g2_ExifPropertiesMap | | g2_ExternalIdMap | | g2_FactoryMap | | g2_FailedLoginsMap | | g2_FileSystemEntity | | g2_G1MigrateMap | | g2_Getid3PropsMap | | g2_Group | | g2_ImageBlockCacheMap | | g2_ImageBlockDisabledMap | | g2_Item | | g2_ItemAttributesMap | | g2_LinkItem | | g2_Lock | | g2_MaintenanceMap | | g2_MimeTypeMap | | g2_MovieItem | | g2_MultiLangItemMap | | g2_PendingUser | | g2_PermalinksMap | | g2_PermissionSetMap | | g2_PhotoItem | | g2_PluginMap | | g2_PluginPackageMap | | g2_PluginParameterMap | | g2_QuotasMap | | g2_RatingCacheMap | | g2_RatingMap | | g2_RecoverPasswordMap | | g2_RssMap | | g2_Schema | | g2_SequenceId | | g2_SequenceLock | | g2_SessionMap | | g2_ThumbnailImage | | g2_TkOperatnMap | | g2_TkOperatnMimeTypeMap | | g2_TkOperatnParameterMap | | g2_TkPropertyMap | | g2_TkPropertyMimeTypeMap | | g2_UnknownItem | | g2_User | | g2_UserGroupMap | | g2_WatermarkImage | | g2_WebDavLockMap | | milo_cas_count | | milo_cas_image | | milo_comments | | milo_links | | milo_options | | milo_postmeta | | milo_posts | | milo_term_relationships | | milo_term_taxonomy | | milo_terms | | milo_usermeta | | milo_users | | sami_cas_count | | sami_cas_image | | sami_comments | | sami_links | | sami_ngg_album | | sami_ngg_gallery | | sami_ngg_pictures | | sami_options | | sami_postmeta | | sami_posts | | sami_term_relationships | | sami_term_taxonomy | | sami_terms | | sami_usermeta | | sami_users | | scott_cas_count | | scott_cas_image | | scott_comments | | scott_links | | scott_options | | scott_postmeta | | scott_posts | | scott_term_relationships | | scott_term_taxonomy | | scott_terms | | scott_usermeta | | scott_users | +--------------------------+ 98 rows in set (0.00 sec)mysql> quit
For this exercise, I want to dump all the tables with the ‘g2_’ prefix. These are left over from some photo gallery software that I didn’t really use and so deleted. I need to modify the listing so it only has the lines with the prefix ‘g2_’ in them. I can use the ‘sed’ utility for that. The following command strips out the white space and vertical bars. It leaves only the words that are prefixed with ‘g2_’ and appends a comma. It writes the whole thing to a new file called ‘drop_table.sql’
$ sed -n 's/|\s\(\<g2_.*\>\).*/\1,/gp' sqlout.txt > drop_table.sql
I now open the file with a text editor (my favorite is vi) and add the appropriate MySQL statements for dropping tables. My list now looks like this:
DROP TABLE IF EXISTS g2_AccessMap, g2_AccessSubscriberMap, g2_AlbumItem, g2_AnimationItem, g2_CacheMap, g2_ChildEntity, g2_Comment, g2_CustomFieldMap, g2_DataItem, g2_Derivative, g2_DerivativeImage, g2_DerivativePrefsMap, g2_DescendentCountsMap, g2_Entity, g2_ExifPropertiesMap, g2_ExternalIdMap, g2_FactoryMap, g2_FailedLoginsMap, g2_FileSystemEntity, g2_G1MigrateMap, g2_Getid3PropsMap, g2_Group, g2_ImageBlockCacheMap, g2_ImageBlockDisabledMap, g2_Item, g2_ItemAttributesMap, g2_LinkItem, g2_Lock, g2_MaintenanceMap, g2_MimeTypeMap, g2_MovieItem, g2_MultiLangItemMap, g2_PendingUser, g2_PermalinksMap, g2_PermissionSetMap, g2_PhotoItem, g2_PluginMap, g2_PluginPackageMap, g2_PluginParameterMap, g2_QuotasMap, g2_RatingCacheMap, g2_RatingMap, g2_RecoverPasswordMap, g2_RssMap, g2_Schema, g2_SequenceId, g2_SequenceLock, g2_SessionMap, g2_ThumbnailImage, g2_TkOperatnMap, g2_TkOperatnMimeTypeMap, g2_TkOperatnParameterMap, g2_TkPropertyMap, g2_TkPropertyMimeTypeMap, g2_UnknownItem, g2_User, g2_UserGroupMap, g2_WatermarkImage, g2_WebDavLockMap ;
The first line instructs MySQL to drop (delete) tables. You’ll also notice I removed a comma from the last table name in the list. The final line has a semicolon because all MySQL statement must be terminated that way.
I can now pass my script into MySQL and delete these tables. That looks like this :
$ mysql <databasename> -u <user> -p < drop_table.sql
And that’s it. As you can see by looking at a new listing of tables in my database, all tables with the prefix ‘g2_’ are gone.
mysql> show tables; +--------------------------+ | Tables_in_database | +--------------------------+ | milo_cas_count | | milo_cas_image | | milo_comments | | milo_links | | milo_options | | milo_postmeta | | milo_posts | | milo_term_relationships | | milo_term_taxonomy | | milo_terms | | milo_usermeta | | milo_users | | sami_cas_count | | sami_cas_image | | sami_comments | | sami_links | | sami_ngg_album | | sami_ngg_gallery | | sami_ngg_pictures | | sami_options | | sami_postmeta | | sami_posts | | sami_term_relationships | | sami_term_taxonomy | | sami_terms | | sami_usermeta | | sami_users | | scott_cas_count | | scott_cas_image | | scott_comments | | scott_links | | scott_options | | scott_postmeta | | scott_posts | | scott_term_relationships | | scott_term_taxonomy | | scott_terms | | scott_usermeta | | scott_users | +--------------------------+ 39 rows in set (0.00 sec)