Archiving CDRs to a remote MySQL Master/Master pair

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:

• database0 - Main active database server
• database1 - Main standby database server
• cdrdatabase0 - Server where CDRs will be archived
• cdrdatabase1 - 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

Firewall configuration

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 cdrdatabase0 and cdrdatabase1:

sudo apt-get install mysql-server

Configure master/master replication between cdrdatabase0 and cdrdatabase1:

Enable remote connections to MySQL on cdrdatabase0 and cdrdatabase1. 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 cdrdatabase0 and cdrdatabase1:

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 cdrdatabase0:

server-id = 10

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

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 cdrdatabase0:

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

Add a replicate user on cdrdatabase1:

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

On cdrdatabase0, 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 cdrdatabase1:

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 cdrdatabase1:

slave start;

On cdrdatabase1, 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 cdrdatabase0:

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 cdrdatabase0:

slave start;

Install and configure Heartbeat

Add entries to /etc/hosts for each server on cdrdatabase0 and cdrdatabase1:

10.1.0.88   cdrdatabase0
10.1.0.89   cdrdatabase1

Install heartbeat:

sudo apt-get install heartbeat

Configure heartbeat:

Create /etc/ha.d/haresources:

On cdrdatabase0 and cdrdatabase1:

cdrdatabase0 IPaddr::10.1.0.93/26/eth0

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

On cdrdatabase0:

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 cdrdatabase0
node cdrdatabase1
respawn hacluster /usr/lib/heartbeat/ipfail

On cdrdatabase1:

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 cdrdatabase0
node cdrdatabase1
respawn hacluster /usr/lib/heartbeat/ipfail

Create /etc/ha.d/authkeys on cdrdatabase0 and cdrdatabase1:

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 cdrdatabase0:

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

On cdrdatabase1:

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

Apply new firewall rules:

sudo sh /etc/firewall.sh

Start Heartbeat:

Start heartbeat on cdrdatabase0:

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 cdrdatabase1:

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 cdrdatabase0:

CREATE DATABASE enswitch;

Create tables:

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

Create enswitchcdrsrw user on cdrdatabase0"

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/