I need to setup a MySQL database server for a small number of local (write) users (~6) in a remote area with somewhat irregular internet access via WiMAX. Outages are not at predictable times or of predictable duration. I will have another small number of users (only really need read access) at a central office and…
I would like to know what backup/replication options are amenable to this scenario?
My fallback seems to be taking a nightly dump and running rsync as a cron job to sync it to local NAS + remote storage (central office + S3).
I'd prefer doing either replication or log-shipping to a remote server at the central office for r/o query users + dumps w/rsync to local NAS + remote S3 backup. But I'm unsure of the effects of extended network outages (hours to day) and/or significant packet loss.
Best Answer
Generally this is not a dba question as much as it turns into a networking issue. You need to decide what you want / what is more important to you: Having backups or having them centrally.
I generally would go for the first - make a backup locally, then move it offline for serious problems coming. This is particularly important given that you have no reliable internet - so unless you do this, you may end up not having backup done at all because internet is bad.
Now, for packet loss - there is not a lot you can do and it kills data transfers. It also generally is a sign of a very bad internet connection. Wimax generally is not exactly an excuse here as much as it is a symptom - a not really widely used technology with better alternatives existing. You can use a relatively cheap point to point wlan link over many km with much higher speed and - very little packet loss. I am writing this on the receiving end of a 4km WLAN based internet connection and my packet loss on most days (when it is not storming or we have lightning) is neglegible. TO fix the packet loss you must address that from a business point - talking with the ISP and make him maintain his equipment properly. High packet loss WILL kill large transfers with not a lot you can do. It also is the main issue here and - totally not a dba related question, as it affects any kind of data transfer.
And again: Given extended network outages that seem to be normal, I would go for a local backup that then gets copied out. Because this means if your internet is down for 3 days, your backups still happen.