Danthony at 18:10, 30 October 2015

2015-10-30T18:10:45Z

Danthony at 18:04, 30 October 2015

2015-10-30T18:04:31Z

← Older revision		Revision as of 18:04, 30 October 2015
Line 39:		Line 39:

	sudo apt-get install htop iotop bwm-ng tshark		sudo apt-get install htop iotop bwm-ng tshark


	= Database install and configuration =		= Database install and configuration =

Danthony at 18:04, 30 October 2015

2015-10-30T18:04:13Z

← Older revision		Revision as of 18:04, 30 October 2015
Line 20:		Line 20:
	*cdrdatabase0 - Server where CDRs will be archived		*cdrdatabase0 - Server where CDRs will be archived
	*cdrdatabase1 - Server where CDRs will be archived		*cdrdatabase1 - Server where CDRs will be archived


	= OS install =		= OS install =
Line 112:		Line 113:

	slave start;		slave start;


	= Install and configure Heartbeat =		= Install and configure Heartbeat =
Line 219:		Line 221:
	You should now be able to test heartbeat by running hb_takeover on each box and having it take over the 10.1.0.93 IP and the drbd0 volume.		You should now be able to test heartbeat by running hb_takeover on each box and having it take over the 10.1.0.93 IP and the drbd0 volume.
	sudo /usr/share/heartbeat/hb_takeover		sudo /usr/share/heartbeat/hb_takeover


	= Configure CDR archiving =		= Configure CDR archiving =
Line 273:		Line 276:

	sudo service apache2 restart		sudo service apache2 restart


	= Configure roles =		= Configure roles =

	In order to allow non System Owner users to search archived CDRs, set the "Call history (archived)" to "Yes" under the appropriate roles. It would be best to set this for only roles where it is absolutely necessary.		In order to allow non System Owner users to search archived CDRs, set the "Call history (archived)" to "Yes" under the appropriate roles. It would be best to set this for only roles where it is absolutely necessary.


	= Configure backups of archived CDRs =		= Configure backups of archived CDRs =
Line 317:		Line 322:

	sudo chmod 700 /etc/cron.d/mysql-backup		sudo chmod 700 /etc/cron.d/mysql-backup


	= References =		= References =

Danthony at 17:00, 30 October 2015

2015-10-30T17:00:02Z

Show changes

Danthony: /* Create tables: */

2015-10-30T16:52:50Z

Create tables:

← Older revision		Revision as of 16:52, 30 October 2015
Line 244:		Line 244:
	=== Create tables: ===		=== Create tables: ===

	Do 'SHOW CREATE TABLE' on ~~enswitchcdb00~~ for each of the 3 cdr* tables and run these on enswitchcdrdb00.		Do 'SHOW CREATE TABLE' on enswitchdb00 for each of the 3 cdr* tables and run these on enswitchcdrdb00.

	Create enswitchcdrsrw user on enswitchcdrdb00"		Create enswitchcdrsrw user on enswitchcdrdb00"

	GRANT ALL ON enswitch.* to enswitchcdrsrw IDENTIFIED BY 'password';		GRANT ALL ON enswitch.* to enswitchcdrsrw IDENTIFIED BY 'password';




	=== Run enswitch_cdrs_archive_remote manually: ===		=== Run enswitch_cdrs_archive_remote manually: ===

Danthony: Created page with "= Disclaimer = The following comes with no warranty whatsoever. I am not responsible for any data loss or other issues that may arise from following these instructions. Pl..."

2015-10-30T16:49:44Z

Created page with "= Disclaimer = The following comes with no warranty whatsoever. I am not responsible for any data loss or other issues that may arise from following these instructions. Pl..."

New page

= Disclaimer =

The following comes with no warranty whatsoever. I am not responsible for any data loss or other issues that may arise from following these instructions. Please make backups of all files and test this thoroughly in your lab environment before using it in production.

= Info =

This document is a companion to the official documentation from Integrics at http://integrics.com/enswitch/guides/3.11/en/field/cdrs/

Some of the commands are borrowed from the above page and from other Integrics documentation.

The Enswitch version used is 3.11, and the servers run Ubuntu 12.04 64bit.

The servers used in this example are as follows:

*enswitchdb00 - Main active database server
*enswitchdb01 - Main standby database server
*enswitchcdrdb00 - Server where CDRs will be archived
*enswitchcdrdb01 - Server where CDRs will be archived

= OS install =

Load servers with the same OS as the current Enswitch database servers, in this example I use Ubuntu 12.04 64bit.

=== Update all OS packages: ===

sudo apt-get update
sudo apt-get dist-upgrade
sudo apt-get autoremove
sudo init 6

Install an appropriate firewall, this is out of the scope of this document, but an example may be added later.

=== Install optional packages: ===

sudo apt-get install htop iotop bwm-ng tshark

= Database install and configuration =

=== Install MySQL on enswitchcdrdb00 and enswitchcdrdb01: ===

sudo apt-get install mysql-server

=== Configure master/master replication between enswitchcdrdb00 and enswitchcdrdb01: ===

Enable remote connections to MySQL on enswitchcdrdb00 and enswitchcdrdb01. In /etc/mysql.my.cnf, change the bind-address variable to 0.0.0.0.

Add the following configuration options to /etc/mysql/my.cnf under [mysqld] to enable binary logging on enswitchcdrdb00 and enswitchcdrdb01:

log_bin = /var/lib/mysql/mysql-bin.log

Add the following configuration options to /etc/mysql/my.cnf under [mysqld] to limit replication to only the enswitch database:

replicate-do-db = enswitch

Add the following configuration options to /etc/mysql/my.cnf under [mysqld] on enswitchcdrdb00:

server-id = 10

Add the following configuration options to /etc/mysql/my.cnf under [mysqld] on enswitchcdrdb01:

server-id = 11

Add the following configuration options to /etc/mysql/my.cnf under [mysqld] to enable a single file per table:

innodb_file_per_table

Restart MySQL:

sudo service mysql restart

Configure replication

Add a replicate user on enswitchcdrdb00:

grant super, replication client, replication slave, reload on *.* to replicate@10.1.0.89 identified by 'PASSWORD';

Add a replicate user on enswitchcdrdb01:

grant super, replication client, replication slave, reload on *.* to replicate@10.1.0.88 identified by 'PASSWORD';

On enswitchcdrdb00, display the current log file and position:

show master status\G

Insert the appropriate values instead of LOGFILE and POSITION below, and then run the command in mysql on enswitchcdrdb01:

change master to master_host='10.1.0.88', master_user='replicate', master_password='PASSWORD', master_log_file='LOGFILE', master_log_pos=POSITION;

Start the slave process on enswitchcdrdb01:

slave start;

On enswitchcdrdb01, display the current log file and position:

show master status\G

Insert the appropriate values instead of LOGFILE and POSITION below, and then run the command in mysql on enswitchcdrdb00:

change master to master_host='10.1.0.89', master_user='replicate', master_password='PASSWORD', master_log_file='LOGFILE', master_log_pos=POSITION;

Start the slave process on enswitchcdrdb00:

slave start;

= Install and configure Heartbeat =

=== Add entries to /etc/hosts for each server on enswitchcdrdb00 and enswitchcdrdb01: ===

10.1.0.88 enswitchcdrdb00
10.1.0.89 enswitchcdrdb01

=== Install heartbeat: ===

sudo apt-get install heartbeat

=== Configure heartbeat: ===

Create /etc/ha.d/haresources:

On enswitchcdrdb00 and enswitchcdrdb01:

enswitchcdrdb00 IPaddr::10.1.0.93/26/eth0

Create /etc/ha.d/ha.cf:

On enswitchcdrdb00:

debug 1
debugfile /var/log/heartbeat_debug.log
logfile /var/log/heartbeat.log
logfacility local0

keepalive 1
deadtime 10
warntime 5
initdead 60

udpport 700
bcast eth0
ucast eth0 10.1.0.89
ping 10.1.0.65

auto_failback off
node enswitchcdrdb00
node enswitchcdrdb01
respawn hacluster /usr/lib/heartbeat/ipfail

On enswitchcdrdb01:

debug 1
debugfile /var/log/heartbeat_debug.log
logfile /var/log/heartbeat.log
logfacility local0

keepalive 1
deadtime 10
warntime 5
initdead 60

udpport 700
bcast eth0
ucast eth0 10.1.0.88
ping 10.1.0.65

auto_failback off
node enswitchcdrdb00
node enswitchcdrdb01
respawn hacluster /usr/lib/heartbeat/ipfail

Create /etc/ha.d/authkeys on enswitchcdrdb00 and enswitchcdrdb01:

auth 2
1 crc
2 sha1 secret
3 md5 dhcp

Change permissions on /etc/ha.d/authkeys

sudo chmod 600 /etc/ha.d/authkeys

Add firewall rule allow heartbeat. This goes in /etc/firewall.sh anywhere after the "iptables -A INPUT -i lo -j ACCEPT" line and before "iptables -A INPUT -j LOG":

On enswitchcdrdb00:

iptables -t filter -A INPUT -p udp --dport 700 -s 10.1.0.89 -m comment --comment "Allow heartbeat from enswitchcdrdb01" -j ACCEPT

On enswitchcdrdb01:

iptables -t filter -A INPUT -p udp --dport 700 -s 10.1.0.88 -m comment --comment "Allow heartbeat from enswitchcdrdb00" -j ACCEPT

Apply new firewall rules:

sudo sh /etc/firewall.sh

=== Start Heartbeat: ===

Start heartbeat on enswitchcdrdb00:
sudo service heartbeat start

Wait until you see the 10.1.0.93 IP address on eth0:0 and drbd0 volume mounted at /mnt/drbd0, then start heartbeat on enswitchcdrdb01:
sudo service heartbeat start

You should now be able to test heartbeat by running hb_takeover on each box and having it take over the 10.1.0.93 IP and the drbd0 volume.
sudo /usr/share/heartbeat/hb_takeover

= Configure CDR archiving =

Make a backup of the current Enswitch database

Optionally, also backup the individual tables in the current database for easy retreival. NOTE, this should only be run on a test system, not on production since it will take a very long time.

CREATE TABLE cdrs_backup1 LIKE cdrs;
INSERT INTO cdrs_backup1 SELECT * FROM cdrs;
CREATE TABLE cdrcosts_backup1 LIKE cdrcosts;
INSERT INTO cdrcosts_backup1 SELECT * FROM cdrcosts;
CREATE TABLE cdrcost_taxes_backup1 LIKE cdrcost_taxes;
INSERT INTO cdrcost_taxes_backup1 SELECT * FROM cdrcost_taxes;

=== Create enswitch database on enswitchcdrdb00: ===

CREATE DATABASE enswitch;

=== Create tables: ===

Do 'SHOW CREATE TABLE' on enswitchcdb00 for each of the 3 cdr* tables and run these on enswitchcdrdb00.

Create enswitchcdrsrw user on enswitchcdrdb00"

GRANT ALL ON enswitch.* to enswitchcdrsrw IDENTIFIED BY 'password';

=== Run enswitch_cdrs_archive_remote manually: ===

sudo su - enswitch -c "/opt/enswitch/current/bin/enswitch_cdrs_archive_remote 365 debug"

=== Cron configuration ===

Disable the original enswitch_cdrs_archive if it is currently in use.

= Configure web servers =

=== Instruct web interface to use the archive CDR database ===

Add the following to /etc/enswitch/databases.conf on the web servers:

delete/cdrs/archive, 1, 100, mysql, 10.1.0.93, 3306, enswitch, enswitchcdrsrw, PASSWORD, 1
insert/cdrs/archive, 1, 100, mysql, 10.1.0.93, 3306, enswitch, enswitchcdrsrw, PASSWORD, 1
select/cdrs/archive, 1, 100, mysql, 10.1.0.93, 3306, enswitch, enswitchcdrsrw, PASSWORD, 1

=== Restart apache ===

sudo service apache2 restart

= Configure roles =

In order to allow non System Owner users to search archived CDRs, set the "Call history (archived)" to "Yes" under the appropriate roles. It would be best to set this for only roles where it is absolutely necessary.

= Configure backups of archived CDRs =

CDRs archived on a remote server will no longer be backed up by the standard Enswitch backup script.

=== backup script ===

This script will make a backup for each day, overwriting the last.

Create /usr/local/sbin/mysql-backup.sh withthe following contents:

#!/bin/sh

DAY=`date +%a`
HOSTNAME=`hostname`
MYSQL_USERNAME=$1
MYSQL_PASSWORD=$2
BACKUP_PATH=$3
COMPRESS_LEVEL=$4

rm -f $BACKUP_PATH/mysql_backup-$HOSTNAME-$DAY.sql*

mysqldump -u $MYSQL_USERNAME --password=$MYSQL_PASSWORD --all-databases --skip-lock-tables --single-transaction > $BACKUP_PATH/mysql_backup-$HOSTNAME-$DAY.sql

if [ $COMPRESS_LEVEL -gt 0 ] && [ $COMPRESS_LEVEL -lt 10 ]
then
xz -$COMPRESS_LEVEL $BACKUP_PATH/mysql_backup-$HOSTNAME-$DAY.sql
fi

Change permissions on /usr/local/sbin/mysql-backup.sh

sudo chmod +x /usr/local/sbin/mysql-backup.sh

Add cron entry:

echo "1 0 * * * root /usr/local/bin/mysql-backup.sh root PASSWORD /root/mysqlbackups 3" | sudo tee /etc/cron.d/mysql-backup

Since the cron file will contain a MySQL password, make it readable only by root

sudo chmod 700 /etc/cron.d/mysql-backup

= References =

https://integrics.com/enswitch/guides/3.11/en/field/cdrs/

https://integrics.com/enswitch/guides/3.11/en/field/install/mysql/replication/

Archiving CDRs to a remote MySQL Master/Master pair - Revision history

Danthony at 18:10, 30 October 2015

Danthony at 18:04, 30 October 2015

Danthony at 18:04, 30 October 2015

Danthony at 17:00, 30 October 2015

Danthony: /* Create tables: */

Danthony: Created page with "= Disclaimer = The following comes with no warranty whatsoever. I am not responsible for any data loss or other issues that may arise from following these instructions. Pl..."