MySQLFabric overview _ MySQL

In May this year, Oracle launched a set of MySQL product-MySQLFabric, which has high hopes for all parties. it cannot be seen from the literal sense, but its name is still traceable. Fabric means fabric, which means it is used to build a MySQL database. In May this year, MySQL Oracle launched a set of MySQL product-MySQL Fabric, which has high hopes for all parties. it cannot be seen from the literal sense, but its name is still traceable. Fabric means "fabric", which means it is used to "Weave" a piece of MySQL database. MySQL Fabric is a Database Server Farm architecture management system.

What is MySQL Fabric? MySQL Fabric can "organize" multiple MySQL databases. the application system distributes tables larger than several TB to multiple databases, that is, Data sharding ). The same shard can contain multiple databases, and the Fabric AutomaticSelect a suitable primary Database. other databases are configured as slave databases for master-slave replication. When the primary database crashes, select an upgrade from each database to the primary database. Then, the other data is copied from the database to the new primary database. Note: "automatic" means that MySQL Fabric completes the configuration in the background without manual configuration changes. Most importantly, MySQL Fabric is an open-source GPL software. in compliance with GPL specifications, you can freely use and modify this software. Why is data sharding required for MySQL Fabric? Data Shard is often required when your application needs to process tables larger than 1 TB of Data. Such a large table may cause great problems in terms of query and update efficiency, or the time required for backup and structure change. However, when you distribute such a large table to multiple database servers, each database server may have a single fault point. If one fails, the operation of the entire system may fail. On the other hand, applications will become more complex because each query points to different databases according to its query conditions (the content of the where clause. In addition, when the structure of data shards changes (such as adding a database), all applications on the application end must be modified, resulting in extremely complex maintenance. To solve the problem of increasing application complexity, someone adds a proxy or a switch between the application and the database server. all database commands of the application are sent to the proxy first, then, the proxy determines the database to be transferred. This is the solution. This may solve the problem that the application is difficult to maintain. However, when the number of applications increases, database shards increase, or the system pressure increases, this switch will become a bottleneck for capacity and performance and a single point of failure (when it goes down, the application cannot find the database ), in addition, all database commands must be passed twice (first go to switch and then to the database ). Each query results in additional load. MySQL Fabric architecture

The architecture of MySQL Fabric is shown in. The main feature is to merge the switch into the ctor of each application 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:

It is a python script and 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). it will find/etc/mysql/fabric at startup. the cnf configuration file specifies the location, port, and connection account of the MySQL database that stores the Server Farm architecture and the configured repository. When Fabric is initialized (execute the mysqlfabric manage setup command), a schema (usually a database named fabric) is opened on the MySQL database to store configuration information about the Server Farm, for example, which server groups are composed of databases, and which server groups have master-slave servers. When configuring the MySQL Fabric node, the master-slave replication command (red lines) is issued to each database in the Server Farm ). During normal operation, ping the master servers of each group regularly. when the master database is not running properly, it starts the failover program, find an appropriate upgrade from the database on the server farm as the master server. Other slave databases are switched to the new master database to continue data replication.

2. database server farm (farm) is a working engine in the entire architecture. in traditional database applications, this is a single MySQL database, mySQL Fabric is based on the need for multiple databases to support big data tables (TB-level or higher) and high-availability databases. These databases are divided into several HA groups. Each Group contains more than one database server. the following gray and light blue blocks represent the high availability Group. If there are multiple databases in the high-availability group, MySQL Fabric selects (using the command mysqlfabric group promote command) a Master database (Master), and other databases become Slave databases (Slave ), replicate the changes in the primary database from the database to complete master-slave replication in the same high-availability group. In the future, Fabric will handle the primary database on a regular basis. When the primary data goes down, Fabric selects a master database from the high-availability group, and other databases switch to the new primary database for further replication. On the other hand, MySQL Fabric only uses the application's conector to separate the read and write operations on these master and slave databases. when the application performs read and write operations on the database, connector submits the command 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 are evenly distributed to these databases. With the help of read/write splitting, the data processing capability of the application system is increased. In addition, as described above, MySQL Fabric can also process tables that need to be split into multiple database servers (sharding tables). each high-availability group may store part of shard table data. The application connector ctor sends the shard table commands to different high-availability groups according to the management node settings of MySQL Fabric, this increases the database capacity with the increase in the number of high-availability groups. In addition, connector sends commands and all DDL statements for non-sharded tables to the global high-availability group ), the master database of the global high-availability Group is set as the master database of another high-availability group by MySQL Fabric. The master database of the high-availability group for all the tables to be saved and split copies the changes of the global group. in this way, all other high-availability groups have non-sharding table data. This makes the JOIN operation on non-sharded tables easier in SQL. 3. when the Connector application system is running, each SQL command is sent to the database through connector. The connector used by MySQL Fabric is the same as that used by a single-host MySQL database. However, in the newer versions of the connector, fabric aware connector has some more database server farms (database server farm). Enable them to check the server farm configuration in the management node of MySQL Fabric as a XML-RPC protocol when establishing a database connection, and then deliver the query under the connection to the appropriate database according to fabric instructions. As a result, the common database shard solution puts the proxy that may cause performance bottleneck in connector, thus solving this problem. Currently, MySQL Fabric supports java, python, and PHP, that is, ctor/J, Connector/Python, and Connector/PHP are both Fabric-aware. Take java as an example. The JDBC driver must be of Connector/J 5.1.30 or later versions. The Java program of Fabric is similar to the Java program for querying a single-host MySQL, only when the database connection object is created, the database connection URL does not point to the database. Instead, it points to the MySQL Fabric management node (the server's IP address and port are usually 32274 ). When a global table (without table shard) or DDL (for example, creating a table or modifying the table structure) is queried, the ''fabricservergroup = "parameter must be added to the connection object, then, the SQL commands under this connection object will be sent to the master database of the Global Group, and then copied from the database to other high-availability groups (shard. If the table to be operated by the SQL command is a shard table, the ''fabricshardtable = "parameter must be added to the connection object, then, the SQL command under this connection object will be sent to the high-availability group of each shard according to the sharding principle set by MySQL Fabric. In this way, the application does not need to determine the database to be sent in SQL for the SQL commands under these shard tables, it is entirely determined by the configuration information of the server farm (which database belongs to which shard group and the sharding principles of each shard table) that the connector detects to MySQL Fabric when establishing a database connection. This configuration is cached on the application end where Connector is located after the master connection is established. In this way, you do not need to repeatedly query the MySQL Fabric management node every time you run the SQL command. Instead, the table Shard configuration at the application end is directly sent to the correct database. The efficiency of applications will not be reduced by splitting tables. Conclusion MySQL Fabric has been released for more than two months and has attracted the attention and use of many heavyweight MySQL users. Oracle does not want to invest more in it to accelerate its function improvement. A new version has been released, and support for SSL connections is added to the latest MySQL Fabric version, in the near future, the focus of Fabric improvement will be on making Fabric more transparent to applications (for example, a single SQL statement can query the shard table across shard), supports more high-availability solutions, and provides a more easy-to-use GUI. We recommend that you pay attention to the development of MySQL and try it out. Report your comments and experiences to Oracle. if you are satisfied with the functions and stability provided by Oracle, you can add it to your production system for official operation, the Fabric development team will welcome any contributions made to the new members of the MySQL family. The purpose of this article is to introduce the problem to be solved by MySQL Fabric and The Fabric architecture. for detailed settings and operations, please share with you an example in the next article.