Shulou(Shulou.com)05/31 Report--

This article is about how Mycat achieves read-write separation in Mysql clusters. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

An overview of MySQL read-write separation

MySQL, as the most widely used free database in the world, is believed to have been contacted by all engineers engaged in system operation and maintenance.

In the actual production environment, a single MySQL as an independent database can not meet the actual needs, whether in security, high availability, high concurrency and other aspects.

Therefore, generally speaking, it is through master-slave replication (Master-Slave) to synchronize data, and then through read-write separation (MySQL-Proxy/Amoeba) to improve the concurrent load capacity of the database for deployment and implementation.

Working principle of read-write separation

The basic principles are:

The master 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 should read and write be separated?

In the face of increasing access pressure, the performance of a single server has become a bottleneck and needs to share the load.

The master and slave are only responsible for their own writing and reading, greatly alleviating the contention between X (write) lock and S (read) lock.

Myisam engine can be configured from the library to improve query performance and save system overhead.

Increase redundancy and availability

The way to achieve the separation of reading and writing

There are generally two ways to implement it.

Application layer implementation, website program implementation

Application layer implementation refers to the separation of read and write within the application and in the connector.

Advantages:

The separation of read and write is implemented within the application, and the installation can either use the

Reduce the difficulty of deployment

The access pressure is below a certain level and the performance is very good.

Disadvantages:

Once the architecture is adjusted, the code will change accordingly.

It is difficult to realize advanced applications, such as automatic database division and table division.

Unable to apply to large-scale application scenarios

Middleware layer implementation:

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

Common middleware programs

Cobar:

Alibaba B2B developed a relational distributed system that manages nearly 3000 MySQL instances. After Ali withstood the test, there was no one to maintain cobar due to the author's walking away, and Ali also developed tddl to replace cobar.

MyCAT:

Community enthusiasts carried out secondary development on the basis of Ali cobar, solved some problems existing in cobar at that time, and added many new functions in it. At present, the MyCAT community is very active, and some companies are already using MyCAT. Overall support ratio

Higher, it will be maintained all the time.

OneProxy:

Database industry Daniel, the former Alipay database team leader building chief development, based on mysql official proxy ideas using c for development, OneProxy is a commercial charge for middleware, the building gave up some function points, focusing on performance and stability. Someone measured

I tried to say it was stable under high concurrency.

Vitess:

This middleware is used by Youtube production, but the architecture is very complex. Different from the previous middleware, the use of Vitess application changes a lot. To use the API interface of the language provided by him, we can learn from some of his design ideas.

Kingshard:

Kingshard was developed by Chen Fei of the former 360Atlas middleware development team in the go language in his spare time. At present, there are about 3 people involved in the development. At present, it is not a mature product that can be used and needs to be constantly improved.

Atlas:

The team rewrote lua in C based on mysql proxy. The original version supports sub-tables, and the sub-database sub-table version has been released. On the Internet, some friends often say that they will often hang up under high concurrency. If you want to use it, you need to do a good test in advance.

MaxScale and MySQL Route:

Both middleware are official. MaxScale was developed by mariadb (a version maintained by the original author of MySQL). The current version does not support sub-database and sub-table. MySQL Route is a middleware released by MySQL official Oracle company.

Advantages:

Architecture design is more flexible

Can achieve some advanced control in the program, such as: transparency level split, failover, monitoring can rely on technical means to improve mysql performance, less impact on business code, but also security

Disadvantages:

Need the support of a certain development operation and maintenance team.

What is MyCAT?

A thoroughly open source large database cluster for enterprise application development

Enhanced database that supports transactions, ACID, and can replace MySQL

An enterprise database that can be regarded as a MySQL cluster to replace the expensive Oracle cluster

A new SQL Server that integrates memory cache technology, NoSQL technology and HDFS big data

A New Generation of Enterprise Database products combining traditional Database and New distributed data Warehouse

A novel database middleware product

MyCat service installation and configuration

MyCat provides compiled installation packages that can be installed and run on Windows, Linux, Mac, Solaris, etc.

Official download home page http://www.mycat.org.cn/

Lab architecture:

192.168.2.2 Mycat CentOS 8.3.2011

192.168.2.3 Primary Server CentOS 7.6

192.168.2.5 CentOS 7.6 from the server

JDK 1.7 or above is required to run Mycat

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: the command supported by mycat {console | start | stop | restart | status | dump}

Conf directory stores configuration files: server.xml is the configuration file for Mycat server parameter adjustment and user authorization, schema.xml is the configuration file for logic library definition and table and sharding definition, rule.xml is the configuration file for sharding rules, and some specific parameter information of sharding rules is stored separately as files. Also in this directory, the configuration file is modified and 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 a day, and the log is configured in conf/log4j.xml. According to your needs, you can adjust the output level to debug. At the debug level, more information will be output to facilitate troubleshooting.

MyCat service startup and startup settings

When MyCAT is deployed and launched in Linux, you first need to configure MYCAT_HOME in the environment variables of the Linux system, as follows:

Sudo vim / etc/profile.d/mycat.sh

MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH

Make the environment variable effective

. / etc/profile.d/mycat.sh

Start the service

/ usr/local/mycat/bin/mycat start

Cat/ usr/local/mycat/logs/wrapper.log

The user account and authorization information of mycat is configured in the conf/server.xml file.

Vim / usr/local/mycat/conf/server.xml

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

Edit the configuration file schema.xml of MyCAT. The configuration information about dataHost is as follows:

Back up the previous configuration file

\ cp / usr/local/mycat/conf/schema.xml {, .bak}

Edit configuration file

Vim / usr/local/mycat/conf/schema.xml

Pay attention to the URL here, miswriting will start and fail!

Show slave status

Write server

Read server

Force all read operations to run on the read server and switch to the write server only when data is written

Note that all mycat users here have to authorize 192.168.2.3 and 2.5 on the master-slave database.

GRANT ALL PRIVILEGES ON *. * TO 'mycat'@'%' IDENTIFIED BY' 123456'

Or specify a network segment

GRANT ALL PRIVILEGES ON *. * TO 'mycat'@'192.168.2.%' IDENTIFIED BY' 123456'

Flush privileges

If this error is reported and the server is running normally, first check whether there is authorization.

ERROR 1184 (HY000): Invalid DataSource:0

Schema: logical library, corresponding to the Database (database) in MySQL. A logical library defines the included Table.

Table: a table, that is, a table stored in a physical database. Unlike a traditional database, a table here needs to declare its stored logical data node DataNode, which is realized through the definition of the slicing rules of the table. Table can define the "childTable" to which it belongs, and the fragmentation of the child table depends on the specific sharding address of the "parent table". That is, all records belonging to a child table of record An in the parent table are stored on the same slice as A.

Sharding rule: it is a bundled definition of a field and a function. The sequence number of the stored shard (DataNode) is returned according to the value of this field. Each table can define a sharding rule, and the sharding rule can be flexibly extended. The number-based sharding rule and string sharding rule are provided by default.

DataNode: the logical data node of the MyCAT is the specific physical node where the table is stored, also known as the sharding node. It is associated to a specific database at the back end through DataSource. Generally speaking, for high availability, each DataNode has two DataSource, one master and one slave. When the master node goes down, the system automatically switches to the slave node.

DataHost: defines the access address of a physical library, which is used to bind to the dataNode.

MyCAT currently defines logical libraries and related configurations through configuration files:

Define logical libraries, tables, sharding nodes, etc., in MYCAT_HOME/conf/schema.xml

Define sharding rules in MYCAT_HOME/conf/rule.xml

Users and system-related variables, such as ports, are defined in MYCAT_HOME/conf/server.xml.

Notes:

The schema tag is used to define the logical library in the MyCat instance. Name: followed by the logical library name MyCat, you can have multiple logical libraries, each with its own configuration. You can use the schema tag to divide these different logical libraries.

The checkSQLschema attribute defaults to false. The official document means whether or not to remove the name of the database in front of the table, "select * from db1.testtable". If set to true, db1 will be removed. But if the name of db1 is not

The name of schema, then it will not be removed, so it is officially recommended not to use this syntax. It is also set to false by default.

SqlMaxLimit when the value is set to a numerical value. For each executed SQL statement, if no limit statement is added, the MyCat will automatically add the corresponding value. For example, if you set the value to 100and execute "select * from test_table", the effect will be

"selelct * from test_table limit 100".

The dataNode tag 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

Configure MySQL Master and Slave

Install and configure mariadb on the two servers. For more information, please see: https://blog.csdn.net/gaofei0428/article/details/103829676?spm=1001.2014.3001.5501

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

/ 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

Lower_case_table_names=1 enables case matching

Note that the database that needs to be synchronized must exist in advance

Configure / etc/my.cnf on slave server 192.168.2.5 after startup is correct

Vim / etc/my.cnf  1

[mysqld]

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 enables read-only mode to prevent data from being written back and will not affect slave synchronous replication.

Lower_case_table_names=1 enables case matching

After restarting the slave database service, do the following

Stop the slave from the server and create a slave database user

Mysql-uroot-p123456-e "stop slave"

Mysql-uroot-p123456-e "grant replication slave on *. * to 'slave'@'%' identified by' 123456'"

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 "start slave"

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

The test first exports the backup mysqldump-uroot-p-- all-databases > / tmp/all_dbs.sql of all the libraries of the master server 192.168.2.3, and then imports mysql-uroot-p < / tmp/all_dbs.sql on the slave server 192.168.2.5

Add some data to the master database side 192.168.2.3 to observe whether the slave database is synchronized.

Before viewing from the server side

If there is an error in synchronization, you need to stop slave from the server, and then re-change master

Use slave user login test

Back to mycat server 192.168.2.2

Try to log in

Mysql-uroot-p123456-h292.168.2.2-P8066

8066 is the port number of the mycat runtime

Test read-write separation

Mysql-uroot-p123456-h292.168.2.2-P9066-e "show @ @ datasource"

9066 is the mycat management port

Select * from mydata.mylist

Write data or change data

Insert into mydata.mylist values (10 ~ (th))

To simulate a failure, first stop the slave server 192.168.2.5

Systemctl stop mariadb.service

Attempt to write data on 192.168.2.2

Insert into mydata.mylist values (7)

View on the primary server 192.168.2.3

Enable slave server 192.168.2.5

Simulated primary server 192.168.2.3 downtime

Query is normal, try to write data

Query is normal but cannot be written

Add multiple libraries

Vim cat/ usr/local/mycat/conf/server.xml

Mydata,wordpress

Vim / usr/local/mycat/conf/schema.xml

After adding the restart service

/ usr/local/mycat/bin/mycat restart

Tail / usr/local/mycat/logs/wrpper.log

Error reporting processing

Startup failed: Timed out waiting for a signal from the JVM.

JVM did not exit on request, terminated

Solution.

Add in wrapper.conf

Wrapper.startup.timeout=300 / / timeout 300s

Wrapper.ping.timeout=120

Thank you for reading! This is the end of the article on "Mycat how to achieve the separation of reading and writing in Mysql clusters". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.