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'.

Niek van der Steen
  • 101
  • 2
  • Use [Galera](https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/). – Nikita Kipriyanov Nov 17 '22 at 15:53
  • You can open SSH tunnels from master to slaves by using AutoSSH, and then have it do remote port forwarding. – Tero Kilkanen Nov 17 '22 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](https://dba.stackexchange.com/) or ask moderators to move this question there. – Nikita Kipriyanov Nov 18 '22 at 03:47

1 Answers1

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.

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • 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. – Nikita Kipriyanov Nov 17 '22 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 '22 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. – Nikita Kipriyanov Nov 18 '22 at 03: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 '22 at 06:58