Mysql – Is it possible to have MySQL slave outside firewall

MySQLreplication

For various reasons I need one of the databases from intranet MySQL server to be made available to (insecure) online server. Due to the requirements it was decided that a db dump will be used via cron to transfer data as the firewall does not allow any connections to be initiated from outside. Then MySQL Master-Slave solution was suggested. From what I have understood, the slave should be able to connect to the master via (preferably insecure) TCP protocol. Such a solution is preferable, however, via secure TCP connections without permitting the slave to initiate (possibly lost) connections to the master.

I would like to know if it is possible to have Master-Slave replication via secure TCP where connections are always initiated by the Master to the slave. The use case does not require too much data transfer, and data base size is within 5GB on a 1GBPS intranet. It is acceptable that there could be some delay in updating to the slave.

Best Answer

Rather than exposing your database's ports to the public network at all, I would recommend arranging for the services to be able to see each other by more secure means.

The SSH tunnel option already mentioned will work, though for reliability you'd want to look into making sure that the tunnel remained active after a network blip or other event (look into autossh, or hooking your SSH tunnel into init/systemd).

I would instead recommend a "proper" VNP solution. OpenVPN is common (available in just about all Linux distributions' standard repositories) and easy enough to setup (IMO), though there are many other options. If you setup a point-to-point VPN between the two hosts (or a more general arrangement if more machines need to take part) then the database instances will be able to connect to each other securely over that without needing to be exposed to the outside world.

Related Solutions

MySQL Replication – Master and Slave Same Server-ID Issue

The server_id of the Master is recorded in its binary logs along with each query it executed to completion. To see those server_id values in the binary log, you run mysqlbinlog against any binary log.

Since Master -> Slave_A works fine, here is what you can do to clean things up between Slave_A and Slave_B:

On Slave_A, run the following in mysql:

mysql> STOP SLAVE;
mysql> RESET MASTER;

On Slave_A, run the following in the OS:

$ echo "STOP SLAVE;" > /root/MyData.sql
$ mysqldump -u... -p... --master-data=1 --routines --triggers --all-databases >> /root/MyData.sql
$ echo "START SLAVE;" >> /root/MyData.sql
$ mysql -hSlave_B -u... -p... -A < /root/MyData.sql

On Slave_A, run the following in mysql:

mysql> START SLAVE;

This will get Slave_B to stare at the correct values for server_id, and also reestablish MySQL Replication.

BTW You need to make sure binary logging is disabled on Slave_B since it is not necessary for a Slave unless Slave_B is going to a Master to some other Slave.

PostgreSQL failover and replication

slaves wont understand new master. you should manually do that.
yes they are different and you should create new ones for old master.however old standby will continue to work as a master but you should set max_wal_senders on that node. you should also set pg_hba.conf of the new master after failover. after failover (when nodes changes roles master->slave slave->master), you should transfer new wal files to new standby folders data directory which you set in recovery.conf file. or simply you can use rsync.
may be you can use pgbouncer. this way you will just change the pgbouncer server adres to new master.
EnterpriseDB has some commercial tools. may be you can check them.

and finally yes you are right. there is no single fully automatic solution to solve these questions.

Best Answer

Related Solutions

MySQL Replication – Master and Slave Same Server-ID Issue

PostgreSQL failover and replication

Related Question