Monday, July 4, 2011

Working with IP's in MySQL and MariaDB

For MySQL it's a best practice to store IP addresses in a INT column rather than in a VARCHAR. This allows for more compact storage. This best practice is only for MySQL as PostgreSQL does have data types like inet and cidr.

MySQL is equiped with the INET_ATON() and INET_NTOA() functions since version 3.23.15. In MySQL 5.6.3 the INET6_ATON() and INET6_NTOA() functions were addes to finaly add IPv6 address support. To make it easier to work with IPv4 and IPv6 addresses the IS_IPV4_COMPAT(), IS_IPV4_MAPPED(), IS_IPV4() and IS_IPV6() functions were added. The difference between the new and old functions is that the old functions use INT UNSIGNED to store IP addresses and the new function uses VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses.

Here are some examples about how you could do nifty things with IP addresses in MySQL.

Store hosts and networks in MySQL
CREATE TABLE `hosts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hostname` varchar(255) DEFAULT NULL,
`domainname` varchar(255) DEFAULT NULL,
`ip` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
)

CREATE TABLE `networks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`network` int(10) unsigned NOT NULL,
`mask` int(10) unsigned NOT NULL,
`name` varchar(255) DEFAULT '',
PRIMARY KEY (`id`),
KEY `network` (`network`),
KEY `mask` (`mask`)
)

Insert and retrieve entries
mysql> INSERT INTO `hosts`(`hostname`,`domainname`,`ip`) VALUES('test6','example.com',INET_ATON('192.168.1.2'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----+----------+-------------+------------+
| id | hostname | domainname  | ip         |
+----+----------+-------------+------------+
|  6 | test6    | example.com | 3232235778 |
+----+----------+-------------+------------+
1 row in set (0.00 sec)

mysql> SELECT hostname,domainname,INET_NTOA(ip) ip FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----------+-------------+-------------+
| hostname | domainname  | ip          |
+----------+-------------+-------------+
| test6    | example.com | 192.168.1.2 |
+----------+-------------+-------------+
1 row in set (0.00 sec)

Convert a subnetmask to CIDR notation.
mysql> SELECT INET_ATON('255.255.255.0'),BIT_COUNT(INET_ATON('255.255.255.0'));
+----------------------------+---------------------------------------+
| INET_ATON('255.255.255.0') | BIT_COUNT(INET_ATON('255.255.255.0')) |
+----------------------------+---------------------------------------+
|                 4294967040 |                                    24 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION mask_to_cidr (mask CHAR(15)) RETURNS INT(2) DETERMINISTIC RETURN BIT_COUNT(INET_ATON(mask));
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mask='255.255.252.0';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @mask,mask_to_cidr(@mask);
+---------------+---------------------+
| @mask         | mask_to_cidr(@mask) |
+---------------+---------------------+
| 255.255.252.0 |                  22 |
+---------------+---------------------+
1 row in set (0.00 sec)

And convert a CIDR mask back to dot-decimal notation

mysql> CREATE FUNCTION cidr_to_mask (cidr INT(2)) RETURNS CHAR(15) DETERMINISTIC RETURN INET_NTOA(CONV(CONCAT(REPEAT(1,cidr),REPEAT(0,32-cidr)),2,10));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT cidr_to_mask(22);
+------------------+
| cidr_to_mask(22) |
+------------------+
| 255.255.252.0    |
+------------------+
1 row in set (0.00 sec)

Use the & operator to find the network address using an IP and mask.
mysql> SELECT INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3'));
+------------------------------------------------------------------+
| INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3')) |
+------------------------------------------------------------------+
| 192.168.2.0                                                      |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @netmask='255.255.255.0';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @ipaddr = '192.168.2.6';Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
1 row in set (0.00 sec)

This can be used to join a table with host ip's with a table of networks. (Remember the netmasks table in Solaris and/or NIS?)
mysql> SELECT CONCAT(h.hostname,'.',h.domainname) fqdn, INET_NTOA(h.ip) ip, INET_NTOA(n.mask & h.ip) network, INET_NTOA(n.mask) mask FROM hosts h INNER JOIN networks n ON (n.mask & h.ip) = n.network;
+-------------------+-------------+-------------+---------------+
| fqdn              | ip          | network     | mask          |
+-------------------+-------------+-------------+---------------+
| test1.example.com | 192.168.0.1 | 192.168.0.0 | 255.255.255.0 |
| test2.example.com | 192.168.0.2 | 192.168.0.0 | 255.255.255.0 |
| test3.example.com | 192.168.0.3 | 192.168.0.0 | 255.255.255.0 |
| test4.example.com | 10.0.0.1    | 10.0.0.0    | 255.0.0.0     |
| test5.example.com | 10.0.0.2    | 10.0.0.0    | 255.0.0.0     |
+-------------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

With the ~ operator the mask can be inverted and then be used to find the broadcast address using the XOR operator |.
mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network, CONV(CONV(SUBSTRING(CONV(~INET_ATON('255.255.255.0'),10,2),-32),2,10) | INET_ATON(@ipaddr),10,2) broadcast\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
broadcast: 11000000101010000000001011111111
1 row in set (0.00 sec)

You could use a view to make it easier for users to select the rows they wanted without using functions. But that will result in a full scan if you search using an IP. MySQL does not optimize this by first converting the search value to an INT but converts all rows to a dot-decimal notation and then compares it. MariaDB has a nifty feature called persistent virtual columns which allow indexing. For the example below this means that instead of scanning all four rows it just uses the index to find the row we need.

mysql> CREATE TABLE `iptest`(`ip_n` int(11) UNSIGNED NOT NULL, `ip_a` CHAR(15) AS (INET_NTOA(`ip_n`)) PERSISTENT, PRIMARY KEY(`ip_n`), KEY `ip_a` (`ip_a`));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `iptest`(`ip_n`) VALUES (INET_ATON('192.168.1.1')),(INET_ATON('192.168.1.2')),(INET_ATON('192.168.1.3')),(INET_ATON('192.168.1.4'));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `iptest`;
+------------+-------------+
| ip_n       | ip_a        |
+------------+-------------+
| 3232235777 | 192.168.1.1 |
| 3232235778 | 192.168.1.2 |
| 3232235779 | 192.168.1.3 |
| 3232235780 | 192.168.1.4 |
+------------+-------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | iptest | ref  | ip_a          | ip_a | 16      | const |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> CREATE VIEW v_iptest AS SELECT ip_n,INET_NTOA(ip_n) ip_a FROM `iptest`;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM `v_iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | iptest | index | NULL          | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

11 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Really nice!

    I have one question... For example when I have a table with lots of networks (and each having differrent netmask etc.)... How would a query look like when I want to find all networks containing a given IP ordered by netmask?

    Example:
    Networks:
    10.0.0.0 / 8
    10.0.2.0 / 24
    10.10.0.0 / 16

    And now I want to find the networks matching my IPs.
    when searching for 10.10.5.100 I want to get 10.10.0.0 / 16
    when searching for 10.0.2.100 I want to get 10.0.2.0 / 24
    when searching for 10.0.5.100 I want to get 10.0.0.0 / 8

    How would that work?

    Kind regards
    Martin

    ReplyDelete
  3. The every day payback rate won't be higher than 10% of day by day net deals, the day by day rate depends on the month to month charge cards deals volume and the measure of loan required. The payback time allotment is organized for a 6-9 months term, however it's not settled, and there won't be any punishments on the off chance that it takes longer. Cash Advances corona

    ReplyDelete
  4. Ceaselessly steady and an exceptional sense of duty regarding the universe of bloggers.
    192 .168.0.1

    ReplyDelete
  5. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. https://192-168-i-i.com

    ReplyDelete
  6. A lot of people having an incorrect image about the cash advance loans or sometimes refer it as bad credit payday loans. Plus d'information

    ReplyDelete
  7. I wish more authors of this type of content would take the time you did to research and write so well. I am very impressed with your vision and insight. bezoek website

    ReplyDelete
  8. Excellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing.  deze website

    ReplyDelete
  9. The data you have posted is extremely valuable. The locales you have alluded was great. A debt of gratitude is in order for sharing... privacyinthenetwork

    ReplyDelete
  10. You've completed in excellent work. t suggest to my frtends ind personilly wtll certitnly dtgtt. t'm conftdent they'll be gitned from thts webstte. privacyonline

    ReplyDelete
  11. Grandstream PBX System- Grandstream Distributor Cameroon VDS the Grandstream distributor in  Cameroon continues to bring innovative Grandstream Products to the IP communications / Telephony market with compelling values and features. Grandstream Networks is headquartered in Brookline, Massachusetts with offices in Yealink Office Phones

    ReplyDelete