MySQL statement to show tables with 'cache' in the table name

drupal-cache-tables

#mysql> show tables from `mydrupaldb` where `tables_in_mydrupaldb` like '%cache%';
mysql> show tables like '%cache%'; # this works, for just a single pattern match
+----------------------------------+
| Tables_in_mydrupaldb             |
+----------------------------------+
| cache                            |
| cache_admin_menu                 |
| cache_apachesolr                 |
| cache_block                      |
| cache_bootstrap                  |
| cache_coffee                     |
| cache_ds_panels                  |
| cache_entity_comment             |
| cache_entity_file                |
| cache_entity_node                |
| cache_entity_og_membership       |
| cache_entity_og_membership_type  |
| cache_entity_taxonomy_term       |
| cache_entity_taxonomy_vocabulary |
| cache_entity_user                |
| cache_field                      |
| cache_filter                     |
| cache_form                       |
| cache_image                      |
| cache_libraries                  |
| cache_location                   |
| cache_media_xml                  |
| cache_menu                       |
| cache_page                       |
| cache_path                       |
| cache_rules                      |
| cache_token                      |
| cache_update                     |
| cache_variable                   |
| cache_vars                       |
| cache_views                      |
| cache_views_data                 |
| ctools_css_cache                 |
| ctools_object_cache              |
+----------------------------------+
34 rows in set (0.00 sec)