what is mysql fabric

MySQL Fabric is an extensible framework for managing MySQL server groups. It is GPL open source software; that is to say, users can freely use and modify this software in compliance with GPL specifications. mysql fabric is the process that handles any management requests; when using the HA feature, you can make this process responsible for monitoring the master server and, in the event of a failure, initiate failover and upgrade the slave server to the master server.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL Fabric Introduction

MySQL Fabric is an extensible framework for managing MySQL server farms.

MySQL Fabric can "organize" multiple MySQL databases. The application system distributes tables larger than several TB to multiple databases, that is, data shards. The same shard can contain multiple databases, and Fabric automatically selects a suitable one as the master database, and the other databases are configured as slave databases for master-slave replication. When the master database hangs up, one of the slave databases is selected to be promoted to the master database. Afterwards, other slave databases are transferred to the new primary database to copy the new data. Note: The "automatic" mentioned here means that MySQL Fabric completes it in the background without requiring the user to manually change the configuration. The most important thing is that MySQL Fabric is GPL open source software, which means that you can freely use and modify this software under the GPL specifications.

The Fabric framework implements two features: high availability (high availability) and horizontal expansion using data sharding (sharding). These two features can be used alone or in combination.

Both features are implemented based on the following two levels:

mysql fabric is the process that handles any management requests. When using the HA feature, you can also let this process monitor the master server and initiate failover when a failure occurs, upgrading the slave server to the master server. The MySQL Fabric-aware connector stores the routing information obtained from MySQL Fabric in a cache and then uses this information to send the transaction or query to the correct MySQL server.

High Availability

The HA group consists of two or more MySQL servers; at any time, one server serves as the master server (the master of the MySQL replication function). server), and other servers serve as slave servers (slave servers for MySQL replication function). The role of the HA group is to ensure that the data saved in the group is always accessible. MySQL's replication function ensures data security through replication, and MySQL Fabric's high availability solution provides two essential additional elements on this basis:

Fault detection and upgrade—MySQL Fabric monitoring HA group The master server in the master server, in the event of a master server failure, selects a slave server and promotes it to the master server Database request routing - the operation of routing write requests to the master server and load balancing read requests among the slave servers Transparent to the application, even when topology changes during failover

Sharding - Scale out

When close to a MySQL server (or HA group) When capacity or write performance limits are reached, MySQL Fabric supports database server horizontal scaling by partitioning data across multiple MySQL server "groups." Note that a group can contain just one MySQL server, or it can be an HA group. The administrator defines how data is sharded between these servers; specifying which table columns should be used as sharding keys, and whether to use HASH mapping or RANGE mapping to map these keys to the correct shards, if further sharding is required. MySQL Fabric can split existing shards; additionally, shards can be reallocated.

Problems to be solved by MySQL Fabric

Why do data sharding? Data Shard is often necessary when your application needs to process tables larger than 1TB of data. Such a large table will cause inefficiency in terms of query and update efficiency, or the time required to back up and change the structure. Big question. However, when you spread such a large table across multiple database servers, makes each database server likely to be a single point of failure. As long as one of them hangs up, it will cause problems in the operation of the entire system . On the other hand, the application-side program will also be confused because each query must point to different databases according to its query conditions (the content of the where clause). becomes more complex. Furthermore, when the structure of data fragmentation changes (for example, adding a database), all programs on the application side must be modified, resulting in Maintenance becomes extremely complex.

###

In order to solve the problem of increased application complexity, someone adds a proxy between the application and the database server (proxy) or becomes a switch, and the application has all Instructions for the database are first sent to the proxy, and then proxy determines which database to should be transferred to. The picture below is a schematic diagram of this plan. This may solve the problem of the application being difficult to maintain, but when the number of applications increases, database sharding increases, or the system pressure increases, this The switch will become a bottleneck and single point of failure for capacity and performance (when it goes down, the application cannot find the database), and all database instructions need to be transmitted Twice (first to switch and then to database). Each query creates additional load.

#MySQL Fabric Architecture

MySQL Fabric adopts a different approach, and its architecture is shown in the figure below. The main feature is to merge switches into connectors on each application side to solve the single point of failure and performance bottleneck of a single switch.

##MySQL Fabric consists of three parts:

1.MySQL Fabric management node:

is a python script and is the core of the entire architecture.

The main function of the MySQL Fabric management node is to manage the entire database server farm (Database Server Farm). When it starts, it will find the configuration file /etc/mysql/fabric.cnf, and use it to Specify the MySQL database location, port, connection account and other information behind the fabric as the repository that stores the Server Farm architecture and configuration.

When Fabric is initialized (execute the mysqlfabric manage setup command), it will open a schema on the MySQL database (usually a database named fabric) to store Server Farm configuration related information. For example, which server groups are composed of which databases, which master and slave servers are in each server group, etc.

When setting up the configuration of the MySQL Fabric node, it will issue commands to establish master-slave replication for each database in the Server Farm (the red line in the above figure).

Regularly ping the main server of each group during normal operation. When it is found that the main database is not running normally, it will start the failover program and find a slave database in the server farm. Appropriate promotion to master server. Other slave databases will turn to the new master database to continue replicating data.

2. Database server farm

This is the working engine in the entire architecture. In traditional database applications, this It is a single MySQL database, while MySQL Fabric uses multiple databases to support the needs of large data scales (terabytes or more) and high-availability databases. These databases are divided into several high availability groups (HA Group), each group contains more than one database server. The bottom few gray and light blue squares in the figure above represent high availability groups. If there are multiple databases in the high availability group, MySQL Fabric will select (use the command mysqlfabric group promote command) to promote one to the master database (Master), and the other databases will become slave databases (Slave). The slave database will copy the changes of the master database and complete. Set up master-slave replication within the same high availability group. In the future, Fabric will periodically access this main database. When the primary data goes down, Fabric will select one from the high availability group and promote it to the primary database, and other databases will turn to this new primary database to continue replication.

On the other hand, MySQL Fabric will only use the conector on the application side to separate the reading and writing of these master-slave databases. When the application performs both read and write operations on the database, the connector will submit the instruction to the master. database. If the application only performs read operations on the database, and the read_only parameter of the connection is set to "ON", all queries will be sent to these databases in turn. With the separation of reading and writing, the data processing capabilities of the application system can be increased. In addition, as mentioned earlier, MySQL Fabric can also handle tables that need to be split into multiple database servers (sharding tables). Each high availability group may store part of the data of the shard table. The connector on the application side will send the instructions for the shard table to different high availability groups according to the settings of the MySQL Fabric management node. This allows the database capacity to grow as the number of high availability groups increases. At the same time, the instructions and all DDL issued for the non-split table will be sent to the global high availability group (global group) by the connector. The main database of the global high availability group is MySQL Fabric is set up as the primary database for other high availability groups. The primary databases of all high-availability groups that store split tables replicate the changes in the global group, so that other high-availability groups have a copy of the non-split table data. This makes the JOIN operation of split tables on non-split tables in SQL easier.

3. Connector

When the application system is running, each SQL command will be sent to the database through the connector. The connector that MySQL Fabric is equipped with is the same as the general stand-alone MySQL database, except that the newer version of the connector is the fabric aware connector, which has more functions that can handle database server farms. This enables them to check the configuration of the server farm in the management node of MySQL Fabric using the XML-RPC protocol when establishing a database connection, and then queries under the connection can be sent to the appropriate database according to the instructions of the fabric.

In this way, the proxy that may cause performance bottlenecks in common database shard solutions is placed in the connector, thus solving this problem. Currently, the technologies supported by MySQL Fabric include java, python, and PHP, that is, Connector/J, Connector/Python and Connector/PHP are all Fabric-aware.

Taking java as an example, the JDBC driver must be Connector/J 5.1.30 or later. Fabric’s Java program is similar to the general Java program for querying stand-alone MySQL, but it is just creating When the database connection object is used, the database connection URL does not point to the database, but to the MySQL Fabric management node (the IP and port of the server are usually 32274).

When the queried table is a global table (without table shard) or DDL (such as creating a table or changing the table structure), ''fabricServerGroup=" must be added when establishing the connection object. parameters, and then the SQL commands issued through this connection object will be sent to the Global Group's main database, and then the database will be copied to other high availability groups (shards).

If SQL The table to be operated by the command is a shard table. When creating a connection object, you must add the ''fabricShardTable=" parameter to the parameter. Then the SQL command issued through this connection object will be set according to MySQL Fabric. The table (shard) principle is sent to the high availability group of each partition (shard).

In this way, when the application program issues SQL instructions under these shard tables, it does not need to determine which database to send to in SQL. The connector completely determines which database to send to. It is determined by the configuration information of the server farm (which database belongs to which shard group, the splitting principle of each shard table, etc.) found in MySQL Fabric when establishing a database connection. And this configuration is cached on the application side where the Connector is located after the main connection is established.

#In this way, there is no need to repeatedly query the MySQL Fabric management node every time a SQL command is issued, and the sub-table configuration that depends on the application side is directly sent to the correct database. The efficiency of the application will not be reduced in any way due to table splitting.

[Related recommendations: mysql video tutorial]

The above is the detailed content of what is mysql fabric. For more information, please follow other related articles on the PHP Chinese website!