Redundant database: Difference between revisions

From VoIPmonitor.org
(Add detailed migration configuration steps: copy old voipmonitor.conf, change managerport, disable bind* options for zero-downtime migration)
(Fix AI Summary format: use bold labels instead of sub-headings)
 
(16 intermediate revisions by 2 users 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 the following methods for CDR synchronization:
* '''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.
{| 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]])
|}


== Online GUI Migration Workflow ==
{{Note|Both methods synchronize CDR data only. GUI settings (users, alerts, capture rules) must be migrated separately via '''Tools → Backup & Restore'''.}}
When migrating the GUI to a new server, you have two primary methods:


=== Method 1: Dump/Restore (With Downtime) ===
== Database Backup Mode ==
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.
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.


=== Method 2: Online Migration (Minimal/No Downtime) ===
=== Architecture ===
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 BEFORE starting migration instance)
4. Create a <code>voipmonitor-migrate.conf</code> file by copying the old <code>voipmonitor.conf</code> 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.
<kroki lang="plantuml">
@startuml
skinparam shadowing false
skinparam defaultFontName Arial


== Comparison: Dump/Restore vs Online Migration ==
rectangle "OLD Server" {
| Aspect | Dump/Restore | Online Migration |
  rectangle "Sniffer" as SNIFF
| :--- | :--- | :--- |
  database "Source DB" as SRC
| 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 ==
rectangle "NEW Server" {
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.
  rectangle "Migration\nInstance" as MIG
  database "Destination DB" as DST
}


=== Creating the Migration Configuration File ===
SNIFF -down-> SRC : writes CDRs
MIG -left-> SRC : reads\n(database_backup_from_*)
MIG -down-> DST : writes\n(mysql*)


The migration configuration file should be named consistently to avoid confusion (e.g., <code>voipmonitor-migrate.conf</code>).
note bottom of MIG
  No packet capture
  managerport = 5030
end note
@enduml
</kroki>


**Important: Start With a Copy**
=== Online GUI Migration Workflow ===
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.
 
# 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">
<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 ===
<syntaxhighlight lang="ini">
# 1. Avoid port conflict with production sniffer
managerport = 5030


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:
# 2. DISABLE packet capture (comment out all interface lines)
# interface = eth0


<pre>
# 3. Destination Database (NEW server - local)
# --- 1. CRITICAL: Change managerport to avoid conflicts ---
mysqlhost      = 127.0.0.1
# The original sniffer typically uses managerport = 5029
mysqldb        = voipmonitor
managerport = 5030 # Use a different port
mysqlusername  = root
mysqlpassword = new_password


# --- 2. CRITICAL: Disable all packet capture bindings ---
# 4. Source Database (OLD server - remote)
# These prevent the migration instance from competing for network interfaces
database_backup_from_mysqlhost    = 192.168.0.1
# Comment out or delete ALL bind* options:
database_backup_from_mysqldb      = voipmonitor
# bind = eth0          # DISABLE - do not capture packets
database_backup_from_mysqlusername = root
# bind = eth1          # DISABLE - do not capture packets
database_backup_from_mysqlpassword = old_password
# bind = any          # DISABLE - do not capture packets
# bindchannelfilter    # DISABLE - do not capture packets


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


# --- 4. Source Database (where to read data from) ---
# 6. Performance tuning
# These parameters are ADDED to enable replication from the old database
database_backup_insert_threads = 3  # Higher = faster but more load
database_backup_from_mysqlhost  = 192.168.0.1  # IP of the OLD server
database_backup_pause          = 3  # Higher = less load on source DB
database_backup_from_mysqldb    = voipmonitor
</syntaxhighlight>
database_backup_from_mysqlusername = root
 
database_backup_from_mysqlpassword = old_db_password
'''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>
 
== Migrating PCAP Files ==
 
Database migration does NOT include PCAP files. Migrate separately using rsync:


# --- 5. Replication Control ---
<syntaxhighlight lang="bash">
# Choose the date from which replication should begin
rsync -avz root@old-server:/var/spool/voipmonitor/ /var/spool/voipmonitor/
database_backup_from_date        = 2024-01-01
</syntaxhighlight>


# Performance tuning for replication speed
{{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.}}
database_backup_insert_threads  = 3
database_backup_pause            = 3
</pre>


=== Why These Changes Are Required ===
<syntaxhighlight lang="bash">
# Check timezone
timedatectl


*'''managerport''': VoIPmonitor uses this port for internal communication. If both instances use the same port, they will conflict.
# Set timezone
*'''bind* options''': The migration instance should NOT capture packets - its only purpose is database replication. Disabling packet capture prevents:
timedatectl set-timezone Europe/Prague
** CPU competition with the production sniffer
</syntaxhighlight>
** Duplicate packets being processed by both instances
** Network interface conflicts


=== GUI Configuration Backup ===
== Schema Compatibility ==


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.
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


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


Once your configuration file is ready, you can start the sensor in this special mode.
{{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.}}


=== 1. Manual Test Run ===
== See Also ==
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-migrate.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 ===
* [[Mysql_master-master_replication_hints|Master-Master Replication]]
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_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.
* [[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 (also known as "database backup mode"). This is particularly useful for online GUI migration with minimal downtime, creating read-only replicas, hot-standby backups, or disaster recovery. The guide provides a detailed example of a `voipmonitor.conf` file configured for replication, distinguishing between destination database settings (`mysqlhost`, etc.) and source database settings (`database_backup_from_mysqlhost`, etc.). It explains `database_backup_from_date` to control the sync starting point and `database_backup_insert_threads` for performance. Finally, it provides command-line instructions for running the replication instance, comparing dump/restore vs online migration methods.
 
'''Keywords:''' database replication, backup, mysql backup, mariadb replication, replica, read-only, standby, disaster recovery, GUI migration, migrate database, online migration, zero downtime migration, database_backup_from_date, database_backup_from_mysqlhost, voipmonitor.conf, high availability
'''Summary:''' VoIPmonitor supports two methods for CDR database redundancy: (1) Database Backup Mode — a dedicated migration instance reads CDRs from source DB and writes to destination using database_backup_from_* parameters, recommended for online server migration; (2) Master-Master MySQL replication for full HA. GUI settings migrate separately via Backup & Restore. PCAP files require separate rsync migration with matching timezones.
 
'''Keywords:''' database backup, migration, redundant database, CDR replication, database_backup_from_mysqlhost, database_backup_from_date, database_backup_insert_threads, database_backup_pause, voipmonitor-migrate.conf, PCAP migration, rsync, timezone, master-master replication, high availability
 
'''Key Questions:'''
'''Key Questions:'''
* How can I migrate the GUI database with minimal downtime?
* How to migrate VoIPmonitor database to a new server?
* How to migrate the database during GUI migration?
* What are the database_backup_from parameters for database migration?
* What are the two methods for migrating the VoIPmonitor database?
* How to move old CDR records to another server?
* How can I create a real-time backup of my VoIPmonitor database?
* How to migrate PCAP files to a new server?
* How do I replicate the VoIPmonitor database to another server?
* What is the difference between database backup mode and master-master replication?
* What is the "database backup mode" for the sniffer?
* How to migrate GUI settings between servers?
* What configuration is needed to mirror the VoIPmonitor database?
* Can I use VoIPmonitor to create a read-only replica of my database for reporting?
* How does the `database_backup_from_date` parameter work?
* What is the difference between dump/restore and online migration for GUI migration?

Latest revision as of 00:13, 11 February 2026


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

Overview

VoIPmonitor offers the following 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)

ℹ️ Note: Both 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

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 supports two methods for CDR database redundancy: (1) Database Backup Mode — a dedicated migration instance reads CDRs from source DB and writes to destination using database_backup_from_* parameters, recommended for online server migration; (2) Master-Master MySQL replication for full HA. GUI settings migrate separately via Backup & Restore. PCAP files require separate rsync migration with matching timezones.

Keywords: database backup, migration, redundant database, CDR replication, database_backup_from_mysqlhost, database_backup_from_date, database_backup_insert_threads, database_backup_pause, voipmonitor-migrate.conf, PCAP migration, rsync, timezone, master-master replication, high availability

Key Questions:

  • How to migrate VoIPmonitor database to a new server?
  • What are the database_backup_from parameters for database migration?
  • How to move old CDR records to another server?
  • How to migrate PCAP files to a new server?
  • What is the difference between database backup mode and master-master replication?
  • How to migrate GUI settings between servers?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Redundant_database&oldid=11236"