[Dnsmasq-discuss] Dnsmasq MySQL back-end
Michael Rack
michael.rack at rsm-freilassing.de
Mon Feb 10 06:36:23 GMT 2014
Hi audiotec69,
i use a combination of DNSMASQ and PHP to store the leases in database.
I've attached a PHP-Script that will do the job. You need to install
CreoleDB (Database Abstraction Layer).
You have to give the script executable bits (chmod 775 )
In your DNSMASQ-Configuration enable the dhcp-script option and point it
absolute to the script-path. Restart your DNSMASQ and your leases will
be stored in database.
BTW: A native MySQL-Backend-Driver will be the best solution an
recommend. But my skills in programming C are not the best to accomplish
the goals.
Liebe Grüße aus Freilassing,
Michael Rack
RSM Freilassing
--
RSM Freilassing Tel.: +49 8654 607110
Nocksteinstr. 13 Fax.: +49 8654 670438
D-83395 Freilassing www.rsm-freilassing.de
Am 10.02.2014 06:31, schrieb audiotec69:
> So apparently back in 2005 there was a patch created for dnsmasq
> version 2.20
> (http://lists.thekelleys.org.uk/pipermail/dnsmasq-discuss/2005q1/000163.html)
> to use a MySQL back-end for leases. Could this still be possible? If
> so where might I find documentation on such a configuration. If this
> is not possible, perhaps someone could send me in the right direction.
> I am working on a project that needs the ability to store leases in a
> database.
>
> Ideally I would like to be able to capture the time the lease was
> issued and the time it expired.
>
> Thanks.
>
> _______________________________________________
> Dnsmasq-discuss mailing list
> Dnsmasq-discuss at lists.thekelleys.org.uk
> http://lists.thekelleys.org.uk/mailman/listinfo/dnsmasq-discuss
-------------- next part --------------
#!/usr/bin/php
<?PHP
$params = array_slice($_SERVER['argv'], 1);
if( count($params) !== 4 AND count($params) !== 3 AND (count($params) === 1 AND $params[0] === 'init') === false )
{
printLine('Usage: store-dhcp-lease action mac-address ip-address hostname');
exit( 100 );
}
// Datenbank laden
define( 'MYSQL_LOCAL_DSN', 'mysql://username:password@unix('. rawurlencode('/tmp/mysql.sock') .')/database' );
require_once('creole/Creole.php');
// Try to get connection
$trys = 0; $maxTrys = 10;
while(true)
{
try
{
$conn = Creole::getConnection( MYSQL_LOCAL_DSN );
break;
}
catch( SQLException $e )
{
usleep( 500000 );
if( ++$trys === $maxTrys )
{
printLine($e->getMessage());
exit( 11 );
}
}
}
$now = date('Y-m-d H:i:s');
switch( strtolower($params[0]) )
{
// Add Lease
case 'add':
if( strlen($params[1]) < 17 ) $params[1] = '00:'. $params[1];
$strQuery = 'INSERT INTO `dhcp_leases` (`mac_address`, `ip_address`, `dhcp_relay`, `hostname`, `lease_end`)'.
' VALUES ('
.chr(39). mysql_escape_string($params[1]) .chr(39)
.', INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'
.', '. ( ( array_key_exists('DNSMASQ_RELAY_ADDRESS', $_ENV)
AND empty($_ENV['DNSMASQ_RELAY_ADDRESS']) === false
)
? 'INET_ATON('. chr(39). mysql_escape_string($_ENV['DNSMASQ_RELAY_ADDRESS']) . chr(39) .')'
: 'NULL'
)
.', '. ( ( array_key_exists('DNSMASQ_SUPPLIED_HOSTNAME', $_ENV)
AND $_ENV['DNSMASQ_SUPPLIED_HOSTNAME'] !== ''
)
? chr(39). mysql_escape_string($_ENV['DNSMASQ_SUPPLIED_HOSTNAME']) . chr(39)
: 'NULL'
)
.', '.chr(39). mysql_escape_string(date('Y-m-d H:i:s', (int)$_ENV['DNSMASQ_LEASE_EXPIRES'])) .chr(39)
.')';
write_log( $strQuery );
if( $conn->ExecuteUpdate( $strQuery ) > 0 )
{
printLine( sprintf('OK: Lease for ip-address %s added', $params[2]) );
}
else
{
printLine( sprintf('ERR: Failed to add lease for %s', $params[2]) );
exit( 32 );
}
exit( 0 );
break;
// Renew Lease
case 'old':
if( strlen($params[1]) < 17 ) $params[1] = '00:'. $params[1];
$strQuery = 'UPDATE `dhcp_leases`'.
' SET `lease_end`='.chr(39). mysql_escape_string(date('Y-m-d H:i:s', (int)$_ENV['DNSMASQ_LEASE_EXPIRES'])) .chr(39).
' WHERE `mac_address`='.chr(39). mysql_escape_string($params[1]) .chr(39).
' AND `ip_address`=INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'.
' AND `lease_end` >= \''. $now .'\'';
write_log( $strQuery );
if( $conn->ExecuteUpdate( $strQuery ) === 0 )
{
$strQuery = 'INSERT INTO `dhcp_leases` (`mac_address`, `ip_address`, `dhcp_relay`, `hostname`, `lease_end`)'.
' VALUES ('
.chr(39). mysql_escape_string($params[1]) .chr(39)
.', INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'
.', '. ( ( array_key_exists('DNSMASQ_RELAY_ADDRESS', $_ENV)
AND empty($_ENV['DNSMASQ_RELAY_ADDRESS']) === false
)
? 'INET_ATON('. chr(39). mysql_escape_string($_ENV['DNSMASQ_RELAY_ADDRESS']) . chr(39) .')'
: 'NULL'
)
.', '. ( ( array_key_exists('DNSMASQ_SUPPLIED_HOSTNAME', $_ENV)
AND $_ENV['DNSMASQ_SUPPLIED_HOSTNAME'] !== ''
)
? chr(39). mysql_escape_string($_ENV['DNSMASQ_SUPPLIED_HOSTNAME']) . chr(39)
: 'NULL'
)
.', '.chr(39). mysql_escape_string(date('Y-m-d H:i:s', (int)$_ENV['DNSMASQ_LEASE_EXPIRES'])) .chr(39)
.')';
write_log( $strQuery );
if( $conn->ExecuteUpdate( $strQuery ) === 0 )
{
printLine( sprintf('ERR: Lease for %s could not be reinserted into database.', $params[2]) );
exit( 42 );
}
else
{
printLine( sprintf('OK: Lease for %s reinserted.', $params[2]) );
}
}
else
{
printLine( sprintf('OK: Lease for %s updated.', $params[2]) );
}
break;
// Delete a Lease
case 'del':
write_log( 'Delete called for Lease: '. $params[2] );
if( strlen($params[1]) < 17 ) $params[1] = '00:'. $params[1];
$strQuery = 'UPDATE `dhcp_leases`'.
' SET `lease_end`=\''. $now .'\'' .
' WHERE `mac_address`='.chr(39). mysql_escape_string($params[1]) .chr(39).
' AND `ip_address`=INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'.
' AND `lease_end` >= \''. $now .'\'';
write_log( $strQuery );
if( $conn->ExecuteUpdate( $strQuery ) > 0 )
{
printLine( sprintf('OK: Lease for %s deleted', $params[2]) );
exit( 0 );
}
else
{
printLine( sprintf('ERR: Lease for %s was not found', $params[2]) );
exit( 51 );
}
break;
// Lease-File
case 'init':
$strQuery = 'SELECT MAX(UNIX_TIMESTAMP(`lease_end`)) as `expire`, `mac_address`, INET_NTOA(`ip_address`) as `ip_address` FROM `dhcp_leases`'.
' WHERE `lease_end` >= \''. $now .'\''.
' GROUP BY `mac_address`, `ip_address`';
$rs = $conn->ExecuteQuery( $strQuery );
while( $rs->next() )
{
printLine( $rs->getString('expire') .' '. $rs->getString('mac_address') .' '. $rs->getString('ip_address') .' * *' );
}
$rs->close();
unset( $rs );
exit( 0 );
break;
default:
printLine('Usage-Options: add, old, del, init');
exit( 200 );
}
/**
* Gibt an der Konsole eine Zeile aus
* @param string $line Zeile
* @return void
*/
function printLine( $line )
{
echo $line . chr(10);
echo write_log( $line );
}
/**
* Update Log-File
* @param string $line Info
* @return void
*/
function write_log( $line )
{
$fp = fopen('/tmp/dnsmasq.log', 'a');
fwrite($fp, sprintf( '%1$s %2$s', date('Y-m-d H:i:s'), $line) . chr(10));
fclose($fp);
}
?>
-------------- next part --------------
-- phpMyAdmin SQL Dump
-- version 4.0.8
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 10. Feb 2014 um 07:34
-- Server Version: 5.5.33a-MariaDB
-- PHP-Version: 5.3.2
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Datenbank: `net-rsm-connect-hotspot`
--
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `dhcp_leases`
--
CREATE TABLE IF NOT EXISTS `dhcp_leases` (
`mac_address` char(25) NOT NULL,
`ip_address` int(10) unsigned NOT NULL,
`dhcp_relay` int(11) unsigned DEFAULT NULL,
`hostname` char(255) DEFAULT NULL,
`lease_start` datetime NOT NULL,
`lease_end` datetime NOT NULL,
`last_change` datetime NOT NULL,
PRIMARY KEY (`mac_address`,`ip_address`,`lease_end`),
UNIQUE KEY `ip_address` (`ip_address`,`lease_end`)
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 COMMENT='Lease-Tabelle';
--
-- Trigger `dhcp_leases`
--
DROP TRIGGER IF EXISTS `dhcp_leases_before_insert`;
DELIMITER //
CREATE TRIGGER `dhcp_leases_before_insert` BEFORE INSERT ON `dhcp_leases`
FOR EACH ROW BEGIN
IF new.`lease_start` IS NULL
OR new.`lease_start`='0000-00-00 00:00:00'
THEN
SET new.`lease_start` = now();
END IF;
SET new.`last_change` = now();
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `dhcp_leases_before_update`;
DELIMITER //
CREATE TRIGGER `dhcp_leases_before_update` BEFORE UPDATE ON `dhcp_leases`
FOR EACH ROW BEGIN
SET new.`last_change` = now();
END
//
DELIMITER ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
More information about the Dnsmasq-discuss
mailing list