MySQL Data Replication 솔루션

MySQL Cluster

MySQL Cluster, which is a different product than the MySQL database server, provides several of these features natively. Synchronous replication and automatic partitioning are standard features in MySQL Cluster; however, this is a very different product than the MySQL server. The name is appropriate for features, yet deceiving when compared with MySQL.

While this product includes a SQL interface, data access for high throughput environments can be achieved from an Application Programming Interface (API). The strengths and features of MySQL Cluster are not always applicable to an Online Transaction Processing (OLTP) application using traditional MySQL replication. There are also additional limitations, including the database size with available memory. This product is ideal in certain situations and is widely used in the telecommunications and gaming industries. The use, understanding, and benefits of MySQL Cluster are topics for an entire book.

It should be noted that MySQL Cluster mainly differs from a regular MySQL server for the following additional reasons:

•   There is currently no referential integrity. Foreign keys are being developed for a future version.

•   You can only use the NDB storage engine and are then limited to READ-COMMITTED transaction level.

•   While primary key lookups are fast and concurrent access is faster than a regular MySQL server, more complex queries, including table joins and group by operations, are more expensive.

For more information about MySQL Cluster, refer to http://www.mysql.com/products/cluster/.

 

Galera Cluster for MySQL

Galera Cluster for MySQL provides synchronous replication and multi-master features when using InnoDB. This means a Galera Cluster supports reads and writes to any node, and provides a true multi-master experience with no lag and no loss of transactions.

Galera Cluster is built on a more generic replication API called wsrep (Write Set REPlication). The wsrep API defines an interface between the database server and the replication plugin and is a separate open source project by Codership. MySQL-wsrep is a patch for MySQL to implement the wsrep API in the database server. This patch will enable the MySQL server to use a wsrep provider plugin, for example, Galera. Galera is the wsrep provider including synchronous multi-master replication.

Galera 2.x is the currently supported version available with MySQL 5.1 GA and MySQL 5.5 GA. Galera is an open source project available under the GPL v3 license, with integration components available under the GPL v2 license to be compatible with MySQL.

Current Limitations

When comparing Galera with a traditional MySQL server, there are some limitations; however, these are not serious roadblocks to trying and using Galera effectively:

•   Only InnoDB tables are supported in replication.

•   LOCK and UNLOCK statements, GET_LOCK() and RELEASE_LOCK() functions are not supported.

•   Log output to TABLE using log_output is not supported. Logging must be to FILE.

•   XA (eXtended Architecture) transactions are not currently supported.

 

Tungsten Replicator

Tungsten Replicator provides an extensive list of additional replication features to MySQL, including supporting seamless failover of MySQL servers, flexible filtering of operations, multi-master and multi-master to slave support (i.e., fan in), parallel replication, and much more.

In addition to MySQL replication, Tungsten Replicator can provide heterogeneous data management with other RDBMS and noSQL products, including data synchronization between MySQL and Oracle and vice versa.

Tungsten Replicator is available under the open source GNU GPL v2 license. Continuent, the creators of Tungsten Replicator, also provide commercial support with an enterprise offering. More information can be found athttp://www.continuent.com/solutions/overview.

Features

There is a long list of possible features that can be discussed. In this section, which is an introduction to Tungsten Replicator, the following will be discussed and demonstrated:

•   Master failover via slave promotion

•   Bidirectional replication (e.g., active/active multi-master)

•   Parallelization and replication prefetching for improved performance

•   Complex topologies

With traditional MySQL replication, the version of a MySQL slave should be the same or greater than that of the master. With Tungsten Replicator, this limitation does not exist. It is possible to replicate from MySQL 5.5 to MySQL 5.0, for example. There are limitations to any features that are in the newer version of MySQL; however, Tungsten can handle the error condition gracefully and not interrupt replication from continuing.

NOTE    Tungsten Replicator has one significant benefit over other products. There is no change to the standard MySQL installation necessary. There are no custom or patched MySQL binaries to install or maintain. Tungsten Replicator works with your existing MySQL environment and can replicate between MySQL 4.11^*, 5.0, 5.1, 5.5, and 5.6 as well as different flavors, including MySQL Community, MySQL Enterprise, MariaDB, and Percona Server.

 

SchoonerSQL

SchoonerSQL by Schooner Information Technology (recently acquired by SanDisk) provides a commercial synchronous MySQL replication solution using a modified version of InnoDB. The documented features include no loss of data, instance failover, and automated recovery capabilities. A SchoonerSQL solution can also include traditional MySQL replication for additional slaves. As a commerical product, there is no access to the software without a formal pre-sales process. A request to review the software for this book was not granted.