There are several ways to accomplish what you are attempting. Which option is the best would depend on how much data is being moved, how much coding you want to do and possibly your network configuration (can the two machines ftp files to each other? can you see both machines from a third machine using ODBC?).
I haven't done exactly what you are trying to do but I have some ideas of how I would attack the problem.
Idea #1: The online database is live. The replicated database is read-only. Dump the online tables to flat files. Ftp them to the other box. Drop the read-only tables and reload them from the flat files using the LOAD DATA INFILE command (see documentation for examples). This method would be simple to code because you move everything each time, but would stink if you had a lot of data.
Idea #2: Only move the data that has changed. This gets a little more complicated because you have to have a way of telling where you last stopped and where you are stopping this time. If each table had an AutoIncrement number in it, you could create a replication information table that had three fields: TableName, LastMaxNbr, CurrentMaxNbr. To replicate TableX you would:
1. Update the CurrentMaxNbr = max(AutoIncrementNbr) in TableX.
2. Run a "SELECT INTO OUTFILE" command where AutoIncrementNbr BETWEEN LastMaxNbr and CurrentMaxNbr.
3. Update LastMaxNbr = CurrentMaxNbr
4. Move the records over to the read-only machine and upload into tables.
Your replication table would look something like this giving the range of records that needed to be moved:
TableX 415 575
TableY 325 463
TableZ 719 823
Idea #3: ODBC connect to both machines from a third machine (a workstation?), open a read-only cursor on the online machine and execute an insert to the local lan machine for each fetch. This solution suffers from the fact that while servers stay up a lot, workstations (read Windows here) have a tendency to crash or get turned off. Dependability may be a problem. I only mention it because I do a lot of VB / ADO programming and could produce this solution quickly.
Idea #4: If somehow possible, have the application that inserts into the web database, also insert into the lan database (double insertion, double updates, double deletes). I have no clue if this is possible or even a good idea. It would just make replication instantaneous if you could.
