Shulou(Shulou.com)06/01 Report--

Original text: http://www.enmotech.com/web/detail/1/714/1.html (copy the link and open the browser to view the original text)

Ink Guide: ProxySQL is a high-performance MySQL middleware with a powerful rule engine.

ProxySQL provides strong routing rules. When the application itself does not support read-write separation, DBA can configure routing rules to provide transparent read-write separation for the application. When using Keepalived + ProxySQL + Orchestrator to provide high availability as master and slave, it can effectively avoid the pain points of keepalived + double master structure causing data to be miswritten due to keepalived brain fissure.

Introduction

ProxySQL is a high-performance MySQL middleware with a powerful rule engine. Has the following characteristics:

1. Provide "intelligent" load balancing for application requests to multiple databases.

two。 The MySQL request protocol is implemented, which can provide transparent read-write separation for the application, and avoid the complex read-write separation of the application.

3. It can automatically perceive the database health status and topology and automatically route application requests to MySQL instances in a healthy state.

4. To protect the application from the changes in the complex topology of the underlying database, the master library that can automatically forward the write request and the master library and the slave library that automatically send the read request according to weight.

5. Provides the monitoring and analysis statistics of the query SQL.

6. Provides a powerful control mechanism for administrators to cache queries at the proxy layer in order to respond to queries faster, reroute queries, and even rewrite poor-quality query statements.

Module

ProxySQL component module

Qurey Processor is used to match query rules and decide whether to cache queries or blacklist queries or reroute, rewrite, or mirror queries to other hostgroup based on the rules.

User Auth provides user credentials for underlying back-end database authentication.

Hostgroup manager-responsible for managing the backend database of sending SQL requests and tracking the status of SQL requests.

Connection pool-responsible for managing back-end database connections, and connections established in the connection pool are shared by all front-end applications.

Monitoring-responsible for monitoring the health status of backend database master-slave replication delays and temporary offline abnormal database instances.

Installation

1. Download the appropriate version from https://github.com/sysown/proxysql/releases.

2.yum localinstall proxysql-1.x.rpm

3. Start ProxySQL

/ etc/init.d/proxysql start

Configuration structure

The ProxySQL configuration can be stored in the SQLite database and managed through the SQL statement, and the ProxySQL can be managed through the following three-tier configuration.

DISK: use SQLite to persist the ProxySQL configuration in case the configuration is lost after ProxySQL restart.

Memory: the configuration that exists in memory and is directly managed by the user through SQL.

Runtime: the configuration currently in use is in effect.

When using ProxySQL to modify the configuration, you can modify the configuration in Memory directly through the SQL statement, and then use the load command to load the configuration in Memory to the runtime layer to verify whether the configuration is correct. If the verification passes, the configuration can be saved to the SQLite database through save, and if the verification fails, the configuration in the DISK layer can be loaded into the Memory and runtime layers through the load command to achieve the rollback effect.

Introduction of built-in library table

Log in to the proxysql management port, and the default username password is: admin/admin

Mysql-uadmin-padmin-h227.0.0.1-P6032

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.6.30 (ProxySQL Admin Module)

Admin > show databases

+-+

| | seq | name | file | |

+-+

| | 0 | main |

| | 2 | disk | / var/lib/proxysql/proxysql.db |

| | 3 | stats |

| | 4 | monitor |

+-+

* main: backend db instance, user authentication, routing rules and other information are stored in the database. The current configuration of proxysql whose table name starts with runtime cannot be modified by dml statement, only the corresponding table that does not start with runtime (in memory) can be modified, then LOAD makes it effective, and SAVE saves it to the hard disk for next restart to load.

* disk is the configuration that persists to the hard disk, sqlite data file.

* stats is the statistics of proxysql running crawls, including the number of commands executed to the backend, traffic, processlist, query category summary / execution time, and so on.

* the monitor library stores the information collected by the monitor module, mainly for the health / delay check of the backend db.

Admin > show tables

+-+

| | tables |

+-+

| | global_variables |

| | mysql_collations |

| | mysql_query_rules |

| | mysql_replication_hostgroups |

| | mysql_servers |

| | mysql_users |

| | runtime_global_variables |

| | runtime_mysql_query_rules |

| | runtime_mysql_replication_hostgroups |

| | runtime_mysql_servers |

| | runtime_mysql_users |

| | runtime_scheduler |

| | scheduler |

+-+

13 rows in set (0.00 sec)

Admin > show tables from stats

+-+

| | tables |

+-+

| | global_variables |

| | stats_mysql_commands_counters |

| | stats_mysql_connection_pool |

| | stats_mysql_global |

| | stats_mysql_processlist |

| | stats_mysql_query_digest |

| | stats_mysql_query_digest_reset |

| | stats_mysql_query_rules |

+-+

8 rows in set (0.00 sec)

Mysql_servers

Admin > show create table mysql_servers\ G

CREATE TABLE mysql_servers (

Hostgroup_id INT NOT NULL DEFAULT 0

Hostname VARCHAR NOT NULL

Port INT NOT NULL DEFAULT 3306

Status VARCHAR CHECK (UPPER (status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT',' OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE'

Weight INT CHECK (weight > = 0) NOT NULL DEFAULT 1

Compression INT CHECK (compression > = 0 AND compression = 0) NOT NULL DEFAULT 1000

Max_replication_lag INT CHECK (max_replication_lag > = 0 AND max_replication_lag = 0) NOT NULL DEFAULT 0

Comment VARCHAR NOT NULL DEFAULT''

PRIMARY KEY (hostgroup_id, hostname, port))

1 row in set (0.00 sec)

Mysql_servers groups database instances and configures instance information.

Hostgroup_id: the group to which the MySQL instance belongs

Status:

ONLINE: running statu

SHUNNED: the database is temporarily kicked out due to the presence of "too many connections error" in the background database or the master-slave latency of the back-end database exceeds the allowed threshold.

OFFLINE_SOFT: "soft offline" state, new connections are no longer accepted, but established connections wait for active transactions to finish

OFFLINE_HARD: "hard offline" state, no new connections are accepted, established connections may be forcibly interrupted. This occurs when the backend instance goes down or the network is unreachable.

Weight: select the weight value of the backend database when load balancing. The higher the weight, the higher the selected rate.

Max_connections: the maximum number of connections allowed to this backend instance. Do not set this value to be greater than the maximum number of connections to the backend database.

Max_latency_ms:mysql_ping response time, greater than this threshold will remove it from the connection pool (even if it is ONLINE)

Mysql_replication_hostgroups

This table is used for traditional asynchronous / semi-synchronous master-slave replication, and mysql_group_replication_hostgroups or mysql_galera_hostgroups is required for MGT/GALERA (after ProxySQL 2.x). Each row in this table represents a pair of writer_hostgroup and reader_hostgroup. ProxySQL will monitor the value of read_only, and ProxySQL will assign whether the MySQL instance is reader_hostgroup or writer_hostgroup, based on the value of read_only. If it is found that the read_only of the slave database becomes 0 and the master database becomes 1, the roles are changed, and the hostgroup relationship in the mysql_servers table is automatically rewritten to achieve the automatic Failover effect.

Admin > SHOW CREATE TABLE mysql_replication_hostgroups\ G

* * 1. Row *

Table: mysql_replication_hostgroupsCreate Table:

CREATE TABLE mysql_replication_hostgroups (

Writer_hostgroup INT CHECK (writer_hostgroup > = 0) NOT NULL PRIMARY KEY

Reader_hostgroup INT NOT NULL CHECK (reader_hostgroupwriter_hostgroup AND reader_hostgroup > 0)

Check_type VARCHAR CHECK (LOWER (check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT' read_only'

Comment VARCHAR, UNIQUE (reader_hostgroup))

1 row in set (0.00 sec)

Mysql_users

CREATE TABLE mysql_users (

Username VARCHAR NOT NULL

Password VARCHAR

Active INT CHECK (active IN (0jue 1)) NOT NULL DEFAULT 1, use_ssl INT CHECK (

Use_ssl IN (0Pol 1)) NOT NULL DEFAULT 0

Default_hostgroup INT NOT NULL DEFAULT 0

Default_schema VARCHAR

Schema_locked INT CHECK (schema_locked IN (0Pol 1)) NOT NULL DEFAULT 0

Transaction_persistent INT CHECK (transaction_persistent IN (0Pol 1)) NOT NULL DEFAULT 0

Fast_forward INT CHECK (fast_forward IN (0Pol 1)) NOT NULL DEFAULT 0

Backend INT CHECK (backend IN (0Pol 1)) NOT NULL DEFAULT 1

Frontend INT CHECK (frontend IN (0Pol 1)) NOT NULL DEFAULT 1

Max_connections INT CHECK (max_connections > = 0) NOT NULL DEFAULT 10000

PRIMARY KEY (username, backend)

UNIQUE (username, frontend)

)

Username, password: the user password to connect to the backend db. You can insert this password either in plain text or in hash encrypted ciphertext.

Default_hostgroup: when this user's request does not match the rule, it will be sent to this hostgroup by default.

Default_schema: when this user connects without specifying database name, the default schema used is NULL on the surface, but it is actually affected by the variable mysql-default_schema, which defaults to information_schema.

Transaction_persistent: if set to 1, after connecting to a ProxySQL session, if a transaction is opened on a hostgroup, then the subsequent sql will continue to be maintained on that hostgroup, whether it will match other routing rules until the transaction ends.

Fast_forward: ignore the query rewriting / caching layer and pass the user's request directly to the back-end DB. It is equivalent to using only its connection pooling function, generally not routing rules. * it's fine.

Mysql_query_rules

CREATE TABLE mysql_query_rules (

Rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

Active INT CHECK (active IN (0Pol 1)) NOT NULL DEFAULT 0

Username VARCHAR

Schemaname VARCHAR

FlagIN INT NOT NULL DEFAULT 0

Client_addr VARCHAR

Proxy_addr VARCHAR

Proxy_port INT

Digest VARCHAR

Match_digest VARCHAR

Match_pattern VARCHAR

Negate_match_pattern INT CHECK (negate_match_pattern IN (0Pol 1)) NOT NULL DEFAULT 0

FlagOUT INT

Replace_pattern VARCHAR

Destination_hostgroup INT DEFAULT NULL

Cache_ttl INT CHECK (cache_ttl > 0)

Reconnect INT CHECK (reconnect IN (0Pol 1)) DEFAULT NULL

Timeout INT UNSIGNED

Retries INT CHECK (retries > = 0 AND retries insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (1010)

Admin > insert into mysql_servers (hostgroup_id,hostname,port) values (10meme 192.168.20.31)

Admin > insert into mysql_servers (hostgroup_id,hostname,port) values (192.168.20.32)

Admin > insert into mysql_servers (hostgroup_id,hostname,port) values (10meme 192.168.20.33pr 3306)

Admin > save mysql servers to disk

Admin > load mysql servers to runtime

4. Configure read-write separation rules

Admin > INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES

(1 SELECT.*FOR UPDATE$',10,1), (2) SELECT',20,1)

Admin > LOAD MYSQL QUERY RULES TO RUNTIME

Admin > SAVE MYSQL QUERY RULES TO DISK

5. Configure MySQL users to ProxySQL

Admin > INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('app','pass',10)

Admin > save mysql users to disk

Admin > load mysql users to runtime

ProxySQL + Orchestrator to achieve high availability

Orchestrator (https://github.com/github/orchestrator) is a topology management tool of MySQL replication structure, which can automatically detect the MySQL topology and automatically promote the "optimal" slave library to the master library when the master database fails. Orchestrator provides rich API interfaces and hook functions for fault detection and failover. MyData uses the hook functions provided by Orchestrator together with ProxySQL, Keepalived and HAProxy to realize application insensitive failover.

Switching process:

1. First of all, when the back-end master database detected by Orchestrator fails, it will confirm the failure of the master database twice through the slave database, so as to avoid misjudging the downmachine of the master database when the master database is under high load.

2. Before the Orchestrator switch, the PreFailoverProcesses hook function is called. In the hook function, MyData first kicks the main library of the "down" machine from the ProxySQL to prevent the application from writing the request to the "fake death" main library. Execute the statement as follows:

# forcibly close the connection to the fake-dead master library to prevent data from being written to the old fake-dead master library.

Admin > update runtime_mysql_servers set status= "HARD_OFFLINE" where hostname='192.168.20.31' and port='3306'

# put the fake dead old master database into the cluster to prevent subsequent data from being written to the old master database

Admin > delete from mysql_servers where hostname='192.168.20.31' and port='3306'

Admin > load mysql servers to runtime

Admin > save mysql serbers to disk

3. MyData can make recovery decisions based on the "intelligence" of users' RPO and RTO.

4. MyData notifies Orchestrator of the development switch, and after the Orchestrator switch is completed, the read_ onlyvalue of the new main library will be modified to 0.

5. ProxySQL routes new write requests to the main library.

In the whole process, MyData has made a large number of optimizations, which can reduce the data lost by users to less than 10% while satisfying the user RTO. MyData also provides a highly available solution for ProxySQL itself, avoiding the single point of failure of ProxySQL itself.

Summary

ProxySQL provides strong routing rules. When the application itself does not support read-write separation, DBA can configure routing rules to provide transparent read-write separation for the application. When using Keepalived + ProxySQL + Orchestrator to provide high availability as master and slave, it can effectively avoid the pain points of keepalived + double master structure causing data to be miswritten due to keepalived brain fissure.

About MyData

MyData is independently developed by Cloud and Enmo to provide an overall solution with high availability, high reliability, high security and easy to use for MySQL databases. MyData combines the experience and best practices of cloud and Enmo senior database engineers to help customers quickly build a highly available database cluster environment, ensure that the MySQL database running environment meets the requirements of enterprise-class databases, and help customers improve their ability to deliver quickly.

Cloud and Enmo provide professional and smart end-to-end services to MyData, covering the four stages of planning and design, construction and implementation, operation management, optimization and promotion, to build a secure, continuous, efficient and stable data environment for customers.

MyData currently has a number of best practice cases in the government and financial industry, dedicated to providing enterprises with solutions for the integration of open source databases.

Want to know more about database and cloud technology?

Come and follow the official account of "data and Cloud" and the official website of "Yunhe Enmo". We look forward to learning and making progress with you.

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.