Redundant database

From VoIPmonitor.org
Revision as of 21:18, 4 January 2026 by Admin (talk | contribs) (Add PCAP migration section with timezone considerations)


This guide explains how to use a dedicated VoIPmonitor sensor instance to perform real-time replication of the CDR database to a secondary MySQL/MariaDB server. This provides a native, application-level method for creating backups or read-only replicas.

Overview & Use Case

VoIPmonitor offers a built-in mechanism to replicate its database. This is achieved by running a special instance of the sensor in "database backup mode." In this mode, the sensor does not sniff any packets; its sole purpose is to connect to a primary (source) database, read CDR data, and write it to a secondary (destination) database.

This method is an alternative to traditional MySQL replication and is particularly useful for:

  • Online GUI Migration: Migrating the GUI database from an old server to a new server with minimal or no downtime. The sensor replicates CDR data incrementally while users continue using the old GUI. Once replication completes, you can switch users to the new GUI without service interruption.
  • Creating a read-only replica database for reporting or analysis without impacting the performance of the primary database.
  • Setting up a hot-standby database for disaster recovery.
  • Consolidating data from multiple, smaller VoIPmonitor databases into one central database.

The process incrementally syncs data starting from a specified date, ensuring that the destination database stays up-to-date in near real-time.

Architecture Overview

Online GUI Migration Workflow

When migrating the GUI to a new server, you have two primary methods:

Method 1: Dump/Restore (With Downtime)

If a service interruption is acceptable:

  1. Stop the sniffer on the old server
  2. Perform a database dump: 
    mysqldump -u root -p voipmonitor > backup.sql
  3. Transfer and restore on new server: 
    mysql -u root -p voipmonitor < backup.sql
  4. Repoint the GUI to the new database
  5. Install and configure the new GUI instance

This method is simple but requires downtime and may take hours for terabyte-sized databases.

Method 2: Online Migration (Minimal/No Downtime)

Using the sensor's database backup mode:

  1. Install VoIPmonitor GUI on the new server with a fresh empty database
  2. On the old GUI: Go to Tools → Backup & Restore → Backup configuration TABLES and download the backup file
  3. On the new GUI: Go to Tools → Backup & Restore → Restore configuration tables and upload the backup file
    IMPORTANT: Complete this step BEFORE starting the migration instance
  4. Create a voipmonitor-migrate.conf file by copying the old voipmonitor.conf and adding migration options (see Configuration section below)
  5. Configure and run the migration instance in "database backup mode" on the new server
  6. The sensor connects to the old database and replicates CDR data incrementally
  7. Once replication catches up, switch users to the new GUI
  8. Stop migration instance and decommission the old server

For assistance with this method, provide remote SSH access to both the old and new hosts to the support team.

Comparison: Dump/Restore vs Online Migration

Aspect Dump/Restore Online Migration
Downtime Required (hours for large DBs) Minimal/None
Complexity Low - Manual SQL dump Medium - Sensor configuration
Data Loss Risk Moderate (if dump fails) Low - Incremental sync
Best For Small databases Large databases, zero-downtime requirements

Configuration

To set up database replication, you will run a new, dedicated VoIPmonitor sensor instance on the server that hosts your secondary (destination) database. This instance requires its own voipmonitor.conf file.

Creating the Migration Configuration File

The migration configuration file should be named consistently to avoid confusion (e.g., voipmonitor-migrate.conf).

Important: Start with a copy of the existing configuration. The easiest way to create this configuration is to copy the existing voipmonitor.conf from the old server and add the migration-specific options. This ensures all existing settings are preserved. 

# On the NEW server, copy the old config:
scp root@old-server:/etc/voipmonitor.conf /etc/voipmonitor-migrate.conf

Then edit /etc/voipmonitor-migrate.conf to modify the critical settings for migration mode.

Required Modifications

If you plan to run the migration instance while the original sniffer continues to capture traffic (zero-downtime migration), you MUST make the following changes to avoid conflicts: 

# --- 1. CRITICAL: Change managerport to avoid conflicts ---
# The original sniffer typically uses managerport = 5029
managerport = 5030

# --- 2. CRITICAL: Disable all packet capture bindings ---
# These prevent the migration instance from competing for network interfaces
# Comment out or delete ALL interface/bind options:
# interface = eth0       # DISABLE - do not capture packets
# interface = eth1       # DISABLE - do not capture packets
# interface = any        # DISABLE - do not capture packets

# --- 3. Destination Database (where to write data) ---
# Update to point to the NEW server's local database
mysqlhost                      = 127.0.0.1
mysqldb                        = voipmonitor
mysqlusername                  = root
mysqlpassword                  = new_db_password
cdr_partition                  = yes

# --- 4. Source Database (where to read data from) ---
# These parameters are ADDED to enable replication from the old database
database_backup_from_mysqlhost     = 192.168.0.1
database_backup_from_mysqldb       = voipmonitor
database_backup_from_mysqlusername = root
database_backup_from_mysqlpassword = old_db_password

# --- 5. Replication Control ---
# Choose the date from which replication should begin
database_backup_from_date          = 2024-01-01

# Performance tuning for replication speed
database_backup_insert_threads     = 3
database_backup_pause              = 3

Configuration Parameters Reference

Parameter Description
managerport TCP port for the sensor's manager API. Must be different from the production sniffer (default: 5029).
interface Network interface(s) to capture. Must be disabled/commented out for migration instance.
mysqlhost IP/hostname of the destination database (new server).
database_backup_from_mysqlhost IP/hostname of the source database (old server).
database_backup_from_date Start date for incremental sync (format: YYYY-MM-DD).
database_backup_insert_threads Number of parallel threads for inserting data (default: 1).
database_backup_pause Pause in seconds between sync batches (default: 0).

Why These Changes Are Required

managerport
VoIPmonitor uses this port for internal communication and GUI connectivity. If both instances use the same port, they will conflict.
interface options
The migration instance should NOT capture packets - its only purpose is database replication. Disabling packet capture prevents:
  • CPU competition with the production sniffer
  • Duplicate packets being processed by both instances
  • Network interface conflicts

GUI Configuration Backup

Before starting the migration instance, you must migrate the GUI configuration tables (users, sensors, capture rules, alerts). This is done via the GUI's Tools → Backup & Restore feature.

IMPORTANT: Complete this step BEFORE starting the migration instance. See Backup and restore GUI tables for detailed instructions.

Running the Replication Instance

Once your configuration file is ready, you can start the sensor in this special mode.

Manual Test Run

It is highly recommended to first run the process manually to ensure all settings are correct and there are no connection errors. 

voipmonitor --config-file /etc/voipmonitor-migrate.conf -k -v 1
Command-line options
  • -k: Prevents the process from forking into the background (keeps it in foreground).
  • -v 1: Sets verbosity to level 1, which will show status information.

Watch the output for any database connection errors or other warnings. A successful start will show messages indicating CDR data is being read from the source and written to the destination.

Running as a Service

To run the migration instance permanently as a background service, you will need to create a separate systemd or init.d service file for it. This process is covered in the Multiple Sniffer Instances guide. The key is to ensure the new service unit file uses the -c /etc/voipmonitor-migrate.conf argument to load your specific configuration.

Example systemd unit file (/etc/systemd/system/voipmonitor-migrate.service): 

[Unit]
Description=VoIPmonitor Database Migration Instance
After=network.target mysql.service

[Service]
Type=simple
ExecStart=/usr/local/sbin/voipmonitor -c /etc/voipmonitor-migrate.conf
Restart=on-failure

[Install]
WantedBy=multi-user.target

Enable and start: 

systemctl daemon-reload
systemctl enable voipmonitor-migrate
systemctl start voipmonitor-migrate

Migrating PCAP (Packet Capture) Files

The database migration process described above migrates only the CDR (Call Detail Records). It does not migrate PCAP files. You must migrate PCAP files separately.

Using rsync to Copy PCAPs

Use rsync over SSH to copy the spooldir (default: /var/spool/voipmonitor) from the old server to the new server. 

# Run on NEW SERVER pulling from OLD
rsync -avz root@old-server:/var/spool/voipmonitor/ /var/spool/voipmonitor/
-a
Archive mode (preserves permissions, times, symbolic links)
-v
Verbose output
-z
Compress during transfer (saves bandwidth)

Timezone Considerations (CRITICAL)

OS Timezone Must Match
If you are copying PCAP files from an old probe to a new probe that will continue capturing, ensure both systems have the same OS timezone configured.

PCAP file paths are based on directory structure: spooldir/YYYY-MM-DD/HH/MM/. The directory structure uses the system's local timezone. If the old probe was in timezone A and the new probe is in timezone B, the new probe will create PCAP files in different directory paths than the old files. This can cause the GUI to fail to find or link historical PCAPs.

The database stores relative paths, so as long as the directory structure is preserved and the timezone remains the same, the GUI will correctly reference the old PCAPs.

To check/set timezone: 

# Check current timezone
timedatectl

# Set timezone (example for Europe/Prague)
timedatectl set-timezone Europe/Prague

Architecture-Specific Considerations

Client-Server Mode with Local Processing
In the new architecture where probes store their own PCAPs (packetbuffer_sender=no), you have these options:
  • Keep the PCAPs on the OLD probe hardware if it is becoming one of the new probes
  • Archive historical PCAPs to external storage
  • Copy to the new probe only if timezone matches
Centralized PCAP Storage
If you are centralizing PCAPs (packetbuffer_sender=yes), copy all old PCAPs to the Central Server's spool directory. Ensure the central server's OS timezone matches all probes' timezones.

AI Summary for RAG

Summary: This article describes how to use a dedicated VoIPmonitor sensor instance in "database backup mode" to perform application-level replication of the MySQL/MariaDB database. This is useful for online GUI migration with minimal downtime, creating read-only replicas, hot-standby backups, or disaster recovery. The configuration requires setting up destination database parameters (mysqlhost, etc.) and source database parameters (database_backup_from_mysqlhost, etc.), with database_backup_from_date controlling the sync starting point. PCAP files must be migrated separately using rsync, with critical attention to timezone matching between old and new servers to ensure PCAP files remain accessible through the GUI.

Keywords: database replication, backup, mysql backup, mariadb replication, replica, read-only, standby, disaster recovery, GUI migration, migrate database, online migration, zero downtime, database_backup_from_date, database_backup_from_mysqlhost, voipmonitor.conf, high availability, PCAP migration, spooldir, rsync, timezone, packetbuffer_sender, client-server mode, centralized storage

Key Questions:

  • How can I migrate the GUI database with minimal downtime?
  • What is the difference between dump/restore and online migration?
  • How can I create a real-time backup of my VoIPmonitor database?
  • How do I replicate the VoIPmonitor database to another server?
  • What is the "database backup mode" for the sniffer?
  • What configuration is needed to mirror the VoIPmonitor database?
  • Can I use VoIPmonitor to create a read-only replica for reporting?
  • How does database_backup_from_date work?
  • How do I migrate PCAP files to a new server?
  • Why is timezone matching important when migrating PCAP files?
  • How can I use rsync to copy VoIPmonitor spooldir?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Redundant_database&oldid=6607"