DHCP MYSQL: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
 
(не показано 86 промежуточных версий этого же участника)
Строка 1: Строка 1:
  +
[[Категория:Linux]]
  +
[[Категория:DHCP]]
  +
[[Категория:MySQL]]
 
=Конфиг для DHCP средсвами MYSQL=
  
=Конфиг для DHCP средсвами MYSQL=
 
Есть желание уйти от всяких скриптов (php, bash, perl) для получения конфига из MySQL для dhcpd а так же с помошью представлений отказаться от привязки к конкретным таблицам.
  
Есть желание уйти от всяких скриптов (php, bash, perl) для получения конфига из MySQL для dhcpd а так же с помошью представлений отказаться от привязки к конкретным таблицам.
 
Заодно проверить какое из решений будет работать быстрее.
  
Заодно проверить какое из решений будет работать быстрее.
  +
<BR><B>Это только черновик пока и я еще не использую это решение в продакшене</B>
  +
<BR>
  +
<B>TODO</B>
  +
* Проверить что бы использовалась только таблица mtraf_status
   
 
==Создание представдения==
  
==Создание представдения==
 
Для того что бы не быть привязаным к структуре базы (которую программист биллинга вообще-то может менять по своему усмотрению) все данные для dhcp буду получать из view.
  
Для того что бы не быть привязаным к структуре базы (которую программист биллинга вообще-то может менять по своему усмотрению) все данные для dhcp буду получать из view.
  +
Стараюсь хранить данные в виде их "физических сущностей" - а именно IP - как INT UNSIGNED. В некоторых случаях приходиться брать данные "как есть" и преобразовывать.<BR>
−
<P>
  
  +
−
Стараюсь хранить данные в виде их "физических сущностей" - а именно IP - как INT UNSIGNED
  
 
<PRE>
  
<PRE>
 
DROP view mtraf_status;
  
DROP view mtraf_status;
Строка 18: Строка 25:
 
mac AS mac,
  
mac AS mac,
 
IF( 0n_a<0 OR ( 0n_a=0 AND debt=0), inet_ntoa(inet_aton(ip)+6553600),ip) AS ip_dhcp,
  
IF( 0n_a<0 OR ( 0n_a=0 AND debt=0), inet_ntoa(inet_aton(ip)+6553600),ip) AS ip_dhcp,
−
lower(substr(replace(concat(':', mac), ':0', ':') from 2)) AS mac_dhcp ,
 +
lower(substr(replace(concat(':', mac), ':0', ':') from 2)) AS mac_dhcp
−
INET_NTOA(network_mask.network) AS network
  
 
FROM
  
FROM
−
mtraf,
 +
mtraf;
  +
−
network_mask
  
  +
</PRE>
−
WHERE
  
  +
Условие в IF - условие когда абонент "должен" - ему нужно дать другой IP (у меня вместо, например, 10.0.136.10 он получит 10,100,136,10) и соответвенно, пускать только на биллинг (если короко, то файрволл не пускает абонентов никуда кроме отдельного ДНСа (или отдельного view) который на все запросы отвечает адресом странички "дай денег, дорогой абонент" и собственно биллинга) Всем кто будет напоминать про dhcp snooping, opt 82 и прочее - это все делается для остатоков неуправляемых сегментов.
−
INET_ATON(mtraf.ip)&network_mask.mask=network_mask.network;
  
  +
<PRE>
  +
mysql> SELECT * from mtraf_status limit 10;
  +
+------------+--------+----------------+-------------------+----------------+------------------+
  +
| domain | status | ip | mac | ip_dhcp | mac_dhcp |
  +
+------------+--------+----------------+-------------------+----------------+------------------+
  +
| 007 | 1 | 10.0.135.49 | 00:50:BF:B1:3E:44 | 10.0.135.49 | 0:50:bf:b1:3e:44 |
  +
| 01125 | 0 | 10.100.82.186 | FA:CE:0F:FF:A1:11 | 10.200.82.186 | fa:ce:f:ff:a1:11 |
  +
| 01496 | 1 | 10.0.142.81 | 00:21:91:21:8B:A7 | 10.0.142.81 | 0:21:91:21:8b:a7 |
  +
| 04079 | 1 | 10.0.86.33 | 00:04:4B:80:80:03 | 10.0.86.33 | 0:4:4b:80:80:3 |
  +
| 060696 | 1 | 10.5.6.73 | 00:1D:60:47:B7:4F | 10.5.6.73 | 0:1d:60:47:b7:4f |
  +
| 060708 | 1 | 10.0.88.55 | 00:24:8C:C1:94:AD | 10.0.88.55 | 0:24:8c:c1:94:ad |
  +
| 090403 | 1 | 10.0.96.13 | 00:17:31:65:D7:86 | 10.0.96.13 | 0:17:31:65:d7:86 |
  +
| 0lenka | 1 | 10.4.225.63 | 00:17:31:9A:0A:26 | 10.4.225.63 | 0:17:31:9a:a:26 |
  +
| 1003 | 1 | 10.4.9.50 | 00:1F:E2:50:50:75 | 10.4.9.50 | 0:1f:e2:50:50:75 |
  +
| 1004210045 | 0 | 10.100.120.139 | FA:CE:0F:FF:A1:11 | 10.200.120.139 | fa:ce:f:ff:a1:11 |
  +
+------------+--------+----------------+-------------------+----------------+------------------+
  +
10 rows in set (0.00 sec)
  +
</PRE>
  +
  +
==Дополнительные данные о сети==
  +
В биллинге (в моем случае) нет никакой информации о топологии сети).
  +
Нужно добавить данные о сетях, релеях, гейтвеях. Отмечу, что в сети есть как минимум 2 релея (т.к. сети 10.0... и 10.100... - алиасы ) и запрос прийти может через любой из них. В общем случае релеев может быть сколько угодно - по числу алиасов на интерфейсе.
  +
  +
<PRE>
  +
DROP TABLE network_relay;
  +
CREATE TABLE network_relay
  +
(
  +
network int(64) UNSIGNED,
  +
relay int(64) UNSIGNED
  +
);
  +
  +
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.1'));
  +
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.254'));
  +
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.100.135.1'));
  +
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.100.135.254'));
  +
  +
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.1'));
  +
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.254'));
  +
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.100.136.1'));
  +
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.100.136.254'));
  +
  +
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.1'));
  +
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.254'));
  +
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.100.137.1'));
  +
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.100.137.254'));
  +
</PRE>
  +
  +
  +
  +
<PRE>
  +
DROP TABLE network_mask;
  +
CREATE TABLE network_mask
  +
(
  +
network int UNSIGNED NOT NULL UNIQUE,
  +
mask int UNSIGNED,
  +
is_dhcp int DEFAULT 1
  +
);
  +
  +
INSERT into network_mask(network,mask) values(INET_ATON('10.0.135.0'), INET_ATON('255.255.255.0'));
  +
INSERT into network_mask(network,mask) values(INET_ATON('10.0.136.0'), INET_ATON('255.255.255.0'));
  +
INSERT into network_mask(network,mask) values(INET_ATON('10.0.137.0'), INET_ATON('255.255.255.0'));
  +
</PRE>
  +
  +
  +
<PRE>
  +
DROP TABLE network_gateway;
  +
CREATE TABLE network_gateway
  +
(
  +
network int UNSIGNED,
  +
gateway int UNSIGNED
  +
);
  +
  +
INSERT into network_gateway values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.254'));
  +
INSERT into network_gateway values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.254'));
  +
INSERT into network_gateway values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.254'));
  +
  +
INSERT into network_gateway values(INET_ATON('10.100.135.0'), INET_ATON('10.100.135.254'));
  +
INSERT into network_gateway values(INET_ATON('10.100.136.0'), INET_ATON('10.100.136.254'));
  +
INSERT into network_gateway values(INET_ATON('10.100.137.0'), INET_ATON('10.100.137.254'));
  +
</PRE>
  +
  +
==Создание индексов==
  +
На тадицы я дополнительно создал индексы. По результатов моих тестов это не внесло изменений в производительность, потому допускаю что индекс созданы неоптимально.
  +
У таблцы mtraf (таблица с данными).
  +
<PRE>
  +
KEY `idx_ip` (`ip`),
  +
KEY `idx_0n_a` (`0n_a`),
  +
KEY `idx_debt` (`debt`),
  +
KEY `idx_status` (`debt`,`0n_a`),
  +
KEY `idx_mac` (`mac`)
  +
</PRE>
  +
У таблиц с дополнительными данными - сети, релеи и гейтвеи
  +
<PRE>
  +
KEY `idx_network` (`network`)
  +
</PRE>
  +
  +
==Процедуры и функции==
  +
Решил максимально разбить задачу на отдельные атомарные функции, MySQL к это теперь позволяет
  +
===Вспомогательные функции===
  +
  +
====Вычисление броадкаста====
  +
Тривиальная задача зная ip и маску. Для простоты вынес в отдельную ф-ю.
  +
<PRE>
  +
DROP FUNCTION GetBroadcast;
  +
delimiter //
  +
CREATE FUNCTION GetBroadcast(n1 int UNSIGNED, m1 INT UNSIGNED) RETURNS INT UNSIGNED
  +
BEGIN
  +
DECLARE b1 INT UNSIGNED;
  +
SELECT n1+POWER(2,32-BIT_COUNT(m1))-1 INTO b1;
  +
RETURN b1;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
====Получение шлюза для сети====
  +
Списки шлюзов храню отдельно, для определения какой шлюз передать клиенту использую эту ф-ю
  +
<PRE>
  +
DROP FUNCTION GetGateway;
  +
delimiter //
  +
CREATE FUNCTION GetGateway(n1 int UNSIGNED) RETURNS INT UNSIGNED
  +
BEGIN
  +
DECLARE g1 INT UNSIGNED;
  +
SELECT gateway FROM network_gateway where network=n1 LIMIT 1 INTO g1;
  +
RETURN g1;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
====Получение маски для IP====
  +
Зная IP получаем маску сети (которая в общем случае может отличаться от "255.255.255.0". Возвращаю первое вхождение.
  +
<PRE>
  +
DROP FUNCTION GetMaskForIP;
  +
delimiter //
  +
CREATE FUNCTION GetMaskForIP(ip1 int(64) UNSIGNED) RETURNS INT UNSIGNED
  +
BEGIN
  +
DECLARE mask1 INT UNSIGNED;
  +
SELECT mask from network_mask WHERE ip1&mask=network LIMIT 1 INTO mask1;
  +
RETURN mask1;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
Пример:
  +
<PRE>
  +
mysql> SELECT INET_NTOA(GetMaskForIP(INET_ATON('10.0.136.12')));
  +
+---------------------------------------------------+
  +
| INET_NTOA(GetMaskForIP(INET_ATON('10.0.136.12'))) |
  +
+---------------------------------------------------+
  +
| 255.255.255.0 |
  +
+---------------------------------------------------+
  +
1 row in set (0.01 sec)
  +
</PRE>
  +
  +
Аналогично для "неплатильщиков"
  +
<PRE>
  +
DROP FUNCTION GetMaskForNoPayIP;
  +
delimiter //
  +
CREATE FUNCTION GetMaskForNoPayIP(ip1 int(64) UNSIGNED) RETURNS INT UNSIGNED
  +
BEGIN
  +
DECLARE mask1 INT UNSIGNED;
  +
SELECT mask from network_mask WHERE (ip1-6553600)&mask=network limit 1 INTO mask1;
  +
RETURN mask1;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
====Определить какой сети принадлежит IP====
  +
  +
<PRE>
  +
DROP FUNCTION GetNetForIP;
  +
delimiter //
  +
CREATE FUNCTION GetNetForIP(ip1 int UNSIGNED) RETURNS int UNSIGNED
  +
BEGIN
  +
DECLARE net1 INT UNSIGNED;
  +
SELECT network INTO net1 FROM network_mask WHERE ip1&mask=network LIMIT 1;
  +
RETURN net1;
  +
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
Пример:
  +
<PRE>
  +
mysql> SELECT INET_NTOA(GetNetForIP(INET_ATON('10.0.136.11')));
  +
+--------------------------------------------------+
  +
| INET_NTOA(GetNetForIP(INET_ATON('10.0.136.11'))) |
  +
+--------------------------------------------------+
  +
| 10.0.136.0 |
  +
+--------------------------------------------------+
  +
1 row in set (0.00 sec)
  +
</PRE>
  +
  +
===Генерация CLASS===
  +
====Получение списка relay для сети====
  +
В сети запрос может прийти от одного или нескольких возможных relay-агентов. Для каждой сети - определен список этих relay. Задача - сгенерировать часть конфига вида "запрос пришел через релей1 ИЛИ запрос пришел через релей2 ИЛИ ..."
  +
  +
<PRE>
  +
DROP FUNCTION GetRelayForNet;
  +
delimiter //
  +
CREATE FUNCTION GetRelayForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
-- done is "end of cycle" variable
  +
DECLARE done INT DEFAULT 0;
  +
DECLARE current_relay MEDIUMTEXT default "";
  +
DECLARE dhcp_relay MEDIUMTEXT;
  +
DECLARE dhcp_relay_res MEDIUMTEXT default "";
  +
-- set done=1 when cusror ends
  +
-- cursor for relay
  +
DECLARE cursor_relay CURSOR FOR SELECT relay from network_relay WHERE network=n1 LIMIT 1,18446744073709551615;
  +
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
OPEN cursor_relay;
  +
SELECT INET_NTOA(relay) INTO dhcp_relay FROM network_relay WHERE network=n1 LIMIT 1;
  +
SELECT CONCAT("( ( binary-to-ascii(10, 8, \".\", packet(24, 4))=\"", dhcp_relay, "\" ) ") INTO dhcp_relay_res;
  +
FETCH cursor_relay INTO current_relay;
  +
WHILE done = 0 DO
  +
SELECT INET_NTOA(relay) INTO dhcp_relay FROM network_relay WHERE relay = current_relay LIMIT 1;
  +
SELECT CONCAT(dhcp_relay_res, " or (binary-to-ascii(10, 8, \".\", packet(24, 4))=\"", dhcp_relay,"\")") INTO dhcp_relay_res;
  +
FETCH cursor_relay INTO current_relay;
  +
END WHILE;
  +
CLOSE cursor_relay;
  +
RETURN CONCAT(dhcp_relay_res, ")");
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
Пример:
  +
<PRE>
  +
SELECT GetRelayForNet(INET_ATON('10.0.136.0'));
  +
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  +
| GetRelayForNet(INET_ATON('10.0.136.0')) |
  +
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  +
| ( ( binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.1" ) or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.254") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.1") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.254")) |
  +
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  +
1 row in set (0.00 sec)
  +
</PRE>
  +
  +
==== Заготовка class для IP====
  +
Для простоты разделелил генерацию класса на несколько частей <BR>
  +
Эта функция возващает заготовку класса (match mac-address) для определенного IP.
  +
<PRE>
  +
DROP FUNCTION GetClassForIP;
  +
delimiter //
  +
CREATE FUNCTION GetClassForIP(ip1 int UNSIGNED) RETURNS char(255)
  +
BEGIN
  +
DECLARE mac_dhcp1 char(255) DEFAULT "";
  +
DECLARE ip_d char(255) DEFAULT "";
  +
SELECT ip_dhcp INTO ip_d FROM mtraf_status WHERE ip=INET_NTOA(ip1);
  +
SELECT mac_dhcp INTO mac_dhcp1 FROM mtraf_status WHERE mtraf_status.ip=ip_d LIMIT 1;
  +
RETURN CONCAT("class \"",ip_d,"__",mac_dhcp1,"\" { match if ( ( binary-to-ascii(16, 8, \":\", substring(hardware,1, 6)) = \"",mac_dhcp1,"\") and");
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
Пример
  +
<PRE>
  +
mysql> SELECT GetClassForIP(INET_ATON('10.0.136.11'));
  +
+------------------------------------------------------------------------------------------------------------------------------------+
  +
| GetClassForIP(INET_ATON('10.0.136.11')) |
  +
+------------------------------------------------------------------------------------------------------------------------------------+
  +
| class "10.0.136.11__0:13:d4:5:d9:f4" { match if( ( binary-to-ascii(16, 8, ":", substring(hardware,1, 6)) = "0:13:d4:5:d9:f4 and |
  +
+------------------------------------------------------------------------------------------------------------------------------------+
  +
1 row in set (0.10 sec)
  +
</PRE>
  +
  +
====Генерация полноценного класса====
  +
Собираем полноценный класс из частей.
  +
<PRE>
  +
DROP FUNCTION GetFullClassForIP;
  +
delimiter //
  +
CREATE FUNCTION GetFullClassForIP(ip1 int UNSIGNED) RETURNS TEXT
  +
BEGIN
  +
RETURN CONCAT( GetClassForIP(ip1), " " , GetRelayForNet(GetNetForIP(ip1)) ,"}\n" ) ;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
Пример (читать так: Классу соответвует запрос с маком $mac прошедьший через $relay[1] или $relay[2] или ... или relay[N] ). В такой конфигурации мак должен быть уникален в пределах сегмента а не в пределах всей сети:
  +
<PRE>
  +
mysql> SELECT GetFullClassForIP(INET_ATON('10.0.136.11'));
  +
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  +
| GetFullClassForIP(INET_ATON('10.0.136.11')) |
  +
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  +
| class "10.0.136.11__0:13:d4:5:d9:f4" { match if( ( binary-to-ascii(16, 8, ":", substring(hardware,1, 6)) = "0:13:d4:5:d9:f4 and ( ( binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.1" ) or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.254") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.1") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.254"))}
  +
|
  +
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  +
1 row in set (0.08 sec)
  +
</PRE>
  +
  +
  +
  +
===Создание конфига SUBNET===
  +
====создание пула для IP====
  +
<PRE>
  +
DROP FUNCTION GetPoolForIP;
  +
delimiter //
  +
CREATE FUNCTION GetPoolForIP(ip1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE mac_dhcp1 VARCHAR(255);
  +
SELECT mac_dhcp INTO mac_dhcp1 FROM mtraf_status WHERE INET_ATON(mtraf_status.ip)=ip1 LIMIT 1;
  +
RETURN CONCAT(
  +
"pool { range ",
  +
INET_NTOA(ip1),
  +
"; allow members of \"",
  +
INET_NTOA(ip1),
  +
"__",
  +
mac_dhcp1,
  +
"\"; default-lease-time 36000; max-lease-time 72000; } "
  +
);
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
====Cоздание пула для IP неплатильщика====
  +
<PRE>
  +
DROP FUNCTION GetPoolForNoPayIP;
  +
delimiter //
  +
CREATE FUNCTION GetPoolForNoPayIP(ip1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE ip_dhcp1 VARCHAR(32) DEFAULT "";
  +
DECLARE mac_dhcp1 VARCHAR(255) DEFAULT "";
  +
SELECT INET_NTOA(ip1) INTO ip_dhcp1;
  +
SELECT mac_dhcp INTO mac_dhcp1 FROM mtraf_status WHERE INET_ATON(mtraf_status.ip)=ip1-6553600 LIMIT 1;
  +
RETURN CONCAT(
  +
"pool { range ",
  +
ip_dhcp1,
  +
"; allow members of \"",
  +
ip_dhcp1,
  +
"__",
  +
mac_dhcp1,
  +
"\"; default-lease-time 36000; max-lease-time 72000; } "
  +
);
  +
END
  +
//
  +
delimiter ;
  +
SELECT GetPoolForNoPayIP(INET_ATON('10.100.136.84'));
  +
</PRE>
  +
Пример:
  +
<PRE>
  +
mysql> SELECT GetPoolForNoPayIP(INET_ATON('10.100.136.84'));
  +
+---------------------------------------------------------------------------------------------------------------------------------+
  +
| GetPoolForNoPayIP(INET_ATON('10.100.136.84')) |
  +
+---------------------------------------------------------------------------------------------------------------------------------+
  +
| pool { range 10.100.136.84; allow members of 10.100.136.84__0:1d:92:58:27:cf default-lease-time 36000; max-lease-time 72000; } |
  +
+---------------------------------------------------------------------------------------------------------------------------------+
  +
1 row in set (0.04 sec)
  +
</PRE>
  +
  +
==== Полные пулы для тех кто платит и тех кто нет (из одногй сети)====
  +
Получить пулы для тех кто платит
  +
<PRE>
  +
DROP FUNCTION GetAllPoolsForNet;
  +
delimiter //
  +
CREATE FUNCTION GetAllPoolsForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE done INT DEFAULT 0;
  +
DECLARE mask1 int UNSIGNED;
  +
DECLARE current_ip int UNSIGNED DEFAULT 0;
  +
DECLARE pools MEDIUMTEXT DEFAULT " ";
  +
DECLARE cursor_ip CURSOR FOR SELECT INET_ATOn(ip_dhcp) from mtraf_status WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n1;
  +
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
SELECT mask from network_mask WHERE network=n1 INTO mask1;
  +
OPEN cursor_ip;
  +
FETCH cursor_ip INTO current_ip;
  +
WHILE done = 0 DO
  +
SELECT CONCAT(GetPoolForIP(current_ip)," \n",pools) INTO pools;
  +
FETCH cursor_ip INTO current_ip;
  +
END WHILE;
  +
CLOSE cursor_ip;
  +
RETURN pools;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
Версия ф-и которую мне подсказал Серж Шевченко (оптимизирована по производительности)
  +
  +
<PRE>
  +
DROP FUNCTION GetAllPoolsForNet;
  +
delimiter //
  +
CREATE FUNCTION GetAllPoolsForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE done INT DEFAULT 0;
  +
DECLARE mask1 int UNSIGNED;
  +
DECLARE mac_dhcp1 VARCHAR(255);
  +
DECLARE current_ip int UNSIGNED DEFAULT 0;
  +
DECLARE pools MEDIUMTEXT DEFAULT " ";
  +
DECLARE cursor_ip CURSOR FOR SELECT INET_ATOn(ip_dhcp),mac_dhcp from mtraf_status WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n1;
  +
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
SELECT mask from network_mask WHERE network=n1 INTO mask1;
  +
OPEN cursor_ip;
  +
FETCH cursor_ip INTO current_ip,mac_dhcp1;
  +
WHILE done = 0 DO
  +
SELECT CONCAT(
  +
"pool { range ",
  +
INET_NTOA(current_ip),
  +
"; allow members of \"",
  +
INET_NTOA(current_ip),
  +
"__",
  +
mac_dhcp1,
  +
"\"; default-lease-time 36000; max-lease-time 72000; } "
  +
," \n",pools) INTO pools;
  +
FETCH cursor_ip INTO current_ip,mac_dhcp1;
  +
END WHILE;
  +
CLOSE cursor_ip;
  +
RETURN pools;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
  +
Пример (часть поскипано):
  +
  +
<PRE>
  +
mysql> SELECT GetAllPoolsForNet(INET_ATON('10.0.136.0'));
  +
...
  +
pool { range 10.0.136.154; allow members of 10.0.136.154__0:e:2e:98:32:c default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.153; allow members of 10.0.136.153__0:15:f2:5:44:29 default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.152; allow members of 10.0.136.152__0:e0:1c:3c:33:cd default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.151; allow members of 10.0.136.151__0:e0:1c:3b:d6:39 default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.150; allow members of 10.0.136.150__0:19:d1:e6:1e:2c default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.149; allow members of 10.0.136.149__0:18:f3:fb:fe:b6 default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.174; allow members of 10.0.136.174__0:26:2d:5f:97:c1 default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.148; allow members of 10.0.136.148__0:26:9e:75:50:d2 default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.147; allow members of 10.0.136.147__0:a0:d1:ca:47:b default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.145; allow members of 10.0.136.145__0:e0:4c:3:d7:55 default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.146; allow members of 10.0.136.146__0:1f:c6:4f:ed:7a default-lease-time 36000; max-lease-time 72000; }
  +
...
  +
1 row in set (13.79 sec)
  +
</PRE>
  +
  +
И тех кто не платит
  +
<PRE>
  +
DROP FUNCTION GetAllPoolsForNoPayNet;
  +
delimiter //
  +
CREATE FUNCTION GetAllPoolsForNoPayNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE done INT DEFAULT 0;
  +
DECLARE mask1 int UNSIGNED;
  +
DECLARE current_ip int UNSIGNED DEFAULT 0;
  +
DECLARE pools MEDIUMTEXT DEFAULT " ";
  +
  +
DECLARE cursor_ip CURSOR FOR SELECT INET_ATON(ip_dhcp) from mtraf_status WHERE (INET_ATON(mtraf_status.ip_dhcp))&mask1=(n1+6553600);
  +
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
SELECT mask from network_mask WHERE network=n1 INTO mask1;
  +
OPEN cursor_ip;
  +
FETCH cursor_ip INTO current_ip;
  +
WHILE done = 0 DO
  +
SELECT CONCAT(GetPoolForNoPayIP(current_ip)," \n",pools) INTO pools;
  +
FETCH cursor_ip INTO current_ip;
  +
END WHILE;
  +
CLOSE cursor_ip;
  +
RETURN pools;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
<PRE>
  +
DROP FUNCTION GetAllPoolsForNoPayNet;
  +
delimiter //
  +
CREATE FUNCTION GetAllPoolsForNoPayNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE done INT DEFAULT 0;
  +
DECLARE mask1 int UNSIGNED;
  +
DECLARE n2 int UNSIGNED;
  +
DECLARE mac_dhcp1 VARCHAR(255);
  +
DECLARE current_ip int UNSIGNED DEFAULT 0;
  +
DECLARE pools MEDIUMTEXT DEFAULT " ";
  +
DECLARE cursor_ip CURSOR FOR SELECT INET_ATON(ip_dhcp),mac_dhcp from mtraf_status WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n2;
  +
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
SELECT (n1+6553600) INTO n2;
  +
SELECT mask from network_mask WHERE network=n1 INTO mask1;
  +
OPEN cursor_ip;
  +
FETCH cursor_ip INTO current_ip,mac_dhcp1;
  +
WHILE done = 0 DO
  +
SELECT CONCAT(
  +
"pool { range ",
  +
INET_NTOA(current_ip),
  +
"; allow members of \"",
  +
INET_NTOA(current_ip),
  +
"__",
  +
mac_dhcp1,
  +
"\"; default-lease-time 60; max-lease-time 120; } "
  +
," \n",pools) INTO pools;
  +
FETCH cursor_ip INTO current_ip,mac_dhcp1;
  +
END WHILE;
  +
CLOSE cursor_ip;
  +
RETURN pools;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
====Полный конфиг SUBNET====
  +
Для того что бы выдавать статические маршруты (и не говорите мне что при правильном дизайне сети это не нужно=) ) нужно gateway разбивать на октеты и писать в виде 192, 168, 0, 1 что вносит приятное разнообразие программирование. Ну, и еще - рассчитвать broadcast зная netmask и network (спасибо дяде Кораблеву за <S>наше счастливое детство</s> лекции )<BR>
  +
Строка с маршрутами получилась довольно длинная. В остальном ф-я просто собирает конфиг из частей.
  +
<PRE>
  +
DROP FUNCTION GetSubnet;
  +
delimiter //
  +
CREATE FUNCTION GetSubnet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE b1 INT UNSIGNED;
  +
DECLARE m1 INT UNSIGNED;
  +
DECLARE g1 INT UNSIGNED;
  +
DECLARE oct_1 INT UNSIGNED;
  +
DECLARE oct_2 INT UNSIGNED;
  +
DECLARE oct_3 INT UNSIGNED;
  +
DECLARE oct_4 INT UNSIGNED;
  +
DECLARE gateway_dhcp VARCHAR(255);
  +
DECLARE prefix MEDIUMTEXT DEFAULT "";
  +
DECLARE pools MEDIUMTEXT DEFAULT "";
  +
SELECT GetMaskForIP(n1) INTO m1;
  +
SELECT GetBroadcast(n1,m1) INTO b1;
  +
SELECT GetGateway(n1) INTO g1;
  +
SELECT (g1 & INET_ATON('255.0.0.0')) >> 24 INTO oct_1;
  +
SELECT (g1 & INET_ATON('0.255.0.0')) >> 16 INTO oct_2;
  +
SELECT (g1 & INET_ATON('0.0.255.0')) >> 8 INTO oct_3;
  +
SELECT (g1 & INET_ATON('0.0.0.255')) >> 0 INTO oct_4;
  +
SELECT CONCAT(oct_1,",",oct_2,",",oct_3,",",oct_4) INTO gateway_dhcp;
  +
SELECT CONCAT("subnet ",INET_NTOA(n1)," netmask ", INET_NTOA(m1), " { \n") INTO prefix;
  +
SELECT CONCAT(prefix, "option subnet-mask ", INET_NTOA(m1), ";\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "option broadcast-address ", INET_NTOA(b1), ";\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "option routers ",INET_NTOA(g1),";\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "ddns-update-style none;\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "option domain-name \"airbites.kh.ua\";\n ") INTO prefix;
  +
SELECT CONCAT(prefix, "option domain-name-servers 193.33.48.33, 193.33.49.160, 195.69.244.7, 195.69.244.2, 193.33.48.1, 193.33.49.1;\n ") INTO prefix;
  +
  +
SELECT CONCAT(
  +
prefix,
  +
"option ms-classless-static-routes 4, 224, ",
  +
gateway_dhcp,
  +
", 8, 10, ",
  +
gateway_dhcp,
  +
", 12, 172,16, ",
  +
gateway_dhcp,
  +
",16, 192,168, ",
  +
gateway_dhcp,
  +
", 17, 95,69,128, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 48, 1, ",
  +
gateway_dhcp,
  +
",32, 193, 33, 48, 33, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 49, 1, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 49, 160, ",
  +
gateway_dhcp,
  +
", 18, 188, 230, 0, ",
  +
gateway_dhcp,
  +
";\n"
  +
) INTO prefix;
  +
  +
  +
  +
SELECT CONCAT(
  +
prefix,
  +
"option rfc3442-classless-static-routes 4, 224, ",
  +
gateway_dhcp,
  +
", 8, 10, ",
  +
gateway_dhcp,
  +
", 12, 172,16, ",
  +
gateway_dhcp,
  +
", 16, 192,168, ",
  +
gateway_dhcp,
  +
",17, 95,69,128, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 48, 1, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 48, 33, ",
  +
gateway_dhcp,
  +
",32, 193, 33, 49, 1, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 49, 160, ",
  +
gateway_dhcp,
  +
", 17, 188, 230, 0, ",
  +
gateway_dhcp,
  +
";\n"
  +
) INTO prefix;
  +
  +
  +
SELECT GetAllPoolsForNet(n1) INTO pools;
  +
RETURN CONCAT(prefix,"\n\n",pools,"\n\n }");
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
Пример:
  +
<PRE>
  +
mysql> SELECT GetSubnet(INET_ATON('10.0.136.0'));
  +
...
  +
subnet 10.0.136.0 netmask 255.255.255.0 {
  +
option subnet-mask 255.255.255.0;
  +
option broadcast-address 10.0.136.255;
  +
option routers 10.0.136.254;
  +
ddns-update-style none;
  +
option domain-name "airbites.kh.ua";
  +
option domain-name-servers 193.33.48.33, 193.33.49.160, 195.69.244.7, 195.69.244.2, 193.33.48.1, 193.33.49.1;
  +
option ms-classless-static-routes 4, 224, 10,0,136,254, 8, 10, 10,0,136,254, 12, 172,16, 10,0,136,254,16, 192,168, 10,0,136,254, 17, 95,69,128, 10,0,136,254, 32, 193, 33, 48, 1, 10,0,136,254,32, 193, 33, 48, 33, 10,0,136,254, 32, 193, 33, 49, 1, 10,0,136,254, 32, 193, 33, 49, 160, 10,0,136,254, 18, 188, 230, 0, 10,0,136,254;
  +
option rfc3442-classless-static-routes 4, 224, 10,0,136,254, 8, 10, 10,0,136,254, 12, 172,16, 10,0,136,254, 16, 192,168, 10,0,136,254,17, 95,69,128, 10,0,136,254, 32, 193, 33, 48, 1, 10,0,136,254, 32, 193, 33, 48, 33, 10,0,136,254,32, 193, 33, 49, 1, 10,0,136,254, 32, 193, 33, 49, 160, 10,0,136,254, 17, 188, 230, 0, 10,0,136,254;
  +
  +
  +
pool { range 10.0.136.154; allow members of "10.0.136.154__0:e:2e:98:32:c"; default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.153; allow members of "10.0.136.153__0:15:f2:5:44:29"; default-lease-time 36000; max-lease-time 72000; }
  +
pool { range 10.0.136.152; allow members of "10.0.136.152__0:e0:1c:3c:33:cd"; default-lease-time 36000; max-lease-time 72000; }
  +
</PRE>
  +
====Полный конфиг SUBNET для неплатильщиков====
  +
  +
<PRE>
  +
DROP FUNCTION GetNoPaySubnet;
  +
delimiter //
  +
CREATE FUNCTION GetNoPaySubnet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
-- broacast
  +
DECLARE b1 INT UNSIGNED;
  +
DECLARE n2 INT UNSIGNED;
  +
DECLARE m1 INT UNSIGNED;
  +
DECLARE g1 INT UNSIGNED;
  +
DECLARE oct_1 INT UNSIGNED;
  +
DECLARE oct_2 INT UNSIGNED;
  +
DECLARE oct_3 INT UNSIGNED;
  +
DECLARE oct_4 INT UNSIGNED;
  +
DECLARE gateway_dhcp VARCHAR(255);
  +
  +
DECLARE prefix MEDIUMTEXT default "";
  +
DECLARE pools MEDIUMTEXT default "";
  +
SELECT (n1+6553600) INTO n2;
  +
  +
SELECT GetMaskForNoPayIP(n2) INTO m1;
  +
SELECT GetBroadcast(n2,m1) INTO b1;
  +
SELECT GetGateway(n2) INTO g1;
  +
SELECT (g1 & INET_ATON('255.0.0.0')) >> 24 INTO oct_1;
  +
SELECT (g1 & INET_ATON('0.255.0.0')) >> 16 INTO oct_2;
  +
SELECT (g1 & INET_ATON('0.0.255.0')) >> 8 INTO oct_3;
  +
SELECT (g1 & INET_ATON('0.0.0.255')) >> 0 INTO oct_4;
  +
  +
SELECT CONCAT(oct_1,",",oct_2,",",oct_3,",",oct_4) INTO gateway_dhcp;
  +
  +
SELECT CONCAT("subnet ",INET_NTOA(n2)," netmask ", INET_NTOA(m1), " { \n") INTO prefix;
  +
SELECT CONCAT(prefix, "option subnet-mask ", INET_NTOA(m1), ";\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "option broadcast-address ", INET_NTOA(b1), ";\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "option routers ",INET_NTOA(g1),";\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "ddns-update-style none;\n" ) INTO prefix;
  +
SELECT CONCAT(prefix, "option domain-name \"airbites.kh.ua\";\n ") INTO prefix;
  +
SELECT CONCAT(prefix, "option domain-name-servers 193.33.48.16;\n ") INTO prefix;
  +
  +
SELECT CONCAT(
  +
prefix,
  +
"option ms-classless-static-routes 4, 224, ",
  +
gateway_dhcp,
  +
", 8, 10, ",
  +
gateway_dhcp,
  +
",12, 172,16, ",
  +
gateway_dhcp,
  +
",16, 192,168, ",
  +
gateway_dhcp,
  +
", 17, 95,69,128, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 48, 1, ",
  +
gateway_dhcp,
  +
",32, 193, 33, 48, 33, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 49, 1, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 49, 160, ",
  +
gateway_dhcp,
  +
",18, 188, 230, 0, ",
  +
gateway_dhcp,
  +
";\n"
  +
) INTO prefix;
  +
  +
SELECT CONCAT(
  +
prefix,
  +
"option rfc3442-classless-static-routes 4, 224, ",
  +
gateway_dhcp,
  +
", 8, 10, ",
  +
gateway_dhcp,
  +
",12, 172,16,",
  +
gateway_dhcp,
  +
", 16, 192,168, ",
  +
gateway_dhcp,
  +
",17, 95,69,128, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 48, 1, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 48, 33, ",
  +
gateway_dhcp,
  +
",32, 193, 33, 49, 1, ",
  +
gateway_dhcp,
  +
", 32, 193, 33, 49, 160, ",
  +
gateway_dhcp,
  +
", 17, 188, 230, 0,",
  +
gateway_dhcp,
  +
";\n"
  +
) INTO prefix;
  +
  +
  +
  +
  +
  +
  +
SELECT GetAllPoolsForNoPayNet(n1) INTO pools;
  +
RETURN CONCAT(prefix,"\n\n",pools,"\n\n }");
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
===Окончательная сборка конфига===
  +
====Объединение subnets====
  +
И функция которая соберет все части subnet вместе. (для всех подсетей)
  +
<PRE>
  +
DROP FUNCTION GetAllSubnets;
  +
delimiter //
  +
CREATE FUNCTION GetAllSubnets(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE current_net INT UNSIGNED;
  +
DECLARE done INT DEFAULT 0;
  +
DECLARE all_subnets MEDIUMTEXT DEFAULT " ";
  +
DECLARE cursor_net CURSOR FOR SELECT network from network_mask;
  +
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
  +
  +
OPEN cursor_net;
  +
FETCH cursor_net INTO current_net;
  +
WHILE done = 0 DO
  +
SELECT CONCAT(all_subnets,"\n\n\n\n", GetSubnet(current_net), "\n\n\n\n", GetNoPaySubnet(current_net) ) INTO all_subnets;
  +
FETCH cursor_net INTO current_net;
  +
END WHILE;
  +
CLOSE cursor_net;
  +
RETURN all_subnets;
  +
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
====Объединение всех Classes для всех IP====
  +
<PRE>
  +
DROP FUNCTION GetAllClassesForAllNets;
  +
delimiter //
  +
CREATE FUNCTION GetAllClassesForAllNets(n1 INT) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE current_net INT UNSIGNED;
  +
DECLARE done INT DEFAULT 0;
  +
DECLARE classes MEDIUMTEXT DEFAULT " ";
  +
DECLARE cursor_net CURSOR FOR SELECT network from network_mask;
  +
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
  +
  +
OPEN cursor_net;
  +
FETCH cursor_net INTO current_net;
  +
WHILE done = 0 DO
  +
SELECT CONCAT(classes," \n\n\n\n", GetAllClassesForNet(current_net)) INTO classes;
  +
FETCH cursor_net INTO current_net;
  +
END WHILE;
  +
CLOSE cursor_net;
  +
RETURN classes;
  +
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
====Создание статичной части конфига====
  +
Часть конфига - неизменна (префикс).
  +
Для простоты вынес ее в отдельную функцию.
  +
<PRE>
  +
DROP FUNCTION GetPrefix;
  +
delimiter //
  +
CREATE FUNCTION GetPrefix(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE prefix MEDIUMTEXT DEFAULT "
  +
ddns-update-style none;
  +
option domain-name \"airbites.kh.ua\";
  +
option domain-name-servers 193.33.48.33, 193.33.49.160, 193.33.48.1, 193.33.49.1, 195.69.244.2, 195.69.244.7;
  +
option ms-classless-static-routes code 249 = array of unsigned integer 8;
  +
option rfc3442-classless-static-routes code 121 = array of integer 8;
  +
default-lease-time 300;
  +
max-lease-time 600;
  +
authoritative;
  +
log-facility local7;
  +
subnet 172.16.30.32 netmask 255.255.255.240
  +
{
  +
log(info,\"subnet subnet 172.16.30.32 netmask 255.255.255.240\");
  +
}
  +
  +
  +
log (info, concat(\"0 RELAYIP=\",binary-to-ascii(10, 8, \".\", packet(24, 4))));
  +
log (info, concat(\"0 MAC=\",binary-to-ascii(16, 8, \":\", substring(hardware, 1, 6))));
  +
log (info, concat(\"0 option dhcp-lease-time = \",binary-to-ascii(16, 8, \".\", option dhcp-lease-time)));
  +
log (info, concat(\"0 option dhcp-message = \",binary-to-ascii(16, 8, \".\", option dhcp-message)));
  +
log (info, concat(\"0 option dhcp-message-type = \",binary-to-ascii(16, 8, \".\", option dhcp-message-type)));
  +
log (info, concat(\"0 option dhcp-parameter-request-list = \",binary-to-ascii(16, 8, \".\", option dhcp-parameter-request-list)));
  +
log (info, concat(\"0 option dhcp-requested-address = \",binary-to-ascii(16, 8, \".\", option dhcp-requested-address)));
  +
log (info, concat(\"0 option agent.link-selection = \",binary-to-ascii(16, 8, \".\", option agent.link-selection)));
  +
";
  +
RETURN prefix;
  +
END
  +
//
  +
delimiter ;
  +
</PRE>
  +
  +
====Окончательная сборка конфига====
  +
<PRE>
  +
DROP FUNCTION GetDHCPConfing;
  +
delimiter //
  +
CREATE FUNCTION GetDHCPConfing(n1 int UNSIGNED) RETURNS MEDIUMTEXT
  +
BEGIN
  +
DECLARE config MEDIUMTEXT DEFAULT "";
  +
SELECT CONCAT( config, GetPrefix(1) ) INTO config;
  +
SELECT CONCAT( config, GetAllClassesForAllNets(1) ) INTO config;
  +
SELECT CONCAT( config, GetAllSubnets(1) ) INTO config;
  +
RETURN config;
  +
END
  +
//
  +
delimiter ;
 
</PRE>
  
</PRE>
−
Условие в IF - условие когда абонент "должен" - ему нужно дать другой IP (у меня вместо, например, 10,0,136,10 он получит 10,100,136,10) и соответвенно, пускать только на биллинг (если короко, то файрволл не пускает абонентов никуда кроме отдельного ДНСа (или отдельного view) который на все запросы отвечает адресом странички "дай денег, дорогой абонент" и собственно биллинга) Всем кто будет напоминать про dhcp snooping, opt 82 и прочее - это все делается для остатоков неуправляемых сегментов,
  

Текущая версия на 15:33, 28 сентября 2012

Конфиг для DHCP средсвами MYSQL

Есть желание уйти от всяких скриптов (php, bash, perl) для получения конфига из MySQL для dhcpd а так же с помошью представлений отказаться от привязки к конкретным таблицам. Заодно проверить какое из решений будет работать быстрее.
Это только черновик пока и я еще не использую это решение в продакшене
TODO

  • Проверить что бы использовалась только таблица mtraf_status

Создание представдения

Для того что бы не быть привязаным к структуре базы (которую программист биллинга вообще-то может менять по своему усмотрению) все данные для dhcp буду получать из view. Стараюсь хранить данные в виде их "физических сущностей" - а именно IP - как INT UNSIGNED. В некоторых случаях приходиться брать данные "как есть" и преобразовывать.

DROP view  mtraf_status;
CREATE VIEW
    mtraf_status
AS
    SELECT
        domain AS domain,
        IF( 0n_a<0 OR ( 0n_a=0 AND debt=0 )  ,0,1) AS status,
        ip AS ip,
        mac AS mac,
        IF( 0n_a<0 OR ( 0n_a=0 AND debt=0), inet_ntoa(inet_aton(ip)+6553600),ip) AS ip_dhcp,
        lower(substr(replace(concat(':', mac), ':0', ':') from 2)) AS  mac_dhcp
FROM
    mtraf;

Условие в IF - условие когда абонент "должен" - ему нужно дать другой IP (у меня вместо, например, 10.0.136.10 он получит 10,100,136,10) и соответвенно, пускать только на биллинг (если короко, то файрволл не пускает абонентов никуда кроме отдельного ДНСа (или отдельного view) который на все запросы отвечает адресом странички "дай денег, дорогой абонент" и собственно биллинга) Всем кто будет напоминать про dhcp snooping, opt 82 и прочее - это все делается для остатоков неуправляемых сегментов. 

mysql> SELECT * from mtraf_status limit 10;
+------------+--------+----------------+-------------------+----------------+------------------+
| domain     | status | ip             | mac               | ip_dhcp        | mac_dhcp         |
+------------+--------+----------------+-------------------+----------------+------------------+
| 007        |      1 | 10.0.135.49    | 00:50:BF:B1:3E:44 | 10.0.135.49    | 0:50:bf:b1:3e:44 |
| 01125      |      0 | 10.100.82.186  | FA:CE:0F:FF:A1:11 | 10.200.82.186  | fa:ce:f:ff:a1:11 |
| 01496      |      1 | 10.0.142.81    | 00:21:91:21:8B:A7 | 10.0.142.81    | 0:21:91:21:8b:a7 |
| 04079      |      1 | 10.0.86.33     | 00:04:4B:80:80:03 | 10.0.86.33     | 0:4:4b:80:80:3   |
| 060696     |      1 | 10.5.6.73      | 00:1D:60:47:B7:4F | 10.5.6.73      | 0:1d:60:47:b7:4f |
| 060708     |      1 | 10.0.88.55     | 00:24:8C:C1:94:AD | 10.0.88.55     | 0:24:8c:c1:94:ad |
| 090403     |      1 | 10.0.96.13     | 00:17:31:65:D7:86 | 10.0.96.13     | 0:17:31:65:d7:86 |
| 0lenka     |      1 | 10.4.225.63    | 00:17:31:9A:0A:26 | 10.4.225.63    | 0:17:31:9a:a:26  |
| 1003       |      1 | 10.4.9.50      | 00:1F:E2:50:50:75 | 10.4.9.50      | 0:1f:e2:50:50:75 |
| 1004210045 |      0 | 10.100.120.139 | FA:CE:0F:FF:A1:11 | 10.200.120.139 | fa:ce:f:ff:a1:11 |
+------------+--------+----------------+-------------------+----------------+------------------+
10 rows in set (0.00 sec)

Дополнительные данные о сети

В биллинге (в моем случае) нет никакой информации о топологии сети). Нужно добавить данные о сетях, релеях, гейтвеях. Отмечу, что в сети есть как минимум 2 релея (т.к. сети 10.0... и 10.100... - алиасы ) и запрос прийти может через любой из них. В общем случае релеев может быть сколько угодно - по числу алиасов на интерфейсе. 

DROP TABLE network_relay;
CREATE TABLE network_relay
(
    network int(64) UNSIGNED,
    relay int(64) UNSIGNED
);

INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.1'));
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.254'));
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.100.135.1'));
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.100.135.254'));

INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.1'));
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.254'));
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.100.136.1'));
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.100.136.254'));

INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.1'));
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.254'));
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.100.137.1'));
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.100.137.254'));



DROP TABLE network_mask;
CREATE TABLE network_mask
(
    network int  UNSIGNED  NOT NULL UNIQUE,
    mask int  UNSIGNED,
    is_dhcp int DEFAULT 1
);

INSERT into network_mask(network,mask) values(INET_ATON('10.0.135.0'), INET_ATON('255.255.255.0'));
INSERT into network_mask(network,mask) values(INET_ATON('10.0.136.0'), INET_ATON('255.255.255.0'));
INSERT into network_mask(network,mask) values(INET_ATON('10.0.137.0'), INET_ATON('255.255.255.0'));



DROP TABLE network_gateway;
CREATE TABLE network_gateway
(
    network int UNSIGNED,
    gateway int  UNSIGNED
);

INSERT into network_gateway values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.254'));
INSERT into network_gateway values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.254'));
INSERT into network_gateway values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.254'));

INSERT into network_gateway values(INET_ATON('10.100.135.0'), INET_ATON('10.100.135.254'));
INSERT into network_gateway values(INET_ATON('10.100.136.0'), INET_ATON('10.100.136.254'));
INSERT into network_gateway values(INET_ATON('10.100.137.0'), INET_ATON('10.100.137.254'));

Создание индексов

На тадицы я дополнительно создал индексы. По результатов моих тестов это не внесло изменений в производительность, потому допускаю что индекс созданы неоптимально. У таблцы mtraf (таблица с данными). 

  KEY `idx_ip` (`ip`),
  KEY `idx_0n_a` (`0n_a`),
  KEY `idx_debt` (`debt`),
  KEY `idx_status` (`debt`,`0n_a`),
  KEY `idx_mac` (`mac`)

У таблиц с дополнительными данными - сети, релеи и гейтвеи 

  KEY `idx_network` (`network`)

Процедуры и функции

Решил максимально разбить задачу на отдельные атомарные функции, MySQL к это теперь позволяет

Вспомогательные функции

Вычисление броадкаста

Тривиальная задача зная ip и маску. Для простоты вынес в отдельную ф-ю. 

DROP FUNCTION GetBroadcast;
delimiter //
CREATE FUNCTION GetBroadcast(n1 int UNSIGNED, m1 INT UNSIGNED) RETURNS INT UNSIGNED
BEGIN
    DECLARE b1 INT UNSIGNED;
    SELECT  n1+POWER(2,32-BIT_COUNT(m1))-1 INTO b1;
    RETURN b1;
END
//
delimiter ;

Получение шлюза для сети

Списки шлюзов храню отдельно, для определения какой шлюз передать клиенту использую эту ф-ю 

DROP FUNCTION GetGateway;
delimiter //
CREATE FUNCTION GetGateway(n1 int UNSIGNED) RETURNS INT UNSIGNED
BEGIN
    DECLARE g1 INT UNSIGNED;
    SELECT gateway FROM network_gateway where network=n1 LIMIT 1 INTO g1;
    RETURN g1;
END
//
delimiter ;

Получение маски для IP

Зная IP получаем маску сети (которая в общем случае может отличаться от "255.255.255.0". Возвращаю первое вхождение. 

DROP FUNCTION GetMaskForIP;
delimiter //
CREATE FUNCTION GetMaskForIP(ip1 int(64) UNSIGNED)  RETURNS INT UNSIGNED
BEGIN
    DECLARE mask1 INT UNSIGNED;
    SELECT mask  from network_mask WHERE ip1&mask=network LIMIT 1 INTO mask1;
    RETURN mask1;
END
//
delimiter ;

Пример: 

mysql> SELECT INET_NTOA(GetMaskForIP(INET_ATON('10.0.136.12')));
+---------------------------------------------------+
| INET_NTOA(GetMaskForIP(INET_ATON('10.0.136.12'))) |
+---------------------------------------------------+
| 255.255.255.0                                     |
+---------------------------------------------------+
1 row in set (0.01 sec)

Аналогично для "неплатильщиков" 

DROP FUNCTION GetMaskForNoPayIP;
delimiter //
CREATE FUNCTION GetMaskForNoPayIP(ip1 int(64) UNSIGNED)  RETURNS INT UNSIGNED
BEGIN
    DECLARE mask1 INT UNSIGNED;
    SELECT mask  from network_mask WHERE (ip1-6553600)&mask=network limit 1 INTO mask1;
    RETURN mask1;
END
//
delimiter ;

Определить какой сети принадлежит IP

DROP FUNCTION GetNetForIP;
delimiter //
CREATE FUNCTION GetNetForIP(ip1 int UNSIGNED) RETURNS int UNSIGNED
BEGIN
    DECLARE net1 INT  UNSIGNED;
    SELECT network INTO net1 FROM network_mask  WHERE ip1&mask=network LIMIT 1;
    RETURN net1;

END
//
delimiter ;

Пример: 

mysql> SELECT INET_NTOA(GetNetForIP(INET_ATON('10.0.136.11')));
+--------------------------------------------------+
| INET_NTOA(GetNetForIP(INET_ATON('10.0.136.11'))) |
+--------------------------------------------------+
| 10.0.136.0                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)

Генерация CLASS

Получение списка relay для сети

В сети запрос может прийти от одного или нескольких возможных relay-агентов. Для каждой сети - определен список этих relay. Задача - сгенерировать часть конфига вида "запрос пришел через релей1 ИЛИ запрос пришел через релей2 ИЛИ ..." 

DROP FUNCTION GetRelayForNet;
delimiter //
CREATE FUNCTION GetRelayForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
-- done is "end of cycle" variable
    DECLARE done INT DEFAULT 0;
    DECLARE current_relay MEDIUMTEXT default "";
    DECLARE dhcp_relay MEDIUMTEXT;
    DECLARE dhcp_relay_res MEDIUMTEXT default "";
-- set done=1 when cusror ends
-- cursor for relay
    DECLARE cursor_relay CURSOR FOR  SELECT  relay from network_relay  WHERE network=n1 LIMIT 1,18446744073709551615;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    OPEN cursor_relay;
    SELECT INET_NTOA(relay) INTO dhcp_relay FROM network_relay WHERE network=n1 LIMIT 1;
    SELECT CONCAT("(  ( binary-to-ascii(10, 8, \".\", packet(24, 4))=\"", dhcp_relay, "\" ) ") INTO dhcp_relay_res;
    FETCH cursor_relay INTO current_relay;
    WHILE done = 0 DO
        SELECT INET_NTOA(relay) INTO dhcp_relay FROM network_relay WHERE relay = current_relay LIMIT 1;
        SELECT CONCAT(dhcp_relay_res, " or (binary-to-ascii(10, 8, \".\", packet(24, 4))=\"", dhcp_relay,"\")") INTO dhcp_relay_res;
        FETCH  cursor_relay INTO current_relay;
    END WHILE;
    CLOSE cursor_relay;
    RETURN CONCAT(dhcp_relay_res, ")");
END
//
delimiter ;

Пример: 

SELECT  GetRelayForNet(INET_ATON('10.0.136.0'));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GetRelayForNet(INET_ATON('10.0.136.0'))                                                                                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| (  ( binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.1" )  or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.254") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.1") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.254")) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Заготовка class для IP

Для простоты разделелил генерацию класса на несколько частей
Эта функция возващает заготовку класса (match mac-address) для определенного IP. 

DROP FUNCTION GetClassForIP;
delimiter //
CREATE FUNCTION GetClassForIP(ip1 int UNSIGNED) RETURNS char(255)
BEGIN
    DECLARE mac_dhcp1 char(255) DEFAULT "";
    DECLARE ip_d char(255) DEFAULT "";
    SELECT ip_dhcp  INTO  ip_d FROM mtraf_status WHERE ip=INET_NTOA(ip1);
    SELECT mac_dhcp INTO mac_dhcp1 FROM mtraf_status WHERE mtraf_status.ip=ip_d LIMIT 1;
    RETURN  CONCAT("class \"",ip_d,"__",mac_dhcp1,"\" {  match if ( ( binary-to-ascii(16,  8, \":\", substring(hardware,1, 6)) = \"",mac_dhcp1,"\") and");
END
//
delimiter ;

Пример 

mysql> SELECT GetClassForIP(INET_ATON('10.0.136.11'));
+------------------------------------------------------------------------------------------------------------------------------------+
| GetClassForIP(INET_ATON('10.0.136.11'))                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------+
| class "10.0.136.11__0:13:d4:5:d9:f4" {  match if(  ( binary-to-ascii(16,  8, ":", substring(hardware,1, 6)) = "0:13:d4:5:d9:f4 and |
+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

Генерация полноценного класса

Собираем полноценный класс из частей. 

DROP FUNCTION GetFullClassForIP;
delimiter //
CREATE FUNCTION GetFullClassForIP(ip1 int UNSIGNED) RETURNS TEXT
BEGIN
    RETURN  CONCAT( GetClassForIP(ip1), " " ,  GetRelayForNet(GetNetForIP(ip1)) ,"}\n" ) ;
END
//
delimiter ;

Пример (читать так: Классу соответвует запрос с маком $mac прошедьший через $relay[1] или $relay[2] или ... или relay[N] ). В такой конфигурации мак должен быть уникален в пределах сегмента а не в пределах всей сети: 

mysql> SELECT GetFullClassForIP(INET_ATON('10.0.136.11'));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GetFullClassForIP(INET_ATON('10.0.136.11'))                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class "10.0.136.11__0:13:d4:5:d9:f4" {  match if(  ( binary-to-ascii(16,  8, ":", substring(hardware,1, 6)) = "0:13:d4:5:d9:f4 and (  ( binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.1" )  or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.254") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.1") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.254"))}
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)


Создание конфига SUBNET

создание пула для IP

DROP FUNCTION GetPoolForIP;
delimiter //
CREATE FUNCTION GetPoolForIP(ip1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE mac_dhcp1 VARCHAR(255);
    SELECT  mac_dhcp INTO mac_dhcp1 FROM mtraf_status WHERE INET_ATON(mtraf_status.ip)=ip1 LIMIT 1;
    RETURN   CONCAT(
            "pool { range ",
            INET_NTOA(ip1),
            "; allow members of \"",
            INET_NTOA(ip1),
            "__",
            mac_dhcp1,
            "\"; default-lease-time 36000; max-lease-time 72000; } "
            );
END
//
delimiter ;

Cоздание пула для IP неплатильщика

DROP FUNCTION GetPoolForNoPayIP;
delimiter //
CREATE FUNCTION GetPoolForNoPayIP(ip1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE ip_dhcp1 VARCHAR(32) DEFAULT "";
    DECLARE mac_dhcp1 VARCHAR(255) DEFAULT "";
    SELECT INET_NTOA(ip1) INTO ip_dhcp1;
    SELECT  mac_dhcp INTO mac_dhcp1 FROM mtraf_status WHERE INET_ATON(mtraf_status.ip)=ip1-6553600 LIMIT 1;
    RETURN CONCAT(
            "pool { range ",
            ip_dhcp1,
            "; allow members of \"",
            ip_dhcp1,
            "__",
            mac_dhcp1,
            "\"; default-lease-time 36000; max-lease-time 72000; } "
        );
END
//
delimiter ;
SELECT GetPoolForNoPayIP(INET_ATON('10.100.136.84'));

Пример: 

mysql> SELECT GetPoolForNoPayIP(INET_ATON('10.100.136.84'));
+---------------------------------------------------------------------------------------------------------------------------------+
| GetPoolForNoPayIP(INET_ATON('10.100.136.84'))                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------+
| pool { range 10.100.136.84; allow members of 10.100.136.84__0:1d:92:58:27:cf default-lease-time 36000; max-lease-time 72000; }  |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

Полные пулы для тех кто платит и тех кто нет (из одногй сети)

Получить пулы для тех кто платит 

DROP FUNCTION GetAllPoolsForNet;
delimiter //
CREATE FUNCTION GetAllPoolsForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE mask1 int UNSIGNED;
    DECLARE current_ip int UNSIGNED DEFAULT 0;
    DECLARE pools MEDIUMTEXT DEFAULT " ";
    DECLARE cursor_ip CURSOR FOR  SELECT INET_ATOn(ip_dhcp) from mtraf_status  WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n1;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    SELECT mask from network_mask WHERE network=n1 INTO mask1;
    OPEN cursor_ip;
    FETCH cursor_ip INTO current_ip;
    WHILE done = 0 DO
        SELECT  CONCAT(GetPoolForIP(current_ip),"  \n",pools) INTO pools;
        FETCH  cursor_ip INTO current_ip;
    END WHILE;
    CLOSE cursor_ip;
    RETURN pools;
END
//
delimiter ;

Версия ф-и которую мне подсказал Серж Шевченко (оптимизирована по производительности) 

DROP FUNCTION GetAllPoolsForNet; 
delimiter // 
CREATE FUNCTION GetAllPoolsForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT 
BEGIN 
    DECLARE done INT DEFAULT 0; 
    DECLARE mask1 int UNSIGNED; 
    DECLARE mac_dhcp1 VARCHAR(255); 
    DECLARE current_ip int UNSIGNED DEFAULT 0; 
    DECLARE pools MEDIUMTEXT DEFAULT " "; 
    DECLARE cursor_ip CURSOR FOR  SELECT INET_ATOn(ip_dhcp),mac_dhcp from mtraf_status  WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n1; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 
    SELECT mask from network_mask WHERE network=n1 INTO mask1; 
    OPEN cursor_ip; 
    FETCH cursor_ip INTO current_ip,mac_dhcp1; 
    WHILE done = 0 DO 
        SELECT  CONCAT( 
            "pool { range ", 
            INET_NTOA(current_ip), 
            "; allow members of \"", 
            INET_NTOA(current_ip), 
            "__", 
            mac_dhcp1, 
            "\"; default-lease-time 36000; max-lease-time 72000; } " 
            ,"  \n",pools) INTO pools; 
        FETCH  cursor_ip INTO current_ip,mac_dhcp1; 
    END WHILE; 
    CLOSE cursor_ip; 
    RETURN pools; 
END 
// 
delimiter ;


Пример (часть поскипано): 

mysql> SELECT GetAllPoolsForNet(INET_ATON('10.0.136.0'));
...
pool { range 10.0.136.154; allow members of 10.0.136.154__0:e:2e:98:32:c default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.153; allow members of 10.0.136.153__0:15:f2:5:44:29 default-lease-time 36000; max-lease-time 72000; }                                                                                                                  
pool { range 10.0.136.152; allow members of 10.0.136.152__0:e0:1c:3c:33:cd default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.151; allow members of 10.0.136.151__0:e0:1c:3b:d6:39 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.150; allow members of 10.0.136.150__0:19:d1:e6:1e:2c default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.149; allow members of 10.0.136.149__0:18:f3:fb:fe:b6 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.174; allow members of 10.0.136.174__0:26:2d:5f:97:c1 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.148; allow members of 10.0.136.148__0:26:9e:75:50:d2 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.147; allow members of 10.0.136.147__0:a0:d1:ca:47:b default-lease-time 36000; max-lease-time 72000; }                                                                                                                  
pool { range 10.0.136.145; allow members of 10.0.136.145__0:e0:4c:3:d7:55 default-lease-time 36000; max-lease-time 72000; }                                                                                                                  
pool { range 10.0.136.146; allow members of 10.0.136.146__0:1f:c6:4f:ed:7a default-lease-time 36000; max-lease-time 72000; }           
...
1 row in set (13.79 sec)

И тех кто не платит 

DROP FUNCTION GetAllPoolsForNoPayNet;
delimiter //
CREATE FUNCTION GetAllPoolsForNoPayNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE mask1 int UNSIGNED;
    DECLARE current_ip int UNSIGNED DEFAULT 0;
    DECLARE pools MEDIUMTEXT DEFAULT " ";

    DECLARE cursor_ip CURSOR FOR  SELECT INET_ATON(ip_dhcp) from mtraf_status  WHERE (INET_ATON(mtraf_status.ip_dhcp))&mask1=(n1+6553600);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    SELECT mask from network_mask WHERE network=n1 INTO mask1;
    OPEN cursor_ip;
    FETCH cursor_ip INTO current_ip;
    WHILE done = 0 DO
        SELECT  CONCAT(GetPoolForNoPayIP(current_ip),"  \n",pools) INTO pools;
        FETCH  cursor_ip INTO current_ip;
    END WHILE;
    CLOSE cursor_ip;
    RETURN pools;
END
//
delimiter ;

DROP FUNCTION GetAllPoolsForNoPayNet; 
delimiter // 
CREATE FUNCTION GetAllPoolsForNoPayNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT 
BEGIN 
    DECLARE done INT DEFAULT 0; 
    DECLARE mask1 int UNSIGNED; 
    DECLARE n2 int UNSIGNED;
    DECLARE mac_dhcp1 VARCHAR(255); 
    DECLARE current_ip int UNSIGNED DEFAULT 0; 
    DECLARE pools MEDIUMTEXT DEFAULT " "; 
    DECLARE cursor_ip CURSOR FOR  SELECT INET_ATON(ip_dhcp),mac_dhcp from mtraf_status  WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n2; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    SELECT (n1+6553600) INTO n2; 
    SELECT mask from network_mask WHERE network=n1 INTO mask1; 
    OPEN cursor_ip; 
    FETCH cursor_ip INTO current_ip,mac_dhcp1; 
    WHILE done = 0 DO 
        SELECT  CONCAT( 
            "pool { range ", 
            INET_NTOA(current_ip), 
            "; allow members of \"", 
            INET_NTOA(current_ip), 
            "__", 
            mac_dhcp1, 
            "\"; default-lease-time 60; max-lease-time 120; } " 
            ,"  \n",pools) INTO pools; 
        FETCH  cursor_ip INTO current_ip,mac_dhcp1; 
    END WHILE; 
    CLOSE cursor_ip; 
    RETURN pools; 
END 
// 
delimiter ;

Полный конфиг SUBNET

Для того что бы выдавать статические маршруты (и не говорите мне что при правильном дизайне сети это не нужно=) ) нужно gateway разбивать на октеты и писать в виде 192, 168, 0, 1 что вносит приятное разнообразие программирование. Ну, и еще - рассчитвать broadcast зная netmask и network (спасибо дяде Кораблеву за наше счастливое детство лекции )
Строка с маршрутами получилась довольно длинная. В остальном ф-я просто собирает конфиг из частей. 

DROP FUNCTION GetSubnet;
delimiter //
CREATE FUNCTION GetSubnet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE b1 INT UNSIGNED;
    DECLARE m1 INT UNSIGNED;
    DECLARE g1 INT UNSIGNED;
    DECLARE oct_1 INT UNSIGNED;
    DECLARE oct_2 INT UNSIGNED;
    DECLARE oct_3 INT UNSIGNED;
    DECLARE oct_4 INT UNSIGNED;
    DECLARE gateway_dhcp VARCHAR(255);
    DECLARE prefix MEDIUMTEXT DEFAULT "";
    DECLARE pools MEDIUMTEXT DEFAULT "";
    SELECT GetMaskForIP(n1) INTO m1;
    SELECT GetBroadcast(n1,m1)  INTO b1;
    SELECT GetGateway(n1) INTO g1;
    SELECT (g1 & INET_ATON('255.0.0.0')) >> 24 INTO oct_1;
    SELECT (g1 & INET_ATON('0.255.0.0')) >> 16 INTO oct_2;
    SELECT (g1 & INET_ATON('0.0.255.0')) >> 8  INTO oct_3;
    SELECT (g1 & INET_ATON('0.0.0.255')) >> 0  INTO oct_4;
    SELECT CONCAT(oct_1,",",oct_2,",",oct_3,",",oct_4) INTO gateway_dhcp;
    SELECT CONCAT("subnet ",INET_NTOA(n1)," netmask ",   INET_NTOA(m1), " { \n") INTO prefix;
    SELECT CONCAT(prefix, "option subnet-mask         ", INET_NTOA(m1), ";\n" )   INTO prefix;
    SELECT CONCAT(prefix, "option broadcast-address   ", INET_NTOA(b1), ";\n" )   INTO prefix;
    SELECT CONCAT(prefix, "option routers ",INET_NTOA(g1),";\n" ) INTO prefix;
    SELECT CONCAT(prefix, "ddns-update-style none;\n" ) INTO prefix;
    SELECT CONCAT(prefix, "option domain-name \"airbites.kh.ua\";\n ") INTO prefix;
    SELECT CONCAT(prefix, "option domain-name-servers    193.33.48.33,   193.33.49.160,    195.69.244.7,        195.69.244.2,      193.33.48.1,    193.33.49.1;\n ") INTO prefix;

    SELECT CONCAT(
            prefix,
            "option ms-classless-static-routes      4, 224, ",
            gateway_dhcp,
            ", 8, 10, ",
            gateway_dhcp,
            ", 12, 172,16, ",
            gateway_dhcp,
            ",16, 192,168, ",
            gateway_dhcp,
            ", 17, 95,69,128, ",
            gateway_dhcp,
            ", 32, 193, 33, 48, 1, ",
            gateway_dhcp,
            ",32, 193, 33, 48, 33, ",
            gateway_dhcp,
            ", 32, 193, 33, 49, 1, ",
            gateway_dhcp,
            ", 32, 193, 33, 49, 160, ",
            gateway_dhcp,
            ", 18, 188, 230, 0, ",
            gateway_dhcp,
            ";\n"
            ) INTO prefix;



    SELECT CONCAT(
            prefix,
            "option rfc3442-classless-static-routes 4, 224, ",
            gateway_dhcp,
            ", 8, 10, ",
            gateway_dhcp,
            ", 12, 172,16, ",
            gateway_dhcp,
            ", 16, 192,168, ",
            gateway_dhcp,
            ",17, 95,69,128, ",
            gateway_dhcp,
            ", 32, 193, 33, 48, 1, ",
            gateway_dhcp,
            ", 32, 193, 33, 48, 33, ",
            gateway_dhcp,
            ",32, 193, 33, 49, 1, ",
            gateway_dhcp,
            ", 32, 193, 33, 49, 160, ",
            gateway_dhcp,
            ", 17, 188, 230, 0, ",
            gateway_dhcp,
            ";\n"
            ) INTO prefix;


    SELECT GetAllPoolsForNet(n1) INTO pools;
    RETURN CONCAT(prefix,"\n\n",pools,"\n\n }");
    END
//
delimiter ;

Пример: 

mysql> SELECT  GetSubnet(INET_ATON('10.0.136.0'));
...
subnet 10.0.136.0 netmask 255.255.255.0 {
option subnet-mask         255.255.255.0;
option broadcast-address   10.0.136.255;
option routers 10.0.136.254;
ddns-update-style none;
option domain-name "airbites.kh.ua";
 option domain-name-servers    193.33.48.33,   193.33.49.160,    195.69.244.7,        195.69.244.2,      193.33.48.1,    193.33.49.1;
 option ms-classless-static-routes      4, 224, 10,0,136,254, 8, 10, 10,0,136,254, 12, 172,16, 10,0,136,254,16, 192,168, 10,0,136,254, 17, 95,69,128, 10,0,136,254, 32, 193, 33, 48, 1, 10,0,136,254,32, 193, 33, 48, 33, 10,0,136,254, 32, 193, 33, 49, 1, 10,0,136,254, 32, 193, 33, 49, 160, 10,0,136,254, 18, 188, 230, 0, 10,0,136,254;
option rfc3442-classless-static-routes 4, 224, 10,0,136,254, 8, 10, 10,0,136,254, 12, 172,16, 10,0,136,254, 16, 192,168, 10,0,136,254,17, 95,69,128, 10,0,136,254, 32, 193, 33, 48, 1, 10,0,136,254, 32, 193, 33, 48, 33, 10,0,136,254,32, 193, 33, 49, 1, 10,0,136,254, 32, 193, 33, 49, 160, 10,0,136,254, 17, 188, 230, 0, 10,0,136,254;


pool { range 10.0.136.154; allow members of "10.0.136.154__0:e:2e:98:32:c"; default-lease-time 36000; max-lease-time 72000; }
pool { range 10.0.136.153; allow members of "10.0.136.153__0:15:f2:5:44:29"; default-lease-time 36000; max-lease-time 72000; }
pool { range 10.0.136.152; allow members of "10.0.136.152__0:e0:1c:3c:33:cd"; default-lease-time 36000; max-lease-time 72000; }

Полный конфиг SUBNET для неплатильщиков

DROP FUNCTION GetNoPaySubnet;
delimiter //
CREATE FUNCTION GetNoPaySubnet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
-- broacast
    DECLARE b1 INT UNSIGNED;
    DECLARE n2 INT UNSIGNED;
    DECLARE m1 INT UNSIGNED;
    DECLARE g1 INT UNSIGNED;
    DECLARE oct_1 INT UNSIGNED;
    DECLARE oct_2 INT UNSIGNED;
    DECLARE oct_3 INT UNSIGNED;
    DECLARE oct_4 INT UNSIGNED;
    DECLARE gateway_dhcp VARCHAR(255);

    DECLARE prefix MEDIUMTEXT default "";
    DECLARE pools MEDIUMTEXT default "";
    SELECT (n1+6553600) INTO n2;

    SELECT GetMaskForNoPayIP(n2) INTO m1;
    SELECT GetBroadcast(n2,m1)  INTO b1;
    SELECT GetGateway(n2) INTO g1;
    SELECT (g1 & INET_ATON('255.0.0.0')) >> 24 INTO oct_1;
    SELECT (g1 & INET_ATON('0.255.0.0')) >> 16 INTO oct_2;
    SELECT (g1 & INET_ATON('0.0.255.0')) >> 8  INTO oct_3;
    SELECT (g1 & INET_ATON('0.0.0.255')) >> 0  INTO oct_4;

    SELECT CONCAT(oct_1,",",oct_2,",",oct_3,",",oct_4) INTO gateway_dhcp;

    SELECT CONCAT("subnet ",INET_NTOA(n2)," netmask ",   INET_NTOA(m1), " { \n") INTO prefix;
    SELECT CONCAT(prefix, "option subnet-mask         ", INET_NTOA(m1), ";\n" )   INTO prefix;
    SELECT CONCAT(prefix, "option broadcast-address   ", INET_NTOA(b1), ";\n" )   INTO prefix;
    SELECT CONCAT(prefix, "option routers ",INET_NTOA(g1),";\n" ) INTO prefix;
    SELECT CONCAT(prefix, "ddns-update-style none;\n" ) INTO prefix;
    SELECT CONCAT(prefix, "option domain-name \"airbites.kh.ua\";\n ") INTO prefix;
    SELECT CONCAT(prefix, "option domain-name-servers    193.33.48.16;\n ") INTO prefix;

    SELECT CONCAT(
            prefix,
            "option ms-classless-static-routes      4, 224, ",
            gateway_dhcp,
            ", 8, 10, ",
            gateway_dhcp,
            ",12, 172,16, ",
            gateway_dhcp,
            ",16, 192,168, ",
            gateway_dhcp,
            ", 17, 95,69,128, ",
            gateway_dhcp,
            ", 32, 193, 33, 48, 1, ",
            gateway_dhcp,
            ",32, 193, 33, 48, 33, ",
            gateway_dhcp,
            ", 32, 193, 33, 49, 1, ",
            gateway_dhcp,
            ", 32, 193, 33, 49, 160, ",
            gateway_dhcp,
            ",18, 188, 230, 0, ",
            gateway_dhcp,
            ";\n"
            ) INTO prefix;

    SELECT CONCAT(
            prefix,
            "option rfc3442-classless-static-routes 4, 224, ",
            gateway_dhcp,
            ", 8, 10, ",
            gateway_dhcp,
            ",12, 172,16,",
            gateway_dhcp,
            ", 16, 192,168, ",
            gateway_dhcp,
            ",17, 95,69,128, ",
            gateway_dhcp,
            ", 32, 193, 33, 48, 1, ",
            gateway_dhcp,
            ", 32, 193, 33, 48, 33, ",
            gateway_dhcp,
            ",32, 193, 33, 49, 1, ",
            gateway_dhcp,
            ", 32, 193, 33, 49, 160, ",
            gateway_dhcp,
            ", 17, 188, 230, 0,",
            gateway_dhcp,
            ";\n"
            ) INTO prefix;






    SELECT GetAllPoolsForNoPayNet(n1) INTO pools;
    RETURN CONCAT(prefix,"\n\n",pools,"\n\n }");
    END
//
delimiter ;

Окончательная сборка конфига

Объединение subnets

И функция которая соберет все части subnet вместе. (для всех подсетей) 

DROP FUNCTION GetAllSubnets;
delimiter //
CREATE FUNCTION GetAllSubnets(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE current_net INT UNSIGNED;
    DECLARE done INT DEFAULT 0;
    DECLARE all_subnets  MEDIUMTEXT DEFAULT " ";
    DECLARE cursor_net  CURSOR FOR  SELECT network  from network_mask;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;


    OPEN cursor_net;
    FETCH cursor_net INTO current_net;
    WHILE done = 0 DO
        SELECT  CONCAT(all_subnets,"\n\n\n\n", GetSubnet(current_net), "\n\n\n\n",  GetNoPaySubnet(current_net) ) INTO all_subnets;
        FETCH  cursor_net INTO current_net;
    END WHILE;
    CLOSE cursor_net;
    RETURN all_subnets;

END
//
delimiter ;

Объединение всех Classes для всех IP

DROP FUNCTION GetAllClassesForAllNets;
delimiter //
CREATE FUNCTION GetAllClassesForAllNets(n1 INT) RETURNS MEDIUMTEXT
BEGIN
    DECLARE current_net INT UNSIGNED;
    DECLARE done INT DEFAULT 0;
    DECLARE classes MEDIUMTEXT DEFAULT " ";
    DECLARE cursor_net  CURSOR FOR  SELECT network  from network_mask;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;


    OPEN cursor_net;
    FETCH cursor_net INTO current_net;
    WHILE done = 0 DO
        SELECT  CONCAT(classes," \n\n\n\n", GetAllClassesForNet(current_net)) INTO classes;
        FETCH  cursor_net INTO current_net;
    END WHILE;
    CLOSE cursor_net;
    RETURN classes;

END
//
delimiter ;

Создание статичной части конфига

Часть конфига - неизменна (префикс). Для простоты вынес ее в отдельную функцию. 

DROP FUNCTION GetPrefix;
delimiter //
CREATE FUNCTION GetPrefix(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE prefix MEDIUMTEXT DEFAULT "
ddns-update-style none;
option domain-name \"airbites.kh.ua\";
option domain-name-servers 193.33.48.33,   193.33.49.160,       193.33.48.1,    193.33.49.1,    195.69.244.2,   195.69.244.7;
option ms-classless-static-routes       code 249 = array of unsigned integer 8;
option rfc3442-classless-static-routes  code 121 = array of integer 8;
default-lease-time 300;
max-lease-time 600;
authoritative;
log-facility local7;
subnet 172.16.30.32 netmask 255.255.255.240
{
log(info,\"subnet subnet 172.16.30.32 netmask 255.255.255.240\");
}


log (info, concat(\"0 RELAYIP=\",binary-to-ascii(10, 8, \".\", packet(24, 4))));
log (info, concat(\"0 MAC=\",binary-to-ascii(16,  8, \":\", substring(hardware,                1, 6))));
log (info, concat(\"0 option dhcp-lease-time = \",binary-to-ascii(16, 8, \".\", option dhcp-lease-time)));
log (info, concat(\"0 option dhcp-message = \",binary-to-ascii(16, 8, \".\", option dhcp-message)));
log (info, concat(\"0 option dhcp-message-type = \",binary-to-ascii(16, 8, \".\", option dhcp-message-type)));
log (info, concat(\"0 option dhcp-parameter-request-list = \",binary-to-ascii(16, 8, \".\", option dhcp-parameter-request-list)));
log (info, concat(\"0 option dhcp-requested-address = \",binary-to-ascii(16, 8, \".\", option dhcp-requested-address)));
log (info, concat(\"0 option agent.link-selection = \",binary-to-ascii(16, 8, \".\", option agent.link-selection)));
";
    RETURN prefix;
END
//
delimiter ;

Окончательная сборка конфига

DROP FUNCTION GetDHCPConfing;
delimiter //
CREATE FUNCTION GetDHCPConfing(n1 int UNSIGNED) RETURNS MEDIUMTEXT
BEGIN
    DECLARE config  MEDIUMTEXT DEFAULT "";
    SELECT CONCAT( config, GetPrefix(1) ) INTO config;
    SELECT CONCAT( config, GetAllClassesForAllNets(1) ) INTO config;
    SELECT CONCAT( config, GetAllSubnets(1) ) INTO config;
    RETURN config;
END
//
delimiter ;
Источник — https://noname.com.ua/mediawiki/index.php?title=DHCP_MYSQL&oldid=5718