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

Brief introduction

ProxySQL is an open source middleware product based on MySQL, is a flexible MySQL proxy layer, can achieve read-write separation, support Query routing functions, support to dynamically specify a SQL for caching, support dynamic loading (no need to restart ProxySQL services), failover and some SQL filtering functions.

Tencent Cloud cdb can provide the main library VIP+ read-only slave library VIP, so that you need to maintain 2 VIP and do not automatically separate read and write. Based on the current situation, you plan to use proxysql combined with clb and cdb to achieve automatic separation of read and write.

The architecture is as follows:

App--clb layer 4-- proxysql-- main vip+ self-reading vip

I. ProxySQL deployment

1. Download and install start

[root@VM_8_24_centos ~] # wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm[root@VM_8_24_centos ~] # rpm-ivh proxysql-2.0.8-1 centos7.x8664.rpm [root @ VM_8_24_centos ~] # systemctl start proxysql [root @ VM_8_24_centos ~] # systemctl enable proxysql [root @ VM_8_24_centos ~] # netstat-tulpn | grep 603tcp 0 0 0.0.0. 0 tulpn 6032 0.0.0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 00 of the LISTEN 26063/proxysql tcp 6033 0. 0. 0. 0.

two。 Landing test

[root@VM_8_24_centos] # mysql-uadmin-padmin-h 127.0.0.1-P6032Welcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MySQL [(none)] > 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 [(none)] > MySQL. |

II. ProxySQL configuration

1. Architecture

Node1 (vip 192.168.0.9), mysql master

Node2 (vip 192.168.0.42), mysql slave

Prxoy (192.168.8.24purl 6033), proxysql

Clb (vip 192.168.9.27), clb proxysql

App (192.168.0.26), mysql client

Add mysql master-slave database information on 2.proxysql

Execute on proxysql, put the main library master, that is, the node for writing, into group 0, and the salve node for reading into group 1

The syntax is as follows:

Insert into mysql_servers (hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values; insert into mysql_servers (hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values; insert into mysql_servers (192.168.0.42) values; select * from mysql_servers

Example:

MySQL [(none)] > insert into mysql_servers (hostgroup_id,hostname,port,weight,max_connections,ax_replication_lag,comment) values (0meme 192.168.0.9); Query OK, 1 row affected (0.00 sec) MySQL [(none)] > insert into mysql_servers (hostgroup_id,hostname,port,weight,max_connections,ax_replication_lag,comment) values Query OK, 1 row affected (0.00 sec) MySQL [(none)] > select * from mysql_servers + -+-+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_relication_lag | use_ssl | max_latency_ms | comment | +- -+-+ | 0 | 192.168.0.9 | 3306 | ONLINE | 1 | 0 | 2000 | 10 | | 0 | 0 | TESTuser | | 1 | 192.168.0.42 | 3306 | ONLINE | 1 | 0 | 2000 | 10 | 0 | 0 | TESTuser | +-| +-+-+ 2 rows in set (0.01sec)

Add proxysql monitoring account and application operation account to 3.mysql main library

Execute on mysql master. Note that the application operation account grants permissions according to the actual situation, and it is best to authorize only proxysql hosts.

The syntax is as follows:

Grant usage on *. * to 'proxysql'@'192.168.8.24' identified by' ProxySQL@20191226';grant select, insert, update, delete ON *. * TO 'testuser'@'192.168.8.24' identified by' TESTuser@20191226';flush privileges;select host,user from mysql.user

Example:

MySQL [(none)] > grant usage on *. * to 'proxysql'@'192.168.8.24' identified by' ProxySQL@20191226';Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)] > grant select, insert, update, delete ON *. * TO 'testuser'@'192.168.8.24' identified by' TESTuser@20191226';Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)] > flush privileges Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > select host,user from mysql.user +-+-+ | host | user | +-+-+ |% | TESTuser_data_servic |% | TESTuser_rule_rw | | 192.168.24 | proxysql | | 192.168.8 .24 | testuser | | 127.0.0.1 | root | | localhost | mysql.sys | | localhost | root | +-+-+ 7 rows in set (0.01 sec)

Add application operation account to 4.proxysql

Execute on proxysql. Note that the application account is authorized to the master hostgroup. This is 0.

The syntax is as follows:

Insert into mysql_users (username,password,active,default_hostgroup) values ('testuser','TESTuser@20191226',1,0); select * from mysql_users

Example:

MySQL [(none)] > insert into mysql_users (username,password,active,default_hostgroup) values ('testuser','TESTuser@20191226',1,0); Query OK, 1 row affected (0.00 sec) MySQL [(none)] > select * from mysql_users +- -+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +-+- -+ -+ | testuser | TESTuser@20191226 | 1 | 0 | 0 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | +- -+- -+ 1 row in set (0.00 sec)

Note: if you set mysql_users with a clear text password, you can use the save command to convert it to a hash password.

The syntax is as follows:

Save mysql users to mem

Example:

MySQL [(none)] > save mysql users to mem;Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > select username,password from mysql_users +-+-+ | username | password | +-+- -+ | testuser | * 59D7241B6C5F951F331FF88505C582CBCD42482F | +-+-+ 1 row in set (0.00 sec)

5. Set up monitoring account

Operate on proxysql. The account password is the same as the account password on mysql main database.

The syntax is as follows:

Update global_variables set variable_value='proxysql' where variable_name='mysql-monitor_username';update global_variables set variable_value='ProxySQL@20191226' where variable_name='mysql-monitor_password'

Example:

MySQL [(none)] > update global_variables set variable_value='proxysql' where variable_name='mysql-monitor_username';Query OK, 1 row affected (0.00 sec) MySQL [(none)] > update global_variables set variable_value='ProxySQL@20191226' where variable_name='mysql-monitor_password';Query OK, 1 row affected (0.00 sec)

6. Apply to the line

Operation on proxysql

The syntax is as follows:

Load mysql servers to runtime;load mysql users to runtime;load mysql variables to runtime

Example:

MySQL [(none)] > load mysql servers to runtime;Query OK, 0 rows affected (0.01 sec) MySQL [(none)] > load mysql users to runtime;Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > load mysql variables to runtime;Query OK, 0 rows affected (0.00 sec)

7. Persistence

Operation on proxysql

The syntax is as follows:

Save mysql servers to disk;save mysql users to disk;save mysql variables to disk

Example:

MySQL [(none)] > save mysql servers to disk;Query OK, 0 rows affected (0.07 sec) MySQL [(none)] > save mysql users to disk;Query OK, 0 rows affected (0.04 sec) MySQL [(none)] > save mysql variables to disk;Query OK, 97 rows affected (0.02 sec)

8. Automatic read-write separation configuration

Operate on proxysql to define routing rules, such as: all select of select * from tb for update is sent to slave, and other statements are sent to master.

The syntax is as follows:

Insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) values (1 active,match_pattern,destination_hostgroup,apply'^ SELECT.*FOR UPDATE$',0,1); insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) values (1 ^ SELECT',1,1); load mysql query rules to runtime;save mysql query rules to disk

Example:

MySQL [(none)] > insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) values (1Med'^ SELECT.*FOR UPDATE$',0,1); Query OK, 1 row affected (0.00 sec) MySQL [(none)] > insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) values (1Med'^ SELECT',1,1); Query OK, 1 row affected (0.00 sec) MySQL [(none)] > load mysql query rules to runtime Query OK, 0 rows affected (0.01 sec) MySQL [(none)] > save mysql query rules to disk;Query OK, 0 rows affected (0.10 sec)

Note: active indicates whether to enable this sql routing item. Match_pattern is our regular match. Destination_hostgroup indicates which mysql we want to forward this type of sql to. An apply of 1 means that after the regular match, other matches will not be accepted and will be forwarded directly.

9. Client test effect

Connect to the database and access through the client interface of proxysql (6033)

[root@VM_8_24_centos] # mysql-utestuser-pTESTuser@20191226-h 127.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, 2018, 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 | | TESTuser_data_services_dev | | TESTuser_data_services_test | | TESTuser_rule | +- -+ 7 rows in set (0.01sec) MySQL [(none)] > use TESTuser_rule Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedMySQL [TESTuser_rule] > show tables +-- + | Tables_in_TESTuser_rule | +-- + | organ_1_b_12 | | organ_1_b_13 | | organ_1_b_14 | | organ_1_b_15 | | organ_1_b_16 | | organ_1_b_19 | +-- + 6 rows in set (0.00 sec) MySQL [TESTuser_rule] > select * from organ_1_b_12 | +- -+ | id | T1 | T1 | T2 | T3 | T4 | T5 | T6 | T7 | T8 | T9 | T10 | T11 | T12 | T13 | T14 | T15 | T16 | T17 | T18 | T19 | user_id | nick_name | created | +- +- -+ | 1 | 513427199402063414 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | admin | 2018-10-31 11:50:38 | 2 | 140502198811102244 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | 2018-10-31 | 14:35:36 | +- -- + 2 rows in set (0.00 sec)

10.proxysql statistical effect

Use the proxysql API to operate the data normally, and view the ProxySQL statistics from the management API:

The syntax is as follows:

Select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt > 0

Example:

MySQL [(none)] > select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt > 0 +-+ | Command | Total_Time_us | Total_cnt | +-+ | SELECT | 5650 | 3 | SHOW | 7044 | 4 | +- -+ 2 rows in set (0.00 sec)

Check the implementation of various types of SQL

The syntax is as follows:

Select * from stats_mysql_query_digest

Example:

MySQL [(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 | +- +- -+ | 1 | TESTuser_rule | testuser | 0x504CF0771C9E548B | select * from organ_1_b_12 | 1 | 1577341448 | 1577341448 | 3746 | 3746 | 3746 | 0 | TESTuser_rule | testuser | 0x99531AEFF718C501 | show tables | | 2 | 1577341415 | 1577341425 | 2252 | 1583 | | 1 | information_schema | testuser | 0x620B328FE9D6D71A | SELECT DATABASE () | 1 | 1577341415 | 1577341415 | 1904 | 1904 | 1 | information_schema | testuser | 0x02033E45904D3DF0 | show databases | 1 | 1577341374 | 1577341374 | 2326 | 2326 | | | 2326 | | 0 | TESTuser_rule | testuser | 0x02033E45904D3DF0 | show databases | 1 | 1577341415 | 1577341415 | 1577341415 | 2466 | 2466 | 0 | information_schema | testuser | select @ @ version_comment limit? | 1 | 1577341364 | 1577341364 | 0 | 0 | 0 | +-| -+ -+ 6 rows in set (0.00 sec)

11. Solve proxysql single point failure and debt balance through clb

Private network clb 192.168.9.27 tcp port 6033 to 192.168.24 6033 port, other proxysql binding can be done.

[root@VM_0_26_centos] # mysql-utestuser-p-h 192.168.9.27-P6033Enter password: Welcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 2699Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2018, 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 | | TESTuser_data_services_dev | | TESTuser_data_services_test | | TESTuser_rule | +- -+ 7 rows in set (0.01sec) MySQL [(none)] > select user Host from mysql.user +-+-+ | user | host | +-+-+ | TESTuser_data_servic |% | | TESTuser_rule_rw |% | proxysql | 192.168.24 | | testuser | | 192.168.8.24 | | root | 127.0.0.1 | | mysql.sys | localhost | | root | localhost | +-+-+ 7 rows in set (0.01 sec) |

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.