Shulou(Shulou.com)06/01 Report--

The previous article talked about the installation and configuration of mycat. I believe friends who have been verified already know that the practice of the previous article has achieved the effect of separating master-slave replication from reading and writing. Here, we will describe the following read-write separation part in detail:

My architecture is still a dual-master and double-slave architecture: (experimental environment 1, master and slave) the effect is the same, that is, I want to read the whole document as in the previous article, and the errors in the middle are explained after:

Server IP describes the Mycat192.168.0.2mycat server, when connecting to the database, connect to this server database1192.168.0.3 physical database 1, database database2192.168.0.4 physical database 2 that really stores data, and database that really stores data

Mycat as the main database middleware, must be weakly associated with the code, so the code does not need to be modified, after using Mycat, the connection to the database is unchanged, the default port is 8066. The connection method is the same as ordinary database, such as jdbc:mysql://192.168.0.2:8066/

Server.xml

Example

Test lunch false

Focus on the following paragraph, other defaults are fine.

The parameter describes the user user configuration node-- the user name of the name login, that is, the user name of the connection Mycat-- the password of the password login, that is, the password of the connection Mycat-- the name of the schemas database. Here, it will be associated with the configuration in schema.xml, and multiple databases are separated by commas. For example, if you need to manage two database db1,db2, configure db1,dbs--privileges to configure the user's permission to add, delete, modify and query tables. For more information, please see the document.

I have configured an account test password is also test, for the database lunch, read and write permissions, do not have any special permissions for the table.

Schema.xml

Schema.xml is the most important configuration item. Look at my configuration file first.

Select user (); select user (); parameters describe the schema database settings. This database is a logical database. Name and schema in server.xml correspond to dataNode sharding information, that is, the physical database of the configuration dataHost related to the sub-database, and the database that really stores the data.

The attributes of each node are described one by one:

Schema:

The attribute describes the name logical database name, which corresponds to the checkSQLschema database prefix of schema in server.xml. It is recommended to see the document. It is temporarily set to the default limit for folsesqlMaxLimitselect to avoid querying the whole table.

Table:

The attribute describes the name of the name table, the nodes to which the table name dataNode table is stored in the physical database, and multiple nodes separated by commas. The node is the name of the nameprimaryKey primary key field set by dataNode below. When generating the primary key automatically, you need to set whether autoIncrement increments the name of rule sharding rule. The specific rules are described in detail in rule below.

DataNode

Attribute description name node name, datahost physical database name corresponding to dataNode in table, database name in database physical database corresponding to name in datahost

DataHost

Attribute describes the name of the name physical database and the balance load balancing method corresponding to dataHost in dataNode. WriteType write mode dbType database type heartbeat heartbeat detection statement. Note that the semicolon at the end of the statement should be added. Application scene database sub-table and sub-database

The configuration is as follows:

Select user (); select user ()

I have database lunch at 192.168.0.2 and 192.168.0.3.

The tables lunchmenu, restaurant, userlunch, and users are written only to node dn1, which is the service 192.168.0.2, while dictionary writes to dn1 and dn2 nodes, that is, 192.168.0.2 and 192.168.0.3 servers. The rule of slicing is: mod-long.

Mainly focus on the rule attribute, rule attribute content from the rule.xml file, Mycat supports 10 sub-table sub-library rules, basically meet your needs, this must be praised, other database middleware does not seem to have so many.

The rule attribute in table corresponds to the name of tableRule in the rule.xml file. It is recommended to take a look at the document for the implementation of which sub-tables and sub-libraries. The mod-long I choose here is to split the data evenly. Because my backend consists of two physical libraries, the function count corresponding to mod-long in rule.xml is 2, as shown in the following code:

Id mod-long 2 database read-write separation

The configuration is as follows:

Select user ()

This configuration is changed from the previous example configuration as follows:

Removed the rule assigned by table, and there is only one datanode

There is only one datahost, but writehost always adds readhost,balance to 1, which means read-write separation.

The effect of the above configuration is 102.168.0.2 as the master library and 192.168.0.3 as the slave library.

Note: Mycat master-slave separation is only processed when reading. When writing data, it is only written to writehost, and the data needs to be copied to readhost through master-slave replication of mycat. At that time, I struggled with this problem for a long time. After the data was written into writehost, readhost had no data and thought it was a problem of its own configuration. Later, I found that Mycat did not achieve the function of master-slave replication. After all, this function of the database itself is the most efficient and stable.

As for other scenarios, such as master-slave and sub-table and sub-library are also supported, as long as you understand this implementation and then modify the configuration, it can be achieved. And hot backup and failure professional official recommendation to use haproxy together, you can try.

Use

The startup of Mycat is also simple, with the startup command in the Bin directory:

# # start mycat start##, stop mycat stop## and restart mycat restart

If you find an exception at startup, check the log in the logs directory.

Wrapper.log is the log of the startup of the program. For problems at startup, see this.

Mycat.log is the log of the execution of the script. Check this file for the specific error content after the execution of the SQL script. Mycat.log is the latest error log, and the history log is saved according to the directory generated by time.

After the mycat starts, the execution of the command is not successful, and there may actually be an error in the configuration, resulting in the subsequent command not being well executed.

The biggest advantage of Mycat is that there is no need to modify the original code at all. after mycat is started by command, you only need to switch the database connection to the address of Mycat. You can connect as follows:

Mysql-h292.168.0.1-P8806-uroot-p123456

After the connection is successful, you can execute the sql script.

Therefore, you can connect and execute scripts directly through sql management tools (such as navicat, datagrip). I have been using datagrip for simple daily management, which is very convenient.

Mycat also has a managed connection, the port number is 9906.

Mysql-h292.168.0.1-P9906-uroot-p123456

After connecting, you can check the operation of Mycat according to the management commands. Of course, people who like UI management can install a Mycat-Web for management and are interested in searching on their own.

In short, there is no difference between using Mycat in development and using Mysql directly.

common problem

I will always encounter some pitfalls after using Mycat. I will list some of the problems I have encountered here, hoping to resonate with you:

After Mycat is configured, can you completely solve the problem of sub-table, sub-database and read-write separation?

With the replication function of the database itself, Mycat can solve the problem of read-write separation, but it is not a perfect solution to the problem of sub-table and sub-database. Or, so far, there is no perfect solution in the industry.

The writing of sub-table and sub-database can be solved perfectly, but it can not perfectly solve the problem of join table query. Mycat supports the query of two table join tables, but not the query of more than two tables. In fact, many database middleware about the query after sub-table and sub-database, all need to be implemented on their own, and the abridged version does not support join table query, Mycat has been regarded as very advanced.

The query problem of sub-table and sub-database can be avoided by reasonable database design.

Which databases are supported by Mycat and can other platforms such as. Net and PHP be used?

Officials said that the supported databases include MySQL, SQL Server, Oracle, DB2, PostgreSQL and other mainstream databases, which are great.

Try to use Mysql, I have tried SQL Server, there will be some minor problems, because some of the syntax is a little different.

Can Mycat non-JAVA platforms such as. Net and PHP work?

You can use it. MyCat did a great job on this, too.

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.