mon Posted July 12, 2007 Share Posted July 12, 2007 we need to synchronize the data in a mysql database running on ubuntu / apache2 / php5 with the data from an old windows 2000 / iis / access database hosted away from our office. the update should happen at least once a day. there are many ways to do this task - we would prefer something with cron and a php script (have tryed some tinkering already) but we would be interested to hear anyones opinion on what the best method is. any help or suggestions are appreciated. Quote Link to comment Share on other sites More sharing options...
Fearpig Posted July 12, 2007 Share Posted July 12, 2007 Assuming that you have some sort of VPN secure tunnel between the two servers I would probably go for just a straight query sync of the data and not even use php... ...just a suggestion! Before I get jumped on by the PHP addicts! Quote Link to comment Share on other sites More sharing options...
mon Posted July 12, 2007 Author Share Posted July 12, 2007 we dont have vpn - just straight old school http and ftp access. we can probably download the access db file from the asp server using an automated script though.. Quote Link to comment Share on other sites More sharing options...
Fearpig Posted July 13, 2007 Share Posted July 13, 2007 Right then.... Why not put a copy of the access database onto your 'ubuntu / apache2 / php5' server and synchronise that. Then schedule an ftp batch file to connect and download a copy of the access file. To ensure you had all of the data on both databases you would have to do an upload and download.... Ftp Upload - Sync - Ftp Download .... I wouldn't have a clue where to start with php though! Quote Link to comment Share on other sites More sharing options...
MadTechie Posted July 13, 2007 Share Posted July 13, 2007 really depends on the database setup.. do you have a DB schema? Quote Link to comment Share on other sites More sharing options...
mon Posted July 13, 2007 Author Share Posted July 13, 2007 so far we've tried two routes: 1) we tried to write a script to schedule in cron that uses wget to pull the access mdb file off the asp server and copy it to our linux server. then we try to import all the data - replacing our existing data with fresh data - using kexi, but kexi gives a structure error (1170) and unfortunately we cant change the structure of the mdb to get rid of this error. (maybe someone knows of another tool we could use to import the db). 2) the second route we've tried involved writing an asp script to output all the data as sql statements in a dump file. we then wget or lynx or whatever the asp script remotely to trigger the write. we download the script and run all the sql statements on our local mysql to update all the data. we had to abandon this route because of a permission problem on the asp server which means we can't write the dump file. (it worked on my local machine). at this point we thought it would be a good idea to get a second opinion instead of blindly stumbling around like n00bs. Fearpig - we dont need to upload anything, just get the data from the asp box onto the linux box. how would you do the port once you've downloaded the access db? MadTechie - we only really need one table which should make things simple - nothing exciting - just your basic auto enc id field then a bunch of text & boolean columns. thanks for your help peeps. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted July 13, 2007 Share Posted July 13, 2007 We run a similar operation where I work. Our clients are able to sync the data from our server software, which is Visual Foxpro and uses dbase, to our MySQL software hosted elsewhere. We built a routine into our Foxpro product that builds a series of DBF files to export, zips them up, and then places them into a client directory on the server where the MySQL DB is located. We have a cron script that runs and looks for new data from our clients and then imports the data. Your #2 approach is closest to what I would do in the ideal world. Which would be to create a script / program on the server with the originating DB that creates a .sql file, zips it, sends it to the destination server, and then just executes mysql from the shell and redirects stdio to come from the .sql file. You already said you encountered a permission error, I'd look into resolving that. Another thing to consider is the sensitivity of this information. As a last note, I wasn't working at my current job when they devised the scheme of importing data from our Foxpro product to the Web product. Depending on what you're doing, it becomes very difficult to keep the same data in sync in two places, especially if it can be modified in either place. The best scenario is one in which you only import from one DB into another a single time and then use the new DB from that point forward. No one from a business stand-point wants to do that because it means abandoning a product for one that may not have as many features. But IMO, the time spent dealing with database sync issues could be better spent developing the new product. But I didn't major in business, so what do I know. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.