View source for 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


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/