Move the XCALLY database to a different server

Owned by Valerio Ciotta
Last updated: Jun 22, 2022 by XCALLY Knowledge (Unlicensed)

 Use external realtime DB on different server




Scaling Architecture


Configure the Asterisk Realtime Architecture

 

Important

After the configuration changes, please reboot the machine.

Scalable architecture

For large scalable deployments on one hand, and to be able to interact with the XCALLY SHUTTLE DB from external application on the other hand, without affecting the operation, we suggest at least 3 Linux CentOS6.X servers, like in the following example.

Please contact our XCALLY Team for help and more details about this scalable deployment solution.



GUI

 
You have to edit:


- the file called database.php, under the directory /var/www/html/application/config, as follows:

 
$db['default']['hostname'] = 'DESTINATION_HOSTNAME_MYSQL';
$db['default']['username'] = 'DESTINATION_USERNAME_MYSQL';
$db['default']['password'] = 'DESTINATION_PASSWORD_MYSQL';
$db['default']['database'] = 'DESTINATION_DATABASE_MYSQL';


- the file called mysql.json, under the directory /var/www/html, as follows:


{
"host": "DESTINATION_HOSTNAME_MYSQL",
"port": "3306",
"user": "DESTINATION_USERNAME_MYSQL",
"password": "DESTINATION_PASSWORD_MYSQL",
"database": "DESTINATION_DATABASE_MYSQL"
}


- In Settings / General set the parameter "Direct database extract" to NO (in order to extract the CSV files in the correct location):

ASTERISK CDR

 
You have to edit the file called cdr_mysql.conf, under the directory /etc/asterisk/, as follows:
 
hostname=DESTINATION_HOSTNAME_MYSQL
 
dbname=DESTINATION_DATABASE_MYSQL
 
password=DESTINATION_PASSWORD_MYSQL 
 
user=DESTINATION_USERNAME_MYSQL
 

ASTERISK REALTIME

 
You have to edit:

- the file called res_config_mysql.conf under the directory /etc/asterisk/

 
dbhost = DESTINATION_HOSTNAME_MYSQL

dbname = DESTINATION_DATABASE_MYSQL
 
dbuser = DESTINATION_USERNAME_MYSQL
 
dbpass = DESTINATION_PASSWORD_MYSQL


ASTERISK ODBC

You have to edit:

- the file called res_odbc.conf under the directory /etc/asterisk/ (only if you have changed the default user credentials)

; Username for connecting to the database.  The user defaults to the context
; name if unspecified.
username => DESTINATION_USERNAME_MYSQL
;
; Password for authenticating the user to the database.  The default
; password is blank.
password => DESTINATION_PASSWORD_MYSQL
;


- the file called odbc.ini under the directory /etc/odbc.ini

Server = DESTINATION_HOSTNAME_MYSQL 
User = DESTINATION_USERNAME_MYSQL    
Pass = DESTINATION_PASSWORD_MYSQL   
Database = DESTINATION_DATABASE_MYSQL
 
 

PHONEBAR


You have to edit:

- the file called res_top.conf under the directory /etc/asterisk
 


hostname = DESTINATION_HOSTNAME_MYSQL
 
schema = DESTINATION_DATABASE_MYSQL
 
user = DESTINATION_USERNAME_MYSQL 
 
secret =DESTINATION_PASSWORD_MYSQL

- the file called res_xcalld.conf under the directory /etc/asterisk 
 


[mysql]

hostname = DESTINATION_HOSTNAME_MYSQL
 
schema = DESTINATION_DATABASE_MYSQL
 
user = DESTINATION_USERNAME_MYSQL 
 
secret =DESTINATION_PASSWORD_MYSQL
  
 

AGISQUARE


You have to edit the file called agisquare.conf under the directory /var/www/html/agisquare/etc
 


[database]

host = DESTINATION_HOSTNAME_MYSQL
 
database = DESTINATION_DATABASE_MYSQL
 
user = DESTINATION_USERNAME_MYSQL 
 
password =DESTINATION_PASSWORD_MYSQL


Move the database



Important

The firewall, in the destination server, must permit the connection on the MySQL port (default 3306)
  • Create, on the destination server (DESTINATION_HOSTNAME_MYSQL), the xcally database and the user DESTINATION_USERNAME_MYSQL/DESTINATION_PASSWORD_MYSQL
  • Connect to MySQL and launch the following queries:
CREATE DATABASE IF NOT EXISTS DESTINATION_DATABASE_MYSQL;
GRANT ALL PRIVILEGES ON DESTINATION_DATABASE_MYSQL.* TO
'DESTINATION_USERNAME_MYSQL'@'localhost' IDENTIFIED BY 'DESTINATION_PASSWORD_MYSQL';
GRANT ALL PRIVILEGES ON DESTINATION_DATABASE_MYSQL.* TO 'DESTINATION_USERNAME_MYSQL'@'%' IDENTIFIED BY 'DESTINATION_PASSWORD_MYSQL';
GRANT SUPER ON *.* TO 'DESTINATION_USERNAME_MYSQL'@'localhost';
GRANT FILE ON *.* TO 'DESTINATION_USERNAME_MYSQL'@'localhost' identified by 'DESTINATION_PASSWORD_MYSQL'; GRANT FILE ON *.* TO 'DESTINATION_USERNAME_MYSQL'@'%' identified by 'DESTINATION_PASSWORD_MYSQL';
FLUSH PRIVILEGES;
  • Go, on the original server, under /usr/src
cd /usr/src
  • Launch the following commands:


mysqldump -u root -ppwd --routines xcally > xcally-dump.sql
mysql -u DESTINATION_USERNAME_MYSQL -pDESTINATION_PASSWORD_MYSQL -h DESTINATION_HOSTNAME_MYSQL DESTINATION_DATABASE_MYSQL < xcally-dump.sql
 
In case your database username is different from the default one 'xcall', insert these commands instead of the last ones:
 


mysqldump -u root -ppwd --routines xcally | sed -e 's/DEFINER=`xcall`@`%`/DEFINER=`DESTINATION_USERNAME_MYSQL`@`%`/' > xcally-dump.sql> xcally-dump.sql
mysql -u DESTINATION_USERNAME_MYSQL -pDESTINATION_PASSWORD_MYSQL -h DESTINATION_HOSTNAME_MYSQL DESTINATION_DATABASE_MYSQL < xcally-dump.sql