DHCP MYSQL: различия между версиями
Материал из noname.com.ua
Перейти к навигацииПерейти к поискуSirmax (обсуждение | вклад) |
Sirmax (обсуждение | вклад) |
||
| Строка 408: | Строка 408: | ||
// |
// |
||
delimiter ; |
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 " "; |
||
| + | SELECT mask from network_mask WHERE network=n1 INTO mask1; |
||
| + | 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; |
||
| + | |||
| + | 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; |
||
| + | END WHILE; |
||
| + | CLOSE cursor_ip; |
||
| + | RETURN pools; |
||
| + | END |
||
| + | // |
||
| + | delimiter ; |
||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
</PRE> |
</PRE> |
||
Версия 13:48, 6 апреля 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
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 " ";
SELECT mask from network_mask WHERE network=n1 INTO mask1;
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;
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;
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 ;
Полный конфиг 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 ;
