mySQL data sync

  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Does anyone know of a good way to do remote automatic data syncing over port 80 between two mySQL databases?

Here's the specifics...

The development environment is a Win2K server running IIS. It has the Interwoven TeamSite CMS installed on it, which uses an internal Apache/Tomcat system (including Perl-CGI engine). All CMS custom scripts must be written in Perl (not a problem for me. technically, any CGI language... but anyway, I do Perl).

The production environment is a straight IIS system, no CGI, no PHP, only ASP.

BOTH systems have a mySQL server installed. The dev mySQL server is on it's own system, but the production one is not, since window's has the convineient DNS source, code doesn't have to be any different no matter where the database is (assuming you name your DNS the same, of course).

Here's what I'm doing right now.

Part of CMS system is a sophisticated deployment system. It's basically an advanced FTP system (actually... it's a very advanced one). It allows TeamSite to execute CGI scripts before or after a deployment runs.

So, here's the steps:
1) A Perl-CGI script that executes at the start of a deployment process that creates a fake HTTP User Agent and sends an HTTP GET request to an ASP script on the productions system.

2) The ASP script validates the IP and User Agent (some protection against someone running this on their own, not that it would be that big of a deal if they did).

3) This production ASP script then sends a request to another ASP script running on a local public site (in CARE's network, not production which is hosted off-site).

4) The local ASP script queries the database (predefined databases and tables only) and builds an XML object out of the result set (this is a builting ASP function, pretty nice actually) and returns this to the production script.

5) The production script, in response, knows what to do with the XML object, translating it into an ASP RecordSet which then automagically (actually, it's a painstaking process of comparing existing data to the new data... all code I wrote) either INSERTS or UPDATES data in the remote database.

Now... is there an easier way? This process, which relatively fast, is pretty intensive. You can't just copy the table files across, since Windows locks those files while the mySQL service is running, so I'd have to temporarily stop the service, copy over the files, then restart the service. I'm not about to give any script that can be executed remotely the ability to manipulate services... That would be silly.

So... without any special connections (that is, over port 80 or 443 only), can anyone think of a better way to do this?


*personal note...
I've harped many times on how people should use tools suited to the system... funny, you probably think, that I'm using Perl and mySQL on windows after all that. Yes.. it's a bit hypocritical of me, but the thing is, I work for a non-profit, and we can't afford the several thousand dollars, actually, it would be about $20K a year, it takes to get a MS SQL Server for production (we have one on dev, but mirrored environments is important). Our site get a significant amount of traffic (pushing *many* GBs a month in bandwidth), so the lower cost hosters won't do (been there, crushed that, so to speak). The Perl, Apache thing is built in to the CMS and isn't something I can control... TeamSite is the best-of-breed CMS platform, and Interwoven gave it to us for a song... pretty much a no-brainer. (I don't put infrastructures like this together myself... some of it's political, other budgetary... but, it's what I have to work with here...)

Anyway, any help would be appreciated! Just think, you'll be helping to fight global poverty too! =]

.c
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • this213
  • Guru
  • Guru
  • User avatar
  • Posts: 1260
  • Loc: ./

Post 3+ Months Ago

You'd do best to throw the ASP script away that's updating the database and replace it with a perl one. just have the perl script rip out all of the relevant data, compare it internally, then update the DB as needed. Doing this, you can hard code a key into the data and you don't have to even worry with the user agent stuff - though you could keep it if you really wanted to.

The reason I say this is simply because Perl is much faster than ASP on the backend - after all, that's what it was originally designed for. If you absolutely can't get perl installed local to that DB system, or at least to a system that can talk to the DB system, I'd think you've pretty much got about the best you could hope for.

Note however, that the DB system doesn't neccessarily have to be allowing CGI for you to use perl scripts on it. You can have your development server output the data files and FTP/SSH or whatever them to the DB server, then time your DB server scripts to run by the windows scheduler (Start> Programs> Accessories> System Tools> Scheduled Tasks). All the DB server has to have installed for this is perl - and the script won't have internet access at all, you can't get much more secure than that.

If your DB server is on a different host, but one you can set up to allow connections from you, you can write your script locally to just connect up using IO::Socket - but that leaves a door open when you're not using it - both on the development server and the DB server (of course, if the DB server is set up like that now you've already got a door open)

hth
This


*edit*
As an afterthought, I might be able to help you with bandwidth issues. What nonprofit are you working with?
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Wow that is a pretty mean system you got going there!


A COM component on the two systems wouldn't make life easier (Maybe in C++ or seeing that you have windows servers maybe .NET?)

The component would then on a timed interval dump the local copy to file, the remote component would pick up the file a few minutes after it has been dumped and then upload it. Once uploaded it can proceed to update the db?

This way the requests will be only for the file and the components can update the DB when net traffic is low.

Copying tables and stuff is not an option especially if you are using InnoDB type tables (unless you know something I don't)

The above idea is pretty similiar to the system that you have in place, just not using ASP pages to update the file.

In the serial proxy killer app I built, I used transactions to eliminate duplicate keys with the field set to unique.

Don't suppose doing it in batches would help the load issue would it?

Anyways, hope this sparks a genius idea or just reassures you that the way you did it was the best way. There is always VNC lol
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

I used a flat file system at first. Perl DBI would build a flat text file (technically, it was a XML file, but it could have been anything). This file would then deploy out to the production server where it would be imported to the database (much the same was the XML stream works now, I guess, only with a flat file).

That server doesn't have direct FTP access. only port 80,443 and whatever the port is that OpenDeploy uses (20003... 21006... something like that).. All other ports are closed inbound. Then, we use an encrypted VPN connection for all other things (terminal services, FTP, etc). Let me tell you... scripting in rasdial commands is not only a pain, it's very unpredictable. Besides the VPN disconnects the normal internet connection on the dev server, making it inaccessable to users not on the local network (it has a web based interface, and I don't require that my team be here all the time, as long as they are doing their work, and attend scheduled meetings, I don't care where they are).

Anyway, I investigated a .NET solution (both servers are .NET ready), but I was having trouble getting it to work, and I ran across this XML feed object built into ASP... .NET or SOAP maybe, would be better...

Ugh, data syncing sucks.

.c
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

So surely ASP.NET must have the XML object built into it.

Hope this link will help
http://www.google.com/search?hl=en&ie=U ... ed+object+
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

I know it does.. The reciever on production works fine, it's just straight IIS box. The sender side, though, it more complicated. TeamSite thows a wrench in the works to a pretty significant degree... Although, unlike production mySQL is on it's own box (the code stays the same by using Window's DSNs). I have a hardended IIS server on that box too, mostly for reporting. I could perhaps build the sync system on that server instead... There really isn't anything that REQUIRES the system to be on the CMS, really.
...

hmmm
.c
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

I would build it seperate to the CMS, after all it is going to be running a scheduled task exvery x amount of time so the cms doesn't need to be involved[/img][/u]
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Unfortunatly... (this will make you laugh...) the Interwoven's OpenDeploy application doesn't adjust for daylight savings time (side note... daylight savings time is a moronic anachronism that should be done away with...). So, twice a year, I have to go in and reschedule every deployment task (there's only about 12 or so... it's not that big of a deal, it's just annoying).

If I use an AT job on Windows, these will become out of sync, which could cause problems if the files are looking for data that hasn't been deployed, or has been removed by the data deployment.

TeamSite has a system called DataDeploy as well, which I *might* be able to use, although it's not really designed for mySQL (they say it can be customized to work... well... OF COURSE it can... at $150/hr...)

You know what would be nice on Ozzu? A skills matrix of users. A system that lets users enter skills so other users can find people who say they are skilled in an area. Cross reference that with the user's TOS bar (so to speak) to get people that have been around a while... for example, I could search for people with experience in Interwoven products... I don't know, it's just a though.

.c
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Doesn't windows have a switch to daylight savings mode? Thought I had seen that somewhere
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Yes, except that our stats system is daylight savings compliant, but not by profile. Also, our patching system adjusts for it, and will see that the clock on the CMS system is out of sync and do something (not sure if it will change the clock or send an alert about it to the network ops people, and they are ...difficult... to deal with sometimes).

So... the bottom line is, can't use AT jobs for anything related to actual content deployment..

.c
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Damn, this is the only thing that bugs me about the internet - I don't control it! (megalomaniac I know)

Well considering the constraints that you have I think that the ASP option you mentioned, all though intesive, is the safest route to go

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 61 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.