Redundant database: Difference between revisions

From VoIPmonitor.org
No edit summary
(Rewrite: consolidated structure, removed redundancy, more concise)
 
(11 intermediate revisions 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 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:
VoIPmonitor offers three methods for CDR synchronization:
* 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.
{| class="wikitable"
|-
! 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 [[Mysql_master-master_replication_hints|Master-Master Replication]])
|-
| '''Dual Write''' || Sniffer writes CDRs to two databases simultaneously || Real-time backup, centralized reporting
|}


== Configuration ==
{{Note|All methods synchronize CDR data only. GUI settings (users, alerts, capture rules) must be migrated separately via '''Tools → Backup & Restore'''.}}
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.


Below is a complete example configuration file for a replication instance.
== Database Backup Mode ==


=== Example `voipmonitor.conf` for Replication ===
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.
Create a new configuration file, for example at `/etc/voipmonitor-backup.conf`:


<pre>
=== Architecture ===
# /etc/voipmonitor-backup.conf


[general]
<kroki lang="plantuml">
@startuml
skinparam shadowing false
skinparam defaultFontName Arial


# --- Destination Database (where to write data) ---
rectangle "OLD Server" {
# This instance connects to the local database where data will be replicated.
  rectangle "Sniffer" as SNIFF
mysqlhost                      = 127.0.0.1
  database "Source DB" as SRC
mysqldb                        = voipmonitor_replica  # It is wise to use a different DB name
}
mysqlusername                  = root
mysqlpassword                  = your_destination_db_password
cdr_partition                  = yes  # Ensure partitioning is enabled on the destination


# --- Source Database (where to read data from) ---
rectangle "NEW Server" {
# These parameters tell the sensor which primary database to replicate.
  rectangle "Migration\nInstance" as MIG
database_backup_from_mysqlhost   = 192.168.0.1 # IP of the PRIMARY database server
  database "Destination DB" as DST
database_backup_from_mysqldb     = voipmonitor   # Name of the source database
}
 
SNIFF -down-> SRC : writes CDRs
MIG -left-> SRC : reads\n(database_backup_from_*)
MIG -down-> DST : writes\n(mysql*)
 
note bottom of MIG
  No packet capture
  managerport = 5030
end note
@enduml
</kroki>
 
=== Online GUI Migration Workflow ===
 
# Install VoIPmonitor GUI on the new server with a fresh database
# '''Backup configuration:''' On old GUI → '''Tools → Backup & Restore''' → backup configuration tables
# '''Restore configuration:''' On new GUI → upload the backup file (do this BEFORE starting migration)
# Create migration config file (see below)
# Start migration instance
# Once replication catches up, switch users to the new GUI
# Stop migration instance and decommission old server
 
=== Configuration ===
 
Copy the original config and modify for migration mode:
 
<syntaxhighlight lang="bash">
scp root@old-server:/etc/voipmonitor.conf /etc/voipmonitor-migrate.conf
</syntaxhighlight>
 
'''Required modifications in <code>/etc/voipmonitor-migrate.conf</code>:'''
 
<syntaxhighlight lang="ini">
# 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_mysqlusername = root
database_backup_from_mysqlpassword = your_source_db_password
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
</syntaxhighlight>
 
'''Parameter Reference:'''
 
{| class="wikitable"
|-
! Parameter !! Description
|-
| <code>database_backup_from_date</code> || Start date for sync (YYYY-MM-DD)
|-
| <code>database_backup_insert_threads</code> || Parallel write threads (higher = faster, more load)
|-
| <code>database_backup_pause</code> || Seconds between batches (higher = gentler on source DB)
|-
| <code>database_backup_desc_dir</code> || Set <code>yes</code> to also write new incoming data to destination
|}
 
=== Running the Migration ===
 
'''Test manually first:'''
<syntaxhighlight lang="bash">
voipmonitor --config-file /etc/voipmonitor-migrate.conf -k -v 1
</syntaxhighlight>
 
'''Run as systemd service:'''
 
Create <code>/etc/systemd/system/voipmonitor-migrate.service</code>:
<syntaxhighlight lang="ini">
[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
</syntaxhighlight>
 
<syntaxhighlight lang="bash">
systemctl daemon-reload
systemctl enable --now voipmonitor-migrate
</syntaxhighlight>
 
== Dual Write Method ==
 
Configure the sniffer to write each CDR to both local and remote databases simultaneously.
 
=== Configuration ===
 
Add secondary database parameters to <code>/etc/voipmonitor.conf</code>:
 
<syntaxhighlight lang="ini">
# 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
</syntaxhighlight>
 
{{Warning|1=If the secondary database becomes unreachable, CDRs are written only to primary. Missed CDRs are NOT retroactively synced.}}
 
=== Comparison: Dual Write vs Master-Master ===
 
{| class="wikitable"
|-
! 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:
 
<syntaxhighlight lang="bash">
rsync -avz root@old-server:/var/spool/voipmonitor/ /var/spool/voipmonitor/
</syntaxhighlight>
 
{{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.}}


# --- Replication Control ---
<syntaxhighlight lang="bash">
# The date from which the replication should begin. The sensor will replicate all
# Check timezone
# data from this date forward.
timedatectl
database_backup_from_date        = 2024-01-01


# The number of parallel threads to use for inserting data into the destination database.
# Set timezone
# Increase this value for higher throughput.
timedatectl set-timezone Europe/Prague
database_backup_insert_threads  = 3
</syntaxhighlight>


# The pause in seconds between each batch of replicated data.
== Schema Compatibility ==
database_backup_pause            = 3
</pre>


== Running the Replication Instance ==
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


Once your configuration file is ready, you can start the sensor in this special mode.
'''To identify schema changes manually:'''
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
tail -f /var/log/voipmonitor/voipmonitor.log | grep -E "CLI|ALTER|CREATE TABLE"
</syntaxhighlight>


=== 1. Manual Test Run ===
{{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.}}
It is highly recommended to first run the process manually to ensure all settings are correct and there are no connection errors.
<pre>
voipmonitor --config-file /etc/voipmonitor-backup.conf -k -v 1
</pre>
* <code>-k</code>: Prevents the process from forking into the background.
* <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.


=== 2. Running as a Service ===
== See Also ==
To run the replication 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_instancies|Multiple Sniffer Instances]] guide. The key is to ensure the new service unit file uses the <code>-c /etc/voipmonitor-backup.conf</code> argument to load your specific configuration.
 
* [[Mysql_master-master_replication_hints|Master-Master Replication]]
* [[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 how to use a dedicated VoIPmonitor sensor instance to perform application-level replication of its MySQL/MariaDB database. It explains that this feature is an alternative to standard database replication, useful for creating read-only replicas or hot-standby backups. The guide provides a detailed example of a `voipmonitor.conf` file configured for this "database backup mode." It highlights the key parameters, distinguishing between the destination database connection settings (`mysqlhost`, etc.) and the source database settings (`database_backup_from_mysqlhost`, etc.). It explains the function of `database_backup_from_date` to control the starting point of the sync, and `database_backup_insert_threads` for performance tuning. Finally, it provides command-line instructions for running the replication instance, recommending a manual test run before setting it up as a permanent service.
 
'''Keywords:''' database replication, backup, mysql backup, mariadb replication, replica, read-only, standby, disaster recovery, database_backup_from_date, database_backup_from_mysqlhost, voipmonitor.conf, high availability
'''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 create a real-time backup of my VoIPmonitor database?
* How can I migrate VoIPmonitor database with zero downtime?
* How do I replicate the VoIPmonitor database to another server?
* What methods are available for database replication in VoIPmonitor?
* What is the "database backup mode" for the sniffer?
* What is the difference between database backup mode, dual write, and master-master replication?
* What configuration is needed to mirror the VoIPmonitor database?
* How do I configure the migration instance (voipmonitor-migrate.conf)?
* Can I use VoIPmonitor to create a read-only replica of my database for reporting?
* How can I control database load during replication (database_backup_pause)?
* How does the `database_backup_from_date` parameter work?
* 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?

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"