Installation du Serveur DB3 (Master MySQL-Astrad)
Updated by Nicolas, 2011-01-21
Oussama Hammami, 2011-01-07
Switzernet
Ce document explique l’installation du serveur Master MySQL-Astrad version 0.
Dans cette version, on ne prend pas en considération la configuration les réplications MySQL.
Vous devez commencer par l’installation su serveur MySQL.
# aptitude install mysql-server
Télécharger le fichier 110317-db3.sql [http://switzernet.com/3/public/110317-db3-versions/data1/110317-db3.sql]
# wget http://switzernet.com/3/public/110317-db3-versions/data1/110317-db3.sql
Créer les bases, leurs routines et leurs triggers
# mysql -uroot -p < 110317-db3.sql
-p : si vous avez défini un mot de passe pour l’utilisateur root lors de l’installation de MySQL.
A cette étape, les bases de données ainsi que les routines sont correctement créés mais elles sont vides.
Pour remplir la base de données porta-billing vous pouvez l’exporter depuis DB2 (dump)
Essayons tous d’abord quelques testes.
Connecter l’interface de MySQL :
# mysql –uroot -p
Vous devez avoir les mêmes résultats de ces requêtes MySQL
mysql> show databases;
+------------------------+
| Database |
+------------------------+
| information_schema |
| ast-replication |
| astrad |
| mysql |
| porta-billing |
+------------------------+
5 rows in set (0.00 sec)
mysql> select SPECIFIC_NAME, ROUTINE_TYPE, ROUTINE_SCHEMA from `information_schema`.ROUTINES;
+-----------------+--------------+-----------------+
| SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA |
+-----------------+--------------+-----------------+
| get_deny | FUNCTION | ast-replication |
| SET_REALM | PROCEDURE | ast-replication |
| INSERT_USER | PROCEDURE | porta-billing |
| UPDATE_CUSTOMER | PROCEDURE | porta-billing |
+-----------------+--------------+-----------------+
4 rows in set (0.00 sec)
mysql> select TRIGGER_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING from `information_schema`.TRIGGERS;
+-----------------+--------------------+-------------------------+--------------------+---------------+
| TRIGGER_SCHEMA | EVENT_OBJECT_TABLE | TRIGGER_NAME | EVENT_MANIPULATION | ACTION_TIMING |
+-----------------+--------------------+-------------------------+--------------------+---------------+
| ast-replication | ip_customers | Trg_Insert_ip_customers | INSERT | AFTER |
| ast-replication | ip_customers | Trg_Update_ip_customers | UPDATE | AFTER |
| ast-replication | ip_customers | Trg_Delete_ip_customers | DELETE | AFTER |
| ast-replication | realm | Trg_Insert_realm | INSERT | AFTER |
| ast-replication | realm | Trg_Update_realm | UPDATE | AFTER |
| ast-replication | realm | Trg_Delete_realm | DELETE | AFTER |
| ast-replication | users | Trg_Insert_users | INSERT | AFTER |
| ast-replication | users | Trg_Update_users | UPDATE | AFTER |
| ast-replication | users | Trg_Delete_users | DELETE | AFTER |
| ast-replication | vendors | Trg_Insert_vendors | INSERT | AFTER |
| ast-replication | vendors | Trg_Update_vendors | UPDATE | AFTER |
| ast-replication | vendors | Trg_Delete_vendors | DELETE | AFTER |
| porta-billing | Accounts | Trg_Insert_Accounts | INSERT | AFTER |
| porta-billing | Accounts | Trg_Update_Accounts | UPDATE | AFTER |
| porta-billing | Accounts | Trg_Delete_Accounts | DELETE | AFTER |
| porta-billing | Connections | Trg_Insert_Connections | INSERT | AFTER |
| porta-billing | Connections | Trg_Update_Connections | UPDATE | AFTER |
| porta-billing | Connections | Trg_Delete_Connections | DELETE | AFTER |
| porta-billing | Customers | Trg_Update_Customers | UPDATE | AFTER |
| porta-billing | Nodes | Trg_Insert_Nodes | INSERT | AFTER |
| porta-billing | Nodes | Trg_Update_Nodes | UPDATE | AFTER |
| porta-billing | Nodes | Trg_Delete_Nodes | DELETE | AFTER |
+-----------------+--------------------+-------------------------+--------------------+---------------+
22 rows in set (0.01 sec)
mysql> use ast-replication;
Database changed
mysql> show tables ;
+---------------------------+
| Tables_in_ast-replication |
+---------------------------+
| ip_customers |
| realm |
| triggers_log |
| users |
| vendors |
+---------------------------+
5 rows in set (0.00 sec)
mysql> select * from realm;
+----+------------+
| id | val |
+----+------------+
| 1 | switzernet |
+----+------------+
1 row in set (0.00 sec)
mysql> use astrad;
Database changed
mysql> show tables ;
+------------------+
| Tables_in_astrad |
+------------------+
| sippeers |
| sipusers |
+------------------+
2 rows in set (0.00 sec)
mysql> use porta-billing;
Database changed
mysql> show tables ;
+-------------------------+
| Tables_in_porta-billing |
+-------------------------+
| Accounts |
| Connections |
| Customers |
| Nodes |
+-------------------------+
4 rows in set (0.00 sec)
mysql> -- Insertion d’un nouveau client 'Swiss offices'
mysql> INSERT INTO Customers VALUES (1,NULL,'Swiss offices','0000-00-00 00:00:00',"1314.41000",'CHF','','','','','','','','','','','','','','','','','','','','','billing@switzernet.com','billing@switzernet.com','S','41215500329','cl9alero',1,1,NULL,96,"2220.00000",20613,1,160,NULL,0,'N','N',5,'N',NULL,'2011-01-03 03:44:36','YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',0,NULL,NULL,'N','Y',136,"0.00000","0.00000",NULL,'en','local_to_e164($_,{cc=>\'41\',dp=>\'0\',ip=>\'00\'});#% cc=41 dp=0 ip=00',NULL,'80%','N','2011-03-09 15:00:00',NULL,NULL,NULL,'2008-12-24 08:52:10',NULL,1,'NNNNNNN',1,NULL,"0.00000",'O');
Query OK, 1 row affected (0.00 sec)
mysql> select i_customer,name,i_rep from Customers;
+------------+---------------+-------+
| i_customer | name | i_rep |
+------------+---------------+-------+
| 1 | Swiss offices | 5 |
+------------+---------------+-------+
1 row in set (0.00 sec)
mysql> -- Insertion du compte 41215500001
mysql> INSERT INTO Accounts VALUES (1,'41215500001',NULL,'PASSWORD1',"253.06000","0.00000",'2009-09-08',NULL,1,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',1,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');
mysql> -- Insertion du compte 41215500002
mysql> INSERT INTO Accounts VALUES (2,'41215500002',NULL,'PASSWORD2',"253.06000","0.00000",'2009-09-08',NULL,1,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',2,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');
mysql> -- Insertion du compte 41215500003
mysql> INSERT INTO Accounts VALUES (3,'41215500003',NULL,'PASSWORD3',"253.06000","0.00000",'2009-09-08',NULL,1,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',3,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');
mysql> -- Insertion du compte 41215500004
mysql> INSERT INTO Accounts VALUES (4,'41215500004',NULL,'PASSWORD4',"253.06000","0.00000",'2009-09-08',NULL,1,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',4,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');
mysql> -- Insertion du client IP 192.168.1.1
mysql> INSERT INTO Accounts VALUES (5,'192.168.1.1',NULL,'',"253.06000","0.00000",'2009-09-08',NULL,1,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',5,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');
mysql> -- Insertion du client IP 192.168.1.2
mysql> INSERT INTO Accounts VALUES (6,'192.168.1.2',NULL,'',"253.06000","0.00000",'2009-09-08',NULL,1,1,'2009-09-08',NULL,69,'N','2011-03-08 00:20:52',6,1,'2010-01-07',NULL,NULL,33,'en',96,NULL,'CHF',"0.00000","13.00000",NULL,'','2011-02-15 02:13:54','N',155,25830,'en',NULL,NULL,NULL,'Y',NULL,NULL,'YYYY-MM-DD','HH24:MI:SS','YYYY-MM-DD HH24:MI:SS','YYYY-MM-DD','HH24:MI:SS',NULL,NULL,NULL,'NA^^N');
mysql> select i_account,i_customer,id,h323_password,i_env from Accounts;
+-----------+------------+-------------+---------------+-------+
| i_account | i_customer | id | h323_password | i_env |
+-----------+------------+-------------+---------------+-------+
| 6 | 1 | 192.168.1.2 | | 1 | Client IP
| 5 | 1 | 192.168.1.1 | | 1 | Client IP
| 4 | 1 | 41215500004 | PASSWORD4 | 1 | Compte SIP
| 3 | 1 | 41215500003 | PASSWORD3 | 1 | Compte SIP
| 2 | 1 | 41215500002 | PASSWORD2 | 1 | Compte SIP
| 1 | 1 | 41215500001 | PASSWORD1 | 1 | Compte SIP
+-----------+------------+-------------+---------------+-------+
6 rows in set (0.00 sec)
mysql> select * from `ast-replication`.triggers_log order by id desc ;
+----+--------------+--------+---------------------+----------------------------------------------+
| id | table_name | event | log_time | comment |
+----+--------------+--------+---------------------+----------------------------------------------+
| 70 | Accounts | INSERT | 2011-03-17 18:41:54 | Add to ip_customers 192.168.1.2 |
| 69 | ip_customers | INSERT | 2011-03-17 18:41:54 | Update deny in sipusers 192.168.1.2 |
| 68 | ip_customers | INSERT | 2011-03-17 18:41:54 | Add to sippeers 192.168.1.2 context=fromhost |
| 67 | Accounts | INSERT | 2011-03-17 18:41:43 | Add to ip_customers 192.168.1.1 |
| 66 | ip_customers | INSERT | 2011-03-17 18:41:43 | Update deny in sipusers 192.168.1.1 |
| 65 | ip_customers | INSERT | 2011-03-17 18:41:43 | Add to sippeers 192.168.1.1 context=fromhost |
| 64 | Accounts | INSERT | 2011-03-17 18:41:33 | Add to users 41215500004 |
| 63 | users | INSERT | 2011-03-17 18:41:33 | Add to sippeers 41215500004 |
| 62 | users | INSERT | 2011-03-17 18:41:33 | Add to sipusers 41215500004 |
| 61 | Accounts | INSERT | 2011-03-17 18:41:22 | Add to users 41215500003 |
| 60 | users | INSERT | 2011-03-17 18:41:22 | Add to sippeers 41215500003 |
| 59 | users | INSERT | 2011-03-17 18:41:22 | Add to sipusers 41215500003 |
| 58 | Accounts | INSERT | 2011-03-17 18:41:07 | Add to users 41215500002 |
| 57 | users | INSERT | 2011-03-17 18:41:07 | Add to sippeers 41215500002 |
| 56 | users | INSERT | 2011-03-17 18:41:07 | Add to sipusers 41215500002 |
| 55 | Accounts | INSERT | 2011-03-17 18:40:57 | Add to users 41215500001 |
| 54 | users | INSERT | 2011-03-17 18:40:57 | Add to sippeers 41215500001 |
| 53 | users | INSERT | 2011-03-17 18:40:57 | Add to sipusers 41215500001 |
+----+--------------+--------+---------------------+----------------------------------------------+
18 rows in set (0.00 sec)
mysql> select * from `ast-replication`.users;
+----+-------------+----------------------------------+
| id | username | md5secret |
+----+-------------+----------------------------------+
| 8 | 41215500004 | 1424dbdb67f690a7d2fd4d8349c97c5b |
| 7 | 41215500003 | 2b04ad62e33f5f12c3fb8279e92f7873 |
| 6 | 41215500002 | 7e4feda6440c3d42fce6b3b902d51b43 |
| 5 | 41215500001 | 6f223e3916c49c3abda5d2297e6106ec |
+----+-------------+----------------------------------+
4 rows in set (0.00 sec)
mysql> select * from `ast-replication`.ip_customers;
+----+-------------+---------+
| id | remote_ip | is_node |
+----+-------------+---------+
| 5 | 192.168.1.2 | N |
| 4 | 192.168.1.1 | N |
+----+-------------+---------+
2 rows in set (0.00 sec)
mysql> select * from astrad.sipusers;
+----+-------------+-------------+----------------------------------+------+-------------------------+
| id | name | context | md5secret | nat | deny |
+----+-------------+-------------+----------------------------------+------+-------------------------+
| 6 | 41215500002 | fromaccount | 7e4feda6440c3d42fce6b3b902d51b43 | yes | 192.168.1.1;192.168.1.2 |
| 8 | 41215500004 | fromaccount | 1424dbdb67f690a7d2fd4d8349c97c5b | yes | 192.168.1.1;192.168.1.2 |
| 7 | 41215500003 | fromaccount | 2b04ad62e33f5f12c3fb8279e92f7873 | yes | 192.168.1.1;192.168.1.2 |
| 5 | 41215500001 | fromaccount | 6f223e3916c49c3abda5d2297e6106ec | yes | 192.168.1.1;192.168.1.2 |
+----+-------------+-------------+----------------------------------+------+-------------------------+
4 rows in set (0.00 sec)
mysql> select * from astrad.sippeers;
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
| id | name | host | context | insecure | ipaddr | port | regseconds | username | md5secret | nat |
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
| 14 | 192.168.1.2 | 192.168.1.2 | fromhost | port | NULL | 5060 | NULL | NULL | NULL | yes |
| 13 | 192.168.1.1 | 192.168.1.1 | fromhost | port | NULL | 5060 | NULL | NULL | NULL | yes |
| 12 | 41215500004 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 1424dbdb67f690a7d2fd4d8349c97c5b | yes |
| 11 | 41215500003 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 2b04ad62e33f5f12c3fb8279e92f7873 | yes |
| 9 | 41215500001 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 6f223e3916c49c3abda5d2297e6106ec | yes |
| 10 | 41215500002 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 7e4feda6440c3d42fce6b3b902d51b43 | yes |
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
6 rows in set (0.00 sec)
mysql> -- Insertion d’une nouvelle node 192.168.2.1
mysql> INSERT INTO Nodes VALUES (1,'192.168.2.1','test.switzernet.com','test.switzernet.com','VOIP-GW','Y','k',1,NULL,NULL,'Cisco',345,'c','s/^011//; s/^00//; s/^3450011//; s/^3950011//; s/^3542011//; s/^627463//;','192.168.2.2',NULL,11,NULL);
mysql> select i_node,ip,i_env,i_node_type from Nodes;
+--------+-------------+-------+-------------+
| i_node | ip | i_env | i_node_type |
+--------+-------------+-------+-------------+
| 1 | 192.168.2.1 | 1 | 11 |
+--------+-------------+-------+-------------+
1 row in set (0.00 sec)
mysql> select * from `ast-replication`.triggers_log order by id desc ;
+----+--------------+--------+---------------------+-------------------------------------------------------------------------+
| id | table_name | event | log_time | comment |
+----+--------------+--------+---------------------+-------------------------------------------------------------------------+
| 74 | Nodes | INSERT | 2011-03-17 18:49:06 | Add to ip_customers and vendors 192.168.2.1 |
| 73 | vendors | INSERT | 2011-03-17 18:49:06 | Vendor already exist on sippeers (ip existe on ip_customers) 192.168.2.1|
| 72 | ip_customers | INSERT | 2011-03-17 18:49:06 | Update deny in sipusers 192.168.2.1 |
| 71 | ip_customers | INSERT | 2011-03-17 18:49:06 | Add to sippeers 192.168.2.1 context=fromnode |
| 70 | Accounts | INSERT | 2011-03-17 18:41:54 | Add to ip_customers 192.168.1.2 |
| 69 | ip_customers | INSERT | 2011-03-17 18:41:54 | Update deny in sipusers192.168.1.2 |
| 68 | ip_customers | INSERT | 2011-03-17 18:41:54 | Add to sippeers 192.168.1.2 context=fromhost |
| 67 | Accounts | INSERT | 2011-03-17 18:41:43 | Add to ip_customers 192.168.1.1 |
| 66 | ip_customers | INSERT | 2011-03-17 18:41:43 | Update deny in sipusers192.168.1.1 |
| 65 | ip_customers | INSERT | 2011-03-17 18:41:43 | Add to sippeers 192.168.1.1 context=fromhost |
| 64 | Accounts | INSERT | 2011-03-17 18:41:33 | Add to users 41215500004 |
| 63 | users | INSERT | 2011-03-17 18:41:33 | Add to sippeers 41215500004 |
| 62 | users | INSERT | 2011-03-17 18:41:33 | Add to sipusers 41215500004 |
| 61 | Accounts | INSERT | 2011-03-17 18:41:22 | Add to users 41215500003 |
| 60 | users | INSERT | 2011-03-17 18:41:22 | Add to sippeers 41215500003 |
| 59 | users | INSERT | 2011-03-17 18:41:22 | Add to sipusers 41215500003 |
| 58 | Accounts | INSERT | 2011-03-17 18:41:07 | Add to users 41215500002 |
| 57 | users | INSERT | 2011-03-17 18:41:07 | Add to sippeers 41215500002 |
| 56 | users | INSERT | 2011-03-17 18:41:07 | Add to sipusers 41215500002 |
| 55 | Accounts | INSERT | 2011-03-17 18:40:57 | Add to users 41215500001 |
| 54 | users | INSERT | 2011-03-17 18:40:57 | Add to sippeers 41215500001 |
| 53 | users | INSERT | 2011-03-17 18:40:57 | Add to sipusers 41215500001 |
+----+--------------+--------+---------------------+-------------------------------------------------------------------------+
mysql> select * from `ast-replication`.ip_customers;
+----+-------------+---------+
| id | remote_ip | is_node |
+----+-------------+---------+
| 6 | 192.168.2.1 | Y |
| 5 | 192.168.1.2 | N |
| 4 | 192.168.1.1 | N |
+----+-------------+---------+
3 rows in set (0.00 sec)
mysql> select * from `ast-replication`.vendors;
+----+-------------+
| id | remote_ip |
+----+-------------+
| 4 | 192.168.2.1 |
+----+-------------+
1 row in set (0.00 sec)
mysql> select * from astrad.sipusers;
+----+-------------+-------------+----------------------------------+------+-------------------------------------+
| id | name | context | md5secret | nat | deny |
+----+-------------+-------------+----------------------------------+------+-------------------------------------+
| 6 | 41215500002 | fromaccount | 7e4feda6440c3d42fce6b3b902d51b43 | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
| 8 | 41215500004 | fromaccount | 1424dbdb67f690a7d2fd4d8349c97c5b | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
| 7 | 41215500003 | fromaccount | 2b04ad62e33f5f12c3fb8279e92f7873 | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
| 5 | 41215500001 | fromaccount | 6f223e3916c49c3abda5d2297e6106ec | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
+----+-------------+-------------+----------------------------------+------+-------------------------------------+
4 rows in set (0.00 sec)
mysql> select * from astrad.sippeers;
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
| id | name | host | context | insecure | ipaddr | port | regseconds | username | md5secret | nat |
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
| 14 | 192.168.1.2 | 192.168.1.2 | fromhost | port | NULL | 5060 | NULL | NULL | NULL | yes |
| 13 | 192.168.1.1 | 192.168.1.1 | fromhost | port | NULL | 5060 | NULL | NULL | NULL | yes |
| 12 | 41215500004 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 1424dbdb67f690a7d2fd4d8349c97c5b | yes |
| 11 | 41215500003 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 2b04ad62e33f5f12c3fb8279e92f7873 | yes |
| 9 | 41215500001 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 6f223e3916c49c3abda5d2297e6106ec | yes |
| 10 | 41215500002 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 7e4feda6440c3d42fce6b3b902d51b43 | yes |
| 15 | 192.168.2.1 | 192.168.2.1 | fromnode | port | NULL | 5060 | NULL | NULL | NULL | yes |
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
7 rows in set (0.00 sec)
mysql> -- Insertion d’une connexion 192.168.3.1
mysql> INSERT INTO Connections VALUES (1,55,'Test1',NULL,228,'originate','VoIP',NULL,NULL,1,'s/^\\+//;','192.168.3.1',10,0.0000,NULL,'s/^(\\d)/\\+$1/;',NULL,'Y','N','s/^(\\d)/\\+$1/;',2);
mysql> -- Insertion d’une connexion 192.168.1.2 (qui aussi un client IP)
mysql> INSERT INTO Connections VALUES (2,55,'Test2',NULL,228,'originate','VoIP',NULL,NULL,1,'s/^\\+//;','192.168.1.2',10,0.0000,NULL,'s/^(\\d)/\\+$1/;',NULL,'Y','N','s/^(\\d)/\\+$1/;',2);
mysql> select i_connection,remote_ip,i_env,sip from Connections;
+--------------+-------------+-------+-----+
| i_connection | remote_ip | i_env | sip |
+--------------+-------------+-------+-----+
| 2 | 192.168.1.2 | 1 | Y |
| 1 | 192.168.3.1 | 1 | Y |
+--------------+-------------+-------+-----+
2 rows in set (0.00 sec)
mysql> select * from `ast-replication`.triggers_log order by id desc ;
+----+--------------+--------+---------------------+-------------------------------------------------------------------------+
| id | table_name | event | log_time | comment |
+----+--------------+--------+---------------------+-------------------------------------------------------------------------+
| 78 | Connections | INSERT | 2011-03-17 18:58:47 | Add to vendors 192.168.1.2 |
| 77 | vendors | INSERT | 2011-03-17 18:58:47 | Vendor already exist on sippeers (ip existe on ip_customers)192.168.1.2 |
| 76 | Connections | INSERT | 2011-03-17 18:57:55 | Add to vendors 192.168.3.1 |
| 75 | vendors | INSERT | 2011-03-17 18:57:55 | Add vendor to sippeers 192.168.3.1 |
| 74 | Nodes | INSERT | 2011-03-17 18:49:06 | Add to ip_customers and vendors 192.168.2.1 |
| 73 | vendors | INSERT | 2011-03-17 18:49:06 | Vendor already exist on sippeers (ip existe on ip_customers)192.168.2.1 |
| 72 | ip_customers | INSERT | 2011-03-17 18:49:06 | Update deny in sipusers192.168.2.1 |
| 71 | ip_customers | INSERT | 2011-03-17 18:49:06 | Add to sippeers 192.168.2.1 context=fromnode |
| 70 | Accounts | INSERT | 2011-03-17 18:41:54 | Add to ip_customers 192.168.1.2 |
| 69 | ip_customers | INSERT | 2011-03-17 18:41:54 | Update deny in sipusers192.168.1.2 |
| 68 | ip_customers | INSERT | 2011-03-17 18:41:54 | Add to sippeers 192.168.1.2 context=fromhost |
| 67 | Accounts | INSERT | 2011-03-17 18:41:43 | Add to ip_customers 192.168.1.1 |
| 66 | ip_customers | INSERT | 2011-03-17 18:41:43 | Update deny in sipusers192.168.1.1 |
| 65 | ip_customers | INSERT | 2011-03-17 18:41:43 | Add to sippeers 192.168.1.1 context=fromhost |
| 64 | Accounts | INSERT | 2011-03-17 18:41:33 | Add to users 41215500004 |
| 63 | users | INSERT | 2011-03-17 18:41:33 | Add to sippeers 41215500004 |
| 62 | users | INSERT | 2011-03-17 18:41:33 | Add to sipusers 41215500004 |
| 61 | Accounts | INSERT | 2011-03-17 18:41:22 | Add to users 41215500003 |
| 60 | users | INSERT | 2011-03-17 18:41:22 | Add to sippeers 41215500003 |
| 59 | users | INSERT | 2011-03-17 18:41:22 | Add to sipusers 41215500003 |
| 58 | Accounts | INSERT | 2011-03-17 18:41:07 | Add to users 41215500002 |
| 57 | users | INSERT | 2011-03-17 18:41:07 | Add to sippeers 41215500002 |
| 56 | users | INSERT | 2011-03-17 18:41:07 | Add to sipusers 41215500002 |
| 55 | Accounts | INSERT | 2011-03-17 18:40:57 | Add to users 41215500001 |
| 54 | users | INSERT | 2011-03-17 18:40:57 | Add to sippeers 41215500001 |
| 53 | users | INSERT | 2011-03-17 18:40:57 | Add to sipusers 41215500001 |
+----+--------------+--------+---------------------+-------------------------------------------------------------------------+
26 rows in set (0.00 sec)
mysql> select * from `ast-replication`.ip_customers;
+----+-------------+---------+
| id | remote_ip | is_node |
+----+-------------+---------+
| 6 | 192.168.2.1 | Y |
| 5 | 192.168.1.2 | N |
| 4 | 192.168.1.1 | N |
+----+-------------+---------+
3 rows in set (0.00 sec)
mysql> select * from `ast-replication`.vendors;
+----+-------------+
| id | remote_ip |
+----+-------------+
| 6 | 192.168.1.2 |
| 5 | 192.168.3.1 |
| 4 | 192.168.2.1 |
+----+-------------+
3 rows in set (0.41 sec)
mysql> select * from astrad.sipusers;
+----+-------------+-------------+----------------------------------+------+-------------------------------------+
| id | name | context | md5secret | nat | deny |
+----+-------------+-------------+----------------------------------+------+-------------------------------------+
| 6 | 41215500002 | fromaccount | 7e4feda6440c3d42fce6b3b902d51b43 | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
| 8 | 41215500004 | fromaccount | 1424dbdb67f690a7d2fd4d8349c97c5b | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
| 7 | 41215500003 | fromaccount | 2b04ad62e33f5f12c3fb8279e92f7873 | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
| 5 | 41215500001 | fromaccount | 6f223e3916c49c3abda5d2297e6106ec | yes | 192.168.1.1;192.168.1.2;192.168.2.1 |
+----+-------------+-------------+----------------------------------+------+-------------------------------------+
4 rows in set (0.00 sec)
mysql> select * from astrad.sippeers;
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
| id | name | host | context | insecure | ipaddr | port | regseconds | username | md5secret | nat |
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
| 14 | 192.168.1.2 | 192.168.1.2 | fromhost | port | NULL | 5060 | NULL | NULL | NULL | yes |
| 13 | 192.168.1.1 | 192.168.1.1 | fromhost | port | NULL | 5060 | NULL | NULL | NULL | yes |
| 12 | 41215500004 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 1424dbdb67f690a7d2fd4d8349c97c5b | yes |
| 11 | 41215500003 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 2b04ad62e33f5f12c3fb8279e92f7873 | yes |
| 9 | 41215500001 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 6f223e3916c49c3abda5d2297e6106ec | yes |
| 10 | 41215500002 | dynamic | forbidden | no | NULL | NULL | NULL | NULL | 7e4feda6440c3d42fce6b3b902d51b43 | yes |
| 15 | 192.168.2.1 | 192.168.2.1 | fromnode | port | NULL | 5060 | NULL | NULL | NULL | yes |
| 16 | 192.168.3.1 | 192.168.3.1 | forbidden | no | NULL | 5060 | NULL | NULL | NULL | yes |
+----+-------------+-------------+-----------+----------+--------+------+------------+----------+----------------------------------+------+
8 rows in set (0.00 sec)
Avec ces requêtes on a créé:
4 comptes SIP
2 clients IP
1 Node
2 connexions
192.168.1.2 est au même temps un client IP et un vendeur (il a le droit de nous envoyer des appels entrants)
192.168.3.1 est uniquement un vendeur, contrairement au vendeur 192.168.1.2 il n’a pas le droit de nous envoyer de trafic.
192.168.2.1 est une node donc elle sera considérer comme une connexion et un client IP et.
192.168.1.1 est un client IP (authentification des ses appels est basée sur l’adresse IP)
4121550000[1-4] sont des comptes SIP (authentification est basée sur le nom de comptes et le mot de passe)
http://switzernet.com/3/public/110321-db3-tests/
http://switzernet.com/3/public/110317-db3-versions/
http://switzernet.com/3/public/110317-db3-versions/
http://switzernet.com/3/public/110316-astrad-triggers/
http://switzernet.com/3/public/110314-astrad-triggers/
http://switzernet.com/3/public/110302-test-asterisk-mysql/
http://switzernet.com/3/public/110315-astrad-triggers-tests/
http://switzernet.com/3/public/110316-astrad-call-tests/