/l3/users/2014-11-nt-voip/nt-voip/root :1 :2 :3 :4 :5 :6 :7 :8 :9 :10 :11 :12 :13 :14 :15 :16 |
|
#apt-cache search asterisk mysql
asterisk-mysql - MySQL database protocol support for the Asterisk PBX |
## коннектор asterisk для mysql
|
## к примеру mysql
|
## давайте делать прараллельно
|
## ставим mysql-server
|
#apt-get install mysql-server
141128 7:46:09 [Note] Plugin 'FEDERATED' is disabled. 141128 7:46:09 InnoDB: The InnoDB memory heap is disabled 141128 7:46:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins 141128 7:46:09 InnoDB: Compressed tables use zlib 1.2.7 141128 7:46:09 InnoDB: Using Linux native AIO 141128 7:46:09 InnoDB: Initializing buffer pool, size = 128.0M 141128 7:46:09 InnoDB: Completed initialization of buffer pool 141128 7:46:09 InnoDB: highest supported file format is Barracuda. 141128 7:46:09 InnoDB: Waiting for the background threads to start 141128 7:46:10 InnoDB: 5.5.38 started; log sequence number 1595675 141128 7:46:10 InnoDB: Starting shutdown... 141128 7:46:11 InnoDB: Shutdown completed; log sequence number 1595675 Starting MySQL database server: mysqld .. Checking for tables which need an upgrade, are corrupt or were not closed cleanly.. Setting up libhtml-template-perl (2.91-1) ... Setting up mysql-server (5.5.38-0+wheezy1) ... |
#apt-get install mysql-client
Reading package lists... Done Building dependency tree Reading state information... Done The following NEW packages will be installed: mysql-client 0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded. Need to get 76.7 kB of archives. After this operation, 124 kB of additional disk space will be used. Get:1 http://127.0.0.1/debian/ wheezy/main mysql-client all 5.5.38-0+wheezy1 [76.7 kB] Fetched 76.7 kB in 0s (560 kB/s) Selecting previously unselected package mysql-client. (Reading database ... 145972 files and directories currently installed.) Unpacking mysql-client (from .../mysql-client_5.5.38-0+wheezy1_all.deb) ... Setting up mysql-client (5.5.38-0+wheezy1) ... |
#apt-get install asterisk-mysql
Reading package lists... Done Building dependency tree Reading state information... Done The following NEW packages will be installed: asterisk-mysql 0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded. Need to get 658 kB of archives. After this operation, 783 kB of additional disk space will be used. Get:1 http://127.0.0.1/debian/ wheezy/main asterisk-mysql amd64 1:1.8.13.1~dfsg1-3+deb7u3 [658 kB] Fetched 658 kB in 0s (17.0 MB/s) Selecting previously unselected package asterisk-mysql. (Reading database ... 145977 files and directories currently installed.) Unpacking asterisk-mysql (from .../asterisk-mysql_1%3a1.8.13.1~dfsg1-3+deb7u3_amd64.deb) ... Setting up asterisk-mysql (1:1.8.13.1~dfsg1-3+deb7u3) ... |
#ls -l /tmp/1.sql
-rw-r--r-- 1 user user 910 Nov 28 07:54 /tmp/1.sql |
#cat /tmp/1.sql
create database asterisk; use asterisk; CREATE TABLE cdr ( calldate datetime NOT NULL default ‘0000-00-00 00:00:00′, clid varchar(80) NOT NULL default ”, src varchar(80) NOT NULL default ”, dst varchar(80) NOT NULL default ”, dcontext varchar(80) NOT NULL default ”, channel varchar(80) NOT NULL default ”, dstchannel varchar(80) NOT NULL default ”, ... billsec int(11) NOT NULL default ‘0′, disposition varchar(45) NOT NULL default ”, amaflags int(11) NOT NULL default ‘0′, accountcode varchar(20) NOT NULL default ”, userfield varchar(255) NOT NULL default ”, uniqueid varchar(32) NOT NULL default ” ); ALTER TABLE cdr ADD INDEX ( calldate ); ALTER TABLE cdr ADD INDEX ( dst ); ALTER TABLE cdr ADD INDEX ( uniqueid ); |
#mysql -u root -p <
![]() l3script: syntax error near unexpected token `newline' |
#mysql -u root -p < /tmp/1.sql
![]() Enter password: ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘0000-00-00 00:00:00′, clid varchar(80) NOT NULL default ”, src varcha' at line 2 |
#vim /tmp/1.sql
--- /tmp/l3-saved-1067.5483.18534 2014-11-28 07:55:20.866968632 -0500 +++ /tmp/1.sql 2014-11-28 07:56:42.998971114 -0500 @@ -4,22 +4,22 @@ use asterisk; CREATE TABLE cdr ( - calldate datetime NOT NULL default ‘0000-00-00 00:00:00′, - clid varchar(80) NOT NULL default ”, - src varchar(80) NOT NULL default ”, - dst varchar(80) NOT NULL default ”, - dcontext varchar(80) NOT NULL default ”, - channel varchar(80) NOT NULL default ”, - dstchannel varchar(80) NOT NULL default ”, - lastapp varchar(80) NOT NULL default ”, - lastdata varchar(80) NOT NULL default ”, - duration int(11) NOT NULL default ‘0′, - billsec int(11) NOT NULL default ‘0′, - disposition varchar(45) NOT NULL default ”, - amaflags int(11) NOT NULL default ‘0′, - accountcode varchar(20) NOT NULL default ”, - userfield varchar(255) NOT NULL default ”, - uniqueid varchar(32) NOT NULL default ” + calldate datetime NOT NULL default '0000-00-00 00:00:00', + clid varchar(80) NOT NULL default '', + src varchar(80) NOT NULL default '', + dst varchar(80) NOT NULL default '', + dcontext varchar(80) NOT NULL default '', + channel varchar(80) NOT NULL default '', + dstchannel varchar(80) NOT NULL default '', + lastapp varchar(80) NOT NULL default '', + lastdata varchar(80) NOT NULL default '', + duration int(11) NOT NULL default '0', + billsec int(11) NOT NULL default '0', + disposition varchar(45) NOT NULL default '', + amaflags int(11) NOT NULL default '0', + accountcode varchar(20) NOT NULL default '', + userfield varchar(255) NOT NULL default '', + uniqueid varchar(32) NOT NULL default '' ); ALTER TABLE cdr ADD INDEX ( calldate ); |
#mysql -u root -p < /tmp/1.sql
Enter password: ERROR 1007 (HY000) at line 2: Can't create database 'asterisk'; database exists |
#mysql -u root -p < /tmp/1.sql
Enter password: |
#cpall /tmp/1.sql /tmp/
1.sql 100% 905 0.9KB/s 00:00 1.sql 100% 905 0.9KB/s 00:00 1.sql 100% 905 0.9KB/s 00:00 1.sql 100% 905 0.9KB/s 00:00 1.sql 100% 905 0.9KB/s 00:00 1.sql 100% 905 0.9KB/s 00:00 1.sql 100% 905 0.9KB/s 00:00 1.sql 100% 905 0.9KB/s 00:00 |
#cpall /tmp/1.sql /tmp/
1.sql 100% 915 0.9KB/s 00:00 1.sql 100% 915 0.9KB/s 00:00 1.sql 100% 915 0.9KB/s 00:00 1.sql 100% 915 0.9KB/s 00:00 1.sql 100% 915 0.9KB/s 00:00 1.sql 100% 915 0.9KB/s 00:00 1.sql 100% 915 0.9KB/s 00:00 1.sql 100% 915 0.9KB/s 00:00 |
#cat /tmp/1.sql
drop database if exists asterisk; create database asterisk; use asterisk; CREATE TABLE cdr ( calldate datetime NOT NULL default '0000-00-00 00:00:00', clid varchar(80) NOT NULL default '', src varchar(80) NOT NULL default '', dst varchar(80) NOT NULL default '', dcontext varchar(80) NOT NULL default '', channel varchar(80) NOT NULL default '', ... billsec int(11) NOT NULL default '0', disposition varchar(45) NOT NULL default '', amaflags int(11) NOT NULL default '0', accountcode varchar(20) NOT NULL default '', userfield varchar(255) NOT NULL default '', uniqueid varchar(32) NOT NULL default '' ); ALTER TABLE cdr ADD INDEX ( calldate ); ALTER TABLE cdr ADD INDEX ( dst ); ALTER TABLE cdr ADD INDEX ( uniqueid ); |
#mysql -u root -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 48 Server version: 5.5.38-0+wheezy1 (Debian) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ... You can turn off this feature to get a quicker startup with -A Database changed mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> on asterisk.* -> to 'asterisk'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> SET PASSWORD FOR 'asterisk'@'localhost' = PASSWORD('password'); Query OK, 0 rows affected (0.00 sec) mysql> #SET PASSWORD FOR 'asterisk'@'localhost' = PASSWORD('password'); mysql> Bye |
#mysql -u -p
![]() ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO) |
#mysql -u asterisk -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 50 Server version: 5.5.38-0+wheezy1 (Debian) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> Bye |
#ls -l /etc/asterisk/cdr_mysql.conf
-rw-r----- 1 asterisk asterisk 2231 Jan 3 2014 /etc/asterisk/cdr_mysql.conf |
## вот здесь вы указываете астериску куда ему коннектится
|
#vim /etc/asterisk/cdr_mysql.conf
--- /tmp/l3-saved-1067.6275.19065 2014-11-28 08:06:20.790988572 -0500 +++ /etc/asterisk/cdr_mysql.conf 2014-11-28 08:07:10.878990086 -0500 @@ -12,12 +12,14 @@ ; ;[global] ;hostname=database.host.name -;dbname=asteriskcdrdb -;table=cdr -;password=password -;user=asteriskcdruser +dbname=asterisk +table=cdr +password=password +user=asterisk ;port=3306 -;sock=/tmp/mysql.sock + +; здесь нужен другой файл +sock=/tmp/mysql.sock ;timezone=UTC ; Previously called usegmtime ; ; If your system's locale differs from mysql database character set, |
#netstat -lnp | grep mysql
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 7490/mysqld unix 2 [ ACC ] STREAM LISTENING 347268 7490/mysqld /var/run/mysqld/mysqld.sock |
##
|
#вÐâ# ÑÑÐ¾Ñ /var/run/mysqld/mysqld.sock
|
#TZ=EET date
Fri Nov 28 15:08:41 EET 2014 |
## правильно
|
#vim /etc/asterisk/cdr.conf
--- /tmp/l3-saved-1067.14898.13939 2014-11-28 08:09:08.694993642 -0500 +++ /etc/asterisk/cdr.conf 2014-11-28 08:09:23.754994099 -0500 @@ -11,7 +11,9 @@ ; Define whether or not to use CDR logging. Setting this to "no" will override ; any loading of backend CDR modules. Default is "yes". -;enable=yes + +; включить CDR на всякий случай +enable=yes ; Define whether or not to log unanswered calls. Setting this to "yes" will ; report every attempt to ring a phone in dialing attempts, when it was not |
## всё
|
#/etc/init.d/asterisk restart
Stopping Asterisk PBX: asterisk. Starting Asterisk PBX: asterisk. |
## воÑmysql -u asterisk -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 52 Server version: 5.5.38-0+wheezy1 (Debian) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use asterisk Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from cdr; Empty set (0.00 sec) mysql> Bye |
#mysql -u asterisk -ppassword asterisk -c 'select * from cdr;'
![]() mysql Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Usage: mysql [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup ... i-am-a-dummy FALSE connect-timeout 0 max-allowed-packet 16777216 net-buffer-length 16384 select-limit 1000 max-join-size 1000000 secure-auth FALSE show-warnings FALSE plugin-dir (No default value) default-auth (No default value) |
#man mysql
|
## на машине 8 всё правильно сделано
|
## на машинах 2,3,4,5,6 неправильно установлен пароль
|
#â# на маÑинаÑ
2 3 6 по пÑÐµÐ¶Ð½ÐµÐ¼Ñ Ð¾Ñибки
|
#all "echo 'select * from cdr;' | mysql -u asterisk -ppassword asterisk "
2: ERROR 1049 (42000): Unknown database 'asterisk' 3: ERROR 1045 (28000): Access denied for user 'asterisk'@'localhost' (using password: YES) 4: calldate clid src dst dcontext channel dstchannel lastapp lastdata duration billsec disposition amaflags accountcode userfield uniqueid 4: 2014-11-28 15:33:31 2101 2101 3101 gr1 SIP/2101-00000001 SIP/london-00000002 Dial SIP/london/3101 6 2 ANSWERED 3 1417181611.1 7: calldate clid src dst dcontext channel dstchannel lastapp lastdata duration billsec disposition amaflags accountcode userfield uniqueid 7: 2014-11-28 15:22:39 3101 3101 s menu SIP/3101-00000000 BackGround /var/tmp/asterisk/record1 6 6 ANSWERED 3 1417180959.0 7: 2014-11-28 15:33:36 "2101" <2101> 2101 3101 gr4 SIP/london-00000001 SIP/3101-00000002 Dial SIP/3101,30,tTkK 6 2 ANSWERED 3 1417181616.1 8: calldate clid src dst dcontext channel dstchannel lastapp lastdata duration billsec disposition amaflags accountcode userfield uniqueid 8: 2014-11-28 15:13:13 3201 3201 2 submenu SIP/3201-00000000 BackGround /var/lib/asterisk/sounds/tts/tts-7726ca6e8145e83a1ac38cadb9e05613 14 14 ANSWERED 3 1417180393.0 8: 2014-11-28 15:14:10 3201 3201 5002 gr1 SIP/3201-00000001 SIP/ny-00000002 Dial SIP/ny/5002 12 3 ANSWERED 3 1417180450.1 8: 2014-11-28 15:17:24 3201 3201 3202 gr1 SIP/3201-00000003 SIP/3202-00000004 Dial SIP/3202,20,tTkK 17 8 ANSWERED 3 1417180644.3 8: 2014-11-28 15:33:36 "2101" <2101> 2101 3101 gr4 SIP/ny-00000005 SIP/liverpool-00000006 Dial SIP/liverpool/3101 6 2 ANSWERED 3 1417181616.5 |
#_ last
настройка карточки PRI 1:OK 2:OK 3:OK 4:OK 5:OK 6:OK 7:OK 8:OK |
#_ подклÑÑениÐе к mysql
|
#_ +7 +8
+7 +8 |
#_ подклюlast | ff
![]() l3script: ff: command not found |
#ff() { grep ^"[$(_ last | tr ' ' '\n' | grep :- | cut -c1-2 |tr -d '\n:' ; echo)]": ; }
|
#_ last | ff
|
#_ +4 +5
+4 +5 |
#all "echo 'select * from cdr;' | mysql -u asterisk -ppassword asterisk " | ff
3: ERROR 1045 (28000): Access denied for user 'asterisk'@'localhost' (using password: YES) |
#vim /etc/asterisk/cdr_mysql.conf
|
#;compat=no
2: [global] 2: 2: hostname=localhost 2: dbname=asterisk 2: table=cdr 2: password=password 2: user=asterisk 2: port=3306 6: [global] 6: hostname=localhost 6: dbname=asterisk 6: table=cdr 6: password=password 6: user=asterisk 6: port=3306 6: sock=/var/run/mysqld/mysql.sock |
#ssh m1
l3-agent is already running: pid=3555; pidfile=/root/.lilalo/l3-agent.pid |
#less /var/log/asterisk/messages
|
#less /var/log/asterisk/messages
|
#[Nov 28 15:40:42] NOTICE[10611] res_smdi.c: No SMDI interfaces are available to listen on, not starting SMDI list
; ; Note - if the database server is hosted on the same machine as the ; asterisk server, you can achieve a local Unix socket connection by ; setting hostname=localhost ; ; port and sock are both optional parameters. If hostname is specified ; and is not "localhost" (you can use address 127.0.0.1 instead), then ; cdr_mysql will attempt to connect to the port specified or use the ; default port. If hostname is not specified or if hostname is ; "localhost", then cdr_mysql will attempt to connect to the socket file ... ;alias dstchannel => <a_field_not_named_dstchannel> ;alias lastapp => <a_field_not_named_lastapp> ;alias lastdata => <a_field_not_named_lastdata> ;alias duration => <a_field_not_named_duration> ;alias billsec => <a_field_not_named_billsec> ;alias disposition => <a_field_not_named_disposition> ;alias amaflags => <a_field_not_named_amaflags> ;alias accountcode => <a_field_not_named_accountcode> ;alias userfield => <a_field_not_named_userfield> ;alias uniqueid => <a_field_not_named_uniqueid> |
#/etc/init.d/asterisk restart
Stopping Asterisk PBX: asterisk. Starting Asterisk PBX: asterisk. |
#vim /etc/asterisk/cdr_mysql.conf
|
#[global]
; is "yes". ;safeshutdown=yes ; ; ; CHOOSING A CDR "BACKEND" (what kind of output to generate) ; ; To choose a backend, you have to make sure either the right category is ; defined in this file, or that the appropriate config file exists, and has the ; proper definitions in it. If there are any problems, usually, the entry will ; silently ignored, and you get no output. ... ; find this information horribly useless. Others find it very valuable. Note, in "yes" ; mode, you will see one CDR, with one of the call targets on one side, and the originating ; channel on the other, and then one CDR for each channel attempted. This may seem ; redundant, but cannot be helped. ; ; In brief, this option controls the reporting of unanswered calls which only have an A ; party. Calls which get offered to an outgoing line, but are unanswered, are still ; logged, and that is the intended behaviour. (It also results in some B side CDRs being ; output, as they have the B side channel as their source channel, and no destination ; channel.) |