大网数据广告活动
服务器运维问答

Best way to keep two or more databases in sync?

· 52CE 我爱测

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…

问题原文

原帖链接 · Donno · CC BY-SA 3.0

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.

已采纳解答

原帖链接 · Donno · CC BY-SA 3.0

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

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-08-14)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。