Home >Database >Mysql Tutorial >Let's talk about Mycat's implementation of Mysql cluster read and write separation
This article introduces you to the relevant knowledge about MySQL read and write separation, I hope it will be helpful to you.
MySQL is currently the most widely used free database in the world. I believe that all people engaged in system operation All engineers from Weiwei must have come into contact with it.
In the actual production environment, a single MySQL as an independent database is completely unable to meet actual needs, whether in terms of security, high availability, high concurrency and other aspects.
Therefore, generally speaking, data is synchronized through Master-Slave replication, and then through Separation of reading and writing (MySQL-Proxy/Amoeba) to improve the concurrent load capacity of the databaseDeploy and implement.
The basic principle is:
The main database handles transactional add, modify and delete operations (INSERT, UPDATE, DELETE)
Processing SELECT query operations from the database
Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.
The application implements read and write separation internally, and can be used during installation
Reduce certain deployment difficulty
The access pressure is below a certain level and the performance is very good
Once the architecture is adjusted, the code must change accordingly
It is difficult to implement advanced applications, such as automatic database sharding and table sharding
Unable to apply to large application scenarios
Kingshard was developed by Chen Fei, a former member of the 360Atlas middleware development team, using the go language in her spare time. There are currently about 3 people involved in the development. At present, it is not a mature product that can be used. It needs to be continuously improved.
Atlas：
360 team rewrote lua in C based on mysql proxy. The original version supports table sharding, and a sharded database and table version has been released. I saw some friends on the Internet often saying that it often hangs under high concurrency. If you want to use it, you need to test it in advance.
MaxScale and MySQL Route:
These two middleware are considered official, MaxScale is a version maintained by mariadb (the original author of MySQL ), the current version does not support sub-databases and tables. MySQL Route is a middleware now released by MySQL official Oracle company.
Advantages:
The architecture design is more flexible
Can achieve some advanced control on the program , such as: transparent horizontal splitting, failover, and monitoring can rely on technical means to improve mysql performance. It has little impact on business code and is also safe
Disadvantages:
Requires the support of a certain development and operation team.
A completely open source, enterprise-oriented application The developed large database cluster
supports transactions, ACID, and an enhanced version of the database that can replace MySQL
An enterprise-level database that can be regarded as a MySQL cluster Database, used to replace expensive Oracle clusters
A new SQL Server that integrates memory caching technology, NoSQL technology, and HDFS big data
combination A new generation of enterprise-level database products for traditional databases and new distributed data warehouses
A novel database middleware product
MyCat provides compiled installation packages that support Windows, Linux, Mac, Solaris and other systems Install and run on
official download homepage http://www.mycat.org.cn/
When deploying and starting MyCAT in Linux, you first need to Configure MYCAT_HOME in the environment variable of the Linux system. The operation method is as follows:
sudo vim /etc/profile.d/mycat.sh
MYCAT_HOME=/usr/local/mycat PATH= $MYCAT_HOME/bin:$PATH
Enable environment variables to take effect
. /etc/profile.d/mycat.sh
vim /usr/local/mycat/conf/server.xml
vim /usr/local/mycat/conf/schema.xml writeType= "0" dbType="mysql" dbDriver="native"
< "dn1" dataHost="dthost" database="mydata"/>
switchType="-1" slaveThreshold="100"> /heartbeat> # Host> ;
Force all read operations to run on the read server, and only switch to the write server when data is written
Note that the mycat user here must be authorized on 192.168.2.3 and 2.5 on the master-slave database
schema：
Logical library, the same as in MySQL Database corresponds to the Table included in a logical library.
table: Table, that is, a table stored in a physical database. Different from traditional databases, the table here needs to declare the logical data node DataNode it stores. This is through the sharding of the table. To achieve this through rule definition, a table can define the "childTable" to which it belongs. The sharding of the child table depends on the specific sharding address of the "parent table". Simply put, it belongs to a certain record in the parent table. All records of A's subtables are stored on the same shard as A. Sharding rule: It is a bundled definition of a field and a function. Based on the value of this field, the sequence number of the stored shard (DataNode) is returned. Each table can define a sharding rule, and the sharding rule can be flexible. Extension, providing number-based sharding rules, string sharding rules, etc. by default.
dataNode: The logical data node of MyCAT is the specific physical node where the table is stored. It is also called the shard node. It is associated to a specific back-end database through DataSource. Generally speaking, For high availability, each DataNode is equipped with two DataSources, one master and one slave. When the master node goes down, the system automatically switches to the slave node.
dataHost: Define the access address of a physical library for binding to dataNode.
MyCAT currently defines logical libraries and related configurations through configuration files: MYCAT_HOME/conf/schema.xml defines logical libraries, tables, shard nodes, etc.; MYCAT_HOME/conf Define fragmentation rules in /rule.xml;
Note:
The schema tag is used to define the logical library in the MyCat instance. name: is followed by the logical library name. MyCat can have multiple logical libraries, and each logical library has its own related configuration. You can use schema tags to divide these different logical libraries.
checkSQLschema This attribute defaults to false. The official document means whether to remove the name of the database in front of the table, "select * from db1.testtable". If set to true, db1 will be removed. However, if the name of db1 is not the name of
sqlMaxLimit When the value is set to a certain number. For each executed SQL statement, if no limit statement is added, MyCat will automatically add the corresponding value. For example, if you set the value to 100 and execute "select * from test_table", the effect will be
"selelct * from test_table limit 100".
The dataNode label defines the data node in MyCat, which is what we usually call data. Fragmentation
##Use slave User login test
Test read-write separation
##Write data or change data
##Simulate a fault, first stop the slave server 192.168.2.5
insert into mydata.mylist values( 7,'gf');
The above is the detailed content of Let's talk about Mycat's implementation of Mysql cluster read and write separation. For more information, please follow other related articles on the PHP Chinese website!