MySQL: Which table is eating my Disk Space?!!!

If you are using mysql with innodb engine without files per table, and want to know which table is eating your disk space… here is the the way I have checked today:

mysql> SELECT CONCAT(table_schema, '.', table_name), 
         CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, 
         CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, 
         CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, 
         CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, 
         ROUND(index_length / data_length, 2) idxfrac 
FROM information_schema.TABLES 
         ORDER BY data_length + index_length 
         DESC 
         LIMIT 30;

Also if you want to find Size of each Database the:

mysql> select table_schema,
 concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') SIZE 
FROM TABLES 
 GROUP BY table_schema 
 ORDER BY sum(data_length+index_length) 
 desc;

:)

Freeradius – Problem with rlm_perl

There is a problem in debian/ubuntu that when you use rlm_perl module, freeradius will fail to start.

——
root@debian:/etc/freeradius# /etc/init.d/freeradius restart
Stopping FreeRADIUS daemon: freeradius/var/run/freeradius/freeradius.pid not found….
Starting FreeRADIUS daemon: freeradiusCan’t load ‘/usr/lib/perl5/auto/DBI/DBI.so’ for module DBI: /usr/lib/perl5/auto/DBI/DBI.so: undefined symbol: PL_memory_wrap at /usr/lib/perl/5.10/DynaLoader.pm line 192.
at /usr/lib/perl5/DBI.pm line 265
BEGIN failed–compilation aborted at /usr/lib/perl5/DBI.pm line 265.
Compilation failed in require at /etc/freeradius/dump.pl line 2.
BEGIN failed–compilation aborted at /etc/freeradius/dump.pl line 2.
failed!
——

after a little googleing I find out solution, all you need is to preload perl library:

LD_PRELOAD=/usr/lib/libperl.so.5.10.1 /usr/sbin/freeradius -X

Note: you will need to find out your perl library path:
# find /usr/lib/ -name “libperl.so*”

you also need to update your init script just like this :

LD_PRELOAD=/usr/lib/libperl.so.5.10.1 start-stop-daemon –start –quiet –pidfile $PIDFILE –exec $PROGRAM — $FREERADIUS_OPTIONS ……

Freeradius – check nested ldap group membership

if your organization have lots of users and groups , you also may use nested groups.
for example, UserA is a member of SalesGroup, and SalesGroup is a member of VPN_Group.
I want all members of VPN_GROUP able to connect to VPN Server. if you use normal groupmembership_filter in your ldap module , then UserA will not be able to authenticate as he is not a member of VPN_Group.
If you need such thing , then the only way (that I know) is making use of active directory Matching rule OID (LDAP_MATCHING_RULE_IN_CHAIN).

I made it work using following group membership query in ldap module:

groupmembership_filter = "(&(objectcategory=group)(member:1.2.840.113556.1.4.1941:=%{control:Ldap-UserDn}))"

read more here about ldap search filters.

Bash: String Manipulations

Bash supports a surprising number of string manipulation operations. If you use bash in your daily work, I’m pretty sure you will find these operations many useful and handy.

1. Counting Arguments: ${#}
You need to know with how many parameters the script was invoked. Use the shell built-in variable ${#}.

2. String Length: ${#parameter}
The length in characters of the value of parameter is substituted. If parameter is * or @, the value substituted is the number of positional parameters.

3.1 Shortest Substring Match: ${parameter#word}
Deletes the shortest match of word from front of parameter

3.2 Shortest Substring Match: ${parameter%word}
Deletes the shortest match of word from back of parameter

4.1 Longest Substring Match: ${parameter##word}
Deletes the longest match of word from front of parameter

4.2 Longest Substring Match: ${parameter%%word}
Deletes the Longest match of word from back of parameter

5.1 Substring Replacement: ${parameter/pattern/word}
Replace first match of pattern with string.

5.2 Substring Replacement: ${parameter//pattern/word}
Replace All matches of pattern with word.

6.1 Replace beginning and end: ${parameter/#pattern/word}
If pattern matches front end of parameter, substitute word for pattern.

6.2 Replace end and beginning: ${parameter/%pattern/word}
If pattern matches end end of parameter, substitute word for pattern.

You can find more Information and Examples regarding mentioned operators in following links:
http://tldp.org
http://www.linuxtopia.org
http://linuxgazette.net
http://www.thegeekstuff.com

Bash: Saving or Grouping Output from Several Commands

You want to capture the output with a redirect, but you’re typing several commands
on one line.
$ pwd; ls; cd ../elsewhere; pwd; ls > /tmp/all.out
The final redirect applies only to the last command, the last ls on that line. All the
other output appears on the screen (i.e., does not get redirected).

Solution:

Use braces { } to group these commands together, then redirection applies to the
output from all commands in the group. For example:

$ { pwd; ls; cd ../elsewhere; pwd; ls; } > /tmp/all.out

There are two very subtle catches here. The braces are actually
reserved words, so they must be surrounded by white space. Also, the
trailing semicolon is required before the closing space.
Alternately, you could use parentheses ( ) to tell bash to run the commands in a subshell,
then redirect the output of the entire subshell’s execution. For example:

$ (pwd; ls; cd ../elsewhere; pwd; ls) > /tmp/all.out

lsof: A Unix Utility You Should Know About

LSOF lists information about files opened by processes. An open file may be a regular file, a directory, a NFS file, a block special file, a character special file, a shared library, a regular pipe, a named pipe, a symbolic link, a socket stream, an Internet socket, a UNIX domain socket, and many others. Since almost everything in Unix is a file, you can imagine how incredibly useful lsof is!

lsof in action:

– Find who’s using a file:
lsof /path/to/file

– List of Open files Per Process:
lsof -p

– List of open Files Per User:
lsof -u

– List of Open File Descriptors:
lsof -d

– List of Open Internet protocols & ports:
lsof -i

– Directory Search :
lsof +D

– Find all open files by program’s name:
lsof -c

Examples:
# lsof -u admin,nasser
This will list all the files that are open by users admin and nasser.

# lsof -c httpd
It the list open files for processes whose name begins with httpd.

# lsof -a -u nasser -c tcsh
-a means AND
The output will be list of files opened by tcsh, which is run under nasser user privilege.

# lsof -u ^root
The ^ character before root username will negates the match and causes lsof print all open files by all users who are not root.

# lsof -p ^1010
List all open files by all the processes EXCEPT process with PID 1010.

# lsof -i tcp
List all TCP network connections.

# lsof -i udp
List all UDP network connections.

# lsof -i :22
The :22 option to -i makes lsof find processes using TCP or UDP port 25.

# lsof -i tcp:80
Finds who’s using a TCP port 80.

# lsof -a -u nasser -i
Will Find all network activity by user nasser.

# lsof -U
List all Unix domain socket files.

# lsof -g 1234
List all files for processes with a specific group id.

# lsof -r 5 -i tcp:22
The -r option makes lsof repeatedly list files until interrupted.

Have Fun.

Packet Lost on Linux Server !

Today Our NOC team report packet lost on my Linux server, I start to check problem by pinging my server from connected router and got this result :

router#ping linux-server repeat 1000

Type escape sequence to abort.
Sending 1000, 100-byte ICMP Echos to linux-server, timeout is 2 seconds:
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!.!!!!.!!!.!!!!!..!!!.!!!!!!!!!!!!!!.!!!..!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!.!..!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!..!!.
Success rate is 97 percent (579/596), round-trip min/avg/max = 1/1/4 ms

as you can see Success rate is 97 percent , and i have 3% packet lost from Connected router !

after login to server , I saw this messages on syslog :

Feb 5 12:49:31 linux kernel: ip_conntrack: table full, dropping packet.

[root@linux ~]# sysctl net.ipv4.netfilter.ip_conntrack_max
net.ipv4.netfilter.ip_conntrack_max = 65,536

It looks like the conntrack database doesn’t have enough entries for your environment. Connection tracking by default handles up to a certain number of simultaneous connections. This number is dependent on you system’s maximum memory size.

You can easily increase the number of maximal tracked connections, but be aware that each tracked connection eats about 350 bytes of non-swappable kernel memory!

To increase this limit :

[root@linux ~]# echo "net.ipv4.netfilter.ip_conntrack_max = 131072" >> /etc/sysctl.conf
[root@linux ~]# sysctl -p

Varnish cache Security – Removing Default Headers

If you want to completely remove your varnish cache footprints on HTTP Headers, flow this:

sub vcl_deliver {
...
...
    remove resp.http.X-Varnish;
    remove resp.http.Via;
    remove resp.http.Age;
    remove resp.http.X-Powered-By;
    unset obj.http.Server;
    set obj.http.Server = "Go Away"; 
...
...
}


sub vcl_error {
...
...
    unset obj.http.Server;
    set obj.http.Server = "Go Away"; 
...
...

Block POST Method with VARNISH for Invalid URLS

Recently, I’ve experienced very high load on my http server because of spam bots.
After some inspection on the server using tools like varnishtop , tcpdump, apache mod_log_post , I’ve realized that Web Server receives lots of invalid POST Requests.
as I have only few forms on the Web Server that uses POST method, I decide to Block ALL POST method REQUESTS except my forms , lets say the form urls is :
/upload/mainform.php
/form1.php
/form2.php
/form3.php

I just add thease lines to my Varnish configuration:

...
...

sub vcl_recv {

...
...

        if ( req.request == "POST" ) {
                if (       req.url ~ "/upload/mainform.php"
                        || req.url ~ "/form1.php"
                        || req.url ~ "/form2.php"
                        || req.url ~ "/form3.php"
                        ) {
                        return (pass);
                } else {
                        error 403 ": Requested Method is not supported by this server.";
                      
                }
        }
...
...

MySQL fatal error 1236

DB-1:

mysql> show master status;

File	Position	Binlog_Do_DB	Binlog_Ignore_DB
bin.007733	824963644

DB-2:

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO
MASTER_HOST=’DB-1′,
MASTER_USER=’replication’,
MASTER_LOG_FILE=’bin.007733′,
MASTER_LOG_POS=824963644;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

MySQL fatal error 1236