2

I have two PowerDNS servers and have configured the zone records to be natively stored in mariaDB. What would be the best way to sync these two databases?

Note :

I would like to scale this to use 5 servers later on. Currently the data would be added to DNS server 1 and would only be copied from DNS server 1 to DNS server 2. If I have more DNS servers, I would like it if DNS server 1 goes down, then content created on DNS server 2 would serve DNS server 3 and so on and when DNS 1 is up again it would sync with DNS server 2.

There is also one table which will not be synced: that is the table that PowerDNS uses to store the server IP address and so on.

My server can only access mariaDB locally so for a remote computer to access mariaDB it would have to ssh tunnel.

Kevin Bowen
  • 111
  • 11
Donno
  • 31
  • 1
  • 6
  • How much latency will you have between the servers (worst link)? What's your read/write distribution? Distributed ACID compliance is very tricky business, and shouldn't be taken lightly. – Brennen Smith Aug 14 '17 at 17:12

4 Answers4

2

Since you are asking this on SF and not on database I will give you an answer that does not replicate the database.

You want to replicate a DNS.

The official mean for that is either a peer DNS or a secondary DNS that might get promoted to primary.

In any case your main DNS has to push changes towards the peer/secondary.

That is done with DNS protocol on layer 7. So no need to replicate databases.

Nils
  • 7,695
  • 3
  • 34
  • 73
  • With powerdns using mariadb as a backend it is easier to use db replication (native in pdns terminology) rather than using notify (rfc1996) and zone transfers. The correct terminology for the later is master / slave and not "peer" or "secondary" and there is no promotion. – Mark Wagner Aug 23 '17 at 17:27
1

The easiest way after reviewing the answers and googling the problem was to make a mariaDB Cluster with rsync

Ubuntu 16.04.03 using MariaDB 10.2.8 setup Steps :

sudo apt-get install curl
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
sudo apt-get update
sudo apt-get install mariadb-server
sudo apt-get install rsync
sudo systemctl start rsync.service
sudo systemctl enable rsync.service
systemctl list-unit-files |grep rsync

on the first node edit /etc/mysql/conf.d/galera.cnf

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="[Cluster name]"
wsrep_cluster_address="gcomm://[node1 ip],[node2 ip]"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="[node1 ip]"
wsrep_node_name="[node1 name]"

Stop MariaDB and start first node on cluster

    systemctl stop mysql.service
    galera_new_cluster
    systemctl status mysql.service
    mysql -u root -p -e "show status like 'wsrep_cluster_size'"

        +--------------------+-------+
        | Variable_name      | Value |
        +--------------------+-------+
        | wsrep_cluster_size | 1     |
        +--------------------+-------+  

on the second node edit /etc/mysql/conf.d/galera.cnf

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="[Cluster name]"
wsrep_cluster_address="gcomm://[node1 ip],[node2 ip]"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="[node2 ip]"
wsrep_node_name="[node2 name]"

Stop MariaDB and start second node on cluster

    systemctl stop mysql.service
    systemctl start mysql.service
    systemctl status mysql.service
    mysql -u root -p -e "show status like 'wsrep_cluster_size'"

        +--------------------+-------+
        | Variable_name      | Value |
        +--------------------+-------+
        | wsrep_cluster_size | 2     |
        +--------------------+-------+

To add more nodes to the cluster just edit /etc/mysql/conf.d/galera.cnf like this :

....
wsrep_cluster_address="gcomm://[node1 ip],[node2 ip],[node3 ip],[node4 ip],[node5 ip]"
....
Donno
  • 31
  • 1
  • 6
0

You can try percona xtraDB
I'm actually using it to sync databases from an open-xchange server

https://www.percona.com/software/mysql-database/percona-xtradb-cluster

0

Use mariadb replication. Don't overthink it. "MySQL [mariadb] replication has proven to be very robust and well suited, even over transatlantic connections between badly peering ISPs. Other PowerDNS users employ Oracle replication which also works very well." https://doc.powerdns.com/md/authoritative/modes-of-operation/

Mark Wagner
  • 18,019
  • 2
  • 32
  • 47