Command Line Magic

Imagine we have a Linux-based web server using MySQL, but phpMyAdmin is not available for maintaining the databases. In that case we can log into the server via SSH and use the command line instead. For a brief summary of basic MySQL commands see e.g. here.

Individual tables or entire databases can be easily removed using the drop command, but there is no straightforward way to delete a large number of tables from a database. I encountered this problem when attempting to upgrade a Joomla installation from v1.5 to v2.5. When I wasn't happy with the result of an upgrade attempt, I would first make tabula rasa by deleting the newly created mysql tables before making another attempt. Let's say the database in question looks like this:

 

$ mysql -u myusername -p mydatabase
mysql> show tables;
+-----------------------------+
| Tables_in_mydatabase        |
+-----------------------------+
| bak_banner                  |
| bak_categories              |
| bak_menu                    |
| bak_users                   |
| j25_banner                  |
| j25_categories              |
| j25_menu                    |
| j25_users                   |
| jos_banner                  |
| jos_categories              |
| jos_menu                    |
| jos_users                   |
| jupgrade_categories         |
| jupgrade_menus              |
| jupgrade_modules            |
| jupgrade_steps              |
+-----------------------------+

My actual database was much larger, but for clarity I've shortened the list for this article. The tables belonging to the Joomla upgrade which we want to drop have the prefix j25_. We can use the wildcard % to list them separately:

 

mysql> show tables like 'j25_%';
+-----------------------------+
| Tables_in_mydatabase (j25_%)|
+-----------------------------+
| j25_banner                  |
| j25_categories              |
| j25_menu                    |
| j25_users                   |
+-----------------------------+

Unfortunately, we can't combine the wildcard % with the drop command to remove these tables in one go, so instead we create a simple script. On the shell we create a text file with the relevant tables:

$ mysql -u myusername -p mydatabase -e "show tables like 'j25_%'" > j25list.txt

Now j25list.txt contains the list of tables we want to drop. Before feeding back that list to mysql, we need to make some cosmetic changes: The first line in j25list.txt needs to be replaced with the string DROP TABLE, and all other lines need to end with commas, except the last line which ends with a semicolon. The following unwieldy sed command will accomplish this:

sed 's/$/,/;$s/.$/;/;1d;2i\DROP TABLE' j25list.txt > j25list.sql

Now j25list.sql contains a mysql-readable script. We feed this back to mysql via

$ mysql -u myusername -p mydatabase < j25list.sql

And we're done!