Import dhcp leases, forward rules and simple queue rules from linux server using PHP & MySQL

eviltrick · March 24, 2016, 9:47am

Hello,

In the last years I used Linux as internet router (quagga for BGP, TC for bandwidth management, isc-dhcpd, iptables for firewall and a cute web interface coded in PHP & MySQL for clients dababase).
Two months ago i’ve moved to Mikrotik and i bought a piece of CCR1036-12G-4S.

Setting BGP was a little bit didifferent but doable. However, the real job was to make Mikrotik work with Linux web interface and MySQL database that I use for clients management.

So… for all users who need useful ideas I post here 3 PHP scripts I’m using for dhcp, forward and queue.

php dhcp script

<?php
// mikrotik dhcp script

$mysql_host = "localhost";
$mysql_user = "user";
$mysql_password = "password";
$clienti_db = "clients";
$subnets_table = "conf_subnets";

$link = mysql_connect($mysql_host, $mysql_user, $mysql_password, true)
        or die("Could not connect : " . mysql_error());
mysql_select_db($clienti_db, $link) or die("Could not select database");

$subnetsquery = sprintf("SELECT * FROM `%s` ORDER BY id_subnet ASC", $subnets_table);
$subnetsresult = mysql_query($subnetsquery, $link) or die("Query failed : " . mysql_error());

print("# dhcp lease setup\n");
print("/ip dhcp-server lease\n");
print("remove [find where !dynamic]\n");
print("\n");

while ($subnet = mysql_fetch_assoc($subnetsresult)) {
printf("# clients in subnet %s\n", $subnet["nume_subnet"]);

$serverno = intval(substr($subnet['if_subnet'], -1)); // in subnets table interface column is like vlan101, vlan102, vlan103 etc.

$clientsquery = sprintf("SELECT * FROM `clienti_%s` ORDER BY id_client ASC", $subnet["nume_subnet"]);
$clientsresult = mysql_query($clientsquery, $link) or die("Query failed : " . mysql_error());

while ($client = mysql_fetch_assoc($clientsresult)) {
$comment = "{$subnet['nume_subnet']} :: {$client['numele']}";
printf("add address=\"%s\" mac-address=\"%s\" disabled=\"no\" server=\"server%d\" comment=\"%s\"\n",
       $client["adresaip"], $client["adresamac"], $serverno, $comment);
}

print("\n");
}

mysql_close($link);

// end of mikrotik dhcp script
?>

php forward script

<?php
// mikrotik forward script

$mysql_host = "localhost";
$mysql_user = "user";
$mysql_password = "password";
$clienti_db = "clients";
$subnets_table = "conf_subnets";

$link = mysql_connect($mysql_host, $mysql_user, $mysql_password, true)
        or die("Could not connect : " . mysql_error());
mysql_select_db($clienti_db, $link) or die("Could not select database");

$subnetsquery = sprintf("SELECT * FROM `%s` ORDER BY id_subnet ASC", $subnets_table);
$subnetsresult = mysql_query($subnetsquery, $link) or die("Query failed : " . mysql_error());

print("# forward setup\n");
print("/ip firewall filter\n");
print("remove [find chain=forward && action=drop]\n");
print("remove [find chain=forward]\n");
print("\n");

while ($subnet = mysql_fetch_assoc($subnetsresult)) {
printf("# clients in subnet %s\n", $subnet["nume_subnet"]);

$clientsquery = sprintf("SELECT * FROM `clienti_%s` ORDER BY id_client ASC", $subnet["nume_subnet"]);
$clientsresult = mysql_query($clientsquery, $link) or die("Query failed : " . mysql_error());

while ($client = mysql_fetch_assoc($clientsresult)) {
if ($client['restanta'] == 'nu' || $client['restanta'] == 'protocol') { $disabled = 'no'; } else { $disabled = 'yes'; }
printf("add chain=forward src-address=\"%s\" src-mac-address=\"%s\" disabled=\"%s\" action=\"accept\" comment=\"%s\"\n",
       $client['adresaip'], $client['adresamac'], $disabled, $client['numele']);
printf("add chain=forward dst-address=\"%s\" disabled=\"%s\" action=\"accept\"\n", $client['adresaip'], $disabled);
}

printf("\n");
}

mysql_close($link);

printf("add chain=forward disabled=\"no\" action=\"drop\" comment=\"DROP everything\"\n");

// end of mikrotik forward script
?>

php queue script

<?php
// mikrotik queue script

$mysql_host = "localhost";
$mysql_user = "user";
$mysql_password = "password";
$clienti_db = "clients";
$subnets_table = "conf_subnets";

$link = mysql_connect($mysql_host, $mysql_user, $mysql_password, true)
        or die("Could not connect : " . mysql_error());
mysql_select_db($clienti_db, $link) or die("Could not select database");

$subnetsquery = sprintf("SELECT * FROM `%s` ORDER BY id_subnet ASC", $subnets_table);
$subnetsresult = mysql_query($subnetsquery, $link) or die("Query failed : " . mysql_error());

print("# queue setup\n");
print("/queue simple\n");
print("remove [find dynamic=no]\n");
print("\n");
print("# add main queues\n");
print("add name=\"national\" target=\"0.0.0.0/0\" dst=\"vlan600\" parent=\"none\" packet-marks=\"\" priority=1/1 queue=default/default limit-at=500M/500M max-limit=500M/500M\n");
print("add name=\"international\" target=\"0.0.0.0/0\" dst=\"vlan573\" parent=\"none\" packet-marks=\"\" priority=1/1 queue=default/default limit-at=300M/300M max-limit=300M/300M\n");
print("\n");

while ($subnet = mysql_fetch_assoc($subnetsresult)) {
printf("# clients in subnet %s\n", $subnet["nume_subnet"]);

$clientsquery = sprintf("SELECT * FROM `clienti_%s` ORDER BY id_client ASC", $subnet["nume_subnet"]);
$clientsresult = mysql_query($clientsquery, $link) or die("Query failed : " . mysql_error());

while ($client = mysql_fetch_assoc($clientsresult)) {
if ($client['restanta'] == 'nu' || $client['restanta'] == 'protocol') { $disabled = 'no'; } else { $disabled = 'yes'; }
printf("add name=\"%s national\" target=\"%s\" dst=\"vlan600\" parent=\"national\" disabled=\"%s\" packet-marks=\"\" priority=1/1 queue=default/default limit-at=%dK/%dK max-limit=%dM/%dM\n",
      $client['numele'], $client['adresaip'], $disabled, $client['min_metro_up'], $client['min_metro'], $client['max_metro_up'], $client['max_metro']);
printf("add name=\"%s international\" target=\"%s\" dst=\"vlan573\" parent=\"international\" disabled=\"%s\" packet-marks=\"\" priority=1/1 queue=default/default limit-at=%dK/%dK max-limit=%dM/%dM\n",
      $client['numele'], $client['adresaip'], $disabled, $client['min_net_up'], $client['min_net'], $client['max_net_up'], $client['max_net']);
}

print("\n");
}

mysql_close($link);

print("# default\n");
printf("add name=\"%s national\" target=\"%s\" dst=\"vlan600\" parent=\"national\" packet-marks=\"\" priority=1/1 queue=default/default limit-at=%dK/%dK max-limit=%dK/%dK\n",
       "default", "0.0.0.0/0", 16, 16, 16, 16);
printf("add name=\"%s international\" target=\"%s\" dst=\"vlan573\" parent=\"international\" packet-marks=\"\" priority=1/1 queue=default/default limit-at=%dK/%dK max-limit=%dK/%dK\n",
       "default", "0.0.0.0/0", 16, 16, 16, 16);

// end of mikrotik queue script
?>

And of course mikrotik scripts:

# reload-dhcp
/tool fetch url=http://server-name/dhcp.php dst-path=dhcp.rsc
/import dhcp.rsc

# reload-forward
/tool fetch url=http://server-name/forward.php dst-path=forward.rsc
/import forward.rsc

# reload-queue
/tool fetch url=http://server-name/queue.php dst-path=queue.rsc
/import queue.rsc

Scheduler export (reload forward every 10 minutes starting from boot, reload queue every day at 05:00AM):

/system scheduler
add interval=10m name=reload-forward on-event=reload-forward policy=ftp,reboot,read,write,policy,test,password,sniff,sensitive start-time=startup
add interval=1d name=reload-queue on-event=reload-queue policy=ftp,reboot,read,write,policy,test,password,sniff,sensitive start-date=mar/23/2016 start-time=05:00:00

DHCP leases reload is made from web interface using Mikrotik API:

$API = new RouterosAPI(); $API->debug = false;

if ($API->connect('mikrotik-ip', 'user', 'password')) {
$API->comm("/system/script/run", array('number'=>'reload-dhcp'));
$API->disconnect();
echo '<b>OK: DHCP leases written and loaded</b>';
} else {
echo '<b>ERROR: Cannot connect to Mikrotik router!</b>';
}