Replicating tables via ssh?

IdahoEv

I have recently developed and installed a database for a small business, running on a Debian cluster in their office.

Now, we want the database running in their office to export some of its' data (4 tables out of 13) to an external shared host webserver, where that information will be used to automate some of their web content. Soon after that, I will need to accept some information at the website and replicate those changes back to the home database. This replication doesn't need to be real time, though that would be nice.

I don't want to replicate the entire database, only four tables, and maybe only certain rows in those tables

The internal database is behind NAT and firewall, only ports 22 and 443 are open. My current plan is to export the relevant tables as SQL files via mysqldump and copy them to the webserver via scp. A cron job on the webserver would look for updated files every few minutes and when changes are discovered, it would reload the web database content from the new files. All the database entries are timestamped, so I could export only recently-changed rows (though this database is small enough that it may not matter).

This solution feels somewhat clunky to me, though, and so I wanted to know if anyone had better suggestions.

Thanks!

bastien

I would create a CRON script on the webserver that uses 443 to connect to your local server and pull the data straight from the db...then parse and load into the webserver

bretticus

If the database is relatively small, why not replicate it? I mean what would it hurt? You would then have current data. You can get the MySQL source for Debian and turn on SSL before recompiling (and making your deb file.) Real-time replication over SSL. Doesn't get any sweeter than that? Unless you can use a VPN tunnel with that firewall appliance (guessing it's an appliance.) Without the VPN tunnel and in spite of the firewall, you could even use an ssh tunnel to the replicate the database.

IdahoEv

<i>why not replicate it? I mean what would it hurt?</i>

Actually, yes. The webserver is nowhere near as secure. I only need four or five of the tables for the web functionality, and some of the data in the other tables is much more sensitive. The four contain information about available products the company wants to automatically advertise on the site. (no online sales, for now). But some of the other tables contain sensitive sales and marketing info, and I'd prefer not to have them on the site just in case the webserver is compromised for whatever reason.

Replication would be great, if it were possible to only replicate some tables.

But then again, I've had a hard time getting replication working at all anyhow.

IdahoEv

bastien wrote:
I would create a CRON script on the webserver that uses 443 to connect to your local server and pull the data straight from the db...then parse and load into the webserver

Hmm... this is intriguing, but I'm not 100% certain what you mean. Like set the DB up to listen to 443, instead of just 3306, and have the cron job on the webserver inititate a remote connection to the internal DB? i'd have to assign another port, Apache's listening on 443...

If I were to do that, I'd probably do it the other way around; have the internal server open up a DB connection to the webserver's mySQL. For security reasons, I'd prefer not to have the webserver contain any information that might assist a remote login to the local server.