Mysql – Using MySQL replication to enable a read-only copy of a database in a DMZ

databaseMySQLmysql-replicationreplicationSecurity

I need to set up a MySQL database instance in a DMZ that is a read-only copy of the live master inside a secure network.

MySQL replication seems ideal for this, except that it works by the slave "pulling" changes from the master. This implies that the slave, in the DMZ, has to be able to open a connection to the master, which the security folk won't allow.

I have played with setting up ssh tunnels from the master, but the slave seems to hold the connection open (even after calling "SLAVE STOP"), meaning the tunnel is always open (somewhat defeating the security restriction).

Is there some way of forcing mysql to drop the connection, short of stopping mysqld?

Is there another way of achieving the same goal I haven't thought of?

Updates from master to slave need to be near-real time (i.e. a couple of minutes latency is acceptable, but not more).

Best Answer

Another idea, a slight variation on what you have described:

Setup tunnel from master
Start slave from master (note binlog position of master)
Run slave until binlog position of master is met or exceeded
Stop slave
Close tunnel
Repeat every x minutes

The key here is that the tunnel is created and destroyed by the master, updates are run periodically.

Related Solutions

MySQL – How to Replicate Only One Database

It's not that all db.table INSERTs won't work, it's that cross-database ones won't (see the example in the doc page you link to). Your replication probably works because you're not doing any cross-database INSERTs; most people don't.

replicate-wild-do-table won't change anything for this purpose.

Mysql – Which binlog format to use for MySQL Replication

Statement-based replication is the fastest and most compact, but in some circumstances it can produce different (non-deterministic) results on slaves than on the master, resulting in inconsistency. An example might be:

UPDATE mytable SET a = a + 1 LIMIT 1;

There's no way to guarantee which row will get updated as there is no sort order on it and order on disk is not predictable or consistent.

Row based replication avoids this problem by replicating the changed data rather than the queries, but whereas a statement like:

UPDATE mytable SET a = a + 1;

requires replicating just a few bytes for statement-based replication no matter how many rows it affects: if it updates 1 million rows, row-based replication will replicate all 1 million rows, which will be much slower and create much bigger binary logs.

Mixed mode switches between the two, using whichever is most efficient or safe (for example, simple inserts are probably best done by row-based replication - using statements may be slower). The opportunity for problems comes in recognising which statements are non-deterministic, which is a non-trivial problem.

In summary:

Row-based: always safe, possibly very slow and inefficient in time and space
Statement-based: not always safe, but may be much faster
Mixed-mode: best of both worlds in theory, but could possibly get it wrong resulting in either slow performance, or wrong data depending on which way it gets it wrong!

Official docs are here.

This is also an old question: you should be using MySQL 5.5 for any new builds now. I prefer Percona's builds.

Best Answer

Related Solutions

MySQL – How to Replicate Only One Database

Mysql – Which binlog format to use for MySQL Replication

Related Topic