High Availability and Scalability guaranteed by the built-in MySQL database

TriDoc standard includes an embedded MySQL 5.1 database. MySQL is well known as the most popular open source database of the world. But MySQL is not only a cost effective alternative for databases, due to its reliability it is also a perfect solution for large multinational companies, who need to handle large volumes of data and have high-availability requirements.

Availability refers to the ability to cope with, and if necessary recover from, failures on the host, including failures of MySQL, the operating system, or the hardware. Scalability refers to the ability to spread the load of your application queries across multiple MySQL servers. As your application and usage increases, you may need to spread the queries for the application across multiple servers to improve response times.

There are a number of solutions available for solving issues of availability and scalability. The two primary solutions supported by the TriDoc built-in database are MySQL Replication and MySQL Cluster. (Further options are available using third-party solutions such as DRBD (Distributed Replicated Block Device) and Heartbeat, and more complex scenarios can be solved through a combination of these technologies.)

Replication, backup and archiving

MySQL Replication enables statements and data from one MySQL server instance to be replicated to another MySQL server instance. Without using more complex setups, data can only be replicated from a single master server to any number of slaves. The replication is asynchronous, so the synchronization does not take place in real time, and there is no guarantee that data from the master has been replicated to the slaves.

• • Advantages

    • Replication is available on all platforms supported by MySQL, and since it isn't operating system-specific it can operate across different platforms.

    • Replication is asynchronous and can be stopped and restarted at any time, making it suitable for replicating over slower links, partial links and even across geographical boundaries.

    • Data can be replicated from one master to any number of slaves, making replication suitable in environments with heavy reads, but light writes (for example, many web applications), by spreading the load across multiple slaves.

  • Disadvantages

    • Data can only be written to the master. In advanced configurations, though, you can set up a multiple-master configuration where the data is replicated around a ring configuration.

    • There is no guarantee that data on master and slaves is consistent at a given point in time. Because replication is asynchronous, there may be a small delay between data being written to the master and it being available on the slaves. This can cause problems in applications where a write to the master must be available for a read on the slaves (for example a web application).

  • 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. By replicating live data to a slave you can perform queries on the slave without affecting the operation of the master.

    • Online backup (availability), where you need an active copy of the data available. You need to combine this with other tools, such as custom scripts or Heartbeat. However, because of the asynchronous architecture, the data may be incomplete.

    • Offline backup. You can use replication to keep a copy of the data. By replicating the data to a slave, you take the slave down and get a reliable snapshot of the data (without MySQL running), then restart the built-in database and replication to catch up. The master (and any other slaves) can be kept running during this period.

  For information on setting up and configuring replication, see Replication.

Clustering

MySQL Cluster is a synchronous solution that enables multiple MySQL instances to share database information. Unlike replication, data in a cluster can be read from or written to any node within the cluster, and information is distributed to the other nodes.

• • 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.

    • Nodes within a cluster should be connected through a LAN; geographically separate nodes are not supported. However, you can replicate from one cluster to another using 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.

  For information on MySQL Cluster, see MySQL Cluster.

Summary