Home > Article > Database > Let's talk about Mycat's implementation of Mysql cluster read and write separation

Let's talk about Mycat's implementation of Mysql cluster read and write separation

WBOYforward: 2022-01-21 18:11:062797browse

This article introduces you to the relevant knowledge about MySQL read and write separation, I hope it will be helpful to you.

Overview of MySQL read-write separation

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 working principle of read-write separation

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.

##Why read and write separation

Faced with increasing access pressure, the performance of a single server has become a bottleneck, and the load needs to be shared
The master and slave are only responsible for their own writing and reading, to a great extent Alleviate X (write) lock and S (read) lock contention
The myisam engine can be configured from the library to improve query performance and save system overhead
Increase redundancy and improve availability

How to achieve read and write separation

Application layer implementation, website program implementation
Application layer implementation refers to the separation of reading and writing within the application and in the connector

Advantages:
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
Disadvantages:
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

Middleware layer implementation:

Middleware layer implementation refers to the separation of reading and writing in the external middleware program
Common middleware programs

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.

What is MyCAT

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 service installation and configuration

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/

Experimental architecture:
192.168.2.2 Mycat CentOS 8.3.2011
192.168.2.3 Main server CentOS 7.6
192.168.2.5 Slave server CentOS 7.6
Running Mycat requires JDK 1.7 or above

Download Mycat
wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
tar xf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /usr/local/
sudo useradd -M -N -s /sbin/nologin mycat && echo "123456" | sudo passwd --stdin mycat
sudo chown -R mycat. /usr/local/mycat/

bin program directory, run under Linux: ./mycat console, first chmod x *
Note: Mycat supports commands { console | start | stop | restart | status | dump }
conf Configuration files are stored in the directory: server.xml is the configuration file for Mycat server parameter adjustment and user authorization, schema.xml is the configuration file for logical library definitions, tables and sharding definitions, rule.xml is the configuration file for sharding rules, and schema.xml is the configuration file for sharding rules. Some specific parameter information of the slice rules are stored separately as files, which are also in this directory. If the configuration file is modified, Mycat needs to be restarted to take effect.
The lib directory mainly stores some jar files that mycat depends on.
The log is stored in logs/mycat.log, one file per day. The log configuration is in conf/log4j.xml. According to your own needs, you can adjust the output level to debug. At the debug level, more will be output. information to facilitate troubleshooting.

MyCat service startup and startup settings

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

Start the service
/usr/local/mycat/bin/mycat start
cat /usr/local /mycat/logs/wrapper.log

mycat’s user account and authorization information are in Configure in the conf/server.xml file

vim /usr/local/mycat/conf/server.xml
What is defined here is the username and password for logging in to mycat on 192.168.2.2. The name can be customized. The mysqld service is not running on 192.168.2.2. The database name specified in the schemas is a database that must exist on the server side!

Edit MyCAT configuration file schema.xml, about the configuration information of dataHost As follows:

Back up the original configuration file
\cp /usr/local/mycat/conf/schema.xml{,.bak}
Edit the configuration file
vim /usr/local/mycat/conf/schema.xml

http://io.mycat/"> Pay attention to the URL here, Wrong writing will cause startup failure!
                                                                                                                                                             < "dn1" dataHost="dthost" database="mydata"/>
                                                                                                                                                                   balance="1"
writeType= "0" dbType="mysql" dbDriver="native"
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
##GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%' IDENTIFIED BY '123456';
Or specify the network segment
##If this error is reported, the server is running normally , first check whether there is authorization
ERROR 1184 (HY000): Invalid DataSource:0

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;

Define user and system-related variables, such as ports, etc. in MYCAT_HOME/conf/server.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

schema, it will not be removed, so it is officially recommended not to use this syntax. Also set to false by default.

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

Restart the service
/usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Stopped Mycat -server.
Starting Mycat-server...
tail /usr/local/mycat/logs/wrapper.log

##Configuring MySQL master-slave

First on the main database side 192.168.2.3 Edit /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-bin=/data/mysql/mysql-bin

server-id=1 binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
innodb_flush_log_at_trx_commit=1
binlog-do-db=
mydata replicate-do-db=
mydata
Note that the database to be synchronized must exist in advance

#After starting without error, configure it on the slave server 192.168.2.5 /etc/my.cnf
vim /etc/my.cnf  1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-bin=/data/mysql/mysql-bin
server-id=2
relay-log-index=/data/mysql/slave-relay-bin.index
relay-log=/data/mysql/slave-relay-bin
lower_case_table_names=1

read_only =1 Turn on read-only mode to prevent data writeback and will not affect slave synchronous replication
lower_case_table_names=1 Turn on case matching

After restarting the slave database service, perform the following operationsStop the slave of the slave server and create the slave database user
mysql -uroot -p123456 - e "stop slave"
mysql -uroot -p123456 -e "select user ,password from mysql.user"

mysql -uroot -p123456 -e "change master to master_host='192.168.2.3',master_user=' slave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=245;"

mysql -uroot -p123456 -e "show slave status"

Test
First export the backup of all libraries of the main server 192.168.2.3
mysqldump -uroot -p --all-databases > /tmp/all_dbs.sql
##Then import from server 192.168.2.5
##mysql -uroot -p < ; /tmp/all_dbs.sql
Add some data on the main database side 192.168.2.3 and observe whether the secondary database is synchronized

Check on the slave server

If synchronization errors occur, you need to stop slave on the slave server and then change master again

##Use slave User login test

## Return to mycat server 192.168.2.2

Try to log in
mysql -uroot -p123456 -h192.168.2.2 -P8066
8066 is the port number when mycat is running

Test read-write separation

mysql -uroot -p123456 -h192.168.2.2 -P9066 -e "show @@datasource"
9066 is mycat management port
select * from mydata.mylist;

##Write data or change data

##Simulate a fault, first stop the slave server 192.168.2.5

systemctl stop mariadb.service
Try to write data on 192.168.2.2

insert into mydata.mylist values( 7,'gf');

View on main server 192.168.2.3

Open the slave server 192.168.2.5

##Simulate the master server 192.168.2.3 down

- The query is normal, try to write data
- The query is normal but cannot be written
- Add multiple libraries
- vim cat /usr/local/mycat /conf/server.xml
- ,, ,
##vim /usr/local/mycat/conf/schema.xml
Restart the service after adding
- /usr/local/mycat/bin/mycat restart
- tail / usr/local/mycat/logs/wrpper.log
##Error handling
- Startup failed: Timed out waiting for a signal from the JVM.
  JVM did not exit on request, terminated
  Solution Add# to wrapper.conf
  ##wrapper.startup.timeout=300
  
  //Timeout 300 seconds wrapper.ping.timeout=120
- mysql video tutorial

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!

Statement：

This article is reproduced at:csdn.net. If there is any infringement, please contact admin@php.cn delete

Previous article：Let’s talk about how to enable remote access in mysqlNext article：Let’s talk about how to enable remote access in mysql

See more

Let's talk about Mycat's implementation of Mysql cluster read and write separation

Overview of MySQL read-write separation

The working principle of read-write separation

##Why read and write separation

How to achieve read and write separation

Middleware layer implementation:

What is MyCAT

MyCat service installation and configuration

MyCat service startup and startup settings

mycat’s user account and authorization information are in Configure in the conf/server.xml file

What is defined here is the username and password for logging in to mycat on 192.168.2.2. The name can be customized. The mysqld service is not running on 192.168.2.2. The database name specified in the schemas is a database that must exist on the server side!

Edit MyCAT configuration file schema.xml, about the configuration information of dataHost As follows:

##Configuring MySQL master-slave

First on the main database side 192.168.2.3 Edit /etc/my.cnf

#After starting without error, configure it on the slave server 192.168.2.5 /etc/my.cnf

Test

First export the backup of all libraries of the main server 192.168.2.3

mysqldump -uroot -p --all-databases > /tmp/all_dbs.sql

##Then import from server 192.168.2.5

##mysql -uroot -p < ; /tmp/all_dbs.sql

systemctl stop mariadb.service

Related articles