Restoring DataBase

[richardroi]

Support,

Is it possible to restore database from all in one server to a cluster setup?

Any guidelines?

Thank you.

[covarrubiasgg]

This is basiclly the method ...

first use mysqldump to capture your present db (just in case).

Code: Select all

mysqldump asterisk > 2012_08_06_XXXXXXXXXXX.sql

If using a password, you'll need to add "-p" and enter the pass at the prompt.

then drop the existing database and recreate it

Code: Select all

mysql -e "drop database asterisk"
mysql -e "create database asterisk"


then apply the mysql backup to it.

Code: Select all

mysql asterisk < Prior_backup_name.sql

It may be a good idea to reboot. It's definitely a good idea to tell the server to rebuild the conf files under Admin->Servers->(select your server)

i usually use /usr/share/astguiclient/ADMIN_backup.pl --db-only instead of mysqldump

Code: Select all

vicidial:~ # /usr/share/astguiclient/ADMIN_backup.pl --help
allowed run time options:
  [--db-only] = only backup the database
  [--db-without-logs] = do not backup the log tables in the database
  [--conf-only] = only backup the asterisk conf files
  [--without-db] = do not backup the database
  [--without-conf] = do not backup the conf files
  [--without-web] = do not backup web files
  [--without-sounds] = do not backup asterisk sounds
  [--without-voicemail] = do not backup asterisk voicemail
  [--without-crontab] = do not backup crontab
  [--ftp-transfer] = Transfer backup to FTP server
  [--debugX] = super debug
  [--debug] = debug
  [--test] = test

Just take in mind that it will have the setup for the all in one, so after you have install the backup, set Active Asterisk Server to N, in order to let vicidial that is no longer a dialer server.

This method comes with no warranty from me

Do it under your own risk AND ALWAYS BACKUP ALL

[richardroi]

Thank you. One more thing, after restoring the SVN version revert back to 2016 on REPORTS then SERVERS (time not sync), but when I check on system settings it is 2153.
Then I add my asterisk server the SVN version is 2153 also, it does not much on REPORTS, database SVN version is 2016 (from restored backup) and the asterisk SVN version is 2153, how can I correct this?

[richardroi]

How do I upgrade SVN VERSION from 2016 to 2153 on running server?

[covarrubiasgg]

How do I upgrade SVN VERSION from 2016 to 2153 on running server?

cd /usr/src/astguiclient/trunk
svn update // this will take you to the last revision available
svn update -r 2153 // this will take you to 2153 specifiably

after that READ THE UPGRADE DOCUMENT !!!!

again, READ THE UPGRADE DOCUMENT.

Please note this part:

NOTE: Upgrading from 2.7 to 2.9 is below the first section
NOTE: Upgrading from 2.4 to 2.7 is below the first section
NOTE: Upgrading from 2.2.1 to 2.4 is below the first section
NOTE: Upgrading from 2.0.5 to 2.2.0 is below the second section
NOTE: Upgrading from 2.0.4 to 2.0.5 is below the third section
NOTE: Upgrading from 2.0.3 to 2.0.4 is below the fourth section
NOTE: Upgrading from 2.0.2 to 2.0.3 is below the fifth section
NOTE: Upgrading from 2.0.1 to 2.0.2 is in the next section
NOTE: Upgrading from 1.1.12-3 to 2.0.1 is at the bottom

First check your Vicidial Version and DB Schema before proceed.

[richardroi]

Thank you so much. When I looked at cd /usr/src/astguiclient/trunk I checked the version file it says " version: 2.8b0.5" is this my vicidial version?
" this is what i have on my upgarde document?
NOTE: Upgrading from 2.4 to 2.7 is below the first section
NOTE: Upgrading from 2.2.1 to 2.4 is below the first section
NOTE: Upgrading from 2.0.5 to 2.2.0 is below the second section
NOTE: Upgrading from 2.0.4 to 2.0.5 is below the third section
NOTE: Upgrading from 2.0.3 to 2.0.4 is below the fourth section
NOTE: Upgrading from 2.0.2 to 2.0.3 is below the fifth section
NOTE: Upgrading from 2.0.1 to 2.0.2 is in the next section
NOTE: Upgrading from 1.1.12-3 to 2.0.1 is at the bottom

Which one should I follow?

This is what I have on my system settings:
Version: 2.8b0.5
SVN Version: 2016
DB Schema Version: 1355

Need guidance!
Thank you

[covarrubiasgg]

You should read carefully the UPGRADE doccument.

########## UPGRADING FROM 2.9 TO 2.10 ##########

OPTIONAL STEPS(But highly recommended) - Backup existing system:

1. Run this for a 1-server system or server with database on it:
(this may take hours on large system)
/usr/share/astguiclient/ADMIN_backup.pl --debugX

2. Run this on dialer/Asterisk-only servers:
(do not run this if you only have one server):
/usr/share/astguiclient/ADMIN_backup.pl --debugX --without-db --without-web


REQUIRED STEPS!!!

1. Check system_settings, make sure you are at DB Schema Version 1378 or higher
If not, run the instructions for 2.7 to 2.9 before this section.

########## UPGRADING FROM 2.7 TO 2.8 (2.9 release branch) ##########

OPTIONAL STEPS(But highly recommended) - Backup existing system:

1. Run this for a 1-server system or server with database on it:
(this may take hours on large system)
/usr/share/astguiclient/ADMIN_backup.pl --debugX

2. Run this on dialer/Asterisk-only servers:
(do not run this if you only have one server):
/usr/share/astguiclient/ADMIN_backup.pl --debugX --without-db --without-web


REQUIRED STEPS!!!

1. Check system_settings, make sure you are at DB Schema Version 1347 or higher
If not, run the instructions for 2.4 to 2.7 before this section.

You can find you DB Schme under Admin -> System Settings

[richardroi]

########## UPGRADING FROM 2.7 TO 2.8 ##########

OPTIONAL STEPS(But highly recommended) - Backup existing system:

1. Run this for a 1-server system or server with database on it:
(this may take hours on large system)
/usr/share/astguiclient/ADMIN_backup.pl --debugX

2. Run this on dialer/Asterisk-only servers:
(do not run this if you only have one server):
/usr/share/astguiclient/ADMIN_backup.pl --debugX --without-db --without-web


REQUIRED STEPS!!!

1. Check system_settings, make sure you are at DB Schema Version 1347 or higher
If not, run the instructions for 2.4 to 2.7 before this section.

2. upgrade the MySQL asterisk database(you have two options):
A. Running the upgrade file directly from Linux:
mysql -f --database=asterisk < /path/from/root/extras/upgrade_2.8.sql

B. Going into mysql and executing the upgrade sql file:
mysql
use asterisk
\. /path/from/root/extras/upgrade_2.8.sql
quit

3. install new files:
perl ./install.pl
NOTES: If you have customized any scripts in the bin or agi folders,
then make sure you back them up before running the install.pl script.
This script will replace existing files in the astguiclient installation.

4. For each of your ViciDial servers, go the Admin -> Servers -> Modify Server
page and set each one to "Rebuild conf files = Y" and click submit.
This will rebuild the conf files to ensure any changes are updated.

5. On one server only, update your phone codes data:
/usr/share/astguiclient/ADMIN_area_code_populate.pl --purge-table --debug


I followed these steps, when i checked system settings there is no data there.

I encountered these errors on ssh:

MariaDB [(none)]> use asterisk
Database changed
MariaDB [asterisk]> \. /usr/src/astguiclient/trunk/extras/upgrade_2.8.sql
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1060 (42S21) at line 3 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'ct_holidays'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1060 (42S21) at line 7 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'failed_login_count'
ERROR 1060 (42S21) at line 8 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'last_login_date'
ERROR 1060 (42S21) at line 9 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'last_ip'
ERROR 1060 (42S21) at line 10 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'pass_hash'
ERROR 1060 (42S21) at line 12 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'pass_hash_enabled'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1060 (42S21) at line 16 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'phone_login'
ERROR 1060 (42S21) at line 17 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'server_phone'
ERROR 1060 (42S21) at line 18 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'phone_ip'
ERROR 1060 (42S21) at line 20 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'pass_key'
ERROR 1060 (42S21) at line 21 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'pass_cost'
ERROR 1061 (42000) at line 23 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate key name 'phone_ip'
Query OK, 37276 rows affected (0.85 sec)
Records: 37276 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1060 (42S21) at line 28 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'disable_auto_dial'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1050 (42S01) at line 32 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Table 'vicidial_monitor_calls' already exists
ERROR 1050 (42S01) at line 50 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Table 'vicidial_monitor_log' already exists
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1060 (42S21) at line 66 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate column name 'template_statuses'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

ERROR 1050 (42S01) at line 70 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Table 'nanpa_prefix_exchanges_master' already exists
ERROR 1050 (42S01) at line 82 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Table 'nanpa_prefix_exchanges_fast' already exists
ERROR 1061 (42000) at line 87 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Duplicate key name 'nanpaacprefix'
ERROR 1050 (42S01) at line 89 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Table 'nanpa_wired_to_wireless' already exists
ERROR 1050 (42S01) at line 93 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Table 'nanpa_wireless_to_wired' already exists
ERROR 1050 (42S01) at line 97 in file: '/usr/src/astguiclient/trunk/extras/upgrade_2.8.sql': Table 'vicidial_nanpa_filter_log' already exists

[covarrubiasgg]

The ERROR Messages about the duplicates are O.K., what it is not right is the lack of information under system settings.

At this point, you should revert the changes by restoring the database backup and revert the SVN revision and try again,

[richardroi]

This is what I have on my system settings before I did the upgrade!
Version: 2.8b0.5
SVN Version: 2016
DB Schema Version: 1355

Question: Did I use the right one "########## UPGRADING FROM 2.7 TO 2.8 ##########" for my system?
Will try again.
Thank you for giving your time tom answer.

[covarrubiasgg]

1355 > 1347 So, Yest that should be correct.

After that you may run the section UPGRADING FROM 2.9 TO 2.10

[richardroi]

tried it twice still no go!
Here is my step by step procedure!
1.mysql
2.use asterisk
3.\. /usr/src/astguiclient/trunk/extras/upgrade_2.8.sql
4.quit
5.perl ./install.pl (I choose N for Manual configuration)
6./usr/share/astguiclient/ADMIN_area_code_populate.pl --purge-table --debug

Did I miss something?

[richardroi]

seems i have it working!
after the steps!
1.mysql
2.use asterisk
3.\. /usr/src/astguiclient/trunk/extras/upgrade_2.8.sql
4.quit
5.cd /usr/src/astguiclient/trunk
6.perl ./install.pl (I choose N for Manual configuration)
7."Rebuild conf files = Y" and click submit
8./usr/share/astguiclient/ADMIN_area_code_populate.pl --purge-table --debug

I did the steps again replacing step number 3 with this one " \. /usr/src/astguiclient/trunk/extras/upgrade_2.10.sql"

1.mysql
2.use asterisk
3.\. /usr/src/astguiclient/trunk/extras/upgrade_upgrade_2.10.sql
4.quit
5.cd /usr/src/astguiclient/trunk
6.perl ./install.pl (I choose N for Manual configuration)
7."Rebuild conf files = Y" and click submit
8./usr/share/astguiclient/ADMIN_area_code_populate.pl --purge-table --debug

Whola! I have informations again!
Will be testing some calls!
Kindly confirmed if I did it Right???
Thank you so much!
One more thing, I have to do this on all of my " asterisk servers"?

[richardroi]

Will this work?
My setup is one all in one server then I add two asterisk box.

The two asterisk servers are on DB SCHEMA 2016!

[covarrubiasgg]

I did the steps again replacing step number 3 with this one " \. /usr/src/astguiclient/trunk/extras/upgrade_2.10.sql"

1.mysql
2.use asterisk
3.\. /usr/src/astguiclient/trunk/extras/upgrade_upgrade_2.10.sql
4.quit
5.cd /usr/src/astguiclient/trunk
6.perl ./install.pl (I choose N for Manual configuration)
7."Rebuild conf files = Y" and click submit
8./usr/share/astguiclient/ADMIN_area_code_populate.pl --purge-table --debug

Whola! I have informations again!
Will be testing some calls!
Kindly confirmed if I did it Right???
Thank you so much!
One more thing, I have to do this on all of my " asterisk servers"?

Yes this is right, this is exactly what i as trying to tell when i said to move forward to UPGRADING FROM 2.9 TO 2.10. i am glad that you figured out


You should upgrade your dialers too, it seems like you are almost done