BACKUP ALL LIST/LEADS ONLY

[Leckbush]

Hey... Is there any command or instruction to back up all leads? Im going to do a fresh installation and want to install the previous system leads manually. I dont want any restore process to avoid any issues. And also how good is the Vicibox 8 now? I see that there's too many issue to it?




PS: Can I install netdata for monitoring my vicibox? If I can. How to?

[esampark]

just backup the two taables vicidial_list , and vicidial_lists , using the command mysqldump

you can load it up in your new system using something along the lines of "mysql -u root -p<mysqlpassword> asterisk < your-backup-file.sql"

not ssure about vicidial 8, i am trying it now too.

also no idea on netdata.

[williamconley]

Vicibox 8.1.2 is very stable. Note that it installs a later version of OpenSuSE AND asterisk 13 (instead of asterisk 11, which was Vicibox 8.0) compared to your present install.

Modify your sqldump to include field names and to omit the "drop/create" table commands. This way the table structure from the newer system won't hammer the old one if there's a problem of some sort (which can cause total failure if they don't match due to missing fields)

It's usually a good idea to back up the database the previous system, upgrade the DB to match the "about to get installed" vicidial system, then run the dump commands so the structures *should* match. Then you can run the restore on the original system to reset it back to the original DB so it can be run in case you have problems with the new one. Least Likelihood of Conflict.

But you can probably just dump and reload without issues IF you avoid the drop/create table functions.

[Leckbush]

I didn't quite get you there about omit the drop/create? I got the vicidial_list and vicidial_lists on backup now on our NAS. Should I proceed fresh install?

[esampark]

he means to use the command with this option

Code: Select all

mysqldump --no-create-info

this wont create the drop table if exists sql statement in your output file , meaning your new table structure wont be alteredd , if it is changed from the older version , its the safest method.

if you have already created the sql file, you can use any text editor to edit it [given its not too large] and just delete the sql statements regarding the drop and create of tables , and just keep the insert queries.

[williamconley]

he means to use the command with this option

Code: Select all

mysqldump --no-create-info

this wont create the drop table if exists sql statement in your output file , meaning your new table structure wont be alteredd , if it is changed from the older version , its the safest method.

if you have already created the sql file, you can use any text editor to edit it [given its not too large] and just delete the sql statements regarding the drop and create of tables , and just keep the insert queries.

i am truly honored, sir. i think you are my first explainer. well done.

[esampark]

thanks @ williamconley , i just try to help , where i can, i dont know much , but i can offeer my assistance in few areas where i know.

[Leckbush]

Thanks guys. I have now the sql file that I backup using mysqldump but I didnt use william "hammer thing" and the new vicibox 8 is up and runnning. So how do I restore this sql?

[williamconley]

1) Back up the new server's sql data before you begin. Full asterisk database.

Code: Select all

mysqldump asterisk > /root/full_asterisk_database.sql

(May require -pPaSwWorD if root has a password)

2) Restore from any .sql file with this:

Code: Select all

mysql asterisk < /root/filename.sql

(May require -pPaSwWorD if root has a password)

[Leckbush]

Did the restore from list and lists. But receiving this email from cron:


DBD::mysql::st execute failed: Unknown column 'daily_reset_limit' in 'field list' at /usr/share/astguiclient/ADMIN_keepalive_ALL.pl line 4393.
executing: SELECT list_id,daily_reset_limit,resets_today FROM vicidial_lists where reset_time LIKE "%1420%"; Unknown column 'daily_reset_limit' in 'field list' at /usr/share/astguiclient/ADMIN_keepalive_ALL.pl line 4393.


I check the sql file "Lists" which I backup and and didn't find the "daily_reset_limit' in the "lists".sql that I back-up. So I think I need to add a line for the 'daily_reset_limit' on the "lists".sql backup

Here's the "lists".sql backup:

DROP TABLE IF EXISTS `vicidial_lists`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vicidial_lists` (
`list_id` bigint(14) unsigned NOT NULL,
`list_name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`campaign_id` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`active` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT NULL,
`list_description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`list_changedate` datetime DEFAULT NULL,
`list_lastcalldate` datetime DEFAULT NULL,
`reset_time` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',
`agent_script_override` varchar(10) COLLATE utf8_unicode_ci DEFAULT '',
`campaign_cid_override` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`am_message_exten_override` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',
`drop_inbound_group_override` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`xferconf_a_number` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',
`xferconf_b_number` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',
`xferconf_c_number` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',
`xferconf_d_number` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',
`xferconf_e_number` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',
`web_form_address` text COLLATE utf8_unicode_ci,
`web_form_address_two` text COLLATE utf8_unicode_ci,
`time_zone_setting` enum('COUNTRY_AND_AREA_CODE','POSTAL_CODE','NANPA_PREFIX','OWNER_TIME_ZONE_CODE') COLLATE utf8_unicode_ci DEFAULT 'COUNTRY_AND_AREA_CODE',
`inventory_report` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'Y',
`expiration_date` date DEFAULT '2099-12-31',
`na_call_url` text COLLATE utf8_unicode_ci,
`local_call_time` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'campaign',
`web_form_address_three` text COLLATE utf8_unicode_ci,
`status_group_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
PRIMARY KEY (`list_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

'daily_reset_limit' is missing from the backup sql? Idk.

[williamconley]

which means you altered the structure of the vicidial_list table. your mysqldump had the drop/create functions in it. start over and do it again, this time use the appropraite mysql command to omit those features.

[Leckbush]

But I dont have the old server. Its been reformatted already? Is there any way I can restore this leads? Im so sorry

[williamconley]

1) Back up the new server's sql data before you begin. Full asterisk database.

Code: Select all

mysqldump asterisk > /root/full_asterisk_database.sql

(May require -pPaSwWorD if root has a password)

2) Restore from any .sql file with this:

Code: Select all

mysql asterisk < /root/filename.sql

(May require -pPaSwWorD if root has a password)

remember this "backup before you begin" step (#1 above)? did you ... skip it?

[esampark]

But I dont have the old server. Its been reformatted already? Is there any way I can restore this leads? Im so sorry

what do you mean when you say you dont have old server , its just easier if you specify clearly what all do you have right now ?

also , yes very important , do you have the backup of database from your new vicidial 8 setup , as per williamconley said ??

[Leckbush]

I mean is I dont have the server of the sql that I back up. But I do have back up of the new server full database and revert it back and everything on db is working fine until I can restore this sql from old server w/o any problem.


Sorry for my grammar

[Leckbush]

@william. Yes I revert it back to the original state before I restore the sql from the old server.

[esampark]

Restore from any .sql file with this:

Code: Select all

mysql asterisk < /root/filename.sql

but wait , thats not it , the sql file you have , from the old server , it must have create-drop table statements in it , thats why your new table structure got changed.

now you have two options

option 1 ) use any text editor to open your .sql file from old server , look for create-drop table queries , and delete these queries , remember to delete everyone of them, then save your sql file , then transfer it to the new server , then restore from it
but i am assuming your ssql file doesnot have field names in it either , in the insert queries , so that will create a problem , if the table structure has changed, so you should go for option 2

option 2 ) create a new database asterisk_temp , restore your old server sql file in to this database , with command

Code: Select all

mysql asterisk_temp < /root/filename.sql

after the database has been populated , we will now take the backup in a desired format , use the following command to take a backup of newly created database:

Code: Select all

mysqldump --no-create-info --complete-insert --no-create-db asterisk_temp vicidial_list > vicidial_list.sql
mysqldump --no-create-info --complete-insert --no-create-db asterisk_temp vicidial_lists > vicidial_lists.sql

after this , transfer these two files , to the root folder of your new vicidial 8 server , and restore using following commands :

Code: Select all

mysql asterisk < vicidial_list.sql
mysql asterisk < vicidial_lists.sql


and that should do it.

after you have verified everything is working fine , you can remove the temporarily created database , or might even just keep it , your wish !

[Leckbush]

after this , transfer these two files , to the root folder of your new vicidial 8 server , and restore using following commands :

Code: Select all

mysql asterisk < vicidial_list.sql
mysql asterisk < vicidial_lists.sql


and that should do it.

after you have verified everything is working fine , you can remove the temporarily created database , or might even just keep it , your wish !

Im on this part now but I soon I execute this one

Code: Select all

mysql asterisk < vicidial_lists.sql


I recieve an error :

ERROR 1062 (23000) at line 23: Duplicate entry '999' for key 'PRIMARY'

[esampark]

those are the default lists that are already there in the vicidial , like the default manual list , default in group list etc etc

just use this to delete these lists from the table on new sserver , and then they can be written again by your sql query :

Code: Select all

delete from vicidial_lists where list_id in ('998','999');

please note that you have to execute aabove query in the mysql client , which can be run by

Code: Select all

mysql asterisk

[Leckbush]

Okay executed successfully.

I check the webpage and everything is there from the old server. No issue from cron. HAHAHA

Thank you very much sir! Couldnt done this without you and the community.


Pardon my knowledge sir

[carpenox]

good shit this just helped me bigtime for a client heh