There are various solutions available for achieving high availability and scalability of MySQL data/service. ‘MySQL Replication’ and ‘MySQL Cluster’ the solutions offered and supported by MySQL. Third-party solutions such as DRBD (Distributed Replicated Block Device) and Heartbeat can also be used or one could implement a combination of these technologies.
The two solutions offered by MySQL are discussed here. Of these, MySQL Cluster is considered a feasible and reliable solution and is our subject of interest.
MySQL Replication:
It enables data from one MySQL server instance to be replicated to another MySQL server instance. It allows replication of data from a single master server to any number of slaves using a simple setup. However, this is an asynchronous replication solution, so the synchronization does not take place in real time, and data replication among all slaves is can not guaranteed always.
- Advantages
- This can be implemented on any platform (which MySQL supports) and isn’t OS specific.
- Replication is asynchronous and can be stopped and restarted at any time, and is easily manageable during network issues.
- Data can be replicated from one master to any number of slaves. This is suitable for environments with heavy reads, but light writes, by spreading the load across multiple slaves.
- Disadvantages
- Data can only be written to the master.
- There is no guarantee that data on master and slaves will be consistent at a given point in time. Due to asynchronous replication of data, there may be a small delay between data being written to the master and it being available on the slaves.
- Recommended uses
- Scale-out solutions that require a large number of reads but fewer writes (for example, web serving).
- Logging/data analysis of live data. Queries can be performed on the slave without affecting the operation of the master.
- Online backup (high availability – when used in combination with heartbeat). However, since asynchronous replication is used, the data may be incomplete.
- Offline backup – Snapshot of existing data at a given point of time can be taken by replicating the data to a slave.
MySQL Cluster:
It is a is a high-availability, high-redundancy synchronous solution that enables multiple MySQL instances to share database information. Data in a cluster can be read from or written to any node within the cluster, and information will be distributed to the other nodes.
This technology enables clustering of in-memory databases in a shared-nothing system. Shared-nothing means each component is allocated its own memory and disk and such an architecture allows the system to work with very inexpensive hardware, and with a minimum of specific requirements for hardware or software. Use of shared storage mechanisms such as SAN, NFS etc is not supported.
- Advantages
- Offers multiple read and write nodes for data storage.
- Provides automatic failover between nodes. Only transaction information for the active node being used is lost in the event of a failure.
- Data on nodes is instantaneously distributed to the other data nodes.
- Disadvantages
- Available on a limited range of platforms.
Only a development version is available for Microsoft Windows and Apple Mac OSX. It is not supported on SuSE Eneterprise Linux 11 as of this writing. Please check the following link for more details: http://www.mysql.com/support/supportedplatforms/cluster.html
-
- Nodes within a cluster should be connected via a LAN; geographically separate nodes are not supported. However, you can replicate from one cluster to another using MySQL Replication, although the replication in this case is still asynchronous.
- Recommended uses
- Applications that need very high availability, such as telecoms and banking.
- Applications that require an equal or higher number of writes compared to reads.
