Redundant database: Difference between revisions

From VoIPmonitor.org
(Created page with "Since sniffer 10 the voipmonitor has feature which mirrors data from some remote sniffer database to another database. It replicates all partitions from certain date increasin...")
 
(Fix AI Summary format: use bold labels instead of sub-headings)
 
(22 intermediate revisions by 2 users not shown)
Line 1: Line 1:
Since sniffer 10 the voipmonitor has feature which mirrors data from some remote sniffer database to another database. It replicates all partitions from certain date increasingly syncing data to secondary database. You can have unlimited number of slave databases just for offloading or for backup purpose. Here is example of the configuration which will initiate full backup from 2014-06-10 from mysql server on 192.168.0.1 to localhost database creating the whole schema.
{{DISPLAYTITLE:Database Redundancy and Migration}}
[[Category:Configuration]]
[[Category:High Availability]]


[general]
This guide covers methods for replicating, synchronizing, and migrating the VoIPmonitor CDR database between instances.
sqldriver = mysql
 
mysqlcompress = yes
== Overview ==
mysqlhost = 127.0.0.1
 
mysqldb = voipmonitor
VoIPmonitor offers the following methods for CDR synchronization:
  cdr_partition = yes
 
  mysqltable = cdr
{| class="wikitable"
mysqlusername = root
|-
mysqlpassword = neupk,2
! Method !! Description !! Use Case
database_backup_from_date = 2014-06-10
|-
database_backup_from_mysqlhost = 192.168.0.1
| '''Database Backup Mode''' || Dedicated sniffer reads from source DB, writes to destination DB || Online migration, read-only replicas, hot-standby
database_backup_from_mysqldb = voipmonitor
|-
database_backup_from_mysqlusername = root
| '''Master-Master Replication''' || Bidirectional MySQL/MariaDB replication || Full HA with automatic failover (see [[Mysql_master-master_replication_hints|Master-Master Replication]])
database_backup_from_mysqlpassword = pass
|}
database_backup_pause = 3
 
database_backup_insert_threads = 3
{{Note|Both methods synchronize CDR data only. GUI settings (users, alerts, capture rules) must be migrated separately via '''Tools → Backup & Restore'''.}}
cleandatabase = 70
 
cdr_sipport = yes
== Database Backup Mode ==
cdr_rtpport = yes
 
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 ===
 
<kroki lang="plantuml">
@startuml
skinparam shadowing false
skinparam defaultFontName Arial
 
rectangle "OLD Server" {
  rectangle "Sniffer" as SNIFF
  database "Source DB" as SRC
}
 
rectangle "NEW Server" {
  rectangle "Migration\nInstance" as MIG
  database "Destination DB" as DST
}
 
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_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>
 
== 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.}}
 
<syntaxhighlight lang="bash">
# Check timezone
timedatectl
 
# Set timezone
timedatectl set-timezone Europe/Prague
</syntaxhighlight>
 
== 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:'''
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
tail -f /var/log/voipmonitor/voipmonitor.log | grep -E "CLI|ALTER|CREATE TABLE"
</syntaxhighlight>
 
{{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.}}
 
== See Also ==
 
* [[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 ==
 
'''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?

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"