Difference between revisions of "Insert Calls from Backup server to Main server"
Line 3: | Line 3: | ||
=ON BACKUP 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; | 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"; | 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; | 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 | 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 | 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.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\" | 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, | <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, | ||
Line 25: | Line 26: | ||
'31555555555',0,0,'111.222.333.444','123.123.123.123',10.293,7.931,8,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= | =ON MAIN SERVER= |
Revision as of 09:51, 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