Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Migrating KimSufi to new KimSufi with zero mysql downtime?
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Migrating KimSufi to new KimSufi with zero mysql downtime?

Going to upgrade my KS-9 to a KS-17.

It's running LAMP, with debian, apache and mariadb.

I want to migrate to the new server with no more than a minute or two of down time, ideally none.

Now, I can easily get the new server set up and configured, thats fine, but my biggest concern is how I can keep the DB up and running, changes and all, while "switching" over.

the DB is around 10GB at present and theres probably 10-20 updates a second.

You can probably guess at this point, I have zero experience of replication or using mysql remotely.

I found a guide here: https://autoize.com/migrating-mysql-databases-with-no-downtime-for-non-dbas/

but I have a couple of questions:

  1. Can I do this /without/ using SSL? theres no sensitive data in the DB and I'd like to avoid steps relating to certificate generation etc. plus it's probably quicker.
  2. with a master/slave configuration, if i make DB updates on the slave, will they be replicated to the master, or is syncing only one way? Because ideally I'd like to switch the services that are using the DB over one by one, which would mean that changes made to the DB on either server would need to be replicated, until I've finished moving them over and then I can switch off the master/old server.

Any help appreciated.

Comments

  • SSL is not needed. Master-slave is one way (that is why it is called a slave).

    You can set up replication in the other direction independently, so it is like a two-way street. But then you are responsible for not screwing it up by introducing inconsistencies.

    You can also switch off read-only for the slave and start updating it for specific tables while still replicating others from the master. This is living dangerously.

    Thanked by 1Adam1
  • PilzbaumPilzbaum Member
    edited December 2021

    I would setup a wireguard tunnel between the two servers, so 1.) is neglegible (due to wireguards encryption).

    However, afaik, it is not possible to use the slave to update the master (only one way).
    Another possibility would be to setup a master/master cluster (mysql galera cluster for instance). But that would drastically reduce db speed I believe.

    However, how about syncing to the new server via master/slave and then portforward the mysql port on your old KS-9 to the new KS-17 via the tunnel?
    The would just mean a minimal / no downtime, if done correctly, and allows the old applications to operate, so you can switch them one by one.
    I don't know how the slave behaves, if data gets written to it. And you wouldn't be able to set it into read only mode as suggested by the tutorial you sent.
    But I would try it this way

    Thanked by 1Adam1
  • ArirangArirang Member
    edited December 2021
    1. mysql master-master setup.
    2. setup proxy in front of mysql which are active-standby.
    3. change your client mysql connect string to the proxy instead of old server
    4. Shutdown old one.
    Thanked by 2Adam1 t0m
  • jarjar Patron Provider, Top Host, Veteran

    Set low TTL a day or so before, switch the DNS, kill the app on server1 as you move the database to server2 right before activating the app.

    There's something to be said for simplicity. If it's that vital that it can't withstand a few minutes of downtime, for the love of god please don't host it on bargain basement servers 😛

  • @jar said: There's something to be said for simplicity. If it's that vital that it can't withstand a few minutes of downtime, for the love of god please don't host it on bargain basement servers

    so much this. why spend hours of configuring replication stuff on an already running system? other than for educational purpose it's the wrong way of doing it. what do you really lose over some minutes downtime?

    plan and announce (if needed) a proper maintenance windows. shut down old stuff, transfer/migrate, switch back on. as @jar already said, keep in mind dns is something you can't factor in properly anyway, so people might still end up on your old instance because caching/TTL. shutting it down properly is the right way to go.

    if at all I'd plan some proper failover/replication now for the new setup, so you can utilize this next time. but trying to build it on top of the old production system... hmm.
    do backups.

  • AlwaysSkintAlwaysSkint Member
    edited December 2021

    @jar method is what I use for migrating ecommerce, though db updates are much less frequent. Apart from the one minute, or so, downtime (during the switchover) any inconsistencies are down to how quick the browsing (potential) customers get their cache/DNS updated. A "Store Closed - check back soon" on the original site helps though.

  • A 10GB Database hosting an app on that can't afford downtime of even a few minutes... on a Kimsufi? Sorry but I could not swallow that in the first place.

  • NeoonNeoon Community Contributor, Veteran

    Well, Kimsufi is pretty solid, can't beat the uptime.
    However, running a app that critical, on a single machine, seriously?

    Keep in mind, kimsufi even if automated can easily take 1 hour to replace a motherboard.

  • If you reaaaaly can't afford any downtime >30s, do:

    1. install the LAMP stack as you wish on your new dedicated machine
    2. Setup a Master-Slave replication, your KS-9 as the master and ks-17 as the slave
    3. Once all data is in sync, check with a show slave status; if the data is in sync. Then execute this: https://blog.pythian.com/mysql-recipes-promoting-a-slave-to-master-or-changing-masters/
    4. Setup a proxypass or nginx forward proxy on your current webserver to your new one, and then change the DNS.

    This would be the most effective way i think in your usecase. Best is to have some minutes of downtime though and take your time to execute this. And as always MAKE BACKUPS.

    Thanked by 1ariq01
  • Hey guys, some great points, i'll try to answer them

    1. re:DNS; everything is proxied via CF, so actually there will be next to zero worries there. I'll just be changing the origin server via api.
    2. KimSufi has been 100% (may actually be 100% for want of a better expresion) solid for me. Current uptime is nealry 2 years.
    3. mission critical - it's a personal project and the cost of downtime to me is actually google indexing and the effect on serps. It does matter for this project.
    4. Bargain basement - I somewhat agree, however the difference between KS and someone that you may consider decent is massive in terms of price. There does not seem to be much of a middle ground although if you have suggestions, I'd listen. KS-17 is 20 eur+tax. I could probably budget around 100 Eur+tax, but if I'm just essentially paying 80 euros for better support, then maybe that's not for me. Also, I may well keep the KS-9 as an online backup server if I think the slave/master thing works well without any impact on the mysql performance.
  • deankdeank Member, Troll

    I will echo @Faizo's point here.

    Zero downtime sounds cool, and that's the only charm.
    Don't act cool. Do the shit properly even if it involves some minutes of downtime.

    Now, if you have any user complaining over those minutes of downtime, those are ones you should be avoiding in the first place.

    Thanked by 1Falzo
  • @Adam1 said: google indexing and the effect on serps

    what makes you think a bit of downtime has any effect here at all? whoever told you this, is rather bullshitting you or really believes in some crappy old urban legends.

    what would you do if ovh has a network outage? those do happen and you might not even recognize them all the time. how'd you think this effects indexing and serp?
    also even if a small downtime would have an effect, wouldn't you agree that google is then also smart enough to recognize what's going on and quickly get back on track afterwards? :-)

    I mean, it's definitely your show and as said before learning something new is always a good approach, so keep on digging into the replication / HA topic.
    but personally I'd just find it too risky to try that directly now on your valuable project. if things go wrong you might end up with an even larger downtime or mess of data instead. would that be worth it?

    best of luck in any case ;-)

  • @Adam1 said:
    3. mission critical - it's a personal project and the cost of downtime to me is actually google indexing and the effect on serps. It does matter for this project.

    Lol 🤣.. you must be losing millions by 5 minute downtime..

    Thanked by 1ariq01
  • deankdeank Member, Troll
    edited December 2021

    I think it's important for OP to realize that the world does not revolve around him.

    Some minutes of downtime - nobody will care. Not certainly Google. Not certainly Yahoo (dead). Not Bing.

    And certainly not us.

    Thanked by 1dragon1993
  • @Falzo said: what makes you think a bit of downtime has any effect here at all? whoever told you this, is rather bullshitting you or really believes in some crappy old urban legends.
    @Falzo said: also even if a small downtime would have an effect, wouldn't you agree that google is then also smart enough to recognize what's going on and quickly get back on track afterwards? :-)

    years of first hand experience of running literally 1500+ domains in certain niches. If google considers a site down, even for only a few minutes, then they can and do (sometimes) reduce/harm serps; maybe for a few hours, maybe for much longer. Theres various factors at play including the sites authority and age.

    but it's not only serps, it'll also lead to google reducing indexing frequency, sometimes googlebot doesnt return for a day or two in any meaningful numbers.

    @Falzo said: what would you do if ovh has a network outage? those do happen and you might not even recognize them all the time. how'd you think this effects indexing and serp?

    FYI, I'm having 300-500k pages indexed daily across my services. after the few hours of OVH network outages a little while ago, this dropped to ~100k for the following day and creeping back up over the next few days.

    that's happened a few times, and it has 100% led to it affecting serps negatively too. I've in the past considered having a second box up and ready to go in the case of downtime but it's just something i've put at the bottom of a list. But in the case of network downtime, a second KS box (cant be in CA) isnt going to help me and boxes elsewhere are significantly more epexnsive if I want the same or better specs (the ram and ssd being most importnat).

    I did look at the ryzen hetzner box as an upgrade path and I would have gone that way in a few months time if KS hadnt done this refresh.

  • @FoxelVox said: And as always MAKE BACKUPS.

    3x a day rclone to google drive :)

  • @deank said: Now, if you have any user complaining over those minutes of downtime, those are ones you should be avoiding in the first place.

    well, it's me complaining, not the user. I can probably avoid myself but I don't think it'll help me :)

  • Adam1Adam1 Member
    edited December 2021

    @Neoon said: Well, Kimsufi is pretty solid, can't beat the uptime.
    However, running a app that critical, on a single machine, seriously?
    Keep in mind, kimsufi even if automated can easily take 1 hour to replace a motherboard.

    yeah, ive been complacent because of how rock solid this KS-9 has been. I do want to improve the setup and have a backup machine live and ready to go.

  • @chocolateshirt said: Lol 🤣.. you must be losing millions by 5 minute downtime..

    yeah its a running meme on here, but while not millions, it would likely be enough for most people to want to avoid it given the choice.

  • deankdeank Member, Troll

    PMS detected.

    Thanked by 1donko
  • nfnnfn Veteran

    If you can't do a 503 Service unavailable for a couple of minutes, I would suggest looking for SymmetricDS.

    It's a multi-master setup where you can sync both MySQL databases and switch whenever you want between the two.

    After the move, you can remove SymmetricDS and it's tables/triggers easily.

    Thanked by 1niknar1900
Sign In or Register to comment.