Posts Tagged ‘trivial task’

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.
 

How to Split files on Linux FreeBSD, NetBSD and OpenBSD

Sunday, July 31st, 2011

Split large files in pieces Scissors

Did you have the need to sometimes split an SQL extra large files to few pieces in order to be able to later upload it via phpmyadmin?
Did you needed an extra large video or data file to be cut in few pieces in order to transfer it in few pieces over an USB stick?
Or just to give you an another scenario where I sometimes need to have an enormous file let’s say 3G split in few pieces, in order to later read it in vim or mcedit .
I sometimes need to achieve this on FreeBSD and Linux hosts thus I thought it will be helpful to somebody to give a very quick tutorial on the way large files can be cut in pieces on Linux and BSD hosts.

GNU/Linux and FreeBSD are equipped with the split command. The purpose of this command is exactly the cutting of a file to a number of pieces.

On Linux the split command comes by default install to the system with the coreutils package on most Debian (deb) based and Redhat based (rpm) distributions, theerefore Linux’s version of split is GNU/split since it’s part of the GNU Coreutils package. An interesting fact about Linux split is that one of the two programmers who has coded it is Richard Stallman 😉

On BSD Unix split is the AT&T UNIX (BSD) split

In the past splitting files in pieces was much more needed than today, as people used floppy drives to transfer data, though today with the bloom of Internet and the improve of the data carriers transferring even an extra large files from one place to another is a way more trivial task still at many occasions splitting it in pieces is needed.

Even though today splitting file is very rarely required, still there are times when being able to split a file in X number of parts is very much needed.
Maybe the most common use of splitting a file today is necessery when a large SQL file dumps, like let’s say 200 MBytes of info database needs to be moved from ane hosting provider to another one.
Many hosting providers does disallow direct access with standard mySQL client programs to the database directly and only allow a user to connect only via phpMyAdmin or some other web interface like Cpanel to improve data into the SQL or PostgreSQL server.

In such times, having knowledge on the Unix split command is a priceless asset.

Even though on Linux and BSD the code for the split command is not identical and GNU/split and BSD/split has some basic differences, the use of split on both of these Unices is identical.
The way to split a file in few pieces using on both Linux and BSD OSes is being done with one and the same command, here is how:

1. Splitting file in size of 40 mb On Linux

linux:~# split -b 40m SQL-Backup-Data.sql SQL-Backup-Data_split

2. Splitting file in size of 40mb on BSD (FreeBSD, OpenBSD, NetBSD)

freebsd# split -b 40m SQL-Backup-Data.sql SQL-Backup-Data_split

The Second argument the split command takes is actually called a prefix, the prefix is used as a basis name for the creation of the newly generated files cut in pieces file based on SQL-Backup-Data.sql.

As I said identical command will split the SQL-Backup-Data.sql files in a couple of parts which of it will be sized 40 megas.

These command will generate few files output like:

freebsd# ls -1 SQL-Backup-Dat*SQL-Backup-Data.sql
SQL-Backup-Dataa
SQL-Backup-Dataab
SQL-Backup-Dataac
SQL-Backup-Dataad
SQL-Backup-Dataae

As you see the SQL-Backup-Data.sql with size 200MB is being split in four files each of which is sized 40mbytes.

After the files are transfered to another Linux or BSD host, they can easily be again united in the original file with the command:

linux:~# for i in $(ls -1 SQL-Backup-Data_split*); echo $i >> SQL-Backup-Data.sql

Alternatively in most Unices also using cat should be enough to collect back the pieces into the original file, like so:

freebsd# cat SQL-Backup-Data_split* >> SQL-Backup-Data.sql

Enjoy splitting

How to make pptp VPN connection to use IPMI port (IPKVM / Web KVM) on Debian Linux

Wednesday, July 27th, 2011

If you have used KVM, before you certainly have faced the requirement asked by many Dedicated Server Provider, for establishment of a PPTP (mppe / mppoe) or the so called Microsoft VPN tunnel to be able to later access via the tunnel through a Private IP address the web based Java Applet giving control to the Physical screen, monitor and mouse on the server.

This is pretty handy as sometimes the server is not booting and one needs a further direct access to the server physical Monitor.
Establishing the Microsoft VPN connection on Windows is a pretty trivial task and is easily achieved by navigating to:

Properties > Networking (tab) > Select IPv4 > Properties > Advanced > Uncheck "Use default gateway on remote network".

However achiving the same task on Linux seemed to be not such a trivial, task and it seems I cannot find anywhere information or precise procedure how to establish the necessery VPN (ptpt) ms encrypted tunnel.

Thanksfully I was able to find a way to do the same tunnel on my Debian Linux, after a bunch of experimentation with the ppp linux command.

To be able to establish the IPMI VPN tunnel, first I had to install a couple of software packages, e.g.:

root@linux:~# apt-get install ppp pppconfig pppoeconf pptp-linux

Further on it was necessery to load up two kernel modules to enable the pptp mppe support:

root@linux:~# modprobe ppp_mppe
root@linux:~# modprobe ppp-deflate

I’ve also enabled the modules to be loading up during my next Linux boot with /etc/modules to not be bother to load up the same modules after reboot manually:

root@linux:~# echo ppp_mppe >> /etc/modules
root@linux:~# echo ppp-deflate >> /etc/modules

Another thing I had to do is to enable the require-mppe-128 option in /etc/ppp/options.pptp.
Here is how:

root@linux:~# sed -e 's$#require-mppe-128$require-mppe-128$g' /etc/ppp/options.pptp >> /tmp/options.pptp
root@linux:~# mv /tmp/options.pptp /etc/ppp/options.pptp
root@linux:~# echo 'nodefaultroute' >> /etc/ppp/options.pptp

In order to enable debug log for the ppp tunnel I also edited /etc/syslog.conf and included the following configuration inside:

root@linux:~# vim /etc/syslog.conf
*.=debug;
news.none;mail.none -/var/log/debug
*.=debug;*.=info;
*.=debug;*.=info;
root@linux:~# killall -HUP rsyslogd

The most important part of course is the command line with ppp command to connect to the remote IP via the VPN tunnel ;), here is how I achieved that:

root@linux:~# pppd debug require-mppe pty "pptp ipmiuk2.net --nolaunchpppd" file /etc/ppp/options.pptp user My_Dedi_Isp_Given_Username password The_Isp_Given_Password

This command, brings up the ppp interface and makes the tunnel between my IP and the remote VPN target host.

Info about the tunnel could be observed with command:

ifconfig -a ppp
ppp0 Link encap:Point-to-Point Protocol
inet addr:10.20.254.32 P-t-P:10.20.0.1 Mask:255.255.255.255
UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1496 Metric:1
RX packets:7 errors:0 dropped:0 overruns:0 frame:0
TX packets:12 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:3
RX bytes:70 (70.0 B) TX bytes:672 (672.0 B)

One more thing before I could finally access the IPMI’s web interface via the private IP was to add routing to the private IP address via the tunnel other side IP address:

# 10.20.0.1 P-t-P IP address
ip route add 10.20.1.124/32 dev ppp0

Now logically one would thing the Web interface to login and use the Java Applet to connect to the server would be accessible but no IT wasn’t !

It took me a while to figure out what is the problem and if not the guys in irc.freenode.net ##networking helped me I would never really find out why http://10.20.1.124/ and https://10.20.1.124/ were inaccessible.

Strangely enough both ports 80 and 443 were opened on 10.20.1.124 and it seems like working, however though I can ping both 10.20.1.124 and 10.20.0.1 there was no possible way to access 10.20.1.124 with TCP traffic.

Routing to the Microsoft Tunnel was fine as I’ve double checked all was fine except whether I tried accessing the IPMI web interface the browser was trying to open the URL and keeps opening like forever.

Thanksfully after a long time of futile try outs, a tip was suggested by a good guy in freenode nick named ne2k

To make the TCP connection in the Microsoft Tunnel work and consequently be able to access the webserver on the remote IPMI host, one needs to change the default MTU set for the ppp0 tunnel interface.
Here is how:


ip link set ppp0 mtu 1438

And tadam! It’s done now IPKVM is accessible via http://10.20.1.124 or https://10.20.1.124 web interface. Horay ! 🙂