MySQL Fabric Overview

In May, Oracle launched a MySQL Fabric, which has great expectations for all of the parties, and is literally not sure what it is, but there are still traces in the name. Fabric is the meaning of "fabric", which means it is used to "weave" a MySQL database. MySQL fabric is a set of schema management systems for the database server farm.

What is MySQL fabric? MySQL fabric can "organize" multiple MySQL databases, and the application system will spread more than a few terabytes of tables across multiple databases, data Shard. In the same shard can contain multiple databases, and by the fabric automatically pick a suitable as the primary database, the other database configuration from the database, to master from replication. When the primary database hangs, pick one from each database to promote the primary database. After that, the other moves from the database to the new primary database to replicate the new data. Note: The word "automatic" here means that it is done in the background by MySQL fabric without requiring the user to manually change the configuration. Most importantly, MySQL fabric is the GPL's open source software, which is under the GPL, and you are free to use and modify the software. MySQL fabric To solve the problemWhy do data shards? When your app needs to work with tables larger than 1TB, data shard is oftennecessary. Such a large table, whether in terms of query, update efficiency, or backup, change the structure requiredtime, will buildintoa big problem. However, when you spread such a large table across multiple database servers,It will also make every database servercan be a single point of failure. Justone of them hangs up and makes the whole systemthe operation of the problem occurs. On the other hand,application-side programs also because each query depends on its query criteria(the contents of the WHERE clause) point to a different databaseand become more complex. Furthermore, when data is fragmented,structurechanges (for example, by adding a database), it causes the application side to have allprograms must be modified, causingmaintenance becomes extremely complex. in order to solve the problem of increasing application complexity, there is an increase in the application and database serveran agent(proxy) or switch, all of the application's instructions to the database are sent to the proxy first, and then by theproxy judgment to go towhich database. That's the plan. This may solve the application processproblems that are difficult to maintain, butwhen the number of applications is increased, the database shards increase, or the system pressure increasesovertime, this switch becomes capacity and sexand a single point of failure (when it goes down, the application can not findDatabase ), and all database directives need to betwo times (first to switch and then to database). Each query will cause additional load. Architecture of MySQL Fabric

MySQL Fabric uses the unused approach, as shown in the architecture. The main feature is to incorporate switch into the connector of each application to address single-switch point-of-failure and performance bottlenecks.

MySQL fabric is comprised of three parts:

1.MySQL Fabric Management node:

is a Python script, which is the core of the entire architecture. The primary function of the MySQL Fabric Management node is to manage the entire database server farm, which, when started, will look for the/ETC/MYSQL/FABRIC.CNF configuration file, which specifies that the fabric is behind the server Farm architecture and configuration of the repository MySQL database location, port and connection account information. When fabric is initialized (executing the mysqlfabric Manage Setup command), a schema is opened on the MySQL database (usually the database with the name fabric), and the configuration-related information for the server farm is stored. such as which server groups are composed of which databases, which are the master and slave servers in each server group, and so on. When the MySQL fabric node sets up the configuration, it releases a master-slave copy of the command (the Red line) for each database in the server farm. The primary server of each group is ping periodically during normal operation, and when it is found that the primary database is not functioning properly, it starts the failover program and finds an appropriate elevation master server from the database in the server farm. The other from the database goes to the new master database to continue replicating the data.

2. Database server farm (DB server farm) This is the entire architecture of the work engine, in traditional database applications this is a single MySQL database, MySQL fabric is to support large data scale (more than TB level) and high availability database in multiple databases. These databases are divided into several highly available groups (HA Group), each containing more than one database server, and the bottom of the few gray and light blue squares that represent high availability groups. If there are multiple databases in a high availability group, MySQL fabric picks up (using the command mysqlfabric Group Promote command) one promotion to the primary database (master), and the other database becomes from the database (Slave), replicating the change of the primary database from the database To finish setting up master-slave replication within the same high availability group. Later, fabric will periodically work on this master database. When the master data goes down, fabric picks a promotion from the highly available group as the primary database, and the other databases move on to the new primary database for replication. on the other hand, MySQL fabric will be just the application side of the Conector to the master-slave database read and write separation, when the application to read and write the database operation, Connector will submit the instruction to the primary database. If the application only reads the database and the READ_ONLY parameter of the connection is set to "on", all queries are transferred to these databases in turn. With the use of read/write separation, the data processing capacity of the application system is increased. In addition, as mentioned earlier, MySQL fabric can also handle tables (sharding tables) that need to be split across multiple database servers, and each high-availability group may hold part of Shard table data. The connector of the application will send instructions to the Shard table to different high-availability groups based on the configuration of the MySQL fabric Management node, which increases the capacity of the database as the number of highly available groups grows. At the same time, the directives and all DDL for non-split tables are sent by connector to the global High Availability Group,and the primary database of the global High Availability group is set by MySQL fabric as the primary database for the other highly available groups. The master database of all the high availability groups that are in the split table replicates the changes in the global group, so that other high availability groups have a non-split table of data. This makes it easier for a split table in SQL to join operations on non-split tables.
3. ConnectorWhen the application is running, each SQL instruction is sent to the database via connector. MySQL fabric is paired with the connector and generally use a single-machine MySQL database, just in the newer version of connector is the fabric aware connector more capable of processing the database server farm (DB server Farm) functions. Enables them to check the configuration of the server farm in the management node of MySQL fabric with the XML-RPC protocol when establishing a database connection, and then the query under that connection can be sent to the appropriate database according to Fabric's instructions. As a result, the common Database shard scenario can cause performance bottlenecks in the connector, which solves this problem. Currently, MySQL fabric supports Java, Python, PHP, connector/j, Connector/python, and connector/php, all of which are fabric-aware. In Java, for example, the JDBC driver must be a later version of Connector/j 5.1.30, and the Java program of fabric is almost the same as the Java program for a single-machine MySQL query, just to build the database connection object when the database connection URL is not pointing to the DB, instead pointing to the MySQL Fabric Management node (the server's IP and port is typically 32274). When querying a table with a global table (no table shard) or DDL (for example, building a table or changing the table structure), establish connection object with the ' fabricservergroup= ' parameter, and then pass this connection The SQL instructions under object are sent to the master database of the global group, which is then replicated to other high-availability groups (shard). If the SQL command is to manipulate the table when the partition table (shard table) is established, the connection object is created when the parameter is addedThe ' fabricshardtable= ' parameter, followed by the SQL command under this connection object, is sent to the high-availability group for each partition (shard) According to the table (shard) principle set by MySQL fabric. In this way, the application does not need to determine in SQL what database to send to the SQL instructions under these Shard table, completely by connector the configuration information of the server farm that was found to MySQL fabric when the database connection was established ( Which database belongs to which Shard Group, the split principle of each shard table, etc.) is determined. Moreover, this configuration is slow to exist on the application side of connector after the primary connection is established. This way, the MySQL fabric management node does not need to be queried repeatedly each time the SQL instruction is under, and the application-dependent table configuration is sent directly to the correct database. The efficiency of the application is not reduced by the splitting of the table.
Conclusion
MySQL Fabric launched the official release version only two months, has caused many heavy-weight MySQL users attention and use. And Oracle is too generous to invest in it to accelerate its functionality. Now that a newer version is available, with support for SSL connections in the latest edition of MySQL fabric, the focus on fabric refinement in the near future will be to focus on making the fabric more transparent to the application (for example, single SQL Shard table query conditions can span shard), Support more highly available scenarios, provide a more user-friendly GUI, and more. It is recommended that a friend who cares about MySQL development can take note of the development of this product and try it further. If you are satisfied with the functionality and stability that it provides, you can add it to your production system, and the fabric development team will welcome any contributions to the new members of this MySQL family. The purpose of this article is to introduce the problems that MySQL fabric needs to solve and the architecture of fabric, and for detailed setup and operation, let me share with you in an example in the next article, so please look forward to it.

MySQL Fabric Overview