DHCP MYSQL: различия между версиями
Материал из noname.com.ua
Перейти к навигацииПерейти к поискуSirmax (обсуждение | вклад) |
Sirmax (обсуждение | вклад) |
||
| Строка 326: | Строка 326: | ||
</PRE> |
</PRE> |
||
| ⚫ | |||
<PRE> |
<PRE> |
||
DROP FUNCTION GetAllPoolsForNoPayNet; |
DROP FUNCTION GetAllPoolsForNoPayNet; |
||
| Строка 373: | Строка 372: | ||
1 row in set (13.79 sec) |
1 row in set (13.79 sec) |
||
</PRE> |
</PRE> |
||
| ⚫ | |||
Версия 22:48, 26 марта 2010
Конфиг для 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 ,
INET_NTOA(network_mask.network) AS network
FROM
mtraf,
network_mask
WHERE
INET_ATON(mtraf.ip)&network_mask.mask=network_mask.network;
Условие в IF - условие когда абонент "должен" - ему нужно дать другой IP (у меня вместо, например, 10,0,136,10 он получит 10,100,136,10) и соответвенно, пускать только на биллинг (если короко, то файрволл не пускает абонентов никуда кроме отдельного ДНСа (или отдельного view) который на все запросы отвечает адресом странички "дай денег, дорогой абонент" и собственно биллинга) Всем кто будет напоминать про dhcp snooping, opt 82 и прочее - это все делается для остатоков неуправляемых сегментов.
Дополнительные данные о сети
В биллинге (в моем случае) нет никакой информации о топологии сети). Нужно добавить данные о сетях, релеях, гейтвеях. Отмечу, что в сети есть как минимум 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'));
Процедуры и функции
Решил максимально разбить задачу на отдельные атомарные функции, бляго, MySQL к этому распологает.
Получение маски для IP
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)
Получение списка relay для сети
Курсоры - довольно удобно.
DROP FUNCTION GetRelayForNet;
delimiter //
CREATE FUNCTION GetRelayForNet(n1 int UNSIGNED) RETURNS char(255)
BEGIN
-- done is "end of cycle" variable
DECLARE done INT DEFAULT 0;
DECLARE current_relay VARCHAR(255);
DECLARE dhcp_relay VARCHAR(255);
DECLARE dhcp_relay_res VARCHAR(255) default "";
-- cursor for relay
DECLARE cursor_relay CURSOR FOR SELECT relay from network_relay WHERE network=n1 LIMIT 1,18446744073709551615;
-- set done=1 when cusror ends
-- эта конструкция применяется для завершения цикла
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN cursor_relay;
-- Так как надо собрать часть конфига, то первый элемент немного отличается (не имеет прификса "or .."
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;
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
Для простоты разделелил генерацию класса на несколько частей
DROP FUNCTION GetClassForIP;
delimiter //
CREATE FUNCTION GetClassForIP(ip1 int UNSIGNED) RETURNS char(255)
BEGIN
DECLARE mac_dhcp VARCHAR(255);
SELECT lower(substr(replace(concat(':', mac), ':0', ':') from 2)) INTO mac_dhcp FROM mtraf_status WHERE INET_ATON(mtraf_status.ip)=ip1 LIMIT 1;
RETURN CONCAT("class \"",INET_NTOA(ip1),"__",mac_dhcp,"\" { match if( ( binary-to-ascii(16, 8, \":\", substring(hardware,1, 6)) = \"",mac_dhcp," 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)
Определить какой сети принадлежит 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;
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)
создание пула для IP
DROP FUNCTION GetPoolForIP;
delimiter //
CREATE FUNCTION GetPoolForIP(ip1 int UNSIGNED) RETURNS TEXT
BEGIN
DECLARE mac_dhcp VARCHAR(255);
SELECT lower(substr(replace(concat(':', mac), ':0', ':') from 2)) INTO mac_dhcp FROM mtraf WHERE INET_ATON(mtraf.ip)=ip1 LIMIT 1;
RETURN CONCAT( "pool { range ",INET_NTOA(ip1),"; allow members of ",INET_NTOA(ip1), "__", mac_dhcp, " default-lease-time 36000; max-lease-time 72000; } ");
END
//
delimiter ;
Пример:
mysql> SELECT GetPoolForIP(INET_ATON('10.0.136.11'));
+----------------------------------------------------------------------------------------------------------------------------+
| GetPoolForIP(INET_ATON('10.0.136.11')) |
+----------------------------------------------------------------------------------------------------------------------------+
| pool { range 10.0.136.11; allow members of 10.0.136.11__0:13:d4:5:d9:f4 default-lease-time 36000; max-lease-time 72000; } |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)
создание пула для ip неплатильщика
Возможно тут надо переписать соеденив 2 ф-и в одну (уточняю)
DROP FUNCTION GetPoolForNoPayIP;
delimiter //
CREATE FUNCTION GetPoolForNoPayIP(ip1 int UNSIGNED) RETURNS TEXT
BEGIN
DECLARE mac_dhcp VARCHAR(255);
SELECT lower(substr(replace(concat(':', mac), ':0', ':') from 2)) INTO mac_dhcp FROM mtraf WHERE INET_ATON(mtraf.ip)=ip1-6553600 LIMIT 1;
RETURN CONCAT( "pool { range ",INET_NTOA(ip1),"; allow members of ",INET_NTOA(ip1), "__", mac_dhcp, " default-lease-time 36000; max-lease-time 72000; } ");
END
//
delimiter ;
Пример:
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 TEXT
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE mask1 int UNSIGNED;
DECLARE current_ip int UNSIGNED DEFAULT 0;
DECLARE pools TEXT 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 GetAllPoolsForNoPayNet;
delimiter //
CREATE FUNCTION GetAllPoolsForNoPayNet(n1 int UNSIGNED) RETURNS TEXT
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE mask1 int UNSIGNED;
DECLARE current_ip int UNSIGNED DEFAULT 0;
DECLARE pools TEXT 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 ;
Пример (поскипано):
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)
