2

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.

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • local backups are a given. I am trying to get a handle on whether MySQL replication routines have any built in mitigation for interruptions - I feel like you would have mentioned that if it were an applicable case. packet loss isn't a constant, but springs up similar to outages (like brownouts vs. blackouts).
    – TST
    Mar 31, 2016 at 15:56
  • I would not say so (not using MySql though). This is a highly unusual setup (due to like seriously impaired internet quality).
    – TomTom
    Mar 31, 2016 at 16:01
  • okay - that was a partial edit on my part as I hit return partway through and didn't re-edit fast enough. missing was - local backups are a given. this was just in hopes of making replication to a read-only database for reporting and query use more timely. Yeah, I get the networking viewpoint. The reason I asked DBA is because the network is just a fact of life - the resources don't exist to improve it significantly. The distance is about 50 mountainous miles, and there's no point of high quality closer or more easily sighted.
    – TST
    Mar 31, 2016 at 16:08
  • Yeah, but this is normally not something dba's deal with - network is networking. I personally thinkg I would have to go with something like Windows DFS replication - their replication handles interruptions VERY well. Whatever you choose for VPN will matter a lot. That is heavy network administration. I can not say I envy you for this situation.
    – TomTom
    Mar 31, 2016 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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