3

What are some generally accepted strategies for restarting a MySQL server on a busy website without interrupting current users? I am using a LAMP setup. I don't mind taking down the site for a time if need be, but if certain user activities are interrupted I could wind up with corrupted data. I do have the ability to bring up a second server if that helps in the transition. I need a solution that results in no corrupted data / data loss.

I suspect this could be a common problem without an easy solution, but not sure what the best approach would be. Any guidance would be appreciated.

Thanks, Brian

Brian
  • 2,107
  • 6
  • 22
  • 40

1 Answers1

3

Any solution for high availability depends on redundancy.

The most popular strategy today is to run two MySQL servers. Configure the two servers to replicate bidirectionally. This comes with its own challenges; you must manage your applications carefully to write to only one server at a time, to avoid creating update conflicts. When you need to restart one MySQL server, switch your apps to use the other MySQL server.

Even with this configuration, you can't make the switchover without interrupting connections, even if the interruption is brief.

Another solution is MySQL Cluster, in which both MySQL Servers and storage are redundant, but this is also complex to set up and manage, requires high-end hardware resources, and shards your data in ways that make it hard to optimize for general SQL queries.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828