Журнал лабораторных работ

Содержание

Журнал

Пятница (11/28/14)

/dev/pts/11
13:37:43
#ls /var/log/asterisk/cdr-csv/

13:43:59
#apt-cache search asterisk mysql
asterisk-mysql - MySQL database protocol support for the Asterisk PBX
13:44:07
## коннектор asterisk для mysql

13:44:13
## к примеру mysql

13:44:49
## давайте делать прараллельно

13:45:15
## ставим mysql-server

13:45:21
#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) ...
13:47:42
#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) ...
13:48:57
#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) ...
13:49:25
#ls -l /tmp/1.sql
-rw-r--r-- 1 user user 910 Nov 28 07:54 /tmp/1.sql
13:54:33
#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 );
13:54:42
#mysql -u root -p <
l3script: syntax error near unexpected token `newline'
13:54:59
#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
13:55:06
#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 );
13:56:43
#mysql -u root -p < /tmp/1.sql
Enter password:
ERROR 1007 (HY000) at line 2: Can't create database 'asterisk'; database exists
13:56:57
#mysql -u root -p < /tmp/1.sql
Enter password:
13:57:01
#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
14:01:10
#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
14:01:15
#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 );
14:02:50
#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
14:04:58
#mysql -u -p
ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)
14:05:05
#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
14:05:15
#ls -l /etc/asterisk/cdr_mysql.conf
-rw-r----- 1 asterisk asterisk 2231 Jan  3  2014 /etc/asterisk/cdr_mysql.conf
14:05:55
## вот здесь вы указываете астериску куда ему коннектится

14:06:10
#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,
14:07:10
#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
14:07:15
##

14:07:22
#вÐâ„# ‚ этот /var/run/mysqld/mysqld.sock

14:08:36
#TZ=EET date
Fri Nov 28 15:08:41 EET 2014
14:08:41
## правильно

14:09:04
#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
14:09:23
## всё

14:09:27
#/etc/init.d/asterisk restart
Stopping Asterisk PBX: asterisk.
Starting Asterisk PBX: asterisk.
14:12:24
## воÑ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
14:13:13
#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)
14:13:35
#man mysql
14:14:01
## на машине 8 всё правильно сделано

14:14:22
## на машинах 2,3,4,5,6 неправильно установлен пароль

14:20:11
#â„# на машинах 2 3 6 по прежнему ошибки

прошло 13 минут
14:33:45
#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
14:33:47
#_ last
настройка карточки PRI                             1:OK 2:OK 3:OK 4:OK 5:OK 6:OK 7:OK 8:OK
14:33:55
#_ подключениÐе к mysql

14:34:02
#_ +7 +8
+7 +8
14:34:06
#_ подклюlast | ff
l3script: ff: command not found
14:34:09
#ff() { grep ^"[$(_ last | tr ' ' '\n' | grep :- | cut -c1-2 |tr -d '\n:' ; echo)]": ; }

14:34:23
#_ last | ff

14:38:50
#_ +4 +5
+4 +5
14:39:00
#all "echo 'select * from cdr;' | mysql -u asterisk -ppassword asterisk " | ff
3: ERROR 1045 (28000): Access denied for user 'asterisk'@'localhost' (using password: YES)
14:39:05
#vim /etc/asterisk/cdr_mysql.conf
14:39:37
#;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
14:40:40
#ssh m1
l3-agent is already running: pid=3555; pidfile=/root/.lilalo/l3-agent.pid
14:40:43
#less /var/log/asterisk/messages
14:41:10
#less /var/log/asterisk/messages
14:41:26
#[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>
14:41:49
#/etc/init.d/asterisk restart
Stopping Asterisk PBX: asterisk.
Starting Asterisk PBX: asterisk.
14:42:35
#vim /etc/asterisk/cdr_mysql.conf
14:43:13
#[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.)
14:43:31