How To - Automatic Backup and Restore MySQL db from Web Server to Local Machine

Question

I have a web app that is supposed to be run on a single local machine (kiosk display), but I want the option to let a user make changes on the web (from any PC) and update the instance on their local PC (kiosk display). From what i imagine, the MySQL instance on the web server will just replace the MySQL instance on the local (kiosk display) machine. This can be done once nightly (doesnt have to be instantaneous).

Does anyone know of a way to do this? I am open to a product or coding it myself...

I will appreciate any info or brainstorm.

Thanks.

Answer 1

If the clients have fixed IPs, you can do this:

• Create mySQL user accounts on server, allow them to log in from a specific IP range only, give them SELECT privileges

• Install mysql client binaries on client PC

• Use cron or the Windows Task Scheduler to do a remote mysqldump:

  mysqldump -u username -h remote.server.com databasename > dump.txt
  mysql -u local_username -h localhost databasename < dump.txt
  

this is not a perfect solution - the usual consensus is to have mySQL not accessible from the outside at all. But if you have fixed IPs, and can also maybe adjust the firewall to adjust mySQL connections from those IPs only, it's surely the easiest solution.

A safer solution might be:

• Use a server side scripting language to build database dump locally and store it in a known directory

• Install wget binaries on client side

• Have the client side wget call the server side script (e.g. as a web page) that creates the dump

• When the page has finished downloading (= the dump is done) start another wget, FTPing into the server (with very limited rights of course) and fetching the data dump just built - or, for security, use a safe protocol like sftp or scp/ssh. Putty has Windows binaries to that end.