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

This article mainly introduces how to use mycat to achieve mysql database read-write separation, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

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

Key features of MyCAT

Support for SQL92 standards

Common SQL syntax that supports DB such as MySQL, Oracle, DB2, SQL Server, PostgreSQL, etc.

Comply with MySQL native protocol, cross-language, cross-platform, cross-database general middleware agent.

Automatic failover based on heartbeat, support for read-write separation, support for MySQL master-slave, and galera cluster clustering.

Support Galera for MySQL cluster, Percona Cluster or MariaDB cluster

Based on Nio implementation, effectively manage threads and solve the problem of high concurrency.

Support automatic routing and aggregation of multiple pieces of data, support common aggregation functions such as sum,count,max, and support paging across databases.

Support arbitrary join within a single library, support cross-library 2-table join, and even multi-table join based on caltlet.

It supports the slicing strategy of global table and ER relationship, and realizes efficient multi-table join query.

Support the multi-tenant scheme.

Support for distributed transactions (weak xa).

Support for XA distributed transactions (1.6.5).

Support global serial number to solve the problem of primary key generation in distributed environment.

Slicing rules are rich, plug-in development, easy to expand.

Powerful web, command line monitoring.

The frontend is supported as a general proxy for MySQL, and the backend JDBC supports Oracle, DB2, SQL Server, mongodb, and giant sequoia.

Support password encryption

Support service degradation

Support IP whitelist

Support SQL blacklist and SQL injection attack interception

Support for prepare precompiled instructions

Supports non-heap memory (Direct Memory) aggregate computing

Native protocol that supports PostgreSQL

Support for MySQL and oracle stored procedures, out parameters, multiple result set returns

Support zookeeper coordination master-slave handover, zk sequence, configuration zkization

Support for sub-tables in the library

The cluster is based on ZooKeeper management, online upgrade, expansion, intelligent optimization, big data processing (2.0 development version).

1. Here, based on the implementation of mysql master-slave replication, mycat is used to separate read and write. The architecture figure is as follows:

2 、 Demo

2.1 create a database on mysql master and create a db1

2.2 create table student in database db1

At the same time, because mysql master-slave replication is configured, there are the same databases and tables on mysql slave.

2.3 Editing the configuration file server.xml for mycat

Test TESTDB user TESTDB true

2.4 Editing the configuration file schema.xml for mycat

Select user ()

At this point, the separation of read and write using mycat has been configured.

Notice the following three attributes of the dataHost node

Balance, switchType, writeType

Balance= "0", the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost.

Balance= "1", all readHost and stand by writeHost participate in the load balancing of select statements. To put it simply, when the double master and double slave mode (M1-> S1J M2-> S2, and M1 and M2 are the master and standby of each other), under normal circumstances, M2M S1jue S2 is involved in the load balancing of select statements.

Balance= "2", all read operations are randomly distributed on writeHost and readhost.

Balance= "3". All read requests are randomly distributed to the readhost under writeHost for execution. WriteHost does not bear the read pressure.

WriteType indicates write mode

WriteType= "0", all operations are sent to the first writehost configured

WriteType= "1", randomly sent to all configured writehost

WriteType= "2", do not perform write operation

SwitchType refers to the switching mode. Currently, there are 4 values:

SwitchType='-1' means no automatic switching.

Default value of switchType='1', which indicates automatic switching

SwitchType='2' decides whether to switch based on the status of MySQL master-slave synchronization, and the heartbeat statement is show slave status.

SwitchType='3' 's MySQL galary cluster-based handover mechanism (suitable for clustering) (1.4.1), with a heartbeat statement of show status like 'wsrep%'.

Thank you for reading this article carefully. I hope the article "how to use mycat to achieve the separation of reading and writing in mysql database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.