Our scenario is a local Linux MySQL server on which we can do whatever we want including installing stuff and setting up cronjobs, and a remote one which we can access only via PHP. The remote one is the usual LAMP hosting service so we can upload whatever PHP we want but only the php scripts themselves from localhost will be able to connect to MySQL, we can't directly connect to the db from outside. There is FTP to upload and manage PHP files.

The local server runs some business software, and we wrote scripts to get some data from that software. We need to load that data up in some dedicated tables on the website MySQL hosted somewhere else.

For testing purposes we exported the local db (using a local phpMyAdmin) and imported it on the remote system (using the remote phpMyAdmin), of course it works fine. Now we need to automatize this.

Our "synchronization" is actually a full erase of the remote end and then we re-import everything from the local db. This is gonna happen every night.

The first idea is a local cron job that exports via mysqldump, uploads via FTP, wget's a remote php file URL that (after some credentials checking) finally runs the uploaded SQL. Sort of blind, stupid script... should work well as long as I keep strangers outside of the SQL upload / SQL import area.

Rather than re-inventing the wheel, is something out there that could achieve the same result, maybe in some more intelligent way that I'm not thinking of?



I can't think of a much better way other than potentially skipping the ftp part. Instead just using a straight POST using wget (though I'd check to see what happens if the POST fails i.e. data is too large for the PHP min upload size). This would skip some of the complexity from the script as you are just doing mysqldump followed by an wget auth then POST (unless I've missed something).

However, the more important question is why are you using a host that only allows PHP access? You already have a dev system working locally so you know how to handle the admin side of things. You really should be keeping production and development environments as close as possible to avoid the annoying niggles that arise from troubleshooting disparate systems (not to mention avoiding this particular admin hassle).

My VDS provider do a hosting package for £48 for the year which gives 1GB of space, 100GB traffic and a database (this is inc. VAT so knock more off that price if you can claim it back). And you get full shell access. I'm sure there are loads of hosting providers like this as well so why hamper future development? It will only kick you in the arse further down the line.

  • The local and remote systems are doing two different things, business software here and website there, that's why they are completely different systems. We could get a fully managed system for the website but we think it's not worth it just to move tables around without passing through php. We prefer to leave the web server management to the current hoster. I tried to clarify this differences in my question. – Luke404 Mar 15 '12 at 17:54
  • And re your upload-via-POST tip, I'm gonna explore that for sure. It sounds like a good idea, we know what the exact php configuration is going to be (no upload size issues) and the dataset is really small - some thousand rows with a bunch of columns. – Luke404 Mar 15 '12 at 17:55
  • Aha then yes, that sounds like a plan. Sorry if my answer came across as me preaching to the choir. – webtoe Mar 16 '12 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.