How to list and exclude table names from a
database in MySQL (exclude table names from an show tables in
MySQL) by using information_schema
Listing all table names from a
MySQL database is a very easy
and trivial task that every sql or system administrator out there
is aware of.
However
excluding certain table names from a whole list of
tables belonging to a database is not that commonly used and
therefore I believe many people have no clue how to do it when they
have to.
Today for one of my sql backup scripts it was necessary that
certain tables from a database to be excluded from the whole list
of tables for a database I'm backupping.
My example database has the sample name
exampledatabase and
usually I do list all the table contents from that database with
the well known command:
mysql> SHOW tables from
exampledatabase;
However as my desire was to exclude certain tables from the list
(preferrably with a certain SQL query) I had to ask around in
irc.freenode.net for some hints on a ways to achieve my exclude
table goals.
I was adviced by some people in
#mysql that what I need to
achieve my goal is the
information_schema mysql structure,
which is available since MySQL version 5.0.
After a bit of look around in the
information_schema and the
respective documentation on mysql.com, thanksfully I could
comprehend the idea behind the information_schema, though to be
honest the first time I saw the documentation it was completly
foggy on how to use this information_schema;
It seems using the information_schema is very easy and is not much
different from your normal queries syntax used to do trivial
operations in the mysql server.
If you wonder just like I did what is mysql's information_schema go
and use the information_schema database (which I believe is a
virtual database that is stored in the system memory).
For instance:
mysql> use information_schema;
Database changed
mysql> show tables
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
17 rows in set (0.00 sec)
To get a general view on what each of the tables in the
information_schema database contains I used the normal SELECT
command for example
mysql> select * from TABLES limit 10;
I used the
limit clause in order to prevent being overfilled
with data, where I could still see the table fields name to get
general and few lines of the table to get an idea what kind of
information the
TABLES table contains.
If you haven't got any ecperience with using the information_schema
I would advice you do follow my example select and look around
through all the listed tables in the
information_schema
database
That will also give you a few hints about the exact way the MySQL
works and comprehends it's contained data structures.
In short
information_schema virtual database and it's
existing tables provides a very thorough information and if you're
an SQL admin you certainly want to look over it every now and
then.
A bit of playing with it lead me to a command which is actually a
good substitute for the normal
SHOW TABLES; mysql
command.
To achieve a
SHOW TABLES from exampledatabase via the
information_schema info structure you can for example issue:
select TABLE_NAME from TABLES where
TABLE_SCHEMA='exampledatabase';
Now as I've said a few words about information_schema let me go
back to the main topic of this small article, which is
How to
exclude table names from a SHOW tables list
Here is how exclude a number of tables from a complete list of
tables belonging to a database:
select TABLE_NAME from TABLES where
TABLE_SCHEMA='exampledatabase' \
AND TABLE_NAME not in \
('mysql_table1_to_exlude_from_list',
'mysql_table2_to_exclude_from_list',
'table3_to_exclude');
In this example the above mysql command will list all the tables
content belonging to
exampledatabase and instruct the MySQL
server not to list the table names with names
mysql_table1_to_exlude_from_list,
mysql_table2_to_exclude_from_list, table3_to_exclude
If you need to exclude more tables from your mysql table listing
just add some more tables after the
...'table3_to_exclude',
'new_table4_to_exclude','etc..');
Of course this example can easily be adopted to a MySQL backup
script which requires the exclusion of certain tables from a backed
up database.
An example on how you can use the above table exclude command
straight from the bash shell would be:
debian:~# echo "use information_schema; select TABLE_NAME
from TABLES where \
TABLE_SCHEMA='exampledatabase' AND TABLE_NAME not in \
('mysql_table1_to_exlude_from_list',
'mysql_table2_to_exclude_from_list', 'table3_to_exclude',);"
\
| mysql -u root -p
Now this little bash one-liner can easily be customized to a backup
script to create backups of a certain databases with a certain
tables (e.g. with excluded number of tables) from the backup.
It's seriously a pity that by default the mysqldump command does
not have an option for a certain tables exclude while making a
database dump.
I've saw the mysqldump exclude option, being suggested somewhere
online as a future feature of mysqldump, I've also seen it
being reported in the mysql.com's bug database, I truly hope in the
upcoming releases we will see the exclude option to appear as a
possible mysqldump argument.