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

The role of proxysql

1. Read and write separation, server can be divided into read group and write group

two。 Dynamically specify a SQL for cache

3. Failover, which depends on the dynamic loading of his configuration, but cannot automatically select the master. It depends on keepalived and MHA.

4. Configure dynamic UPDAT

5. A node can run a lot of proxysql

Proxy of 6.percona

Proxysql port

Port number of monitoring: 6633

Management port number: 6032

Installation and download

[root@Darren1 tmp] # wget https://github.com/sysown/proxysql/releases/download/v1.4.0/proxysql-1.4.0-1-centos67.x86_64.rpm

[root@Darren1 tmp] # rpm-ivh proxysql-1.4.0-1-centos67.x86_64.rpm

[root@Darren1 tmp] # / etc/init.d/proxysql start

[root@Darren1 tmp] # ps-ef | grep proxy

Root 50769 10 05:19? 00:00:00 proxysql-c / etc/proxysql.cnf-D / var/lib/proxysql

Root 50770 50769 3 05:19? 00:00:00 proxysql-c / etc/proxysql.cnf-D / var/lib/proxysql

Root 50798 50261 0 05:20 pts/3 00:00:00 grep proxy

# Login:

[root@Darren1 proxysql] # mysql-u admin-padmin-h 127.0.0.1-P6032-- prompt='Admin >'

Admin > show databases

+-+

| | seq | name | file | |

+-+

| | 0 | main |

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

| | 3 | stats |

| | 4 | monitor |

+-+

Multi-layer configuration system

(1) allow automatic configuration updates

(2) most configurations do not need to be restarted and take effect in the running state

(3) allow rollback of the wrong configuration

Three-tier structure diagram:

[1] LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME

Loads MySQL users from the in-memory database to the runtime data structures

[2] SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME

Persists the MySQL users from the runtime data structures to the in-memory database

[3] LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK

Loads MySQL users from the on-disk database to the in-memory database

[4] SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK

Persists the MySQL users from the in-memory database to the on-disk database

[5] LOAD MYSQL USERS FROM CONFIG

Loads from the configuration file the users into the in-memory database

For tables:

Mysql_users mysql_servers

Mysql_query_rules

Mysql_variables

Admin_variables

Scheduler scheduling tasks

Scheduler is a task scheduling plan similar to cron that allows custom scripts to be run.

There are two main tables:

Admin@127.0.0.1 [main] > SHOW TABLES LIKE'% scheduler%'

+-+

| | tables |

+-+

| | scheduler |

| | runtime_scheduler |

+-+

Scheduler: can be used to set up a scheduling plan

Runtime_scheduler: read-only, cannot be configured and modified

Admin@127.0.0.1 [main] > SHOW CREATE TABLE scheduler\ G

* * 1. Row *

Table: scheduler

Create Table: CREATE TABLE scheduler (

Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

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

Interval_ms INTEGER CHECK (interval_ms > = 100AND interval_msINSERT INTO mysql_users (username,password) VALUES ('user1','password1')

Create a user by hostgroup,schema:

Admin@127.0.0.1 [main] > INSERT INTO mysql_users (username,password,default_hostgroup,default_schema) VALUES ('user2','password2',10,'sbtest1')

Modify the maximum number of connections for user2 users to 100:

Admin@127.0.0.1 [main] > UPDATE mysql_users SET max_connections=100 WHERE username='user2'

Modify transaction persistence:

Admin@127.0.0.1 [main] > UPDATE mysql_users SET transaction_persistent=1 WHERE username='user2'

Password hash encryption:

Admin@127.0.0.1 [main] > load mysql users to runtime

Admin@127.0.0.1 [main] > save mysql users from runtime

Admin@127.0.0.1 [main] > save mysql users to disk

Service configuration (server configuration)

1. When you add server to the mysql_servers table, divide it into hostgroup_id (for example, 0 for write group, 1 for read group)

2. Add server to the mysql_servers table without distinguishing between hostgroup_id (for example, all set to 0), and then use the values in the mysql_replication_ hostgroups table to automatically set hostgroup_id for the back-end server according to the read_only variable values detected by proxysql for each server.

The first is highly recommended: because the first is completely under our control, and the second is that if we mistakenly set the read_only property of the read server to 0, proxysql will reassign it to the write group, which is absolutely undesirable.

(1) add a new service:

Admin@127.0.0.1 [main] > insert into mysql_servers (hostgroup_id,hostname,max_connections) values (0meme 192.168.91.23mp1000)

Admin@127.0.0.1 [main] > insert into mysql_servers (hostgroup_id,hostname,max_connections) values (192.168.91.22)

Admin@127.0.0.1 [(none)] > select * from mysql_servers

+- +-+ +

| | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

+- +-+ +

| | 0 | 192.168.91.23 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| | 1 | 192.168.91.22 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+- +-+ +

(2) modify the limited number of connections max_connections:

Admin@127.0.0.1 [main] > UPDATE mysql_servers SET max_connections=10 WHERE hostname='192.168.91.23'

(3) modify the weight value weight:

For example, in the environment of one master and two slaves, set different weights:

Admin > SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers

+-+

| | hostgroup_id | hostname | weight | max_replication_lag | |

+-+

| | 0 | 172.16.0.1 | 1000 | 30 |

| | 1 | 172.16.0.2 | 1000 | 30 |

| | 1 | 172.16.0.3 | 1000 | 30 |

| | 1 | 172.16.0.1 | 1 | 30 |

+-+

In the above case, if there are reads, 99.95% will be sent to 172.16.0.2 and 172.16.0.3 hosts, and 0.05% will be sent to 172.16.0.1 hosts. If 172.16.0.2 and 172.16.0.3 become unavailable, then all reads will be borne by 172.16.0.1.

(4) modify the maximum replication delay max_replication_lag:

Admin@127.0.0.1 [main] > UPDATE mysql_servers SET max_replication_lag=30 WHERE hostname='192.168.91.23'

(5) enable compression and set compression to non-0

Admin@127.0.0.1 [main] > UPDATE mysql_servers SET compression=2 WHERE hostname='192.168.91.23'

(6) gracefully shut down a service:

Change state to status = offline_soft.

Admin@127.0.0.1 [main] > UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='192.168.91.23'

(7) simply delete a service delete

Connect msyql server at proxysql

[root@Darren1] # mysql-u admin-padmin-h 127.0.0.1-P6032

# configure DB hosts:

Admin@127.0.0.1 [main] > insert into mysql_servers (hostgroup_id,hostname) values (0meme 192.168.91.23')

Admin@127.0.0.1 [main] > insert into mysql_servers (hostgroup_id,hostname) values (1 recording 192.168.91.22')

Admin@127.0.0.1 [(none)] > select * from mysql_servers

+- +-+ +

| | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

+- +-+ +

| | 0 | 192.168.91.23 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| | 1 | 192.168.91.22 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+- +-+ +

# create a monitoring user on the main mysql library:

Root@localhost [(none)] > create user monitor@'192.168.91.%' identified by 'monitor'

Root@localhost [(none)] > grant all on *. * to monitor@'192.168.91.%'

# configure monitoring users and passwords in proxysql:

Admin@127.0.0.1 [(none)] > UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'

Admin@127.0.0.1 [(none)] > UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password'

# configure monitoring intervals, such as mysql-monitor_connect_interval, mysql-monitor_ping_interval, mysql-monitor_read_only_interval:

Admin@127.0.0.1 [(none)] > UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval')

Admin@127.0.0.1 [(none)] > select * from global_variables where variable_name like 'mysql-monitor_%'

+-+ +

| | variable_name | variable_value |

+-+ +

| | mysql-monitor_enabled | true |

| | mysql-monitor_connect_timeout | 600 | |

| | mysql-monitor_ping_max_failures | 3 | |

| | mysql-monitor_ping_timeout | 1000 | |

| | mysql-monitor_replication_lag_interval | 10000 | |

| | mysql-monitor_replication_lag_timeout | 1000 | |

| | mysql-monitor_groupreplication_healthcheck_interval | 5000 | |

| | mysql-monitor_groupreplication_healthcheck_timeout | 800 | |

| | mysql-monitor_username | monitor |

| | mysql-monitor_password | monitor |

| | mysql-monitor_query_interval | 60000 | |

| | mysql-monitor_query_timeout | 100 | |

| | mysql-monitor_slave_lag_when_null | 60 | |

| | mysql-monitor_wait_timeout | true |

| | mysql-monitor_writer_is_also_reader | true |

| | mysql-monitor_history | 600000 | |

| | mysql-monitor_connect_interval | 2000 | |

| | mysql-monitor_ping_interval | 2000 | |

| | mysql-monitor_read_only_interval | 2000 | |

| | mysql-monitor_read_only_timeout | 500 | |

+-+ +

Admin@127.0.0.1 [(none)] > LOAD MYSQL VARIABLES TO RUNTIME

Admin@127.0.0.1 [(none)] > SAVE MYSQL VARIABLES TO DISK

Admin@127.0.0.1 [(none)] > show tables from monitor

+-+

| | tables |

+-+

| | mysql_server_connect |

| | mysql_server_connect_log |

| | mysql_server_group_replication_log |

| | mysql_server_ping |

| | mysql_server_ping_log |

| | mysql_server_read_only_log |

| | mysql_server_replication_lag_log |

+-+

Admin@127.0.0.1 [(none)] > SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10

+-+

| | hostname | port | time_start_us | connect_success_time_us | connect_error | |

+-+

| | 192.168.91.23 | 3306 | 1495445436268761 | 1894 | NULL |

| | 192.168.91.22 | 3306 | 1495445436267947 | 3301 | NULL |

| | 192.168.91.23 | 3306 | 1495445434267631 | 601 | NULL |

| | 192.168.91.22 | 3306 | 1495445434266684 | 1898 | NULL |

| | 192.168.91.23 | 3306 | 1495445432267286 | 527 | NULL |

| | 192.168.91.22 | 3306 | 1495445432266457 | 1646 | NULL |

+-+

Admin@127.0.0.1 [(none)] > SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10

+-+ +

| | hostname | port | time_start_us | ping_success_time_us | ping_error | |

+-+ +

| | 192.168.91.23 | 3306 | 1495445542307107 | 147 | NULL |

| | 192.168.91.22 | 3306 | 1495445542306299 | 504 | NULL |

| | 192.168.91.23 | 3306 | 1495445540306625 | 173rd | NULL |

| | 192.168.91.22 | 3306 | 1495445540305786 | 596 | NULL |

| | 192.168.91.23 | 3306 | 1495445538305981 | 16585 | NULL |

+-+ +

Admin@127.0.0.1 [(none)] > LOAD MYSQL SERVERS TO RUNTIME

MySQL replication hostgroups

Corresponding table mysql_replication_hostgroups

The function of the table: configure the write group (e.g. 1) and the read group (e.g. 2). ProxySQL will assign the host to the corresponding read combination write group according to the read_only parameter.

Such as read_only=0, assigned to hostgroup 1

Such as read_only=1, assigned to hostgroup 2

Admin@127.0.0.1 [(none)] > INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) VALUES (1)

Admin@127.0.0.1 [(none)] > select * from mysql_replication_hostgroups

+-+

| | writer_hostgroup | reader_hostgroup | comment | |

+-+

| | 1 | 2 | NULL |

+-+

# now 192.168.91.22 is read_only=0, so hostgroup_id=1:

Admin@127.0.0.1 [(none)] > SELECT * FROM mysql_servers

+- +-+ +

| | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

+- +-+ +

| | 0 | 192.168.91.23 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| | 1 | 192.168.91.22 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+- +-+ +

Admin@127.0.0.1 [(none)] > load mysql servers to runtime

Root@localhost [(none)] > set global read_only=1

Admin@127.0.0.1 [(none)] > SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10

+-+ +

| | hostname | port | time_start_us | success_time_us | read_only | error | |

+-+ +

| | 192.168.91.22 | 3306 | 1495449784913364 | 10240 | 1 | NULL |

| | 192.168.91.22 | 3306 | 1495449782913147 | 3161 | 1 | NULL |

| | 192.168.91.22 | 3306 | 1495449780912973 | 5600 | 1 | NULL |

| | 192.168.91.22 | 3306 | 1495449778913003 | 8661 | 1 | NULL | |

+-+ +

# now 192.168.91.22 is read_only=1, so it is assigned to hostgroup_id=2:

Admin@127.0.0.1 [(none)] > select * from mysql_servers

+- +-+ +

| | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

+- +-+ +

| | 0 | 192.168.91.23 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| | 2 | 192.168.91.22 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+- +-+ +

Admin@127.0.0.1 [(none)] > SAVE MYSQL SERVERS TO DISK

Admin@127.0.0.1 [(none)] > save mysql variables to disk

Mysql_users

Admin > INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('user1','147258',0)

Admin > INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('user2','147258',1)

Admin > INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('user3','147258',2)

Admin@127.0.0.1 [(none)] > load mysql users to runtime

Admin@127.0.0.1 [(none)] > save mysql users to disk

# you need to create a corresponding user name and password at the database level to log in to the database:

Root@localhost [(none)] > create user user1@'%' identified by '147258'

Root@localhost [(none)] > grant all on *. * to user1@'%'

Mysql-u user1-p147258-h 127.0.0.1-P6033

User1@127.0.0.1 [testdb] > select @ @ hostname

+-+

| | @ @ hostname |

+-+

| | Darren1 |

+-+

# you can test with sysbench:

[root@Darren1] # sysbench--time=20-- threads=4-- mysql-host=localhost-- mysql-user=user1-- mysql-password='147258'-- mysql-port=6033-- mysql-db=sbtest-- tables=4-- table_size=1000 / home/mysql/sysbench-1.0.3/src/lua/oltp_read_write.lua prepare

[root@Darren1] # sysbench--time=20-- threads=4-- mysql-host=localhost-- mysql-user=user1-- mysql-password='147258'-- mysql-port=6033-- mysql-db=sbtest-- tables=4-- table_size=1000 / home/mysql/sysbench-1.0.3/src/lua/oltp_read_write.lua run

ProxySQL Statistics

ProxySQL can collect a large amount of statistical information through the stats library

Admin@127.0.0.1 [(none)] > show tables from stats

+-+

| | tables |

+-+

| | global_variables |

| | stats_mysql_commands_counters |

| | stats_mysql_connection_pool |

| | stats_mysql_connection_pool_reset |

| | stats_mysql_global |

| | stats_mysql_processlist |

| | stats_mysql_query_digest |

| | stats_mysql_query_digest_reset |

| | stats_mysql_query_rules |

| | stats_mysql_users |

+-+

# stats.stats_mysql_connection_ Pool table:

Admin@127.0.0.1 [(none)] > SELECT * FROM stats.stats_mysql_connection_pool

+-- -+

| | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |

+-- -+

| | 0 | 192.168.91.23 | 3306 | ONLINE | 0 | 0 | 0 | 22 | 0 | 0 | 0 | 168 |

| | 2 | 192.168.91.22 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 926 | |

| | 1 | 192.168.91.22 | 3306 | ONLINE | 0 | 0 | 176 | 0 | 0 | 0 | 926 | |

+-- -+

# stats_mysql_commands_ countermeasures table:

Function:

Returns detailed information about the type of statements executed, and the distribution of execution time!

# count some sql operations:

Admin@127.0.0.1 [(none)] > select * from stats_mysql_commands_counters

+- -+

| | Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |

+- -+

| | ALTER_TABLE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | ALTER_VIEW | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | ANALYZE_TABLE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | BEGIN | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | CALL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | CHANGE_MASTER | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

| | COMMIT | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

.

# stats_mysql_query_digest to view the query operations performed

Admin@127.0.0.1 [(none)] > SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC

+- -+

| | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |

+- -+

| | 0 | information_schema | user1 | 0x1E092DAEFFBBF262 | select? | 1 | 1495462584 | 1495462584 | 111316 | 111316 | 111316 |

| | 0 | testdb | user1 | 0x3765930C7143F468 | select * from T1 | 1 | 1495462644 | 1495462644 | 101438 | 101438 | 101438 |

| | 0 | information_schema | user1 | 0x99531AEFF718C501 | show tables | 2 | 1495462594 | 1495 |

.

# the function of querying table stats_mysql_query_digest_reset is to empty table stats_mysql_query_digest

Admin@127.0.0.1 [(none)] > select * from stats_mysql_query_digest_reset

Admin@127.0.0.1 [(none)] > select * from stats_mysql_query_digest

Empty set (0.00 sec)

Routing rules (MySQL Query Rules)

Table: mysql_query_rules

Function: control the separation of reading and writing

Table: apply=1 means that no further rules are checked if there is a match

# create a rule: the SQL statement that starts with SELECT * FROM T1 $will be executed through the host with hostgroup 0:

Admin@127.0.0.1 [(none)] > INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10 SELECT * FROM T1 $', 0meme 1)

Admin@127.0.0.1 [(none)] > LOAD MYSQL QUERY RULES TO RUNTIME

[root@Darren1 lua] # mysql-u user1-p147258-h 127.0.0.1-P6033

User1@127.0.0.1 [testdb] > select * from T1

# you can see that the previous select statement is assigned to the server with hostgroup 0:

Admin@127.0.0.1 [(none)] > SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC

+-- +

| | hg | sum_time | count_star | digest_text | |

+-- +

| | 0 | 716 | 1 | select * from T1 |

+-- +

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.