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

Question

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

Answer 1

The slave keeps a permanent connection open to the master, but does close it when you issue a "STOP SLAVE".

If your data are very small and you can tolerate a few minutes' latency, you could potentially script something using the mk-table-sync tool from Maatkit which does the job. It will checksum your tables and figure out what's changed to apply them. You could run this from the master and connect, periodically, to the slave in the DMZ, which would be much safer from a security standpoint - even if an attacker compromised the DMZ machine and replaced it entirely with their own software, they'd still get no more access than a snapshot of the db.

Answer 2

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.

Answer 3

it is ugly but still - if there is not much happening at the master you can run at master periodically [ even once per minute ]:

• FLUSH LOGS; [ i assume you have bin-logging on master. if so - this will close old log file and force master to create new log file ]
• using ssh tunnel or just mysql and binlog command 'reply' log file on slave in dmz

so you'll not need to rely on mysql replication mechanism.

you need to take into account fact that there might be more than one log file created by server in a minute.

Answer 4

I've have to deal with something similar to this in the past. You've got a few options.

1. mysql replication using ssl, certs, and IP restrictions. It's easy to setup and you can lock it down pretty good. You still open a connection to the server, but you do control access tightly. Might not be good enough for the security people.

2. As someone else mentioned, copy the log-bin files to the slave and then use mysqlbinlog to ingest them. I ended up using this to move some new acquisitions into the corp network. However this was a temp measure and we only used it for a month and ran it infrequently. Also you're going to have latency in regards to how soon the data gets to the DMZ server.

3. Use Mysql Proxy to write to both Mysql servers at the same time. http://dev.mysql.com/downloads/mysql-proxy/index.html This might have some interesting side issues if your schema has quirks. Downside, new tool to install and learn as well as being pretty beta. Upside, solves most of your problems.

Answer 5

There are a lot of great morsels here. However, everyone is over complicating different pieces of the puzzle.

mroe said:
...the slave seems to hold the connection open (even after calling "SLAVE STOP"), meaning the tunnel is always open...

1. It's not the slave mysqld's role to terminate an incoming SSH session. (That's closer to the way tunneling with netcat works, but this is not the place for that so let's not confuse the issue.) The master should terminate the connection because [a] it originated it [b] it is the one inside the network you wish to protect. If you want terminate the connection for the slave side call "kill -s HUP $pid_of_the_sshd_owned_by_mysqlmaster". I'll say again, I don't see the use.

2. Why would you ever call "SLAVE STOP"? The slave is a slave. Since we're on the topic... The slave should never stop itself. If the slave is going to be stopped, the master server (see point 4.) or the administrator should do it.

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

You can't have it both ways. You either have the slave IO thread running or it is out of date.

3. Don't worry about the 'binlog position of master' that's what built in replication does.

4. If you really have your heart set on disconnecting and reconnecting every minute, write a cron script on the master. It should do the following:

• Test a lock file and either exit or create it.
• Create your tunnel like so ssh -N -R 3333:127.0.0.1:3306 mysqlmaster@slave.server &
• mysql -hslave.server -e 'SLAVE START'
• Now you have to make a choice:
  1. Stay connected until the slave is caught up with the master. You can test for '0' in the output of the following (one line, ignore wrapping): mysql -hslave.server -e 'show slave status\G'|awk '/Seconds_Behind_Master/{print $2}'
  2. Stay connected of a certain amount of time. Maybe you would sleep for this. Do research about your system to make sure it doesn't consume any CPU.
• MySQL is handles dropped connections gracefully, so this is optional: mysql -hslave.server -e 'SLAVE STOP' (infact, the SLAVE START is optional if you use MASTER_CONNECT_RETRY)
• kill the pid of the ssh client from the 2nd bullet.

That should do it. Now keep in mind that you have to get the slave ready to replicate before this is going to work. Since you mention issuing a SLAVE STOP, I'll take that to mean that you were able to get a consistent backup from the master to the slave and get the log position for the CHANGE MASTER command. I'll just add that you need to specify MASTER_HOST='127.0.0.1, MASTER_PORT=3333

Obviously I have no problem taking the time to research, and explain in detail. I'm also trying to not insult anyone by explaining too much. If you want more detail, just ask me.