Archive for March 30th, 2011

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

Wednesday, March 30th, 2011

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.
 

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Posted in Linux, MySQL, Programming, System Administration | 2 Comments »

Speed up your DNS resolve if your Internet Service Provider DNS servers fail or resolve slowly / Privacy concerns of public DNS services use

Wednesday, March 30th, 2011

In my experience with many network Internet Service Providers by so far I’ve encountered a lot of DNS oddities and therefore surfing (web) and mail slowness.

It’s sometimes very irritating especially in cases, when I use my internet over Wireless public or university wireless networks.
In principle many of the Wireless routers which distribute the internet especially in organizations are badly configured and the slowness with DNS resolvings is an absolute classic.
If you haven’t encountered that slowness in opening web pages when connected from your University’s canteen, whether it’s fill with people for the lunch break, then I should say you’re really lucky!

My personal experience with this bad configured devices DNS services has been quite negative and every now and then I use to set and use public DNS servers like OpenDNS and Google DNS

Very often when I connect to a wireless network with my notebook running Debian Linux and the internet is too slow in opening pages I automatically set the Google or OpenDNS servers as a default DNS IP resolving servers.

1. DNS IP addresses of Google Public DNS are:

8.8.8.7
and
8.8.8.8

2. OpenDNS Public DNS servers has the IP addresses of:

208.67.222.222
208.67.222.220

I do set up and use the upper public DNS services addresses via the commands:

3. Set and use Google Public DNS services on my Linux debian:~# cp -rpf /etc/resolv.conf /etc/resolv.conf.orig
debian:~# echo "nameserver 8.8.8.7n nameserver 8.8.8.8 n" > /etc/resolv.conf;

I first create backu pof my resolv.conf under the name resolv.conf.orig just to make sure I can revert back to my old DNSes if I need them at some point.

If you prefer to use the OpenDNS services for some let’s say privacy reasons, you do it in the same manner as in the above commands, you only change the IP addresses. 4. Configure and use the OpenDNS public DNS services

debian:~# cp -rpf /etc/resolv.conf /etc/resolv.conf.orig
debian:~# echo "nameserver 208.67.222.222n nameserver 208.67.222.220 n" > /etc/resolv.conf;

Of course using Public DNS services has it’s disadvantages over the domain resolving speed up advantage.
One major issue is that Public DNS services are running on a top of a cloud and if you have red my previous article Cloud Computing a possible threat to users privacy and system administrator employment you might be agaist the idea of using a services which are powered by cloud.

The other primary concern is related to your SECURITY and a PRIVACY by using Public DNS networks, you risk that your Public DNS provider might use some DNS spoof techniques to mislead you and resolve you common domain names which usually resolve to let’s say 1.1.1.1 to let’s say 1.5.5.10

Even though this kind of practices on a side of a public DNS provider is not a likely scenario the possible implications of Public DNS providers using DNS forgery to fool you about domain names locations is a very serious issue.

As public DNS providers does contain again the good old philosophy of cloud computing embedded in themselves and they strive to become some kind of a standard which people might vote to adopt and use, the future implications of a wide adoption of Public DNS servers might be a terrible thing on internet users privacy!!!

Just think about a future scenario where we users of the Internet are forced to use a number of public DNS servers in order to use the Internet!
Usually a very huge companies are possessing the Public DNS services and do pay for the tech equipment required for building up the cluster clouds which provide the DNS services and therefore, if in the short future public DNS becomes a fashion and (God forbid!) a standard which shifts up the regular ISP DNS servers to resolve domains to IPs then it will be terrible.

The corporations which does own the Public DNS service/s might have a direct control over filtering and censoling information posted on any website on the internet.
Even worser if the world decides to adopt public DNS services somewhere in the future this means that large corporations owning the open dns cluster or clusters will be able to check each and every resolving made by any user on the net.
If you think closely such an information possessed by a company is not the best thing we want.

So let me close up this article, I’m not a fan and an evangelist who preaches the use of Public DNS services. Right on Contrary I do honestly hate the idea behind public DNS.
Nevertheless apart from my personal opinion I’m a practical person and using the public DNS servers every now and then when this will accelerate my access to the internet is still an option I do enjoy.

Maybe it’s time for a free software project (a tor like), which will provide users with an OpenDNS alternative which will run on hobbyist computers around the globe (just like with tor).

What’s rather funny is that the loud name OpenDNS is a big lie in reality OpenDNS is not opened it’s a company owned closed source service 😉

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Posted in Linux, Linux and FreeBSD Desktop, System Administration | 1 Comment »