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

This article is about how to use DAL middleware in MySQL. Xiaobian thinks it is very practical, so share it with everyone to learn. I hope you can gain something after reading this article. Let's not say much. Let's take a look at it together with Xiaobian.

Atlas is a MySQL protocol-based data middle tier project developed and maintained by Qihoo 360's Web Platform Infrastructure Team. It is based on MySQL's official MySQL-Proxy version 0.8.2, modified a lot of bugs, and added a lot of features. At present, the project has been widely used in 360 Company. Many MySQL businesses have been connected to Atlas platform, and the number of read and write requests carried every day reaches billions. At the same time, more than 50 companies have deployed Atlas in production, and more than 800 people have joined our developer community, and those numbers are growing.

2.2. Atlas architecture

Atlas is a middleware that sits between applications and MySQL. Atlas looks like a client to the backend DB and looks like a DB to the front-end application. Atlas communicates with applications as a server, implementing MySQL's client and server protocols, and communicates with MySQL as a client. It shields DB details from applications and maintains connection pools to reduce MySQL burden.

2.3 Main functions

1. read-write separation

2. Slave Library Load Balancer

3. IP filtering

4. automatic sorting table

5. DBA can smooth the upper and lower lines DB

6. Automatic removal of downed DB

Atlas advantages over official MySQL-Proxy

1. Rewrite all Lua code in the main flow with C, Lua is only used for management interfaces

2. Rewrite network model, thread model

3. A true connection pool is implemented

4. Optimized locking mechanism, performance improved dozens of times

3、Mysql router

Official website: dev.mysql.com/doc/mysql-router/en/

3.1 Introduction to mysql router

MySQL Router is the official database middleware released by mysql. It is a lightweight agent between application client and dbserver. It can detect, analyze and forward queries to backend database instances and return the results to client. It is an alternative to mysql-proxy. Its architecture and functions are as follows.

3.2 mysql router architecture

1. Router to achieve read-write separation, the program is not directly connected to the database IP, but fixed connection to the mysql router. MySQL Router is transparent to front-end applications. The application treats MySQL Router as an ordinary MySQL instance, sends queries to MySQL Router, and MySQL Router returns query results to the front-end application.

2. From database server failure, business can run normally. MySQL Router automatically logs off unavailable servers. The program configuration does not require any modification.

3. When the master database fails, MySQL Router decides to automatically switch between master and slave, and the business can be accessed normally. The program configuration does not require any modification.

MySQL Router read/write separation principle:

After MySQL Router receives the request of the front-end application program, it distinguishes between reading and writing according to different ports, sends all queries connected to the read-write port to the master library, and sends select queries connected to the read-only port to multiple slave libraries in a polling manner, thus realizing the purpose of separating reading and writing. The results of the read and write are passed to MySQL Router, which returns them to the client application.

3.3. mysql router's main functions

MySQL Router's main uses are read-write separation, automatic failover of master and master, Load Balancer, connection pooling, etc.

4、Mycat

Official website: www.mycat.org.cn/

4.1 Mycat Profile

Mycat is based on the evolution of open source cobar, a thorough refactoring of cobar code, the use of NIO refactoring of the network module, and optimization of the Buffer kernel, enhanced aggregation, Join and other basic features, while compatible with most databases to become a common database middleware. 1.4 After the version, it completely broke away from the basic cobar kernel, combined with Mycat cluster management, automatic expansion, intelligent optimization, and became a high-performance middleware.

A completely open source, large database cluster for enterprise application development

Enhanced database that supports transactions, ACID, and MySQL

An enterprise-class database that can be considered a MySQL cluster to replace expensive Oracle clusters

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

A new generation of enterprise database products combining traditional databases and new distributed data warehouses

A Novel Database Middleware Product

4.2 mycat architecture

4.3 Mycat's main function

Support SQL92 standard

Common middleware agent that complies with Mysql native protocols, across languages, platforms, and databases.

Heartbeat based automatic failover, support for read/write separation, MySQL master-slave, and galera cluster.

Support Galera for MySQL cluster, Percona Cluster or MariaDB cluster

Based on Nio implementation, effective thread management, high concurrency issues.

Supports automatic routing and aggregation of multiple pieces of data, and supports common aggregation functions such as sum,count,max, etc.

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

It supports fragmentation strategy through global tables and ER relationships, and realizes efficient multi-table join queries.

Support multi-tenant solutions.

Support for distributed transactions (weak xa).

Support global serial number, solve the problem of primary key generation under distributed.

Fragment rules rich, plug-in development, easy to expand.

Powerful web, command line monitoring.

The front-end is supported as mysq universal proxy, and the back-end JDBC mode supports Oracle, DB2, SQL Server, mongodb and giant fir.

Support password encryption

Support service degradation

Support IP whitelisting

Support SQL blacklist, SQL injection attack interception

Support sub-table (1.6)

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

Mysql+Mycat Architecture Actual Combat Please stamp Mysql+Mycat to achieve database master-slave synchronization and read-write separation

5、Cobar

Official website: github.com/alibaba/cobar/wiki

5.1. Introduction to cobar

Cobar is a middleware that provides distributed services for relational databases (MySQL), allowing traditional databases to scale linearly well and appear to be a database transparent to applications. The product has been running stably in Alibaba for more than 3 years. Took over 3000+ MySQL database schemas. Clusters process more than 5 billion online SQL requests per day. Cluster daily processing online data traffic TB level above.

5.2. Cobar architecture

5.3 Status of Cobar

In 2013, Ali Cobar was found to have some serious problems in the process of community use, and its use restrictions were later improved on the basis of cobar to produce mycat, which is the replacement version of cobar at present, and there is no version update after 2013.

6、Amoeba

Official website: docs.hexnova.com/amoeba/

6.1 About amoeba

The Amoeba project, an open source framework that started releasing Amoeba for Mysql software in 2008. This software is dedicated to MySQL's distributed database front-end proxy layer, which mainly acts as SQL routing function when accessing MySQL at the application layer, focusing on distributed database proxy layer development. Located between Client and DB Server(s), transparent to clients. Load Balancer, high availability, SQL filtering, read-write separation, routable correlation to the target database, concurrent request for multiple database consolidation results. With Amoeba you can accomplish high availability, Load Balancer, and data slicing for multiple data sources

6.2 amoeba architecture

6.3 Amoeba status quo

The author has stopped maintaining it.

7、Mysql proxy

7.1. mysql proxy

MySQL Proxy is a simple program that sits between your client side and MySQL server side and can monitor, analyze, or alter their communications. It is flexible and has no restrictions. Common uses include: Load Balancer, Fault, Query Analysis, Query Filtering and Modification, etc. MySQL Proxy is such a middle-layer proxy, simply put, MySQL Proxy is a connection pool, responsible for forwarding connection requests from foreground applications to the database in the background, and through the use of lua scripts, complex connection control and filtering can be implemented to achieve read and write separation and load balancing. For applications, MySQL Proxy is completely transparent, and applications only need to connect to MySQL Proxy's listening port. Of course, this proxy machine may become a single point of failure, but it is entirely possible to use multiple proxy machines as redundancy, and configure the connection parameters of multiple proxies in the connection pool configuration of the application server. One of MySQL Proxy's more powerful features is to achieve "read-write separation". The basic principle is to let the master database handle transactional queries and let the slave database handle SELECT queries. Database replication is used to synchronize changes caused by transactional queries to the slaves in the cluster.

7.2 Status of MySQL proxy

Since mysql router appeared on mysql official website, mysql proxy has stopped maintenance.

mysql proxy architecture actual combat ProxySQL+Mysql database read and write separation actual combat

8. Client-side fragmentation

8.1 Introduction to client fragmentation

The client side of the program divides the database into tables. That is, directly in the program inside the database and table splitting, such as user tables. According to the UID of the user, for example, 13678789, according to the last bit, it can be divided into 10 databases from 0 to 9. The ones ending with 0 are stored in db_user_0 database, and the ones ending with 1 are stored in db_user_1 database. The same is true when selecting. Then according to the penultimate digit, it can be divided into 0-9 total 10 tables, according to the penultimate tail number written in the corresponding table. For example, the UID information of 13678789 is written in the table_user_8 table of db_user_9 database.

8.2, advantages

(1)No middleware required No impact on performance (2) Controlled by code, highly definable

The above is how to use DAL middleware in MySQL, Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.