Le champ register représente la date de l’enregistrement du compte username fourni par l’Asterisk dont l’adresse est ‘domain’ et l’adresse de son DBA est dba, la date de réception de cet enregistrement dans DBA est reception.

Ce changement de la structure de la table location2 a impliqué des modifications dans :

1. Le script exec-registration.pl qui insère les enregistrements dans le Master (Porta-Billing).

2. La structure de la table registration utilisée par le script exec-registration.pl

mysql> desc registration;

+----------+----------------------+------+-----+----------+----------------+

+----------+----------------------+------+-----+----------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| dba | varchar(15) | YES | | NULL | |

+----------+----------------------+------+-----+----------+----------------+

9 rows in set (0.00 sec)

3. De la procedure REGISTER

DELIMITER |

DROP PROCEDURE IF EXISTS `REGISTER`|

CREATE PROCEDURE `REGISTER`(IN USR VARCHAR(80),IN DOM VARCHAR(15),IN IP_ VARCHAR(15), IN PORT_ smallint(5) unsigned, IN EXR DATETIME,IN REG DATETIME)

BEGIN

DECLARE MAXD INT(5) DEFAULT 0;

DECLARE MAXI INT(5) DEFAULT 0;

DECLARE IPAD varchar(15) DEFAULT '';

SET IPAD=(SELECT value FROM config WHERE name='ipaddr');

IF (SELECT 1 FROM location2 WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND domain=DOM AND dba=IPAD) THEN

UPDATE location2 SET expires=EXR,register=REG WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND domain=DOM AND dba=IPAD;

ELSE

INSERT INTO location2 (username,domain,dba,ipaddr,port,expires,register) VALUES (USR,DOM,IPAD,IP_,PORT_,EXR,REG);

END IF;

INSERT INTO location2_history (start,stop,domain,username,ipaddr,port,count) VALUES (REG,REG,DOM,USR,IP_,PORT_,1) ON DUPLICATE KEY UPDATE count=count+1, stop=REG;

SET MAXI=(SELECT value FROM config WHERE name='max_reg_to_check');

IF (SELECT value FROM config WHERE name='tmp_reg_to_check') < MAXI THEN

UPDATE config SET value=value+1 WHERE name='tmp_reg_to_check';

ELSE

UPDATE config SET value=1 WHERE name='tmp_reg_to_check';

SET MAXD=(SELECT value FROM config WHERE name='day_log_history');

DELETE FROM location2_history WHERE stop < DATE_ADD(NOW(), INTERVAL -MAXD DAY);

END IF;

END |

DELIMITER ;

4. Des triggers de la table location2

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Insert_location2`|

CREATE TRIGGER `Trg_Insert_location2` AFTER INSERT ON `location2` FOR EACH ROW BEGIN

DECLARE IPAD varchar(15) DEFAULT '';

SET IPAD=(SELECT value FROM config WHERE name='ipaddr');

IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL AND NEW.dba=IPAD THEN

INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,action) VALUES (NEW.username,NEW.dba,NEW.domain,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,'REGISTER');

END IF;

CALL add_multiple_ua(NEW.username,NEW.domain,NEW.ipaddr,NEW.port,"ADD");

END |

DROP TRIGGER IF EXISTS `Trg_Update_location2`|

CREATE TRIGGER `Trg_Update_location2` AFTER UPDATE ON `location2` FOR EACH ROW BEGIN

DECLARE IPAD varchar(15) DEFAULT '';

SET IPAD=(SELECT value FROM config WHERE name='ipaddr');

IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL AND NEW.dba=IPAD THEN

INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,action) VALUES (NEW.username,NEW.dba,NEW.domain,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,'REGISTER');

END IF;

END |

DROP TRIGGER IF EXISTS `Trg_Delete_location2`|

CREATE TRIGGER `Trg_Delete_location2` AFTER DELETE ON `location2` FOR EACH ROW BEGIN

DECLARE IPAD varchar(15) DEFAULT '';

SET IPAD=(SELECT value FROM config WHERE name='ipaddr');

IF OLD.dba=IPAD THEN

INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,action) VALUES (OLD.username,OLD.dba,OLD.domain,OLD.ipaddr,OLD.port,OLD.expires,OLD.register,'UNREGISTER');

END IF;

CALL add_multiple_ua(OLD.username,OLD.domain,OLD.ipaddr,OLD.port,"DEL");

END |

DELIMITER ;

L’Asterisk n’envoi plus le contact (IP + Port) sous forme d’une chaîne de caractère.

Les routines du serveur DBA utilisé les champs ci-dessous pour récupérer l’adresse IP de ce dernier :

INSERT INTO config VALUES ('ipaddr','91.121.XX.XX','ip adress.');

INSERT INTO config VALUES ('hostname','dbX.switzernet.com','server hostname.');

La table multiple-ua

On a ajouté une nouvelle table dans la base de donnée asterisk qui sera par conséquent répliqué dans tous les Asteriks, dont la structure est la suivante :

mysql> use asterisk

Database changed

mysql> desc multiple_ua;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| username | varchar(80) | NO | PRI | | |

| domain | varchar(15) | NO | PRI | | |

| ipaddr | varchar(15) | NO | PRI | | |

| port | smallint(5) unsigned | NO | PRI | 0 | |

+----------+----------------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

Cette table contiendra tous les comptes qui ont plus un enregistrement valide donc tous les URI (IP + Port) des téléphones SIP qui utilisent le même compte SIP.

Exemple

mysql> select * from multiple_ua;

+-------------+---------------+-------------+-------+

+-------------+---------------+-------------+-------+

| 41215500327 | 91.121.151.58 | 85.1.232.16 | 61864 |

| 41215500327 | 91.121.151.58 | 85.1.232.16 | 61878 |

| 41215500327 | 94.23.225.212 | 85.1.232.16 | 61860 |

+-------------+---------------+-------------+-------+

Cette table est gérée par la procédure add_multiple_ua

DELIMITER |

DROP PROCEDURE IF EXISTS `add_multiple_ua`|

CREATE PROCEDURE `add_multiple_ua`(IN USR VARCHAR(80), IN DOM VARCHAR(15),IN IP_ VARCHAR(15), IN PORT_ smallint(5) unsigned, IN ACTION VARCHAR(80))

BEGIN

DECLARE REQ text DEFAULT '';

DECLARE tdomain varchar(15) default NULL;

DECLARE tipaddr varchar(15) default NULL;

DECLARE tport smallint(5) unsigned default NULL;

DECLARE done INT DEFAULT 0;

DECLARE cur CURSOR FOR select domain,ipaddr,port from location2 where username=USR;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

IF ACTION LIKE 'ADD' THEN

IF (SELECT COUNT(username) FROM location2 WHERE username=USR ) > 1 THEN

OPEN cur;

read_loop: LOOP

FETCH cur INTO tdomain,tipaddr,tport;

IF done THEN

LEAVE read_loop;

END IF;

SET REQ=CONCAT("INSERT IGNORE INTO multiple_ua VALUES ('",USR,"','",tdomain,"','",tipaddr,"','",tport,"')");

INSERT INTO request (val) VALUES (REQ);

END LOOP;

CLOSE cur;

END IF;

ELSE

IF (SELECT COUNT(username) FROM location2 WHERE username=USR ) > 1 THEN

SET REQ=CONCAT("DELETE FROM multiple_ua WHERE username='",USR,"' AND domain='",DOM,"' AND ipaddr='",IP_,"' AND port=",PORT_);

ELSE

SET REQ=CONCAT("DELETE FROM multiple_ua WHERE username='",USR,"'");

END IF;

INSERT INTO request (val) VALUES (REQ);

END IF;

END |

DELIMITER ;

Cette procédure insère dans la table request la requête qui sera exécutée par le script exec-replication.pl.

Synchronisation des DBAs

Le script exec-registration.pl assure aussi la synchronisation de la table location2 dans tous les serveurs DBA.

Ce script cherche les enregistrements dans la table registration et il insère ces derniers dans le master (la table location) et les serveurs DBA (la table location2)

sub exec_register {

my ($str,$stp)=(0,0);

my $req1=' ';

my $req2=' ';

my @req3;

my $sth = $dbh1->prepare("SELECT * FROM registration ORDER BY id LIMIT 30");

safe_exit() if !$sth->execute();

return 0 if !$sth->rows;

while (my $result = $sth->fetchrow_hashref) {

$str=$result->{id} if !$str;

$stp=$result->{id};

if ($result->{action} eq 'REGISTER') {

$req1 .= "('".$result->{username}."','".$result->{domain}."','sip:".$result->{username}.'@'.$result->{ipaddr}.':'.$result->{port}."','".$result->{expires}."',-1.00,'Astrad-V008', 1, 1,'Not Available'),";

push(@req3, "INSERT INTO location2 (username,domain,dba,ipaddr,port,expires,register) VALUES ('".$result->{username}."','".$result->{domain}."','".$result->{dba}."','".$result->{ipaddr}."','".$result->{port}."','".$result->{expires}."','".$result->{register}."') ON DUPLICATE KEY UPDATE expires='".$result->{expires}."';");

}

elsif ($result->{action} eq 'UNREGISTER') {

$req2 .= "(username='".$result->{username}."' AND domain='".$result->{domain}."' AND contact='sip:".$result->{username}.'@'.$result->{ipaddr}.':'.$result->{port}."' AND expires='".$result->{expires}."') OR ";

push(@req3, "DELETE FROM location2 WHERE username='".$result->{username}."'AND domain='".$result->{domain}."'AND dba='".$result->{dba}."' AND expires='".$result->{expires}."'");

}

$sth->finish;

$dbh2->do("REPLACE INTO location (username,domain,contact,expires,q,callid,cseq,flags,user_agent) VALUES ".substr($req1,0,length($req1)-1)) if $req1 ne ' ';

$dbh2->do("DELETE FROM location WHERE ".substr($req2,0,length($req2)-3)) if $req2 ne ' ';

$dbh1->do("DELETE FROM registration WHERE id BETWEEN $str AND $stp");

return 1 if (!$SYNC_DBA);

foreach my $treq (@req3) {

foreach my $tdbh (@dba_dbh) {

$tdbh->do($treq);

}

return 1;

}

Les DBAs n’insèrent dans la table registration que les enregistrements dont le champ dba est son adresse IP.

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Insert_location2`|

CREATE TRIGGER `Trg_Insert_location2` AFTER INSERT ON `location2` FOR EACH ROW BEGIN

DECLARE IPAD varchar(15) DEFAULT '';

SET IPAD=(SELECT value FROM config WHERE name='ipaddr');

IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL AND NEW.dba=IPAD THEN

INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,action) VALUES (NEW.username,NEW.dba,NEW.domain,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,'REGISTER');

END IF;

CALL add_multiple_ua(NEW.username,NEW.domain,NEW.ipaddr,NEW.port,"ADD");

END |

2. Installation avec puppet

Le Zip d’installation puppet de la version 4 du serveur DBA [http://switzernet.com/3/public/110317-db3-versions/]

Changement de la hostname

# echo "dbX.switzernet.com" > /etc/hostname

# vi /etc/hosts

< 91.121.XXX.XXX dbX.switzernet.com

Activation de la nouvelle hostname

# /etc/init.d/hostname.sh

# hostname

> dbX.switzernet.com

# sed -i -e '$d' /etc/motd;sed -i -e '/^hostname/d' /etc/motd; echo -e "hostname : `hostname`\ncompany : Switzernet@2011\n" >> /etc/motd

Installation du puppet

# aptitude update

# aptitude install -y puppet

# /etc/init.d/puppet stop

> Stopping puppet configuration management tool.

Configuration du puppet

# vi /etc/puppet/puppet.conf

< pluginsync=false

Certification

# puppetd --server puppet.switzernet.com --waitforcert 60 --test

> warning: peer certificate won't be verified in this SSL session

> notice: Did not receive certificate

> notice: Set to run 'one time'; exiting with no certificate

Dans puppet master (puppet.switzernet.com)

# puppetca --list

> dbX.switzernet.com

# puppetca --sign dbX.switzernet.com

Dans le serveur DBA

# puppetd --server puppet.switzernet.com --waitforcert 60 --test

# /etc/init.d/puppet start

3. Mise à jour depuis la version 3 sans puppet

Mysql

Télécharger et exécuter le fichier SQL de la mise à jour:

# wget http://switzernet.com/3/public/110722-dba-v4/data1/110718-db3.sql

# mysql –u<User> -p<Password> < 110718-db3.sql

Ajouter l’adresse IP et le hostname du serveur DBA en question dans la table config :

# mysql –u<User> -p<Password> Astrad

mysql> INSERT INTO config VALUES ('ipaddr','<IP Adress>','ip adress.');

mysql> INSERT INTO config VALUES ('hostname','<Hostname>','server hostname.');

Autoriser l’accès des autres DBAs à la table location2, pour chaque serveur vous devez exécuter les requêtes suivantes :

# mysql –u<User> -p<Password> mysql

mysql> INSERT IGNORE INTO user (Host,User,Password) VALUES ('<IP Adress>','<User>','<Password>');

mysql> FLUSH PRIVILEGES;

mysql> GRANT all privileges ON astrad.location2 TO '<User>'@'<IP adress>' IDENTIFIED BY '<Password>';

mysql> FLUSH PRIVILEGES;

Cron

# cd /etc/cron.d/

# rm db3

# wget http://switzernet.com/3/public/110722-dba-v4/data1/dba

Script

# mv /etc/db3 /etc/dba

# cd /etc/dba

# wget http://switzernet.com/3/public/110722-dba-v4/data1/exec-registration.plc

# mv exec-registration.plc exec-registration.pl

# chmod +x exec-registration.pl

Configuration

Ajouter les adresses IP des serveurs DBAs dans le fichier de configuration setup-mysql-replication.conf

# cd /etc/dba

# vi setup-mysql-replication.conf

[DBA]

Sync=yes

User=astrob

Host=91.121.XX.XX, 91.121.XX.XX, ...

Référence

Versions du Master MySQL-Astrad (DBA)

http://switzernet.com/3/public/110317-db3-versions/

Liste des fonctionnalités à ajouter dans Astrad

http://switzernet.com/3/public/110523-astrad-wish-list/

Installation du Serveur DB3 (Master MySQL-Astrad V000)

http://switzernet.com/3/public/110316-db3-setup/

Préparation de la base de données (MySQL) pour Asterisk

http://switzernet.com/3/public/110316-astrad-triggers/

Astrad Versionning

http://switzernet.com/3/public/110126-astrad-versions/

Tester la commande Dial d’Asterisk en précisant l’adresse IP et le port du téléphone

http://switzernet.com/3/public/110630-test-dial-for-incomming-call/

Problèmes des appels entrants causés par l’expiration du NAT port mapping

http://switzernet.com/3/public/110627-astrad-nat-port-mapping/

Send empty UDP packets to keeping NAT router port alive

http://switzernet.com/3/public/110627-astrad-empty-udp-keepalive/

Astrad V8 : Test Notify

http://switzernet.com/3/public/110609-astradv8-notify/

Liste des fonctionnalités à ajouter dans Astrad

http://switzernet.com/3/public/110523-astrad-wish-list/