MySQL high availability – Top 7 solutions to improve MySQL uptime
As they say, first impressions matter. Your next high value customer could be browsing your site right now.
What if your site fails to load? This scenario is not far from the truth for many websites, and very often it’s the database that fails.
Our Support Engineers support server infrastructure of several online businesses.
In our line of work we’ve seen Databases can fail due to resource limits (I/O, memory, CPU), file system errors, database table errors, or even hardware failures.
To avoid a downtime, it is important to setup high-availability systems for databases.
MySQL is by far the most popular database for web applications. Today, we’ll take a look at the top 7 solutions to enable high availability in MySQL database systems.
Hardware failures is the number 1 reason for MySQL downtime*. This includes hard disk crashes, network card failures, power failure, etc.
The simplest way to avoid this pitfall is to use “two of everything” – use dual power units, dual network cards, RAID hard disks, etc. This will ensure that there’s no Single Point Of Failure (SPOF). Even if one device fails, another will keep the server running.
2. Shared storage with SAN and NAS
Using redundant hardware such as RAID keeps MySQL online even if one server component fails, but what if the whole server is cut off for some reason (like the rack fails)?
To combat that possibility, use a stand-by server that can come online if the main server fails. But it should be connected to a shared storage like SAN or NAS that sits outside the server.
So, if the main server goes down, a backup server can just connect to the shared storage, and continue serving database queries. However, the SAN/NAS device should be replicated to another device so as to avoid a Single Point Of Failure at storage level.
3. Storage replication using DRBD
In a server with RAID hard disks, if one disk fails, another with replicated data takes its place. But since both RAID disks are in the same server, the database will fail if the server itself goes down.
What if there’s a way to replicate a hard disk to another hard disk in a different server? That’s what Distributed Replicated Block Device (DRBD) achieves. It’s an open source Linux kernel module that allows data replication over the network.
When two servers are connected with DRBD, any update made to the database in one server is immediately replicated in the other server. So, in case the main server goes down, the backup server can be immediately brought online to continue the service.
Manually switching the database service to the backup server can cost a lot of time. As a solution, high availability switching tools such as Pacemaker can be used to automatically bring the backup server online.
[ You don’t have to be a MySQL expert to keep your sites always available. Hire experienced MySQL admins to manage your infrastructure. Plans start at $12.99/hour.]