Shulou(Shulou.com)06/01 Report--

AWS RDS MySQL

The MySQL service provided by Amazon deploys high availability zone, single master node and hidden slave node, automatically realizes HA switching, and can be expanded online to exceed the capacity of a single database deployment to handle high-read database load.

1. Advantages:

(1)。 The online upgrade does not affect the normal operation of the business, and the capacity expansion is better in the later stage.

(2)。 High availability zone deployment, hidden slave nodes, automatic HA switching, front-end business is not affected

(3)。 The core parameters of the database layer are adjusted adaptively and dynamically according to the database configuration without human intervention.

(4)。 Built-in database system layer monitoring and alarm function

2. Shortcomings

Can not avoid the pressure of single node is too high, can only read and write operation of single node

MariaDB Galera Cluster

Galera Cluster is a multi-active and multi-master cluster of MariaDB, which can keep all nodes of MariDB synchronized. Galera provides synchronous replication for MariaDB (as opposed to native asynchronous replication), so it can ensure that the HA of the database layer is fully compatible with MariaDB and MySQL in terms of data. The database HA for the whole project needs to use middleware to implement HA (such as LVS, haproxy … )

1. Advantages:

(1)。 Replicate Synchronous replication synchronously

(2) Active-active multi-master topological logic

(3)。 It can read and write data to any node in the cluster.

(4)。 Automatic membership control, fault nodes are automatically removed from the cluster

(5)。 Automatic node join

(6)。 True parallel replication, based on row level

(7)。 Direct client connection, native MySQL interface

(8)。 Each node contains a complete copy of the data

(9)。 Data synchronization in multiple databases is realized by wsrep interface.

two。 Shortcoming

(1)。 Current replication only supports the InnoDB storage engine. Any tables written to other engines, including mysql.* tables, will not be replicated, but DDL statements will be replicated, so the creating user will be replicated, but insert into mysql.user... It will not be copied.

Delete operation does not support tables without primary keys. Tables without primary keys will have different order in different nodes. If SELECT is performed, the order of tables will be different. LIMIT... Different result sets will appear.

(3)。 The query log cannot be saved in a table. If you open the query log, it can only be saved to a file.

(4)。 The maximum transaction size allowed is defined by wsrep_max_ws_rows and wsrep_max_ws_size. Any large operation will be rejected. Such as large LOAD DATA operations.

(5)。 Because the cluster is optimistic about concurrency control, the transaction commit may be aborted at this stage. If two transactions are written and committed to the same line to different nodes in the cluster, the failed node will abort. For cluster-level aborts, the cluster returns a deadlock error code (Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).

(6) .XA transactions are not supported because they may be rolled back on the commit.

(7)。 The write throughput of the entire cluster is limited by the weakest node, and if one node becomes slow, the entire cluster will be slow. In order to achieve stable and high performance requirements, all nodes should use unified hardware.

(8)。 A minimum of 3 cluster nodes are recommended.

(9) .DDL operation will lock the whole table, and the whole cluster will hang. In order to avoid the whole database cluster hang, you need to modify the schema replication DDL mode before operation.

Summary:

According to the above information, in the case of single node writing, RDS MySQL can meet the needs of the business. If not, you can deploy three nodes with Galera Cluster cluster. For operation and maintenance personnel, Galera Cluster has higher maintenance costs and lower costs than RDS MySQL.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.