analyazing mysql column length using procedure analyse

Advertisements

We all do mysql optimization. It is important to adjust the column length for each field so that memory will not be wasted.

Suppose there is a table named people and there is a column email_id in it. We would like to know what is the current minimum and maximum values of this field. So that if its defined to high, we can bring it down. How we can check this?

We can use mysql procedure analyse to check this.

For eg:

mysql> select email_id from people procedure analyse();
+------------------------+------------+--------------------------+------------+------------+------------------+-------+-------------------------+------+----------------------+
| Field_name             | Min_value  | Max_value                | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std  | Optimal_fieldtype    |
+------------------------+------------+--------------------------+------------+------------+------------------+-------+-------------------------+------+----------------------+
| people.people.email_id | 1234234234 | abcdefline_94@hotmail.de |          4 |         39 |              287 |     0 | 18.6437                 | NULL | VARCHAR(39) NOT NULL |
+------------------------+------------+--------------------------+------------+------------+------------------+-------+-------------------------+------+----------------------+
1 row in set (0.00 sec)
mysql>


In the above example, we can see that minimum value of the email id is of length 4 and maximum value is 39. So if we had define varchar(100) for email_id, we can bring it down to varchar(60) or near to that.

How to restrict user agents in Apache Traffic Server

Advertisements

We all want to restrict access to our videos if the videos are used in a closed network or supposed to use only on a particular device. We can do this by restricting the access to only a particular user agent which can be your custom player or if you want to grant access only to IOS devices, then AppleWebkit.

In Apache traffic server how we can do this? This can be done using header_rewrite plugin.

How we can do it?

First we need to enable the plugin with a configuration file(s).

[root@trafficserver]# cat plugin.config

xdebug.so

header_rewrite.so config_file_1.conf

Then in the configuration file specified, we need to give the rules.

[root@trafficserver]# cat config_file_1.conf

cond %{READ_REQUEST_HDR_HOOK}

cond %{CLIENT-HEADER:User-Agent} /AppleWebkit./ [NOT]

set-status 403

After this, restart the traffic server and you are done.

This will deny access to all the user agents other than AppleWebkit. They will get 403 response.

Logstash shipper configuration for Apache access log and error log

Advertisements

Logstash is one of the best tool for the log analysis. We can create pie charts bar graphs and a lot of advanced charts using Logstash and Kibana.

We all use Apache webservers. How to send apache web server logs to Logstash? Easiest way is to use the logstash shipper.

You can use the following configuration file to ship apache access and error logs to a redis server which acts as a aggregator for logstash.

[root@test patterns]# cat /etc/logstash/shipper.conf
input {
file {
        start_position => beginning
        path => ["/var/log/httpd/access_log"]
        type => "apache-access"
        }
file {
        start_position => beginning
        path => ["/var/log/httpd/error_log"]
        type => "apache-error"
        }
}

filter {
 if [type] == "apache-access" {
  grok {
        match => { "message" => "%{COMBINEDAPACHELOG}" }
        add_tag => [ "vod_origin" ]
        }
}
 if [type] == "apache-error" {

  grok {
        patterns_dir => [ "/etc/logstash/patterns" ]
        match => [ "message", "%{APACHE_ERROR_LOG}" ]
        add_tag => [ "vod_origin" ]
        }
}
 geoip {
        source => "clientip"
        }
}

output {
stdout { }
redis {
        host => "xxx.xxx.xxx.xxx"
        data_type => "list"
        key => "logstash"
        }
}

[root@testpatterns]# cat /etc/logstash/patterns/apache-error
APACHE_ERROR_TIME %{DAY} %{MONTH} %{MONTHDAY} %{TIME} %{YEAR}
APACHE_ERROR_LOG \[%{APACHE_ERROR_TIME:timestamp}\] \[%{LOGLEVEL:loglevel}\] (?:\[client %{IPORHOST:clientip}\] ){0,1}%{GREEDYDATA:errormsg}

How to list and delete iptables rules with line numbers

Advertisements

If you are a Linux system administrator, many times you might wanted to list the iptables rules with line numbers and delete any rule with a particular line number. In this post we will see how we can list iptables rules with line numbers and how to delete a rule using line number

Listing iptables rules with line numbers
#iptables --list --line-numbers

Deleting a rule whose number is 3
#iptables -D INPUT 3

Thats it. Try to keep them in mind. They are very useful commands.