A2Billing – MySQL Master / Slave and Asterisk load balancing – Part 1

14 March 2012MattA2Billing

Below are instructions for setting up A2Billing with a MySQL Master/Slave database. In this configuration the A2Billing database is replicated to a standby/slave server. Whilst it would require a manual failover to the standy server, the configuration is pretty simple and easy to manage.

It also walks through setting up A2Billing load balancing across multiple Asterisk servers. This relies on Asterisk/A2Billing being configured to use RealTime, which it is by default on the SysAdminMan template.

All 3 servers used were built using the SysAdminMan VPS template. You will need to change IP addresses throughout the instructions to match your server IPs. The test servers used are –

Server 1 – IP Address 1.1.1.1 – Master MySQL server + Asterisk server
Server 2 – IP Address 2.2.2.2 – Slave MySQL server + Asterisk server
Server 3 – IP Address 3.3.3.3 – Asterisk server

While the instructions are quite long the outline of what is happening is –

Open ports in the firewall
Configure server 1 as the Master MySQL database
Export the mya2billing database from the Master server and copy it to the slave
Configure server 2 as the Slave MySQL database
Import the database from the Master server and start the replication
On all 3 servers configure Asterisk and A2Billing to use the Master database

On 1.1.1.1 – Configure Master MySQL server

Open MySQL ports in the firewall

iptables -I INPUT -s 2.2.2.2 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -I INPUT -s 3.3.3.3 -p tcp -m tcp --dport 3306 -j ACCEPT
service iptables save

Edit /etc/my.cnf and under the [mysqld] section ensure the following settings are set –

log-bin=mysql-bin
server-id=1
binlog-do-db=mya2billing

Restart MySQL

service mysqld restart

Create a MySQL user to allow replication from the Standby server

mysql -p
CREATE USER 'repl'@'2.2.2.2' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'2.2.2.2';
FLUSH PRIVILEGES;
exit;

Dump the mya2billing database and copy it to the Slave server

cd /root
mysqldump -p mya2billing --master-data --single-transaction >mya2billing.dump.db
scp /root/mya2billing.dump.db 2.2.2.2:/root

Make a note of the database log file and position. You will need these later on. Do this while the Master database is not being updated.

mysql -p
SHOW MASTER STATUS; * make a note of file and position
exit;

Make a note of the existing A2Billing database password being used. We will use it later.

grep password /etc/a2billing.conf
password = j1Ob74JWyH

On 2.2.2.2 – Configure Slave MySQL server

Open ports in the firewall for MySQL

iptables -I INPUT -s 1.1.1.1 -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -I INPUT -s 3.3.3.3 -p tcp -m tcp --dport 3306 -j ACCEPT
service iptables save

Edit /etc/my.cnf and under the [mysqld] section ensure the following settings are changed/set –

server-id=2 * change this - already there
relay-log=mysqld-relay-bin
replicate-do-db=mya2billing
log-bin=mysql-bin
log-slave-updates=false
binlog-do-db=mya2billing

Create a fresh database and assign some permissions in case we need to failover to using this as the primary.

mysql -p
drop database mya2billing;
create database mya2billing;
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'1.1.1.1' IDENTIFIED BY 'j1Ob74JWyH';
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'2.2.2.2' IDENTIFIED BY 'j1Ob74JWyH';
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'3.3.3.3' IDENTIFIED BY 'j1Ob74JWyH';
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'localhost' IDENTIFIED BY 'j1Ob74JWyH';
FLUSH PRIVILEGES;
exit;

Set the slave replication settings. You will need to change the settings below to reflect the IP of the Master plus the log file and position settings you noted earlier

CHANGE MASTER TO
MASTER_HOST='1.1.1.1',
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1343;
exit;

Import the mya2billing database and restart MySQL

mysql -p mya2billing < /root/mya2billing.dump.db
service mysqld restart

Check that the replication looks ok. Ensure – Slave_IO_State: Waiting for master to send event.

mysql -p
SHOW SLAVE STATUS\G
exit;

Next we need to tell A2Billing and Asterisk to use the mya2billing MySQL database on the Master database server.

On 1.1.1.1 – Set up some MySQL users to allow Asterisk/A2Billing servers to connect

Grant permissions to be allow secondary Asterisk/A2Billing servers to connect.

mysql -p
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'1.1.1.1' IDENTIFIED BY 'j1Ob74JWyH';
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'2.2.2.2' IDENTIFIED BY 'j1Ob74JWyH';
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'3.3.3.3' IDENTIFIED BY 'j1Ob74JWyH';
GRANT ALL PRIVILEGES ON mya2billing.* TO 'asteriskuser'@'localhost' IDENTIFIED BY 'j1Ob74JWyH';
FLUSH PRIVILEGES;
exit;

On 2.2.2.2 – Configure A2Billing and Asterisk to use the Master database

Edit /etc/a2billing.conf and ensure the following are changed.

hostname=1.1.1.1
password = j1Ob74JWyH

Edit /etc/asterisk/res_config_mysql.conf and ensure the following are changed under the [mya2billing] context.

dbhost = 1.1.1.1
dbpass = j1Ob74JWyH

service asterisk restart

On 3.3.3.3 – Configure A2Billing and Asterisk to use the Master database

Edit /etc/a2billing.conf and ensure the following are changed.

hostname=1.1.1.1
password = j1Ob74JWyH

Edit /etc/asterisk/res_config_mysql.conf and ensure the following are changed under the [mya2billing] context.

dbhost = 1.1.1.1
dbpass = j1Ob74JWyH

service asterisk restart

On ALL SERVERS – Create any trunks and Inbound Routes

Remember that anything that you don’t change via A2Billing you will need to do on all three servers. If it’s changed in A2Billing then it will be stored in the database, and accessible by all 3 servers.

Common things that you may need to do on all 3 servers are –

Create any required trunks
Create any inbound routes

And that’s replication set up. If everything has worked correctly you should be able to create your rates, customers, trunks etc. If you then create a SIP account you should be able to point a SIP phone to any of the 3 servers and make a call.

See some ideas in Part 2 for simple load balancing.

Tags: a2billing, balance, load, replicate

Previous post FusionPBX getting started : Conference Room Next post A2Billing – MySQL Master / Slave and Asterisk load balancing – Part 2

A2Billing troubleshooting – Ratecard Simulator

13 July 2011

FreePBX and A2Billing video guides

14 April 2012

Using callwithus with Asterisk, FreePBX and A2Billing

15 September 2008

1 comment. Leave new

Mettichi Bassem

19 April 2012 2:22 pm

Hello,

i have configured master/slave mysql servers as described in your tutorial, replication works fine in some tables but in cc_card table this replication didn’t work i have this error: access denied; you need the super privilege for this operation on query. Default database mya2billing. Query update cc_ratecard …..

I have add all privileges to replication_user but still the same error message