Proxysql read/write splitting configuration, proxysql read/write splitting

Proxysql read/write splitting configuration, proxysql read/write splitting

ProxySQL is the read/write splitting middleware promoted by Percona. It is:

Https://www.percona.com/downloads/proxysql/ I. Installation

1: Download wget pull-ivh proxysql-1.4.5-1.1.el6.x86_64.rpm3: start service proxysql start/stop/status

Ii. Configuration

-- Log on to the Proxysql management and monitoring interface (the Mysql protocol is also used. The port is 6032. If-h is not added, the local sock is automatically connected to the mysql port 3306, therefore, you must add the-h Parameter) mysql-uadmin-padmin-P6032-h127.0.0.1

6032 is the management and monitoring port, 6033 is the port that provides external database services, and admin/amin is the default account and password for management and monitoring, which can be found in admin_variables in the proxysql. conf file.

Main

Is the default database name, which stores information such as backend db instances, user verification, and routing rules in the table. The table name starting with runtime _ indicates the configuration content currently running in proxysql. It cannot be modified using dml statements. You can only modify the corresponding tables not starting with runtime _ (in memory, then LOAD it to take effect, SAVE it to the hard disk for the next restart to LOAD.

Disk

Is persistent to the hard disk configuration, sqlite data file.

Stats

It is the statistical information captured by running proxysql, including the number of executions of each command to the backend, traffic, processlist, query type Summary/execution time, and so on.

Monitor

The information collected by the monitor module is mainly used to check the health/delay of backend databases.

After you log on to the Proxysql management and monitoring page, you can directly query the list without using the use database_name; command.

Main table information:

Most importantlyMysql_servers, mysql_users, mysql_query_rulesThese three tables are rarely modified in other tables. The content of the three most important tables can also be found in/etc/proxysql. conf, but it is only useful for the first initialization. Subsequent modifications and initialization information are stored in the sqllite file. We recommend that you do not modify/etc/proxysql. make any changes to the conf file.

• Mysql_servers:

Define the IP addresses of all available mysql databases. The hostgroup_id can be customized. You only need to set the Read and Write groups to different hostgroup_id.

In this example, three nodes are configured in the MHA environment, 59, 60, and 61 are MHA nodes, of which 59 are currently master nodes and 200 are mha vip addresses (Public IP addresses ).

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'10.0.1.59',3306,1,1000,10,'test my proxysql');insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'10.0.1.60',3306,1,1000,10,'test my proxysql');insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'10.0.1.61',3306,1,1000,10,'test my proxysql');insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'10.0.1.200',3306,1,1000,10,'test my proxysql');

Here, we set the IP--59 of the three nodes of MHA, hostgroup_id of 60 and 61 to 100, and set hostgroup_id of VIP-200 of MHA to 1000 to achieve load balancing of read/write splitting and read.

• Mysql_users:

Define the database account and password for connecting to proxysql. First, the Account must exist in the mysql database. Here, the existing account leo is used, you can see that this account can connect to both mysql and proxysql's port 6033. Default_hostgroup is set to 1000 defined above, so that proxysql is automatically connected to the mha vip, that is, it is always connected to the master database by default.

insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)values('leo','leo',1,1000,1);

We recommend that you define another monitoring account as follows:

Mysql> grant super, replication client on *. * TO 'proxysql' identified by 'proxysql'; then mysql-uadmin-padmin-h127.0.0.1-P6032 enter the management and monitoring interface: set mysql-monitor_username = 'proxysql '; set mysql-monitor_password = 'proxysql'; -- this account can also directly use the default admin: admin without configuring, the difference is that the default admin: admin permission is very large, you can only view the monitoring information for your proxysql account.

Finally, load and save the preceding changes in sqlite:

load mysql servers to runtime;load mysql users to runtime;load mysql variables to runtime;save mysql servers to disk;save mysql users to disk;save mysql variables to disk;

• Mysql_query_rules:

Define read/write splitting rules. We can see that regular expressions are implemented. The following definitions indicate that all select statements except select for update are forwarded to the virtual node with hostgroup_id 100, that is to say, the read operation Load balancer is allocated to three databases: 59, 60, and 61. For other operations, the default hostgroup_id is 1000, that is, the master database of MHA.

Insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^ SELECT. * for update $ ',); insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1,' ^ select ); -- load and save the preceding modification in sqlite. Load mysql query rules to runtime; save mysql query rules to disk;

• View related statistics

After a series of addition, deletion, modification, and query operations, you can view the statistics in the following view. You need to use the monitoring account to connect to port 6032 of proxysql. in this example, the connection mode is mysql-uproxysql-pproxysql-P6032-h127.0.0.1 (127.0.0.1 is the server where proxsql is located, and I will check it locally)

select * from stats_mysql_query_digest_reset;

Iii. SummaryIn this example, Proxysql is used to implement MHA read/write splitting. The VIP is set to a readable hostgroup, and the real IP address of the three MHA servers is set to a readable hostgroup, however, when the MHA synchronization latency is large, the latest data may not be read.