How to synchronize local MySQL (full access) to remote one (PHP only)

Question

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?

thanks.

Answer 1

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.