Redundant database: Difference between revisions

From VoIPmonitor.org
(Review: oprava {{Note}} šablony (prefix 1= pro text s =), nahrazení HTML <strong> za wiki formátování)
(Rewrite: consolidated structure, removed redundancy, more concise)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
{{DISPLAYTITLE:Database Replication using a Dedicated Sniffer Instance}}
{{DISPLAYTITLE:Database Redundancy and Migration}}
[[Category:GUI manual]]
[[Category:Configuration]]
[[Category:High Availability]]


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.
This guide covers methods for replicating, synchronizing, and migrating the VoIPmonitor CDR database between instances.


== Overview & Use Case ==
== Overview ==
VoIPmonitor offers multiple built-in mechanisms to replicate or synchronize Call Detail Records (CDRs) between instances. These methods enable datacenter failover, disaster recovery, hot-standby setups, and centralized reporting.


=== Available Synchronization Methods ===
VoIPmonitor offers three methods for CDR synchronization:


There are three primary approaches for synchronizing CDRs between VoIPmonitor instances:
{| class="wikitable"
 
|-
;1. Database Backup Mode (Read-Only Replica)
! Method !! Description !! Use Case
:Use a dedicated sniffer instance to read from the primary database and write to a secondary database. This creates a one-way, read-only replica. This method is ideal for online GUI migration, hot-standby setups, or centralized reporting.
|-
 
| '''Database Backup Mode''' || Dedicated sniffer reads from source DB, writes to destination DB || Online migration, read-only replicas, hot-standby
;2. Master-Master Database Replication
|-
:Configure MySQL/MariaDB in a bidirectional replication topology. Both instances can write CDRs, and changes automatically propagate between them. This provides full high-availability with automatic failover and read load balancing. See [[Mysql_master-master_replication_hints|Master-Master Replication]] for detailed setup.
| '''Master-Master Replication''' || Bidirectional MySQL/MariaDB replication || Full HA with automatic failover (see [[Mysql_master-master_replication_hints|Master-Master Replication]])
|-
| '''Dual Write''' || Sniffer writes CDRs to two databases simultaneously || Real-time backup, centralized reporting
|}


;3. Dual Write Method
{{Note|All methods synchronize CDR data only. GUI settings (users, alerts, capture rules) must be migrated separately via '''Tools → Backup & Restore'''.}}
:Configure the active sniffer to write CDRs simultaneously to both a local and a remote database. This provides real-time synchronization without requiring MySQL replication setup. '''Note:''' This method only synchronizes CDRs; it does not synchronize GUI settings (users, sensors, capture rules, etc.).


This article primarily covers '''Method 1 (Database Backup Mode)'''. Method 2 is covered in the [[Mysql_master-master_replication_hints|Master-Master Replication]] guide. Method 3 is described below.
== Database Backup Mode ==


The database backup mode method is particularly useful for:
A dedicated VoIPmonitor instance reads CDRs from a source database and writes them to a destination database. This is the recommended method for online GUI migration.
* '''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 ===
 
=== Architecture Overview ===


<kroki lang="plantuml">
<kroki lang="plantuml">
Line 36: Line 32:
skinparam shadowing false
skinparam shadowing false
skinparam defaultFontName Arial
skinparam defaultFontName Arial
skinparam rectangle {
  BorderColor #4A90E2
  BackgroundColor #FFFFFF
}


rectangle "OLD Server" as OLD {
rectangle "OLD Server" {
   rectangle "VoIPmonitor\nSniffer" as SNIFF
   rectangle "Sniffer" as SNIFF
   database "MySQL\n(Source DB)" as SRC_DB
   database "Source DB" as SRC
}
}


rectangle "NEW Server" as NEW {
rectangle "NEW Server" {
   rectangle "Migration\nInstance" as MIG
   rectangle "Migration\nInstance" as MIG
   database "MySQL\n(Destination DB)" as DST_DB
   database "Destination DB" as DST
}
}


SNIFF -down-> SRC_DB : writes CDRs
SNIFF -down-> SRC : writes CDRs
MIG -left-> SRC_DB : reads CDRs\n(database_backup_from_*)
MIG -left-> SRC : reads\n(database_backup_from_*)
MIG -down-> DST_DB : writes CDRs\n(mysql*)
MIG -down-> DST : writes\n(mysql*)


note bottom of MIG
note bottom of MIG
   Migration instance runs with:
   No packet capture
  - No packet capture (interface disabled)
   managerport = 5030
   - Different managerport (5030)
  - database_backup_from_date = 2024-01-01
end note
end note
@enduml
@enduml
</kroki>
</kroki>


== Online GUI Migration Workflow ==
=== 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:
# Stop the sniffer on the old server
# Perform a database dump:
#:<syntaxhighlight lang="bash">mysqldump -u root -p voipmonitor > backup.sql</syntaxhighlight>
# Transfer and restore on new server:
#:<syntaxhighlight lang="bash">mysql -u root -p voipmonitor < backup.sql</syntaxhighlight>
# Repoint the GUI to the new database
# 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) ===
# Install VoIPmonitor GUI on the new server with a fresh database
Using the sensor's database backup mode:
# '''Backup configuration:''' On old GUI → '''Tools → Backup & Restore''' → backup configuration tables
# Install VoIPmonitor GUI on the new server with a fresh empty database
# '''Restore configuration:''' On new GUI → upload the backup file (do this BEFORE starting migration)
# On the old GUI: Go to '''Tools → Backup & Restore → Backup configuration TABLES''' and download the backup file
# Create migration config file (see below)
# On the new GUI: Go to '''Tools → Backup & Restore → Restore configuration tables''' and upload the backup file
# Start migration instance
#:'''IMPORTANT:''' Complete this step BEFORE starting the migration instance
# Create a <code>voipmonitor-migrate.conf</code> file by copying the old <code>voipmonitor.conf</code> and adding migration options (see Configuration section below)
# Configure and run the migration instance in "database backup mode" on the new server
# The sensor connects to the old database and replicates CDR data incrementally
# Once replication catches up, switch users to the new GUI
# Once replication catches up, switch users to the new GUI
# Stop migration instance and decommission the old server
# Stop migration instance and decommission old server


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


== Comparison: Dump/Restore vs Online Migration ==
Copy the original config and modify for migration mode:
 
{| class="wikitable"
|-
! 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 <code>voipmonitor.conf</code> file.
 
=== Creating the Migration Configuration File ===
 
The migration configuration file should be named consistently to avoid confusion (e.g., <code>voipmonitor-migrate.conf</code>).
 
'''Important:''' Start with a copy of the existing configuration. The easiest way to create this configuration is to copy the existing <code>voipmonitor.conf</code> from the old server and add the migration-specific options. This ensures all existing settings are preserved.


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# On the NEW server, copy the old config:
scp root@old-server:/etc/voipmonitor.conf /etc/voipmonitor-migrate.conf
scp root@old-server:/etc/voipmonitor.conf /etc/voipmonitor-migrate.conf
</syntaxhighlight>
</syntaxhighlight>


Then edit <code>/etc/voipmonitor-migrate.conf</code> to modify the critical settings for migration mode.
'''Required modifications in <code>/etc/voipmonitor-migrate.conf</code>:'''
 
=== 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:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
# --- 1. CRITICAL: Change managerport to avoid conflicts ---
# 1. Avoid port conflict with production sniffer
# The original sniffer typically uses managerport = 5029
managerport = 5030
managerport = 5030


# --- 2. CRITICAL: Disable all packet capture bindings ---
# 2. DISABLE packet capture (comment out all interface lines)
# These prevent the migration instance from competing for network interfaces
# interface = eth0
# 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) ---
# 3. Destination Database (NEW server - local)
# Update to point to the NEW server's local database
mysqlhost     = 127.0.0.1
mysqlhost                     = 127.0.0.1
mysqldb       = voipmonitor
mysqldb                       = voipmonitor
mysqlusername = root
mysqlusername                 = root
mysqlpassword = new_password
mysqlpassword                 = new_db_password
cdr_partition                  = yes


# --- 4. Source Database (where to read data from) ---
# 4. Source Database (OLD server - remote)
# These parameters are ADDED to enable replication from the old database
database_backup_from_mysqlhost    = 192.168.0.1
database_backup_from_mysqlhost    = 192.168.0.1
database_backup_from_mysqldb      = voipmonitor
database_backup_from_mysqldb      = voipmonitor
database_backup_from_mysqlusername = root
database_backup_from_mysqlusername = root
database_backup_from_mysqlpassword = old_db_password
database_backup_from_mysqlpassword = old_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


# Performance Impact Explanation:
# 5. Start date for replication
# - database_backup_pause: Higher values (e.g., 10-60 seconds) create MORE pause between batches,
database_backup_from_date = 2024-01-01
#  resulting in LOWER load on the SOURCE database. Use high values if the source database
#  is also handling live traffic and cannot withstand additional replication load.
# - database_backup_insert_threads: Higher values use MORE threads for inserting data,
#  resulting in HIGHER load on both source and destination databases. Lower values (1-2)
#  are gentler on the system but replication is slower.


# --- 6. Optional: Migrate Static Data ---
# 6. Performance tuning
# Set to 'yes' if migrating static data and you want to write NEW data to the new database.
database_backup_insert_threads = 3  # Higher = faster but more load
# This is useful when you want the new server to continue accepting data after migration.
database_backup_pause          = 3  # Higher = less load on source DB
database_backup_desc_dir = yes
</syntaxhighlight>
</syntaxhighlight>


{{Note|1=<code>database_backup_desc_dir = yes</code> is an optional parameter that should be used when migrating static data (historical CDRs) and you plan to write new data to the destination database. This tells the migration instance to write both migrated data AND future new data to the destination database.}}
'''Parameter Reference:'''
 
=== Configuration Parameters Reference ===


{| class="wikitable"
{| class="wikitable"
Line 185: Line 107:
! Parameter !! Description
! Parameter !! Description
|-
|-
| <code>managerport</code> || TCP port for the sensor's manager API. Must be different from the production sniffer (default: 5029).
| <code>database_backup_from_date</code> || Start date for sync (YYYY-MM-DD)
|-
| <code>interface</code> || Network interface(s) to capture. Must be disabled/commented out for migration instance.
|-
|-
| <code>mysqlhost</code> || IP/hostname of the '''destination''' database (new server).
| <code>database_backup_insert_threads</code> || Parallel write threads (higher = faster, more load)
|-
|-
| <code>database_backup_from_mysqlhost</code> || IP/hostname of the '''source''' database (old server).
| <code>database_backup_pause</code> || Seconds between batches (higher = gentler on source DB)
|-
|-
| <code>database_backup_from_date</code> || Start date for incremental sync (format: YYYY-MM-DD).
| <code>database_backup_desc_dir</code> || Set <code>yes</code> to also write new incoming data to destination
|-
| <code>database_backup_insert_threads</code> || Number of parallel threads for inserting data (default: 1). Higher values = higher load on both source and destination databases. Lower values (1-2) = gentler on the system but slower replication.
|-
| <code>database_backup_pause</code> || Pause in seconds between sync batches (default: 0). Higher values = lower load on the SOURCE database. Use high values (10-60 seconds) if the source database handles live traffic.
|}
|}


=== Why These Changes Are Required ===
=== Running the Migration ===
 
;'''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|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.


'''Test manually first:'''
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
voipmonitor --config-file /etc/voipmonitor-migrate.conf -k -v 1
voipmonitor --config-file /etc/voipmonitor-migrate.conf -k -v 1
</syntaxhighlight>
</syntaxhighlight>


;Command-line options:
'''Run as systemd service:'''
* <code>-k</code>: Prevents the process from forking into the background (keeps it in foreground).
* <code>-v 1</code>: 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 <code>systemd</code> or <code>init.d</code> service file for it. This process is covered in the [[Multiple_sniffer_instancies|Multiple Sniffer Instances]] guide. The key is to ensure the new service unit file uses the <code>-c /etc/voipmonitor-migrate.conf</code> argument to load your specific configuration.
 
Example <code>systemd</code> unit file (<code>/etc/systemd/system/voipmonitor-migrate.service</code>):


Create <code>/etc/systemd/system/voipmonitor-migrate.service</code>:
<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
[Unit]
[Unit]
Description=VoIPmonitor Database Migration Instance
Description=VoIPmonitor Database Migration
After=network.target mysql.service
After=network.target mysql.service


Line 253: Line 140:
</syntaxhighlight>
</syntaxhighlight>


Enable and start:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
systemctl daemon-reload
systemctl daemon-reload
systemctl enable voipmonitor-migrate
systemctl enable --now voipmonitor-migrate
systemctl start voipmonitor-migrate
</syntaxhighlight>
</syntaxhighlight>


== Dual Write Method (Real-Time Synchronization) ==
== Dual Write Method ==
 
The dual write method is a simpler alternative to MySQL replication for synchronizing CDRs between two active VoIPmonitor instances. Instead of configuring database replication at the MySQL level, you configure the sniffer to write each CDR to both a local (primary) and a remote (secondary) database simultaneously.
 
=== Overview ===


In this setup, the sniffer writes each CDR record to two databases:
Configure the sniffer to write each CDR to both local and remote databases simultaneously.
* '''Primary Database''' (local): The main database for the instance
* '''Secondary Database''' (remote): The secondary instance's database
 
This provides real-time synchronization without the complexity of MySQL replication configuration.
 
=== Use Cases ===
 
* '''Active-Active Datacenter Deployment''': Two separate datacenters both capturing traffic, with each sniffer writing to the other's database
* '''Real-time Backup''': Every CDR is immediately backed up to a remote database
* '''Centralized Reporting''': Multiple sniffers write to a central reporting database


=== Configuration ===
=== Configuration ===


To enable dual write mode, add the secondary database connection parameters to your <code>voipmonitor.conf</code>:
Add secondary database parameters to <code>/etc/voipmonitor.conf</code>:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
# Primary Database Connection (Local)
# Primary Database (local)
mysqlhost      = 127.0.0.1
mysqlhost      = 127.0.0.1
mysqldb        = voipmonitor
mysqldb        = voipmonitor
Line 289: Line 160:
mysqlpassword  = primary_password
mysqlpassword  = primary_password


# Secondary Database Connection (Remote) - These parameters enable dual write
# Secondary Database (remote) - enables dual write
mysql2host      = 192.168.1.100
mysql2host      = 192.168.1.100
mysql2db        = voipmonitor
mysql2db        = voipmonitor
Line 296: Line 167:
</syntaxhighlight>
</syntaxhighlight>


=== Important Notes ===
{{Warning|1=If the secondary database becomes unreachable, CDRs are written only to primary. Missed CDRs are NOT retroactively synced.}}
 
;'''GUI Settings Not Synchronized'''
:The dual write method only replicates CDR data. GUI configuration tables (users, sensors, capture rules, alerts) are NOT synchronized. You must manually configure these on each instance.
 
;'''Write Performance Impact'''
:Writing to two databases adds overhead. Ensure your network can handle the additional database traffic. Consider using a dedicated database server for the secondary if performance becomes an issue.
 
;'''Connection Failure Handling'''
:If the secondary database becomes unreachable, the sniffer will log connection errors but continue writing to the primary database. CDRs written during the outage will NOT be retroactively synced.
 
;'''Suitable for Simple Scenarios'''
:For complex high-availability requirements with bidirectional write support, consider [[Mysql_master-master_replication_hints|Master-Master replication]] instead.


=== Comparison: Dual Write vs Master-Master Replication ===
=== Comparison: Dual Write vs Master-Master ===


{| class="wikitable"
{| class="wikitable"
|-
|-
! Aspect !! Dual Write Method !! Master-Master Replication
! Aspect !! Dual Write !! Master-Master
|-
| '''Configuration Complexity''' || Low - Add mysql2* parameters || Medium - MySQL replication setup
|-
|-
| '''Bidirectional Sync''' || No - A writes to B (one-way) || Yes - A and B sync with each other
| '''Complexity''' || Low (add mysql2* params) || Medium (MySQL replication setup)
|-
|-
| '''GUI Settings Sync''' || No || Yes (via MySQL replication)
| '''Bidirectional''' || No || Yes
|-
|-
| '''Automatic Failover''' || No || Yes (with load balancer)
| '''GUI Settings Sync''' || No || Yes
|-
|-
| '''Best For''' || Simple one-way backup || Full high-availability setup
| '''Best For''' || Simple one-way backup || Full HA setup
|}
|}


== Migrating PCAP (Packet Capture) Files ==
== Migrating PCAP 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 <code>rsync</code> over SSH to copy the <code>spooldir</code> (default: <code>/var/spool/voipmonitor</code>) from the old server to the new server.
Database migration does NOT include PCAP files. Migrate separately using rsync:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Run on NEW SERVER pulling from OLD
rsync -avz root@old-server:/var/spool/voipmonitor/ /var/spool/voipmonitor/
rsync -avz root@old-server:/var/spool/voipmonitor/ /var/spool/voipmonitor/
</syntaxhighlight>
</syntaxhighlight>


;<code>-a</code>: Archive mode (preserves permissions, times, symbolic links)
{{Warning|1='''Timezone must match between servers.''' PCAP paths use local timezone (<code>spooldir/YYYY-MM-DD/HH/MM/</code>). Mismatched timezones cause GUI to fail finding historical PCAPs.}}
;<code>-v</code>: Verbose output
;<code>-z</code>: Compress during transfer (saves bandwidth)


=== Timezone Considerations (CRITICAL) ===
<syntaxhighlight lang="bash">
# Check timezone
timedatectl


;'''OS Timezone Must Match'''
# Set timezone
: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.
timedatectl set-timezone Europe/Prague
</syntaxhighlight>


PCAP file paths are based on directory structure: <code>spooldir/YYYY-MM-DD/HH/MM/</code>. 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.
== Schema Compatibility ==


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.
The migration instance automatically handles database schema upgrades. This is useful when:
* Migrating to different MySQL/MariaDB versions
* Upgrading VoIPmonitor versions during migration
* Moving from local MySQL to remote Percona


To check/set timezone:
'''To identify schema changes manually:'''
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Check current timezone
systemctl restart voipmonitor
timedatectl
tail -f /var/log/voipmonitor/voipmonitor.log | grep -E "CLI|ALTER|CREATE TABLE"
 
# Set timezone (example for Europe/Prague)
timedatectl set-timezone Europe/Prague
</syntaxhighlight>
</syntaxhighlight>


=== Architecture-Specific Considerations ===
{{Warning|1=For cross-version migration (e.g., MySQL 5.7 to 8.0), use the migration instance method instead of mysqldump. It handles schema differences automatically.}}


;'''Client-Server Mode with Local Processing'''
== See Also ==
:In the new architecture where probes store their own PCAPs (<code>packetbuffer_sender=no</code>), 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'''
* [[Mysql_master-master_replication_hints|Master-Master Replication]]
:If you are centralizing PCAPs (<code>packetbuffer_sender=yes</code>), copy all old PCAPs to the Central Server's spool directory. Ensure the central server's OS timezone matches all probes' timezones.
* [[Backing_Up_GUI_Configuration|Backing Up GUI Configuration]]
* [[Multiple_sniffer_instancies|Multiple Sniffer Instances]]


== AI Summary for RAG ==
== AI Summary for RAG ==
'''Summary:''' This article describes three methods for synchronizing VoIPmonitor databases between instances: (1) Database Backup Mode - using a dedicated sensor in "database backup mode" to perform application-level replication for read-only replicas (described in detail here), (2) Master-Master Database Replication - bidirectional MySQL/MariaDB replication for full high-availability (covered in [[Mysql_master-master_replication_hints|Master-Master Replication]]), and (3) Dual Write Method - configuring the sniffer to write CDRs simultaneously to local and remote databases using mysql2* parameters. The article primarily covers Method 1 with configuration details for setting up destination (mysqlhost) and source (database_backup_from_mysqlhost) databases, including database_backup_from_date for sync control. To control database load during replication, use database_backup_pause (higher values = lower load on source database) and database_backup_insert_threads (higher values = higher load on both databases). PCAP files must be migrated separately using rsync, with critical attention to timezone matching between servers.


'''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, dual write, mysql2host, master-master replication, synchronize, datacenter, performance tuning, database_backup_pause, database_backup_insert_threads, performance impact, load control
'''Summary:''' VoIPmonitor offers three CDR synchronization methods: (1) Database Backup Mode - a dedicated sensor reads from source DB and writes to destination DB for online migration or read-only replicas, configured with <code>database_backup_from_*</code> parameters; (2) Master-Master Replication - bidirectional MySQL replication for full HA (see separate article); (3) Dual Write - sniffer writes CDRs to both local and remote DB using <code>mysql2*</code> parameters. Key configuration: <code>database_backup_from_date</code> sets sync start date, <code>database_backup_pause</code> controls load on source DB (higher = less load), <code>database_backup_insert_threads</code> controls write parallelism. Migration instance must disable packet capture (comment out <code>interface</code>) and use different <code>managerport</code>. PCAP files must be migrated separately with rsync, ensuring timezone matches between servers. The migration instance automatically handles database schema upgrades when moving between MySQL/MariaDB versions.
 
'''Keywords:''' database replication, mysql backup, database migration, online migration, zero downtime, database_backup_from_date, database_backup_from_mysqlhost, database_backup_pause, database_backup_insert_threads, dual write, mysql2host, PCAP migration, rsync, timezone, schema upgrade, voipmonitor-migrate.conf, read-only replica, disaster recovery, high availability


'''Key Questions:'''
'''Key Questions:'''
* How can I synchronize CDRs between two VoIPmonitor instances?
* How can I migrate VoIPmonitor database with zero downtime?
* What methods are available for database replication in VoIPmonitor?
* What methods are available for database replication in VoIPmonitor?
* What is the difference between database backup mode, dual write, and master-master replication?
* What is the difference between database backup mode, dual write, and master-master replication?
* How can I migrate the GUI database with minimal downtime?
* How do I configure the migration instance (voipmonitor-migrate.conf)?
* What is the difference between dump/restore and online migration?
* How can I control database load during replication (database_backup_pause)?
* How can I create a real-time backup of my VoIPmonitor database?
* How do I configure dual write mode (mysql2* parameters)?
* How do I configure dual write mode (mysql2*)?
* 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?
* How do I migrate PCAP files to a new server?
* Why is timezone matching important when migrating PCAP files?
* Why is timezone matching important when migrating PCAP files?
* How can I use rsync to copy VoIPmonitor spooldir?
* Does the migration instance handle database schema upgrades automatically?
* How can I control the database load during replication?
* What configuration parameters are needed for database backup mode?
* What is database_backup_pause and how does it affect performance?
* What is database_backup_insert_threads and how does it affect performance?

Latest revision as of 16:48, 8 January 2026


This guide covers methods for replicating, synchronizing, and migrating the VoIPmonitor CDR database between instances.

Overview

VoIPmonitor offers three methods for CDR synchronization:

Method Description Use Case
Database Backup Mode Dedicated sniffer reads from source DB, writes to destination DB Online migration, read-only replicas, hot-standby
Master-Master Replication Bidirectional MySQL/MariaDB replication Full HA with automatic failover (see Master-Master Replication)
Dual Write Sniffer writes CDRs to two databases simultaneously Real-time backup, centralized reporting

ℹ️ Note: All methods synchronize CDR data only. GUI settings (users, alerts, capture rules) must be migrated separately via Tools → Backup & Restore.

Database Backup Mode

A dedicated VoIPmonitor instance reads CDRs from a source database and writes them to a destination database. This is the recommended method for online GUI migration.

Architecture

Online GUI Migration Workflow

  1. Install VoIPmonitor GUI on the new server with a fresh database
  2. Backup configuration: On old GUI → Tools → Backup & Restore → backup configuration tables
  3. Restore configuration: On new GUI → upload the backup file (do this BEFORE starting migration)
  4. Create migration config file (see below)
  5. Start migration instance
  6. Once replication catches up, switch users to the new GUI
  7. Stop migration instance and decommission old server

Configuration

Copy the original config and modify for migration mode: 

scp root@old-server:/etc/voipmonitor.conf /etc/voipmonitor-migrate.conf

Required modifications in /etc/voipmonitor-migrate.conf: 

# 1. Avoid port conflict with production sniffer
managerport = 5030

# 2. DISABLE packet capture (comment out all interface lines)
# interface = eth0

# 3. Destination Database (NEW server - local)
mysqlhost      = 127.0.0.1
mysqldb        = voipmonitor
mysqlusername  = root
mysqlpassword  = new_password

# 4. Source Database (OLD server - remote)
database_backup_from_mysqlhost     = 192.168.0.1
database_backup_from_mysqldb       = voipmonitor
database_backup_from_mysqlusername = root
database_backup_from_mysqlpassword = old_password

# 5. Start date for replication
database_backup_from_date = 2024-01-01

# 6. Performance tuning
database_backup_insert_threads = 3   # Higher = faster but more load
database_backup_pause          = 3   # Higher = less load on source DB

Parameter Reference:

Parameter Description
database_backup_from_date Start date for sync (YYYY-MM-DD)
database_backup_insert_threads Parallel write threads (higher = faster, more load)
database_backup_pause Seconds between batches (higher = gentler on source DB)
database_backup_desc_dir Set yes to also write new incoming data to destination

Running the Migration

Test manually first: 

voipmonitor --config-file /etc/voipmonitor-migrate.conf -k -v 1

Run as systemd service:

Create /etc/systemd/system/voipmonitor-migrate.service: 

[Unit]
Description=VoIPmonitor Database Migration
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

systemctl daemon-reload
systemctl enable --now voipmonitor-migrate

Dual Write Method

Configure the sniffer to write each CDR to both local and remote databases simultaneously.

Configuration

Add secondary database parameters to /etc/voipmonitor.conf: 

# Primary Database (local)
mysqlhost      = 127.0.0.1
mysqldb        = voipmonitor
mysqlusername  = root
mysqlpassword  = primary_password

# Secondary Database (remote) - enables dual write
mysql2host      = 192.168.1.100
mysql2db        = voipmonitor
mysql2username  = root
mysql2password  = secondary_password

⚠️ Warning: If the secondary database becomes unreachable, CDRs are written only to primary. Missed CDRs are NOT retroactively synced.

Comparison: Dual Write vs Master-Master

Aspect Dual Write Master-Master
Complexity Low (add mysql2* params) Medium (MySQL replication setup)
Bidirectional No Yes
GUI Settings Sync No Yes
Best For Simple one-way backup Full HA setup

Migrating PCAP Files

Database migration does NOT include PCAP files. Migrate separately using rsync: 

rsync -avz root@old-server:/var/spool/voipmonitor/ /var/spool/voipmonitor/

⚠️ Warning: Timezone must match between servers. PCAP paths use local timezone (spooldir/YYYY-MM-DD/HH/MM/). Mismatched timezones cause GUI to fail finding historical PCAPs. 

# Check timezone
timedatectl

# Set timezone
timedatectl set-timezone Europe/Prague

Schema Compatibility

The migration instance automatically handles database schema upgrades. This is useful when:

  • Migrating to different MySQL/MariaDB versions
  • Upgrading VoIPmonitor versions during migration
  • Moving from local MySQL to remote Percona

To identify schema changes manually: 

systemctl restart voipmonitor
tail -f /var/log/voipmonitor/voipmonitor.log | grep -E "CLI|ALTER|CREATE TABLE"

⚠️ Warning: For cross-version migration (e.g., MySQL 5.7 to 8.0), use the migration instance method instead of mysqldump. It handles schema differences automatically.

See Also

AI Summary for RAG

Summary: VoIPmonitor offers three CDR synchronization methods: (1) Database Backup Mode - a dedicated sensor reads from source DB and writes to destination DB for online migration or read-only replicas, configured with database_backup_from_* parameters; (2) Master-Master Replication - bidirectional MySQL replication for full HA (see separate article); (3) Dual Write - sniffer writes CDRs to both local and remote DB using mysql2* parameters. Key configuration: database_backup_from_date sets sync start date, database_backup_pause controls load on source DB (higher = less load), database_backup_insert_threads controls write parallelism. Migration instance must disable packet capture (comment out interface) and use different managerport. PCAP files must be migrated separately with rsync, ensuring timezone matches between servers. The migration instance automatically handles database schema upgrades when moving between MySQL/MariaDB versions.

Keywords: database replication, mysql backup, database migration, online migration, zero downtime, database_backup_from_date, database_backup_from_mysqlhost, database_backup_pause, database_backup_insert_threads, dual write, mysql2host, PCAP migration, rsync, timezone, schema upgrade, voipmonitor-migrate.conf, read-only replica, disaster recovery, high availability

Key Questions:

  • How can I migrate VoIPmonitor database with zero downtime?
  • What methods are available for database replication in VoIPmonitor?
  • What is the difference between database backup mode, dual write, and master-master replication?
  • How do I configure the migration instance (voipmonitor-migrate.conf)?
  • How can I control database load during replication (database_backup_pause)?
  • How do I configure dual write mode (mysql2* parameters)?
  • How do I migrate PCAP files to a new server?
  • Why is timezone matching important when migrating PCAP files?
  • Does the migration instance handle database schema upgrades automatically?
  • What configuration parameters are needed for database backup mode?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Redundant_database&oldid=10096"