The job of a MySQL administrator includes knowing how to scale MySQL systems depending on what’s required of these systems.  It could be that high availability is the most important criteria.  Or it could be that your users would need query times to be fast.  Or you might be looking into using big data for your organization.

There are several techniques to scale your MySQL servers and databases.  And each one provides a set of benefits that touches on costs and complexity.  A great MySQL administrator should decide how to go about scaling MySQL.

In the first part of this article, we have already discussed read/write splitting, something that any MySQL administrator worth his or her salt would know.  And read/write splitting can be somewhat limited, even if it is the most implemented way to scale MySQL.  What are the other techniques to consider when scaling MySQL?

Check out distributed replicated block device, scaling in the cloud and MySQL Cluster.

2. Distributed Replicated Block Device

If you want to achieve high levels of availability, you should use a distributed replicated block device.

A distributed replicated block device gives you a mirror of a whole block device to a networked host.  You work with two independent systems at the very least, and the software would take care of synchronizing these two servers. The primary server will handle all the read and write loads.  All write operations are then replicated to the secondary and other nodes.  If the primary server goes offline, the secondary server will step up and act like the primary server.  This way, you have a reserve database or server for you to use during times when the primary server goes down.

3. Cloud

Scaling your MySQL on the cloud is rather straight-forward.  You just replicate your database and systems to your cloud environment and if your main server or on-premise servers fail, the ones in the cloud can take over.

But because you are storing data on a third-party’s infrastructure, you might not be comfortable with the thought.  Still, compared to traditional hardware and storage investments, putting copies of your database on the cloud might help you save money while still maintaining high availability.

4. MySQL Cluster

If you are dealing with high volumes of big data, then your best bet is MySQL Cluster.  MySQL Cluster allows you to scale efficiently with high availability and more agility.  MySQL Cluster gives you a distributed system that shares nothing with each other.  This gives rise to very high availability, up to 99.999%.  It also shards your database automatically so that even write loads are scalable.  You can also use both relational databases and NoSQL technologies.

While all that sounds great, you should know that MySQL Cluster is very complex and you might need a really good SQL administrator to do the job.  It can also prove to be very expensive.

Four Cornerstone can help you with MySQL administrator duties.  We have a team of MySQL experts that can help you scale, maintain and develop your databases.  We could also train your MySQL administrator to ensure that they are up to par with the best administrators out there.  Call us today!

Photo courtesy of MySQL.