background

Last week, we studied the database middleware MyCat and verified the deployment scheme of “MyCat single machine + MySQL master-slave replication”. This article continues to introduce the deployment process of this scheme.

The principle of MyCat

MyCat is a distributed database middleware. Like other distributed systems, data is stored on different hosts. There are four core concepts:

  1. Logical library: the same as the physical library of MySQL, the client connects to MyCat to access the database;
  2. Logical table: a table accessed by a client after connecting to MyCat in the same way as a physical table in MySQL.
  3. Shard node: DataNode, the host where the logical table resides. A logical table can specify 1-N shard nodes, and a shard node is associated with a specific physical library of a certain DataHost.
  4. Node host: DataHost, which is the host where fragment node data resides. Master/slave separation can be configured.

The core principle of MyCat is “interceptor”, which preprocesses SQL and postprocesses SQL results. The client sends an SQL request, which is intercepted by MyCat, and then performs a series of specific analyses, such as sharding analysis, routing analysis, read/write separation analysis, cache analysis, and then sends the SQL to the real back-end database. The result is processed appropriately and returned to the client.

The whole process is insensitive to the client, with the middleware to handle read and write separation. When the client code is written, there is no need to define multiple data sources, just as a normal MySQL operation.

An example from Distributed Database Architecture and Enterprise Practice — Based on Mycat Middleware is cited to illustrate the collaboration process of various concepts. In a large e-commerce website, order table data is segmented according to the province where it is located, and the data segmentation scheme after using Mycat is as follows:In this figure, the Orders table is divided into three sharding nodes Dn1, DN2 and DN3, which are distributed on two MySQL servers. The sharding scheme can be designed according to the resource quantity of database server, with 1~N servers for sharding. Enumeration is selected as the sharding rule, and its definition passes“Sharding field + Sharding function”To specify that the shard field is proV and the shard function is an enumeration string.

In the end, the focus of MyCat deployment fell on defining sharding schemes and writing configuration files.

MyCat + MySQL master-slave deployment schemes

The author summarizes four common scenarios for MyCat + MySQL master-slave deployment:

  1. Read/write separation: No fragmentation, dataNode points to a dataNode, dataNode points to the primary and secondary clusters.
  2. Separate sharding: no read/write separation, DataHost does not specify ReadHost;
  3. Fragment + single MySQL primary/secondary cluster: DataNode points to the same primary/secondary cluster represented by DataHost.
  4. Fragment + Multiple MySQL primary/secondary: DataNode points to the primary/secondary clusters represented by different datahosts.

MyCat can be configured in two ways, ZooKeeper and native XML. This section uses the local configuration file in the /mycat/conf directory as an example. The main configuration files are as follows:

  1. Schema. XML: mapping between logical libraries and tables, corresponding data sharding and sharding hosts;
  2. Server. XML: MyCat login user authentication and authorization information;
  3. Rule-xml: sharding rules for logical tables;

Determine the deployment scheme and write schema.xml according to product requirements. Due to limited resources, the author chose the third option, one master, one slave + sharding for MySQL. Schema. XML has a large number of commented out sample configurations, so we should change to the configuration consistent with our own environment.

The preparatory work

Step one, prepare the deployment media and scripts; Secondly, scehma. XML is prepared according to the sharding scheme and server information of MyCat. Step 3: Define the MyCat service access permission file server. XML using the default configuration. Add the host name of the primary and secondary MySQL servers’ IP addresses to the machine where MyCat is to be deployed, as referenced by DataHost in schema.xml:

Vi /etc/hosts hostM1 192.168.8.116 hostS1 192.168.8.117Copy the code

Deployment process

CD /home/mycat, execute the scripts in the following order:

  1. The host where MyCat is located, you need to install JDK:sh jdk_install.sh;
  2. MySQL > install primary nodesh master_install.sh 1;
  3. Create a replication account for the active node and grant the replication permission:sh master_grant.sh HostM1Pwd;
  4. Install MySQL secondary node:sh slave_install.sh 302;
  5. Set the Master information for the slave node:Sh 192.168.8.116 mysql-bin.000002 1123 HostS1Pwd;
  6. Install MyCat:sh mycat_install.sh;
  7. Upload the prepared MyCat configuration file to /usr/local/mycat/conf to override the default configuration.
  8. performstart mycatCommand to start MyCat.

These operations must be performed on different servers.

The revelation of

If you have any problems during deployment, refer to this document.

Deployment work should belong to the operation and maintenance profession, these years the author did not do less, I feel that 20% of the conventional skills + search engine, enough to cope with.

The most test of people is patience, pull out the radish pull out the mud, there is a problem, solve a problem, fortunately, the problem has been solved in the end.

Recently, I started to write my articles at 6 am and finished publishing them at 8 am. Although there is no profit, I still need to write down my network notes, in case I will use them in the future. Who knows who will be attracted to me because OF my love of taking notes online, my clear brain circuits, and my relatively smooth writing style!

Resources appendix

  1. MyCat website
  2. Simple demo: a scheme for storing large table data in fragments
  3. Read/write Separation Configuration
  4. Simultaneously sharding and read/write separation