update of mysql server without break

Question

Hy, I want to update my databases on my mysql server, but for now i usually take my server down before to the update.

I wonder if there is any solution to make the update without any disruption of the website disponibility ?

Thanks for your help.

Answer 1

What do you wanna do, update the server software or update/alter tables on the DB? Let's tackle both cases:

1. Update the software: you will need a Master-Master replication setup and a floating IP. This floating IP stays on the first master during normal operations, then you can:

   1. Take down the secondary master.
   2. Update the software.
   3. Bring it up again and wait for the replication to catch up.
   4. Change the floating IP to the secondary (and now updated) master.
   5. Update the primary master the same way you did with the secundary.

2. Update/alter tables: most of those actions can be done while the server is running, unless it's a very big table. ALTER TABLE usually locks the table so while the command is running the app will not be able to access it (can't remember if `ALTER TABLE' is a full lock or just write lock, check the docs). If the migration is going to take too much, you will need to do something like the update above, doing it on the master first and then on the other master, but this is more delicate and the instructions are too complex to accomplish here.