How to store IP address value in integer datatype column.

Most of the time, we use string datatype column like char or varchar to store ip address values. There is a way to store ip address value in numeric datatype column like unsigned interger. There is miscellaneous functions INET_ATON(), INET_NTOA() using which it's possible

INET_ATON() Return the numeric value of an IP address

INET_NTOA() Return the IP address from a numeric value

SELECT INET_ATON('192.168.1.5') AS IP;
+------------+
| IP         |
+------------+
| 3232235781 |
+------------+
1 row in set (0.00 sec)

The generated number is always in network byte order. For example
192x256^3 + 168x256^2 + 1x256^1 + 5

SELECT INET_NTOA('3232235781') AS IP;
+-------------+
| IP          |
+-------------+
| 192.168.1.5 |
+-------------+
1 row in set (0.00 sec)

MySQL root user

MySQL root user is meant for database administrators for administration task. Many times, I found it's being used as replication user and normal user for making connections to mysql database.

One of my clients did the same thing and later it was fixed.

110329 12:01:13 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.003726' at position 850699172, relay log './poxxxx-relay-bin.000821' position: 11845356
110329 12:01:13 [Note] Slave I/O thread: connected to master 'root@xxx.xx.xx.xxx:3306',  replication started in log 'mysql-bin.003726' at position 850699172

It can be problematic in many scenario and mysql server restart can be required. It leads to below errors and don't allow connections to mysql database.

Too many connections

TEST: Default Schema

MySQL and it's fork (Percona Server, MariaDB) contains default schema (database) test. Many times, i found it missing from clients mysql-server. It has it's own importance.

1. On master slave replication setup with below configuration parameters.

binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema

This makes the test schema useful for benchmarking purpose (sysbench, mybench, supersmack) without replicating the benchmark data on slave or new schema can be created if slave benchmark required.

2. On clients server we have to work under restricted conditions and access. In that senario, test schema can be very useful.

3. It's a workspace for users to try and test.

Building Report Server

Although, there are many ways to setup a reporting server depending on the requirements. Sharing one of those.

Reporting Server provides reports i.e. daily, weekly, monthlty and quarterly reports to the clients. Data flow from many different servers (services) to one system called as Report Server. Client might be using more than one services. Therefore, it is necessary to collect the whole data at one place, analyze and generate reports for clients and billing purpose. This makes the data huge in TBs. Many times this server is used for billing clients for the services provided by the company. This makes this server very very critical and the data should be accurate.

Setup process has been breakup into few steps and explained below.

1. Storage Engine Selection:

The first and the most important is storage engine selection. Since, the data can be used for billing purpose. Therefore, it should be accurate and loosing data by crash is not acceptable at all. Mostly select, insert and delete (data purging discussed below) is required in this kind of setup. No update is required. Below command should be used for inserting data.

LOAD DATA INFILE ...

Since, this command is 30% faster than normal inserts.

Concurrent operations (select, insert) is required. As, innodb uses MVCC to achieve high concurrency and it's crash safe. Therefore, I've used innodb storage engine and recommend other's to use for critical activity like billing. Inoodb is a great Storage Engine !!!

2. Table Design:

The table must hold, all the important columns required for all the reports. Some times, it can have 15-20 columns. Partitioning is one of the great feature of MySQL 5.1 It provides good performace with data management. Many types of partitioning is avaliable. It depends which one fits into your senario. I have used range based partitioning on date column.

CREATE TABLE mt      
(  c1 bigint unsigned not null,
c2 varchar(30),
c3 datetime,
.
.
c9 date default NULL) engine=innodb
partition by range (to_days(c9))
(PARTITION p0 VALUES LESS THAN (to_days('2011-01-01')),
PARTITION p1 VALUES LESS THAN (to_days('2011-01-05')),
PARTITION p2 VALUES LESS THAN (to_days('2011-01-10')),
PARTITION p3 VALUES LESS THAN (to_days('2011-01-15')),
PARTITION p2 VALUES LESS THAN (to_days('2011-01-20')),
.
.
PARTITION p25 VALUES LESS THAN (to_days('2011-03-25')),
PARTITION p26 VALUES LESS THAN (to_days('2011-03-30')),
);

3. Purging Data: In order to make query operation faster, Old data should be purged on a regular basis. Dropping partiton is the best and fastest way to purge old data. But, make sure that this operation is performed at off peak hours. Otherwise, it can block the whole table. While dropping partition from the top and adding partition from the bottom, we can maintain some specific duration data for eg 3 months.

In most cases alter table command rebuild the whole table. But, not in below cases.

ALTER TABLE mt DROP PARTITION ...
ALTER TABLE mt ADD PARTITION ...

The above task can be automated (details can be found in information_schema) using procedure and cronjob or mysql event.

4. Replication: Mysql doesn't support many to one replication. Therfore, script is required to transfer the data from many servers (services) to one servers (Report Server)

5: Intelligent Application: As, we know that count is slow in innodb. Therefore, application can be designed in such a way that report for more than a month can be scheduled. The result can be send to client using sms or email.

I've build a reporting server with 2TB+ data and still growing. The ratio of data and index is 6:4