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

This article mainly introduces MySQL through the use of ProxySQL to achieve the separation of reading and writing, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the use of ProxySQL in MySQL to achieve the separation of reading and writing.

1 introduction to ProxySQL:

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

Official document: https://github.com/sysown/proxysql/wiki/

Download address: https://github.com/sysown/proxysql/releases/

2 Environment: system: CentOS7.5ProxySQL version: proxysql-1.4.8-1-centos7.x86_64.rpmMysql version: MySQL 5.7.22ProxySQL host IP:192.168.1.101Mysql master library IP:192.168.1.102Mysql slave library IP:192.168.1.1033 prerequisite: firewall and selinux have been turned off; Mysql master-slave synchronization has been configured 4 install ProxySQL:4.1 install # with ProxySQL source [root@ProxySQL ~] # cat show databases +-+ | seq | name | file | +- -+ | 0 | main | | 2 | disk | / var/lib/proxysql/proxysql.db | | 3 | stats | | 4 | monitor | | | 5 | stats_history | / var/lib/proxysql/proxysql_stats.db | +-- + 5 rows in set (0.00 sec) |

Library description:

Main memory configuration database, which stores backend db instance, user authentication, routing rules and other information in the table. 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 health / latency checks on the back-end db. Stats_history Statistics History Library 5.1.3 Proxysql Management end adds configuration of backend connection to mysql master-slave database (admin@127.0.0.1:6032) [(none)] > show tables from main +-+ | tables | +-+ | global_variables | | # basic configuration parameters of ProxySQL | Similar to MySQL | mysql_collations | # configure support for MySQL character set | mysql_group_replication_hostgroups | # MGR-related tables for automatic allocation of read and write groups of instances | mysql_query_rules | # routing table | mysql_query_rules_fast_routing | # tables related to master-slave replication Automatic allocation of read and write groups for instances | mysql_replication_hostgroups | # Storage of MySQL instance information | mysql_servers | # Storage of MySQL users at this stage, of course, there is a plan to separate front and rear accounts in the future | mysql_users | # Storage ProxySQL information Used for ProxySQL Cluster synchronization | proxysql_servers | # Storage check value of the running environment | runtime_checksums_values | # | runtime_global_variables | # | runtime_mysql_group_replication_hostgroups | # | runtime_mysql_query_rules | # | runtime_mysql_query_rules_fast _ routing | # | runtime_mysql_replication_hostgroups | # corresponds to the above But the configuration being used by the running environment | runtime_mysql_servers | # | runtime_mysql_users | # | runtime_proxysql_servers | # | runtime_scheduler | # | scheduler | # scheduled task Table +-+ 20 rows in set (0.00 sec)

Runtime_ begins with the runtime configuration, which cannot be modified. To modify the configuration of ProxySQL, you need to modify the non-runtime_ table, and you must execute LOAD after modification. TO RUNTIME can be loaded into RUNTIME to take effect, execute save. To disk can persist the configuration to disk.

The following statement was also successful without switching to the main library, because the SQLite3 database engine used internally in ProxySQL is different from the parsing method of MySQL. Even if the USE main statement is executed, it has no effect, but it will not report an error.

Use the insert statement to add the mysql host to the mysql_ sessions table, where hostgroup_id 1 represents the write group and 2 represents the read group.

(admin@127.0.0.1:6032) [(none)] > insert into mysql_servers (hostgroup_id,hostname,port,weight,comment) values; Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)] > insert into mysql_servers (hostgroup_id,hostname,port,weight,comment) values (2Med 192.168.1.103) Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)] > select * from mysql_servers +- +-+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +- -+ | 1 | 192.168.1.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group | | 2 | 192.168.1.103 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | Read Group | +- -+- -+-+ 2 rows in set (0.00 sec)

After modification, it is loaded into RUNTIME and saved to disk.

(admin@127.0.0.1:6032) [(none)] > load mysql servers to runtime; (admin@127.0.0.1:6032) [(none)] > save mysql servers to disk

Add the account you just created to the mysql_users table of the proxysql host, which the proxysql client needs to access the database.

Default_hostgroup default group is set to write group, that is, 1

When the read-write separation routing rules are not met, the database of the default group will be accessed.

(admin@127.0.0.1:6032) [(none)] > insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','pwproxysql',1,1) Query OK 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)] > select * from mysql_users\ gateway * 1. Row * * username: user name of proxysql # backend mysql instance Password: pwproxysql # password of backend mysql instance active: 1 # active=1 indicates that the user is valid 0 means it does not take effect use_ssl: 0 default_hostgroup: 1 # the instance under which the user logs in by default default_schema: NULL # the database to which the user logs in to the backend mysql instance by default. If this place is NULL, it is determined by the global variable mysql-default_schema. The default is information_schema schema_locked: 0 transaction_persistent: 1 # if the session connected to ProxySQL is set to 1, 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. Although the default is 0 fast_forward: 0 # ignores the query rewrite / cache layer and transmits the user's request directly to the back-end DB. It is equivalent to only using its connection pooling function, generally not using routing rules. * just backend: 1 frontend: 1 max_connections: 10000 # # the maximum number of connections allowed by this user is 1 row in set (0.00 sec)

After modification, it is loaded into RUNTIME and saved to disk.

(admin@127.0.0.1:6032) [(none)] > load mysql users to runtime; (admin@127.0.0.1:6032) [(none)] > save mysql users to disk;5.2 add accounts for health monitoring 5.2.1 mysql add accounts that can only be checked by proxysql

First, create a user name for monitoring on the backend master node (just create it on master, because it will be copied to slave), and this user name only needs to have USAGE permission. If you also need to monitor whether slave is seriously lagging behind master in the replication structure (familiar: the idiom is "slow", the term is "replication lag"), you also need to have replication client permission. This permission is directly granted here.

Mysql > GRANT replication client ON *. * TO 'monitor'@'192.168.1.%' IDENTIFIED BY' monitor';5.2.2 proxysql modify the variable to set the health check account (admin@127.0.0.1:6032) [(none)] > set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)] > set mysql-monitor_password='monitor' Query OK, 1 row affected (0.00 sec)

The above setting actually modifies the global_ variables table, which is equivalent to the following two statements:

(admin@127.0.0.1:6032) [(none)] > UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)] > UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec)

After modification, it is loaded into RUNTIME and saved to disk.

(admin@127.0.0.1:6032) [(none)] > load mysql variables to runtime; (admin@127.0.0.1:6032) [(none)] > save mysql variables to disk Add read-write separation routing rules: all select statements are routed to hostgroup_id=2 groups (that is, read groups), but statements such as select * from tb for update modify data, so they need to be defined separately The group that routes it to the hostgroup_id=1 (that is, the write group) other groups that are not matched by the rule will be routed to the user's default group (default_hostgroup in the mysql_ users table) (admin@127.0.0.1:6032) [(none)] > insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values. Query OK, 1 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)] > insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (2 row affected (0.00 sec) (admin@127.0.0.1:6032) [(none)] > select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules +-- +-- +-+ | rule_id | active | match_digest | destination_hostgroup | apply | +- -+ | 1 | 1 | ^ SELECT.*FOR UPDATE$ | 1 | 1 | 2 | 1 | ^ SELECT | 2 | 1 | +- -- +-+-- +-- + 2 rows in set (0.00 sec) 5.4.Load the data we just modified into RUNTIME (see ProxySQL's multi-tier configuration structure): 5.4.1 load into runtime Make the configuration effective (admin@127.0.0.1:6032) [(none)] > load mysql query rules to runtime (admin@127.0.0.1:6032) [(none)] > load admin variables to runtime;5.4.2 save to disk (/ var/lib/proxysql/proxysql.db), permanently save the configuration (admin@127.0.0.1:6032) [(none)] > save mysql query rules to disk; (admin@127.0.0.1:6032) [(none)] > save admin variables to disk;6 test read-write separation 6.1 connection proxysql client:

The login user is the user we just created in the mysql_ user table with port 6033

[root@centos7] # mysql-uproxysql-ppwproxysql-h227.0.0.1-P6033Welcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MySQL [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.00 sec) MySQL [(none)] > 6.2 try to modify the database and query:

Create two databases and look up a table.

MySQL [(none)] > create database bigboss;Query OK, 1 row affected (0.01 sec) MySQL [(none)] > create database weijinyun;Query OK, 1 row affected (0.00 sec) MySQL [(none)] > show databases +-+ | Database | +-+ | information_schema | | bigboss | | mysql | | performance_schema | | test | | weijinyun | +-+ 6 rows in set (0.01 sec) MySQL [(none)] > select user Host from mysql.user +-+-+ | user | host | +-+-+ | root | 127.0.0.1 | | monitor | 192.168.1% | | proxysql | 192.168.1% | | repliaction | 192.168.1. % | | root |:: 1 | | centos7 | | root | centos7 | localhost | | root | localhost | +-+-+ 9 rows in set (0.01 sec) 6.3 verify whether read-write separation is successful: proxysql has a similar audit function. You can view the implementation of various types of SQL. Execute on the proxysql management side: from the following hostgroup and digest_ text values, all write operations are routed to group 1, and read operations are routed to group 2, where group 1 is the write group and group 2 is the read group! (admin@127.0.0.1:6032) [(none)] > select * from stats_mysql_query_digest +-+ + -+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-- -+ -+ | 2 | information_schema | proxysql | 0x3EA85877510AC608 | select * from stats_mysql_query_digest | 2 | 1527233735 | 1527233782 | 4092 | 792 | 3300 | 1 | information_schema | proxysql | 0x594F2C744B698066 | select USER () | | 1 | 1527233378 | 1527233378 | 0 | 0 | 0 | 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 2 | 1527233202 | 1527233495 | 5950 | 1974 | 3976 | 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @ @ version_comment limit? | 2 | 1527233196 | 15272378 | 0 | | | 0 | 0 | +-| -+-+ 4 rows in set (0.00 sec)

(admin@127.0.0.1:6032) [(none)] >

After reading the above about the separation of reading and writing through the use of ProxySQL in MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.