Before setting up MariaDB Galera with VitalPBX, ensure you have the following:+
1. Infrastructure Requirements:
At least three servers to form a Galera cluster for redundancy and high availability.
A stable network connection between cluster nodes with low latency.
2. Operating System:
• A Linux-based OS such as Debian, or Ubuntu, as MariaDB Galera works best in these environments.
3. Software Requirements:
• VitalPBX installed and running on a server.
• MariaDB or MySQL pre-installed (the same version on all nodes is crucial).
• Galera library compatible with your MariaDB version.
4. Configuration Knowledge:
• Familiarity with editing configuration files (e.g., my.cnf for MariaDB).
• Understanding of basic SQL commands for database management.
5. Backup and Security:
• Ensure you have a full backup of your VitalPBX database.
• Configure firewalls to allow necessary ports (default: 3306 for MariaDB and 4567-4568 for Galera synchronization).
With these prerequisites in place, you’ll be ready to configure MariaDB Galera to work seamlessly with VitalPBX, ensuring high availability and robust performance.
Implementing a MariaDB Galera Cluster for VitalPBX ensures high availability, scalability, and redundancy, making your communication system more robust and resilient.
This guide will walk you through configuring multiple database nodes to work in sync, enabling real-time data replication and failover capabilities, using Debian 12 as the operating system and MariaDB 10.11 as the database engine.
This combination provides a stable and modern platform for high availability and fault tolerance. By the end of this tutorial, you’ll have a fully functional Galera Cluster seamlessly integrated with VitalPBX, ensuring your system is reliable, robust, and ready to handle mission-critical workloads.
Step 1
3 Server
• Server1: 192.168.10.61
• Server2: 192.168.10.62
• Server3: 192.168.10.63
Step 2
Update OS
apt update && apt upgrade -y
Step 3
Firewall Configuration on All Three Servers
Make sure the following ports are open on the firewall of each server to allow communication between the nodes:
• 3306 (TCP): For MariaDB connections.
• 4567 (TCP/UDP): For Galera replication.
• 4568 (TCP): For Galera IST replication.
• 4444 (TCP): For SST state transfer.
apt install ufw -y
ufw allow 3306/tcp
ufw allow 4567/tcp
ufw allow 4567/udp
ufw allow 4568/tcp
ufw allow 4444/tcp
ufw allow from 192.168.10.0/23
ufw allow ssh
ufw enable
ufw reload
ufw status
Step 4
Install MariaDB on all three servers
apt install mariadb-server galera-4 rsync -y
Step 6
Configure the hosts file: Make sure each server can resolve the names of the other nodes. Edit /etc/hosts on each server:
nano /etc/hosts
Add
192.168.10.61 cluster01
192.168.10.62 cluster02
192.168.10.63 cluster03
Step 7
Create the State Transfer User (SST)
On one of the nodes, start MariaDB and create a user for state transfer (SST):
mysql -u root
CREATE USER 'sst_user'@'%' IDENTIFIED BY 'password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst_user'@'%';
FLUSH PRIVILEGES;
exit;
Step 8
Configure Galera on All Three Servers
a. Create the vitalpbx.cnf Configuration File on All Three Servers:
nano /etc/mysql/conf.d/vitalpbx.cnf
With the following configuration
[mysqld]
skip-name-resolve
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=64M
innodb_log_buffer_size=64M
bulk_insert_buffer_size=64M
innodb_lock_wait_timeout=180
tmp_table_size=128M
max_heap_table_size=128M
innodb_buffer_pool_size=800M
query_cache_size=64M
wait_timeout=1800
interactive_timeout=1800
max_allowed_packet=1G
log_warnings=1
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=ROW
innodb_autoinc_lock_mode=2
b. Edit the 50-server.cnf File on All Three Servers.
nano /etc/mysql/mariadb.conf.d/50-server.cnf
Change
bind-address = 127.0.0.1
To
bind-address = 0.0.0.0
c. Now let’s edit the 60-galera.cnf file on all three servers.
nano /etc/mysql/mariadb.conf.d/60-galera.cnf
And leave it as shown below
#
# * Galera-related settings
#
# See the examples of server wsrep.cnf files in /usr/share/mysql
# and read more at https://mariadb.com/kb/en/galera-cluster/
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_galera_cluster"
wsrep_cluster_address="gcomm://192.168.10.61,192.168.10.62,192.168.10.63"
wsrep_node_address="192.168.10.XX" # Replace XX with the corresponding node's IP address
wsrep_node_name="clusterXX" # Replace XX with the node number (01, 02, or 03)
wsrep_sst_method=rsync
# Usuario SST
wsrep_sst_auth=sst_user:password # We replace the user and password with the ones we created earlier.
Step 9:
Start the Cluster on the First Node
On the first server (e.g., cluster01), start the cluster:
galera_new_cluster
Verify that the node is functioning correctly:
mysql -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
You should see that the cluster size is 1 (only one node).
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
Next, restart MariaDB on the remaining nodes (Cluster02 and Cluster03):
systemctl restart mariadb
You can verify that the cluster is functioning correctly by checking the Galera status:
mysql -u root -e "SHOW STATUS LIKE 'wsrep%';"
Look for the following values:
• wsrep_cluster_size: It should match the number of nodes in your cluster.
• wsrep_ready: It should be ON on all nodes.
• wsrep_connected: It should be ON.
Note:
Sometimes it is necessary to restart all three servers to ensure proper synchronization. Occasionally, you may need to wait 5 to 10 minutes for all three servers to start MariaDB correctly.
To integrate VitalPBX with your MariaDB Galera cluster, follow these steps:
Export the VitalPBX (ombutel) and Asterisk databases.
VitalPBX installs several databases on the local server. These databases must be fully copied, including their respective permissions, to our MariaDB cluster.
Export:
mysqldump --databases asterisk ombutel provisioning vitalpbx_logs > vitalpbx_backup.sql
scp vitalpbx_backup.sql root@IP_REMOTA:/tmp/
Now, go to the server where your MariaDB cluster is located and restore the database:
cd /tmp
mysql -u root < vitalpbx_backup.sql
Finally, grant permissions to the users so they can be accessed from the local network:
mysql -u root
GRANT ALL PRIVILEGES ON *.* TO 'ombutel'@'192.168.10.%' IDENTIFIED BY 'ombutel' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'vitalpbx'@'192.168.10.%' IDENTIFIED BY 'vitalpbx' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'vitalpbx_logs'@'192.168.10.%' IDENTIFIED BY 'vitalpbx_logs' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'asterisk'@'192.168.10.%' IDENTIFIED BY 'asterisk' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;
Replace 192.168.10 with your local network’s IP address range.
Connecting to MariaDB with Galera Cluster
To configure a client to access a MariaDB Galera cluster without being affected if one of the servers is down, the ideal approach is to use a load balancer or configure the client to attempt connections to multiple nodes in case of failure.
Use a Load Balancer
A common and robust solution is to place a load balancer (such as HAProxy or ProxySQL) in front of the MariaDB nodes. The load balancer will distribute connections among the available nodes and automatically handle failover if one node goes down.
• HAProxy: HAProxy is a reliable option for load balancing MariaDB connections. It monitors the health of each node and redirects traffic only to available servers.
• ProxySQL: ProxySQL is a powerful tool specifically designed for database load balancing. It offers advanced features like query caching and real-time monitoring, making it ideal for MariaDB Galera clusters.
This setup ensures seamless operation and minimizes the impact of node failures on your applications or systems.
Step 1:
Disable MariaDB in VitalPBX
Before making configuration, changes or performing maintenance, it’s important to disable MariaDB on the server to prevent unintended issues. Use the following commands to stop and disable the MariaDB service
systemctl disable mariadb
systemctl stop mariadb
This ensures that MariaDB is not running while you proceed with the necessary adjustments.
Step 2:
MariaDB Client Configuration for Centralized Asterisk CDR Storage
To centralize the storage of Asterisk CDRs, configure the MariaDB client as follows:
Edit the odbc.ini File:
Open the configuration file located at /etc/odbc.ini for editing.
nano /etc/odbc.ini
[MySQL-asterisk]
Description = CDR Connection
Driver = MariaDB Unicode
Database = asterisk
User = asterisk
Password = asterisk
Server = 127.0.0.1
#Socket = /var/run/mysqld/mysqld.sock
[MySQL-vitalpbx]
Description = VPBX Connection
Driver = MariaDB Unicode
Database = ombutel
User = vitalpbx
Password = vitalpbx
Server = 127.0.0.1
#Socket = /var/run/mysqld/mysqld.sock
Configure the /etc/vitalpbx/database.conf File.
To enable centralized database storage and allow remote updates, follow these steps:
Edit the database.conf File:
Open the VitalPBX database configuration file for editingl.
nano /etc/vitalpbx/database.conf
[app]
database=ombutel
port=3306
host=127.0.0.1
username=ombutel
password=ombutel
[main]
host=127.0.0.1
port=3306
username=ombutel
password=ombutel
Step 3:
Install HAProxy
HAProxy is a reliable load balancer that can be used to distribute traffic across MariaDB Galera nodes. Follow these steps to install and configure HAProxy
apt-get install haproxy -y
Step 4:
Configure HAProxy
Edit the HAProxy Configuration File:
Open the HAProxy configuration file using your preferred text editor:
nano /etc/haproxy/haproxy.cfg
Configure HAProxy Frontend and Backend
To set up HAProxy for MariaDB Galera, add the following configuration to the end of the haproxy.cfg file.
defaults
log global
mode tcp
option tcplog
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms
frontend mysql_frontend
bind *:3306
mode tcp
default_backend mysql_backend
backend mysql_backend
mode tcp
balance roundrobin
option tcp-check
server cluster01 192.168.10.61:3306 check
server cluster02 192.168.10.62:3306 check
server cluster03 192.168.10.63:3306 check
Frontend:
• bind *:3306: Binds HAProxy to port 3306 to listen for MariaDB client connections.
• mode tcp: Ensures HAProxy works in TCP mode, which is necessary for database traffic.
• default_backend: Specifies the backend that handles incoming traffic.
Backend:
• balance roundrobin: Distributes requests evenly across all available nodes.
• option mysql-check user haproxy_user: Performs a health check on each node using the specified MariaDB user (haproxy_user).
• server: Defines each node in the cluster with its IP, port, and health check settings.
Step 5:
Restart HAProxy
To apply the new configuration, restart the HAProxy service with the following command:
systemctl restart haproxy
This will restart HAProxy and ensure that the new settings are in effect.
You can verify the status of HAProxy to ensure it’s running correctly:
systemctl status haproxy
Step 6:
Test – Configure the MySQL Client
Now, the MySQL client should connect through HAProxy. Use the IP of the server where you installed HAProxy (which is the same server as the client):
mysql -u tu_usuario -p -h 127.0.0.1 -P 3306
This command will ensure that connections are balanced between the MariaDB Galera nodes. If one of the nodes is down, HAProxy will automatically redirect the connections to the available nodes, ensuring high availability and seamless failover.
By configuring HAProxy as a load balancer for your MariaDB Galera cluster, you’ve ensured that your MySQL clients are able to access the database in a highly available and fault-tolerant manner.
With HAProxy in place, traffic is evenly distributed across the cluster nodes, and automatic failover is enabled in case a node becomes unavailable.
This setup enhances the reliability, scalability, and performance of your database infrastructure, ensuring that your applications remain operational even during hardware failures. The steps outlined in this guide provide a solid foundation for managing a robust MariaDB Galera cluster that can handle high loads and ensure seamless service continuity.
We are pleased to announce the release of VitalPBX 4.2.0 R2, featuring expanded API functionality, improved system security, and essential bug fixes. This update reinforces
VitalPBX 4.5.0 R3 enhances API capabilities with new endpoints, improves system security, and fixes critical issues including tenant isolation and Let’s Encrypt integration.
Introduction SNGREP is a powerful tool for real-time monitoring and analyzing SIP signaling and RTP streams. This documentation outlines how to use SNGREP to diagnose
VitalPBX provides a robust and scalable platform, which will allow you to manage your PBX in an easy and intuitive way.
2292 NW 82nd Ave HB 002998 Miami, Florida 33198.
Email: sales@vitalpbx.com
At VitalPBX, your feedback is crucial. If our system has benefited your business, please leave us a review on Trustpilot. Thank you!