Score:0

Server

MariaDB Master-Master replication with 3 servers, no ports open on master

Niek van der Steen

11/17/23, 3:47 PM

I want three MariaDB database servers, one master and two "slaves". I want to be able to read and write on every server, and have this replicated on the other servers. I read on the internet that this is called "master-master replication".

Now I do not want to open any ports on my 'real' master server, and only on the two 'slave' instances. Is it possible to have the 'real master' open the sockets, so I don't have to open the ports for incoming traffic on the first server?

Edit: I have multiple databases on the 'master' server, and only want to replicate some of these databases to the 'slaves'.

217

1 + 3

mysql

replication

master-slave

mariadb

master-master

Nikita Kipriyanov

11/17/23, 3:53 PM

Use [Galera](https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/).

Tero Kilkanen

11/17/23, 4:15 PM

You can open SSH tunnels from master to slaves by using AutoSSH, and then have it do remote port forwarding.

Nikita Kipriyanov

11/18/23, 3:47 AM

With your additional requirement (replicate only subset of databases), you're limiting yourself into solutions like trigger-based replication. Consider splitting your setup in two parts, a replicated part, which will be handled by Galera, and non-replicated part, deployed as e.g. distinct OS service instances or distinct Docker containers. You can also try asking on [Database Administrators StackExchange](https://dba.stackexchange.com/) or ask moderators to move this question there.

Score:0

Server

Rick James

11/17/23, 5:52 PM

write on every server, and have this replicated on the other servers

To do that you need "circular" replication among all 3 servers. I do not recommend it. If one server goes down, it is a nightmare to get them back in sync.

OTOH, Galera clustering (available in MariaDB) provides that as a standard and takes care of resyncing. I strongly recommend abandoning what you have started and set up Galera.

As for firewalls, tunneling, reverse ssh, etc, you have not provided enough info about the systems. (Note that the Replicas "pull" from the Primary, not the other way around.) In the case of Galera, a few ports need to be opened on all servers.

+ 4

Nikita Kipriyanov

11/17/23, 6:35 PM

One thing must be noted for the Galera that it doesn't have a "true master". All servers are equal in Galera, all of them are masters. It is possible to restrict access to some of them (allow only other servers in the cluster for replication, quorum and SST, and don't allow any clients), but it is completely pointless, as other servers have the same access to the same data and the same grant tables, so clients will be able to steal or corrupt data in the same way if compromised. The database security isn't done this way, at least, not in MariaDB.

Rick James

11/17/23, 10:21 PM

@NikitaKipriyanov - Please move that requirement into the Question. (I, for one, failed to understand your real goal.) See also "delayed replication" -- as a way to recover from a malicious action on the Master.

Nikita Kipriyanov

11/18/23, 3:44 AM

I am not the one who asked the question. I don't fully understand the original goal too, but I see some discrepancy in the answer with the requirement describe in the questions (to "hide" a master), so I wrote a comment about that. By the way, I doubt the "delayed replication" is at all possible with any multi-master setup (another requirement), and it is certainly impossible with Galera.

Rick James

11/18/23, 6:58 AM

@NikitaKipriyanov - An async Replica can hang off one of the Galera nodes; it can be delayed and capture all the data in the cluster

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: MariaDB Master-Master replication with 3 servers, no ports open on master

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.