Shulou(Shulou.com)05/31 Report--

MySQL Cluster example analysis, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. Background

MySQL's cluster solution has many official and third-party choices, and too many choices is a worry. Therefore, we consider that the mysql database meets the following three requirements and examine the feasible solutions on the market:

High availability: automatically switch to backup server after primary server failure

Scalability: it is easy to add DB servers through scripts

Load balancing: supports manual switching of data requests from a company to another server, and which company's data services can be configured to access which server

Need to choose a solution to meet the above needs. The pros and cons of several solutions are referenced on the official MySQL website:

After comprehensive consideration, it is decided to adopt MySQL Fabric and MySQL Cluster schemes, as well as another more mature cluster scheme, Galera Cluster, for pre-research.

2.MySQLCluster

Brief introduction:

MySQL Cluster is the official cluster deployment solution for MySQL, and it has a long history. Support for read-write expansion through automatic sharding, and real-time backup of redundant data is the most available solution, claiming to achieve 99.999% availability.

Architecture and implementation principles:

MySQL cluster consists of three main types of services:

NDB Management Server: the management server is mainly used to manage other types of nodes in the cluster (Data Node and SQL Node), through which you can configure Node information and start and stop Node.

SQL Node: in MySQL Cluster, a SQL Node is a mysql server process that uses the NDB engine to provide access to cluster data for external applications.

Data Node: used to store cluster data; the system will try to keep the data in memory.

Shortcomings and limitations:

For tables that need to be sharded, you need to modify the engine Innodb to NDB, and those that do not need sharding do not need to be modified.

NDB's transaction isolation level only supports Read Committed, that is, a transaction cannot query changes made within a transaction before committing, while Innodb supports all transaction isolation levels and defaults to Repeatable Read, which does not exist.

Foreign key support: although the latest version of Cluster already supports foreign keys, there is a performance problem (because the records associated with foreign keys may be in other shard nodes), so it is recommended to remove all foreign keys.

Data Node node data will be kept in memory as much as possible, which requires a lot of memory.

The database system provides four transaction isolation levels:

A.Serializable (serialization): a transaction does not see updates made to the database by other transactions during execution. (other transactions are not allowed to execute concurrently when a transaction is executed. Transactions are serialized, and transactions can only be executed one after another, not concurrently. ).

B.Repeatable Read (readable): during execution, a transaction can see newly inserted records that have been committed by other transactions, but cannot see updates to existing records by other transactions.

C.Read Commited (read committed data): during execution, a transaction can see newly inserted records that have been committed by other transactions, and updates to existing records that have been committed by other transactions.

D.Read Uncommitted (read uncommitted data): during execution, a transaction can see newly inserted records that are not committed by other transactions, and updates to existing records that are not committed by other transactions.

3.MySQL Fabric

Brief introduction:

In order to achieve and facilitate the management of MySQL sharding and highly available deployment, Oracle launched a set of MySQL products-MySQL Fabric, which has high expectations for all parties, in May 2014, which is used to manage MySQL services and provide a scalable and easy-to-use system. Fabric currently implements two features: high availability and the use of data sharding for scalability and load balancing, which can be used alone or in combination.

MySQL Fabric uses a series of Python scripting implementations.

Application case: as the program was only launched last year, there are no application cases of large companies on the Internet.

Architecture and implementation principles:

The architecture diagram of Fabric that supports high availability is as follows:

Fabric uses HA groups for high availability, one of which is the primary server and the other is the backup server, which achieves data redundancy through synchronous replication. The application uses a specific driver to connect to the Connector component of Fabric. When the primary server fails, Connector automatically upgrades one of the backup servers to the primary server, and the application needs no modification.

Fabric supports scalability and load balancing architecture as follows:

Sharding is implemented using multiple HA groups, and different sharding data is shared between each group (the data within the group is redundant, which has been mentioned in high availability)

The application only needs to send statements such as query and insert to connector, and Connector automatically distributes the data to each group through MasterGroup, or combines qualified data from each group and returns it to the application.

Shortcomings and limitations:

The two restrictions that have a greater impact are:

The self-growing key cannot be used as the key of a slice.

Transactions and queries can only be in the same shard, and the updated data in the transaction cannot be multishard, nor can the data returned by the query statement.

Test high availability

Server architecture:

Function

IP

Port

Backing store (save configuration information for each server)

200.200.168.24

3306

Fabric Management process (Connector)

200.200.168.24

32274

HA Group 1-Master

200.200.168.23

3306

HA Group 1-Slave

200.200.168.25

3306

The installation process is omitted. Here is how to set up high availability groups, add backup servers, and so on.

First, create a highly available group, such as the group name group_id-1, with the command:

Mysqlfabric group create group_id-1

Add machines 200.200.168.25 and 200.200.168.23 to the group group_id-1:

Mysqlfabric group add group_id-1 200.200.168.25:3306

Mysqlfabric group add group_id-1 200.200.168.23:3306

Then check the status of the machines in the group:

Since the primary server is not set, the status of both services is SECONDARY

Promote one of the primary servers:

Mysqlfabric group promote group_id-1-- slave_id 00f9831f-d602-11e3-b65e-0800271119cb

Then check the status:

The service set up as the primary server has become Primary.

In addition, the mode attribute indicates that the server is read-write (READ_WRITE) or read-only (READ_ONLY), and read-only means that the pressure on query data can be shared; only the primary server can be set to read-write (READ_WRITE).

At this point, check the slave status of the 25 server:

You can see that its main server has pointed to 23

Then activate the automatic failover feature:

Mysqlfabric group activate group_id-1

After activation, you can test the high availability of the service.

First, do a status test:

Stop primary server 23

Then check the status:

As you can see, 25 is automatically promoted to the primary server at this time.

However, if you restore 23, you need to manually reset 23 to the primary server.

Real-time testing:

Purpose: to test how long it takes for the backup server to display the data after the primary service updates the data

Test case: use Java code to establish a connection, insert 100 records into a table, and see how long it takes the backup server to synchronize these 100 pieces of data.

Test results:

There are 101 pieces of data in the table. After running the program, check the number of data items on the main server:

It can be seen that the primary server is of course updated immediately.

View the number of data items on the backup server:

But the backup server waited 1-2 minutes to complete synchronously (you can see that fabric uses asynchronous replication, which is the default and has better performance. The primary server does not have to wait for the backup server to return, but the synchronization speed is slow)

For the stability of synchronizing data from the server, there are the following solutions:

Use semi-synchronous to enhance data consistency: asynchronous replication can provide better performance, but the master database only sends binlog logs to the slave database, and the action ends, and does not verify whether the slave database has been received, which is a high risk. Semi-synchronous replication returns after it is sent to the slave library and waits for a confirmation message to be sent from the slave library.

You can set the update method of synchronizing logs from the library, thereby reducing the delay of synchronizing from the library and speeding up the synchronization.

Install semi-synchronous replication:

Run in mysql

Install plugin rpl_semi_sync_master soname 'semisync_master.so'

Install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

SET GLOBAL rpl_semi_sync_master_enabled=ON

SET GLOBAL rpl_semi_sync_slave_enabled=ON

Modify my.cnf:

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_slave_enabled=1

Sync_relay_log=1

Sync_relay_log_info=1

Sync_master_info=1

Stability test:

Test case: use java code to establish a connection, insert 1w records into a table, stop the master server during the insertion process, and see if the backup server has these 1w records.

The test results show that after stopping the master server, the java program throws an exception:

But send the sql command again at this time, and you can return successfully. It proved that it was only the failure of the business at that time. The connection has been switched to the backup server and is still available.

After flipping through the mysql documentation, there is a chapter on this issue:

It is mentioned that: when the primary server crashes, our application does not need to make any changes, but before the primary server is replaced by the backup server, some transactions will be lost, which can be treated as normal mysql errors.

Data integrity check:

Test whether the backup server can synchronize all data after the primary server is stopped.

After restarting the master server, check the number of records.

You can see that it was stopped after 1059 records were inserted.

Now take a look at the number of records on the backup server to see if all the data can be synchronized after the main server crashes.

It took about dozens of seconds to complete the synchronization. Although the data was not synchronized immediately, it was not lost.

1.2. Sharding: how to support scalability and load balancing

Introduction to fabric sharding: when a machine or a group cannot bear the service pressure, you can add a server to share the read and write pressure. Through the sharding function of Fabirc, you can distribute the data in some tables to different servers. We can set the rules for allocating data stores, and set the rules for allocation by setting sharding key in the table. In addition, the data of some tables may not need to be stored in fragments, and the entire table needs to be stored in the same server. You can set a global group (Global Group) to store this data, and the data stored in the global group will be automatically copied to all other sharding groups.

4.Galera Cluster

Brief introduction:

Galera Cluster claims to be the most advanced open source database cluster solution in the world.

Main advantages and characteristics:

True multi-master service model: multiple services can be read and written at the same time, unlike Fabric, some services can only be used for backup

Synchronous replication: no delay replication, no data loss

Hot standby: when a server goes down, the standby server will automatically take over without any downtime.

Automatically extend nodes: when you add a new server, you do not need to manually copy the database to the new node

Support for InnoDB engine

Transparent to the application: the application does not need to be modified

Architecture and implementation principles:

First, let's take a look at the traditional mysql Replication-based architecture diagram:

The Replication method is to start the replication thread to copy the update log from the main server and then transfer it to the backup server for execution. This way has the risk of transaction loss and untimely synchronization. Fabric and traditional master-slave replication are implemented in this way.

On the other hand, Galera uses the following architecture to ensure the consistency of transactions across all machines:

The client accesses the database through Galera Load Balancer, and every transaction committed is executed on all servers through wsrep API. Either all servers execute successfully or all are rolled back to ensure data consistency of all services, and all servers are updated synchronously in real time.

Shortcomings and limitations:

Because the same transaction needs to be executed on multiple machines in the cluster, network transmission and concurrent execution will lead to a certain consumption of performance.

The same data is stored on all machines, which is fully redundant.

If a machine acts as both a primary server and a backup server, the probability of rollback caused by optimistic locks will increase, so be careful when writing programs.

Unsupported SQL:LOCK / UNLOCK TABLES / GET_LOCK (), RELEASE_LOCK ()...

XA Transaction is not supported

At present, there are three implementation schemes based on Galera Cluster: Galera Cluster for MySQL, Percona XtraDB Cluster and MariaDB Galera Cluster.

We adopt Percona XtraDB Cluster which is more mature and has more application cases.

Application case:

More than 2000 foreign enterprises use:

These include:

Cluster deployment architecture:

Function

IP

Port

Backing store (save configuration information for each server)

200.200.168.24

3306

Fabric Management process (Connector)

200.200.168.24

32274

HA Master 1

200.200.168.24

3306

HA Master 2

200.200.168.25

3306

HA Master 3

200.200.168.23

3306

4.1. Test data synchronization

Create a table on machine 24:

Check it in 25 immediately and you can see that it has been created synchronously.

Insert 100 records on a 24 server using Java code

View the number of records on the 25 server immediately

It can be seen that data synchronization takes effect immediately.

4.2. Test adding cluster nodes

The step of adding a cluster node is simple, as long as the Percona XtraDB Cluster is deployed on the newly added machine, and then started, the system will automatically synchronize the data from the existing cluster to the new machine.

Now for testing, stop one of the node services:

Then use java code to insert 100W data on the cluster

View the database size of 100w data:

At this point, another node is started, and the data of the cluster is automatically synchronized when it is started:

It only takes about 20 seconds to start, check the data size is the same, check the number of table records, and have been synchronized.

5. Comparison and summary

MySQL Fabric

Galera Cluster

Use case

It was only launched in May 2014, and no application cases of large companies have been found on the Internet.

The scheme is more mature and is used by many foreign Internet companies.

Real-time performance of data backup

Due to the use of asynchronous replication, the latency is generally tens of seconds, but the data is not lost.

Real-time synchronization, data will not be lost

Data redundancy

With sharding, different data from the same table can be scattered across multiple machines by setting sharding key rules

Each node is fully redundant and has no fragmentation

High availability

The automatic switching of the main server after downtime is realized through Fabric Connector, but due to the backup delay, the data may not be queried immediately after switching.

Use HAProxy to implement. The availability of switching is higher due to real-time synchronization.

Scalability

After adding nodes, you need to manually copy the cluster data

It is very convenient to expand the node and automatically synchronize the cluster data when starting the node. 100w data (100m) only takes about 20 seconds.

Load balancing

Implemented through HASharding

Using HAProxy to achieve load balancing

Program modification

Need to switch to jdbc class and url of jdbc:mysql:fabric

The program does not need to be modified

Performance comparison

Use java to insert 100 records directly with jdbc, about 2000+ms

Just like directly manipulating mysql, insert 100 records directly with jdbc, about 600ms

6. Practical application

Considering the advantages and disadvantages of the above solution, we prefer to choose Galera. If there are only two database servers, consider using the following database architecture for high availability, load balancing and dynamic expansion:

If three machines can consider:

After reading the above, have you mastered the method of example analysis of MySQL Cluster? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.