0

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?

Replication

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

3
  • Use Galera. Nov 17, 2022 at 15:53
  • You can open SSH tunnels from master to slaves by using AutoSSH, and then have it do remote port forwarding. Nov 17, 2022 at 16:15
  • 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 or ask moderators to move this question there. Nov 18, 2022 at 3:47

1 Answer 1

0

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
  • 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. Nov 17, 2022 at 18:35
  • @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.
    – Rick James
    Nov 17, 2022 at 22:21
  • 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. Nov 18, 2022 at 3:44
  • @NikitaKipriyanov - An async Replica can hang off one of the Galera nodes; it can be delayed and capture all the data in the cluster
    – Rick James
    Nov 18, 2022 at 6:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .