Difference between revisions of "Insert Calls from Backup server to Main server"

From Kolmisoft Wiki
Jump to navigationJump to search
Line 16: Line 16:
  mysqldump -u mor -pmor --single-transaction mor temp_calls --complete-insert --skip-triggers --compact -t >/home/2018_CALLS_ONLY.sql
  mysqldump -u mor -pmor --single-transaction mor temp_calls --complete-insert --skip-triggers --compact -t >/home/2018_CALLS_ONLY.sql
6. Now created file must be configured. Open file with text editor.  
6. Now created file must be configured. Open file with text editor.  
6.1 Replace '''temp_calls''' with '''calls'''. It  can be done using vi editor. Open file with vi:
6.1 Replace '''temp_calls''' with '''calls'''. It  can be done using vi editor. Open file with vi:
  vi 2018_CALLS_ONLY>sql
  vi 2018_CALLS_ONLY>sql
Press ESC and enter :%s/temp_calls/calls/g  
Press ESC and enter :%s/temp_calls/calls/g  
Save the file.
Save the file.
6.2 Remove all information instead of '''INSERT INTO `calls` VALUES'''.
6.2 Remove all information instead of '''INSERT INTO `calls` VALUES'''.



Revision as of 09:57, 21 January 2019

Condition

Main condition of this manual is that Backup server contains calls that are missing on Main server.

ON BACKUP SERVER

1. Log in into mysql:

mysql -u mor -pmor

2. Create temp_calls table with same columns as calls table:

create table temp_calls like calls;

3. Insert required calls into temp_calls table. In example below we will copy calls that are Answered and their Call date is between 2018-01-01 and 2018-01-31.

insert into temp_calls select * from calls where disposition = 'ANSWERED' and calldate between "2018-01-01 00:00:00" and "2018-01-31 23:59:59";

4. Main problem of copying calls from one server to another is that calls will have same ID. That is why ID must be removed:

alter table temp_calls drop column id;

5. Make dump of temp_calls table.

mysqldump -u mor -pmor --single-transaction mor temp_calls --complete-insert --skip-triggers --compact -t >/home/2018_CALLS_ONLY.sql

6. Now created file must be configured. Open file with text editor.

6.1 Replace temp_calls with calls. It can be done using vi editor. Open file with vi:

vi 2018_CALLS_ONLY>sql

Press ESC and enter :%s/temp_calls/calls/g Save the file.

6.2 Remove all information instead of INSERT INTO `calls` VALUES.

6.3 In result, your file will look like in example below. Please note that in your case file will be much more longer as example contains only 1 call record.

INSERT INTO `calls` VALUES ('2019-01-15 02:01:06','\"91525\" 
<31555555555>','31555555555','08855555555',,'SIP/91525-00007070',,,,11,8,'ANSWERED',0,'2199','15475555555.33543',,2199,0,0,0,0,7,0.006,8,0.0008,1266,0.0184,8,
0.032453,341,0.0153,8,0.01204,0,0,0,0,'3188',1,16,'Outside',NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,
'31555555555',0,0,'111.222.333.444','123.123.123.123',10.293,7.931,8,0)

7. Transfer file to Main server.

ON MAIN SERVER

1. Import your calls from Backup. As there are no ID field, mysql will create new ID for each call.

mysql </path/to/2018_CALLS_ONLY.sql

2. Now calls must be re-synced. More information here